Excel VBA - strange runtime error 6: Overflow

Question

[M2 MacBook Air, macOS Sequoia 15.6.1 (24G90), Microsoft Excel for Mac Office 365 Version 16.102.1 (25101829)]

In a new .xlsm workbook, with Personal.xlsb removed and no plug-ins enabled (i.e. fresh Excel) - post laptop reboot - when I run the following code I get "Run-time error '6': Overflow" with line "x = i" highlighted. Immediate window shows "1 1". If I enter the debugger and step through the code, it runs properly to completion. I'm totally flummoxed!

Sub test()
  Dim i As Integer
  Dim x As Single
  For i = 1 To 3
    x = i
    Debug.Print i & " " & x
  Next i
End Sub

Answer 1

Hi, Tom Kreutz

Thank you for reaching out to Microsoft Q&A forum.

Sorry for you encountering this issue. The problem appears to be related to how Excel for Mac handles certain type conversions when combined with UI operations like Debug.Print or MsgBox.

The Issue

The overflow error occurs because of a bug in the Excel for Mac VBA runtime where:

• Using Integer and Single data types together
• Combined with UI operations (Debug.Print, MsgBox)
• Causes incorrect type handling that leads to overflow errors

Possible Workarounds

Here are several workarounds that should resolve the issue:

Solution 1: Change Data Types (Recommended)

Sub test()
  Dim i As Long  ' Change Integer to Long
  Dim x As Double ' Change Single to Double
  For i = 1 To 3
    x = i
    Debug.Print i & " " & x
  Next i
End Sub

Solution 2: Use Explicit Type Conversion

Sub test()
  Dim i As Integer
  Dim x As Single
  For i = 1 To 3
    x = CSng(i)  ' Explicit conversion
    Debug.Print i & " " & x
  Next i
End Sub

Solution 3: Use Variant Types

Sub test()
  Dim i As Variant
  Dim x As Variant
  For i = 1 To 3
    x = i
    Debug.Print i & " " & x
  Next i
End Sub

Solution 4: Add DoEvents

Sub test()
  Dim i As Integer
  Dim x As Single
  For i = 1 To 3
    x = i
    Debug.Print i & " " & x
    DoEvents  ' Force UI refresh
  Next i
End Sub

Best Practice

For maximum compatibility on macOS:

• Using Long instead of Integer (modern VBA handles Long more efficiently anyway)
• Using Double instead of Single for floating-point numbers
• Avoiding mixing different numeric types in assignments when possible

The first solution (using Long and Double) is your most reliable fix and is considered a VBA best practice regardless of platform.

Hope this helps. Feel free to get back if you need further assistance.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

I'm delighted to learn that your many problems were simply licensing issues, and that all my worried suppositions about potential incompatibility between Office and new Apple Silicon chips were nothing more than unwarranted doom fantasies! Let that be a lesson to us all...

Answer 3

This bug seems to be related to the use of either MsgBox or Debug.Print. If I comment out the Debug.Print call within the loop, the routine runs to completion, even with a call to either MsgBox or Debug.Print after the loop (to verify subroutine completion). A call to MsgBox (instead of Debug.Print) within the loop also causes the run-time error.

Alex Blakenburg (MrExcel MVP) noticed that this bug appears to be very similar to that reported at:

https://www.mrexcel.com/board/threads/vba-data-type-problem-in-called-routines.1272616/

where removing calls to MsgBox and Debug.Print made run-time errors disappear.

Answer 4

Tom: I'm having a very similar problem. I have an Excel VBA macro I wrote for keeping records of a squash league I run. (My day job is nuclear physics.) Wrote it in 2007, have modified, tweaked it ever since. It has always run on whatever Mac I happen to have at the time. Until now. It has always run on the Intel Macs. It runs on an Air, with M1 chip. But I just acquired a Studio with an M3 UItra chip, it hangs.

If I step through the code (about 200 lines of code in the main loop) it runs well, including the MsgBox lines I put in for debugging. Stepped it through several iterations in the main loop. Worked perfectly. But as soon as I asked it to "continue" it started showing beachballs. If it did stop, it displayed the same error window you described.

I updated the OS to Tahoe this morning. The code ran for about 10 minutes (normal run time is about 2 minutes - pushing data around large arrays from spreadsheet... a very inefficient process, but it works) and displayed a ForceQuit window with a message that "your system has run out of application memory" showing Excel occupying 654.76 GB. Uh...

I'm wondering if the advanced chips, with multiple cores, are trying to parallelize things in a way that VBA just can't handle. (So stepping through will work, but trying to run at normal processing speed ... it gets confused.)

If I look at "About VBA" when the VBA window is active, I see a V 7.1 with copyright date of 2012. Do you know if there is a more recent version, and if so how to get it?

Thx for any information.... Jose Alonso.... ******@LBL.gov