Calculations and Dates in VBA

VBA can perform calculations three ways: store the result in a variable, place a formula in a cell so Excel calculates it, or place the result of the calculation directly in a cell. Each approach has its place. Variables are used when you need the value mid-macro. Formulas in cells let Excel recalculate automatically. Results in cells are faster and don't depend on Excel's calculation engine.

For this course, the most common pattern is calculating into a variable and then displaying or storing the result. Functions like Count, Sum, and Average are available through Application.WorksheetFunction and work exactly like their Excel equivalents.

Basic Math and WorksheetFunction

Basic arithmetic in VBA uses the same operators as Excel: +, -, *, /. You can calculate directly into a variable or into a cell. To use Excel functions like Count, Sum, or Average in VBA, prefix them with Application.WorksheetFunction. Some functions work without the prefix — but when in doubt, include it.

' Basic math into a variable
AverageGPR = TotalGPR / StudentCount

' WorksheetFunction into a variable
NoRecords = Application.WorksheetFunction.Count(Range("A:A"))
TotalSales = Application.WorksheetFunction.Sum(Range("H:H"))

' Place formula in a cell (Excel calculates)
Range("K2") = "=Count(A:A)"

' Place result in a cell (VBA calculates)
Range("K3") = Application.WorksheetFunction.Count(Range("A:A"))

This example from the Macro Handout shows all three approaches for Count:

Dim NoRecords As Integer

' Places the actual count formula in the cell
Range("K2") = "=Count(A:A)"

' Places the result into a cell
Range("K3") = Application.WorksheetFunction.Count(Range("A:A"))

' Places the result into a variable
NoRecords = Application.WorksheetFunction.Count(Range("A:A"))

Accumulating a Total in a Loop

When you need a running total across multiple records, you accumulate it inside a loop using a variable. Start the variable at 0 before the loop, add to it on each pass, then use or display it after the loop ends. This is the same pattern whether you're summing sales, counting records, or calculating an average.

Dim CumulativeTotal As Double
CumulativeTotal = 0              ' reset before loop

Do Until ActiveCell = ""
    CumulativeTotal = CumulativeTotal + ActiveCell.Offset(0, 5)
    ActiveCell.Offset(1, 0).Select
Loop

MsgBox "Total: " & CumulativeTotal

This is the CumulativeTotal example from the Macro Handout:

Dim CumulativeTotal As Double

Range("A2").Select
CumulativeTotal = 0

Do Until ActiveCell = ""
    CumulativeTotal = CumulativeTotal + ActiveCell.Offset(0, 5)
    ActiveCell.Offset(1, 0).Select
Loop

MsgBox "The cumulative total is " & _
       Application.WorksheetFunction.Dollar(CumulativeTotal, 0)

Formatting Numbers and Dates

Formatting affects what is displayed in a cell — it does not change what is stored in a variable. A Double variable holding 3.756 stays 3.756 regardless of how the cell displays it. The Dollar function formats a number as currency for display in a MsgBox. For worksheet cells, use the NumberFormat property.

' Format a cell as currency
Range("H2").NumberFormat = "$#,##0.00"

' Format a cell as percentage with 2 decimal places
Range("B2").NumberFormat = "0.00%"

' Dollar function for MsgBox display only
MsgBox Application.WorksheetFunction.Dollar(AverageVariable, 2)

Date Functions

VBA has built-in date functions for adding time intervals and finding end-of-month dates. DateAdd takes an interval code, a number, and a starting date and returns a new date. EoMonth returns the last day of a month and requires Application.WorksheetFunction. Year, Month, and Day extract parts of a date and work directly without any prefix.

' Add 1 month to a date
DateAdd("m", 1, StartDate)

' Add 1 day
DateAdd("d", 1, StartDate)

' Add 1 year
DateAdd("yyyy", 1, StartDate)

' Last day of the month
Application.WorksheetFunction.EoMonth(StartDate, 0)

' Extract year, month, day
Year(StartDate)
Month(StartDate)
Day(StartDate)

These are the date examples from the Macro Handout:

Dim StartDate As Date
StartDate = Range("B2")

' Add 1 month
Range("B4") = DateAdd("m", 1, StartDate)

' End of month
Range("B7") = Application.WorksheetFunction.EoMonth(StartDate, 0)

' Compare year to current year
If Year(Range("B7")) = Year(Now()) Then
    MsgBox "You are in the current year"
End If

Quick Check

Easy Wins

Option Explicit
Sub CalculateTotals()
    Dim RecordCount As Integer
    Dim TotalGPR    As Double

End Sub

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

    RecordCount = Application.WorksheetFunction.Count(Range("A:A"))
    TotalGPR    = Application.WorksheetFunction.Sum(Range("D:D"))

    MsgBox "Records: " & RecordCount & " | Total GPR: " & _
           Application.WorksheetFunction.Dollar(TotalGPR, 3)

Option Explicit
Sub CalculateTotals()
    Dim RecordCount As Integer
    Dim TotalGPR    As Double

    Sheets("Sheet1").Select
    RecordCount = Application.WorksheetFunction.Count(Range("A:A"))
    TotalGPR    = Application.WorksheetFunction.Sum(Range("D:D"))

    MsgBox "Records: " & RecordCount & " | Total GPR: " & _
           Application.WorksheetFunction.Dollar(TotalGPR, 3)
End Sub

Option Explicit
Sub DateDemo()
    Dim StartDate As Date
    StartDate = Range("B2")

    Range("C2") = DateAdd("m", 1, StartDate)
    Range("C3") = DateAdd("d", 1, StartDate)
    Range("C4") = DateAdd("yyyy", 1, StartDate)
    Range("C5") = Application.WorksheetFunction.EoMonth(StartDate, 0)
    Range("C6") = Year(StartDate)
End Sub

Practice Problem

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.