Variables and Constants in VBA | Option Explicit Use in VBA | Data Types in Excel VBA | Data type 2024

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

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

At the same time, we can use the sVAT_Rate variable to store the VAT rate on the purchased goods. The value of the sVAT_Rate variable can change depending on what kind of product is purchased.

Data types

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

Obviously, using the above table and choosing the right data type, you can use memory more economically (for example, choose the Integer data type instead of Long or Single instead of 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: There is another very important point worth mentioning when talking about variables in VBA. If we declare a variable, but do not assign any value to it, then it is initialized with the default value:

• Text strings – are initialized with empty strings;
• Numbers – value 0;
• Variables of type Boolean – False;
• Dates – December 30, 1899.

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

Dim variable Name As Data_Type

In the above line of code, VariableName is the name of the variable that will be used in the code, and DataType is one of the data types from the table shown earlier in this article. For example:

Dim sVAT_Rate As Single

Dim i As Integer

Constants are declared in a similar way, 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 be of type Variant and can accept both numeric and text values.

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

1. Memory usage and computation speed. If you do not declare a variable with an indication of the data type, then the Varianttype will be set for it by default . This data type uses more memory than other data types. It would seem that a few extra bytes per variable is not that much, but in practice, the programs you write can have thousands of variables (especially when working with arrays). Therefore, the extra memory used by Variant variables , compared to Integer or Single variables , can add up to a significant amount, and operations on Variant variables are performed much slower than with variables of other types, respectively, an extra thousand of type variables Variant can slow down computations significantly.
2. Preventing typos in variable names. If all variables are declared, then you can use the VBA – Option Explicitstatement (which will be discussed later) to identify all undeclared variables, thus avoiding the appearance of an error in the program as a result of an incorrectly spelled variable name. For example, using a variable named sVAT_Rate in your code , you can make a typo and, assigning a value to this variable, write: “VATRate = 0.175”. It is expected that from now on, the sVAT_Rate variable should contain a value of 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 VATRate variable among those announced.
3. Highlighting values ​​that do not match the declared type of the variable.If you declare a variable of a certain type and try to assign data of a different type to it, an error will appear, without fixing which, you can get the program crashing. 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 should have received – 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 advisable to detect the wrong data type and fix 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

The Option Explicit statement forces the declaration of all variables that will be used in the VBA code, and during compilation, it marks all undeclared variables as errors (before running the code). This statement is not difficult to apply – just write this line at the very top of the VBA file:

Option Explicit

If you want to always insert Option Explicit at the beginning of every new VBA module you create, you can do that automatically. To do this, you need to enable the Require Variable Declaration parameter in the VBA editor settings.

This is done like this:

• From the Visual Basic Editor menu click Tools> Options
• In the dialog box that appears, open the Editortab
• Check the box next to Require Variable Declarationand click OK

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

Now More About: VBA Code Formatting

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 variable or constant declaration was made. Take, for example, the sVAT_Rate variable used in the Total_Cost function . The following table shows two options for the scope of the sVAT_Rate variable declared at two different positions in the module:

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

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

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

The following examples demonstrate the use of the Public and Private keywords when applied to variables and constants.