Write hex values to binary file in VBA Excel

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
310
Office Version
  1. 2019
Platform
  1. Windows
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:

Rich (BB code):
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:
Rich (BB code):
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:

Rich (BB code):
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
There are many ways to skin this cat. The devil is in details that you do not provide and I don't have the time to test. Hopefully, the following demonstration will help you invent a solution that is tailored to your goals. The basic idea is: represent hex numeric values by prefixing with "&h".

Code:
Sub testit()
Dim strBytes As String, s As String
Dim h As Byte, b As Variant
strBytes = "F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5"
For Each b In Split(strBytes)
    h = "&h" & b
    s = s & " " & Hex(h)
Next
MsgBox Mid(s, 2)
End Sub
 
Upvote 0
There are many ways to skin this cat. The devil is in details that you do not provide and I don't have the time to test. Hopefully, the following demonstration will help you invent a solution that is tailored to your goals. The basic idea is: represent hex numeric values by prefixing with "&h".

Code:
Sub testit()
Dim strBytes As String, s As String
Dim h As Byte, b As Variant
strBytes = "F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5"
For Each b In Split(strBytes)
    h = "&h" & b
    s = s & " " & Hex(h)
Next
MsgBox Mid(s, 2)
End Sub

No, doesn't work.

Thanks for the help.
 
Upvote 0
Code:
Sub Write2Binary()
  Dim asInp()       As String
  Dim ai()          As Byte
  Dim i             As Long
  Dim iFF           As Integer
  Const sFile       As String = "C:\Users\shg\Desktop\Test.bin"

  asInp = Split("F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5")
  ReDim ai(0 To UBound(asInp))

  For i = 0 To UBound(asInp)
    ai(i) = "&H" & asInp(i)
  Next i

  iFF = FreeFile
  Open sFile For Binary Lock Read Write As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL] 

  Put [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL] , , ai
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL] 
End Sub
 
Upvote 0
No, doesn't work

I suspect you took my demonstration too literally. I did not expect to have to provide a turnkey solution.

The point was to show how to convert "F3" into a numeric hex value, namely &hF3.

The following code demonstrates how to write to your file, based on your example.

Rich (BB code):
Sub Write2Binary()
Dim b As Variant
Dim nFileNum As Integer
Dim sFilename As String

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

sFilename = "D:\OutputPath\Test.bin"
nFileNum = FreeFile
Open sFilename For Binary Lock Read Write As #nFileNum 

  For Each b In Split(strBytes)
    ' No byte position is specified so writing begins at byte 1
    Put #nFileNum , , CByte("&h" & b)
  Next

Close #nFileNum   
End Sub

Doing a byte-by-byte Put is probably not the most efficient way to do this. I'm just following your lead.

PS.... I see that shg demonstrated the more efficient redesign.
 
Last edited:
Upvote 0
Code:
Sub Write2Binary()
  Dim asInp()       As String
  Dim ai()          As Byte
  Dim i             As Long
  Dim iFF           As Integer
  Const sFile       As String = "C:\Users\shg\Desktop\Test.bin"

  asInp = Split("F3 A1 02 00 04 00 8D 24 44 C3 8C 03 83 49 26 92 B5")
  ReDim ai(0 To UBound(asInp))

  For i = 0 To UBound(asInp)
    ai(i) = "&H" & asInp(i)
  Next i

  iFF = FreeFile
  Open sFile For Binary Lock Read Write As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF"]#iFF[/URL] 

  Put [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF"]#iFF[/URL] , , ai
  Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF"]#iFF[/URL] 
End Sub
Thanks shg, it works pretty nice.
 
Upvote 0
I suspect you took my demonstration too literally. I did not expect to have to provide a turnkey solution.

The point was to show how to convert "F3" into a numeric hex value, namely &hF3.
I tested your suggestion but I missed the CByte() part to make it work. Thanks for share another solution.
 
Upvote 0
I have a similar task to perform, except that my input is an array of Longs. I have to store them in a HEX file as 2's complemente 8-bit little-endians, since it is a .wav file so I figured that just writing them to a file would do the trick. But it didn't go as planned.

The test values should yield

DE A8
CC 16
00 00
1E 5B


And I got instead

DE A8 FF FF CC 16 00 00 00 00 00 00 1E 5B 00 00

The code I used is below. Do you have any idea what happenned and how to fix this?

VBA Code:
Sub Gera_sinal()
Dim sinal() As Long

ReDim sinal(3)

'Test values
 sinal(0) = -22306
 sinal(1) = 5836
 sinal(2) = 0
 sinal(3) = 23326

'Creates a file and puts the values in it
Dim n_arq As Integer
Dim path As String

path = "C:\Users\DELL\Desktop\App\WAVs\Sinal_VBA.wav"
 
     Set fs = CreateObject("Scripting.FileSystemObject")
     Set a = fs.CreateTextFile(path, True)
     a.Close
    
     n_arq = FreeFile
     Open path For Binary As n_arq

     Put n_arq, , sinal
    Close n_arq

End Sub
 
Upvote 0
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:
VBA Code:
Dim sinal() As Integer
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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