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:
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 2002
Have I made a schoolboy error somewhere? Why does the single 'read backwards'?
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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | A | B | C | D | ||||||
2 | 65 | 66 | 67 | 68 | Decimal place | Binary Fraction Converter | Binary Fract Value | |||
3 | 01000001 | 01000010 | 01000011 | 01000100 | 1 | 0.5 | 1 | |||
4 | 2 | 0.25 | 0 | |||||||
5 | 3 | 0.125 | 0 | |||||||
6 | 4 | 0.0625 | 0 | |||||||
7 | 5 | 0.03125 | 0 | |||||||
8 | Reverse | 6 | 0.015625 | 1 | ||||||
9 | D | C | B | A | 7 | 0.0078125 | 1 | |||
10 | 68 | 67 | 66 | 65 | 8 | 0.00390625 | 0 | |||
11 | 01000100 | 01000011 | 01000010 | 01000001 | 9 | 0.001953125 | 1 | |||
12 | 10 | 0.000976563 | 0 | |||||||
13 | 01000100010000110100001001000001 | Concatenated DCBA | 11 | 0.000488281 | 0 | |||||
14 | 12 | 0.000244141 | 0 | |||||||
15 | 0 | Sign bit | 0 | 13 | 0.00012207 | 0 | ||||
16 | 10001000 | Exponent | 136 | 14 | 6.10352E-05 | 1 | ||||
17 | 10000110100001001000001 | Fraction (binary) | 1.10000110100001001000001 | 15 | 3.05176E-05 | 0 | ||||
18 | 16 | 1.52588E-05 | 0 | |||||||
19 | 17 | 7.62939E-06 | 1 | |||||||
20 | 18 | 3.8147E-06 | 0 | |||||||
21 | Resulting single value | 19 | 1.90735E-06 | 0 | ||||||
22 | 20 | 9.53674E-07 | 0 | |||||||
23 | 781.0352173 | 21 | 4.76837E-07 | 0 | ||||||
24 | 22 | 2.38419E-07 | 0 | |||||||
25 | 23 | 1.19209E-07 | 1 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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'?