Introduction to VBA
Hello World
Our first encouter with VBA will be to display the familiar "Hello World" message using a button.
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Recording a macro
Even though a lot of your work in VBA both can and will be made using only programming, it is highly recommended to get to know the Record Macro feature as well. By When using Record Macro, VBA will track and translate everything every action you do perform to code. This can help you in several ways:
- It is always easier to start a task with some existing code instead of a blank sheet.
- Use the fact that VBA translates all your actions into code to your advantage. Instead of searching online for e.i. how to mark a range of cells, try recording a macro doing it and then look at the code created.
Our first task will be to duplicate a sheet using the Record Macro feature.
Expand | ||
---|---|---|
| ||
|
Learning the basics
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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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
Info |
---|
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.i. 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. |
Expand | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
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.
A tips to make your loops faster is to declare your counter variable as the smallest data type necessary considering your range:
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. |
Expand | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||
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."
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.
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.
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;
|
Operators
Expand | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||||||
Arithmetic operators in VBA, written in the order of preference when evaluated:
|
Expand | |||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||||||||||||||||||||||||||||||
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:
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:
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:
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.
Examples showing the use of Like:
|
Expand | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||||||||
Logical operators in VBA, written in the order of preference when evaluated:
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.
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 |
Expand | |||||||
---|---|---|---|---|---|---|---|
| |||||||
Concatenation operators in VBA, written in the order of preference when evaluated: + 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.
|
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.
Expand | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||
Before we look at the different variable declarations, we need to establish the difference between the words explicit and implicit.
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.
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.
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.
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:
|
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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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.i. 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.
Expand | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
Example 1 displayes the following dialog box:
Example 2 displayes the following dialog box: |
BibTeX Display Table |
---|