This article was previously published under Q189323
Visual Basic for Applications only supports signed 2- and 4-byte Integers,while other languages, such as C, support both signed and unsignedIntegers. This article provides conversion functions between signed andunsigned 4-byte Integers and between signed and unsigned 2-byte Integers.
In VBA, the range of Integer values is from -32768 to +32767, and for Longvalues from -2147483648 to 2147483647. When making API calls or calling aDLL written in C, you may be requested to pass in or receive unsignedvalues in the range of 0 to 65535 or 0 to 4294967296. The conversionfunctions provided below convert an unsigned Integer to a Long and alsofrom an unsigned Long to a Double for purposes of input and display orother calculations.
The function takes a Double containing a value in the range of an unsignedLong and returns a Long that you can pass to an API that requires anunsigned Long.
The function takes an unsigned Long from an API and converts it to a Doublefor display or arithmetic purposes.
The function takes a Long containing a value in the range of an unsignedInteger and returns an Integer that you can pass to an API that requires anunsigned Integer.
The function takes an unsigned Integer from and API and converts it to aLong for display or arithmetic purposes.
Declare Function MyAPI Lib "xxx" (Value As Long) As Long Dim uResult As Long uResult = MyAPI(UnsignedToLong(3300000000)) Debug.Print "Return Code: " & LongToUnsigned(uResult)
In the above example, the MyAPI API accepts an unsigned Long as a parameterand returns an unsigned Long as a result code. Because VBA only understandssigned Longs, the DECLARE statement uses signed Longs. The UnsignedToLong
function converts a number outside the range of signed Long (but within the
range of an unsigned Long) into a signed Long for purposes of calling theAPI. The LongToUnsigned function performs the opposite conversion.
Without these functions, the input argument would have to have beenspecified as a signed Long, in this case -994967296, and the return valuewould have to be displayed as a signed value, possibly also as a negativenumber and non-intuitive.
Step by Step Example
Create a new VBA project.
Add the following code to a Module:
Option Explicit Private Const OFFSET_4 = 4294967296# Private Const MAXINT_4 = 2147483647 Private Const OFFSET_2 = 65536 Private Const MAXINT_2 = 32767 Function UnsignedToLong(Value As Double) As Long If Value < 0 Or Value >= OFFSET_4 Then Error 6 ' Overflow If Value <= MAXINT_4 Then UnsignedToLong = Value Else UnsignedToLong = Value - OFFSET_4 End If End Function Function LongToUnsigned(Value As Long) As Double If Value < 0 Then LongToUnsigned = Value + OFFSET_4 Else LongToUnsigned = Value End If End Function Function UnsignedToInteger(Value As Long) As Integer If Value < 0 Or Value >= OFFSET_2 Then Error 6 ' Overflow If Value <= MAXINT_2 Then UnsignedToInteger = Value Else UnsignedToInteger = Value - OFFSET_2 End If End Function Function IntegerToUnsigned(Value As Integer) As Long If Value < 0 Then IntegerToUnsigned = Value + OFFSET_2 Else IntegerToUnsigned = Value End If End Function