Is there a way to force circular integer overflow in Excel VBA?

Question

I am trying to convert some Java code to Excel and the required hashcode function generates an overflow error instead of wrapping a negative

Function FnGetStringHashCode(ByVal str As String) As Integer Dim result, i FnGetStringHashCode = 17 For i = 1 To Len(str) Dim c, a c = Mid(str, i, 1) a = AscW(c) FnGetStringHashCode = 31 * FnGetStringHashCode + a Next i End Function 

Is there a way to do this in Excel VBA?

Answer 1

Despite the lack of a built-in way to do this, the calculation is simple:

 Public Function coerceLongToInt(toCoerce As Long) As Integer Const MIN_INT As Long = -32768 Const MAX_INT As Long = 32767 Const NUM_INTS As Long = MAX_INT - MIN_INT + 1 Dim remainder As Long remainder = toCoerce Mod NUM_INTS If remainder > MAX_INT Then coerceLongToInt = remainder - NUM_INTS ElseIf remainder < MIN_INT Then coerceLongToInt = remainder + NUM_INTS Else coerceLongToInt = remainder End If End Function 

Is this the behavior you want right?

 ?coerceLongToInt(-32769) 32767 ?coerceLongToInt(-32768) -32768 ?coerceLongToInt(-1) -1 ?coerceLongToInt(0) 0 ?coerceLongToInt(1) 1 ?coerceLongToInt(32767) 32767 ?coerceLongToInt(32768) -32768 

You would use it as follows:

 Function FnGetStringHashCode(ByVal str As String) As Integer Dim result, i FnGetStringHashCode = 17 For i = 1 To Len(str) Dim c, a c = Mid(str, i, 1) a = AscW(c) FnGetStringHashCode = coerceLongToInt(31 * CLng(FnGetStringHashCode) + a) Next i End Function 

You need a "CLng" call to prevent the VBA from raising an overflow error when calculating an intermediate value (31 * [some integer> = 1058]).

Answer 2

I changed our script a bit. The main difference is the return of the type of your function. Now it returns Option . Since decimal is a subset of Variant, and it can store larger numbers than long, I think this is a good solution ( see VBA Data Types ) - I don’t know if explicit decimal return is possible. Here is the script

 Function FnGetStringHashCode(ByVal str As String) As Variant Dim tmp As Variant, c As String, a As Integer, i As Integer tmp = 17 For i = 1 To Len(str) c = Mid$(str, i, 1) a = AscW(c) tmp = 31 * tmp + a Next i FnGetStringHashCode = tmp End Function 

And a little test program

 Sub test() Debug.Print CStr(FnGetStringHashCode("dawdaedae")) End Sub