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)
But as far as I can tell, the execution of the macros has always been quite fast - I've never noticed a delay in running them. And there aren't too many - 4 or 5, all of them just ways to manage the "view" (because it's in a table I can't use custom views which is all I really want anyway). None use any event to activate.
I'm still learning the wonderful world of excel - so understanding the root of this is purely an academic pursuit. But then, I use a lot of stuff now that were just academic interests in the past.
Thanks again for looking and suggesting!
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.