When looking at other people’s VBA code in books or on the internet we often see the Dim statement being used to declare variables. It is possible to write valid VBA code without declaring variables, so why bother? It seems that Bill “Mr Excel” Jelen does not declare variables in his VBA code. Surely if it’s OK for Bill Jelen, it’s OK for us, right?
The default settings for variables
When variables are declared, they can be given a type. Types include Integers, Strings, Worksheets and many others. Here is an example of a variable declared as an Integer:
Dim someText as Integer
But it is possible to declare a variable without a type:
If we don’t define a variable type then what is it? Any variable, declared and undeclared, which does not have a type is automatically created as a Variant type.
The Variant variable type can be any type of variable. The Variant type can be an integer, a string, a workbook, or any of the other type of variable. And it can change as the variable changes, one moment it can be a string, then it can be changed to a workbook.
Sounds amazing, right – a variable which changes to be whatever we need it to be. So why don’t we just use the Variant variable type all the time? The biggest reasons not to use the Variant type are:
- Prevents errors being found – if a variable in your code needs to always be a whole number the Variant type will not create an error if it is anything else.
- Uses more computer memory, so the code takes longer to execute – which really isn’t what we want. There seems little point in being lazy in declaring variables only to slow ourselves down each time the code is executed. (However, given today’s processing power, I can’t believe that this will have much impact on speed . . . but I could be wrong).
An undeclared variable can only be a Variant type, so to be able to set the variable type, the variable must be declared.
Advantages of declared variables
Declaring variables enables us to avoid the issues caused by the Variant variable type. But, there are other advantages too:
Keeps code organized.
Normally variables are declared at the start of the code. This helps to keep the code organized. Once declared, we know every variable which exists in our code. We can easily look back to the start of the code to check the names we’ve declared.
Reducing typing errors.
My typing accuracy is not 100%. How accurate is yours? If we call a variable loopCounter the first time we use it, but call it lpppCounter the second time there will be an error when we run the code. Which is good, because we really do not want two variables.
Easier for others to follow your code.
Anybody looking at your code has more chance of understanding it if the variables are declared (and also commented).
Force variables to be declared
By inserting the text Option Explicit at the top of the VBA code it will ensue than any undeclared variables are shown as errors. We can set-up the Visual Basic Editor to automatically create the Option Explicit text for us.
From the Visual Basic Editor click Tools -> Options …
From the Options Window, select Require Variable Declaration, then click OK.
Reasons to not declare variables
If you watch the Excel TV interview with Bill Jelen at about 25 minutes he gives his reasons for not needing to declare variables. I’ve summarised them below:
- You don’t need to declare variables to make the VBA code work.
- We’re not programmers, we’re Excel users who use VBA to solve some problems.
- Computers have so much memory these days that we don’t need to worry about saving memory.
- At the start, we don’t know what variables we need, so just create them as you go.
- Adding comments to the code is a suitable method to describe the code, declaring variables doesn’t add information.
Should you declare variables?
Personally, I think the advantages of declaring variables are too strong. I am a human being, I make mistakes, therefore I appreciate any help in finding errors in my code. So, I would say, ‘Yes’ you should declare variables.
Get Excel news, tips & tricks straight to you inbox. Helping you to save time and achieve more with Excel.
You will also receive the Tab Hopper Add-in for FREE.