Working with Variables in VBA

A variable is a named storage location in your computer's memory. Instead of hardcoding a value like a group number or GPR directly into your code, you store it in a variable and reference the variable name instead. This makes your macro adaptable — change the value once and it updates everywhere the variable is used.

Working with variables always follows three steps. Professor Sanders calls them Define, Populate, and Use — and that order is not optional. You must define a variable before you can populate it, and populate it before you can use it. If a value is wrong or blank, the first thing to check is whether these three steps are happening in the right order.

The Three Steps

Defining a variable creates a named slot in memory and tells VBA what kind of data it will hold. Populating it places a value in that slot. Using it means referencing the variable name anywhere you need that value — in a cell assignment, a MsgBox, or a loop condition.

' Step 1 — DEFINE
Dim VariableName As DataType

' Step 2 — POPULATE
VariableName = something

' Step 3 — USE
ActiveCell = VariableName

Here is the three-step pattern in a real macro — prompting for a group number and writing it to a cell:

Dim NewGroup As Integer

NewGroup = InputBox("Enter the new group number")

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell = NewGroup

Data Types

The data type tells VBA how to store the value in memory. Using the wrong type causes silent errors — an Integer silently truncates a decimal, a String won't compare correctly to a number. For this course you need five types: String for text, Integer for small whole numbers, Long for large whole numbers like student IDs, Double for numbers with decimal places like GPR, and Date for dates.

Dim LastName    As String    ' text
Dim GroupNumber As Integer   ' whole number up to ~32,000
Dim StudentID   As Long      ' whole number any size
Dim GPR         As Double    ' number with decimals
Dim StartDate   As Date      ' date value

These are the actual variable declarations from the Project Demo:

Dim NewGroup       As Integer   ' group number — small whole number
Dim NumberAccepted As Integer   ' counter — small whole number
Dim UIN            As Long      ' 9-digit student ID — needs Long
Dim GPR            As Double    ' GPA with decimals
Dim Gender         As String    ' text
Dim Birthdate      As Date      ' date

Option Explicit

By default, VBA lets you use any word as a variable without declaring it. This causes three problems: VBA may choose the wrong data type, a misspelled variable name silently creates a new blank variable instead of an error, and your macro runs slower. Adding Option Explicit at the very top of the module — above the first Sub — forces you to declare every variable. If you use an undeclared name, VBA stops and tells you exactly where the problem is.

Option Explicit          ' ← must be the very first line in the module

Sub YourMacroName()
    Dim VariableName As DataType
End Sub

This is what the top of every well-written module looks like:

Option Explicit
' ACCT 628 - Sanders

Sub Option1_Modified()

    Dim NewGroup       As Integer
    Dim NumberAccepted As Integer
    Dim UIN            As Long
    Dim GPR            As Double
    Dim Gender         As String
    Dim Birthdate      As Date

End Sub

Variables vs Named Ranges

Variables and Named Ranges look similar but behave very differently. A variable exists only while the macro is running — when the macro stops, it's gone. A Named Range exists on the worksheet permanently and can be used in both Excel formulas and VBA. In VBA, Named Ranges always use quotes: Range("GroupData"). Variables never use quotes.

            ' Variable — no quotes, exists only while macro runs
              LegalDrinkingAge = InputBox("Enter the current legal drinking age")

            ' Named Range — quotes required, exists on worksheet
              Range("LegalDrinkingAge") = LegalDrinkingAge

This example from the Macro Handout shows both in the same macro:

Dim LegalDrinkingAge As Integer

LegalDrinkingAge = InputBox("Enter the current legal drinking age")

' Named Range has quotes — Variable does not
Range("LegalDrinkingAge") = LegalDrinkingAge

Quick Check

Easy Wins

Your First Variable Guided

Write a macro that asks for a name using InputBox, stores it in a String variable, and displays "Hello, [name]!" in a MsgBox.

Option Explicit
Sub SayHello()

End Sub

Dim UserName As String

UserName = InputBox("What is your name?")

MsgBox "Hello, " & UserName & "!"

Option Explicit
Sub SayHello()
    Dim UserName As String
    UserName = InputBox("What is your name?")
    MsgBox "Hello, " & UserName & "!"
End Sub

Dim WholeNumber   As Integer
Dim DecimalNumber As Double

WholeNumber   = 3.7
DecimalNumber = 3.7

MsgBox "Integer: " & WholeNumber & " | Double: " & DecimalNumber

Three Steps in Order Guided

Write a macro that reads a student's ID, last name, and GPR from cells A2, B2, and D2, stores them in variables, then displays all three in a MsgBox.

Option Explicit
Sub ReadStudent()
    Dim StudentID As Long
    Dim LastName  As String
    Dim GPR       As Double

    Sheets("Sheet1").Select
    Range("A2").Select

    StudentID = ActiveCell
    LastName  = ActiveCell.Offset(0, 1)
    GPR       = ActiveCell.Offset(0, 3)

    MsgBox "ID: " & StudentID & " | Name: " & LastName & " | GPR: " & GPR
End Sub

Option Explicit
Sub ReadStudent()
    Dim StudentID As Long
    Dim LastName  As String
    Dim GPR       As Double

    Sheets("Sheet1").Select
    Range("A2").Select

    StudentID = ActiveCell
    LastName  = ActiveCell.Offset(0, 1)
    GPR       = ActiveCell.Offset(0, 3)

    MsgBox "ID: " & StudentID & " | Name: " & LastName & " | GPR: " & GPR
End Sub

Sample Data

Practice Problem

MsgBox "ID: " & StudentID & " | Name: " & LastName & ", " & FirstName & " | GPR: " & GPR

Challenge

These exercises are perfect preparation for the Sample Exam, which prepares you for the Macro Project Exam itself. Exam format may vary by semester.