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.
Spreadsheet and Python enthusiast.
