Forum Discussion

Copper Contributor

Jul 01, 2022

Solved

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...

Macros and VBA

NikolinoDE

Jul 02, 2022

CHileman

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 :).

NikolinoDE

I know I don't know anything (Socrates)

CHileman

Copper Contributor

Jul 08, 2022

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....

mtarler

Silver Contributor

Jul 08, 2022

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...

CHileman
Copper Contributor
Jul 08, 2022
Ah - 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?
mtarler
Silver Contributor
Jul 08, 2022
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...
CHileman
Copper Contributor
Jul 08, 2022
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.