It is very good approach to declare variables in VBA. Of course You don’t have too, but I would treat that as privilege rather than obligation. This is so basic thing, but really important and can prevent from lots of mistakes. That’s why I want to show You how to proper declare variables in VBA.
Default type of variable
In VBA, if You don’t declare the type of variable, the default type is Variant. Which is the heaviest of all types. So every time You forgot to declare or just don’t do this, You use much higher amount of memory than it is really required.
To the point, let me show You the numbers.
For example: instead 4bytes for Long variable, 4 bytes for Object or (10 bytes + string length) for String, Variant variable use 16 bytes in case of numbers or even (22 bytes + string length) if there are characters as well!
Taking into consideration, that there are tons of variables in complex code, it makes a quite big difference in overall storage size. And it has its transition into overall performance of your code.
Declare multiple variables at once
To declare variable first of all You need to use Dim, which stands for dimension… or at least I read somewhere about that. Then You put the name of variable. Next thing is as and after that the type of variable.
So in general it looks like this:
Dim NameOfVariable as TypeOfVariable Dim personName as String
To declare more than one variable You can go like this:
Dim personName as String Dim personAge as Integer Dim personBirthday as Date
There is an option to write it in 1 line, but You need to be careful.
Dim personName as String, personAge as Integer, personBirthday as Date
Even if your variables are the same type, You can’t forgot to write its types.
Dim personName as String, personCity as String, personHobby as String
If You type like this:
Dim personName, personCity, personHobby as String
the only String type variable is personHobby. The two previous ones – personName & personCity – are the Variant type in this case.
It is really important to have a good naming convention for your variables. Try to use as describtive names as it is possible. For example, when it comes to create variables for strings like first name, last name and home city of the user, instead of:
Dim stringVar1 as String Dim stringVar2 as String Dim stringVar3 as String
It would be much better to use names like:
Dim firstName as String Dim lastName as String Dim homeCity as String
In this case You don’t have to comment your code at all or only a little bit, because reading the name of the variable You already see what that stands for.
You will be thankful to yourself when You will return to the project from couple of months earlier. Less time to remind what was all about.
And of course the same goes with the other parts of code like subs, functions, modules or classes.
Use & name your variables!
So that was all for now. Those 2 things I really wanted to share with You.
Remember: always declare every variable You use, remove those not used and create descriptive names for them to minimize the amount of comments. The shorter code the easier to read and use.