Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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

I am writing a 2-D array to an Excel spreadsheet by the following:

'Write data (number of entries per line = intWord
'and number of lines = intNpoints)
For i = 1 To intNpoints
    For j = 1 To intWord
        .Cells(i + 3, j) = dblData2(i, j)
    Next j
Next i

When intWord = 37 and intNpoints = 41, it takes about 4 seconds on a 900 MHz computer.   I know that VC++ can do it in a fraction of a second, but I am not a VC++ programmer.  Is there a way to do it in VB 6 more quickly than the above code?

Thanks.  
0
glenisle
Asked:
glenisle
  • 6
  • 3
  • 2
  • +3
1 Solution
 
guidwayCommented:
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
0
 
NPluisCommented:
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?
0
 
mlmccCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vwareCommented:
dim your counter(s) as Long - it'll get you some speed improvement
0
 
glenisleAuthor Commented:
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
0
 
glenisleAuthor Commented:
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
0
 
glenisleAuthor Commented:
mlmcc and vware:

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

glenisle
0
 
vwareCommented:
activate optimization for pentium pro and try it when you run the compiled exe
0
 
glenisleAuthor Commented:
mlmcc and vware:

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

glenisle
0
 
glenisleAuthor Commented:
vware:

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

glenisle
0
 
NPluisCommented:
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.
0
 
mlmccCommented:
Are you actually compiling to an exe and running the exe or are you running from the IDE and compiling as you go?

mlmcc
0
 
glenisleAuthor Commented:
mlmcc:

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

glenisle
0
 
SpideyModCommented:
A request for refund has been made at:
http://www.experts-exchange.com/Community_Support/Q_20547206.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
0
 
mlmccCommented:
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
0
 
SpideyModCommented:
PAQ'd and points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now