VBA: How to Declare & use Variables in Excel
What is a Variable?
Think of a variable as a container for holding data within VBA.
Like in real life the type and size of the container you use depends on what you want it to hold.
This hub will focus on the four types of variables I use most often:
String: This type of variable is used to store text.
Integer: This variable type is used to store whole numbers from 32,768 to 32,767.
Long: This type of variable is used to store numbers ranging from -2,147,483,648 to 2,147,483,647
Date: This type of variable is used to store dates. When using the date variable enclose a date with "#" signs.
The first step in writing code is declaring your variables.
Variables should be declared at the beginning of a macro.
You can use a variable without first declaring it, but I highly discourage this. If a variable is not declared excel will store it as a Variant (the largest type of variable).
You can prevent excel from allowing you to use an undefined variable by prefacing your module with "option explicit" Option Explicit should be included a the first line of code at the top of your module (not within a subroutine)
Defining your variable a very easy task. It is composed of three elements:
Dim VariableName as VariableType
Dim MyString as String
This would create a string variable name my string.
Note: A variable can contain no spaces. It is helpful to include upper and lowercase letters. When you reference your variable again VBA will mirror the case. This makes it easier to tell you have typed your variable name in correctly.
Assigning data to your variable
Data is assigned to your macro using the "=" operator. Below are two ways to assign data to a variable.
Returning Your Variable to a Cell
To return your variable to a cell, reference the cell followed by the "=" operator and the variable name. Do not enclose your variable name in quotes
Let's Build a Macro to Test Our Variables
Option Explicit Sub TestVariables() 'define variables Dim MyString As String Dim MyInteger As Integer Dim MyLong As Long Dim MyDate As Date 'assign values MyString = "Congratlations! You have used your first variables." MyInteger = 10 MyLong = 10700.7 MyDate = #01/01/2012# 'return variables to cells Range("A1")=MyString Cells(2,1)=MyLong Range("C1")=MyDate End Sub
Ready for More?
In the next module, we will be working with ranges. Click here when you are ready to proceed.