Introduction to VBA in Excel
Hello World
Our first encouter with VBA will be to display the familiar "Hello World" message using a button.
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Explicit vs implicit variable declaration
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. When using Record Macro, VBA will track and translate every action you 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.g. 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 | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Making a function
In the previous tutorials, we have made procedures using the Sub statement. We will now look at how we can make a procedure using the Function statement. First, let's have a quick look at an embedded function. SUM(number1;number2...) (SUMMER in Norwegian) is a function that sums up all its parameters. This function is reachable for execution from a cell in our sheet (picture).
We will now make our very own custom function that can be adressed the same way as SUM, but the calculations made are specified by us.
Expand | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||||
| |||||||||||||||||||
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, and declare it automatically, thus not informing us
|
Useful applications of VBA in Excel
Expand | |||||||
---|---|---|---|---|---|---|---|
| |||||||
In order to solve our problem, we will use some code originally made by Jon Peltier. If you are looking to learn more about this method (and other more robust ones), check out his tutorial in this link.
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:
|
Tick the "Require Variable Declaration" box. Click "OK".
BibTeX Display Table |
---|