Program Speed - Arrays Searching vs. Row Searching
 

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

Program Speed - Arrays Searching vs. Row Searching
 

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" skrev i meddelelsen
...
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

Program Speed - Arrays Searching vs. Row Searching
 

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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

Program Speed - Arrays Searching vs. Row Searching
 

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" wrote in message
...
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

Program Speed - Arrays Searching vs. Row Searching
 

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" wrote in message
...
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

Program Speed - Arrays Searching vs. Row Searching
 

Charles,

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

Chris

"Charles Williams" wrote:

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" wrote in message
...
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

Program Speed - Arrays Searching vs. Row Searching
 

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!


"CWillis" wrote:

Charles,

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

Chris

"Charles Williams" wrote:

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" wrote in message
...
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

Program Speed - Arrays Searching vs. Row Searching
 

Excellent!

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

"CWillis" wrote in message
...
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!


"CWillis" wrote:

Charles,

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

Chris

"Charles Williams" wrote:

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" wrote in message
...
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

Program Speed - Arrays Searching vs. Row Searching
 

On 6 Iun, 02:09, "Charles Williams"
wrote:
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..)