Building Blocks of Microsoft Excel VBA

Learn how to automate repetitive or complex tasks using the power of Excel VBA.

Photo by Alex Knight on Unsplash

Most modern programming languages have a set up similar building blocks, for example

  1. Receiving input from the user and Showing output to the user
  2. Ability to store values in variables (usually of different kinds such as integers, floating points or character)
  3. A string of characters where you can store names, addresses, or any other kind of text
  4. Some advance data types such as arrays which can store a series of regular variables (such as a series of integers)
  5. Ability to loop your code in the sense that you want to receive 10 names from a user, you will write the code for that 10 times, but just once and tell the computer to loop through it 10 times
  6. Ability to execute statements of code conditionally, for example if marks are more than 40 then the student passes else fails
  7. Put your code in functions
  8. Advanced data types that are formed through a combination of one or more types of basic data types such as structures or classes
  9. Read file from a disk and save file to a disk
  10. Ability to comment your code so you can understand it when you revisit it some time later

Lets dive right in and see how we can do these things in VBA.

Before we can begin to write a program in VBA, also known as a macro, we need to enable the developer tab. You can do this by going to the File > Options > Customise ribbon. Once the developer tab is available, go there and choose the leftmost option which says Visual Basic. Now you will see a panel in the left where you can double clock on the sheet name you are working on. This will open a empty code window. Here write the following code and save the file as a macro enabled workbook (extension will be .xlsb).

Sub simple_hello()
Range("A2").Value = "Hello World!"
End Sub

Close the file, then opn it back again and chose the option (if shown) to enable macros. Now go to the Developer tab again and this time select the second option called Macros. Here you should see the macro that you just created. Select it and hit run!

There are several ways in which a macro can show output to the user. Let’s look at some ways of showing output:

'Method 1:
Range("A2").Value = "Hello"
'Method 2:
Worksheets("Sheet1").Range("B2").Value = "Hello"
'Method 3:
Worksheets(1).Range("C2").Value = "Hello"
'Method 4:
MsgBox "I added Hello in cell A2, B2 and C2"
'Method 5:
MsgBox "Hello " & Range("C5").Value & vbNewLine & "So you are " & Range("C6") & " years old!"

VBA allows 4 key types of variables: Integer, String, Double and Boolean Integer is good for soring most numeric values, String is for character input and Boolean is for a 0/1 or yes/no type of data. Here are some examples:

'Integer:
Dim x As Integer
x = 6
Range("A1").Value = x
'String:
Dim book As String
book = "bible"
Range("A1").Value = book
'Double:
Dim x As Double
x = 5.5
MsgBox "value is " & x
'Boolean:
Dim continue As Boolean
continue = True
If continue = True Then MsgBox "Boolean variables are cool"

Key idea here is to learn how to manipulate string variables

There are a few common operations that we will focus on: a. Joining strings

'Join Strings
Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"
MsgBox text1 & " " & text2

b. Left/right or middle functions — To extract the leftmost/rightmost or middle characters from a string.

Dim text As String
text = "example text"
MsgBox Left(text, 4)
'Just as left, we can also extract a substing from the right or middle
MsgBox Right("example text", 2)
MsgBox Mid("example text", 9, 2)

c. To get the length of a string, use Len.

MsgBox Len("example text")

d. To find the position of a substring in a string, use Instr.

MsgBox InStr("example text", "am")

Array’s are a series of similar type of data stored together in one variable. Arrays can be one-dimentional or multi-dimentional. a. Following example shows how a one dimentional array works:

Dim Films(1 To 5) As String
Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"
MsgBox Films(4)

b. Following example shows how a two dimentional array works:

Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer
For i = 1 To 5
For j = 1 To 2
Films(i, j) = Cells(i, j).Value
Next j
Next i
MsgBox Films(4, 2)

VBA has several looping options (for, do-while, do-until). There are options of nesting (single, double, triple, ..) loops. a. Following example shows how a simple/single for loop works:

Dim i As Integer
For i = 1 To 6
Cells(i, 1).Value = 100
Next i

b. Following example shows how a double for loop works:

Dim i As Integer, j As Integer
For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100
Next j
Next i

c. Following example shows how a triple for loop works:

Dim c As Integer, i As Integer, j As Integer
For c = 1 To 3
For i = 1 To 6
For j = 1 To 2
Worksheets(c).Cells(i, j).Value = 100
Next j
Next i
Next c

VBA also has a do-while loop. Following example shows how it works:

Dim i As Integer
i = 1
Do While i < 6
Cells(i, 1).Value = 20
i = i + 1
Loop

VBA also has a do-until loop. Following example shows how it works:

Dim i As Integer
i = 1
Do Until i > 6
Cells(i, 1).Value = 20
i = i + 1
Loop

a. If Then Statement — VBA has the option of an if statement, which executes a piece of code only if a specified condition is met.

Dim score As Integer, result As String
score = Range("A1").Value
If score >= 60 Then result = "pass"
Range("B1").Value = result
Dim score As Integer, result As String
score = Range("A1").Value

b. If Else Statement — VBA has the option of an if-else statement, which executes a piece of code only if a specified condition is met, if not then it executes another piece of code.

If score >= 60 Then
result = "pass"
Else
result = "fail"
End If
Range("B1").Value = result

c. If Else Statement — VBA has the option of an if-else statement, which executes a piece of code only if a specified condition is met, if not then it executes another piece of code.

'Select Case
'First, declare two variables. One variable of type Integer named score and one variable of type String named result
Dim score As Integer, result As String
'We initialize the variable score with the value of cell A1
score = Range("A1").Value
'Add the Select Case structure
Select Case score
Case Is >= 80
result = "very good"
Case Is >= 70
result = "good"
Case Is >= 60
result = "sufficient"
Case Else
result = "insufficient"
End Select
'Write the value of the variable result to cell B1
Range("B1").Value = result

VBA allows us to specify a function or a sub. The difference between the two is that funciton allows us to return a variable whereas a sub does not.

a. Function — If you want Excel VBA to perform a task that returns a result, you can use a function. Place a function into a module (In the Visual Basic Editor, click Insert, Module). For example, the function with name Area.

'Explanation: This function has two arguments (of type Double) and a return type (the part after As also of type Double). You can use the name of the function (Area) in your code to indicate which result you want to return (here x * y).
Function Area(x As Double, y As Double) As Double
Area = x * y
End Function
'Explanation: The function returns a value so you have to 'catch' this value in your code. You can use another variable (z) for this. Next, you can add another value to this variable (if you want). Finally, display the value using a MsgBox.
Dim z As Double
z = Area(3, 5) + 2
MsgBox z

b. Sub — If you want Excel VBA to perform some actions, you can use a sub.

Place a sub into a module (In the Visual Basic Editor, click Insert, Module). For example, the sub with name Area.
Sub Area(x As Double, y As Double)
MsgBox x * y
End Sub
'Explanation: This sub has two arguments (of type Double). It does not have a return type! You can refer to this sub (call the sub) from somewhere else in your code by simply using the name of the sub and giving a value for each argument.
'Call it using Area 3, 5

VBA Class allows us to create our own Object function in which we can add any kind of features, details of the command line, type of function. When we create Class in VBA, they act like totally an independent object function but they all are connected together. Detailed example of how to do this is out of the scope of this article.

We can tell VBA that a line of code is a comment by starting it with an single inverted comma.

'this is a comment

To close I will emphasize the importance of practicing in learning anything new. Persistence and trying out different combinations of these building blocks for solving easier problems first and more complex ones later on is the only way to become fluent.

Comments welcome!

Experienced in synthesizing data to identify trends, deliver insights and recommendations. Focused on customer lifecycle, cross-sell, and employee performance.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store