Before diving into the pros and cons of Excel macros, I will start by giving you a definition of macros in Excel and what are they used for.
An Excel macro is an action or a series of actions that are recorded by Excel recorder, at the user’s request, to produce an automated process for repetitive tasks. Excel recorder records every mouse click and keystroke in a VBA code that can be run as much as and whenever the user wants.
The use of macros in Excel can have so many advantages, as well as some disadvantages. In this article, I give you a detailed list of all the pros and cons of using macros in Excel.
1- Advantages of Excel macros
Using recorded macros in Excel offers so many benefits to the user. Here is a list of the main advantages of using macros in Excel.
– Macros automate manual tasks
Automation is the purpose of using Excel macros and is its main advantage. You can use macros in Excel to do a lot of tasks that you do manually and that take time to be done. For example, you can use Excel macros to apply upper case or lower case to a text, to capitalize a text, to apply specific formatting (font and style formatting), to unmerge a lot of cells, to highlight duplicate values, to hide or unhide rows and columns or even worksheets, to protect or unprotect worksheets, etc.
Well, the list goes on indefinitely… So, use your imagination and apply macros for whatever you want; just use them for relatively complex and/or repetitive tasks to get the most out of them.
– Macros allow executing many tasks with a single click
This is the second main purpose of using Excel macros. They allow you to execute complex and multi-step tasks with a single click on the “Run” button from “Macros” command of the “Developer” tab, or with a click on a customized button that you insert on your worksheet.
– Macros save time for long and/or repetitive tasks
By nature, macros will save you time and effort when running them compared to manually executing long and/or repetitive tasks.
– Macros help avoid errors in the execution of tasks
Working for a long time on Excel workbooks that contain complex data can quickly become tiring and lead the user to make mistakes; automated macros can help avoid all the possible errors that may occur during the execution of these long and tedious tasks.
– You can create macros without knowing how to code
Macros are easy to create; you just need to record them using the Macro recorder from the “Developer” tab of Excel ribbon. You don’t have to know how to code to create macros in Excel, nor to run them; however, keep in mind that knowing VBA language can help a lot to edit macros code, in order to clean it, adjust it, or expand its capabilities.
– You can execute macros as much as you want
Excel macros are recorded in the workbook; so, you can run them whenever you want, and as much as you want.
2- Disadvantages of Excel macros
While having so many advantages and giving the user a lot of benefits, Excel macros have also their drawbacks that can keep the user from using them. In the following, I prepared for you a detailed list of all the disadvantages of using Excel macros.
– Macros yield a VBA code with unneeded statements
Recording macros yields a VBA code that is supercharged with unnecessary statements, which can slow the execution of the code, as well as make the debugging longer and harder.
Let’s take the following example of a macro that consists of formatting a range of cells with the following font characteristics: Bold, Italic, Size 16, and Red color.
Here is the code automatically generated by Excel after recording the macro:
Sub BoldItalic16Red() ' ' BoldItalic16Red Macro ' ' Selection.Font.Bold = True Selection.Font.Italic = True With Selection.Font .Name = "Calibri" .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End Sub
What Excel did here is that it took note of all the characteristics provided in the Font group and assigned their values in the code whether they were selected by the user or not.
I could get rid of the false statements and other unnecessary code that I don’t really need for this case and get the following code, much lighter and readable:
Sub BoldItalic16Red() Selection.Font.Bold = True Selection.Font.Italic = True With Selection.Font .Size = 16 .Color = -16776961 End With End Sub
– There is no undo after executing a macro
The undo command of Excel is greyed out after running a macro, and the Ctrl + Z shortcut doesn’t work. This is one of the major drawbacks of using macros in Excel. Hence, you need to be very cautious about executing macros that consist of for example deleting or changing sensitive data.
– Macros increase the size of your Excel workbook
The creation of macros in an Excel workbook adds code to the file, that can be more or less heavy depending on the number of macros and the complexity of the involved actions. This may slow down the Excel workbook a little, and also makes it occupy more space on your device, and heavier for sharing over networks and the internet.
– Macros don’t work in the online version of Excel
Macros don’t work in Excel for the web, the online version of Excel. You can still open an enabled-macro workbook in Excel for the web, but you can’t create a macro or execute it.
– Macros can’t be created for looped actions
You can’t use Excel recorder to create/record macros for tasks that need to be looped; i.e. tasks that need to be repeated a great number of times. For this, you would need to write a loop code manually using VBA language.
– Macros can’t be created for actions that need logic
Obviously, you can’t use Excel recorder to create/record macros for situations that require choosing between two or more sets of actions depending on one or many conditions; e.g. doing this set of actions if this condition is met; otherwise, do these other tasks.
Here again, you’ll need to write your If….. Then….. Else code using VBA language.
– Macros can’t be used in tasks involving external apps
Another obvious thing is that recorded Excel macros can’t be used for tasks that require data from outside Excel or interaction with other applications, simply because you use the Excel built-in recorder that can’t record what’s going on outside Excel. Here again, you’ll need to write this yourself in a VBA code.
– The automated aspect of macros makes them dangerous
Macros are automated, which makes them a little dangerous for important and/or sensitive data, in the way that they can be wrongly edited and then yield an outcome different than the expected one. A much worse thing is when you cannot notice this difference and continue your work assuming that the macro has correctly done its job.
So, be sure to test your macros and double-check their result before applying them to your data.
– Macros can lead to security issues
Macros can lead to security issues as they can hold in them malware codes intentionally injected by a malicious user. So, before executing any macros or sharing them, make sure that you fully trust their source.
– Long use of macros can lead to a lack of knowledge
As you know, the use of macros brings a lot of help to the user; only this automation of actions contains within it a small problem: relying too much on macros, the user gradually loses his knowledge of how to manually do things, even for simple tasks.
If you’re this user that uses macros too much, especially for simple actions, make sure that you check from time to time your knowledge of Excel basic features and how to use them.
As we saw in this article, Excel macros can have a lot of advantages, as well as many disadvantages.
Which aspect outweighs the other? It really depends on the situation and on your goals. Using Excel macros can really save you a lot of time and effort, and makes you so productive, but you need to be aware of all the downsides of using Excel macros and be careful not to harm your data or your system.