Variables

Posted on by Tomasz Decker

Because VBA is a programming language, it shares common principles and elements with other languages. Ones of them are 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

Dim myVal As Integer

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

Without data type

If you declare value without data type

Dim myVal

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.

Dim first As Integer
Dim second As Double
Dim third As Single

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

Dim first As Integer, second As Double, third As Single

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

Dim first As Integer, second As Integer, third As Integer

The following code will be valid.

Dim first, second, third As Integer

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:

Public myVal As Integer
 
Sub mySub()
myVal = 45
myVar = myVal + 3
MsgBox myVal
 
End Sub

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

Option Explicit

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.

Post Views: 217