Microsoft Excel

12 minute read

VBA Variable Types in Excel

Alan Murray

Alan Murray

It is very important to know how to effectively understand and use VBA variable types in Excel, in order 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.

Learn the best Excel shortcuts

Download our printable shortcut cheatsheet for PC and Mac

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.

VBA variable types - programmer at desk

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. 

 

Data Type

Stored

Range of Values

Byte

1 Byte

0 to 255

Integer

2 Bytes

-32,768 to 32,767

Single

4 Bytes

-3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values

Long

4 Bytes

-2,147,483,648 to 2,147,483,648

Double

8 Bytes

-1.79769313486232e+308 to -4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.

Decimal

14 Bytes

+/-79,228,162,514,264,337,593,543,950,335 for no decimal points,+/-7.9228162514264337593543950335 for 28 places to the right of the decimal

Date

8 Bytes

January 1, 100 to December 31, 9999

Currency

8 Bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

String (variable length)

10 bytes added to the string length

0 to 2 billion characters

String (fixed length)

string length

1 to approximately 65,400

Variant (with numbers) 16 bytes Any numeric value up to the range of a Double
Variant (with characters) 22 bytes + string length (24 bytes on 64-bit systems) Same range as for variable-length String

Object

4 Bytes

Object in VBA

Boolean

2 Bytes

True or False

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.

VBA variable types - if statement

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.

VBA variable types - mismatchUsing 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.

VBA variable types - option explicitThis feature can also be very helpful troubleshooting code. If a name is not recognized, the variable not defined error is shown.

VBA variable types - option explicitThis 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.

VBA variable types - require variable declaration

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.

VBA variable types - module scopeWhen 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.

VBA variable types - project scope

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

Enroll in our Excel Macros and VBA course today to fast track your Excel VBA learning.

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.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial
Alan Murray

Alan Murray

Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spreading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner.

No comments

LoginSign up