Repetitive activity? Record a macro. Right?
WRONG!
Find out why…
Contents
Macros
Of course VBA is a powerful facility within Excel (and other Office tools). But most regular users cannot understanding programming. They just use Excel using the menus, options and functions.
Somehow, this wrong concept has been ingrained in the minds of most people – including savvy users (also called Power Users) and developers –
If something is needed to be done repetitively in Excel, record a macro!
Unfortunately, this is a wrong thought process.
Let me explain why.
Repetition = Inefficiency
If some activity is being performed again and again, time is wasted. There is a genuine need to eliminate this wastage. That is usually done with automation.
In my experience, repetitive activity usually means that the currently used method is INEFFICIENT.
Read this article for details: Repetition = Inefficiency
Recording a Macro = Automating Inefficiency
Not a nice idea.
Solution: Find the most optimal method
In case of Office, if you are performing the same activity again and again – there is a good chance that Microsoft has already noticed this wasteful activity and created some feature to solve this problem.
The problem is that you may not have noticed the solution. And trust me, there is a very high chance of this occurrence.
Therefore, the simplest solution is to ASSUME that there is a better way and then find it. In fact, you should find the best possible way.
Record macro if the efficient method is repetitive
This is a genuine use of recording macro feature.
Example
Look at this type of data… typical report generated from business systems using Export to Excel option.
Usually you will need to remove the page headers and footers (the gray area) to keep the tabular data in between. Once all the unwanted headers and footers are removed, we have data in a tabular form which can be used for further analytics using Pivot Table, Power View and Power Map.
This is a repetitive process and therefore you may be tempted to write a macro to remove the unwanted rows.
In fact, removing blank rows from columns and rows is easily done using GoTo Special Blanks or Power Query. That is why there is no NEED to record a macro in this case.
Another type of macro: Adding features which are missing
This is a genuine and appropriate usage scenario for WRITING a macro (VBA code). If you want a specific functionality and Office does not offer it, you can write the code yourself and EXTEND the capabilities of the product.
Here are two examples where I have written macros to get new and useful functionality.
Paste Special Pivot – New way to use Pivot table data
Marketing: How to create “Our Customers” Slide in seconds
Of course, there are millions of macros people have created globally which fall into this category.
Learning Opportunity
It is a good idea to relook at your existing recorded macros and check if you have missed any existing feature which can solve the problem WITHOUT the macro.
***
