Lesson 2 Excel VBA Variables

Lesson 3 Excel VBA Variables

Category: [General,Excel] Demo Available

VARIABLES

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.

A Variable name must start with a letter and not a number. Numbers can be included within the name, but not as the first character.
A Variable name can be no longer than 250 characters.
A Variable name cannot be the same as any one of Excel's key words. By this, I mean you cannot name a Variable with such names as Sheet, Worksheet etc.
All Variables must consist of one continuous string of characters only. You can separate words by either capitalising the first letter of each word, or by using the underscore characters if you prefer.

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:

Your code would become hard to follow (even for yourself)
Excel would constantly need to look for the value elsewhere.
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

Set rMyCell = Range("A1")

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

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

Sub OutsideProcedure ()

sMyWord = Range("A1").Text

End Sub

Sub AnotherProcedure ()

sMyWord = Range("A2").Text

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").Tex

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:

Public sMyWord As String

Sub InsideProcedure()

sMyWord = Range("A1").Text

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 ()

Range ("A1").Value = iMyNumber

End Sub

Open up the VBE and insert a normal Module and paste these two Procedures above in:
Declare the Variable to it's correct data type
Declare it so it is only available within the Procedure Exercise1a. That is at Procedure Level.
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.
Now declare the Variable at Module level and run codes Exercise1a and Exercise1b again.
This time range A1 should hold the value 100 after both Procedures have run.
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.

Any problems or questions, just ask

MESSAGE BOX FUNCTION

Excel has available some useful functions that allow us to either inform the user and/or collect information from the user. The most common of these is the Message box function. While this function is very informative it is also very easy to use. For instance, to display a simple message to a user you would only need to use this:

Sub MessageBox

MsgBox "Hello, my name is David."

End sub

This is using the message box in it's simplest form. Notice that to tell Excel we want a message box we use the abbreviation MsgBox. If we had other code after our MsgBox function, our procedure would pause until the user has acknowledged the message.

The syntax for the MsgBox function is:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The only part of the MsgBox function that is required is the Prompt, all other arguments are optional.

There is a lot of good detailed information on the MsgBox function within the Excel VBA help. Just type MsgBox in any module, select the word then push F1. I strongly suggest reading up on this function as it is very useful.

I will use this lesson to show you how you can determine which button they have clicked on a MsgBox should it have more than one button. Generally when you wish to 'capture' the return value of a function we need to enclose it within parenthesis. The MsgBox is no exception.

Let's imagine we wish to ask the user if they would like to save their file after a procedure has run. To do this we could use:

Sub WhichButton()
Dim iReply As Integer
'

    iReply = MsgBox("Would you like to save now?", _
             vbYesNo, "OzGrid Example")
                If iReply = vbYes Then ThisWorkbook.Save
End Sub

This is how you could return to VBA, the button clicked by the user (Yes or No). Notice how we have used a variable dimmed as a Integer. This is simply because the MsgBox function will return a whole numeric value (Integer) for the button clicked. Each of these Integers that are returned also have a Constant. In our example above, if the user clicks "Yes", the Integer returned is 6 and it's Constant equivalent is "VbYes". Should the user select "No" the value returned would be 7 and the Constant "VbNo". It really is that simple!

The values and Constant returned are show in the table below taken from the Excel help:

Constant	Value	Description
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

You may have noticed when you ran the example code, in the WhichButton procedure, that the default button when the MsgBox was shown was "Yes" (first button). If we want to change this we need to assign our "button" argument to a variable. We can also use this variable to store our message box type and make our message of the Critical type, or one of many other types.

Sub WhichButtonDefault()
Dim iReply As Byte, iType As Integer
'

    ' Define buttons argument.
      iType = vbYesNo + vbCritical + vbDefaultButton2

        iReply = MsgBox("Would you like to save now?", _
                        iType, "OzGrid Example")
            If iReply = vbYes Then ThisWorkbook.Save
End Sub

In the example above we have told Excel that we wish to make the "No" button our default. This was done with the use of: vbDefaultButton2. We also told Excel to make our message box Critical and this was done with: vbCritical. There are many optional arguments for the optional "buttons" argument and are shown in the table below taken from the Excel help.

Constant	Value	Description
vbOKOnly	0	Display OK button only.
vbOKCancel	1	Display OK and Cancel buttons.
vbAbortRetryIgnore	2	Display Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Display Yes, No, and Cancel buttons.
vbYesNo	4	Display Yes and No buttons.
vbRetryCancel	5	Display Retry and Cancel buttons.
vbCritical	16	Display Critical Message icon.
vbQuestion	32	Display Warning Query icon.
vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.
vbDefaultButton1	0	First button is default.
vbDefaultButton2	256	Second button is default.
vbDefaultButton3	512	Third button is default.
vbDefaultButton4	768	Fourth button is default.
vbApplicationModal	0	Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal	4096	System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton	16384	Adds Help button to the message box
VbMsgBoxSetForeground	65536	Specifies the message box window as the foreground window
vbMsgBoxRight	524288	Text is right aligned
vbMsgBoxRtlReading	1048576	Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

INPUT BOX

Another very handy function is the InputBox function. This will allow us to collect a reply from a user in the form of text. Its syntax is:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The only required argument is the Prompt and this would be the message you display to the user.

Try this example below:

Sub WhatsYourName()
Dim stReply As String
'

    stReply = InputBox(Prompt:="What is your name?", _
                Title:="OzGrid Example", Default:="Mine is David!")
       If stReply <> "" _
        And stReply <> "Mine is David!" Then
            MsgBox "Hello " & stReply
       End If

End Sub

In the above example we are asking the user to tell us their name. We have put a default message in the InputBox telling them our name. As with the message box function, we are using a variable to determine their reply. The difference is though, the InputBox returns a String and not an Integer! If the user decides to Cancel the InputBox it would return empty text (""), if they simply clicked Ok without changing our Default message it would return: "Mine is David!". Both of these possibilities are dealt with in our If And combination.

There is also another type of InputBox, which is a member of the Application Object. This InputBox will allow us to specify what type of value to pass back to our InputBox. The syntax for this InputBox is:

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

Again the only required argument is the Prompt. The important part to note with this type of InputBox is that you must precede it with an Application Object, which in Excel's case in the actual word Application

The return value type is specified by the Type argument and is shown in the table below taken from the Excel help.:

Value	Meaning
0	A formula
1	A number
2	Text (a string)
4	A logical value (True or False)
8	A cell reference, as a Range object
16	An error value, such as #N/A
64	An array of values

You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

So to use the InputBox to return a range Object you could use:

Sub Demo()

Dim rMyRange As Range

On Error Resume Next
    Set rMyRange = Application.InputBox _
    (Prompt:="Select any range", Title:="OzGrid Example", Type:=8)
        rMyRange.Select
On Error GoTo 0
End Sub

Notice the use of On Error Resume Next this is used to prevent any possible run time errors that would occur if the user clicks Ok (without select a range) or Cancel. If they did, our Set Statement would not be valid. I have then used the On Error GoTo 0 to reset all run time errors. The use of these two Statements are discussed in a later lesson.

As you can see, with the use of Excels built in function such as MsgBox and InputBox, we are able to communicate with the user. More importantly, we can collect their replies and answers.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.