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
– TylerKehne, Commented Mar 15, 2016 at 22:02

chris neilsen · Accepted Answer · 2016-03-16 00:17:40Z

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, 2016 at 0:17

answered Mar 15, 2016 at 23:01

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TylerKehne Over a year ago

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.

chris neilsen Over a year ago

You could get away with only one function, by negating a parameter, eg a-b = MuFunc(a, -b)

chris neilsen Over a year ago

Yes, for that edge case

Alexander Bell · Accepted Answer · 2016-03-15 22:41:55Z

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, 2016 at 22:41

answered Mar 15, 2016 at 22:14

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

4 Comments

chris neilsen Over a year ago

Misses the point. From OP I want myFunc() to return -30536

Alexander Bell Over a year ago

@chrisneilsen Thanks for your comment: please see the extended answer. Best regards,

TylerKehne Over a year ago

The error handling is intriguing but is it tied to the function itself? I would want this to work for all modules.

Alexander Bell Over a year ago

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,

rheitzman · Accepted Answer · 2016-03-15 22:44:27Z

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

Collectives™ on Stack Overflow

Is it possible to allow Integer Overflow in Excel VBA?

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related