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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top