Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Control when a chart updates in Excel 2007 using VBA

Posted on 2011-02-21
2
Medium Priority
?
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

719 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