I am converting Hex to ieee754 floating point numbers using the following
attached code using VBA. Is this a 'fast' way to do this operation? My code
spends 90% of the time performing this operation. I'm hoping there is a
faster way. Any comments are apprectiate.

Thanks!
Mark


'To type cast in VB you need to use the API to copy the contents of one data
type into another....
'turns hex data into floating point ieee754 standard
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(Destination As Any, Source As Any, ByVal Length As Long)
Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte
Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75
' note the order is reversed

bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4

' copy into the float
CopyMemory fResult, bytArray(0), 4
' print the result (5.75)
'Debug.Print fResult
Hex2Ieee754 = fResult

End Function

Hi Mark,

Same approach - just squeezed somewhat (saved 25% runtime on my
engine):
Function Hex2Ieee(b1 As String, b2 As String, _
b3 As String, b4 As String) As Single

Dim bytArray(0 To 3) As Byte
bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4
CopyMemory Hex2Ieee, bytArray(0), 4

End Function

HTH,
Bernd

I don't know if it is faster, but it doesn't require direct use of copymemory

Type MyHex
Lng As Long
End Type
Type MySingle
sng As Single
End Type

Function Hex2Ieee754(b1, b2, b3, b4)
Dim h As MyHex
Dim s As MySingle
h.Lng = Val("&H" & b1 & b2 & b3 & b4 & "&")
LSet s = h
Hex2Ieee754 = s.sng
End Function

Sub Test()
Debug.Print Hex2Ieee754("40", "B8", "00", "00")
End Sub


It peforms the same - don't know if the same is correct or not in terms of
trying to achieve whatever you are trying to achieve.

--
Regards,
Tom Ogilvy


"Mark HOlcomb" wrote:

I am converting Hex to ieee754 floating point numbers using the following
attached code using VBA. Is this a 'fast' way to do this operation? My code
spends 90% of the time performing this operation. I'm hoping there is a
faster way. Any comments are apprectiate.

Thanks!
Mark


'To type cast in VB you need to use the API to copy the contents of one data
type into another....
'turns hex data into floating point ieee754 standard
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(Destination As Any, Source As Any, ByVal Length As Long)
Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte
Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75
' note the order is reversed

bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4

' copy into the float
CopyMemory fResult, bytArray(0), 4
' print the result (5.75)
'Debug.Print fResult
Hex2Ieee754 = fResult

End Function

bytArray(3) = "&H" + b1

Don't know if it's faster, but maybe you can generate the string "&H" once,
and instead of adding two strings, use "&" instead.

Function Hex2Ieee754(b1, b2, b3, b4) As Single
Dim bytArray(0 To 3) As Byte
Const h As String = "&H"

bytArray(3) = h & b1
bytArray(2) = h & b2
bytArray(1) = h & b3
bytArray(0) = h & b4

CopyMemory Hex2Ieee754, bytArray(0), 4
End Function

Sub TestIt()
'// Both return 5.75
Debug.Print Hex2Ieee754("40", "B8", "00", "00")
Debug.Print Hex2Ieee754(40, "B8", 0, 0)
End Sub


--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Mark HOlcomb" wrote in message
...
I am converting Hex to ieee754 floating point numbers using the following
attached code using VBA. Is this a 'fast' way to do this operation? My
code
spends 90% of the time performing this operation. I'm hoping there is a
faster way. Any comments are apprectiate.

Thanks!
Mark


'To type cast in VB you need to use the API to copy the contents of one
data
type into another....
'turns hex data into floating point ieee754 standard
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(Destination As Any, Source As Any, ByVal Length As Long)
Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte
Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75
' note the order is reversed

bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4

' copy into the float
CopyMemory fResult, bytArray(0), 4
' print the result (5.75)
'Debug.Print fResult
Hex2Ieee754 = fResult

End Function

Probalby easier (and more intuitive to just pass in the Hex value since that
is what the function claims to do)

Type MyHex
Lng As Long
End Type
Type MySingle
sng As Single
End Type

Function Hex2Ieee754(i As Long)
Dim h As MyHex
Dim s As MySingle
h.Lng = i

LSet s = h
Hex2Ieee754 = s.sng
End Function

Sub Test()
Debug.Print Hex2Ieee754(&H40B80000)
End Sub

--
Regards,
Tom Ogilvy


"Dana DeLouis" wrote:

bytArray(3) = "&H" + b1

Don't know if it's faster, but maybe you can generate the string "&H" once,
and instead of adding two strings, use "&" instead.

Function Hex2Ieee754(b1, b2, b3, b4) As Single
Dim bytArray(0 To 3) As Byte
Const h As String = "&H"

bytArray(3) = h & b1
bytArray(2) = h & b2
bytArray(1) = h & b3
bytArray(0) = h & b4

CopyMemory Hex2Ieee754, bytArray(0), 4
End Function

Sub TestIt()
'// Both return 5.75
Debug.Print Hex2Ieee754("40", "B8", "00", "00")
Debug.Print Hex2Ieee754(40, "B8", 0, 0)
End Sub


--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Mark HOlcomb" wrote in message
...
I am converting Hex to ieee754 floating point numbers using the following
attached code using VBA. Is this a 'fast' way to do this operation? My
code
spends 90% of the time performing this operation. I'm hoping there is a
faster way. Any comments are apprectiate.

Thanks!
Mark


'To type cast in VB you need to use the API to copy the contents of one
data
type into another....
'turns hex data into floating point ieee754 standard
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(Destination As Any, Source As Any, ByVal Length As Long)
Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte
Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75
' note the order is reversed

bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4

' copy into the float
CopyMemory fResult, bytArray(0), 4
' print the result (5.75)
'Debug.Print fResult
Hex2Ieee754 = fResult

End Function

Hello Tom,

The following worksheet solution needs about 18% of the original
runtime:
Input is assumed to be in cells A1:D1 (2 char presentation of each
byte)

E1:
=SIGN(55.5-CODE(LEFT(A1,1)))*2^(MOD(CODE(LEFT(A1,1))-IF(CODE(LEFT(A1,1))57,55,48),8)*32+(CODE(RIGHT(A1 ,1))-IF(CODE(RIGHT(A1,1))57,55,48))*2+(CODE(LEFT(B1,1) )55)-127)*(1+(CODE(RIGHT(D1,1))-IF(CODE(RIGHT(D1,1))57,55,48)+(CODE(LEFT(D1,1))-IF(CODE(LEFT(D1,1))57,55,48))*16+(CODE(RIGHT(C1,1 ))-IF(CODE(RIGHT(C1,1))57,55,48))*256+(CODE(LEFT(C1, 1))-IF(CODE(LEFT(C1,1))57,55,48))*4096+(CODE(RIGHT(B1 ,1))-IF(CODE(RIGHT(B1,1))57,55,48))*65536+MOD(CODE(LEF T(B1,1))-IF(CODE(LEFT(B1,1))57,55,48),8)*1048576)/8388608)

Your approach reduces the runtime from 100% to about 10% (measured with
FastExcel on my computer) - and is by far preferrable, I think.

Regards,
Bernd

Maybe you could put it in a .xll add-in and make it even faster.

RBS


wrote in message
ups.com...
Hello Tom,

The following worksheet solution needs about 18% of the original
runtime:
Input is assumed to be in cells A1:D1 (2 char presentation of each
byte)

E1:
=SIGN(55.5-CODE(LEFT(A1,1)))*2^(MOD(CODE(LEFT(A1,1))-IF(CODE(LEFT(A1,1))57,55,48),8)*32+(CODE(RIGHT(A1 ,1))-IF(CODE(RIGHT(A1,1))57,55,48))*2+(CODE(LEFT(B1,1) )55)-127)*(1+(CODE(RIGHT(D1,1))-IF(CODE(RIGHT(D1,1))57,55,48)+(CODE(LEFT(D1,1))-IF(CODE(LEFT(D1,1))57,55,48))*16+(CODE(RIGHT(C1,1 ))-IF(CODE(RIGHT(C1,1))57,55,48))*256+(CODE(LEFT(C1, 1))-IF(CODE(LEFT(C1,1))57,55,48))*4096+(CODE(RIGHT(B1 ,1))-IF(CODE(RIGHT(B1,1))57,55,48))*65536+MOD(CODE(LEF T(B1,1))-IF(CODE(LEFT(B1,1))57,55,48),8)*1048576)/8388608)

Your approach reduces the runtime from 100% to about 10% (measured with
FastExcel on my computer) - and is by far preferrable, I think.

Regards,
Bernd

.... and if we optimize the input (each input half-byte as a character
in column E:L):
=SIGN(55.5-CODE(E1))*2^(MOD(CODE(E1)-IF(CODE(E1)57,55,48),8)*32+(CODE(F1)-IF(CODE(F1)57,55,48))*2+(CODE(G1)55)-127)*(1+(CODE(L1)-IF(CODE(L1)57,55,48)+(CODE(K1)-IF(CODE(K1)57,55,48))*16+(CODE(J1)-IF(CODE(J1)57,55,48))*256+(CODE(I1)-IF(CODE(I1)57,55,48))*4096+(CODE(H1)-IF(CODE(H1)57,55,48))*65536+MOD(CODE(G1)-IF(CODE(G1)57,55,48),8)*1048576)/8388608)

This is about as quick as Tom's UDF.

Regards,
Bernd