Variables and constants in VBA

In VBA, as in any other programming language, variables and constants are used to store any values. As the name implies, variables can change, while constants store fixed values.

For example, a constant Pi stores the value 3,14159265… The number “Pi” will not change during the execution of the program, but it is still more convenient to store such a value as a constant.

At the same time, we can use the variable sVAT_Rate to store the VAT rate on purchased goods. Variable value sVAT_Rate may vary depending on the product purchased.

Data Types

All variables and constants are of a particular data type. The table below lists the data types used in VBA with a description and range of possible values:

Obviously, using the table above and choosing the right data type, you can use memory more economically (for example, choose the data type Integer instead Long or single instead Double). However, when using more compact data types, you need to be careful that your code does not try to fit disproportionately large values ​​into them.

Declaring Variables and Constants

Translator’s Note: Speaking about variables in VBA, it is worth mentioning one more very important point. If we declare a variable but do not assign any value to it, then it is initialized with a default value:

• text strings are initialized with empty strings;

• numbers — value 0;

• type variables Boolean — False;

• dates – December 30, 1899.

Before a variable or constant can be used, it must be declared. To do this, add the following simple line of code to the macro:

Dim Имя_Переменной As Тип_Данных

In the above line of code Variable_name is the name of the variable that will be used in the code, and Data_Type is one of the data types from the table given a little earlier in this article. For example:

Dim sVAT_Rate As Single  Dim i As Integer

Constants are declared similarly, but when declaring constants, their value must be immediately indicated. For example, like this:

Const iMaxCount = 5000  Const iMaxScore = 100

It is not necessary to declare variables in Excel. By default, all entered but not declared variables in Excel will have the type Variant and will be able to accept both numeric and text value.

Thus, the programmer can use the new variable at any time (even if it has not been declared), and Excel will treat it as a variable of type Variant. However, there are several reasons why this should not be done:

1. Memory usage and computational speed. If you do not declare a variable with an indication of the data type, then by default it will be set to the type Variant. This data type uses more memory than other data types. A few extra bytes per variable might not sound like much, but in practice, programs can have thousands of variables (especially when working with arrays). Therefore, the extra memory used by variables like Variant, compared to variables of type Integer or single, can add up to a significant amount. In addition, operations with variables of type Variant are executed much more slowly than with variables of other types, respectively, an extra thousand variables of type Variant can significantly slow down calculations.
2. Prevention of typos in variable names. If all variables are declared, then the VBA statement can be used − Option Explicit (we will talk about it later) in order to identify all undeclared variables. This eliminates the appearance of an error in the program as a result of an incorrectly written variable name. For example, using a variable named sVAT_Rate, you can make a typo and, assigning a value to this variable, write: “VATRate = 0,175”. It is expected that from now on, the variable sVAT_Rate should contain the value 0,175 – but of course it doesn’t. If the mode of mandatory declaration of all used variables is enabled, then the VBA compiler will immediately indicate an error, since it will not find the variable VATRate among those announced.
3. Highlighting values ​​that do not match the declared type of a variable. If you declare a variable of a certain type and try to assign data of a different type to it, you will get an error, which, if left uncorrected, can cause the program to crash. At first glance, this may seem like a good reason not to declare variables, but in fact, than before it turns out that one of the variables received the wrong data that it was supposed to receive – so much the better! Otherwise, if the program continues to run, the results may be incorrect and unexpected, and it will be much more difficult to find the cause of the errors. It is also possible that the macro will be “successfully” executed. As a result, the error will go unnoticed and work will continue with incorrect data!

In this regard, it is desirable to detect an incorrect data type and correct such errors in the code as early as possible. For these reasons, it is recommended that you declare all variables when writing a VBA macro.

Option Explicit

Operator Option Explicit causes all variables that will be used in VBA code to be declared, and flags all undeclared variables as errors during compilation (before code execution starts). Applying this operator is not difficult – just write this line at the very top of the VBA file:

Option Explicit

If you want to always insert Option Explicit to the top of each new VBA module created, this can be done automatically. To do this, you need to enable the option Require Variable Declaration in the VBA editor settings.

This is done like this:

• From the Visual Basic Editor menu, click tools > Options
• In the dialog that appears, open the tab Editor
• Check the box Require Variable Declaration and press OK

When enabled, the string Option Explicit will be automatically inserted at the beginning of each new module created.

Scope of Variables and Constants

Each declared variable or constant has its own limited scope, that is, a limited part of the program in which this variable exists. The scope depends on where the declaration of the variable or constant was made. Take, for example, the variable sVAT_Rate, which is used in the function Total_Cost. The following table discusses two options for variable scoping sVAT_Ratedeclared in two different positions in the module:

Option Explicit    Dim sVAT_Rate As Single    Function Total_Cost() As Double      ...      End Function

Option Explicit    Function Total_Cost() As Double    Dim sVAT_Rate As Single      ...      End Function

In the example shown above, the variable is declared at the module level with the keyword Dim. However, it may be necessary that declared variables can be used in other modules. In such cases, to declare a variable instead of a keyword Dim keyword must be used Public.

By the way, in order to declare a variable at the module level, instead of the keyword Dim keyword can be used Private, which indicates that this variable is intended for use only in the current module.

You can also use keywords to declare constants. Public и Private, but not instead of the keyword Const, along with it.

The following examples show the use of keywords Public и Private applied to variables and constants.

Option Explicit    Public sVAT_Rate As Single    Public Const iMax_Count = 5000    ...    

Option Explicit    Private sVAT_Rate As Single    Private Const iMax_Count = 5000    ...