Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    Since couple of days, I am encountering a serious issue with Excel when I do anything in debug mode. Appreciate if anyone can shed a light on it - or at least confirm that they can reproduce it (not sure if this is reproducible on windows Excel). I'm beginning to think it is a bug in Excel and hoping that the next version will fix it. I had no luck trying to find a way to report directly to Microsoft. Posting to a TechCommunity forum under a microsoft domain didn't help either.

    Issue: When any VBA macro is run in debug mode (or it goes through a debug.print statement in normal 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)

    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.
    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.

    Code below contains methods that reproduce the error and minor variants of the same method that do not produce the error.

    Any feedback is greatly appreciated.

    Regards

    Krishna


    Code:
    Option Explicit
    
    '============================================
    '
    '   Methods that DO produce error
    '
    '============================================
    '
    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 floating point operation (including accessing a floating point variable) seems to throw overflow error
        Dim x As Double
        x = 1
        Debug.Print "Debug statement Used"
        MsgBox "x=" & x
    End Sub
    Sub Test_WithImplicitFloatingPointCalc()
        'Scenario: Variable is long type, but implicit calc in floating point
        'Result: Error on MsgBox line
        Dim x As Long
        x = 1
        Debug.Print "Debug statement Used"
        x = 2 / 2 'Floating point AFTER a debug statement is the problem, not before
        MsgBox "x=" & x
    End Sub
    
    
    '============================================
    '
    '   Methods that do NOT produce error (unless run in debug mode)
    '
    '============================================
    '
    
    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_WithImplicitFloatingPointCalcBeforeDebug()
        'Scenario: Variable is long type, but implicit calc in floating point
        'Result: No Error (As long as it is not run in debug mode)
        Dim x As Long
        x = 1
        x = 2 / 2 'Floating point BEFORE a debug statement is not a problem
        Debug.Print "Debug statement Used"
        MsgBox "x=" & x
    End Sub
    
    Sub Test_WithDebugVariant()
        'Variation: What if x is variant?
        'Result: No Error (As long as it is not run in debug mode)
        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 (As long as it is not run in debug mode)
        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 (As long as it is not run in debug mode)
        Dim x As String
        x = "1"
        Debug.Print "Debug statement Used"
        MsgBox "x=" & x
    End Sub
Working...
X