Home > Uncategorized > Convert two 16bit values to float in excel

Convert two 16bit values to float in excel

If you are ever trying to convert two modbus 16-bit words to a float in excel here is the code to do it.


Its based on the code I found here:
Link

Function convert_to_float(top As Long, bottom As Long)
 
Dim number As Long
number = top * (2 ^ 16)
number = number + bottom
 
Dim n As Single 'this is the resulting single number
Dim frac As Double 'fractional binary number from significant
Dim e As Integer 'exponent
Dim sig As Long 'significant integer
Dim i As Integer 'for for loops and whatnot
Dim sign As Integer
'get exponent
e = Int(number / (2 ^ 23)) 'shifts the bit down
sign = e And 256
e = e And 255
 
e = e - 127 'e is biased by 127 so it can stay unsigned (always positive)
sig = number And 8388607 ' to get lower 23 bits

frac = 1 'start with 1 so result is 1.
If e = -127 Then 'denormalized (0.fraction rather than 1.fraction)
e = -126
frac = 0 'start with 0 so result is 0..
End If
 
'calculate binary fraction from significant.
For i = 22 To 0 Step -1 'step through all 23 bits.
If (sig And 2 ^ i) > 0 Then frac = frac + 2 ^ -(23 - i) 'if the bit is a 1, the number will be non zero.
Next
 
n = (2 ^ e) * frac 'calculate the final number
If (number And -2147483648#) > 0 Then n = -n 'if sign bit is set, make negative.

convert_to_float = n 'return result

End Function

Categories: Uncategorized Tags:
  1. No comments yet.
  1. No trackbacks yet.


4 × two =