When we started looking at macro variables we were playing with some code like this:
View the macro code
Sub AddOneMacro() 'This macro increases the value of the active cell by 1 'Select the active cell ActiveCell.Select 'Declare that x is a variable and that x must be an integer Dim x As Integer 'Make x equal to the ActiveCell x = ActiveCell 'Add 1 to x x = x + 1 'Output the result into the active cell ActiveCell = x End Sub
At that stage we talked briefly about the line: “Dim x as Integer”. We ended up noticing that the code would work without that line included and that Excel was clever enough to interpret our code without that line being included at all. We’re hoping that, with what you’ve learned so far, you’re confident enough now to try running the code for yourself (you should be able to copy and paste the code above into your own macro by now, commenting out the
Dim X As Integer line to see whether the macro still works).
Remember “x” in the line “Dim x as Integer” above can be replaced by anything you like as the variable name – we proved that when we first started looking at macro variables
Why the word 'Dim'?
Why the word “Dim”? The answer is: history. In historic programming “Dim” stood for “Dimension” which kind of makes sense when you understand that what we’re doing with variables is setting a part (or a dimension) of the computer’s memory aside to store some values and do some work for us. Perhaps it’s helpful just to regard “Dim” as standing for something like “Declared in memory” i.e. imagine that we’ve “Declared” to Excel’s computer memory that, in this case, x has to be an integer. Don’t worry, soon you’ll get into the the habit of using “Dim” when you need to and it won’t be that hard to remember.
Why define variables?
Why could it be useful to define variables in VBA?
The code works fine without including the line beginning “Dim…” and without defining the variable. Often you’ll find that’s the case: things work fine without the “Dim” statement. So why could it ever be necessary or helpful to include a line of code like that? Sometimes it can be useful to ‘tell’ Excel what kind of variable it should expect, and what it should do if it is ‘given’ a different kind of variable to work with. You can change the macro’s behaviour with the Dim statement – we illustrate that below. However, as mentioned, many times you’ll find that you don’t need the Dim statement at all.
Behaviour 1: 'tell' Excel to expect an integer
Option 1: Dim as Integer
If you run the macro using the code above (which includes the line “Dim x As Integer”) and enter the value 2.45 into the active cell, Excel will truncate the value to 2 and then add 1 to it. The active cell ends up with the value of 3 in it. That’s one behaviour, but you can make the macro behave differently if you change the way you declare the variables (see the next example below).
Behaviour 2: 'tell' Excel to expect a number with a decimal point
Option 2: Dim As Double
If you leave the line out so that Excel resorts to its default, or declare the variable as a “Double” (= a number with a decimal point), the macro starts behaving differently. You can copy and paste the code below into your own spreadsheet if you wish so you can see what happens.
Sub AddOneMacro() 'This macro increases the value of the active cell by 1 'Select the active cell ActiveCell.Select 'Note the line below can be deleted and this particular macro will still behave the same way Dim x As Double 'Set x as being equal to the ActiveCell x = ActiveCell 'Add 1 to x x = x + 1 'Output the result into the active cell ActiveCell = x End Sub
In the case above the line “Dim x as Double” makes it clear to Excel that it should expect and work with a number that has a decimal point. If you try out the code below and enter e.g. the value 2.45 into the active cell and then run the macro, the value 3.45 results. Changing that one line (in fact, that one word) makes the macro behave differently.
Defining variables can make sure your code behaves the way you want – it helps error-proof your code a little bit.
Different kinds of VBA variable
Different types of VBA variables
There are many different types of VBA variables. Here are some you will find especially useful:
- “Dim x as Integer” – defines the variable “x” as an integer = a whole number
- “Dim x As Double” – defines the variable as a number with a decimal point
- “Dim x As String” – defines the variable as a string of characters i.e. text.
There are others as well though (some of which we hope are self explanatory):
- Boolean – defines the variable as either the logical condition TRUE or FALSE
- Date – in the mm/dd/yyyy format
- Range – a range of cells.
In fact there are lots and lots: see this list of VBA variables from Microsoft. You’re unlikely to need to use many though and many times you’ll find you can get your code working the way you want without having to declare a variable at all (although VBA maestros will happily tell you that it’s good practice always to declare your variables because it saves a bit of computer memory).
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering macros.