3. Undo, Saving and Distributing Macros

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

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.

Lesson Notes

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

Saving 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

Keyboard shortcuts

F12: Save As ALT + L , R: Stop / Start recording Macro
ALT + H , H: Change background color
ALT + F8 : Open Macro dialogue box

Transcript

In the previous lesson we created a macro that used the shortcut Ctrl + Shift + Y to change the formatting of our input cells. When I used 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 Ctrl + Z 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 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 are 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 on 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 2, 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 Ctrl + 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 OK. And in this macro I'll simply change the background color of this cell to a highlighter yellow. Then I'll press Alt + L and 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, finalInput and percentInput. 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 off and I'll touch on these in the next lesson.