"Small" Multi-Dimensional Array Slow Writing To Cell

  • Thread starter Thread starter Steve Hieb
  • Start date Start date
S

Steve Hieb

I have an array like (0 to X, 0 TO 3, 0 TO 12, 0 TO 3) where X is
around 60. I'm also declaring and using other arrays in the
procedure, but Erasing them when they are no longer needed. Filling
the array, sorting, and changing the element values takes less than a
second. Problem is it takes about 30 ms to write a single element to
a cell (strangly spiking occassionally to 400 ms on a random array
element). Normally this would be less than 1 ms. FYI, I'm an
experienced VBA programmer: screenupdating is off, I'm not selecting
stuff or activating anything, With statements are used, I can spell
Ogilvy and Pearson etc. (i.e. my code is very efficient and I know I
could use Transpose or similar). However, I would prefer to loop thru
the elements (only about 600 cells to write to).

I'm using XL97 on Windows NT. Via NT's performance monitor I noticed
a huge spike in page faults per second when the sub ran (80 /s). I
don't know much about this, but seems like it is hitting the hard
drive because it ran out of RAM (256 in my case).

I've simulated the array (even larger) in a separate sub and there is
no delay (i.e. write to cell of <1 ms). I've also used much larger
arrays w/o problems in the past. What in the file, code,
declarations, other processes, etc. could have caused me to run out of
RAM, assuming this is the problem? I guess more important, what would
cause writing to a cell to take 30x longer than normal?

Any ideas??

Thanks,
Steve
 
Since you didn't mention it specifically, is calculation turned to manual.
If you have a lot of dependent calculations, each entry can cause a
recalculate to take place and cause such a delay. It would also be
consistent with testing it on a blank workbook/sheet and seeing much faster
results.
 
Hi Tom,

Calculation is set to automatic, but these values are not used in many
calculations. Good point though. HOWEVER, I am embarrassd to report
that shortly after posting the initial message I remembered there was
a "Recalculate" button on the receiving worksheet. The button's color
changed from Green to Red and back to visually alert the user whenever
there was a .... Worksheet_Change!!! Sorry for wasting everyone's
time. I'm happy to report it takes about 30ms to change the color of
a button ... not write from an array to a cell!

BTW, over the years I've read many very useful posts from you. Thank
you for the excellent work.

Regards,
Steve
 
Back
Top