How to convert a raw hex byte string to Long?

[Viper7]

Hello VBAers -

I am a VBA noob, so be patient.

I have an RS-232 device hooked up to my Win7 Excel 2010 spreadsheet leveraging the CommRead() and CommWrite() VBA functions I found on the web. The communication link works -- I can send VBA "Strings" of hex bytes to and from the device with my spreadsheet when "Buttons" are pressed.

The replies from our device come in as VBA "Strings" 4 hex bytes long.
So when the device sends me a 32 bit integer 1234567 decimal my VBA function gets a "String" of 4 non-printable hex bytes: 0x00 0x12 0xD6 and 0x87, which is 1234567 decimal.

I seek a VBA way to convert these bytes to the VBA "Long" integer.

Lots of VBA functions on the web appear to come close, but when I run them errors about Type Conversion incompatibility pop up. Some functions use Byte Arrays but I don't get how to convert from String to Byte Arrays with VBA.

Therefore, VBA tips to convert such a hex byte String to a Long would be appreciated.

Thanks in advance!
Viper

 

[Richard Schollar]

What are the exact values you get back? If it is a string in the form:

"0x00 0x12 0xD6 and 0x87"

Then you can convert to equivalent decimal via:

Dim strHex As String
Dim i As Long
Dim lngInt As Long
 
strHex = "0x00 0x12 0xD6 0x87"
strHex = Replace(Replace(strHex, "0x", ""), " ", "") 'change to Hex String
'Generate dec value:
 
For i = Len(strHex) To 1 Step -1
    lngInt = lngInt + 16 ^ (Len(strHex) - i) * CDec("&H" & Mid(strHex, i, 1))
    
Next i
 
MsgBox lngInt

 

[Viper7]

Thanks for the effort, Mr. Schollar, but my VBA "String" only
has 4 bytes inside it -- that is 4 characters. The Len() returns 4.

Each character is a (non-printable) hex value of 8 bits.
The hexadecimal value of each of those 4 characters is
in my original posting.

Best regards,
Viper7

 

[John_w]

Here's one way, if I understand correctly:

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (lpDest As Any, lpSource As Any, ByVal cbCopy As Long)


Sub test()
    Dim String4Bytes As String
    Dim longVal As Long
    
    String4Bytes = Chr(&H0) & Chr(&H12) & Chr(&HD6) & Chr(&H87)
    
    Debug.Print "String4Bytes = " & String4Bytes
    Debug.Print "Len(String4Bytes) = " & Len(String4Bytes)
        
    longVal = String4BytesToLong(String4Bytes)
    
    Debug.Print "longVal = " & longVal
    Debug.Print "Hex(longVal) = " & hex(longVal)
    
End Sub

Public Function String4BytesToLong(String4Bytes As String) As Long
    Dim s4 As String
    s4 = Mid(String4Bytes, 4, 1) & Mid(String4Bytes, 3, 1) & Mid(String4Bytes, 2, 1) & Mid(String4Bytes, 1, 1)
    CopyMemory ByVal VarPtr(String4BytesToLong), ByVal s4, 4
End Function

If speed is an issue, using CopyMemory instead of Mid to reverse the bytes in the String4BytesToLong() function would be much faster.

 

[Viper7]

WOW John, thanks!! It works perfect!!
You saved me many hours, and revealed some more
VBA functions / tricks that I can use in other code parts too.
Best regards!
Viper7

 

[Jaafar Tribak]

How about simply using Clng as follows :

Sub Test()

    Dim strHex As String
     
    strHex = " 0x00 0x12 0xD6 0x87"
    strHex = Replace(Replace(strHex, "0x", ""), " ", "")
    MsgBox CLng("&h" & strHex)
    
End Sub

 

[Viper7]

Thank you, Mr. Tribak.
A close look will show your solution starts with a strHex some 19 characters long. In my situation, the starting String is only 4 characters long. They are non-printable bytes / characters so I had to express them in my posting in the human readable 0x-- format. Therefore, the problem John solved for me is quite different than the 19 character String your solution offers.
Thanks anyway for trying, and best regards,
Viper7

 

[shg]

Another way:

Type uByt4: ai(1 To 4) As Byte: End Type
Type uLng4: i          As Long: End Type
 
Function Str2Lng(sInp As String) As Long
    Dim ub          As uByt4
    Dim ul          As uLng4
    Dim i           As Long
 
    For i = 1 To 4
        ub.ai(5 - i) = Asc(Mid(sInp, i, 1))
    Next i

    LSet ul = ub
    Str2Lng = ul.i
End Function
 
Sub test()
    Dim s           As String
 
    s = Chr(&H0) & Chr(&H12) & Chr(&HD6) & Chr(&H87)
    Debug.Print Str2Lng(s)
End Sub

 

[Viper7]

Nice!
That works perfectly too, "shg" so many thanks!
In case other VBA noobs read this, I figured out that the two Type declarations had to go in a new module (not intermingled with my "object" module.)
Thanks everyone.
Viper7

 

[Jaafar Tribak]

Viper7,

Thank you, Mr. Tribak.
In my situation, the starting String is only 4 characters long. They are non-printable bytes / characters so I had to express them in my posting in the human readable 0x-- format.

Sorry, I missed the non-printable bit .

shg,

That's a very cool use of the little known LSet statement and a good alternative to using the Copymemory API used by John. Thanks.