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?
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.
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
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.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: