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 generally 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.

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.

Data Type	Size in Memory	Description	Range of Values
Byte	1 byte	Represents an unsigned (non-negative) number - often used for binary data	0 to 255
Boolean	2 bytes	A simple True or False value	True or False
Integer	2 bytes	Integer (no decimals)	-32,768 to +32,767
Long	4 bytes	Long Integer (no decimals)	-2,147,483,648 to +2,147,483,647
Single	4 bytes	Single Precision Floating Point Number	-3.4e38 to +3.4e38
Double	8 bytes	Double Precision Floating Point Number	-1.8e308 to +1.8e308
Currency	8 bytes	A Floating Point Number with a fixed number of decimal places	-922,337,203,685,477.5808 to +922,337,203,685,477.5807
Date	8 bytes	Date & Time - The Date type is represented internally by a floating point number. The integer part of the number represents the date, and the decimal portion represents the time.	1st January 100 to 31st December 9999
Object	4 bytes	A reference to an object	Any Object Reference
String	varies	Holds a series of characters. The String type can be defined to have a fixed or a variable length, although it is most commonly defined to have a variable length	Fixed - Up to 65,500 characters Variable - Up to approx. 2 billion characters
Variant	varies	Can hold Dates, Floating Point Numbers or Strings of Characters, and should therefore be used when you are not sure what type of data to expect.	Number - same as Double Type String - same as String Type

From the above table, it is clear that you can save on memory by using specific data types (e.g. 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_Typeshould be replaced by one of the above 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

Reasons to Declare Variables

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, it is not good programming practice not to declare variables 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 had been using the VBA option Explicit 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.

Due to the reasons listed above, it is recommended that you always declare all variables when programming in VBA, and use the Option Explicit (see below) to ensure that all variables in the code are declared.

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 activate this:

·         In the Visual Basic Editor, select Tools→Options...;

·         Ensure the Editor tab is selected;

·         Check the box next to the option Require Variable Declaration and click OK.

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

Scope of Variables and Constants

Each time you declare a variable or a constant, this only has a limited Scope (i.e. a limited part of the program over which the variable exists). This 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 (i.e. "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, (i.e. "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.

Click Here to Main Menu