CODEWITHSUNDEEP
vbaexcelinteger-overflow
TylerKehne
TylerKehne

Reputation: 361

Is it possible to allow Integer Overflow in Excel VBA?

Pretty simple, but I couldn't find anything by Googling. An example of what I want to happen:

Function myFunc()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer

    a = 20000
    b = 15000
    c = a + b

    myFunc = c
End Function

I want myFunc() to return -30536 instead of throwing an overflow exception. I know I could write a function that does that, but I've written a bunch of code for a project with the assumption that overflow was allowed, so I'm hoping there's a quick fix.

EDIT: I don't need help coming up with a function that solves the overflow issue with type conversions. I have one already; I just want to avoid changing hundreds of addition and subtraction operations. I'm also bit frustrated that VBA seems to go out of its way to disable overflow functionality--it should let the user decide if they want to use it or not.

Upvotes: 3

Views: 2252

Answers (3)

chris neilsen
chris neilsen

Reputation: 53137

I would suggest writing MyFunc to do the math as Long, and test for integer "overflow" and adjust 

Function MyFunc(a As Integer, b As Integer) As Integer
    Dim sum As Long
    Const Mask As Integer = -1

    sum = CLng(a) + CLng(b)
    If sum > 32767 Then
        sum = sum - 65536
    ElseIf sum < -32768 Then
        sum = sum + 65536
    End If
    MyFunc = sum
End Function

Test with

Sub zx()
    Debug.Print MyFunc(20000, 15000)

End Sub

Upvotes: 2

rheitzman
rheitzman

Reputation: 2297

Use typical VBA error handler but tests for your case.

            Option Explicit

            Sub test()
                MsgBox myFunc
            End Sub

            Function myFunc()
            On Error GoTo Local_err
                Dim a As Integer
                Dim b As Integer
                Dim c As Integer

                a = 20000
                b = 15000
                c = a + b

                myFunc = c
Local_exit:
                Exit Function
Local_err:
                If Err = 6 Then
                    myFunc = -30536
                Else
                    MsgBox Err & " " & Err.Description
                    '  myFunc = whatever error value to return
                End If
                Resume Local_exit
            End Function

Upvotes: 0

Alexander Bell
Alexander Bell

Reputation: 7918

In order to prevent Integer overflow in your Excel VBA code, you may use the custom Function to perform the Integer to Long type casting like shown below:

Sub TestIntegerConversion()
    Debug.Print myFuncLong(20000, 15000)
End Sub

Function myFuncLong(a As Integer, b As Integer) As Long
    myFuncLong = CLng(a) + CLng(b)
End Function

or without using custom Function in a simple form like this:

Sub PreventOverflow()
    Dim a As Integer
    Dim b As Integer
    a = 20000
    b = 15000

    Debug.Print CLng(a) + CLng(b)
End Sub

Alternatively, you may write your own custom function, which should implement that "overflow math" (you have somehow to specify using plain math notation how to get the number -30536 from 35000) and return the result either as Long, or String. Possible implementation is shown below (note: Overflow exception number is 6)

Sub OverflowCustomMath()
    Dim a As Integer
    Dim b As Integer
    Dim c As Long
    a = 20000
    b = 15000

On Error GoTo Err:
    Debug.Print a + b
Err:
    If (Err.Number = 6) Then
        'apply your custom overflow math, as for e.g.
        Debug.Print CLng(a) + CLng(b)
    End If
End Sub

Hope this may help.

Upvotes: 0

Related Questions