Excel VBA Data Types: Choosing the Right Variable for Macros

Excel VBA data types define what kind of value a variable can hold, such as a whole number, decimal, date, text, or object. Declare variables with a type that fits the values you expect: use Long for most whole numbers, Double for general calculations, String for text, and a specific object type such as Worksheet for Excel objects.

What VBA data types mean

A variable is a named place for a value while a macro runs. Its data type tells VBA how to interpret and store that value, and which operations make sense. For example, adding two numbers is straightforward, while combining text with a number may require conversion. Declaring a type also helps VBA catch mistakes and can make code easier to understand.

In a procedure, a declaration such as Dim rowCount As Long creates a variable named rowCount that can hold a whole number within the range of the Long type. The keyword Dim is commonly used for local variables. With Option Explicit at the top of a module, VBA requires variables to be declared before use, helping catch misspelled names that would otherwise become unintended variables.

VBA numeric types and their ranges

VBA provides several numeric types. Their ranges and precision differ, so choosing one is not merely a matter of using the smallest available type. In typical Excel automation, Long and Double cover many needs.

Byte stores whole numbers from 0 to 255. Use it when that narrow range is appropriate, not as a general number type.
Integer stores whole numbers from -32,768 to 32,767. It is a 16-bit type and is often too small for row numbers or counts in modern worksheets.
Long stores whole numbers from -2,147,483,648 to 2,147,483,647. It is usually the sensible choice for counters, row indexes, and whole-number values in VBA.
LongLong is a 64-bit integer type available in 64-bit VBA, with a much larger range than Long. It is not available in 32-bit VBA.
Single and Double store floating-point numbers. Double has more precision and is generally preferred for measurements and calculations involving fractions.
Currency stores fixed-point values with four decimal places. It is useful for monetary amounts when four fractional digits are sufficient.
Decimal supports high-precision decimal values, but it is not a type you can name directly in a declaration such as Dim amount As Decimal. It is a subtype of Variant, commonly obtained with CDec.

Single and Double are approximate binary floating-point types, so some decimal fractions cannot be represented exactly. That is usually acceptable for general calculations, but can matter when comparing results or working with money. Currency represents values in fixed four-decimal increments and avoids many floating-point rounding surprises, though its range is finite and it does not provide more than four decimal places.

Text, dates, and Boolean values

String holds text. A variable-length string is the normal choice for names, labels, file paths, and other text. VBA also permits a fixed-length string declaration such as Dim shortCode As String * 8; it holds eight characters and is padded with spaces when its assigned text is shorter. Fixed-length strings are a specialized option, not a general performance improvement to assume.

Date represents a date and time. Internally, VBA works with a serial-number representation, but declaring a value as Date makes the intended meaning clear and provides date-oriented operations. A date variable can include a time component; for example, midnight and 3:30 p.m. on the same day are different date-time values. Display formatting does not change the value itself.

Boolean holds either True or False. It is appropriate for flags such as whether a check passed or whether a condition was found. Avoid using text like “yes” and “no” for a simple true-or-false state unless the value needs to be displayed or exchanged as text.

Variant, object, and array variables

A Variant can hold different kinds of values, including numbers, text, dates, empty values, and error values. It is useful when the value’s type is genuinely unknown or when receiving data from Excel, because a cell may contain different kinds of content. But a Variant does not make type choices irrelevant: unexpected text, blanks, or errors can still cause problems in later calculations. Using a specific type where the expected value is known makes intent clearer.

Excel range values commonly arrive in a Variant. For example, a single-cell read may return one value, while reading a multi-cell range generally returns a two-dimensional array held in a Variant. A blank cell can be represented as Empty, and a worksheet error can be represented by a VBA error value. Code that reads mixed or user-entered cells should account for these possibilities rather than assuming every value is a number.

Object variables refer to objects, such as a workbook, worksheet, or range. Declare the most specific useful type: Dim ws As Worksheet communicates more than Dim item As Object. Assign an object with Set, as in Set ws = ThisWorkbook.Worksheets("Sales"). A general Object variable can be useful when working with different object types, but it offers less compile-time checking.

Arrays hold multiple values under one variable name. Their elements can have a defined type, such as a dynamic array declared with Dim amounts() As Double, or they can be held in a Variant array. A dynamic array can be sized later with ReDim. An array is a way to organize a collection of values, not a replacement for choosing an appropriate element type.

How to declare variables in a macro

This example reads sales amounts from column B on a worksheet named Sales and adds numeric entries. It assumes row 1 is a heading and that the remaining used cells in column B are intended to contain amounts. The declarations show common choices: a worksheet object, Long row indexes, and Currency for a total with up to four decimal places.

Option Explicit

Sub SumSalesAmounts()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim total As Currency
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For r = 2 To lastRow
        cellValue = ws.Cells(r, "B").Value2
        If IsNumeric(cellValue) Then
            total = total + CCur(cellValue)
        End If
    Next r

    MsgBox "Total: " & Format$(total, "Currency")
End Sub

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The example skips values that VBA does not consider numeric. If column B can contain numeric-looking text, dates, or worksheet errors, decide explicitly how those entries should be handled; a simple numeric check is not a complete data-validation policy. Also note that CCur converts to Currency precision, so values with more than four decimal places are rounded during conversion.

How to choose the right type

Choose a type based on the value’s meaning, possible range, precision needs, and how the value will be used. A practical approach is to ask:

Is it a whole number? Use Long for ordinary counts and indexes. Use LongLong only when its larger range is needed and the code will run in 64-bit VBA.
Does it include a fractional part? Use Double for most general numeric calculations; consider Currency for values that need fixed four-decimal precision.
Is it a date, text value, or true/false condition? Declare it as Date, String, or Boolean to express that purpose.
Is it an Excel object? Use a suitable object type such as Workbook, Worksheet, or Range, and assign it with Set.
Can the incoming value vary or be unknown? A Variant may be appropriate at the boundary where data enters the code; validate it before converting or calculating.

Do not choose Integer just because a value seems small today. Excel worksheets can contain 1,048,576 rows, which exceeds the maximum value of Integer. A loop counter or row number declared as Integer can therefore overflow. Long is the customary choice for such indexes even when the current data set is short.

Excel cell values and type conversions

A worksheet cell’s displayed format and its underlying value are different things. A cell formatted as a date may store a serial number, and a cell showing currency may store a numeric value. When VBA reads a cell, the returned value depends on the property used and the contents. Range.Value returns a Variant and can return a Date or Currency subtype in some circumstances; Range.Value2 does not use those subtypes and returns such values as numbers instead. The cell’s number format controls how Excel displays a value, not whether it is inherently a VBA Date variable.

Conversions such as CLng, CDbl, CCur, CDate, and CStr explicitly convert values. Conversion can fail if the source cannot be interpreted as the target type, and numeric conversions can round or overflow if the value is unsuitable. Check for blank cells, errors, and unexpected text before converting data from a worksheet. For a mixed range, it is often safer to read into a Variant, inspect each value, and then assign valid values to more specific variables.

Common errors and 32-bit versus 64-bit VBA

A Type mismatch commonly occurs when code tries to use a value in a way its actual type does not support, such as treating text as a number or assigning an incompatible object. An Overflow occurs when a value is outside the range of its declared type, such as putting a large row number into an Integer. Declaring variables carefully, using Option Explicit, and checking worksheet inputs can prevent many of these errors.

Most ordinary Excel VBA code can use Long on both 32-bit and 64-bit Office. The type LongPtr is intended for pointer-sized values, especially Windows API handles and pointers in declarations; it adjusts to the platform size in VBA versions that support it. It is not a general substitute for Long in row counters or ordinary numbers. LongLong is for large integer values in 64-bit VBA and cannot be used in 32-bit VBA. Code that calls Windows APIs may also need platform-appropriate declarations; that is a separate compatibility concern from normal worksheet variables.

Frequently Asked Questions

Should I use Integer or Long in Excel VBA?
Use Long for most whole numbers, including row numbers and loop counters. Integer stops at 32,767, below the number of rows in an Excel worksheet.

What is the default data type in VBA?
If a variable is declared without an As type clause, it is a Variant. It is still better to declare the intended type explicitly when it is known, and to use Option Explicit so undeclared names are caught.

Can I declare a variable As Decimal?
No. VBA does not allow Decimal in a variable declaration. A Decimal value is a subtype of Variant; use a Variant and convert a suitable value with CDec when that precision is needed.

When should I use Variant in VBA?
Use it when a value may legitimately have different types or when receiving values from a worksheet whose contents are not uniform. For a known, stable value such as a row index or a date, a specific type is clearer and helps expose incompatible assignments.

What is the difference between Long and LongPtr?
Long is a 32-bit integer for ordinary whole numbers. LongPtr is a platform-sized type intended mainly for pointers and handles in compatible API declarations; use it for that purpose rather than replacing every Long variable.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.