Solved

Application and Screen handling Updating

Posted on 2011-09-04
7
352 Views
Last Modified: 2012-05-12

I have a fairly complex Workbook that as I have integrated a lot of different graphics/charts and formulas, I now have a performance problem.  It didn't emerge until the most recent additions.  I have been experimenting with

Application.ScreenUpdating = True or False
Application.EnableEvents = True or False

But it only seems to work on certain functions and I'm not sure when to use one over the other or if it's better to use both.  The most recent example is when I go to clear all of the data (to refresh it for new input), I have a chart/graph that updates as changes to input occur.  When I activate my "Clear All" Macro, all the data disappears (as it should) but the graphic slowly resets itself and I can literally watch it reset ( it takes about a minute).  Any ideas or education you can give me on what I may need to do to improve the performance?

Thank you,

B.

0
Comment
Question by:Bright01
  • 3
  • 2
  • 2
7 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 36481300
Turn events off only when necessary, and only for as long as required. For example, if a Worksheet_Change event macro would otherwise run when you are changing the values in cells, then you might turn events off before changing the values, and then back on after the changes are complete.

Turning screen updating off has a lot of benefit in speed and very little penalty in usability. If you don't need to respond to MessageBox or dialog requests for cell selection or input, then turn screen updating off when your macro starts. My current practice is not to turn screen updating back on. Excel will do it for you automatically when control returns to the worksheet, and it can be a pain to manage the screen updating if you have a number of macros that call each other.
0
 
LVL 80

Expert Comment

by:byundt
ID: 36481303
If you could post your workbook, we could offer more specific advice for your situation.

Brad
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36482448
Ignoring your general question which byundt has answered fully, I think for your specific problem with the chart data, you should turn Calculation to manual while you reset the data, then turn it back on afterwards.  I obviously can't test this without seeing your sheet, but this will usually work:

    Application.Calculation = xlCalculationManual
    
    ' Your clear code
    ' .....
    
    Application.Calculation = xlCalculationAutomatic

Open in new window


Also I would suggest you raise a new question to get experts to look at the chart.  Although some charts do take a long time to render (e.g. big x-y scatter plots in Excel 2007), they can often be fixed and it should not normally take as much as a minute to refresh.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Bright01
ID: 36483514
Byundt and Andrewssd3,

Thanks so much for the input.  Unfortunately I can't send out the sheet due to some confidentiality restrictions.  However, with that said, let me see if I understand the directions you have put forth;

Application.ScreenUpdating = True or False

I should use this when I have a macro that when run, updates data or output to the screen.  Using this command w/False turns off screen  updating and when the macro completes, Excel automatically updates the screen.  There is no need to add "True" because Excel resets it.  

Application.EnableEvents = True or False

I should use this when I macro is running (perhaps in the background or all the time, or looking for an "event") and I would like to turn it off while inputing information.  Do I need to add "True" at the end or does Excel automatically handle that too?

And finally, use Application.Calculation = xlCalculationManual and Automatic.  This is interesting because where I have pinpointed the problem is as follows;

I have a complex chart that has the formulas (not macros) embedded in the chart.  The chart resides on an individual worksheet.  I have copied the chart to another worksheet where it's used as part of a bigger dashboard.  I then have a macro that clears all of the data input to the chart.  When the macro for clearing is running, the data clears immediately.  The chart however, clears slowly and I can actually see it clearing the values (the bars disappear...albiet slowly).  I think what is happening is that Excel is going through all the formulas as the data becomes null.  Will this Application.Calculation statement work in this case and if so, can you give me some advise on how to embed it in the / a macro?

I'll be happy to close out the first question and ask this as a separate question, but I didn't want to lose you guys in the process.  Let me know if that's what I should do  here.

Thank you again,

B.




0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 200 total points
ID: 36483610
On the Application.Calculation question, it may help if you're clearing the data then adding new data - in this case it should stop the chart from having to redraw twice.  Of course when you turn calculation back on, it will have to recalculate and you'll get the chart redrawing then.  Often in VBA when you're manipulating complex data it is a good idea to switch calculation off until you have completed the process, as this can save a considerable amount of time.  You just need to set it to manual at the start of your update process, then back to automatic (which forces a reclaculation) at the end. Just bear in mind that while calculation of off, formulas will not be recalculated, so be sure you're not depending on any calculations during your update.

I'm not quite clear what sort of formulas you have embedded in your chart.  If you could find a way to depersonalise just that part it would be interesting to post it as a new question and get the community to take a look.
0
 
LVL 80

Accepted Solution

by:
byundt earned 300 total points
ID: 36483627
Very important clarification: you must re-enable events or else they stop working until you relaunch Excel. I probably make this mistake (most often due to a fatal error while debugging code) several times a week.
Application.EnableEvents = False       'Somewhat dangerous, because Excel won't reset this property
'Do stuff that triggers Event macros
Application.EnableEvents = True

Open in new window


Turning events off won't help with execution speed unless you have event macros running in the background. For example, suppose you have a Worksheet_Change event macro that searches the entire column A for blank cells. If you have another macro that manipulates the values in column B, then the Worksheet_Change event macro runs after each manipulation of values in column B. Column A, which contains over a million cells in Excel 2007 and later, may become needlessly searched thousands of times as you run the macro for column B--unless you turn events off before manipulating values in column B.

Based on your description (bars on the chart disappear slowly), you probably have not turned screen updating off (or have turned it back on). Try commenting out every statement that turns screen updating back on and see if speed improves.

Brad
0
 

Author Closing Comment

by:Bright01
ID: 36483704
Thanks guys.  I like the idea of troubleshooting this by commenting out the selections and then rerunning the macro.  You guys taught me a lot about ways to improve performance.

Much thanks,

B.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 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

16 Experts available now in Live!

Get 1:1 Help Now