• Created by Unknown User (jonakaa), last modified on 21.09.2019

Throughout the site we will constantly refer to the offical VBA documentation through links. Getting used to reading such documentation will help you out in the long run, as well as keep us from a lot of rewriting. It may also often contain more in-depth documentation than what we have written as well.

Page content

Learning the basics

Procedures: Sub vs Function

In VBA, programs are usually written using either a Sub procedure or a Function procedure. Both procedures can take arguments by value or by reference. The main difference between a Sub and a Function is that a Sub can not return a value while a Function can (for Subs, this can be bypassed by using parameters passed by reference).

Sub

A Sub has to begin with the statement Sub, and end with the statement End Sub. 

Simple Sub procedure
Sub ExampleSub()
	MsgBox "My first Sub procedure!"
End Sub

Function

A Function has to begin with the statement Function, and end with the statement End Function. The value returned by a function is the same as the function name.

Simple function procedure
Function Squared(num As Integer)
	Squared = num ^ 2
End Function

' We can also make a function that does not return anything.
Function myFunc()
	MsgBox "My second Function procedure!"
End Function

Interaction with each other

Both Functions and Subs can be called from within each other. Using such procedures to split your program into smaller parts is a good way to keep the code clean, reuseable and easy to read.

Interaction example
Sub displayMsg()
    MsgBox "Area"
End Sub

Function area(num1 As Integer, num2 As Integer)
	area = num1 * num2
End Function

' Now, let's make a Function and a Sub calling the procedures above.
' Both will do exactly the same.

Sub mainSub()
    displayMsg
    MsgBox area(3, 17)  
End Sub

Function mainFunction()
    displayMsg
    MsgBox area(3, 17) 
End Function

What is the output of mainSub and mainFunction?

If...Then...Else

The If...Then...Else statement in VBA is similar to If statements found in other languages, such as Pyhton or C++. The main difference is the syntax, which we will shown using some examples. Note that the conditions used in the If...Then...Else statements are further described in the chapter Operators.

Examples 
Sub Example()

If True Then    ' "Then" has to follow the condition after "If" or "ElseIf", not after "Else"
    MsgBox "My first If statement works!"
Else
    MsgBox "Something is really wrong"
End If      ' Remember to always close the If statement

myvar = 5
If myvar < 5 Then
    MsgBox myvar & " is less than 5"
ElseIf myvar > 5 Then
    MsgBox myvar & " is larger than 5"
Else
    MsgBox myvar & " is equal to 5"     ' This is displayed, since myVar = 5
End If

' Nested If statements will very fast become complex, thus making them difficult to read:
myvar = 4
If myvar > 0 Then
    If myvar < 10 Then
        If myvar <> 5 Then
            MsgBox "myVar is more than 0, less than 10 and not equal to 5"
        End If
    End If
End If

' Let's make the example above using only one If statement
myvar = 4
If myvar > 0 And myvar < 10 And myvar <> 5 Then
    MsgBox "myVar is more than 0, less than 10 and not equal to 5"
End If

End Sub

Loops

Infinite loops in VBA can be exited by pressing Esc + Break. Occasionally this does not work, and you will have to force Excel to close, e.g. by pressing Ctrl + Shift + Esc and right-click on Excel. Forcing an exit may not save your current work, so make sure to save while writing.

There are two types of for statements in VBA, For Each...Next and For...Next.

The For Each...Next statement repeats a block of statements for each element in an array or collection. The For...Next statement repeats a block of statements a specified number of times. You may use the Exit For statement inside any of the loops to exit from within.

Sub Example()

Dim myArray(1 To 3) As Integer  ' Declaring myArray as an array of size 3, first index equals 1.
Dim counter As Integer
Dim element

' Declaring each element in our array using a "For...Next" loop.
For counter = 1 To 3
    myArray(counter) = counter
Next counter

' We want to loop through the whole array when displaying. A "For Each...Next" loop will do just this.
For Each element In myArray     ' The element in a "For Each...Next" loop, in this case named "element" has to be of type "Variant",
								' e.g. not "Integer" or "String" etc.
    MsgBox element  ' Note that this in general is not a good way to display an array, but since our example array is so small, it works.
Next

' Let's now assume that the values in myArray are unknown. We want to locate which index the value 2 is located at.
' This is a good example of when to use the "Exit For" statement.
For counter = 1 To 3
    If myArray(counter) = 2 Then
        Exit For
    End If
Next counter
MsgBox "The value 2 is located at index " & counter ' Displays "... at index 2" which is correct.

End Sub

A tips to make your loops faster is to declare your counter variable as the smallest data type necessary considering your range:

Dim CountFaster As Integer	    ' First case, use Integer (2 bytes, range: [-32,768, 32,767]).
For CountFaster = 0 To 32766
Next CountFaster
 
Dim CountSlower As Variant	    ' Second case, use Variant(16 bytes, 
								' range same as Double: [4.94065645841247E-324, 1.79769313486232E308] for positive number).
For CountSlower = 0 To 32766
Next CountSlower

As we can see in the above case, declaring our counter as an integer type saves us 14 bytes of space, thus making the code faster. The different data types and their ranges can be found at the data type summary.

There are two types of while statements in VBA, the Do...Loop and the While...Wend loop.

The Do...Loop repeats a block of statements while a condition is True or until a condition becomes True. This is the preffered while statement in VBA, as "it provides a more structured and flexible way to perform looping." As we will see in the examples, the Do...Loop can be written as a Do While...Loop or Do Until...Loop. We may also choose to have the condition specified at the top or bottom of the loop, thus giving us even more possibilites.

Examples
' All examples are made to do 10 repetitions.


Sub FirstWhile()
    counter = 0
    Do While counter < 10
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions."  ' The loop made 10 repetitions.
End Sub
 
Sub LastWhile()
    counter = 0
    Do
        counter = counter + 1
    Loop While counter < 10
    MsgBox "The loop made " & counter & " repetitions." ' The loop made 10 repetitions.
End Sub

Sub FirstUntil()
    counter = 0
    Do Until counter = 10
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions." ' The loop made 10 repetitions.
End Sub

Sub LastUntil()
    counter = 0
    Do
        counter = counter + 1
    Loop Until counter = 10
    MsgBox "The loop made " & counter & " repetitions." ' The loop made 10 repetitions.
End Sub

At first, the four methods seemingly does exatcly the same. This is often the case, but in some situations, one approach is more desirable than another. Below is an example showing how moving the condition from the top to the bottom of the loop can save you some unnessecary coding.

' We want to make a routine that keeps asking for numbers from the user until it gets a number below 10.

' Using a Do While...Loop.
Sub FirstWhile()

Dim myNum As Integer    ' When myNum is declared as an Integer, it is set equal to 0 by default.

' If we choose to not do anything to myNum before the loop, it will still be 0, and we will never enter the loop.
' This is undisirable, as we want the myNum to be declared by the user.
' To fix this, we may either set myNum equal a number above 10,
myNum = 11
' OR we may request an input from the user before we enter the loop.
myNum = InputBox("Give me a number")
' Notice that by either choice, we have to spend a line preparing myNum for the loop.

Do While myNum > 10
    myNum = InputBox("Give me a number below 10")
Loop

End Sub

' What if we were to do the same task using a Do...Loop While?
Sub LastWhile()

Dim myNum As Integer    ' When myNum is declared as an Integer, it is set equal to 0 by default.

' When the condition is set at the bottom of the loop, we are guaranteed to enter the loop at least once.
' By doing so, the user will have to give myNum a new number before it is evaluated as above or below 10.
' Notice that all we did were to move the loop condition, which caused us to avoid an unnessercary line
Do
    myNum = InputBox("Give me a number below 10")
Loop While myNum > 10

End Sub

Another feature included in the Do...Loop is Exit Do. It allows us to exit a loop even though the original loop condition is still unvalid.

Sub ExampleExitDo()
counter = 0

Do While True   ' This is now an infinite loop
    counter = counter + 1
    If counter = 10 Then Exit Do    ' Even though it is an infinite loop, we may use the Exit Do statement to exit the loop from within.
                                    ' The Exit Do statement is often connected to an If..Then statement.
Loop
MsgBox "The loop made " & counter & " repetitions." ' The loop made 10 repetitions.
End Sub


The other while statement is While...Wend. Since it's not as recommended as the Do...Loop, we will only brifely cover it. The main differences between the two loops are that in While...Wend;

  • you can only have a condition at the start of the loop.
  • you don't have the option of using Until instead of While.
  • you may not exit the loop from within using Exit Do.
Sub ExampleWhileWend()

counter = 0

While counter < 10
    counter = counter + 1
Wend

MsgBox "The loop made " & counter & " repetitions." ' The loop made 10 repetitions.

End Sub

Operators

Arithmetic operators in VBA, written in the order of preference when evaluated:

OperatorDescription
^Exponentiation
-Negetation

* and /

Multiplication and divison
\Integer division
ModModulus arithmetic
+ and -Addition and subtraction
Examples
Dim myResult

myResult = 2 ^ 3    ' Returns 8
myResult = 5 * 5    ' Returns 25
myResult = 10 / 3   ' Returns 3,333333333333333
myResult = 10 \ 3   ' Returns 3
myResult = 10 Mod 3 ' Returns 1
myResult = 6 + 5    ' Returns 11
myResult = -6 - 1   ' Returns -7

Comparison operators are used to compare expressions. They may return True, False or Null. Below are the comparison operators written in the order of preference when evaluated:

OperatorExplanation (True if)
=Equality
< >Inequality
<Less than
>Greater than
< =Less than or equal to
> =Greater than or equal to
Like and Is

The operators Like and Is are described further down, as they have some special functionalities.


To learn when the compartion operators return Null, read the offical documentation on the comparison operators. Here, you will also find more detailed information regarding the actual comparisons done when using the operators. This includes outcome of comparing different variant types, such as a string and a double.

Let's look at some exampels:

Dim MyResult, Var1, Var2	' Variables are declared as type "Variant"
Dim str1 As String
Dim num1 As Integer

MyResult = (10 < 5)    		' Returns False.
MyResult = (1 <> 2)		   ' Returns True.
MyResult = ("5" > "4")    	' Returns True.
MyResult = (4 = 4)			' Returns True.
MyResult = 5 = 5			' Returns True. Notice that we don't have to include the brackets as in the line above.
MyResult = 5 = 2			' Returns False.

MyResult = ("hi" > "hello") ' Returns True, since ("i" > "e").
MyResult = ("hi" > "Hi")    ' Returns True, since the ASCII values for lowercase letters are higher than for uppercase letters.


Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2)    ' Returns True, since "Empty" is evaluated as 0.

Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2)    ' Returns False, since "Empty" is evaluated as 0.

Var1 = 0: Var2 = Empty
MyResult = (Var1 = Var2)    ' Returns True, since "Empty" is evaluated as 0.


Var1 = "1": Var2 = 4
MyResult = (Var1 > Var2)    ' Returns True, since a "Variant of type string" is always greater than a "Variant of type numeric".

str1 = "1": num1 = 4
MyResult = (str1 > num1)	   ' Returns False, since the "string" may be converted to a number, thus using a numerical comparison.

str1 = "a": num1 = 4
MyResult = (str1 > num1)	   ' Returns an error of type "Type missmatch", this because the "string" can not be converted to a number.

As we can see, you may compare almost anything in VBA. Knowing what the outcome of each comparison is, will give you a great advantage when coding.


In addition to the operators above, there are two more comparison operators, Like and Is. They have some special functionalities, and were therefore not described alongside the others.

The Is-operator is used to combine two reference variables. If both objects refer to the same object, the comparison returns True; if they do not, it returns False. An example is shown below:

Dim A As ThisWorkbook   ' A is declared as a valid object in order for "Is" to work.
Dim B, C, myResult

Set C = A           ' C refers to object A.
Set B = A           ' B also refers to object A.

myResult = C Is B   ' Returns True, since both variabels refer to the same object.

The Like-operator is used to compare strings. Its syntax is "result = string Like pattern", and it returns True if the string matches the pattern. If not, it returns False, and if either is Null, it returns Null.

Characters in patternMatches in string
?Any single character
*Zero or more characters.
#Any single digit (0-9).
[ charlist ]Any single character in charlist.
[ !charlist ]Any single character not in charlist.

Examples showing the use of Like:

Dim myResult
myResult = "I" Like "[A-Z]"     ' Returns True, since "I" is between "A" and "Z"
myResult = "i" Like "[A-Z]"     ' Returns False, since lowercase characters are places elsewhere in the ASCII-system
myResult = "a2a" Like "a#a"     ' Returns True, since the pattern requires any single digit between the a's.
myResult = "a5" Like "a #"      ' Returns False, since the pattern has a space between "a" and "#", which is not in the string
myResult = "ab" Like "a*b"      ' Returns True, since the * requires zero or more characters.
myResult = "a*b" Like "a[*]b"   ' Returns True, since the pattern requires a single "*" between "a" and "b".
myResult = "a**b" Like "a[*]b"  ' Returns False.
myResult = "aBBBa" Like "a*a"   ' Returns True.

Logical operators in VBA, written in the order of preference when evaluated:

OperatorDescription
NotUsed to perform logical negation on an expression.
AndUsed to perform a logical conjunction on two expressions.
OrUsed to perform a logical disjunction on two expressions.
XorUsed to perform a logical exclusion on two expressions.
EqvUsed to perform a logical equivalence on two expressions.
ImpUsed to perform a logical implication on two expressions.

The three last operators, Xor, Eqv and Imp may seem unfamiliar, as they are not included in other common languages, such Python or C++. To get a better understanding of them, including tables illustrating how the result is determined, read the operator documentation.

Xor, Eqv and Imp can all be replaced with a combination of Not, And and Or. They are simply added as easier shortcuts.


Examples
Dim myResult

' NOTE! This examples does not take into account if any expressions are "Null"

' For Not to evaluate to True, the expression has to be False.
myResult = Not False        ' Returns True, since True is not False.
myResult = Not 3 > 2        ' Returns False, since the expression after Not evaluates to True.

' For And to evaluate to True, both expressions has to be True.
myResult = True And False   ' Returns False, since one of the expressions are False.
myResult = 2 > 1 And 5 > 6  ' Returns False, since the last comparison is False.

' For Or to evaluate to True, one or both expressions has to be True.
myResult = True Or False    ' Returns True, since one of the expressions is True.
myResult = 3 < 2 Or 5 < 6   ' Returns False, since none of the expressions are True.

' For Xor to evaluate to True, only one of the expressions can be True.
myResult = True Xor True    ' Returns False, since both expressions are True.
myResult = True Xor False   ' Returns True, since only one expression is True.
myResult = 3 < 1 Xor 4 = 3  ' Returns False, since both expressions are False.

' For Eqv to evaluate to True, both expressions has to be the same.
myResult = True Eqv False   ' Returns False, since the expressions are different.
myResult = 2 = 4 Eqv 4 = 2  ' Returns True, since both expressions are False.
myResult = 3 = 3 Eqv 6 = 6  ' Returns True, since both expressions are True.

' For Imp to evaluate to False, expression 1 has to be True and expression 2 has to be False.
' Imp is not used a lot, so if you want to learn the operator, read the added documentation below this example.
' We will therefore not add any examples using Imp, as it would not be useful without the necessary background knowledge.

The implication operator Imp is considered the most advanced logical operator. In some situations, mastering its use may still be very useful. We suggest reading through this document by the Northern Illinois University to improve your understanding of implication in VBA: Implication in programming.pdf

Concatenation operators in VBA, written in the order of preference when evaluated:

OperatorDescription
+Addition
&String concatenation

+ can be used to add numerical values, as well as string concatenation. To learn more about the implications when adding variables of different types, read the operator documentation.

Examples
Dim myResult

myResult = "Concat" & "enation"     ' Returns "Concatenation".
myResult = "Concat" + "enation"     ' Returns "Concatenation".
myResult = 5 + 6        ' Returns 11.
myResult = 5 & 6        ' Returns 56.
myResult = "5" + 6      ' Returns 11.
myResult = "5" & 6      ' Returns 56.
myResult = "5" + "6"    ' Returns 56.
myResult = "5" & "6"    ' Returns 56.
myResult = 1 + "2" & "3"    ' Returns 33, since + are evaluated before &

Explicit vs implicit variable declaration

When writing larger pieces of code, knowing the difference between explicit and implicit variable declaration may save you several hours of debugging.

Before we look at the different variable declarations, we need to establish the difference between the words explicit and implicit.

  • explicit is defined as: "stated clearly and in detail, leaving no room for confusion or doubt."
  • implicit is defined as: "implied, rather than expressly stated"

When coding in Visual Basic, you are by default not obligated to declare your variables before using them. This an example of an implicit variable declaration.

MyVariable = 10

When doing so, VBA will automatically declare it as a variant type, provided that the variable has not been declared before. This is a nice feature, but may cause trouble when writing larger scripts. Let's look at an example:

We have already written a lot of code using the variable "MyVariable". If we were to misspell "MyVariable" with "MyVaiable" once, VBA will think of it as a new variable declared implicitly, and declare it automatically, thus not informing us of our misspell.

MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10

'... lots of code

'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed

To avoid this problem, we can use the Option Explicit Statement. It ensures that all variables must be explicitly declared by using the Dim, Private, Public, ReDim or Static statements. Otherwise, it will display an error at compile time.

If used, the "Option Explicit" statement must appear in a module before any procedures.

Option Explicit

Sub Example()

'Explicitly declaring MyVariable as an integer
Dim MyVariable As Integer

MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10		' We will encounter an error at this line.

'... lots of code

'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed

End Sub

Compiling the code above will result in the following error message.

If you want to make all your modules have the "Option Explicit" statement without having to remember to write it yourself, follow these instructions:

  1. In your VBA editor, go to "Tools" → "Options".


  2. Tick the "Require Variable Declaration" box. Click "OK".

Useful functions

Format

The Format function is a helpful tool when formatting strings to your preference. Since Microsoft already has made a really in-depth explanation including some examples, we have chosen to only show you some additional examples here.

Format function examples 
myVar = Format(50000)
'myVar is now equal to the String "50000".

myVar = Format(50000, "Currency")
'myVar is now equal to the String "kr 50 000,00".

myVar = Format(50000, "#,##0.0")
'myVar is now equal to the String "50,000.0".

myVar = Format(0.88, "Percent")
'myVar is now equal to the String "88.00%".

myVar = Format(0.88, "0.0")
'myVar is now equal to the String "0.9".

myVar = Format("Ola Nordmann", ">")
'myVar is now equal to the String "OLA NORDMANN".

myVar = Format("Ola Nordmann", "<")
'myVar is now equal to the String "ola nordmann".

myVar = Format("123456789", "@@@-@@@-@@@")
'myVar is now equal to the String "123-456-789".

MsgBox

The MsgBox function displays a message in a dialog box and waits for the user to click a button. You may have seen it used in our other tutorials as a simple way to display the value of a variable, but this is just the function in its simplest use. In addition to a message, you can specify e.g. the title or the buttons. The return value of MsgBox is a value (integer) indicating which button the user clicked. To learn about all the buttons and their return values, read the function documentation.


Sub Example1()

MsgBox "Displaying a message!"	' When we only want to display a message, we don't need several parameters.

End Sub

Example 1 displayes the following dialog box:

Sub Example2()

Dim Msg, Style, Title, Response, MyString

Msg = "Do you want to continue ?"                   ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2     ' Define buttons.
Title = "MsgBox Demonstration"                      ' Define title.
Response = MsgBox(Msg, Style, Title)                ' When we have several parameters, 
													' we have to call MsgBox as: MsgBox([parameters]).
If Response = vbYes Then    ' User chose Yes.
    MyString = "Yes"        ' Perform some action.
Else                        ' User chose No.
    MyString = "No"         ' Perform some action.
End If

End Sub

Example 2 displayes the following dialog box:


  • No labels