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)
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 With
Example 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 Sub
Hope I was able to help you :).
I know I don't know anything (Socrates)
- Thank you so much!!!! I still get small hiccups, but nothing like it had been. At the risk of being the man who asks for a mile after an inch, and if it's not too much trouble, could you help explain why it's working?
As I read it, it looks like with these code segments added in, when a macro is run, it is shutting off the RAM intensive bits of excel (calculations, screen updates and events) but then before it exits the macro, it re-enables them.
But the macros wouldn't run unless I called them to run, and the lags I was experiencing were not happening while a macro was running - just when selecting back into excel from another program. So why would turning them off and back on only within the execution of a macro change anything?
Am I pondering this the right way?
Regardless, it is working, so again, thank you!!!Try to explain 🙂 in a few words why these commands could be used.
Please also consider that I do not know the formula and VBA structure of your workbook.
Calculation:
If you change cells/columns, etc. with VBA, to which functions of other cells refer, a recalculation is always started.
Depending on the functions and the associated number of calculations, it can have an impact (mostly negative) on performance.
While Excel is working on the recalculation, the processing of the program code is slowed down because the program flow only continues when the calculation has been completed for the time being.
You can avoid this problem by switching off the automatic calculation in the initial code.
EnableEvents:
The Select methods in the macro would re-invoke the macro called by moving the cursor around the worksheet. Setting the EnableEvent property to False prevents this from happening.
Disabling screen updating speeds up your macro code. While you won't be able to see what the macro is doing, it will run much faster. The ScreenUpdating property should end up being set back to True when your macro exits.
Excel VBA reference (here you can find general information about VBA)
- Thanks again NikolinoDE, all that makes perfect sense to me why it would help while running the macros - but why would it change anything at all for when the macros aren't being run? The only way to call them is to select them manually. Don't get me wrong, I love that they work! But I'm just baffled as to why they work.
