The speed issue is a common complaint from users of Excel Macros. I would love to share my personal experiences regarding speed issues of Excel macros which I experienced during my programming career since 2005. Besides these 10 techniques, there are some other minor factors you have to follow.

1. Disable screen updating, Events and auto calculation at the start of the Macro. Because these auto Functions delay macro tasks at runtime.

2. Avoid using Events under Sheets like Worksheet_Change, Worksheet_SelectionChange. If you are bound to use to automate some tasks, then use:
Application.EnableEvents = False
Yes, each time the user updates a cell, these two Events will work and thus Excel will perform slower.

3. Avoid putting formula through VBA. Try to calculate through VBA to get results rather than putting formula in cells. This adds to the overhead load to the macro.
Example: You may need to put Formula in Z100 as =SUM(Z2:Z99). Instead, use Application.WorksheetFunction.Sum(Range). This way, you can get the desired Sum without using Formula.

4. Avoid long Sub with thousands of lines in VBA, rather split by sections and call from the Main Sub.

5. Write Functions in VBA as many as possible for performing the same tasks/calculations and call the Function from the main Sub.

6. Use “With” statement, for Objects, to minimize the number of words which help macros
When you write 10-15 lines of code for a sheet like below:

7. Avoid using too many IF . . . . Then Statement. For IF…. Then Statement Macro needs to check each block of IF . . . Then to discover if the logic is True. This takes time to check each Statement. So use SELECT Case. VBA goes directly to the related block where Case IS = True rather than checking each IF…. Then Statement. This way your Macro will definitely run faster.

8. Try to use Arrays where possible.

9. Avoid using too many Sheet(Name).Activate or Select. This command forces Macro to switch to this and that sheet and thus takes a longer time. In most of the cases, you can avoid Activate or Select command.

10. Not responding: This is very harmful to the programmer’s reputation and also, embarrassing for the user. Main causes are: Do . . . . Loop or For . . . . Next code or if Macro handles large data sets with 100K rows or more. You can copy half of the data to a new sheet and run the process twice and then consolidate it back. In Loop Function, you can command half of the processing tasks and do the same twice. Also, select the fewest most columns instead of selecting the entire used area.

— Momtaz U, Owner of CoderJc Solutions