slower by 10 times by adding an easy function????

[Guest]

I have to gather a lot of records from Oracle. I have a loop that is pulling
1 record an that is doing calculation with an array(1 to number of records
for that record, 1 to number of records for that record). It was going well
but I found out that some data from previous calculation were still in the
array. So I decided to initialize the array for each loop before going to
another record.

Dim x As Integer
Dim y As Integer
For x = 10 To totalOfRowsCellsArray
For y = 1 To totalOfRowsCellsArray
cellsArray(x, y) = 0
Next
Next


Generally totalOfRowsCellsArray = 10. Without that function, it takes 7
minutes to calculate 1000 records. By adding that function it took me 1hr.
Why???after all it's in the memory.
I tried with redim and it’s slow, I tried with Erase and it’s very slow too.
Please help if you know why.

Thanks
Jack

 

[Keith R]

First, consider trying the keyword Erase (Erase cellsArray). Depending on
what the rest of your macro does, recalculation of worksheet formulas can be
painfully slow- consider adding application.calculation =manual at the
beginning of your sub, and application.calculation=automatic at the end
HTH,
Keith