One issue that I’ve seen quite a few times is seeing macros that end up being very slow to run. Those usually happen when a file evolves over several years and becomes more complex, bigger, etc than was anticipated at the start.
What I Do To Detect The Problem
The biggest challenge of course is trying to find what step is taking too much time so that I can then work on that specific part. How do I do it? I generally build macros, splitting them in different modules with one macro that groups them together. It makes it much easier to get a big picture of what the macros does, etc. Then, I get to something like this:
Sub ImportData()
Call importyest
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importyest”: r = r + 1
Call importinav
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importinav”: r = r + 1
Call importa1
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importa1”: r = r + 1
Call importa2
Sheets(“log”).Cells(r, 2) = Format(Now(), “hh:mm:ss”): Sheets(“log”).Cells(r, 1) = “importa2”: r = r + 1
End Sub
It’s fairly simple and my bigger macros are obviously 10-20 times bigger but this gives you an idea. Every time I call a new part of the macro, I write down the time into a tab that is called “log”. Then, I can easily look at the number of time between each step and find out where the delay is happening. I could then repeat that same step in that part in order to find out how I could speed it up.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
