Slow Chart Scrollbar

[Jeffrey Lebowski]

Hi,


I have a scrollbar that I use with a dynamic chart that goes scrolls through quarters of data. I am experiencing significant (2 sec) delay from when I click the scrollbar to when the chart reflects the update.


Does anyone know what might be the cause of this delay?


Thank you

 

[Smallman]

Hi Jeff


You may have a problem with some heavy formula causing a bottle neck. It may be the overall size of the file. Are you using dynamic named ranges for your chart, they can be memory hogs. If it is the named ranges you could make the ranges dynamic in VB.


These are just ideas though for more precise spreadsheet doctoring if you could upload the file that would be awesome.


Take it easy


Smallman

 

[SirJB7]

Hi, Jeffrey Lebowski!

Could you try the same process in different computers with different hardware configurations? Specifically RAM and CPU specs. Just to evaluate if it's Excel who's sucking all the resources or if you happened to develop the workbook on a non suitable PC.

Regards!

PS: You can consider uploading a sample file too.

 

[Jeffrey Lebowski]

I AM using dynamic named ranges. Please tell me this is not the issue. I have a graphs sheet set up, with 4 charts that have dynamic ranges (for each series and for category series) so that as the source range is updated, the charts reflect the update. I have some big IF and VLOOKUP formulas but I copied/pasted values and there was no change.


I want this great ability to have charts that update and are dynamic, but it loses a bit of the zing when it takes 2 seconds to scroll through the data range. I would appreciate any insight towards a better solution or helpful suggestions!


Thank you.

 

[Smallman]

Hi Jeff


One of the down sides of a dynamic range is that it can slow the calc speed of a file, depending on the file. I tend to just push the range into vb and call get the whole dynamism thing going on there. Something like this;

[pre]

dim Chrng as range
set Chrng = range("A1, Range("C65536").end(xlup))

[/pre]

where i make the assumption your dataset is under 65k rows long - is the same as something like this;


Named rng Chrng

=offset(Sheet1!$a$1,0,0,counta(sheet1!$a1:A65535),3)


Anyways if you like you can post your file. If no one looks at it I will give it a crack this evening Aussie time.


Take care


Smallman

 

[Hui]

Jeffrey


It is highly unlikely to be the use of Named Formula, unless you have referenced entire Columns in complex lookups etc, but VBA won't resolve that either.


It is more likely to be the use of Volatile functions in a large data area within the model. Some of these like Offset can be replaced with other Non-Volatile functions


Can you please upload the file so we can review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook