Excel VBA Lesson 2: Working with Variables

2.1 Introduction to Variables in Excel VBA

Variables are fundamental elements in Excel VBA programming that allow you to store and manipulate data temporarily in memory. Understanding how to declare, initialize, and use variables effectively is essential for creating powerful and efficient Excel macros and functions.

2.2 What Are Variables in VBA?

In programming, a variable is a named storage location in computer memory that holds data which can be modified during program execution. Variables in VBA serve as containers for different types of information such as:

Numbers
Text
Dates
True/False values
References to Excel objects

2.3 Declaring Variables in VBA

Before using variables in your VBA code, it's best practice to declare them. Variable declaration serves two important purposes:

It tells VBA what kind of data the variable will store
It helps prevent typos and makes your code more maintainable

Basic Variable Declaration Syntax

Dim variableName As DataType

Where:

Dim stands for "Dimension" and is the keyword used to declare variables
variableName is the name you choose for your variable
DataType specifies what kind of data the variable will hold

Common VBA Data Types

Data Type	Description	Example Values	Storage Size
Integer	Whole numbers from -32,768 to 32,767	1, 42, -293	2 bytes
Long	Larger whole numbers	50000, -1000000	4 bytes
Single	Decimal numbers with moderate precision	3.14, -10.5	4 bytes
Double	Decimal numbers with high precision	3.14159265359	8 bytes
String	Text values	"Hello", "A1"	Variable
Boolean	True or False values	True, False	2 bytes
Date	Date and time values	#1/1/2023#, #12:30:00 PM#	8 bytes
Object	References to objects like worksheets, ranges	Range("A1"), Sheets("Sheet1")	4 bytes
Variant	Can hold any type of data	Numbers, text, dates, objects	Variable

Example: Declaring Variables with Different Data Types

Sub DeclareVariables()
    Dim employeeID As Integer
    Dim employeeName As String
    Dim monthlySalary As Double
    Dim hireDate As Date
    Dim isActive As Boolean
    Dim workSheet As Worksheet
End Sub

2.4 Variable Naming Best Practices

When naming variables in VBA, follow these guidelines:

Use descriptive names that indicate the variable's purpose
Start with a letter (not a number)
Don't use spaces or special characters (except underscore)
Avoid using VBA keywords or function names
Keep names reasonably short but clear

Good vs. Poor Variable Names

Good Variable Names	Poor Variable Names
firstName	fn
monthlySalary	ms
customerAddress	x
orderTotal	thing1

2.5 Assigning Values to Variables

After declaring a variable, you can assign values to it using the assignment operator (=).

Example: Assigning Values to Variables

Sub AssignValues()
    Dim employeeName As String
    Dim age As Integer
    Dim salary As Double
    
    ' Assigning values to variables
    employeeName = "John Smith"
    age = 35
    salary = 65000.50
    
    ' Displaying the values
    MsgBox "Employee: " & employeeName & vbCrLf & _
           "Age: " & age & vbCrLf & _
           "Salary: $" & salary
End Sub

2.6 The Option Explicit Statement

Adding Option Explicit at the top of your module forces you to declare all variables before using them. This is highly recommended as it:

Prevents typos in variable names
Makes your code more maintainable
Clarifies the data types you're working with
Reduces unexpected behaviors

Example: Using Option Explicit

Option Explicit

Sub CalculateBonus()
    Dim employeeSalary As Double
    Dim bonusRate As Double
    Dim totalBonus As Double
    
    ' If you misspell a variable name, VBA will show an error
    employeeSalary = 50000
    bonusRate = 0.1
    totalBonus = employeeSalary * bonusRate
    
    MsgBox "Bonus amount: $" & totalBonus
End Sub

2.7 Variable Scope in VBA

A variable's scope determines where in your code the variable can be accessed. There are three levels of scope in VBA:

1. Procedure-Level Scope

Variables declared within a procedure (Sub or Function) are only accessible within that procedure.

Sub ProcedureLevelExample()
    Dim localVariable As String
    localVariable = "Only available in this procedure"
    MsgBox localVariable
End Sub

2. Module-Level Scope

Variables declared at the top of a module (outside any procedure) are accessible by all procedures within that module.

' Module-level declaration
Dim moduleVariable As Integer

Sub Procedure1()
    moduleVariable = 10
    MsgBox moduleVariable  ' Displays 10
End Sub

Sub Procedure2()
    moduleVariable = moduleVariable + 5
    MsgBox moduleVariable  ' Displays 15 if run after Procedure1
End Sub

3. Public (Global) Scope

Variables declared with the Public keyword at the module level are accessible from any procedure in any module.

' In Module1:
Public globalVariable As String

' In Module2:
Sub AccessGlobalVariable()
    globalVariable = "This is a global variable"
    MsgBox globalVariable
End Sub

2.8 Working with Different Variable Types

String Variables

Strings store text values and can be manipulated in various ways:

Sub StringExample()
    Dim firstName As String
    Dim lastName As String
    Dim fullName As String
    
    firstName = "John"
    lastName = "Smith"
    
    ' Concatenating strings
    fullName = firstName & " " & lastName
    
    ' String functions
    MsgBox "Full name: " & fullName
    MsgBox "Length: " & Len(fullName)
    MsgBox "Uppercase: " & UCase(fullName)
    MsgBox "First 4 characters: " & Left(fullName, 4)
End Sub

Numeric Variables

VBA offers several types for storing numbers:

Sub NumericExample()
    Dim intValue As Integer
    Dim longValue As Long
    Dim doubleValue As Double
    
    intValue = 500
    longValue = 1000000
    doubleValue = 3.14159
    
    ' Performing calculations
    Dim result As Double
    result = doubleValue * intValue + longValue
    
    MsgBox "Result: " & result
End Sub

Date Variables

Date variables store date and time values:

Sub DateExample()
    Dim currentDate As Date
    Dim futureDate As Date
    Dim daysDifference As Integer
    
    currentDate = Date  ' Current date
    futureDate = DateAdd("m", 3, currentDate)  ' Add 3 months
    
    daysDifference = DateDiff("d", currentDate, futureDate)
    
    MsgBox "Current date: " & Format(currentDate, "mm/dd/yyyy")
    MsgBox "Date in 3 months: " & Format(futureDate, "mm/dd/yyyy")
    MsgBox "Difference in days: " & daysDifference
End Sub

Boolean Variables

Boolean variables store True or False values:

Sub BooleanExample()
    Dim isValid As Boolean
    Dim hasPermission As Boolean
    
    isValid = True
    hasPermission = False
    
    ' Using Boolean in conditions
    If isValid And hasPermission Then
        MsgBox "Access granted"
    ElseIf isValid And Not hasPermission Then
        MsgBox "Valid, but no permission"
    Else
        MsgBox "Invalid"
    End If
End Sub

2.9 Variant Data Type

The Variant data type is a special type that can hold any kind of data. While convenient, it uses more memory and can make your code less efficient.

Sub VariantExample()
    Dim myVariant As Variant
    
    ' A Variant can hold different types of data
    myVariant = 100          ' Number
    MsgBox myVariant & " is a " & TypeName(myVariant)
    
    myVariant = "Hello"      ' String
    MsgBox myVariant & " is a " & TypeName(myVariant)
    
    myVariant = #1/1/2023#   ' Date
    MsgBox myVariant & " is a " & TypeName(myVariant)
    
    myVariant = True         ' Boolean
    MsgBox myVariant & " is a " & TypeName(myVariant)
End Sub

2.10 Using Variables with Excel Objects

Variables can reference Excel objects, making your code more efficient:

Sub WorkWithObjects()
    Dim myWorksheet As Worksheet
    Dim myRange As Range
    Dim cellValue As String
    
    ' Set references to objects
    Set myWorksheet = ThisWorkbook.Worksheets("Sheet1")
    Set myRange = myWorksheet.Range("A1")
    
    ' Write and read data
    myRange.Value = "Hello from VBA"
    cellValue = myRange.Value
    
    MsgBox cellValue
End Sub

2.11 Constants in VBA

Unlike variables, constants store values that don't change during the execution of your code:

Sub UseConstants()
    ' Declaring constants
    Const TAX_RATE As Double = 0.07
    Const COMPANY_NAME As String = "ABC Corporation"
    
    Dim price As Double
    Dim totalPrice As Double
    
    price = 100
    totalPrice = price + (price * TAX_RATE)
    
    MsgBox "Purchase from " & COMPANY_NAME & vbCrLf & _
           "Price: $" & price & vbCrLf & _
           "Tax Rate: " & (TAX_RATE * 100) & "%" & vbCrLf & _
           "Total: $" & totalPrice
End Sub

2.12 Practical Examples: Using Variables in Excel VBA

Example 1: Calculating Sales Commission

Sub CalculateCommission()
    ' Variables
    Dim salesAmount As Double
    Dim commissionRate As Double
    Dim commissionAmount As Double
    
    ' Get input from user
    salesAmount = CDbl(InputBox("Enter the sales amount:"))
    
    ' Determine commission rate based on sales
    If salesAmount <= 5000 Then
        commissionRate = 0.05  ' 5%
    ElseIf salesAmount <= 10000 Then
        commissionRate = 0.075  ' 7.5%
    Else
        commissionRate = 0.1  ' 10%
    End If
    
    ' Calculate commission
    commissionAmount = salesAmount * commissionRate
    
    ' Display result
    MsgBox "Sales Amount: $" & salesAmount & vbCrLf & _
           "Commission Rate: " & (commissionRate * 100) & "%" & vbCrLf & _
           "Commission Amount: $" & commissionAmount
End Sub

Example 2: Processing Data from a Range

Sub ProcessSalesData()
    ' Variables
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim cell As Range
    Dim totalSales As Double
    Dim salesCount As Integer
    Dim averageSale As Double
    
    ' Set references
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("B2:B20")  ' Range containing sales figures
    
    ' Initialize variables
    totalSales = 0
    salesCount = 0
    
    ' Loop through cells in the range
    For Each cell In dataRange
        If IsNumeric(cell.Value) And cell.Value > 0 Then
            totalSales = totalSales + cell.Value
            salesCount = salesCount + 1
        End If
    Next cell
    
    ' Calculate average if we have data
    If salesCount > 0 Then
        averageSale = totalSales / salesCount
        
        ' Output results
        MsgBox "Total Sales: $" & totalSales & vbCrLf & _
               "Number of Sales: " & salesCount & vbCrLf & _
               "Average Sale: $" & Format(averageSale, "0.00")
    Else
        MsgBox "No valid sales data found."
    End If
End Sub

2.13 Variable-Related Tips and Best Practices

Always use Option Explicit at the top of your modules
Choose the right data type for each variable to optimize memory usage
Initialize variables before using them in calculations
Keep variable scope as narrow as possible for better code organization
Use meaningful variable names that describe their purpose
Comment your code to explain complex variable usage
Clean up object variables by setting them to Nothing when done

2.14 Common Variable Errors and How to Avoid Them

Type Mismatch Error: Occurs when you try to assign a value of the wrong data type
Solution: Use conversion functions like CInt(), CStr(), CDbl() when needed
Overflow Error: Occurs when a number is too large for the variable's data type
Solution: Use Long instead of Integer for large whole numbers, or Double for large decimals
Variable Not Defined Error: Occurs when using a variable that hasn't been declared
Solution: Always use Option Explicit and declare all variables
Object Required Error: Occurs when using Set with non-object variables
Solution: Only use Set with object variables (Worksheet, Range, etc.)

2.15 Exercise: Practice Your Skills

To reinforce your understanding of variables, try these exercises:

Create a Sub procedure that asks the user for their name and birth year, then calculates and displays their age.
Write a macro that loops through cells A1:A10, counts how many contain numeric values, and displays the count.
Create a procedure that uses different variable types to store and manipulate employee information (name, ID, salary, hire date).

Summary

✅ In This Lesson, You Learned:

Variables store data temporarily in memory during macro execution
Declaring variables with appropriate data types improves code efficiency
Variable scope determines where variables can be accessed in your code
Using Option Explicit helps prevent errors and improves code quality
Different variable types serve different purposes in VBA programming

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page