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

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 its slow, I tried with Erase and its very slow too.
Please help if you know why.

Thanks
Jack

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

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

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