Excel VBA Tutorial Part 2 - VBA Variables & Constants

In VBA, as in any programming language Variables and Constants are names that represent values. As suggested by the names, the values of Variables can change, while Constants tend to have fixed values.

For example, you might use the constant "Pi" to store the value 3.14159265... The value of "Pi" will not change throughout the course of your program, but it is useful to store this value in a Constant for ease of use.

Also, we might use a variable named "sVAT_Rate", to store the VAT Rate to be paid on purchased goods. The value of sVAT_Rate may vary, depending on the type of goods being purchased.

Data Types

All variables and constants have a data type. The following table shows the VBA data types, along with a description of each type and the range of possible values.


From the above table, it is clear that you can save on memory by using specific data types (eg. Integers rather than Longs or Singles rather than Doubles). However, if you are planning to use the 'smaller' data types, you must be sure that your code will not encounter larger values than can be handled by the data type.


Declaring Variables & Constants

Before using a variable or constant, you can declare it. This is done by adding a simple line of code to your macro, as follows.

To declare a variable:

Dim Variable_Name As Data_Type

Note that in the above line of code, Variable_Name should be replaced by your actual variable name and Data_Type should be replaced by one of the previously listed data types. For example:

Dim sVAT_Rate As Single
Dim i As Integer

Constants are declared in a similar way, except a constant should always be assigned a value when it is declared. Examples of the declaration of constants in VBA are:

Const iMaxCount = 5000
Const iMaxScore = 100

Excel does not force you to declare variables. By default, all variables in Excel will have the Variant type, and can be assigned a number or text.

Therefore, at any point during your program, you can use any variable name (even if it has not been declared), and Excel will assign the Variant type to it. However, this is not good programming practice for the following reasons:


  1. Memory & Calculation Speed: If you do not declare a variable to have a data type, it will, by default, have the Variant type. This takes up more memory than many of the other data types.

    While a few extra bytes per variable might not seem to be a lot of memory, it is not uncommon for programmers to have thousands of variables in their programs (especially when you start to use arrays). Therefore, the additional memory used to store Variants instead of Integers or Singles, can add up significantly.

    Variant data types also take more time to process than some of the other data types, so if you have thousands of unnecessary Variant data types, this can slow down your calculations.

  2. Prevention of 'Typo' Bugs: If you always declare your variables, then you can use the VBA Option Explicit (see below) to force you to declare variables.

    This will prevent you from introducing bugs into your code by accidentally typing a variable name incorrectly. For example, you might be using a variable called "sVAT_Rate" and, when assigning a value to this variable, you might accidentally type "VATRate = 0.175". From this point onwards, you are expecting your variable "sVAT_Rate" to have the value 0.175, but of course it doesn't. However, if you were using the VBA option to force you to declare all variables before using them, this error would be highlighted by the VBA compiler, as the variable "VATRate" would not have been declared.

  3. Highlighting Unexpected Data Values: If you declare a variable to have a specific data type, and you attempt to assign the wrong type of data to it, this will generate an error in your program which, if not handled within your code, can cause your programme to crash.

    While this may initially seem to be a good reason not to declare variables, it is actually useful to know as soon as possible if your variable receives an unexpected data type. Otherwise, if the program continues to run, you could end up with incorrect or unexpected results at a later time, when it is likely to be much more difficult to detect the causes of the errors.

    Also, it is possible that the macro may complete with incorrect results, and you may not notice the error at all - and continue to work with incorrect data!

    It is therefore preferable to detect the unexpected data type at an early stage and add code to handle this appropriately.

Therefore, it is recommended that you always declare all variables when programming in VBA.


Option Explicit

The option 'Explicit' forces you to declare all variables that you use in your VBA code, by highlighting any undeclared variables as errors during compilation (before the code will run). To use this option, simply type the line

Option Explicit

at the very top of your VBA file.

If you want to always include the option Explicit at the top of every new VBA module that you open up, this can be done automatically via the 'Require Variable Declaration' option of your VBA editor.

To do this:

Once the 'Require Variable Declaration' option is selected, the Option Explicit will automatically be inserted at the top of all new modules.


Scope of Variables and Constants

Each time you declare a variable or a constant, this only has a limited Scope (ie. a limited part of the program over which the variable exists), which depends on the position of your declaration.

For example, imagine you are using the variable "sVAT_Rate" within the function, "Total_Cost". The following table discusses the scope of "sVAT_Rate" when it is declared in 2 different places within the module:

Option Explicit
Dim sVAT_Rate As Single
 
Function Total_Cost() As Double
 
   .
   .
   .
 
End Function
If you declare "sVAT_Rate", at the top of your module file, then the scope of this variable is the whole of the module (ie. "sVAT_Rate" will be recognised throughout all procedures within the module).

Therefore, if you assign a value to "sVAT_Rate" in the Total_Cost function and then step into another function in the current module, the assigned value of "sVAT_Rate" will be remembered.

However, if you step into a function that resides in a different module and attempt to use the variable "sVAT_Rate", the variable will not be recognised.
Option Explicit

Function Total_Cost() As Double

   Dim sVAT_Rate As Single
   .
   .
   .

End Function
If you declare "sVAT_Rate", at the start of the Total_Cost function, the scope of this variable will be the whole of this function, (ie. "sVAT_Rate" will be recognised throughout the Total_Cost function, but not outside of this function).

Therefore, if you attempt to use "sVAT_Rate" in any other procedure, the VBA compiler will raise an error, as the variable has not been declared outside of the Total_Cost function (and the Option Explicit is in use).

In the above example, the module level variable has been declared using the 'Dim' keyword. However, it is possible that you may want to declare variables that can be shared with other modules. This can be specified by using the keyword Public in the declaration, instead of 'Dim'.

Note that, for a module-level variable, the 'Dim' keyword could also be replaced with the keyword Private to indicate that the scope of the variable is limited to the current module.

Constants can also use the 'Public' and 'Private' keywords, but in this case, the 'Public' or 'Private' keyword is used in addition to the 'Const' keyword (not instead of).

The following examples show the Public and Private keywords applied to variables and constants:

Option Explicit
Public sVAT_Rate As Single
Public Const iMax_Count = 5000
   .
   .
   .
This example shows the 'Public' keyword used to declare the variable, "sVAT_Rate", and the constant, "iMax_Count". The scope of these two declarations is the whole of the current project.

Therefore "sVAT_Rate" and "iMax_Count" can be accessed from any procedure in any module in the project.
Option Explicit
Private sVAT_Rate As Single
Private Const iMax_Count = 5000
   .
   .
   .
This example shows the 'Private' keyword used to declare the variable, "sVAT_Rate", and the constant, "iMax_Count". The scope of these two declarations is the current module.

Therefore "sVAT_Rate" and "iMax_Count" can be accessed from any procedure in the current module, but can not be accessed from procedures that reside in different modules.