Declaring an integer variable VBA
Variables are used in VBA (and other programming languages) to store a value that you are planning to use later on in your code.
When writing a VBA code it is important that excel knows in advance what variables you will be using in your code so it can save enough memory for them.
When we say declare a variable, it really is just telling excel: this is what variables I will be using in this code, save memory for them.
Open your editor and in a new module follow the example below.
There are 3 elements to declaring a variable:
Using DIM to create a variable
First is the statement Dim- this is just a word that excel understands and It knows you are about to declare something:
Name the variable
Next we will write the name of our variable. This can be anything really but it does need to start with an alphabetical letter and it cannot have spaces (There is a few more restrictions on the name, please read the Microsoft manual for this).
It’s just a name for our variable and I will enter something that will help me remember what value I am storing in this variable. I will call mine my_integer_variable:
Determine the data type for the variable
Depending what you are storing in a variable, excel will save a different amount of memory about for it. So next we will tell excel what kind of value we are planning to store in this variable. In our case we will store an integer value:
That’s pretty much it, we have now declared a variable and we are ready to assign a value to it and use this variable:
If you run the code above the message: 2 will be displayed.
Please note: if you declare a variable as integer, you can only assign it an integer value.