Excel VBA Integer: Range, Limits, and Type Choice

In Excel VBA, Integer is a signed whole-number data type that stores values from -32,768 through 32,767. It is valid, but Long is usually the safer choice for counters, worksheet row numbers, and other values that might exceed that range.

What Integer means in VBA

A variable declared As Integer holds a whole number within a fixed range. For example, a small status code or a count that is guaranteed to remain below 32,768 may fit. VBA does not make the variable larger automatically just because the value comes from an Excel worksheet or a calculation. If a value cannot be represented by the declared type, the code can raise an overflow error.

This is a VBA data type, not a worksheet cell format. Declaring a variable as Integer does not change how a cell looks, limit what a user can type into a cell, or convert a worksheet column to integer storage. The declaration applies only to the variable in the VBA procedure or module where it is declared.

For instance, Dim itemCount As Integer declares a variable named itemCount. After assigning it a value such as 120, that variable can be used in calculations and conditions, provided the resulting value stays in range. The name is your choice; the word Integer after As tells VBA what kind of value the variable is intended to hold.

Integer range and storage size

VBA Integer values use 16-bit signed storage. The range is -32,768 to 32,767, inclusive. The negative limit is one greater in magnitude than the positive limit because the signed representation reserves one value for zero and represents the negative side asymmetrically. The practical point is simple: values at 32,768 or below -32,768 do not fit.

That range can be surprisingly restrictive in an Excel workbook. A worksheet in current Excel versions can contain 1,048,576 rows and 16,384 columns. A VBA Integer cannot represent most row numbers in a full worksheet, even though those row numbers are perfectly valid in Excel. Use a Long for row and column indexes.

Other VBA numeric types cover different needs. Byte holds non-negative whole numbers from 0 to 255; Long is a signed 32-bit whole-number type; and Double is commonly used for numbers that may have fractional parts or a much larger range. Choosing a type is about both the values the code must handle and whether those values need decimals.

How to declare and use an Integer

Declare an Integer with the Dim statement followed by the variable name and As Integer. When Option Explicit is enabled, VBA also requires every variable to be declared, which helps catch spelling mistakes and unintended variables. A declaration can appear near the beginning of a procedure, before the statements that use the variable.

Dim attemptCount As Integer declares a local whole-number variable.
Dim resultCode As Integer may suit a small set of documented status values.
Dim rowNumber As Long is a better fit for a worksheet row index.

When a value is assigned, it must be compatible with the variable’s type. An Integer cannot preserve a fractional value as a fraction. If a calculation produces a decimal, decide deliberately whether to round, truncate, or keep the decimal in a type such as Double; do not rely on an implicit conversion to express the business meaning you want. For exact conversion behavior, use an appropriate conversion or rounding operation and check the result.

VBA’s type keywords are not interchangeable labels. An Integer variable is not the same as a Long variable with a shorter name, and using Integer does not make a value safer from bad input. Validate values from cells, forms, or external sources before using them in calculations that could exceed the variable’s range.

What happens when an Integer overflows

If code tries to store a value outside the Integer range, VBA can stop with run-time error 6, “Overflow.” For example, assigning 32,768 to an Integer is not a request for VBA to silently change that variable into a Long. The declared type remains Integer, so the value does not fit. The same risk applies when a calculation’s result is outside the type’s range.

A common Excel-specific example is a loop that uses an Integer to count through worksheet rows. It may appear to work on a small test sheet, then fail when it reaches a row number above 32,767. The bug can be hard to notice if the workbook is tested only with a few dozen records. Using Long from the beginning avoids that particular limit and makes the code compatible with the full worksheet row range.

Overflow is different from a blank cell or a cell containing text. Those inputs can cause other problems, such as a type mismatch, depending on how the code reads and converts them. A robust macro checks the input and uses a type that can represent the expected range; changing Integer to Long alone does not make arbitrary text or invalid values valid numbers.

Choosing Integer or Long in Excel macros

For most ordinary Excel VBA counters and indexes, choose Long. It can represent values from -2,147,483,648 through 2,147,483,647, comfortably covering Excel’s maximum row and column numbers. That makes it the practical default for loop variables, row references, record counts, and values returned by many Excel object properties.

Integer can still be reasonable when the range is genuinely small and defined, such as a bounded code, a small user-interface selection, or a value whose specification explicitly limits it to the Integer range. Even then, consider whether using Long would make the code easier to maintain. A future change to the allowed range can turn an apparently harmless Integer into an overflow bug.

Do not choose a type just because its name sounds like the kind of number you have. Ask these questions:

What is the smallest and largest valid value, including future workbook sizes?
Can the value be negative, or does it need a fractional part?
Does the value come from a worksheet, a user, an API, or another source that may supply unexpected input?
Will a calculation involving the value exceed the range even if the original value does not?

Example: loop through worksheet rows safely

This example uses Long for the row counter because Excel worksheets have far more than 32,767 rows. The loop reads the number of rows in the worksheet and displays it; change the worksheet name if your workbook does not have a sheet named “Sheet1.” Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

Sub ShowWorksheetRowCount()
    Dim rowNumber As Long
    Dim totalRows As Long

    ' Use the worksheet named Sheet1 in this workbook.
    totalRows = ThisWorkbook.Worksheets("Sheet1").Rows.Count

    ' Long can represent every Excel worksheet row number.
    For rowNumber = 1 To totalRows
        If rowNumber = totalRows Then
            MsgBox "Last row number: " & rowNumber
        End If
    Next rowNumber
End Sub

The loop is intentionally simple: it demonstrates the type choice, not an efficient way to process a large data set. In real macros, avoid doing unnecessary work once per row, and qualify worksheet references so VBA does not accidentally use whichever sheet happens to be active. For a loop that processes a range, a Long counter remains suitable even when the actual data occupies only a few rows today.

Integer versus Long, LongLong, and Double

Long is the usual comparison for Integer in Excel VBA. It has a much larger signed whole-number range and is appropriate for worksheet coordinates. The fact that modern Office can be 64-bit does not make VBA’s Long type 64-bit: Long remains a 32-bit type in both 32-bit and 64-bit Office.

LongLong is a 64-bit signed integer available in 64-bit VBA. It is for values that truly need that wider integer range, not a routine replacement for worksheet row numbers. When declaring Windows API parameters or pointer-sized values, VBA code may need LongPtr, whose size adapts to the Office platform. API declarations have additional compatibility rules, so use documentation for the specific API rather than substituting Integer, LongLong, or Long by guesswork.

Double is a floating-point type for values that can include fractions or require a much larger magnitude than Integer. It is not an exact substitute for an integer in every situation: floating-point numbers can have representation and rounding effects. For currency calculations, VBA also has a Currency type with fixed decimal scaling, but the right choice depends on the required precision and the calculation. Use an integer type when the quantity is inherently whole; use a suitable decimal-capable type when fractions matter.

Common Integer mistakes

Using Integer for worksheet rows. Excel row numbers can exceed 32,767, so use Long for row indexes and counters that may reach them.
Assuming VBA widens the variable automatically. An Integer declaration remains an Integer. Assigning a value outside its range can cause overflow rather than silently changing the declaration.
Confusing a cell’s appearance with its VBA type. Formatting a cell to show zero decimal places affects display, not the declared type of a VBA variable that reads its value.
Ignoring intermediate calculations. A result can exceed a type’s range even when each input seems reasonable. Consider the possible result, not only the values being added or multiplied.
Testing only small samples. A macro may work with 500 rows and fail when a workbook grows. Test against the realistic maximum or choose a type that safely covers it.

Excel version and platform notes

The Integer and Long ranges described here are VBA language types, not features that change with the number of worksheet rows in a particular workbook. Excel for Microsoft 365 on Windows supports VBA macros, and Excel for Mac also supports VBA, although some Windows-specific automation, API calls, and other platform features may not work the same way on Mac. The Integer range itself is not a workaround for those platform differences.

Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. To execute VBA, use a desktop version of Excel that supports macros. When a macro must run across Windows and Mac, keep platform-specific calls separate from ordinary worksheet logic, and verify the supported behavior for each platform.

Frequently Asked Questions

Is Integer 32-bit in Excel VBA?
No. VBA Integer is a signed 16-bit type with a range of -32,768 to 32,767. Long is 32-bit, including in 64-bit Office.

What is the maximum value for an Excel VBA Integer?
The maximum is 32,767. The minimum is -32,768. A value of 32,768 does not fit and can cause run-time error 6 when assigned to an Integer.

Should I use Integer or Long for a row number?
Use Long. Excel worksheets can have 1,048,576 rows, which is far beyond Integer’s maximum. Long also works for ordinary counters that might grow as a workbook grows.

Can an Integer variable hold a decimal?
It cannot retain a fractional part as a fraction. If a decimal is assigned, VBA converts it to an integer value; conversion and rounding details matter, especially around halfway values. Use a decimal-capable type such as Double if the fraction itself must be preserved.

Does using Integer make a VBA macro faster?
It is not a good reason to choose Integer. For typical Excel macros, clarity and a type with a safe range matter more than trying to save a small amount of storage. Use Long for common counters and worksheet indexes unless a specific requirement calls for another type.

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.