Forum Discussion
CHileman
Jul 01, 2022Copper Contributor
Help with Macro Hygiene Please
It's not that the macros run slowly, or that Excel crashes, but it just takes a while to get excel selected when coming from another program or webpage. I tend to have to cut and paste a good de...
- Jul 02, 2022
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)
CHileman
Jul 08, 2022Copper Contributor
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.
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.
mtarler
Jul 08, 2022Silver Contributor
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...
- CHilemanJul 08, 2022Copper ContributorAh - I get it. I think. So by adding a break point to each macro, it will stop on that line and will show me if anything is being inadvertently run. Yes?
Though that still leaves me not sure what the "paste data" part you referred to is.
re: facetious vs sarcasm.... I do believe that sarcasm is typically used as a form of self promotion or bullying - i.e., "I'm so much smarter than whomever/whatever my sarcasm is directed towards, and only those as smart as myself understand my true intent." I've made a conscientious effort to avoid that in my life, though not always successfully of course. It does still play a role for me in how I acknowledge my flaws, and I typically reserve it for myself. But it is also a cop out I suppose, as I will use it to point to my flaw, but then act like knowing and seeing my faults is sufficient - as opposed to working on them. All that said, I haven't much thought about how facetious can be, and is, different. I will have to dwell on that some. Maybe find a way to put a line break on self-referential jokes and see where they land. Might be verging into non-excel territory here though, eh?