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 SubAn 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 SubIt 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
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 SubThe 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 SubThe rule for modern VBA
| To store… | Use | Instead of |
|---|---|---|
| A whole number (row, counter, quantity) | Long | Integer |
| A number with decimals (price, rate, amount) | Double | Single |
| A text | String | Variant |
| A date | Date | Variant |
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?.