Data Types, DIM & SET

(Learn to use them correctly)

“Data types for variables.  Why are they important?  Why do we need them?”

These are very good questions.

To be honest, you can write perfectly acceptable programs and procedures without using data types.  The reason programmers don’t do this is because sooner or later, the lack of data type will byte you. (See what I did there?)

Data types provide several advantages when writing code.

  • Code runs faster
  • Memory is allocated more efficiently

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:


(click to enlarge)

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.Count

What 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.Count

This 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 = Selection

It’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

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE

5 Comments

  1. Jenka April 1, 2019 at 2:30 pm - Reply

    Hello Leila
    Thank you for your sharing on Excel and Vba.
    I would like to know: is it possible to use index + macth in vba on an array variable?
    example:
    I want to store in a two-dimensional array variable named myWeekdays. (this table is not stored in an Excel sheet)
    1, “Monday”
    2, “Tuesday”
    3, “Wednesday”
    4, “Thursday”
    5, “Friday”
    6, “Samadi”
    7 “Sunday”

    and later use the combination index + match to return the name of the day knowing the number of the day
    DayName = index (myWeekdays, match (date () myWeekdays.colonne (1), 0), 2)
    or
    DayName = index (myWeekdays.colonne (2) match (date () myWeekdays.colonne (1), 0))

    I hope my question is pretty clearly explained, and that soon I’ll see the answer in one of your beautiful videos on your channel.
    Thank you Leila and success for XelPlus

    • Bryon Smedley April 19, 2019 at 1:04 pm - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      The XelPlus Team

  2. Nagaraju Bhakthapuri April 10, 2019 at 4:07 am - Reply

    Very informative stuff.. Thanks for maintaining it for us Leila. You are leaving your mark in our business..

  3. Zaher June 12, 2019 at 3:17 pm - Reply

    Hi i have a problem when I want set table to list objects
    When the table have data no problem but when table not have data the vba show error 91 and go to code line of set variable to the table
    For example :
    Sub main ()
    Dim wbk as thisworkbook
    Dim wsh as wbk. Worksheets(“sheet1”)
    Dim z as listObjects

    Set z = wsh. Listobject(“table1”)

    End sub

    • Chris August 23, 2019 at 7:14 pm - Reply

      Hi Zaher, best would be to post your specific question with screenshots on an Excel forum – for example Microsoft’s Tech Community right here. The Excel Tech Community has some of the finest minds in the industry. With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able. I’m certain someone there can inform you of the best way to reach your solution.

Leave A Comment

Share This