Excel Training VBA Lesson 7

A Variable is used to store temporary information that is used for execution within the Procedure, Module or Workbook.  Before we go into some detail of Variables, there are a few important rules that you must know about.

You can name variables with any valid name you wish. For Example you could name a variable "David" and then declare it as any one of the data types shown below. However, it is good practice to formalize some sort of naming convention. This way when reading back your code you can tell at a glance what data type the variable is. An example of this could be the system I use! If you were to declare a variable as a Boolean (shown in table below) I may use: bIsOpen I might then use this Boolean variable to check if a Workbook is open or not. The "b" stands for Boolean and the "IsOpen" will remind me that I am checking if something is open.

You may see code that uses letters only as variables, this is bad programming and should be avoided. Trying to read code that has loads of single letters only can (and usually does) cause grief. The only exception I have to this rule is I do use the letter i as an Integer variable type. This is because is is very widely recognized as such.

Variables can be declared as any one of the following data types:

Byte data type

A data type used to hold positive integer numbers ranging from 0 to 255. Byte variables are stored as single, unsigned 8-bit (1-byte) numbers.

Boolean data type

A data type with only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers.

Integer data type

A data type that holds integer variables stored as 2-byte whole numbers in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic.

Long data type

A 4-byte integer ranging in value from -2,147,483,648 to 2,147,483,647. The ampersand (&) type-declaration character represents a Long in Visual Basic.

Currency data type

A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic.

Single data type

A data type that stores single-precision floating-point variables as 32-bit (2-byte) floating-point numbers, ranging in value from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. The exclamation point (!) type-declaration character represents a Single in Visual Basic.

Double data type

A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic.

Date data type

A data type used to store dates and times as a real number. Date variables are stored as 64-bit (8-byte) numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time.

String data type

A data type consisting of a sequence of contiguous characters that represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. The String data type can store fixed-length strings ranging in length from 0 to approximately 63K characters and dynamic strings ranging in length from 0 to approximately 2 billion characters. The dollar sign ($) type-declaration character represents a String in Visual Basic.

Object data type

A data type that represents any Object reference. Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Variant data type A special data type that can contain numeric, string, or date data as well as the special values Empty and Null. The Variant data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text. The VarType function defines how the data in a Variant is treated. All variables become Variant data types if not explicitly declared as some other data type.

Why we use variables

Excel will still allow us to run our code without using variables, it is not a must! But having said this it is very bad programming to not use variables. You could quite easily just assign a value, string or whatever each time you need it, but it would mean:

1. Your code would become hard to follow (even for yourself)

2. Excel would constantly need to look for the value elsewhere.

3. Editing your formula would become awkward.

Let's use an example to highlight the above

Sub NoVariable()

Range("A1").Value = Range("B2").Value

Range("A2").Value = Range("B2").Value * 2

Range("A3").Value = Range("B2").Value * 4

Range("B2").Value = Range("B2").Value * 5

End Sub

• In the above code, Excel would need to retrieve the value from cell B2 five times. It would also mean if we had many other procedures using the same value ie B2, it would need to retrieve it's value even more times.

• There is a lot of editing to be done if we were to change from wanting B2 value to say, B5 value.

• It is messy code.

Let's now use a variable to store the value of cell B2!

Sub WithVariable()

Dim iMyValue as Integer

iMyValue = Range("B2").Value

Range("A1").Value = iMyValue

Range("A2").Value = iMyValue * 2

Range("A3").Value = iMyValue * 4

Range("B2").Value = iMyValue * 5

End Sub

• In the above code Excel only needs to retrieve the value of cell B2 once.

• To edit our code we only need to change it in one place.

• It is easier to read.

You might be thinking that there is no big difference in the above 2 examples, and to a point you would be correct. But what you must realize is, most VBA projects will consist of hundreds (if not thousands) of lines of code. They would also contain a lot more than one procedure. If you had 2 average size VBA projects, one using variables and one without, the one using variables would run far more efficiently!

Declaring Variables

To declare a variable we use the word "Dim" (short for Dimension) followed by our chosen variable name then the word "As" followed by the variable type. So a variable dimmed as a String could look like:

Dim sMyWord As String

You will notice that as soon as we type the word As, Excel will display a drop-down list of all variables.

• The default value for any Numeric type Variable is zero.  

• The default value for any String type variable is "" (empty text).  

• The default value for an Object type Variable is Nothing.  While the default value for an Object type Variable is Nothing, Excel will still reserve space in memory for it.

To assign a value to a Numeric or String type Variable, you simply use your Variable name, followed by the equals sign (=) and then the String or Numeric type.  eg:

Sub ParseValue()

Dim sMyWord as String

Dim iMyNumber as Integer

    sMyWord = Range("A1").Text

    iMyNumber = Range("A1").Value

End Sub

To assign an Object to an Object type variable you must use the key word "Set". eg:

Sub SetObJect()

Dim rMyCell as Range

    Set rMyCell = Range("A1")

End Sub

In the example immediately above, we have set the Object variable to the range A1.  So when we have finished using the Object Variable "rMyCell" it is a good idea to Set it back to it's default value of Nothing.  eg:

Sub SetObjectBack()

Dim rMyCell as Range

        <Some Code>

    Set rMyCell = Nothing

End Sub

This will mean Excel will not be reserving unnecessary memory.

In the first example above (Sub ParseValue()) we used 2 lines to declare our 2 variables ie

Dim sMyWord as String

Dim iMyNumber as Integer

We can, if we wish just use:

Dim sMyWord as String, iMyNumber as Integer

There is no big advantage to this, but you may find it easier.

Not Declaring Variables

There is a difference between using variables and correctly declaring them. You can if you wish not declare a variable and still use it to store a Value or Object. Unfortunately this comes at a price though! If you are using variables which have not been dimensioned Excel (by default) will store them as the Variant data type. This means that Excel will need to decide each time it (the variable) is assigned a value what data type is should be. The price for this is slower running of code! My advise is do it right and form the good habit early!

There is also another advantage to correctly declaring variables and that is Excel will constantly check to ensure you have spelt the variable name correctly. It does this by capitalizing the all lower case letters that are capitalized at the point it was dimensioned. Let's assume you you use:

Dim iMyNumber As Integer

At the top of your procedure. You then intend to use this variable in other parts of the procedure. Each time you type imynumber and then push the Space bar or Enter Excel will capitalize it for you ie imynumber will become iMyNumber.This is a very simple and easy way to ensure you have used the correct spelling.

While we are on this subject it is very good practice to type all code in lower case, because not only will Excel do this for variables but also for all Keywords!

There may be times when you will actually need to use a Variant data type as you cannot be certain what will be parsed to it, say from cell. It might be text, it maybe a very low or high number etc. In these circumstances you can use:

Dim vUnKnown As Variant

Or, simply:

Dim vUnKnown

Both are quite valid! The reason we do not have to explicitly declare a Variant is because the default for a variable is a Variant.

Scope and Lifetime of Variables

In most instances, when you declare a Variable, you would proceed the Variable name with the keyword "Dim".  The abbreviation "Dim" is short for Dimension.  Depending on where the Variable is declared, will set the scope of where the Variable can be used.  By this a Variable "Dimmed"  inside a Procedure can only be used within that Procedure.  eg;

Sub InsideProcedure ()

Dim sMyWord as String

    sMyWord = Range("A1").Text

    <any code>

End Sub

In the above example, the Variable "sMyWord" will store within itself whatever text is within range A1.  As it has been declared inside the Procedure it will only be available to this Procedure. This is known as declaring a Variable at Procedure level. If you try to access the Variable "sMyWord" from within another Procedure, you would encounter a "run time error". This is because as soon as Excel has reached the end of the procedure the variable has been declared in, it destroys its value and reverts back to it's default.

If we now declared the Variable "sMyWord" outside of the Procedure and at the very top of the Module (the Declaration section) it would be available to all Procedures within the same Module. eg:

Dim sMyWord as String

    sMyWord = Range("A1").Text

    <any code>

End Sub

Sub AnotherProcedure ()

    sMyWord = Range("A2").Text

    <any code>

End Sub

In the above example, we could write more Procedures within the same Module and use our Variable "sMyWord".  This is known as declaring the Variable at Module level.

Another thing to be aware of here is once any Variable has been declared at Module level, and has had a value parsed to it, it will retain that value until such time as it is changed via some code or the Workbook is closed.  An example of this may be as below:

Dim sMyWord As String

Sub InsideProcedure()

sMyWord = Range("A1").Text

'<any code>

End Sub

Sub AnotherProcedure()

MsgBox sMyWord

End Sub

If you ran the first Procedure "InsideProcedure" and then ran the second Procedure "AnotherProcedure", the Variable sMyWord would still be holding the value of Range A1. Until such time as we change it or close the Workbook.

The final level of declaration for a Variable is known as the Project level.  What this means is the Variable that is declared at this level will be available throughout all Procedures and Modules within the Project (Workbook).  To do this, we must declare the Variable at the Procedure level as above, but instead of using the key word "Dim", we use the word "Public".  An example of this is as below:

Sub InsideProcedure()

sMyWord = Range("A1").Text

'<any code>

End Sub

Sub AnotherProcedure()

MsgBox sMyWord

End Sub

We could now write a Procedure in any Module within the Project (Workbook) and either access the value of the Variable "sMyWord" or parse a new value to it. A point to note here is that the variable must be placed at the very top of a Standard Module. You could not place it in a Private Module and be able to access it in a Standard Module. You can however do the opposite, that is place it at the top of a Standard Module and access it from within a Private Module. We will look at Private Modules when we cover Events in a later lesson.

So as you can see, depending on where the Variable is declared, dictates where else we can use the variable without re-dimensioning it ("Dim").  Should you require further information on this, there is a quite a detailed description within the VBE Help under "Understanding the Lifetime of Variables".

While it is all too easy to not declare any Variables and let Excel decide for you remember, this comes at a cost (Excel by default assumes they are all of the Variant type). This mean all your Variables are stored at: 22 bytes (plus string length).  

To force yourself into this habit early ensure you have the words "Option Explicit" at the top of each Module. You can have Excel do this for you by going to Tools>Options and check the "Require Variable Declarations" If this is not on already I urge you to do so! This will force you to declare your variables correctly.

While it is certainly a good habit to declare all Variables correctly, don't fall into the trap of trying to assign the smallest data type to a Variable as you may need to parse a data type which is too big for your Variable to handle.  A common one that I have seen happen a lot is declaring a Variable as an Integer and then parsing a Row number to the Variable.  In this instance it is very important to realise that an Integer can only range from -32,768 to 32,767.  If you remember from your last lesson, there are 65,536 Rows in Excel so whenever using a Variable to hold a Row number Value declare it as a Long data type.

CONSTANTS

Constants are generally used to give a meaningful name to a Value.

An example of this may be a Project that you are working on constantly needs to refer to the Number 35 (for whatever reason).  Let's assume in this example the Number 35 refers to a person's age.  So rather than type the number 35 each time you require this person's age (for whatever reasons) you could declare a meaningful name such as "BillsAge" to the Value 35.  eg:

Const BillsAge as Integer = 35

In the above example, we could use the Constant "BillsAge"  throughout the Procedure, Module or Project.  Again, as with Variables this is dependent on where the Constant "BillsAge" is declared.  So, as with Variables we could make BillsAge available throughout our entire Project by declaring it at the top of a Module, and precede it with the word "Public".  eg:

Public Const BillsAge as Integer = 35

Constants are very similar to Variables with one important difference.  That is that after a Constant has been declared, it cannot be modified or assigned a new value. By this I mean we could not use:

BillsAge as Integer = 40  

Anywhere is our Project after we have already set BillsAge to 35 as a Constant.

EXERCISE

Go to Tools>Options and uncheck the "Require Variable Declarations"

Sub Exercise1a ()

iMyNumber = 100

    Range ("A1").Value = iMyNumber

End Sub

Sub Exercise1b ()

1. Open up the VBE and insert a normal Module and paste these two Procedures above in:      

2. Declare the Variable to it's correct data type

3. Declare it so it is only available within the Procedure Exercise1a.  That is at Procedure Level.

4. When you now run the Sub Exercise1a, the Range A1 of the active sheet should have a value of 100.  When you then run the Sub Exercise1b from the same sheet, the value of A1 will be nothing.

5. Now declare the Variable at Module level and run codes Exercise1a and Exercise1b again.

6. This time range A1 should hold the value 100 after both Procedures have run.

7. Now cut and paste Exercise1b into another Module, then go back to Exercise1a and declare the Variable at the Project level.  Again, run both codes.