Solved

Control when a chart updates in Excel 2007 using VBA

Posted on 2011-02-21
2
606 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now