Variables

Because VBA is a programming language, it shares common principles and elements with other languages. One of them is variables.

A variable is a reserved place in a computer memory that can be used by a program. Excel stores there the value assigned to that variable.

Declaring Variables

When you declare the variable, you can define what kind of data you want to store there. For example:

With data type

It means that you reserve 2 bytes for the integer value.

Without data type

If you declare value without data type

VBA will automatically create its default type called Variant. This data type can be very handy because it changes its type depending on what you actually do with the variable. For example, if you enter „56”, it can behave as a number or as a text string.

Declaring multiple variables at once

When you want to declare multiple variables, you can create each declaration in a new line.

But there is another faster method where you need only one line of code.

If all of your variables are of the same type, you still have to declare each variable separately.

The following code will be valid.

But only the third variable will be treated as an integer. The first and second variables will be treated as Variants.

Option Explicit

It’s a good practice to declare all your variables at the beginning of your code, not only for practical reasons but also because the macro will run faster. If you don’t do this it is easier to make a mistake.

Look at this simple code:

We expect from the myVal variable to display the value 48. But the result is:

That because there is a mistake in the code. Instead of myVal = myVal + 3 there is myVar = myVal + 3. It can be easily overlooked and the result will be wrong.

If you declare the

at the very beginning of your code, Excel will require from you to declare each variable you use. Now when you execute your code you get the error informing you that the variable is not defined.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.