Contents
In this article, you will learn what constants and variables are in macros, where they can be used, and what is the main difference between different data types. It will also be revealed why constants are needed, if you can just write a variable and never change it.
Like other programming languages, data can be stored in variables or constants (both of which are also often referred to as data containers). This is the main difference between these concepts. The former may change depending on what happens in the program. In turn, constants are set once and do not change their value.
Constants can be useful if you need to use the same large value multiple times. Instead of copying the number, you can simply write the name of the constant. For example, you can use the constant “Pi” to store Pi, which is a constant value. It is very large, and each time it is quite difficult to write it or search and copy it. And so, it is enough to write two characters, and the environment automatically uses the desired number.
The Excel user needs to declare variables if he needs to change the value stored in them from time to time. For example, you can set a variable called sVAT_Rate, which will store the current VAT rate for the product. If it changes, you can quickly correct it. This is especially useful for those who do business in the United States, where some goods may not be subject to VAT at all (and this tax also differs from state to state).
Data Types
Each data container can be one of several types. Here is a table describing the standard types of processed information. There are many of them, and it may seem to a beginner initially that they repeat each other. But this is an illusory feeling. Read on to learn why specifying the correct data type is so important.
It is not recommended to use a data type that takes up more space in memory for small numbers. For example, for the number 1, it is sufficient to use the Byte type. This will have a positive effect on the performance of the executable module, especially on weak computers. But it is important not to go too far here. If you use a data type that is too compact, an oversized value may not fit in it.
Declaring Constants and Variables
Using a data container without first declaring it is strongly discouraged. Then a number of problems can arise, to avoid which it is necessary to write a few small lines of code with enumeration of variables or constants.
To declare a variable, the Dim statement is used. For example, like this:
Dim Variable_Name As Integer
Variable_Name is the name of the variable. Next, the As operator is written, indicating the data type. Instead of the strings “Variable_Name” and “Integer”, you can insert your own name and data type.
Constants can also be declared, but you must first specify their value. One of the options is:
Const iMaxCount = 5000
In fairness, in some cases you can do without declaring a variable, but in this case they will automatically be assigned the type Variant. However, this is not recommended for the following reasons:
- Variant is processed much more slowly, and if there are many such variables, information processing can be significantly slowed down on weak computers. It would seem that those seconds will decide? But if you have to write a large number of lines of code, and then also run it on weak computers (which are still sold, given that modern office suites require a lot of RAM), you can completely stop the work. There are cases when ill-conceived writing of macros led to the freezing of smartbooks that have a small amount of RAM and are not designed to perform complex tasks.
- Misprints in names are allowed, which can be prevented by using the Option Explicit statement, which allows you to find an undeclared variable, if one is found. This is an easy way to detect errors, since the slightest typo causes the interpreter to be unable to identify the variable. And if you turn on the variable declaration mode, the interpreter will simply not allow you to run the macro if data containers are found that were not declared at the very beginning of the module.
- Avoid errors caused by variable values not matching the data type. Normally, assigning a text value to an integer variable will throw an error. Yes, on the one hand, a generic type is assigned without a declaration, but if they are declared in advance, then random errors can be avoided.
Therefore, in spite of everything, it is highly recommended to declare all variables in Excel macros.
There is one more thing to keep in mind when declaring variables. It is possible to not assign any values to a variable when declaring it, but in this case it acquires a default value. For example:
- Lines are made empty.
- The numbers take on the value 0.
- Variables of type Boolean are initially considered false.
- The default date is December 30, 1899.
For example, you do not need to assign the value 0 to an integer variable if no value was previously specified. She already contains this number.
Option Explicit Statement
This statement allows you to declare all the variables that are used in the VBA code and determine the presence of any undeclared containers before the code is run. To use this feature, simply write a line of Option Explicit code at the very top of the macro code.
If you need to include this statement in your code every time, you can do so using a special setting in the VBA editor. To enable this option, you must:
- Go to the development environment along the path – Tools > Options.
- In the window that opens after this, open the Editor tab.
- And finally, check the box next to the Require Variable Declaration item.
Upon completion of these steps, click on the “OK” button.
That’s it, now when writing each new macro, this line will be inserted at the top of the code automatically.
Scope of Constants and Variables
Each variable or constant has only a limited scope. It depends where you declare it.
Suppose we have a function Total_Cost(), and it uses the variable sVAT_Rate. Depending on the position in the module, it will have a different scope:
| Option Explicit Dim sVAT_Rate As Single Function Total_Cost() As Double . . . End Function | If a variable is declared at the top of a module itself, it propagates throughout that module. That is, it can be read by every procedure. Moreover, if one of the procedures changed the value of the variable, then the next one will also read this corrected value. But in other modules this variable will still not be read. |
| Option Explicit Function Total_Cost() As Double Dim sVAT_Rate As Single . . . End Function | In this case, the variable is declared inside the procedure, and the interpreter will throw an error if it is used in another procedure. |
If you want the variable to be read by other modules, you must use the Public keyword instead of the Dim keyword. Similarly, you can limit the scope of a variable to only the current module by using the Public statement, which is written instead of the word Dim.
You can set the scope of constants in a similar way, but the keyword here is written together with the Const operator.
Here is a table with a good example of how it works with constants and variables.
| Option Explicit Public sVAT_Rate As Single Public Const iMax_Count = 5000 | In this example, you can see how the Public keyword is used to declare a variable, and what you need to write in the Visual Basic editor to declare a public constant. The scope of these value containers applies to all modules. |
| Option Explicit Private sVAT_Rate As Single Private Const iMax_Count = 5000 | Here, variables and constants are declared using the Private keyword. This means that they can only be seen within the current module, and procedures in other modules cannot use them. |
Why constants and variables are needed
The use of constants and variables allows you to increase the degree of understandability of the code. And if beginners in general do not have questions about why variables are needed, then there are many ambiguities regarding the need for constants. And this question seems, at first glance, quite logical. After all, you can declare a variable once and never change it again.
The answer turns out to be somewhere in the same plane as with regards to the use of data types that occupy a large space in memory. If we are dealing with a huge number of variables, we can accidentally change an existing container. If the user prescribes that a certain value will never change, then the environment will automatically control this.
This is especially important when the macro is written by several programmers. One may know that some variable should not change. And the other is not. If you specify the Const operator, another developer will know that this value does not change.
Or, if there is a constant with one name, and the variable has a different, but similar name. The developer can simply confuse them. For example, one variable that does not need to be changed is called Variable11, and another that can be edited is called Variable1. A person can automatically, when writing code, accidentally skip an extra unit and not notice it. As a result, the container for values will be changed, which should not be touched.
Or the developer himself may forget which variables he can touch and which he can’t. This often happens when the code is written for several weeks, and its size becomes large. During this time, it is very easy to forget even what this or that variable means.
Yes, you can do with comments in this situation, but isn’t it easier to specify the word Const?
Conclusions
Variables are an essential component of macro programming, which allow you to perform complex operations, from calculations to informing the user about certain events or specifying specific values in the cells of a spreadsheet.
Constants should be used if the developer knows for sure that the contents of these containers will not change in the future. It is recommended not to use variables instead, as it is possible to accidentally make a mistake.