-5

I would like to know how to use the bitconverter method in Excel VBA. I would like to convert 4 bytes each one placed in a difference cells in a 32 bit integer using BitConverter.ToInt32.

Can someone help me with an example to use in VBA? I think I am struggling with the syntax.

Thanks

KarmaWin
  • 24
  • 4
  • `I would like to convert 4 bytes to a 32 bit integer` and `I would like to convert 4 bytes to a 32 bit integer using BitConverter.ToInt32 in VBA` are [two different questions](https://meta.stackexchange.com/q/66377/147640). What do you want to do - find a way to call .NET's `BitConverter`'s methods from VBA or convert 4 bytes to an int in VBA? – GSerg Jun 06 '17 at 18:07
  • Thanks for your answer! My final goal is to to convert 4 bytes to an int in VBA. I thougth the most pratical would be calling .NET's BitConverter, but if there is another path I would also be glad to learn – KarmaWin Jun 06 '17 at 18:15
  • If you want to call .NET's `BitConverter`, then lookup how to call .NET code from VBA, don't ask people to write your code for you. – Mathieu Guindon Jun 06 '17 at 18:31

1 Answers1

3

  • With CopyMemory:

    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)

Public Function BytesToLong(b() As Byte) As Long
  CopyMemory BytesToLong, b(LBound(b)), 4
End Function

  • Without CopyMemory 1:

    Private Type thebytes
  b(1 To 4) As Byte
End Type

Private Type thelong
  l As Long
End Type


Public Function BytesToLong(b() As Byte) As Long
  Dim tb As thebytes, tl As thelong
  Dim lb As Long

  lb = LBound(b)
  tb.b(1) = b(lb)
  tb.b(2) = b(lb + 1)
  tb.b(3) = b(lb + 2)
  tb.b(4) = b(lb + 3)

  LSet tl = tb

  BytesToLong = tl.l
End Function

  • Without CopyMemory 2:

    Public Function BytesToLong(b() As Byte) As Long
  Dim lb As Long
  lb = LBound(b)

  If (b(lb + 3) And &H80) = &H80 Then
    BytesToLong = (b(lb) + b(lb + 1) * &H100& + b(lb + 2) * &H10000 + (b(lb + 3) And Not &H80) * &H1000000) Or &H80000000
  Else
    BytesToLong = b(lb) + b(lb + 1) * &H100& + b(lb + 2) * &H10000 + b(lb + 3) * &H1000000
  End If

End Function
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 1
    Nice spoonfeeder answer... would definitely upvote if it were a decent question. – Mathieu Guindon Jun 06 '17 at 18:32
  • @Mat'sMug I tried hard not to answer, but there appears to be no proper duplicates other than https://stackoverflow.com/q/7801080/11683 and https://stackoverflow.com/q/15782705/11683, so I thought, whatever. I have upvoted your comment. – GSerg Jun 06 '17 at 18:39
  • 1
    eh, what the heck, have it anyway =) – Mathieu Guindon Jun 06 '17 at 18:40
  • thanks a lot GSerg! I am really a beginner in VBA so there are a lot of things that I still don't grasp, I apologise if the question sounded too silly or too obvious, that was not my intention. – KarmaWin Jun 06 '17 at 18:58