What is the quickest way to write a 2-D array to EXCEL?

I don't think so. Unless you maybe assign dblData(i,j) to a variable above this statement and then assign .Cells(i+3,j) to that variable. I know in Java that would help a little because it was not having to do nested calls (or something like that). Just my 2 cents though. Not sure if it will help in VB. :0)

guidway

If I run the following code on a 800 Mhz it runs in less than 1 second:
Dim intNpoints As Integer
Dim intWord As Integer
Dim dblData2() As Double

intNpoints = 41
intWord = 37

ReDim dblData2(intNpoints, intWord)
For i = 1 To intNpoints
    For j = 1 To intWord
        dblData2(i, j) = Round(Rnd(), 3) * 100
    Next j
Next i

With Excel.Sheets("Sheet1")
    For i = 1 To intNpoints
       For j = 1 To intWord
           .Cells(i + 3, j) = dblData2(i, j)
       Next j
    Next i
End With

are you sure the code u placed takes 4 seconds?

Here is one thing you can do to speed up the code

Dim max as integer
max = intNpoints + 3
For i = 4 To max
   For j = 1 To intWord
       .Cells(i , j) = dblData2(i, j)
   Next j
Next i

ELiminate the repeated additions inside the loop.

mlmcc

dim your counter(s) as Long - it'll get you some speed improvement

NPluis:

I timed it more accurately on my 900 MHz computer and it takes just under 3 sec to do only the write loop:
   For i = 1 To intNpoints
      For j = 1 To intWord
          .Cells(i + 3, j) = dblData2(i, j)
      Next j
   Next i

With one exception, I am using essentially the same coding as you used (including the Redim dblData2(intNpoints, intWord)), so it is still a mystery why yours takes under 1 sec.

The thing I don't use is your first loop:
For i = 1 To intNpoints
   For j = 1 To intWord
       dblData2(i, j) = Round(Rnd(), 3) * 100
   Next j
Next i

NPluis:

My previous comment was accidentally sent before I finished.  Regarding your first loop, it garbles the array quite drastically and I don't know why you even used it.

Thanks,
glenisle

mlmcc and vware:

I find that your two suggestions had negligible effect on the run time.

glenisle

activate optimization for pentium pro and try it when you run the compiled exe

mlmcc and vware:

I find that your two suggestions had negligible effect on the run time.

glenisle

vware:

Running the compiled code (compiled with activatation of optimization for Pentium Pro checked) had no measurable effect on the run time.

glenisle

The first loop was only used to fill the array so I had something to test it with. I haven'tgot a clue why yours takes 3 times as long as mine.

Are you actually compiling to an exe and running the exe or are you running from the IDE and compiling as you go?

mlmcc

mlmcc:

Yes, I am actually compiling to an exe and running it to get the timing.

glenisle

A request for refund has been made at:
https://www.experts-exchange.com/questions/20547206/Please-delete-one-of-my-questions.html

I'm copying the text so that if I do Refund and PAQ, the information will be here:

The method is described in detail in:
    http://www.vb-helper.com/HowTo/excel.zip
Using that method I could write my 37 x 41 array to an Excel spreadsheet in a blink of a second, instead of 4 seconds, with no other changes to my program.


Experts, please leave any objections within the next 72 hours.  Without them I intend to honor the request.

SpideyMod
Community Support Moderator @Experts Exchange

In his original example, he is opening excel copying the data to a live spreadsheet and able to view when it is done.

In his final answer he is creating an Excel compatible file (it is not a .xls file).  If he now needs to view it, he will have to open Excel then open and convert the file.

mlmcc