Solved

Control when a chart updates in Excel 2007 using VBA

Posted on 2011-02-21
2
614 Views
Last Modified: 2012-05-11
I have an application in Excel 2007 using VBA that places its results in a fixed location that is referred to by a chart, which is the primary output.  As the one thousand values are being placed in the output table, the chart updates after each one, which truns a one second process into a few minutes.

Is there a way to programatically prevent the chart from updating then force it to update at the end?
0
Comment
Question by:sjgrey
2 Comments
 
LVL 3

Accepted Solution

by:
byronwall earned 500 total points
ID: 34948042
You can use the calculation property to switch to manual.  When you are done with your updating, switch back to auto and everything will update after a calculate call.  This is equivalent to changing the option on the Formulas -> Calculation Options menu if you want the non-VBA route.

Sub FasterExecution()
    
    'Switch to manual
    Application.Calculation = xlCalculationManual
    
    'Run your chart code.
    
    
    'Calculate and switch back to auto
    Application.Calculate
    Application.Calculation = xlAutomatic
    
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:sjgrey
ID: 34948216
Perfect thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question