Excel VBA Tutorial Part 2 - VBA Variables & Constants

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

For example, the constant "Pi" is used to refer to the value 3.14159265... The value of "Pi" does not change throughout the course of a 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, according to the type of goods being purchased.


Data Types

All variables and constants have a data type. Therefore, before working with constants and variables in VBA, it is useful to understand the data types that are available to you. 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, by default 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 users 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 a VBA option to force you to declare variables (see Option Explicit discussed below).

    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.l75, but of course it doesn't, because this value has been assigned to a different variable (the variable "VATRate"). 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 macro to crash.

    While this may initially seem to be a good reason not to declare variables, you should think about this more carefully - you actually need 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 will be done automatically via the 'Require Variable Declaration' option of your VBA editor.

To do this:

Once the 'Require Variable Declaration' option is selected, all new modules will have Option Explicit included at the top.


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 declaration applies), which depends on the location where you placed 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 3 different parts of 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 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 function or subroutine, 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).
Option Explicit
 
Function Total_Cost() As Double
   .
   .
   If Total_Cost > 0 Then
      Dim sVAT_Rate as Single
      .
      .
      .
   End If
   .
   .
End Function
If you declare "sVAT_Rate", within an 'If' block, then the scope of this variable will be the interior of the 'If' block (ie. up to the 'End If').

Therefore, if you attempt to use "sVAT_Rate" after you have exited the 'If' block, the VBA compiler will raise an error, as the variable has not been declared outside of this block (and the Option Explicit is in use).

The same scoping rules would apply to variables or constants declared within Loops.

In the above example, the module level variable has been declared using the 'Dim' keyword. However, it is possible that we may wish to declare variables that we want to share with other modules. This can be specified by using the keyword Public in the declaration, instead of 'Dim'. 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 use of the Public and Private keywords 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.
Go To Excel VBA Tutorial Part 3 - Excel Visual Basic Arrays

Return to the Excel VBA Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net