Write hex values to binary file in VBA Excel

[Fractalis]

Hello to all,


I want to write the hexadecimal values to a binary file in order they look the same when I open in hex editor.

My current code is this:

Sub Write2Binary()
Dim i As Integer
Dim nFileNum As Integer
Dim sFilename As String


sFilename = "D:\OutputPath\Test.bin"


strBytes = "F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5"
arrBytes = Split(strBytes)


nFileNum = FreeFile


Open sFilename For Binary Lock Read Write As #nFileNum 


  For i = LBound(arrBytes) To UBound(arrBytes)
    ' No byte position is specified so writing begins at byte 1
    Put #nFileNum , , arrBytes(i)
  Next i


Close #nFileNum 
    
End Sub

This code produces the following binary file that when I open it in a Hex editor looks like this:

08 00 02 00 46 33 08 00 02 00 41 31 08 00 02 00
30 32 08 00 02 00 30 30 08 00 02 00 30 34 08 00 
02 00 30 30 08 00 02 00 38 44 08 00 02 00 32 34 
08 00 02 00 34 34 08 00 02 00 43 33 08 00 02 00 
38 43 08 00 02 00 30 33 08 00 02 00 38 33 08 00 
02 00 34 39 08 00 02 00 32 36 08 00 02 00 39 32 
08 00 02 00 42 35

That is different to the content I want to have in binary file. When I open the file in Hex editor I like to see the following content:

F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5

How can I do this?

Thaks for any help.

 

[Cetico]

For each value you are using a so called binary DWORD which consists of 32 bits. Bits 0-30 contain the value, bit 31 is the sign bit, either set (negative) or not set (positive). As of your expected outcome your intention was to use a WORD (16 bits) for each value. For each WORD your code allocates memory space for a DWORD, so half of which is not used. VBA fills the remaining space (at the place of the most significant WORD part of the DWORD) by repeating the sign bit. The solution is to be using an array with integers rather than longs:

Dim sinal() As Integer

GWteB, I NEVER would have guessed this probem in a million years, let alone a solution to it.
Thank you very much, worked like a charm!

 

[GWteB]

You are welcome and thanks for letting me know.

 

[Cetico]

For each value you are using a so called binary DWORD which consists of 32 bits. Bits 0-30 contain the value, bit 31 is the sign bit, either set (negative) or not set (positive). As of your expected outcome your intention was to use a WORD (16 bits) for each value. For each WORD your code allocates memory space for a DWORD, so half of which is not used. VBA fills the remaining space (at the place of the most significant WORD part of the DWORD) by repeating the sign bit. The solution is to be using an array with integers rather than longs:

Dim sinal() As Integer

Now, what happens if I need to use larger numbers?
I was running some tests and found out that I'll have to use 44100 and 44100*8, while all the other thousands of numbers arewthin integer range.
It's a .wav fila, so I have to state the sample rate and the byte rate.

 

[Cetico]

Now, what happens if I need to use larger numbers?
I was running some tests and found out that I'll have to use 44100 and 44100*8, while all the other thousands of numbers arewthin integer range.
It's a .wav fila, so I have to state the sample rate and the byte rate.

I have a few thousand numbers to add, but the couple of large ones is in the first bytes, so maybe I should add the forst ones as a string in the way mentioned on this thread before and then append the rest in the way you suggested. Or is there a better way?

 

[GWteB]

I am familiar with music, but not with the binary format. I do know that everything has to do with protocol. I know examples (not from music) that there are structures where several items together cover a "field" and thus yield one 32-bit value. Often this has to do with the attempt to save storage space. For example, an item that can reach a maximum value of 9999 requires 14 bits, leaving two unused. An item that can reach a maximum value of 999 requires 10 bits, leaving six unused. The remaining eight bits can together represent an item that allows a maximum value of 255. Of course this is just an example. In addition, there are protocols in which an item has a certain interval. The decimal value is then not converted into a binary because it takes up too much space. A table is then simply applied.

Now, what happens if I need to use larger numbers?
I was running some tests and found out that I'll have to use 44100 and 44100*8, while all the other thousands of numbers arewthin integer range.
It's a .wav fila, so I have to state the sample rate and the byte rate.

If 44100 is stored in its binary equivalent, it will fit within 16 bits. If, on the other hand, 44100 * 8 is stored in its binary equivalent, 20 bits are needed. It is the same type of item, so if there is no table representation storage, then that 44100 is also stored in an area of 20 bits. A 32 bit DWORD is indeed obvious.
I think you will have to determine the order in which the items occur and what their appearance is, in short, unraveling the protocol. If you have the complete overview about this, then you can start coding.

 

[Cetico]

I am familiar with music, but not with the binary format. I do know that everything has to do with protocol. I know examples (not from music) that there are structures where several items together cover a "field" and thus yield one 32-bit value. Often this has to do with the attempt to save storage space. For example, an item that can reach a maximum value of 9999 requires 14 bits, leaving two unused. An item that can reach a maximum value of 999 requires 10 bits, leaving six unused. The remaining eight bits can together represent an item that allows a maximum value of 255. Of course this is just an example. In addition, there are protocols in which an item has a certain interval. The decimal value is then not converted into a binary because it takes up too much space. A table is then simply applied.

If 44100 is stored in its binary equivalent, it will fit within 16 bits. If, on the other hand, 44100 * 8 is stored in its binary equivalent, 20 bits are needed. It is the same type of item, so if there is no table representation storage, then that 44100 is also stored in an area of 20 bits. A 32 bit DWORD is indeed obvious.
I think you will have to determine the order in which the items occur and what their appearance is, in short, unraveling the protocol. If you have the complete overview about this, then you can start coding.

Thanks again!
Actually, the format is laid out here. All numbers are assigned fields wit a number of bits that is enough for them to fit.
For instance, there are four 8-bit fields assigned to the sampling rate (in my case, 44100).

In the link above, the frequency is 22050, and it is stored as

22 56 00 00

And the byte rate is 88200, stored as

88 58 01 00

Whatever the real numbers are, they are always assiged 4 pairs of 2 Hex numbers, little-endian format.

So, it was really sweet to see the code do all the transforming (dec to hex) and inverting (little-endians) being done with no coding effort of mine (after your invaluable contribution). I just took my integer array, put it in the file and voila, all hexes were there.

But since my input array has exactly two numbers that go over the integer range, I have to find an alternative. I hope I have been clearer now.

I guess the alternative seems to be using strings for the 44100 area, in the way pointed in the beggining of this thread, and then append the rest using the ol' integer array. Any thoughts?

 

[GWteB]

If there is no consistency in the "binary stream" then I prefer build up in a string and write the binary string to disk at once.
And as for the endianness, this is common behavior for Intel x86 platform.

 

[GWteB]

I took a look on the format. Quote the author: "The sample data must end on an even byte boundary. Whatever that means. ". LOL
The format isn't super complicated, it's just the trick to get everything in the right order. I think I know how I would handle it. I'll look into it ...

 

[Cetico]

If there is no consistency in the "binary stream" then I prefer build up in a string and write the binary string to disk at once.
And as for the endianness, this is common behavior for Intel x86 platform.

I suppose you're right. I tried to go with the integer alternative anyways since it's so effortless, but as i tried to append the integer array with Write commands, the whole hex/dec switcheroo happenned again, so I'll try your "always strings" suggestion now (which I guess I should've done from the start anyways).

 

[Cetico]

I took a look on the format. Quote the author: "The sample data must end on an even byte boundary. Whatever that means. ". LOL
The format isn't super complicated, it's just the trick to get everything in the right order. I think I know how I would handle it. I'll look into it ...

The header is the easiest part. Most of my numbers are the same as those you see on the format example, so I just pasted the that as a string into my code (with minor adjustments), and the header string was done.

SInce apparently I am going with strings all the way, I suppose the whole problem is solved (though I hate to have the beautiful integer solution ruined because of two entries in a thousand