Integer & Long

🔢 VBA Integer & Long Data Types

Learn how Integer and Long data types are used in Excel VBA to store whole numbers and perform numerical calculations.

BEGINNER • VBA DATA TYPES

📘 What are Integer and Long in VBA?

Integer and Long are VBA data types used to store whole numbers.

For example, if your program needs to store values such as 10, 100, 500, 1000 or 25000, you may use a numeric data type such as Integer or Long.

Simple definition:
Integer and Long are used to store whole-number values without decimal places.

🔢 What is Integer?

The Integer data type is designed to store whole numbers within its supported range.

A basic Integer variable can be declared using:

Dim age As Integer

You can then assign a whole number to the variable.

Dim age As Integer

age = 42

MsgBox age

🔢 What is Long?

The Long data type is also used for whole numbers, but it supports a much larger range of values than Integer.

A Long variable can be declared using:

Dim totalRows As Long

For example:

Dim totalRows As Long

totalRows = 100000

MsgBox totalRows

⚖️ Integer vs Long

Both data types store whole numbers, but their supported ranges are different.

Feature	Integer	Long
Stores	Whole numbers	Whole numbers
Decimal values	No	No
Size	2 bytes	4 bytes
Approx. range	-32,768 to 32,767	-2,147,483,648 to 2,147,483,647
Suitable for	Small whole numbers	Large whole numbers

💡 Simple Example

Suppose you want to store the number of employees in a small company.

Dim employeeCount As Integer

employeeCount = 150

The value 150 is comfortably within the Integer range.

However, Excel automation often deals with large row numbers, records and counters. In such situations, Long is generally more appropriate.

Dim rowNumber As Long

rowNumber = 100000

📊 Why Long is Important in Excel VBA

This is particularly important when working with Excel rows.

Modern Excel worksheets can contain more than 32,767 rows. Therefore, using Integer for a row counter can cause problems when the value becomes larger than the Integer range.

For example, a common VBA pattern is:

Dim i As Long

For i = 1 To 100000

    Cells(i, 1).Value = i

Next i

Here, Long is a better choice for the counter because the loop may process a large number of rows.

✅ When Can You Use Integer?

• Small counters
• Small numerical values
• Values that are guaranteed to stay within the Integer range
• Simple calculations involving small whole numbers

Example

Dim monthNumber As Integer

monthNumber = 12

🚀 When Should You Use Long?

• Excel row numbers
• Large counters
• Number of records
• Large calculations involving whole numbers
• Loops processing large datasets
• Database record counts
• Large Excel automation projects

Practical VBA tip:
When working with Excel row numbers and large counters, Long is generally the safer choice.

⚠️ Important Difference

Integer and Long do not store decimal values.

For example, the following variables are not appropriate for storing a value such as 125.75:

Dim amount1 As Integer
Dim amount2 As Long

For decimal values, you would normally consider another numeric data type such as Double.

🏢 Real-World VBA Example

Imagine you are creating an employee report in Excel. You may have:

• Employee count
• Row number
• Department number
• Year
• Monthly calculation counter

A simple example could be:

Sub EmployeeReport()

    Dim employeeCount As Long
    Dim rowNumber As Long
    Dim yearValue As Integer

    employeeCount = 50000
    rowNumber = 100000
    yearValue = 2026

    MsgBox "Employees: " & employeeCount & _
           vbNewLine & _
           "Row: " & rowNumber & _
           vbNewLine & _
           "Year: " & yearValue

End Sub

❌ Common Beginner Mistake

A common beginner mistake is automatically using Integer for every whole number.

Dim i As Integer

For i = 1 To 100000

    Cells(i, 1).Value = i

Next i

The problem is that the loop counter can exceed the maximum Integer value.

For Excel automation, this is usually better:

Dim i As Long

For i = 1 To 100000

    Cells(i, 1).Value = i

Next i

Remember:
For Excel row numbers and large loops, prefer Long rather than Integer.

🧠 What Will You Learn in the Detailed Integer & Long Topic?

This page gives you the basic understanding of Integer and Long. The detailed lesson will cover these topics with practical Excel VBA examples.

• Integer range and limitations
• Long range and limitations
• Integer vs Long in real projects
• Overflow errors
• Why Long is commonly used for Excel rows
• Using Long in For loops
• Using Long with Range and Cells
• Converting values using CInt()
• Converting values using CLng()
• Working with numeric values from Excel cells
• Integer and Long calculations
• Common mistakes
• Real-world Excel automation examples

🚀 Want to Learn Integer & Long in Depth?

Continue to the detailed lesson to understand ranges, overflow, conversions and practical Excel VBA examples.

Learn Integer & Long in Depth →

← Previous: String Next: Double →