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.
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|
|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|
|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|
|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|
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|
String - same as
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 chosen data type.
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 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
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:
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.
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
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:
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.
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 ExplicitFunction Total_Cost() As Double
Dim sVAT_Rate As Single
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 ExplicitFunction Total_Cost() As Double
Dim sVAT_Rate As SingleEnd 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:
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.
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.