Excel VBA Variables

What is a Variable?

A variable is a placeholder for data that may change (vary) according to the needs of the application. Most of us were introduced to variables in an algebra class where we learned to solve simple equations that contained them. An understanding of variables is necessary to understanding computer programming.

Variables in a programming language are classified according to the type of data they represent. Different programming languages accommodate different data types, but most of the data types are common across all languages.

Variables in computers have limitations based on the underlying computer hardware and software, and the characteristics and limitations also vary with programming languages. These limitations affect the accuracy of calculatios. So, for example, an integer in VBA is not exactly the same as an integer in a mathematics class. Its intended use is the same, but an integer in VBA cannot take on all integer values from negative to positive infinity. This is not possible for any modern computer.

Understanding how this all works on a computer begins with understanding how data is stored. The most basic unit of storage is a bit, which is a piece of the machine that takes on one of two states, kind of like a switch, either on or off. In one of the positions, it is treated as the integer one (1). In the other it is treated as zero (0). All data in a computer is stored as a combination of bits (0s and 1s) and the number of these physical units determines the capacity of data the computer can store.

Declaring Variables

VBA variables may be declared either implicitly or explicitly. Implicit variable declarations allow VBA to determine the variable type. Explicit declarations tell VBA how to interpret a given variable's type. As mentioned before, we can explicitly declare a variable with a Dim statement. Variables can also be declared explicitly by Private, Public, or Static statements.

Declaration Syntax

DeclarationName VariableName As VariableType

Dim X As Integer
Private X As Integer
Public X As Integer
Static X As Integer

Dim Statements

Dim statements are placed inside a routine and are local variables, meaning they are only visible within that routine. Once the routine is exited, other routines that may have called that one will have no knowledge of that variable or its properties, and may use that named variable as a different variable.

Dim declarations are explicit, and you may declare more than one variable within a statement.

Declaring variables with a Dim statement

Dim X As integer
Dim Y, Z As Long
Dim X As integer, Y, Z as Long

Although the latter statement is valid within VBA, it is best to stick to using separate dim statements for different variable types.

Explicit declaration of variables

Sub Name()
Dim X As integer

X = 10

Msgbox X

End Sub

This is an explicit declaration. If we leave off the Dim statement and just set the variable to some value, then we have declared the variable implicitly.

Implicit declaration of variables

Sub Name()

X = 10

Msgbox X

End Sub

By default, all implicit variables are of Variant type and inherit their data type from the data to which they are set, so when variables are left undeclared, VBA will treat them as variant. However, if a variable is declared without specifying its type, the default data type is Object.

A downside to using a variant variable type is that it might not catch all data errors in your dataset. Excel allows mixed data types in columns. If you import data from Excel that has, say, numeric and string data in the same column, a variant data variable will not care. All data will be valid for it. If you declare that variable to be something other than variant, this scenario will result in your being alerted to invalid data. A notable exception to this involves Currency data types. More on this later.

Using variables declared as variant

Using a variant data type will not alert when there is a change in data types it is set to.

Sub testVariant()
Dim X As Variant
Dim Y As string

X = 10

Msgbox X

Y = “Hello”

X = Y

Msgbox X

End Sub

In one statement, X is set to 10; in the second, it is set to a string variable containing the data “Hello.” The first Msgbox will display a value of 10. The second a value of “Hello.” No error messages will result.

Benefit of using explicit variable declarations

Using a declared data type that matches the data expected will usually alert when there is a change in data types it is set to. Again, the exception is currency data.

Sub testVariant()
Dim X As Long
Dim Y As string

X = 10

Msgbox X

Y = “Hello”

X = Y

Msgbox X

End Sub

The first Msgbox will display a value of 10. When the long variable X is set equal to the string variable Y, a Run-time error '13': Type Mismatch alert results.

Run-time error “13”: Type mismatch

Clicking the Debug button will cause VBA to point to the line of code in your routine that triggered the type-mismatch error alert.

VBA Code Debug Helper

In the above example, change “Hello” to “$3.50 ” and re-run the testVariant subroutine. VBA cleverly detects a “$” and checks to see if the additional characters match a currency data type. Here, it does. VBA can detect currency data embedded in a string variable.

VBA working with currency data in string variables

Sub testVariant()
Dim X As Currency
Dim Y As string

X = 10

Msgbox X

Y = “$3.50 ”

X = Y

Msgbox X

End Sub

The first Msgbox will display a value of 10, and the second 3.5. No error messages will be triggered. If we change the data type for X to Long, the value echoed will be 4. Since Long is an integer variable, the result is rounded to a whole number.

Next, add another “$,” and run the routine again.

Type mismatch error message

Sub testVariant()
Dim X As Long
Dim Y As string

Y = “$$3.50 ”

X = Y

Msgbox X

End Sub

A type-mismatch error message is triggered.

Private Declarations

Private declarations are module level, meaning they are visible to all routines within a module that call the routine where they are set, but not visible to routines in other modules. They are declared at the top of a module outside of any routine. Trying to run a routine in which a variable is declared private will cause a Compile Error: Invalid attribute in Sub or Function.

Create this module and run Test1. Its Msgbox will display “10 Hello.” Next run Test2. Its Msgbox displays “10” because it cannot see the Y variable declared in Test1.

Private declarations

Private X As Currency

Sub Test1()
Dim Y As String

X = 10

Y = “Hello”

MsgBox X & “ ” & Y

End Sub

Sub Test2()
Dim Y As String

MsgBox X & “ ” & Y

End Sub

Create this module and run Test1. Its Msgbox will display “10 Hello.” Next run Test2. Its Msgbox displays “10” because it cannot see the Y variable declared in Test1.

How to make private declarations in VBA

Public Declarations

Public declarations are project level, meaning that public variables are visible to all routines in all modules within a project. They are declared at the top of one of the modules outside of routines, similar to the way private declarations are made. Trying to run a routine in which a variable is declared public will cause a Compile Error: Invalid attribute in Sub or Function.

Static Declarations

Static variables are routine level and these declarations are placed inside routines.Trying to run a routine in a module where a variable is declared static outside any routines will cause a Compile Error: Invalid outside procedure. These variables keep their value while the project is running.

Static declarations

Create the following subroutine and function.

Sub testStatic()

MsgBox test2()

End Sub

Function test2() As Integer
Static i As Integer

i = i + 1

test2 = i

End Function

Now run testStatic. Four Msgboxes appear, the first displays 1, the second 2, the third 3, and the fourth 4. Re-run testStatic. The sequence of numbers in this second run is 5, 6, 7, and 8.