VBA declare variables

Variables in VBA are used to store (temporary) values. Declaring variables means that memory space is reserved for these variables. This article explains how to declare variables, what types of variables there are, and the pros and cons of declaring variables. Declaring variables is not mandatory in VBA, but it can be set.

Types of variables

When declaring variables, you can indicate which type of variable this concerns. If this is not indicated or if no declaration is made, the variable is automatically assigned the type Variant. The following table lists the available variable types and their characteristics:

The Dim statement

Variables are usually declared with the Dim statement. For example: "Dim sLocation as String". sLocation is the name of the variable and String is the type of variable. Variable declarations are usually placed at the beginning of a procedure / function, but this is not mandatory. Below is an example of a declaration of variables:

Option Explicit

Sub Declaration()
    Dim s As String
    Dim i As Integer, j As Integer
    Dim k
    Dim m, n, o As Byte

Variable s is declared as a string and i and j as integers. No data type has been defined for variable k and therefore automatically becomes a variant. So 'Dim h' and 'Dim h as Variant' are in fact the same. It is possible to define several variables at the same time on 1 line, as in the second line: 'Dim i As Integer, j As Integer'. In the last line of this example, the variables m, n, and o are declared on one line. But because only o is followed by Byte, only o is a Byte. Variables m and n do not have a data type and are therefore of the Variant type.

When making a declaration, you can choose from the data types as listed in the table in the previous section. Only the data type Decimal is an outsider. This data type cannot be declared directly via a Dim statement. In order to declare a variable as Decimal, it must first be declared as Variant. After that this declaration must be converted to Decimal with CDec. So:

Dim DecVar As Variant
CDec (DecVar)

In the table from the previous section, some variables also have a short form for the declaration type. This dates back to the BASIC era and is hardly used anymore, but it still works. This short type declaration must be pasted directly after the name of the variable. For example variable 'var' can then be declared as an integer as follows: Dim var%

Naming

The name given to a variable can be determined at your own discretion, but this name must meet a number of requirements:

• Letters, numbers and underscores can be used, but the first character must be a letter. Spaces and periods are not allowed.
• VBA does not distinguish between uppercase and lowercase letters, however capital and small letters can be used to improve readability. For example CustomerNumber instead of customernumber.
• The name of a variable can be up to 254 characters long.
• The shorthand characters (%,!, #, $, ^ And & amp;) cannot be part of a variable name. These characters may only be used in the last position to be able to declare the type in abbreviated form (see previous paragraph).
• There are a number of reserved words that cannot be used as variable names. For example, a variable name cannot be a VBA command.

Option Explicit

In VBA it is not mandatory to declare variables, but this can be set by typing "Option Explicit" at the very top of the very first line of the program code sheet. If it says Option Explicit then, before executing the code on that program code sheet, it will be checked whether all used variables have been declared. If not, this error message will follow:

Option Explicit can be set to always be automatically placed above each program code sheet. This can be done in the VBA editor by selecting the menu option Tools → Options and check “Require Variable Declaration” on the editor tab. From that moment on, Option Explicit will automatically appear on new program code screens. Existing program code screens will not be modified.

Scope

The scope of a variable depends on the place of declaration. If a variable is declared within a procedure, then this variable can only be called within this procedure. When the procedure has ended, the variable loses its value and the reserved memory is released again. Variables that are only valid within a procedure can be declared with Dim or Static. Variables declared with Static do retain their value after the procedure has ended. Except when a procedure is ended with an End statement (with the exception of End Sub and End Function).

Variables can also be declared at the module level. This means that all procedures in this module can use this variable. This can be done by declaring these variables with Dim or Private at the very top of the module, directly below any Option Explicit or other Options. These variables are thus declared before the start of the first procedure. Although Dim can also be used in this case, it is better to choose Private. In this way the declaration immediately shows to which level it applies.

Variables can also be valid for the whole project (so all procedures and all modules). Then the variable must be declared with the statement Public. This statement must also be placed at the very top of a module. Sometimes Global is used instead of Public. Global can only be used in normal modules, while Public can be used in all modules (including class modules, forms, worksheets). Global is still available for compatibility reasons and has actually been completely replaced by Public. Although Public variables can therefore be used in all modules, a declaration in a standard module is preferred. The working method in other modules is slightly different. Suppose in the class module of sheet1 the variable bTest is declared as Public (Public bTest as Boolean). To use this variable on another worksheet, the following syntax must be used:

Worksheets("sheet1").bTest

It is allowed, but not recommended, to give variables at different levels the same name. In a procedure, a variable with a name can be declared, which also already exists as a Public variable. In such cases, the variable takes precedence at the lowest level.

Guidelines

Here are some guidelines that can be used when declaring variables:

1. For a data type, choose the type with the lowest memory usage. Memory usage is listed in the table at the beginning of this article.
2. Declare the variable at the lowest possible level. If a variable only needs to be valid within a procedure, declare it also procedure level and not at, for example, module level.
3. Use the Variant data type as little as possible.
4. If possible, reuse variables within a procedure. For example, if several counters are used in a procedure in different loops one after the other (i.e. no nested loops), then these variables can simply be used again. However, it may be necessary to reset the initial value.
5. Release the memory of object variables after use by means of the statement: Set objvar = Nothing

Declare pros and cons

As mentioned, declaration of variables is not mandatory, but this has a number of advantages:

1. Prevention of typos: if Option Explicit is used, then all used variables are checked to see if they are declared. If not, an error message will follow for the relevant variables. Suppose a variable is declared with the name iLength and iLengt is accidentally typed in somewhere in the code. This error is sometimes difficult to detect. If Option Explicit is used, you can quickly check whether all variables are spelled correctly by means of the menu option Debug → Compile VBAProject.
2. Better understanding of code: declaring variables requires active consideration of the values ​​that the variables can take in a code. This can lead to a better understanding of how the code works.
3. Increase speed: declaring variables allows code to run faster. It is often objected that this no longer plays a significant role with today's fast computers. And yes, it is true that with simple procedures the speed difference is not noticeable. But with complex procedures or procedures that require a lot of computing power, this can indeed yield speed gains. Try the 2 procedures below as an example. The second procedure is considerably faster than the first. How much faster is different for each computer, but the difference can easily be a few seconds.

Sub NoVarDeclaration()
    t = Now
    For i = 1 To 100000000
        j = j + 1
        If j > 30000 Then j = 1
    Next i
    MsgBox (Now - t) * 86400 '86400=seconds per day
End Sub

Sub WithVarDeclaration()
    Dim t As Double, i As Long, j As Integer
    t = CDbl(Now)
    For i = 1 To 100000000
        j = j + 1
        If j > 30000 Then j = 1
    Next i
    MsgBox (CDbl(Now) - t) * 86400 '86400=seconds per day
End Sub

There are also a few disadvantages of declaring variables, but in many cases these do not outweigh the advantages:

1. Extra typing work: all declarations have to be typed and that is some extra work. This also makes the code longer. But an advantage is that variables in the code can be entered faster and without errors. The variables appear in the list that can be called up with Ctrl + Space. If one or more letters of the variable have been entered, this list will be limited to the objects that start with the entered letters. If there is only one object left, it will be entered immediately.
2. Error messages due to incorrect declaration: an incorrect declaration of variables can lead to error messages. Example: store a too large a number in an integer, store a text in a number, etc. If declared correctly, this can also indicate a bug in the program: apparently values ​​are generated by the program that are not pre-provided. The program must then be adjusted accordingly.

Questions / suggestions

Hopefully this article has helped you understand all the ins and outs of declaring variables in VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.