VBA Data Types and Scope

Analogy categoryAnalogies

A data type defines what kind of value a variable can hold and how much memory it uses. VBA offers numeric types, text, dates, booleans, and generic types. Scope determines where a variable is visible, whether inside a single procedure, an entire module, or the whole project, while lifetime governs how long a variable keeps its value.

🏏

Cricket analogy: A data type is like a player's specialism, since a fast bowler and a spinner are stored and used differently; scope is like squad selection, deciding whether a player is available to one match or the whole tournament.

Core Data Types

Common numeric types include Integer (16-bit, up to 32,767), Long (32-bit, over two billion), Single and Double (floating point), and Currency (a fixed-decimal type ideal for money). String holds text, Boolean holds True or False, and Date holds dates and times. The generic Variant can hold any type but uses more memory, and Object holds a reference to something like a Range or Worksheet.

🏏

Cricket analogy: Choosing Long over Integer is like picking a batsman who can chase a big total: an Integer caps at 32,767 like a batsman who cannot chase a huge score, while Long comfortably handles the big run chase.

vba

Sub DataTypeDemo()
    Dim count As Long          ' whole numbers, over 2 billion
    Dim price As Currency      ' exact money values
    Dim name As String         ' text
    Dim isDone As Boolean      ' True / False
    Dim created As Date        ' date and time

    count = 150000
    price = 19.99
    name = "Widget"
    isDone = False
    created = Now

    Debug.Print name & ": " & count & " units at " & price
End Sub

Prefer Long over Integer for whole numbers. On modern 32-bit and 64-bit systems Long is actually processed at least as fast, and it avoids the classic Overflow error that strikes when an Integer exceeds 32,767, for example when counting rows in a large sheet.

Variable Scope and Lifetime

A variable declared with Dim inside a procedure is local: it is visible only there and is destroyed when the procedure ends. Declared at the top of a module with Private, it is visible to all procedures in that module. Declared with Public at module level, it is visible across the whole project. A Static variable keeps its value between calls instead of resetting each time the procedure runs.

🏏

Cricket analogy: A local variable is like a nightwatchman used for one over then gone; a Public variable is like the captain known across the whole tournament; a Static variable is like a running batting average that persists between innings instead of resetting.

Constants and Object Variables

A constant declared with Const holds a value that never changes, such as Const Pi As Double = 3.14159, which improves readability and prevents accidental edits. Object variables reference things like Worksheets or Ranges and must be assigned with the Set keyword, for example Set rng = Range("A1"), because you are pointing to an existing object rather than copying a simple value.

🏏

Cricket analogy: A Const is like the fixed boundary-rope length: it never changes mid-match, giving everyone a reliable reference, while Set is like pointing to a specific fielder by name rather than cloning them.

Forgetting Set when assigning an object is a classic beginner error. Writing rng = Range("A1") without Set assigns the cell's value, not the Range object, and later calls like rng.Font.Bold will raise 'Object variable or With block variable not set' (error 91).

A data type defines the kind of value a variable holds and how much memory it uses.
Key numeric types: Integer (to 32,767), Long (over 2 billion), Single/Double (floating point), and Currency (exact money).
String holds text, Boolean holds True/False, Date holds dates and times, Variant holds anything, and Object holds references.
Prefer Long over Integer to avoid Overflow errors, and Currency over Double for money.
Scope controls visibility: Dim in a procedure is local, Private is module-wide, and Public is project-wide.
A Static variable retains its value between procedure calls instead of resetting.
Const declares an unchangeable value; object variables must be assigned with Set, or error 91 results.

Practice what you learned

1. What is the maximum value an Integer can hold in VBA?

2. Which data type is best suited for storing monetary values precisely?

3. A variable declared with Dim inside a procedure has what scope?

4. What does declaring a variable as Static change?

5. How must an object variable such as a Range be assigned?

Was this page helpful?

VBA Syntax and Variables

Your First VBA Macro

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#VBAStudyNotes#VBADataTypesAndScope#VBA#Data#Types#Scope#StudyNotes#SkillVeris#ExamPrep

VBA Data Types and Scope