Program Speed - Arrays Searching vs. Row Searching

[CWillis]

Hello,

I have a spreadsheet with many rows and columns of data. I have a program
that searches the data line by line and performs various calculations. My
problem is that the code takes approximately 15 minutes to run due to the
number of iterations and searches. Rather than restructure the code
completely, I was wondering if initially saving the data to a large array and
then performing the calculations and searches on the array rather than the
spreadsheet itself would reduce my calculation time. If so, this step would
be much easier than restructuring the code.

Thank you in advance for your thoughts.

-Chris

 

[Per Jessen]

Hi

Maybe it could help to set calculation to manual and turn off screen
updating if it haven't been done.

Application.Screenupdating = False ' Reember to set to true again
Application.Calculation=xlManual

Regards,
Per

 

[CWillis]

Per,

Thank you. I forgot to mention that in my original post. I currently am
turning off:

Application.ScreenUpdating
Application.DisplayStatusBar
Application.Calculation
Application.EnableEvents
Application.DisplayPageBreaks


Chris

 

[Charles Williams]

It will be MUCH faster if you get the data into a variant containing an
array and work on that: there is a large overhead associated with
transferring data from Excel to VBA so doing things cell-by-cell is
extremely slow.

dim Varr as variant
varr=Worksheets("fred").Range("a1:E10000")

varr is now a 2-dimensional array with 10000 rows and 5 columns

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[CWillis]

Charles,

Thank you very much. I'll change the code around and reply with the time
savings. Thanks again.

Chris

 

[CWillis]

Charles,

I finished changing the code so that rather than searching the spreadsheet,
I store the necessary data in arrays and perform the calculations on the
arrays rather than referencing the sheet multiple times.

"MUCH faster" was an understatement. Doing this reduced my calculation time
from approximately 15 min to under 10 seconds.

Thanks again!

 

[Charles Williams]

Excellent!

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[ytayta555]

Excellent!
Charles

Mr. Charles , can I send you a workbook , to give me some advices
if I can apply the same methods with my kind of data from my
workbook ?
I red this thread and become really interested in this new way
for me .
(Hope I don't hijack the thread..)