Writing cell contents to binary file

[bluto32]

(I have Excel 2010 running on Windows 7 Ultimate)

I am trying to write an Excel macro which will (1) pull in all satellite transponder info from a webpage; (2) sort it according to which beams are desired; (3) break down the appropriate data into bytes, one per cell; (4)write the appropriate data to a file.

So far, with the help of the built-in macro recorder and searching this forum, I have managed to sort out (1), (2) and (3). Part (4) ought to be the easy part, but I'm struggling...

Suppose I have already have a file called "test.ndf" with initial file size 1000 bytes. After doing stages (1)-(3) above, my spreadsheet has the relevant data in cells A1 to A2000, with each cell containing a decimal between 0 and 255 inclusive. I would like to write a macro which will loop through these 2000 cells, appending each value as a byte to my orginal file, so that it has file size 3000 bytes at the end.

Thanks for taking the time to read this,
Bluto

 

[bluto32]

Thank you so much for replying!

I notice that you have still used a loop. Am I correct in saying that your code will be faster than mine in creating the byte array, since your loop is entirely between arrays and thus has no interaction with the worksheet whatsoever?

Purely out of interest, is it impossible to copy the data from ByteArray2D (variant type) to ByteArray1D (byte type) without using a loop?

 

[Gokhan Aycan]

You can time it yourself.

Insert a new class module, name it "HighResTimer", paste the following. Edit: I got the code from the Net while working on a project.

Option Explicit

'' High Resolution Timer
Private Declare PtrSafe Function QueryFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (ByRef Frequency As Currency) As Long
Private Declare PtrSafe Function QueryCounter Lib "kernel32" Alias "QueryPerformanceCounter" (ByRef PerfromanceCount As Currency) As Long

Dim Frequency As Currency
Dim Started As Currency
Dim Stopped As Currency

Private Sub Class_Initialize()

    Call QueryFrequency(Frequency)

End Sub

Public Sub StartTimer()

    QueryCounter Started
  
End Sub

Public Sub StopTimer()

    QueryCounter Stopped

End Sub

Public Sub ResetTimer()

    QueryCounter Started
    Stopped = 0

End Sub

Public Property Get Elapsed() As Double

    Dim Timer As Currency
  
    If Stopped = 0 Then
        QueryCounter Timer
    Else
        Timer = Stopped
    End If
  
    If Frequency > 0 Then
        Elapsed = (Timer - Started) / Frequency
    End If

End Property

Then in your code:

Dim TestTimer as New HighResTimer

TestTimer.StartTimer

' Code To Measure Elapsed Time Here

TestTimer.StopTimer

Debug.Print "Elapsed Time (ms): " & (TestTimer.Elapsed * 1000)

 

[John_w]

I notice that you have still used a loop. Am I correct in saying that your code will be faster than mine in creating the byte array, since your loop is entirely between arrays and thus has no interaction with the worksheet whatsoever?

That's correct. Loading sheet data into an array and then reading that array is much faster than interacting directly with sheet cells.

Purely out of interest, is it impossible to copy the data from ByteArray2D (variant type) to ByteArray1D (byte type) without using a loop?

Again correct, AFAIK. There's a VBA trick using Application.Transpose and Application.Index which converts a 2-d array to a 1-d array in one statement:

VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index

Sometimes we need to slice an array i.e. fetch a row/column from a multidimensional array. There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. Howev…

usefulgyaan.wordpress.com

however the resultant 1-d array can only be a Variant array, not a Byte array.

Edit - fixed example.

Sub Test()

    Dim Var2DArray As Variant
    Dim Var1DArray As Variant

    Var2DArray = ThisWorkbook.Worksheets("Sheet1").Range("A1:A4")
    Var1DArray = Application.Transpose(Application.Index(Var2DArray, 0, 1))

End Sub

 

[bluto32]

John - many thanks again for your helpful replies.

Gokhan - thank you for providing the timer code. I normally use "t = timer" at the start and "Debug.Print timer - t" at the end to time code, but am interested by your class module. (I've never used one before - or really understood them - and shall experiment with it.)