Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Small" Multi-Dimensional Array Slow Writing To Cell
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
newbie question on multi-dimensional array | New Users to Excel | |||
Problem with Multi-Dimensional Array | Excel Programming | |||
How to declare Multi-dimensional dynamic array? | Excel Programming | |||
Declaring Dynamic Multi-dimensional Array | Excel Programming |