Forum Discussion

CHileman's avatar
CHileman
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's avatar
    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's avatar
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.
mtarler's avatar
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's avatar
    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?

Resources