essjthakkar
asked on
VBA chart-drawing excel macro CPU/Memory optimization
Hi,
I'm not sure if this is the right place to ask...But here it is.
I have 200 columns and 40 rows long sheet in Excel and I have coded a Macro to plot charts of every column agains every other (to understand correlation between the data).
Now the problem is that while plotting, with ScreenUpdate=False, my CPU utilization shoots up to 100% ...plotting hundreds of those charts in new sheets (one per column under consideration)! ....and it takes FOREVER..to finish it........
Please let me know if you know of a trick or optimization to reduce my CPU and MEMORY utilization for such a macro!
Thanks (hope I explained it well!)
~essjthakkar
I'm not sure if this is the right place to ask...But here it is.
I have 200 columns and 40 rows long sheet in Excel and I have coded a Macro to plot charts of every column agains every other (to understand correlation between the data).
Now the problem is that while plotting, with ScreenUpdate=False, my CPU utilization shoots up to 100% ...plotting hundreds of those charts in new sheets (one per column under consideration)! ....and it takes FOREVER..to finish it........
Please let me know if you know of a trick or optimization to reduce my CPU and MEMORY utilization for such a macro!
Thanks (hope I explained it well!)
~essjthakkar
ASKER
Ok, about the Visible=false idea, is this possible ? (algorithmic steps...)
repeat:
create chart object
Set the chart to visible=false,
Populate the chart : get chart data, set the title, etc.
Set the chart to visible=true now, since its ready.
go to "repeat" for another chart
I tried setting Visible=false before actually populating the chart, but apparently, it gave me an Error! IS IT NOT possible to set a chart to invisible and THEN populate it?
:S
~Siddharth
repeat:
create chart object
Set the chart to visible=false,
Populate the chart : get chart data, set the title, etc.
Set the chart to visible=true now, since its ready.
go to "repeat" for another chart
I tried setting Visible=false before actually populating the chart, but apparently, it gave me an Error! IS IT NOT possible to set a chart to invisible and THEN populate it?
:S
~Siddharth
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Im not exactly sure if i understand it, but alot of times, if you have stuff visible it takes longeer just becuase of the refreshes. Try makking each chart .visible = false until you have filled it, then turn it visible.
Also, maybe if you throw a "DoEvents" statement in the loop. That way, after each iteration, you give the computer a chance to process other events on the system.
-Andrew