During Atlanta Excel Training’s seminars, one of the most eye-opening features that we discuss is the ability to create Excel macros. Attendees get excited of not only learning how to create Excel macros, but also finally figuring out what the heck Excel macros are/do. In the spirit of Excel macros I’ve created an overview (not really a full tutorial) below. When reading this blog, see if this description/overview might pique your interest into learning more about Excel macros.
An Overview of Excel Macros
Macros are a powerful automation tool used for completing routine tasks and used predominantly in Excel, but they also can be created and used in the other software versions of Microsoft office.
What the Heck Do Excel Macros Do?
Macros are a time-saving feature to help you complete tasks that share a common sequence of events. You can also create a shortcut which instructs the computer to carry out this “automated sequence” for you. Excel Macros are fully customizable allowing you to create your own personal macros to perfectly fit your Excel needs – and as a result there are numerous common uses for macros such as:
- Cleaning and reformatting data that is entered in Excel
- Compiling weekly / monthly data into reports
- Inserting identical text sequences into worksheets where a simple copy and paste isn’t sufficient in coping with the task
- Inserting identical function commands into worksheets by way of a single button
How does Microsoft do it?
Visual Basic for Applications (VBA) is a simplified programming language created by Microsoft which allows you to build your own executable files to perform these automated macro procedures and you can then choose to activate the macro in a number of ways from buttons inserted into a worksheet or toolbar, creating dialog boxes or a nominated key command in the same way that ctrl + C operates the copy function or ctrl + V triggers paste.
Creating a Macro
There are two basic ways that you can create a macro, recording or writing:
- Recording a macro is by far the easiest method for beginners and it literally is the process of pressing record, performing the task(s) you wish the macro to include, then stop recording. It does have serious limitations insofar as some operations can’t be entered into a macro using this technique but it is a great introduction to macros before you move onto coding as it teaches you the structure.
- Writing Visual Basic code is a very advanced method to create automated macros, and can be pretty intimidating. But having the ability to analyze the VB code of a macro will allow you to both edit existing macros and construct more complex operations into your work. Once you have recorded a few macros as above (and take a peek at the code) you will begin to understand the way that they are constructed, frequent command types, and also become familiar with the basic visual editor which stores the macro code and links it to the correct Excel workbook. Once you get the hand of recording an Excel Macro, and then start dipping your toe in the proverbial “VB Coding water” you’ll find that you’ll slowly start building your knowledge of coding through online tutorials and guides. This can be a daunting task, but most of the new knowledge is simply finding out what is possible with Excel macros and how to write them.
A Couple of Useful Macro Tips
An Excel workbook is not always “enabled” to use macros, so if you plan to incorporate them into your work the first step should be to check that the Excel macro capabilities is active by “enabling macro content”, a setting usually found in the “Excel Options” area under the File Tab – and within the “security” section.
If you build a macro that is dedicated to a specific workbook, and you want to have the capability to share that workbook with others so they can use it, then you’ll want to remember to save the file as an .XLSM file (Excel Macro-Enabled Workbook). This .XLSM file is slightly different than the regular workbook files like (.XLS or .XLSX), and will enable the macro/workbook to be sent to others so that they can use it.
Also keep in mind that if you start creating Excel Macros, and have an IT department at your office, you may run into security problems/flags/warnings because of the “extra security” that may be implemented on your company’s system. These extra security settings are because hackers are always trying to find ways to hack into systems, and one way is to create a malware/virus that looks like a harmless macro. IT Departments may create extra security protocols that end up making it hard to create, use, and share Excel Macros.
When you first start to create your own macros, larger and more complex automations are best done first in sections or separate sequences. This allows you to test each part individually so that in the event the macro doesn’t work as desired, you can more easily troubleshoot the problem because it’s in separate parts. Then if you really feel up to the task, you can compile all those parts into one big comprehensive macro.