Ravindra BagaleCourses & study guides

12. Macros and VBA

12.6 Variables and Data Types

By Last updated:

Declare variables with Dim name As Type.

Type Holds Example
Long Whole numbers (use instead of Integer) Dim orders As Long
Double Decimals Dim amount As Double
Currency Money with 4 fixed decimals Dim fee As Currency
String Text Dim city As String
Date Date/time Dim orderDate As Date
Boolean True/False Dim isLate As Boolean
Variant Anything (default if not declared) Dim v As Variant
Object types Worksheets, ranges Dim ws As Worksheet, Dim rng As Range

Object variables need Set: Set ws = Worksheets("Orders").

Sub VariablesDemo()
    Dim city As String
    Dim orders As Long
    Dim sales As Double
    Dim aov As Double
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")
    city = "Pune"
    orders = Application.WorksheetFunction.CountIfs(ws.Range("C:C"), city)
    sales = Application.WorksheetFunction.SumIfs(ws.Range("G:G"), ws.Range("C:C"), city)

    If orders > 0 Then aov = sales / orders
    MsgBox city & ": " & orders & " orders, AOV Rs " & Format(aov, "0.00")
End Sub

On the Module 3 mini dataset this shows Pune: 4 orders, AOV Rs 136.00.

Ravindra Bagale's Tip

Many students use Integer for row numbers – Integer's limit is 32,767, and with big data you get an "Overflow" error. Always use Long for rows and counts. If you forget Set for an object variable, you get the "Object variable not set" error – that's very common too.

Practice task

Write a macro that stores the Nashik order count, sales and average delivery minutes in variables and shows them in one MsgBox.