Help with Macro Hygiene Please

[CHileman]

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 deal of data into cells in the table I use. When it comes time to select the cell to paste the data into there is an annoying delay. When I remove all the macros it stops, but when I put them back in it starts again. 

All of the macros are just ways to manage the view of the table - selecting criteria from filters on the headers and/or hiding columns I won't need to bother with. 

I suspect that perhaps there is a better way to write my codes, but all my coding experience relies mainly on googling and adapting. 

I'm also pretty new to this - so not too sure what would be the best way to post the codes I have for someone who might be willing to review. But here's what I use:

Unhide_All (I don't think this one is problematic)

Sub Unhide_All()

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

End Sub

Here's another for entering new information without having to look at columns and rows I don't need - my guess is it's one of the ones like this that are problematic:

Sub Referral_Entry()

Call Unhide_All

ActiveSheet.ListObjects("DG_TABLE").Range.AutoFilter Field:=1, Criteria1:= _
        Array("Open Referral", "Approved", "Accepted"), Operator:= _
        xlFilterValues
        
Dim Lastcolumn As Long
Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To Lastcolumn
        If Cells(1, i).Value = "NOA Submitted" Or Cells(1, i).Value = "Auth #" Or Cells(1, i).Value = "1st Auth Day" Or Cells(1, i).Value = "LCD" Or Cells(1, i).Value = "CCR Due Date" Or Cells(1, i).Value = "CCR Submitted" Or Cells(1, i).Value = "DC Date" Or Cells(1, i).Value = "DC Submitted" Or Cells(1, i).Value = "Encounters Authed" Or Cells(1, i).Value = "Encounters Total" Or Cells(1, i).Value = "ART dates in DG" Or Cells(1, i).Value = "Injection Dates" Or Cells(1, i).Value = "H&P Sent" Or Cells(1, i).Value = "Signed H&P Uploaded" Or Cells(1, i).Value = "Billing Config Service Code" Or Cells(1, i).Value = "Bed Night Adjusted intake Date" Or Cells(1, i).Value = "Bed Night Adjusted DC" Or Cells(1, i).Value = "Mbr Bed Night Count" Then Columns(i).Hidden = True
    Next
    
    
End Sub

I could post more, but I would bet if I can figure out the solution from one, I can repeat it for the others. 

TIA!

Chileman

[#2 · Accepted solution]

@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)

[#3]

@CHileman , when does the sub Referral_Entry get called (run)?

Are you using any events like Worksheet_SelectionChange or Workbook_Activate etc?
Such events are fired too frequently and may become one reason of slow activation of Excel window or cell. If so, then may see if you can avoid using these events to run the subs. For example, you can choose to run the macros manually by adding them on QAT

https://support.microsoft.com/en-us/office/assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84...

[#4]

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

[#5]

Thanks @amit_bhola, I appreciate very much you taking the time to look and reply. I did get a fix from @NikolinoDE that seems to be approaching the same considerations you give here. FWIW though, in this case, all the macros were already only running from a button already on the QAT. But his suggestion was to disable events (and screen updating and autocalculations) at the start of the macro call, and then re-enabling before exiting it. It's working, though I don't really see how. But again, I do appreciate your help here!

[#6]

@CHileman 

'lags I was experiencing were not happening while a macro was running'

That's why I thought that some event might be slowing down the window/cell activation from another program.

Just to be clear, did disabling events just make the macro execution fast or did it also solve the Excel window/cell reactivation slow issue?

If it solved, then I share your (not so) mile long puzzle!

Otherwise, you can check if there are many macros installed, and is it some other macro which is depending on events to execute, and if those can be avoided as well.

[#7]

It mostly solved the issue. I still see periods when my computer is likely maxed out (well, not see them, but I hear the fan running - I should look on task manager sometime to see what resources are ACTUALLY doing, but, of course, when it's happening, I'm doing too many other things to be bothered to take the time and check). But in general, reactivating Excel is no longer problematic.

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!

[#8]

well I for one have my curiosity spiked and really wondering how or why the macros might have caused lag if, as you say, none of them were event driven. I also presume none of them are UDF (user defined functions) either by which I basically mean none of them are functions being called from a spreadsheet formula (e.g. =A1*myFunction(B2) sort of thing)

[#9]

@CHileman 

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)

[#10]

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

[#11]

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.

[#12]

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

[#13]

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.

[#14]

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

[#15]

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?