Is it possible to allow Integer Overflow in Excel VBA?

Question

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.

I'm translating some code from C that relies heavily on overflow for angle calculations. It stores angles as 2 byte integers. — TylerKehne, Mar 15 '16 at 22:02
Duplicate of [this article](http://stackoverflow.com/questions/26659256/how-to-turn-off-overflow-check-in-vba-for-ms-access) pretty much. — MikeDub, Mar 16 '16 at 00:27

chris neilsen · Answer 1 · 2016-03-16T00:17:40.637

2

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

edited Mar 16 '16 at 00:17

answered Mar 15 '16 at 23:01

chris neilsen

52,446
10
84
123

Yeah, that's basically what I ended up doing, with separate functions for addition and subtraction. Not what I was hoping for, but at least it works. – TylerKehne Mar 23 '16 at 02:23
You could get away with only one function, by negating a parameter, eg `a-b` = `MuFunc(a, -b)` – chris neilsen Mar 23 '16 at 02:30
Yes, for that edge case – chris neilsen Mar 23 '16 at 02:34

Alexander Bell · Answer 2 · 2016-03-15T22:41:55.753

0

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.

edited Mar 15 '16 at 22:41

answered Mar 15 '16 at 22:14

Alexander Bell

7,842
3
26
42

Misses the point. From OP _I want myFunc() to return **-30536**_ – chris neilsen Mar 15 '16 at 22:18
@chrisneilsen Thanks for your comment: please see the extended answer. Best regards, – Alexander Bell Mar 15 '16 at 22:27
The error handling is intriguing but is it tied to the function itself? I would want this to work for all modules. – TylerKehne Mar 15 '16 at 23:10
You can add the Overflow Error handling to any Function or Sub (I guess this is what you mean by "all modules"). You can, for example, create a Global Function and place it in a Code Module to make it universally accessible from any Worksheet module, etc. Hope this may help. Best regards, – Alexander Bell Mar 16 '16 at 00:34

score 0 · Answer 3 · answered Mar 15 '16 at 22:44

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

Is it possible to allow Integer Overflow in Excel VBA?

3 Answers3