How to declare variables in VBA?

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.

Variables naming

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.

Trust me.

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.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *