Program Speed - Arrays Searching vs. Row Searching

C

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
 
P

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
 
C

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
 
C

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
 
C

CWillis

Charles,

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

Chris
 
C

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!
 
Y

ytayta555

Excellent!
Charles
Click to expand...

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..)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Variable array in Excel VBA charting 7
Finding a match- should i use a nested array, my source arrays, or 2
03 vs 07: speed with different methods of referencing ranges? 1
Fast record searching in array 5
Odd behaviour with a userform 0
Don't add to range if row already exists 15
Unexplained failure of wdFindStop at end of search 0
Data Refresh Speed 1

Top