VBA Data Types and Scope
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.
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 SubPrefer 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?
You May Also Like
VBA Syntax and Variables
The building blocks of every macro: procedures, variable declaration with Dim, operators, and comments in VBA's readable, line-based syntax.
Your First VBA Macro
Write, run, and debug your very first macro: set up the Developer tab, add a module, greet the user with MsgBox, write to a cell, and step through the code.
What Is VBA?
An introduction to Visual Basic for Applications: what it is, where it runs, and why it remains the workhorse of desktop Office automation.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics