3. Undo, Saving and Distributing Macros
Macros can create a few headaches when you want to undo, save or distribute macros. I'll address these problems in detail during this lesson.
Can't UNDO Macros
- After running a Macro in Excel, all the actions stored in Undo are removed
- As a result, you can't undo a Macro action
- For this reason, be sure to exercise extreme caution when using Macros
- You have two different options when saving Macros
- Option 1: Save a Macro to the currently open workbook
- Option 2: Save a Macro to Personal.xlsb
- Personal.xlsb is a special file that automatically opens in the background when you start Excel
- Store a Macro in this location if you want to access it in all of your workbooks
Distributing files as Macro-enabled workbooks
- Macro-enabled workbooks are stored with a .xlsm extension
- If you don't save your workbook with this extension, the attached Macro cannot be accessed
F12: Save As ALT + L , R: Stop / Start recording Macro
ALT + H , H: Change background color
ALT + F8 : Open Macro dialogue box
In the previous lesson, we created a macro that used the shortcut control shift Y to change the formatting of our input cells. When I use the macro shortcut, the formatting change can save us quite a lot of time. If I want to undo the formatting change, you would expect that I could press control zed to undo. But no matter how many times I press it, it doesn't seem to be working. And it turns out, we've just stumbled across one of the weaknesses of macros. When you run a macro by pressing the shortcut, you remove all of the actions stored in the undo stack in Excel. It's actually impossible to undo a macro action, which as you can imagine, is both inconvenient and potentially dangerous for long term data loss. So when creating and running macros, always be aware that the undo action won't be available to you. Now let's move on to saving macros. When saving macros, you have two options, for two different use cases. The first option is to save the macro within the Excel file. And the second option is to save the macro to a special hidden file called personal.XLSB, that runs in the background every time Excel is opened on your computer. For macros that are critical to the functioning of one particular document, you're better off saving the macro to the file, particularly if you want to distribute the file to other users. To save a macro within a file, go to the save as menu, using the shortcut if you want, F12. And in the save as type, select a macro enabled workbook.
And this will save the file with the extension .XLSM. When you send this file to a colleague, she will have access to the macro as well. To use this macro in other Excel files in your computer, you'll need to have this particular macro enabled file open on your computer.
For macros that help with formatting, you'll probably use these repeatedly in many of your Excel sheets. So it's better to use option two, which is saving to the personal XLSB file.
Let's create a new macro with the shortcut Alt L and then R.
And I'll call this macro highlighter.
And the shortcut key will be control shift W.
This time, I won't store the macro as part of my Excel file. I'll store it in the personal macro workbook. And I'll skip a description and just press okay.
And in this macro, I'll simply change the background color of the cell to a highlighter yellow.
And then I'll press Alt L and then R to stop recording.
If I now press Alt F8, I can see that in the personal.XLSB file, I have the highlighter macro included.
While in the Excel file itself, I have the previous two macros included. Final input, and percent input.
As I've said earlier, the location you choose for saving macros depends heavily on the type of macro you've created. I tend to put time saving formatting macros in the personal file, but then file specific macros in a macro enabled workbook.
In macro enabled workbooks, there are some security issues you should be aware of, and I'll touch on these in the next lesson.