Home

Bug in Excel VBA (Debug Mode + ANY Floating Point Operation => Overflow error)

%3CLINGO-SUB%20id%3D%22lingo-sub-894730%22%20slang%3D%22en-US%22%3EBug%20in%20Excel%20VBA%20(Debug%20Mode%20%2B%20ANY%20Floating%20Point%20Operation%20%3D%26gt%3B%20Overflow%20error)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894730%22%20slang%3D%22en-US%22%3E%3CP%3ESince%20couple%20of%20days%2C%20I%20am%20encountering%20a%20serious%20issue%20with%20Excel%20when%20I%20do%20anything%20in%20debug%20mode.%26nbsp%3B%20I%20looked%20for%20ways%20to%20report%20to%20MicroSoft%2C%20but%20a%20quick%20search%20didn't%20turn%20up%20any%20easy%20ways%20to%20directly%20report%20it.%26nbsp%3B%20Hoping%20someone%20here%20with%20good%20connections%20to%20MicroSoft%20can%20shed%20some%20light%20or%20help%20propagate%20the%20issue%20to%20MS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIssue%3C%2FSTRONG%3E%3A%20When%20any%20VBA%20macro%20is%20run%20in%20debug%20mode%2C%20the%20first%20time%20it%20encounters%20a%20floating%20point%20operation%2C%20it%20results%20in%20a%20%22Overflow%22%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExcel%20Versions%3C%2FSTRONG%3E%3A%20Excel%2016.29.1%20(19091700).%3C%2FP%3E%3CP%3EAlso%20replicated%20on%20different%20versions%20from%2016.24%20to%2016.29.%20(Not%20reproduced%20in%2016.23)%3C%2FP%3E%3CP%3E%3CSTRONG%3EOS%3C%2FSTRONG%3E%3A%20Mac%20OS%2010.14%20to%2010.14.6%20(Mojave)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhat%20I%20tried%3C%2FSTRONG%3E%3A%20Tried%20on%20different%20machines%20with%20slightly%20different%20Excel%20versions%20and%20OS%20versions.%26nbsp%3B%20Tried%20removing%20all%20Add-Ins%2C%20restart%20computer%2C%20close%20all%20other%20applications%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblem%20Repeats%20when%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CUL%3E%3CLI%3EProgram%20enters%20debug%20mode%20via%20a%20breakpoint%20-%20OR%20-%20program%20encounters%20first%20debug.print%20call%3C%2FLI%3E%3CLI%3EAny%20subsequent%20operation%20that%20accesses%20a%20variable%20declared%20as%20single%2Fdouble%2C%20or%20any%20math%20that%20implicitly%20or%20explicitly%20invokes%20a%20floating%20point%20operation%20(division%2C%20power%20etc.)%20results%20in%20an%20Overflow%20error.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETest%20Code%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3EHere%20are%20a%20few%20test%20macros%20that%20I%20wrote.%20Tried%20several%20other%20variants%20to%20convince%20myself%20that%20debug%20mode%20%2B%20floating%20point%20is%20the%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESummary%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3ESeems%20like%20a%20very%20serious%20issue%20to%20me.%20I%20can%20no%20longer%20rely%20on%20any%20macros%20that%20I%20wrote%20and%20distributed%20to%20my%20users%2C%20unless%20I%20remove%20all%20debug%20statements%20anywhere%20in%20my%20code.%20Also%2C%20cannot%20use%20debugger%20any%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20Test_WithDouble()%0A%20%20%20%20'Scenario%3A%20Original%20scenario.%0A%20%20%20%20'Result%3A%20Error%20on%20MsgBox%20line%0A%20%20%20%20'Guess%3AOnce%20program%20encounters%20debug%20mode%20(either%20via%20a%20breakpoint%20or%20a%20call%20to%20debug.pring)%20-%20subsequently%20any%20operation%20that%20implicitly%20or%20explicitly%20uses%20a%20type%20conversion%20seems%20to%20give%20error%0A%20%20%20%20Dim%20x%20As%20Double%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithoutDebug()%0A%20%20%20%20'Variation%3A%20What%20if%20debug.print%20is%20comented%20out%0A%20%20%20%20'Result%3A%20No%20Error%20(As%20long%20as%20it%20is%20not%20run%20in%20debug%20mode)%0A%20%20%20%20'Guess%3ANo%20issue%20unless%20debug%20mode%20is%20encountered%0A%20%20%20%20'If%20you%20use%20a%20breakpoint%20on%20x%3D1%20line%2C%20it%20DOES%20throw%20overflow%20error%0A%20%20%20%20Dim%20x%20As%20Double%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20'Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithDebugVariant()%0A%20%20%20%20'Variation%3A%20What%20if%20x%20is%20variant%3F%0A%20%20%20%20'Result%3A%20No%20error%0A%20%20%20%20'Guess%3A%20Perhaps%20no%20coersion%20of%20type%0A%20%20%20%20Dim%20x%20As%20Variant%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithDebugLong()%0A%20%20%20%20'Variation%3A%20What%20if%20x%20is%20variant%3F%0A%20%20%20%20'Result%3A%20No%20error%0A%20%20%20%20'Guess%3A%20Perhaps%20no%20coersion%20of%20type%0A%20%20%20%20Dim%20x%20As%20Long%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithDebugString()%0A%20%20%20%20'Variation%3A%20What%20if%20x%20is%20String%3F%0A%20%20%20%20'Result%3A%20No%20error%0A%20%20%20%20'Guess%3A%20Perhaps%20no%20coersion%20of%20type%0A%20%20%20%20Dim%20x%20As%20String%0A%20%20%20%20x%20%3D%20%221%22%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithDebugInteger()%0A%20%20%20%20'Variation%3A%20What%20if%20x%20is%20Integer%3F%0A%20%20%20%20'Result%3A%20No%20error%0A%20%20%20%20'Guess%3A%20Perhaps%20no%20coersion%20of%20type%0A%20%20%20%20Dim%20x%20As%20Integer%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%0A%0ASub%20Test_WithDebugSingle()%0A%20%20%20%20'Variation%3A%20What%20if%20x%20is%20Single%3F%0A%20%20%20%20'Result%3A%20Error%20on%20MsgBox%20line%0A%20%20%20%20'Guess%3A%20Perhaps%20no%20coersion%20of%20type%0A%20%20%20%20Dim%20x%20As%20Single%0A%20%20%20%20x%20%3D%201%0A%20%20%20%20Debug.Print%20%22Debug%20statement%20Used%22%0A%20%20%20%20MsgBox%20%22x%3D%22%20%26amp%3B%20x%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-894730%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Krishna_Krishna
Visitor

Since couple of days, I am encountering a serious issue with Excel when I do anything in debug mode.  I looked for ways to report to MicroSoft, but a quick search didn't turn up any easy ways to directly report it.  Hoping someone here with good connections to MicroSoft can shed some light or help propagate the issue to MS.

 

Issue: When any VBA macro is run in debug mode, the first time it encounters a floating point operation, it results in a "Overflow" error. 

 

Excel Versions: Excel 16.29.1 (19091700).

Also replicated on different versions from 16.24 to 16.29. (Not reproduced in 16.23)

OS: Mac OS 10.14 to 10.14.6 (Mojave)

 

What I tried: Tried on different machines with slightly different Excel versions and OS versions.  Tried removing all Add-Ins, restart computer, close all other applications etc.

 

Problem Repeats when:

  • Program enters debug mode via a breakpoint - OR - program encounters first debug.print call
  • Any subsequent operation that accesses a variable declared as single/double, or any math that implicitly or explicitly invokes a floating point operation (division, power etc.) results in an Overflow error.

 

Test Code:

Here are a few test macros that I wrote. Tried several other variants to convince myself that debug mode + floating point is the issue.

 

Summary:

Seems like a very serious issue to me. I can no longer rely on any macros that I wrote and distributed to my users, unless I remove all debug statements anywhere in my code. Also, cannot use debugger any more.

 

Option Explicit

Sub Test_WithDouble()
    'Scenario: Original scenario.
    'Result: Error on MsgBox line
    'Guess:Once program encounters debug mode (either via a breakpoint or a call to debug.pring) - subsequently any operation that implicitly or explicitly uses a type conversion seems to give error
    Dim x As Double
    x = 1
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithoutDebug()
    'Variation: What if debug.print is comented out
    'Result: No Error (As long as it is not run in debug mode)
    'Guess:No issue unless debug mode is encountered
    'If you use a breakpoint on x=1 line, it DOES throw overflow error
    Dim x As Double
    x = 1
    'Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithDebugVariant()
    'Variation: What if x is variant?
    'Result: No error
    'Guess: Perhaps no coersion of type
    Dim x As Variant
    x = 1
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithDebugLong()
    'Variation: What if x is variant?
    'Result: No error
    'Guess: Perhaps no coersion of type
    Dim x As Long
    x = 1
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithDebugString()
    'Variation: What if x is String?
    'Result: No error
    'Guess: Perhaps no coersion of type
    Dim x As String
    x = "1"
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithDebugInteger()
    'Variation: What if x is Integer?
    'Result: No error
    'Guess: Perhaps no coersion of type
    Dim x As Integer
    x = 1
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

Sub Test_WithDebugSingle()
    'Variation: What if x is Single?
    'Result: Error on MsgBox line
    'Guess: Perhaps no coersion of type
    Dim x As Single
    x = 1
    Debug.Print "Debug statement Used"
    MsgBox "x=" & x
End Sub

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies