Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-11: VBA for Excel Variables
(Levels: Advanced)

In alphabetical order here are the 6 VBA words that you need to learn to work efficiently with variables:

As, Dim, Double, Redim, String, Variant

A variable is an object that you create and in which you can store text (STRING), dates (DATE), numbers (INTEGER, LONG, SIMPLE, DOUBLE) or almost anything else (VARIANT). Why should you use variable? The first good reason is to make your code dynamic, to avoid hard coding some values.

Hard Coding vs Dynamic Coding

You are hard coding when you write:
Workbooks.Open "MyFile.xls"

You are dynamically coding when you enter the name of the file in a cell (A1) and you write.
varWorkbook=Range("A1").Value
Workbooks.Open varWorkbook
At this point you or the user can change the value in cell A1 and open any workbook.

Counters

You will  create  variables that you will  use  as counters. In a new workbook enter a value in cell B1, Copy/Paste the following procedure in a module and try it (Go to "Tools/Macro/Macros" select "proTest" and click on "Run"):

Sub proTest()
Dim varCounter as Double
Dim varNumber as Double

          varNumber = Range("B1").Value

                    Range("A1").Select

          For varCounter = 1 to 25
                  Selection.Value=varNumber * 3
                  varNumber =Selection.Value
                  Selection.Offset(1,0).Select
        Next

End Sub
Try different values in cell B1.

You will create variables to count the number of rows, store the result in a variable and then do something as many time as there are rows. In a new workbook enter values in cells A1 to A10 then  Copy/Paste the following procedure in a module and try it (Go to "Tools/Macro/Macros" select "proTest" and click on "Run"):

Sub proTest()
Dim varCounter as Double
Dim varNbRows as Double

          Range("A1").select
                    varNbRows=Selection.currentRegion.Rows.Count

          For varCounter = 1 to varNbRows
                  Selection.Value=Selection.Value*2
                                      Selection.Offset(1,0).select
            Next

End Sub
Enter numbers in cells A1 to A:1000 or A1 to whatever row and try it again

I use variables in 99% of my procedures and you will do the same to reduce the number of hard coded values. Maintenance becomes so much simpler.

Option Explicit

When you open a module in the Visual Basic Editor  the sentence "Option Explicit" is usually  the first line of code at the top. This means that you MUST declare all the variables that you use in the procedure. This can be changed. In the Visual Basic Editor go to "Tools/Options/General" and uncheck "Require Variable Declaration" and from then on the "Option Explicit" sentence will not appear and you will not have to declare your variables. But BEWARE.

There are many advantages to this "Option Explicit" and many disadvantages if you don't use it:

Why should this option be activated? Let's say your are using a variable named "varMyVar" and that you write vayNyVar = 32. You have misspelled the name of the variable and you will go on thinking that "varMyVar" 's value is 32 but  IT IS NOT 32 is the value of "vayNyVar" and it is of no use to you. If the Option Explicit is activated the VBE will tell you that your are using a non-existent variable "varNyVar" . You will hence avoid a lot of possible errors.

If you write Rage("A1") instead of Range("A1") VBE will think that Rage("A1") is some variable that you are using and will not tell you about the typo.

So check the "Require Variable Declaration" in  the options and rest in peace.

Variable Declaration

Declaring variables is quite a simple thing. At the beginning of you procedure you tell VBA that some words will define your variables like:
Dim varThisVariable as String
means that varThisVariable is one of your variables and that it will contain text.

Declare all your variables at the beginning of your procedures. Always use the prefix "var" and give a name to your variables that means something. It will make your code easier to read (I hate when I see "i" or "j" or any other meaningless names used as variables). Use  one or many upper case letters within the name:
Dim varThisVariable as String

When you key in your procedure  don't capitalise any letter. If you have spelled the name right VBE will add the capital letters. So if you declare a variable named varCounter and you key in varcnter VBE will not capitalise the "C" telling you that the name is misspelled.

Large sets of data and excessive calculation time

When you have a large number of calculations to perform (many formulas) the calculation time can become a problem. You  can stop  the calculation process with Application.Calculation=xlManual/Activesheet.Calculate/Application.Calculation=xlAutomatic.

But at a certain point even this solution doesn't really help. Using the variable of the type VARIANT 1,000,000 cells are calculated in less than 5 seconds. Find the solution in:
"vba-example-variant-variable.xls" (1 of 25)

Public and Private Variables

When you declare a variable with the code Dim varMyVar as String it can only be used in the procedure  from where it is declared. When the procedure ends, the variable disappear.

If you want to use a variable in many procedures you must declare it at the top of a module this way:
Public  varMyVar as String
You must remember that this variable disappears only when the workbook is closed and that until then it carries the last value that you have stored in it. To clear the value you must use the code:
varMyVar = "" or varMyVar = Empty

I feel uncomfortable making a variable public so I use other approaches to carry the value of a variable from one procedure to the other.

Carrying the value of a variable from one procedure to the other

The easy way to carry the value of a variable from one procedure to the other is by storing this value in any cell of the workbook:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable1=Range("A3456").Value

You can also carry the variable itself from one procedure to the another procedure that you call from the first one. For example:

Sub proTest1()
Dim varMyVar As Double
        varMyVar = 3
        Call proTest2(varMyVar)
End Sub

Sub proTest2(varMyVar)
        varMyVar = varMyVar * 2
        Range("A1").Value = varMyVar
End Sub

The variable is declared only in the first procedure. A value is stored in it (3). A second procedure is then called with the variable as argument. After the execution of the two procedures, the value of range A1 should be 6.

Array Variables

An ARRAY VARIABLE is a multidimensional variable that you can size to your liking: myVariable(3) is a variable consisting of 4  different values, myVariable(5,10) is a variable consisting in 66 values, 6 rows and 11 columns and myVariable(5,10,10) can carry 726 values, etc...

When you declare an array variable, the first element bears the number "0" . The Variable varMyVariable(3) includes 4 elements from "0" to "3" . If like me you are uncomfortable with a variable varMyVariable(0), you can impose that the first element bears the number " 1" . In the general declaration (where you find the Option Explicit), write:
Option Base 1
In this situation, myVariable(3) contains only three elements.

You can carry the value of a variable from one procedure to the other by stocking this value in any cell in the workbook. ex:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable2=Range("A3456").Value

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple