Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Control when a chart updates in Excel 2007 using VBA

Posted on 2011-02-21
2
Medium Priority
?
639 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

916 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