Forum Discussion
Help with Macro Hygiene Please
Try setting up your macro like this, it should actually be faster.
With Application .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Here insert your code With Application .Calculation = xlAutomatic .ScreenUpdating = True .EnableEvents = True End WithExample according to your code specifications.
'Example with your first code Sub Unhide_All() With Application .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False With Application .Calculation = xlAutomatic .ScreenUpdating = True .EnableEvents = True End With End SubHope I was able to help you :).
I know I don't know anything (Socrates)
mtarler , Right? Your assumption is correct - no user defined functions in here. The only way to call any of the macros is manual, I don't event have buttons in the spreadsheet mapped to them, though I've considered adding that. The table itself has a few formulas in it, though the most complex of them is like three or maybe four IF s embedded (probably should use an array, but, well, got it working and don't want to look at it anymore). But it's nothing too complex, and the table itself isn't really all that large - maybe a few hundred rows, and about thirty columns.
So I still don't get why this helps. It makes perfect sense that the macros themselves would be more efficient shutting off auto-calculations, but why would it impact anything before any macro has been called?
I suppose I could play around and see if it really is the calculation part of the code update that provides the solution - take out the other two shut downs and see if performance is still improved. But I can't imagine the events have anything to do with it, unless there are events embedded within Excel itself I didn't purposefully add. And screen updates - I don't have fancy slide show effects built into any of it or anything of the sort.
But then, I probably ought to do my actual work first. Sometimes I think my real love of excel is that it helps immensely in my job, but it also provides escapes where I play with it instead of actually just using it. I'm sure I'm the only one like that though....
- you can and should also put break points in every macro and paste data and see if for some reason any of them are getting triggered.
as for "I'm sure I'm the only one like that though...." I'm assuming you are being facetious because I can personally guarantee that you are not the only one...- Thanks - I'm not familiar with the idea of doing break points.... I'm assuming it's a technique in troubleshooting code, where it inserts data in a test cell to indicate if the macro is being inadvertently called somehow?
What would such a code segment look like, if you don't mind?
And yes - facetious, and self deprecating, though I suppose, inadvertently deprecating others too, which isn't very nice for someone asking for tech handouts.CHileman A break point will stop code execution if/when it reaches it. In the VBA window simply click in the left margin of the code window on an executable line and a red dot will show up and the line will be highlighted:
then when that code runs it will stop at that point and you will have a chance to execute line by line, move execution to a different point, debug values, stop execution or just continue the run.
As for the comment I did not take it as deprecating at all, just truth. As an aside I recently learned that sarcasm is more 'hurtful intent' while facetious is not and in the past I sort of felt it was the other way around...