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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Small" Multi-Dimensional Array Slow Writing To Cell
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. -- Regards, Tom Ogilvy "Steve Hieb" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Small" Multi-Dimensional Array Slow Writing To Cell
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 |
Reply |
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 |