[Resolved--Read It!]Excel Sheet vs. an Array--Big Difference?

[Bartender]

Hey all,
I currently have program that references an index of cells (set up in an excel spreadsheet) millions of times per run of the program. Would this be significantly faster if I first read the index into a 40 by 40 array variable, and then referenced that rather than the spreadsheet?
Thanks a lot!

[si_the_geek]

In my experience it is around 20-30 times faster to use an array to write data into Excel rather than write single cells (ie: if writing individual cells takes 30 seconds, the same work using an array is 1 second). Reading from Excel is also noticably faster using arrays to get the data out, but I cant remember by how much.

I would say that you will probably get a huge performance improvement by switching to an array instead.

[Bartender]

Thanks, I'll try it and see how it goes!

[Bartender]

I think this is interesting and useful:
I read the values into an array since si said this would be more powerful, and wow was he right.
Instead of reading from a 39X39 range on my spreadsheet, I created a 39x39 array variable. This variable is called millions of times per run of my program, and it's a wee bit faster than the excel range index.
Running my program used to take ~1800 seconds... now it takes 56.
Almost exactly 30x as fast like si said---thought you might like to know.
Bartender

[si_the_geek]

Wow, it's a nice surprise to be so accurate!

Thanks for sharing the results