Variable of Single datatype used for binary extraction

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I was involved in a question yesterday where the OP was using a Single variable to extract data from a file opened using the Open statement with Binary Access.

After it filtered thru to my brain that the Single was being used to extract 4 bytes of data from the file at a time I decided to investigate further as to how a Single is constructed in VBA (it uses the IEEE 4 byte specification).

I created a Text file with the contents ABCD and saved it down at "C:\Test\Test.txt". As this isn't unicode, the total file length is 4 bytes (confirmed with the FileLen function). Next I ran the following code:


Code:
Type SingHldr   'UDT
    s As Single
End Type
 

Type SingStr    'UDT
    C1 As Byte
    C2 As Byte
    C3 As Byte
    C4 As Byte
End Type
 

Sub ReadFile()
Dim strF As String
Dim i As Integer
Dim s As Single
Dim splt As SingStr
Dim hldr As SingHldr
 
strF = "c:\Test\Test.txt"

i = FreeFile
 
Open strF For Binary Access Read As #i
    Get #i, , s   'use Single s to hold data (4 bytes)
Close #i
 
 
MsgBox s   'returns 781.0352
 
 
hldr.s = s   'place s value in UDT so LSet statement next will work

LSet splt = hldr   'populate binary data in s into byte variables in UDT variable splt
 
MsgBox Chr$(splt.C1)   'returns A

MsgBox Chr$(splt.C2)   'returns B

MsgBox Chr$(splt.C3)   'returns C

MsgBox Chr$(splt.C4)   'returns D

End Sub

OK, what the above shows is that the first byte of s contains "A", second byte contains "B", third contains "C", 4th contains "D" as you would expect.

So, next I wanted to calculate how ABCD (in binary) translated into the Single return value of 781.0352. I checked out the IEEE specification of the Single floating point datatype and from this determined that the first 8 bits of A and the first bit of B would determine the sign of the value and the exponent component. The remaining 23 bits (7 bits of B and 8 bits each for C and D) comprised the binary fraction.

However, this isn't the case (and this is what i don't understand). To return the Single value of 781.0352, you need to reverse the positions of the characters to be DCBA (ie so the single reads the last byte first and then sequentially backwards):
Excel Workbook
ABCDEFGH
1ABCD
265666768Decimal placeBinary Fraction ConverterBinary Fract Value
30100000101000010010000110100010010.51
420.250
530.1250
640.06250
750.031250
8Reverse60.0156251
9DCBA70.00781251
106867666580.003906250
110100010001000011010000100100000190.0019531251
12100.0009765630
1301000100010000110100001001000001Concatenated DCBA110.0004882810
14120.0002441410
150Sign bit0130.000122070
1610001000Exponent136146.10352E-051
1710000110100001001000001Fraction (binary)1.10000110100001001000001153.05176E-050
18161.52588E-050
19177.62939E-061
20183.8147E-060
21Resulting single value191.90735E-060
22209.53674E-070
23781.0352173214.76837E-070
24222.38419E-070
25231.19209E-071
Sheet1
Excel 2002
Cell Formulas
RangeFormula
A3=RIGHT("00000"&DEC2BIN(A2),8)
A23=((-1)^D15)*(2^(D16-127))*(1+SUMPRODUCT(G3:G25*H3:H25))
G3=1/(2^F3)
H3=MID($A$17,$F3,1)


Have I made a schoolboy error somewhere? Why does the single 'read backwards'?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Richard

That's a choice made by Intel. It's an endianness choice.

You had to choose how to store the numbers in memory and Intel chose that way. It has been like that in the this Intel processor family for ages.

The first microprocessors I programmed were the 8080 and 8085 (8 bit processors) and then 8086, 80186 and 80286 (16 bit processors). This was 30 years ago and they already used this way to store values. This is called little-endian. At the time I also worked with a microprocessor from Motorolla, the 6800 and it was the other way round, called big-endian.

Why did Intel choose little-endian?

The advantage is the possibility to refer to values in memory with types with different sizes in a transparent way. Ex:

You have in memory 12 00 00 00 00 00 00 00 in address 10000000H

If you read that address using different variable types, with different sizes:

byte: 12
word: 12 00
dword: 12 00 00 00
qword: 12 00 00 00 00 00 00 00

They all refer to the same number, 12 hex (18 decimal).

This is one advantage of the little-endian.

You may google endianness or little-endian to get other information.

Check with an easier example with a long:

Code:
Option Explicit
Type LongHldr   'UDT
    l As Long
End Type
 
Type BytStr    'UDT
    C1 As Byte
    C2 As Byte
    C3 As Byte
    C4 As Byte
End Type
 
Sub AnalyseLong()
Dim splt As BytStr
Dim Lng As LongHldr
 
Lng.l = &H34567890 ' this is decimal 878082192 in hexadecimal
 
LSet splt = Lng   'populate binary data in l into byte variables in UDT variable splt
 
MsgBox Hex(Lng.l) & " : " & _
    Format(Hex(splt.C4), "00") & " " & Format(Hex(splt.C3), "00") & " " & _
    Format(Hex(splt.C2), "00") & " " & Format(Hex(splt.C1), "00")
 
End Sub
 
Last edited by a moderator:
Upvote 0
Andrew & Pedro - many thanks for taking the time to look at my (lengthy) question.

Pedro thank you very much for the example - that really helped as it explains why it happens.

This doesn't apply to string variables being used to store the binary data, just to numeric variables?
 
Upvote 0
This doesn't apply to string variables being used to store the binary data, just to numeric variables?

Yes, strings are sequences of characters and are stored in the order they appear in the string. Each character may, however, have more than 1 byte, like in the case of Unicode.

ex. if you use Unicode, like we do in Excel

character "1": 31 (hex)
character "€": 20AC (hex)

string "1€" stored as: 31 00 AC 20

Although the characters are stored in order, each character is stored following the little-endian.

Code:
Sub AnalyseString()
Dim s1 As String, s2 As String, s As String
Dim byt() As Byte
 
s1 = ChrW(&H31)   ' character "1"
s2 = ChrW(&H20AC) ' Euro symbol
s = s1 & s2
 
byt = s
 
MsgBox s & " : " & _
    Format(Hex(byt(0)), "00") & " " & Format(Hex(byt(1)), "00") & " " & _
    Format(Hex(byt(2)), "00") & " " & Format(Hex(byt(3)), "00")
 
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top