Declaring and Using Variables in VBA
Variables can be a little tricky to get your head around at first. They are an important concept and a good understanding of them leads to bug-free, easy to read and efficient code. The length of this post reflects the importance of declaring and using variables sensibly in VBA. We will touch on the topic again and again in various other posts on this website.
What are Variables?
In simple terms a variable is a named storage location for data you want to use in a program. The data held within this storage location has a specific type and the value held can change during the program execution. When you use the variable name within a program you will in effect substitute the variable name for the value held at that point in time.
Variables do much more than just hold data – they also tell VBA how to behave when handling that data. For example, adding two string is different to adding two integers as shown in the example below
Public Sub VariableHandling()
Dim strX As String
Dim strY As String
Dim intX As Integer
Dim intY As Integer
strX = "1"
strY = "1"
intX = 1
intY = 1
Debug.Print vbCrLf
Debug.Print " ========== Variable Handling Example ========== "
Debug.Print " strX + strY = " & strX + strY
Debug.Print " intX + intY = " & intX + intY
Debug.Print " =============================================== "
End SubWe might have expected 1 + 1 = 2 in both cases. However, for strings “+” actually means concatenation rather than addition so we get “1” + “1” = “11”. Exactly what a given operator does depends upon the type of variable it operates on.
An additional important consideration is the maximum size of a given variable: each variable is allocated a specific amount of memory on your computer. This means there is a limit on the maximum value a given variable can take. It also means you can improve the performance of your code by simply choosing the most appropriate variables. Using a variable that takes up more space in memory than you need slows your code down. 1There is one important exception to this that I’m aware of related to integer and long data types in VBA. Details can be found on stack overflow here and here. In short there is little gain (and possibly a performance penalty) of using an integer rather than a long data type on more up to date machines.
To illustrate the problems with variable size the below code is designed to compile correctly but to cause a variable overflow error at run time – it doesn’t happen often but is something to watch out for.
Public Sub variableOverflowExample()
Dim loopCtr As Integer
Dim exampleInteger As Integer
' max value for integer is 32767. The below loop will
' cause an overflow exception error at run time BUT
' will compile fine!!!
exampleInteger = 32700
Debug.Print vbCrLf
Debug.Print " ========== Integer Overflow Example =========== "
For loopCtr = 1 To 100
Debug.Print " exampleInteger = " & exampleInteger
exampleInteger = exampleInteger + 1
Next loopCtr
Debug.Print " =============================================== "
End SubThe error we get is shown below.
What Happens when we don’t Declare Variables?
As much as I think VBA is great, there are some parts of it I don’t like. It is a language which gives you “enough rope to hang yourself”. In other words, it’s very easy to do stuff, and equally as easy to completely screw it up without realising it. Not having to declare variables is one of the ways it provides this easy-to-use-and-screw-up functionality.
If you don’t declare your variables in VBA then it will create variables for you of type Variant. While this may not be the end of the world it can lead to programs that are slow and that don’t behave in ways you expect. The below video shows what VBA does when you don’t use Option Explicit and when you don’t declare variables.
Key Reasons to Declare Variables
There are a number of benefits from fully declaring variables with a given type within VBA. Below is a short list of these:
- You will avoid “variable typo errors”;
- Memory management will be better leading to faster code;
- VBA will have to make less decisions on how to perform actions which will again speed up code;
- Your code will be easier to read and follow (assuming you have good naming conventions);
Declaring and Using Variables in VBA without a Data Type
So let’s say you agree with me that you should declare all variables2Some people will disagree. The usual argument is that it slows them down when they are coding. I would argue that the time saved in fixing the bugs in their code that results in not declaring variables more than offsets this.. The first step in doing this is to use Option Explicit to force variable declarations. I have a separate post on this and that should always be the first step.
Once we include Option Explicit VBA will require all variables to be explicitly declared with the Dim keyword. It will not require all declared variables to have a specific type. This is better than not declaring variables at all – just be aware that when you declare but don’t specify a type you will again get variables of type Variant. It will prevent “variable typo errors” but won’t give us the other benefits of improved memory and speed management. The video below demonstrates how we can declare variables without specifying a data type.
Declaring Variables with Specific Types
The proper way to declare variables when using Option Explicit is to use the syntax Dim [myVarName] As [myVarType]. In this way we not only tell VBA that the variable exists but also tell it exactly what type of data it should hold, how much memory it should take up and how it should behave. This is exactly what we want and some examples of declaring various data types are given below.
Public Sub exampleVariableDeclarations()
' Integers of various sizes
Dim myInteger As Integer
Dim myLong As Long
' strings and characters
Dim myString As String ' variable string
Dim myFixedStr As String * 10 ' fixed string
' Real data types
Dim myDouble As Double
Dim mySingle As Single
Dim myCurrency As Currency
' Others
Dim myBool As Boolean
Dim myDate As Date
' fixed string cuts off characters after 10
myFixedStr = "0123456789xxx"
MsgBox myFixedStr, vbOKOnly
End SubDeclaring One Variable Per Line of Code
A mistake I have seen a number of times is when people try to declare multiple variables on the same line of code but don’t do it correctly. Usually the intention is to declare variables and their types. However, when it’s not done right you can end up with just one variable having the correct type and all others having a Variant type. The code below demonstrates this:
Public Sub MultipleVariablesPerLine()
Dim intX, intY, intZ As Integer
Debug.Print vbCrLf
Debug.Print " ===== Multiple Variables Per Line Example ===== "
Debug.Print " before assignment: "
Debug.Print " intX = " & intX & " TypeName(intX) = " & TypeName(intX)
Debug.Print " intY = " & intY & " TypeName(intY) = " & TypeName(intY)
Debug.Print " intZ = " & intZ & " TypeName(intZ) = " & TypeName(intZ)
Debug.Print vbCrLf
intX = 1
intY = 2
intZ = 3
Debug.Print " after assignment: "
Debug.Print " intX = " & intX & " TypeName(intX) = " & TypeName(intX)
Debug.Print " intY = " & intY & " TypeName(intY) = " & TypeName(intY)
Debug.Print " intZ = " & intZ & " TypeName(intZ) = " & TypeName(intZ)
Debug.Print " =============================================== "
Stop
' Note: when the code stops here open up a Watch window and add a watch
' for each of the variables ...
' you should see only intZ is actually an integer!
' the others are Variant/Integer but "look" like integers because they
' were assigned integer values!
End SubAn extra benefit of declaring one variable per line of code is that your code is much easier to read.
Common VBA Data Types
There are a number of different built in variable types we can use in VBA coding. The right variable type will depend greatly upon usage. There will usually be a number of variables you can use for the same job (for example a loop counter can be an integer or long). As a general rule I tend to ask myself:
- What are the minimum and maximum values the variable could take?;
- How important is the level of precision required?
In many cases we will find we can use a variable type which takes up less memory and has a lower level of precision is perfectly fine for the given task. By using this we can speed up our calculations. This post gives a table with some of the most common VBA variable types, the memory they use and re-iterates some of the key points we make in this post about variables.
Proper Naming is Essential
The name we give a variable can have a huge impact on how easy it is to understand your code and doing it well reduces the need for comments. A general rule is to name a variable according what data it contains. The best way to explain is with a couple of examples. Take a look at the two below – which is easier to read and understand?
Public Function Ft(ByVal S As Double, _
ByVal r As Double, _
ByVal q As Double, _
ByVal T As Double)
Ft = S * Exp((r - q) * T)
End FunctionPublic Function forwardPrice(spotPrice As Double, _
startTimeYrs As Integer, _
expiryTimeYrs As Integer, _
riskFreeRate As Double, _
dividendRate As Double) As Double
Dim durationYrs As Integer
Dim costOfCarry As Double
durationYrs = expiryTimeYrs - startTimeYrs
costOfCarry = riskFreeRate - dividendRate
forwardPrice = spotPrice * Exp(costOfCarry * durationYrs)
End FunctionUsing Variables to Program Defensively
For the final section of this post I’m going to provide a set of golden rules for declaring and using variables in VBA. Some I will give a brief explanation of here and for others I won’t. Some won’t make sense right now … but you should use them anyway and trust there is a good reason for them!
- Always use Option Explicit;
- Declare one variable per line of code;
- Initialize variables explicitly;
- Avoid using Dim myObject As New Object – instead use Dim and As THEN Set and New;
- Avoid using Variant data types;
- Variables should have the tightest scope possible;
The use of Option Explicit is discussed in a separate post so I won’t go into details here. We have also already discussed declaring one variable per line of code.
When we declare variables in VBA they start out with a default value. In most cases this is NULL or zero but this does depend on the variable type. A VBA program will allow you to use this variable before you change the value from this default value. The problem is this default value may not make sense – and that you may not know what it is. I always think it’s safer to initialize variables just after you create them just to be on the safe side 3For non-object variables you can’t do it on the same line as the declaration but you can always do it just below the declaration.
Object variables are one example where you can declare and initialize them on a single line of code and this can give rise to some subtle problems. For this reason I have a best practise around declaring and initializing objects which is demonstrated below:
Public Sub DeclaringObjectsBestPractise()
' Bad Practise
Dim wksMyWorksheet1 As New Worksheet
' Much better!
Dim wksMyWorksheet2 As Worksheet
Set wksMyWorksheet2 = New Worksheet
End SubMaintaining the smallest possible variable scope is most easily done by only declaring variables as public or outside of a function/subroutine when absolutely necessary.
In terms of the Variant data type, there are some important exceptions to this rule. Until you understand what these are it is better not to use them. There will also be a separate future post dealing exclusively with the Variant data type.
Other Useful Resources
Chip Pearson has a few posts related to declaring and using variables in VBA. The most useful of these are:
Notes
- 1There is one important exception to this that I’m aware of related to integer and long data types in VBA. Details can be found on stack overflow here and here. In short there is little gain (and possibly a performance penalty) of using an integer rather than a long data type on more up to date machines.
- 2Some people will disagree. The usual argument is that it slows them down when they are coding. I would argue that the time saved in fixing the bugs in their code that results in not declaring variables more than offsets this.
- 3For non-object variables you can’t do it on the same line as the declaration but you can always do it just below the declaration
