Why to Use Macros
There are several reasons to use macros but the obvious answer is that they can save you a tremendous amount of time. From creating shortcuts for common tasks to automating large, complex, or repetitive processes, macros are probably the most powerful tool in Excel, and the rest of the Microsoft Office suite too. You can multi-task by running a processing-intensive macro in the background while you make a phone call, respond to emails, read an article, or get something else done. You can focus your time on more valuable activities like analysis.
Macros also help reduce the risk from manual errors by performing the programmed steps every time and in precisely the right order (assuming any prerequisites tasks have been completed, of course). They can also help you identify possible exceptions and perform validation checks, such as whether certain files exist or whether the data you need is available.
Macros in Excel and other Microsoft Office applications are programmed in Visual Basic for Applications (“VBA”). Even though VBA is a formal programming language, it is very English-like, easy to learn, extremely well-documented with endless resources on the Internet, and Excel even has a built-in macro recorder that can help you learn the code you need. While programming is not for everyone, I’m often amazed at how many people are able to create macros in Excel to speed up their tasks.
You can also create custom functions with VBA, which can be used within macros, other custom functions, and even within the formulas in your worksheets. You can perform calculations that you otherwise couldn’t with built-in functions, or condense a long formula with lots of functions into one to increase clarity for reviewers. Just remember that if the custom macros are not saved within your workbook or otherwise shared with other users, the formulas will result in a #NAME? error.
How to Use, Record, and Edit Macros
You can record a basic macro in just minutes using the macro recorder. Just remember that the macro recorder will record virtually every step you take, so be careful! You’ll want to have the Develooper ribbon tab visible to record and edit macros, so if it’s not already visible in your ribbon, you can display it by right-clicking the ribbon, clicking the Customize the Ribbon option, and checking the box next to “Developer” on the right side of the prompt box.
I recommend writing down the steps you want to record to make sure you complete them all and in the right order. Once you’re ready to record your steps, you can click the Record Macro button. A prompt will pop up asking you to name your macro (which must start with a letter and contain only letters, numbers, and underscores), and whether to save it in your personal macro workbook (available to all workbooks) or to a workbook (only available when that workbook is open). You can also optionally set a keyboard shortcut and add a description. Once you click OK, you are in record mode. Perform the steps you want to automate, then click the Stop Recording button within the Developer ribbon tab.
To edit your macro, you’ll want to use the Visual Basic Editor. Click the Visual Basic button within the Developer Ribbon tab, or click Alt+F11. The Visual Basic Editor window will open up and you can find your macro code in the related module. If this is the first macro in your personal macro workbook or the active workbook, Module1 will be created. When you double-click this in the project window, it will open up a window with your code. From there, you can scan through it, remembering the steps you took, and most of it will probably make sense. Modify it based on your needs, referencing the many resources available all over the web. Note that when using the recorder, often several extra unnecessary lines of code are added that you may want to delete. If doing this, you might want to make a backup copy of your code (I just use Notepad) in case you accidentally delete something you need. See the video or related example files below for an example of recording a simple macro.
Recent Comments