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)

amit_bhola

Iron Contributor

Jul 07, 2022

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.

CHileman

Copper Contributor

Jul 07, 2022

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!

mtarler
Silver Contributor
Jul 08, 2022
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)
- 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...