Variables in Excel VBA

Integer  |  String  |  Double  |  Boolean

This chapter teaches you how to declare, initialize and display a variable in Excel VBA. Letting Excel VBA know you are using a variable is called declaring a variable. Initializing simply means assigning a beginning (initial) value to a variable.

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Integer

Integer variables are used to store whole numbers.

Dim x As Integer
x = 6
Range(“A1”).Value = x

Result:

Explanation: the first code line declares a variable with name x of type Integer. Next, we initialize x with value 6. Finally, we write the value of x to cell A1.

String

String variables are used to store text.

Code:

Dim book As String
book = “bible”
Range(“A1”).Value = book
Result:

Explanation: the first code line declares a variable with name book of type String. Next, we initialize book with the text bible. Always use apostrophes to initialize String variables. Finally, we write the text of the variable book to cell A1.

Double

A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma.

Code:

Dim x As Integer
x = 5.5
MsgBox “value is ” & x

Result:

But that is not the right value! We initialized the variable with value 5.5 and we get the value 6. What we need is a variable of type Double.

Code:

Dim x As Double
x = 5.5
MsgBox “value is ” & x

Result:

Note: Long variables have even larger capacity. Always use variables of the right type. As a result, errors are easier to find and your code will run faster.

Boolean

Use a Boolean variable to hold the value True or False.

Code:

Dim continue As Boolean
continue = True

If continue = True Then MsgBox “Boolean variables are cool”

Result:

Explanation: the first code line declares a variable with name continue of type Boolean. Next, we initialize continue with the value True. Finally, we use the Boolean variable to only display a MsgBox if the variable holds the value True.

Option Explicit

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.

For example, place a command button on your worksheet and add the following code lines:

Dim myVar As Integer
myVar = 10
Range(“A1”).Value = mVar

Result when you click the command button on the sheet:

Clearly, cell A1 does not contain the value 10. That is because we accidentally misspelled myVar. As a result, Excel VBA places the value of the undeclared, empty variable mVar into cell A1.

When using Option Explicit, the code lines above generate an error because we did not declare the variable mVar.

Result:

1. Click OK. Then Click on Reset (Stop) to stop the debugger.

2. Correct mVar so that it reads myVar.

Result when you click the command button on the sheet:

Now you know why you should always use Option Explicit at the start of your Excel VBA code. It avoids incorrectly typing the name of an existing variable.

Fortunately, you can instruct Excel VBA to automatically add Option Explicit.

1. In the Visual Basic Editor, click on Tools and then click on Options.

2. Check Require Variable Declaration.

Note: Option Explicit will not be added automatically to existing Excel files. Simply type in Option Explicit yourself if you want to use it.

Variable Scope

The scope of a variable in Excel VBA determines where that variable may be used. You determine the scope of a variable when you declare it. There are three scoping levels: procedure level, module level, and public module level.

Place a command button on your worksheet and add the following code lines:

1. Place the two procedures (a procedure is either a sub or a function) into a module. In the Visual Basic Editor, click Insert, Module. Add the following code lines:

2. Result when you click the command button on the sheet (call the two subs):

Explanation: the variable txt has scope procedure level because it is declared in the procedure (between Sub and End Sub). As a result, you can only use this variable in sub1. The variable txt cannot be used in sub2.

3. When you want a variable to be available to all procedures in a module, you are saying you want the variable to have module level scope. You need to declare the variable in the General Declarations section (at the top of the module). Slightly adjust the code as follows:

4. Result when you click the command button on the sheet:

Explanation: the variable txt can now be used in sub2. Module level is used interchangeably with private module level. That is because by default variables declared with the Dim statement in the General Declarations section are scoped as private. You can also scope a variable as public. Read on.

5. By using the Public keyword, your variable will be available to all procedures in all modules in a workbook. This is called public module level scope. Slightly adjust the code as follows:

Explanation: now you can create a new module and place a sub called sub3 into this module. Use the same code as sub2. Add sub3 to your command button code. When you click the command button on the worksheet, you will get three message boxes saying “Variable can only be used in this procedure” (see downloadable Excel file).

Life of Variables

Sometimes you want to retain the value of a variable in Excel VBA when a procedure ends. You can achieve this by using the Static keyword.

1. Place a command button on your worksheet and add the following code lines:

2. Result when you click the command button on the sheet:

3. Result when you click another time:

Explanation: Excel VBA destroys the variable when the procedure ends. Each time you click the command button on the sheet, Excel VBA creates the variable x again, adds the value 1 to it, and displays the result.

4. Now replace the keyword Dim with the keyword Static.

5. Result when you click the command button on the sheet:

6. Result when you click another time:

Conclusion: static variables retain their values, even when a procedure ends.

Note: static variables will be destroyed when you click the Reset (Stop) button or when you close your workbook.

Leave a Reply

Your email address will not be published.