The advantage of VBA is that it can retain vast amounts of information in memory. By storing and then manipulating that memory, we can produce the desired outcome in our programs.
In case you are not aware, a variable is a named place in memory that will store information. When we declare a variable, we give the variable a name. If we don’t tell the system what type of information the variable will hold, the system reserves a pre-defined amount of memory in anticipation of the variable’s use.
Imagine you must store water, but you don’t know what the water will be used for. Will you be storing water for a drink, a bath, or for swimming? Since you don’t know, you’ll set enough space aside for the “worst case” scenario. Setting aside enough space to store a swimming pool’s worth of water when the user is only going to need a glass worth to quench their thirst is an inefficient use of space.
Likewise, if you are going to store a single-digit number, but you reserve enough space to store a sentence worth of letters, is also an inefficient use of memory.
Declaring variables without data types
If you declare a variable but fail to include any information for its data type, VBA will by default assign the variable a data type called Variant.
The Variant data type changes its size based on the data placed inside. This sounds like the ideal data type, but in practice it ends up being the worst in terms of performance.
The reason it performs so poorly is due to the constant examination of the data being placed in the variable and adjusting its size to accommodate the data. What at first appears to be a great feature turns out being its greatest drawback.
When a variable is properly typed (classified), it simply accepts the data and stores it without question. Memory is allocated more efficiently, and code executes faster when the variables do not have to examine the data and make decisions about storage size.
If a variable is declared as a Variant, the system will reserve 16 bytes of memory when storing numbers and 22 bytes for text (plus the memory to store the text itself.)
Data types used when declaring variables
The best practice is to reserve only enough space in memory to hold what is placed in the variable. If you know you are going to hold someone’s age, you can store the number in a Byte. Since a Byte can hold a value between 0 (zero) and 255, this would prove adequate since people rarely live beyond 255 years. (smirk)
A Byte data type only consumes a single byte in memory. This makes for a very small memory footprint when storing numbers between 0 and 255. If you were dealing with an array of ages, and the array’s size ranges in the hundreds of thousands, this would consume only 1/16th the memory of a Variant data type.
Just as the Byte data type has a fixed range, all data types have a fixed range. Consider the table below:
You can see from the table above, the smaller the memory used, the smaller the available range. Since a Byte only consumes 1 byte of memory, it can only hold 256 different things (but not all at the same time.) Where as a Currency type consumes 8 bytes and has a range of over 1.8 quintillion different things (again, not at the same time.)
Improper data type declarations
When using data types, it’s important to anticipate the largest value you may wish to retain. If you were to store page numbers in a variable declared with a Byte data type, and then tried to store a reference to page 300, you would encounter an overflow error as demonstrated in the following error message.
To remedy this issue, you could declare the variable as an Integer data type. This would only consume 2 bytes and have the benefit of storing page numbers up to 32,767.
2 bytes for every Integer is still 1/8th the memory usage of a Variant declaration.
If you are looping through many rows in a spreadsheet, a safe data type to use for storing row numbers is Long. The Long data type is more than capable of storing even the largest worksheet row number. You could use the Integer data type, but you would be limited to using Excel 95 or older. If you’re still using Excel 95, I recommend upgrading. There are many new features that I’m certain you would enjoy.
Boolean is useful for storing the results of “true/false” type operations.
Double is useful when you need to store values containing a high degree of fractional precision.
String is used for storing text.
Object is for storing ranges; such as application, workbook, worksheet, and range.
It’s not to say that you should never use Variant as a data type. If you are storing data that changes from one type to another (i.e. Boolean on moment and text message the next moment), a Variant data typed variable may prove beneficial.
Why declare variables?
Declaring a variable give VBA a head’s up as to your intentions of storing information and reserves a place in memory prior to data storage time.
How are variables declared?
Variables are declared using the DIM keyword.
The name you give a variable is completely up to you. There are a few restrictions you must keep in mind when naming a variable.
- The variable name can have no more than 255 characters.
- The variable name can contain letters and numbers but CANNOT start with a number; it must start with a letter.
- Spaces are not allowed in the name, but it is common to use an “_” (underscore) character to simulate a space.
- Certain special characters are not allowed, such as period, !, @, &, $, and #.
- You cannot use a name that already refers to a function, statement, method, or intrinsic constant.
- You cannot declare two variables with the same name in the same scope level.
As a best practice, it is recommended that the name you assign should be as short as possible, while remaining understandable by the (human) reader of the code. As an example:
- “Federal_Income_Tax_Rate_2019” is perfectly understandable but far to “wordy”.
- “FITR19” is short but is not intuitive in any way.
- “TaxRate2019” is a nice compromise. It remains relative short yet understandable.
Below are examples of variable being declared at the beginning of a VBA procedure.
Assigning data to a variable
Placing data into a variable is accomplished by way of the LET and SET statements.
If we wanted to place the number of rows used in a range in a variable named “LastRow”, the statement would appear as follows.
Let LastRow = Rows.CountWhat we are doing is “letting” the variable “LastRow” hold a number derived by the Rows.Count operation.
In practical use, the statement would most like appear as follows.
LastRow = Rows.CountThis is because the use of the keyword LET is optional, and most programmers elect to not include it in their code.
What about object variables?
Common objects in Excel VBA are the workbook object, the worksheet object, and the range object.
Declaring an object variable looks like the following.
To assign a value to an object requires the use of the SET statement.
Examples of assigning values to objects are as follows:
Set NewBook = ActiveWorkbook
Set NewSheet = ActiveSheet
Set UsedRange = SelectionIt’s easy to forget to use the SET statement when assigning values to object variables. Always remember: if the variable being populated looks like an object in Excel, use the SET statement.
If you fail to use the SET statement when it is required, you will encounter the following error.
Additional Resources
This tutorial is part of the VBA course offered on my website XELPlus.com. If you would be interested in learning more about VBA, click the link below.
https://courses.xelplus.com/p/excel-vba-excel-macros
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
