Excel VBA Dim: How to Declare Variables

In Excel VBA, Dim declares a variable: a named place for a value your code can use, such as a row number, a total, or a worksheet reference. For example, Dim rowNumber As Long declares a variable named rowNumber that can hold a whole number; the declaration belongs inside a procedure or at the top of a module, depending on its intended scope.

What Dim means in VBA

Dim is a VBA keyword, not an Excel worksheet function and not a command typed into a cell. It is short for “dimension,” a term inherited from earlier BASIC programming languages, where it was used particularly to declare arrays. In ordinary VBA code, Dim is the usual way to declare variables, whether they hold numbers, text, dates, arrays, or references to Excel objects.

A variable gives a value a useful name and lets code reuse or update it. A macro might put a calculated total in salesTotal, store the current worksheet in ws, or keep a loop’s row number in rowNumber. Declaring the variable tells VBA its name and, when you specify one, its data type. This makes code easier to read and can help VBA catch mistakes before the macro runs.

Basic Dim syntax

The general pattern is Dim variableName As DataType. Put a procedure-level declaration between the procedure’s opening line, such as Sub Example(), and its executable statements. A declaration at the top of a module, before any procedures, has module-level scope instead.

Dim customerName As String declares text.
Dim invoiceDate As Date declares a date or time value.
Dim itemCount As Long declares a whole number.
Dim ws As Worksheet declares a reference to an Excel worksheet.

Use clear names that describe the value, such as lastRow or invoiceTotal. VBA is generally case-insensitive, so lastRow and LastRow refer to the same identifier. Names cannot contain spaces, and a variable name cannot be a reserved VBA keyword. Underscores can separate words, but descriptive camel-case names are also common.

Choosing a data type

Specifying a type makes your intention clearer and helps avoid unnecessary conversions. For typical Excel automation, Long is the common choice for row numbers and integer counters; Excel worksheets can have far more rows than fit in VBA’s 16-bit Integer. Use Double for many calculations that may include decimal values, Currency for fixed-point values where its supported range and four decimal places suit the task, and String for text.

Boolean stores True or False.
Date stores date and time values.
Variant can hold different kinds of values, but it uses less explicit typing and may make mistakes harder to spot.
Worksheet and Workbook are useful types for Excel object references.

A frequent VBA declaration trap is placing a type after only the last name in a comma-separated declaration. In Dim firstValue, secondValue As Long, only secondValue is a Long; firstValue is a Variant. To make both variables Long, write Dim firstValue As Long, secondValue As Long. Declaring each variable separately is often easiest to review.

Option Explicit and why it helps

Option Explicit instructs VBA to require variables to be declared before they are used. Put it at the very top of each code module, above any procedures. In the Visual Basic Editor on Windows, choose Tools > Options, open the Editor tab, and select Require Variable Declaration to have VBA insert it automatically in new modules. The setting does not add the statement retroactively to modules that already exist.

Without this safeguard, a typing mistake can silently create a new Variant variable. For example, code that declares customerCount but later writes customerCont may run with the wrong variable instead of reporting the typo. With Option Explicit, VBA flags the undeclared name, allowing you to correct it before running the procedure. It does not replace good data typing, but it catches a common source of errors.

Dim in a complete Excel VBA example

This short macro demonstrates declarations for different data types and shows how variables can be assigned and used. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place the code there. On many Mac keyboards, Option+F11 opens the editor; the exact key combination can depend on keyboard settings.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even if the macro’s effect seems small. This example only displays a message, but the same precaution matters for code that edits cells or workbook structure.

Option Explicit

Sub ShowExampleTotal()
    Dim salesTotal As Double
    Dim message As String

    ' Store a sample value and build a message.
    salesTotal = 125.5
    message = "Total: " & Format(salesTotal, "0.00")
    MsgBox message
End Sub

Here, salesTotal is a Double, while message is a String. The ampersand joins text together, and Format displays the number with two decimal places. The code does not write anything to a cell. In the Visual Basic Editor, place the cursor in the procedure and press F5 to run it; from Excel, macros can also be run through the Macro dialog or a button assigned to the macro.

Where to put Dim: scope and lifetime

Placement affects where code can use a variable. A variable declared inside a procedure is normally local to that procedure. Another procedure cannot directly refer to that local variable by name. This is usually desirable: keeping values local reduces unintended interactions between separate parts of a project.

A declaration placed in the declarations section at the top of a module, outside all procedures, is available to procedures in that module. At module level, Dim is private to that module; use Public when a variable genuinely needs broader project visibility. Public variables create shared state, so changing one procedure may affect another. For that reason, prefer procedure-level variables unless there is a clear reason to share a value.

Ordinary procedure-level variables are initialized again when a procedure starts. If a value must be retained between calls to a procedure, VBA also supports a Static declaration. Module-level variables remain available while the VBA project is running, but they are not a way to permanently save data in a workbook. Closing the project or resetting VBA clears them. Store information that must persist in worksheet cells, a file, or another appropriate data store instead.

Declaring arrays and Excel objects

Use an array when one variable needs to hold a collection of values. A fixed-size array declares its bounds as part of the declaration, while a dynamic array leaves the bounds open until code assigns them with ReDim. Arrays are useful for processing batches of values in memory, but they require more care than a single variable.

Option Explicit

Sub ShowArrayExample()
    Dim scores(1 To 3) As Long
    Dim index As Long

    ' Fill and display three sample scores.
    scores(1) = 82
    scores(2) = 91
    scores(3) = 87
    For index = LBound(scores) To UBound(scores)
        Debug.Print scores(index)
    Next index
End Sub

For dynamic arrays, declare the name and type first, then size the array with ReDim before using its elements. ReDim Preserve can keep existing values when resizing, subject to VBA’s array-dimension rules. If you only need to process a worksheet range, it may be simpler to work with a Range object or read the range into an array, depending on the task.

For Excel objects, declare the specific type and use Set to assign the reference. A variable declaration alone does not point it at a worksheet. This example reads numbers from B2:B10 on a worksheet named Sales and displays their sum. The cells are assumed to contain numeric values; text or error values would need additional handling.

Save a copy of the workbook before running this macro: it cannot be undone with Ctrl+Z. This particular procedure reads cells and shows a message, but preserving a copy is a sensible habit when running VBA.

Option Explicit

Sub ShowSalesTotal()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim salesTotal As Double

    ' Use the Sales sheet in the workbook containing this code.
    Set ws = ThisWorkbook.Worksheets("Sales")
    For rowNumber = 2 To 10
        salesTotal = salesTotal + ws.Cells(rowNumber, 2).Value
    Next rowNumber
    MsgBox "Sales total: " & Format(salesTotal, "0.00")
End Sub

ThisWorkbook means the workbook that contains the VBA code, which may differ from the active workbook. Qualifying the worksheet and cells avoids relying on whichever workbook or sheet happens to be selected. If the sheet name is misspelled or does not exist in that workbook, VBA reports an error when it tries to set ws.

Common Dim errors and how to fix them

“Variable not defined”: With Option Explicit enabled, check the spelling and confirm that a declaration appears in the relevant procedure or module.
Unexpected Variant: In a comma-separated declaration, add As type to every variable name that needs an explicit type.
Type mismatch: Check the value being assigned. A cell might contain text, an error, or a date-like value rather than the number your code expects.
Overflow: The number may be outside the chosen type’s range. For worksheet row counters, use Long rather than Integer.
Object variable not set: Assign object references with Set before using them, and check that the referenced workbook, sheet, or range exists.

Another misconception is that Dim itself creates a worksheet cell, changes a value in Excel, or runs a macro. It only declares a variable. Assignment statements, object operations, and other executable VBA code determine what happens when the procedure runs.

Saving and running VBA safely

Desktop Excel for Microsoft 365 on Windows supports VBA in the Visual Basic Editor. If a workbook contains macros, save it in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm); saving as a regular .xlsx file does not preserve VBA code. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros. Excel for Mac includes VBA support, although some Windows-specific features, including certain API calls and integrations, may not work the same way.

Only enable macros from sources you trust. A macro can change or delete workbook data, and VBA code can perform actions beyond ordinary cell formulas. Keep a backup before running unfamiliar code, inspect what it does, and avoid enabling macros just because a workbook asks you to. Ctrl+Z cannot reverse actions performed by VBA; on Mac, Command+Z is the usual undo shortcut, but it likewise does not undo a macro.

Frequently Asked Questions

What does Dim mean in Excel VBA?
Dim is the VBA keyword used to declare a variable or array. It names the value your code will use and can specify its data type, such as Long, String, or Worksheet.

Do I have to use Dim in VBA?
VBA can allow undeclared variables if Option Explicit is not enabled, treating them as Variants. Declaring variables with Dim and using Option Explicit is a safer practice because it helps catch spelling mistakes and makes code clearer.

What is the difference between Dim and Set?
Dim declares a variable. Set assigns an object reference, such as a worksheet, to an object variable after it has been declared. For example, declare ws As Worksheet, then use Set ws = ThisWorkbook.Worksheets("Sales").

Should I use Integer or Long for an Excel row number?
Use Long for Excel row numbers. VBA’s Integer has a much smaller range and cannot represent all row numbers in modern Excel worksheets.

Can Excel for the web run VBA macros?
No. Excel for the web does not run, create, or edit VBA macros. Use a desktop version of Excel, such as Excel for Microsoft 365 on Windows or Mac, to work with VBA.

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.