VBA Variable Types in Excel

It is very important to know how to effectively understand and use VBA variable types in Excel, for your code to work properly.

In this article, we will explain what a variable is, how to use them in your VBA code, and explore the different Excel VBA data types.

What is a variable?

A variable is a location in your computer's memory that you define and then use to store values. This storage is temporary and the values are cleared when your macro ends.

You can name a variable something meaningful and specify the type of data that it will store. You can then access and change that variable's value as many times as you want in your Excel VBA code.

A constant is similar, however once initialized, the value it stores cannot be changed in your VBA code.

Why variables are used

Variables are critical to writing efficient VBA code. You can assign values to a variable and then test, change, and write from that variable during the macros runtime.

Storing values in physical containers such as cells on a worksheet is not efficient if you need to read, write, and change that value multiple times.

Accessing values stored on a worksheet is slow and is also clunky to code. Variables are stored in your computer's memory, so they are accessed a lot faster than it would be to check a cell on a worksheet.

You can also assign a meaningful name when declaring your variables. This makes it a lot more convenient to reference in your VBA code than the cell on a worksheet.

Declaring variables in Excel VBA

Creating variables in VBA is known as declaring your variables.

A variable declaration is made up of three parts:

1. The keyword Dim
2. The name of the variable
3. Its data type. 

The declarations are the first lines of code you see in an Excel macro. 

The code below declares four variables. Each variable is declared on a separate line.

Sub VariableExamples()

Dim companyID as String
Dim companyName as String
Dim numberOfProducts as Integer
Dim productPrice as Double

End Sub

When naming a variable, there are some rules to abide by (these are the same rules to take into account when naming macros).

• The variable name must not begin with a number.
• You cannot use special characters such as %, &, ! or @.
• You cannot use spaces.
• A reserved keyword such as Dim, Public or Next cannot be used. These reserved words are important for other VBA operations.

It is good practice to define a data type for each of your variables. This specifies the type of data that the variable will store.

If a variable type is not defined then the Variant data type is used. This can handle any data type, but takes up more storage space.

There are a few common VBA variable types that you will see and use frequently. These are:

• String to store text values.
• Long and Integer to store whole numbers.
• Double to store numbers with decimals.
• Boolean to store TRUE and FALSE values.
• Object to store VBA objects such as worksheets and charts.

If you have multiple variables of the same data type, you can declare these on the same line.

In the code below, the two String variable types are declared on the same line.

Sub VariableExamples()

Dim companyID, companyName as String
Dim numberOfProducts as Integer
Dim productPrice as Double

End Sub

VBA data types

The table below is a more definitive guide to different Excel VBA data types. It details the storage they consume and the range of values they can hold.

For example, note that an Integer data type can store values only up to 32,767. So, if you are using a variable to store large values such as row numbers, the Long data type would be more appropriate.

This is a useful table to keep as a reference as you get used to the VBA variable types. 

 

Using variables in your VBA code

After a variable is declared, it is initialized by assigning a value to it. This is normally done (if possible) immediately after the declaration.

The code below shows values being assigned to the variables.

Sub VariableExamples()

Dim companyID as String
Dim companyName as String
Dim numberOfProducts as Integer
Dim productPrice as Double

companyID = Range(“A2”).Value
companyName = “Chips and Dips”
numberOfProducts = ActiveSheet.UsedRange.Rows.Count
productPrice = 39.50

End Sub

A few different techniques have been demonstrated here.

• A value from a cell has been assigned to the ‘companyID’ variable.
• Notice that when assigning to String data types, quotation marks must be used.
• The result of the number of rows in the used range has been assigned to the ‘numberOfProducts’ variable.

The variables can then be used in the macro as needed, and their value can be changed at any time.

In this list of useful macros for beginners, you can see many examples of variables being used.

The code below shows an IF statement testing a cell value. If a condition is met, the value of the ‘productPrice’ variable is changed. The value of the variable is then assigned to a cell.

Using object variables

Object variables work a little differently. They are incredibly useful and you should consider exploring them.

They can be declared as an object data type.

Sub ObjectExamples()

Dim wkSht as Object

End Sub

However, they are normally declared as a specific object type.

Sub ObjectExamples()

Dim wbk as Workbook
Dim wkSht as Worksheet
Dim rng as Range

End Sub

When assigning values to object variables, the keyword Set must be used.

Sub ObjectExamples()

Dim wbk as Workbook
Dim wkSht as Worksheet
Dim rng as Range

Set wbk = Workbooks.Open(“C:\Users\Admin\Desktop\Annual Sales.xlsx”)
Set wkSht = Sheets(“March”)
Set rng as wbk.Sheets(1).Range(“A2”)

End Sub

A few different techniques are demonstrated here:

• A workbook is opened and assigned to the ‘wbk’ variable in the same statement.
• The ‘wbk’ variable is then used when assigning to the ‘rng’ variable. This prevents us from having to repeat the workbook name.
• The ‘wkSht’ variable is assigned a specific sheet from the opened workbook (it is the active workbook as it was opened in the previous statement).

The object variables can be used in your code just like the object they represent. For example, the ‘wkSht’ variable has all the properties and methods of a worksheet.

These variables make referencing the objects in code much clearer and more concise.

Using option explicit

It isn’t actually necessary to declare variables or to define VBA data types before using variables in code. However, it is best practice to explicitly declare your variables, and you are strongly advised to follow this practice.

This practice has multiple benefits:

• The code is more legible. Others know where to find your variables and the type of data they will store. This is all declared upfront and not created on the fly, confusing others and yourself.
• The macro is faster. By defining VBA variable types you save space (otherwise the Variant data type is used) and your code does not have to second guess things during runtime.
• It is easier to troubleshoot your code. If you try to assign a value to a variable that does not match the VBA data type set, then a type mismatch error is shown.

Using Option Explicit forces variables to be declared before they can be used. This is a good idea.

To use Option Explicit, you simply type Option Explicit at the top of the code window.

This feature can also be very helpful troubleshooting code. If a name is not recognized, the variable not defined error is shown.

This helps to spot typing mistakes and variables that you forgot to declare.

Option Explicit is an option that you can enable in the Visual Basic Editor. This ensures that Option Explicit is automatically added to the top of a code window. 

To enable Option Explicit:

1. Click Tools > Options
2. Check the Require Variable Declaration box on the Editor tab.

Variable scope

A variable scope refers to where the variable can be used.

All of the variables demonstrated in this article have only been available for use within that macro or procedure.

This is because they were declared with the keyword Dim within that procedure. When the macro finishes, the variable is removed from memory.

A variable can also be set with module scope (available to be used by any procedure in that module) and project scope (available to be used by any procedure in that project).

Module scope variables

To make a variable accessible to all procedures within a module, you declare the variable at the top of the code window above the procedures and use the Dim keyword.

When a procedure finishes, the variable and its value are retained. So, if another procedure uses the variable, it still has the value it finished with prior. It is only released from memory when the project is closed.

Project scope variables

To make the variable available to all of the procedures in the project (usually the workbook), it is declared with the Public keyword, instead of Dim.

This is also declared above all procedures at the top of any code window in the project.

What is a constant?

A constant is like a variable, except the value cannot change.

The code below declares and initializes a constant named ‘fixedRate’.

Sub ConstantExample()

Const fixedRate as Double = 2.45

End Sub

Constants can also be set to procedure, module, or project scope.

Fast track your Excel VBA learning

Enrol in our Excel Macros and VBA course today to fast-track your Excel VBA learning, where you will learn how to maximize VBA functions such as VBA range object.

The course is presented by Microsoft MVP Ken Puls, and covers all the essentials of Excel VBA to get you up and running. Try it for free with a 7 day free trial to all GoSkills courses.

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Start free trial