Why AI Gets VBA Variables Wrong

Reading time: 3 minutes

In How to Declare Variables in VBA, you saw why you should write Option Explicit, and how to declare each variable with Dim in the right family: whole numbers, decimal numbers, text and dates.

Now ask ChatGPT, Claude or Copilot to write a VBA macro. Whatever your request, there is a good chance that the code declares its variables with Integer or Single. The macro works. But these types are obsolete, and the AI never tells you.

1. Whatever you ask, the AI uses Integer and Single

Ask for a macro to fill a column, to calculate a total or to loop on the rows of a table. You will often get declarations like these:

Sub CalculateTotal()
 Dim i As Integer
 Dim total As Single
 For i = 1 To 100
 total = total + Cells(i, 1)
 Next i
 MsgBox total
End Sub

An AI learns from what is already written on the web. And the largest amount of VBA code on the web comes from the forums of the early 2000s, at the time of VBA 6. At that time, computers had little memory, and saving two bytes with an Integer instead of a Long was a good habit.

Since Excel 2010 and VBA 7, this reason no longer exists. But the AI keeps on repeating these old habits, because that is what it read most.

2. The real problem: VBA converts them, and works twice

Since VBA 7, VBA works with Long for whole numbers and Double for decimal numbers. A Microsoft document about VBA says it clearly: in recent versions, VBA converts all Integer values to Long, even when they are declared as Integer. This sentence is quoted on the Microsoft support site: Microsoft Q&A: Dim variable as Long vs. Integer.

So when a variable is declared as Integer or Single, VBA converts the value to Long or Double to make the calculation, then converts the result back to Integer or Single to store it. Each time. In a loop of thousands of rows, the processor does this extra work thousands of times, for nothing.

I discovered it many years ago with a bug: a calculation with Single gave a result that was not coherent. A developer explained to me how VBA works behind the scenes, and I have written it in all my VBA books since then.

And this is the real point: the AI does not know this information, so it does not correct its own code. Most people copy the code of the AI as it is, with these old declarations. Replacing Integer with Long, and Single with Double, already changes the way you write VBA.

3. Integer also stops at 32,767

There is a second problem with Integer: it cannot go above 32,767. A loop on a large table stops with an error:

Sub WriteWithInteger()
 Dim i As Integer
 For i = 1 To 50000
 Cells(i, 1) = i
 Next i
End Sub

It stops at row 32,767 with the error Run-time error 6: Overflow. With Long, the limit is more than 2 billion, and the same loop works:

Sub WriteWithLong()
 Dim i As Long
 For i = 1 To 50000
 Cells(i, 1) = i
 Next i
End Sub
VBA Run-time error 6 Overflow: the Integer loop stops at row 32767

4. Single also rounds your numbers

Single is used for numbers with decimals, but it only keeps about 7 significant digits. Beyond that, VBA rounds the number, without any warning:

Sub TestSingle()
 Dim total As Single
 total = 123456.78
 Debug.Print total ' displays 123456.8
End Sub

The amount has changed by two cents, and in a long calculation, these small errors add up. In a budget or an invoice, this is not acceptable. Double keeps about 15 significant digits, and the same code displays 123456.78:

Sub TestDouble()
 Dim total As Double
 total = 123456.78
 Debug.Print total ' displays 123456.78
End Sub

The rule for modern VBA

To store…UseInstead of
A whole number (row, counter, quantity)LongInteger
A number with decimals (price, rate, amount)DoubleSingle
A textStringVariant
A dateDateVariant

Tell it to the AI

The simplest way is to say it in your prompt, every time you ask for VBA code:

/vba
Start the module with Option Explicit and declare all the variables.
Use Long for whole numbers and Double for decimals. Never use Integer or Single.

And if the AI gives you a macro with Integer or Single, you now know what to change. See also our Prompt Tips for Excel, and check the code before running it: Does AI Always Give the Right Result in Excel?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Why AI Gets VBA Variables Wrong

Reading time: 3 minutes

In How to Declare Variables in VBA, you saw why you should write Option Explicit, and how to declare each variable with Dim in the right family: whole numbers, decimal numbers, text and dates.

Now ask ChatGPT, Claude or Copilot to write a VBA macro. Whatever your request, there is a good chance that the code declares its variables with Integer or Single. The macro works. But these types are obsolete, and the AI never tells you.

1. Whatever you ask, the AI uses Integer and Single

Ask for a macro to fill a column, to calculate a total or to loop on the rows of a table. You will often get declarations like these:

Sub CalculateTotal()
 Dim i As Integer
 Dim total As Single
 For i = 1 To 100
 total = total + Cells(i, 1)
 Next i
 MsgBox total
End Sub

An AI learns from what is already written on the web. And the largest amount of VBA code on the web comes from the forums of the early 2000s, at the time of VBA 6. At that time, computers had little memory, and saving two bytes with an Integer instead of a Long was a good habit.

Since Excel 2010 and VBA 7, this reason no longer exists. But the AI keeps on repeating these old habits, because that is what it read most.

2. The real problem: VBA converts them, and works twice

Since VBA 7, VBA works with Long for whole numbers and Double for decimal numbers. A Microsoft document about VBA says it clearly: in recent versions, VBA converts all Integer values to Long, even when they are declared as Integer. This sentence is quoted on the Microsoft support site: Microsoft Q&A: Dim variable as Long vs. Integer.

So when a variable is declared as Integer or Single, VBA converts the value to Long or Double to make the calculation, then converts the result back to Integer or Single to store it. Each time. In a loop of thousands of rows, the processor does this extra work thousands of times, for nothing.

I discovered it many years ago with a bug: a calculation with Single gave a result that was not coherent. A developer explained to me how VBA works behind the scenes, and I have written it in all my VBA books since then.

And this is the real point: the AI does not know this information, so it does not correct its own code. Most people copy the code of the AI as it is, with these old declarations. Replacing Integer with Long, and Single with Double, already changes the way you write VBA.

3. Integer also stops at 32,767

There is a second problem with Integer: it cannot go above 32,767. A loop on a large table stops with an error:

Sub WriteWithInteger()
 Dim i As Integer
 For i = 1 To 50000
 Cells(i, 1) = i
 Next i
End Sub

It stops at row 32,767 with the error Run-time error 6: Overflow. With Long, the limit is more than 2 billion, and the same loop works:

Sub WriteWithLong()
 Dim i As Long
 For i = 1 To 50000
 Cells(i, 1) = i
 Next i
End Sub
VBA Run-time error 6 Overflow: the Integer loop stops at row 32767

4. Single also rounds your numbers

Single is used for numbers with decimals, but it only keeps about 7 significant digits. Beyond that, VBA rounds the number, without any warning:

Sub TestSingle()
 Dim total As Single
 total = 123456.78
 Debug.Print total ' displays 123456.8
End Sub

The amount has changed by two cents, and in a long calculation, these small errors add up. In a budget or an invoice, this is not acceptable. Double keeps about 15 significant digits, and the same code displays 123456.78:

Sub TestDouble()
 Dim total As Double
 total = 123456.78
 Debug.Print total ' displays 123456.78
End Sub

The rule for modern VBA

To store…UseInstead of
A whole number (row, counter, quantity)LongInteger
A number with decimals (price, rate, amount)DoubleSingle
A textStringVariant
A dateDateVariant

Tell it to the AI

The simplest way is to say it in your prompt, every time you ask for VBA code:

/vba
Start the module with Option Explicit and declare all the variables.
Use Long for whole numbers and Double for decimals. Never use Integer or Single.

And if the AI gives you a macro with Integer or Single, you now know what to change. See also our Prompt Tips for Excel, and check the code before running it: Does AI Always Give the Right Result in Excel?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *