Locked lesson.
About this lesson
A discussion on which files types support macros, and why it is critical to save your work before moving forward.
Quick reference
File types & saving
Saving a file that contains macros.
When to use
When you want to save your new macro.
Instructions
Saving the File
- Go to File --> Save As --> Browse and find the location to save your file.
- For the “Save as type” field, select a file type that supports macros
Choosing a File Format
File formats that support macros:
- .xlsm (recommended)
- .xlsb
- .xls (legacy format)
File formats that are not macro-compatible:
- .xlsx (default file format)
Hints & tips
The following tips give you fall back points in case something goes wrong at any stage (just close without saving changes)
- Save your file before you record your macro
- Save your file again after you record your macro, but before you run and test it
- 00:04 Before we go any further with this file,
- 00:06 the next thing that we need to talk about is about file types and saving.
- 00:11 Because there's a couple of things that I recommend you do with your Macros.
- 00:14 Number one, save before you record your Macro, and then number two,
- 00:17 after you've created, immediately save it, the file.
- 00:20 Again, before you start actually playing with it and trying it out, why?
- 00:25 Well, the number one thing to do here, save before you run,
- 00:29 because there is no undo in Excel when you're using Macros.
- 00:34 The mere presence of triggering a Macro to run at all,
- 00:38 essentially is equivalent to blowing Excel's mind.
- 00:41 And all of the stuff that you had in the undo stack, all disappears.
- 00:46 So, you really,
- 00:46 really wanna make sure that you've got a good fallback point to fall back on.
- 00:50 This is why we save before we actually run our macros when we're in development mode.
- 00:55 Not necessarily when we're using one, regular basis,
- 00:57 cuz you will have tested them by then.
- 00:58 But when we're in development mode, we always wanna save before we run it.
- 01:02 That way if something goes horribly wrong we can close the file and
- 01:06 throw it away without saving changes.
- 01:07 And we haven't completely messed up our entire scenario, okay.
- 01:11 So this is something really important,
- 01:12 save your file before you run it when you're in development mode.
- 01:16 Now, the challenge that we've got here is that there are certain file
- 01:19 types that support Macros and there's certain ones that don't.
- 01:22 The ones that do, the old Excel 97 to 2003 xls format, does support Macros.
- 01:29 You'll also see a file extension called xlsm,
- 01:32 this is one of the new file extensions that came out in Excel 2007.
- 01:37 The m stands for Macros, so if it has an m at the end, it will contain and
- 01:41 run your Macros.
- 01:42 The xlsb is a binary file type, it also will contain Macros,
- 01:47 so those three are safe.
- 01:48 Now, the challenge with the xls format, of course, is if you save in that format,
- 01:52 you'd lose things that aren't compatible with the old worksheets like tables and
- 01:56 power pivot if you're using that, or all kinds of different things.
- 02:00 So, you generally don't wanna be in xls, you probably want to be in xlsm,
- 02:04 that is the most logic one.
- 02:05 The challenge though is there is one other file type that doesn't support Macros and
- 02:11 it is called xlsx.
- 02:13 Xlsx can never, ever, ever have a Macro in it, that's important.
- 02:18 If you see an xlsx file, you know that there's no Macros in it, so
- 02:23 you can open it without worry.
- 02:24 Okay, no problem whatsoever.
- 02:25 If it's an xlsm, it may or may not have a Macro in it, so be aware of that.
- 02:30 Big challenge though, this is your default file save format,
- 02:35 even when you're recording Macros.
- 02:37 It will warn you but if you save your Macro laden file in an xlsx format,
- 02:42 it will eat the Macro and next time you open up that file it will not be there.
- 02:46 So, as you are now a developer of Macros, you want to be very,
- 02:49 very keenly aware that when you save your file.
- 02:52 If you wanna keep you Macros around, it needs to be an xlsm or
- 02:55 xlsb format, let's just say, I generally go with xlsm that sort of my preference.
- 03:00 Let's go take a look at exactly how this goes,
- 03:02 we're gonna jump back to our old project and just save this out right now.
- 03:06 So here we are, we have the code in this workbook, what I'm gonna do now is I'm
- 03:10 gonna hit File, I'm gonna choose Save As, I'm gonna go to browse.
- 03:16 I'm gonna put this in a different folder, we're gonna go to the file types and
- 03:20 saving folder.
- 03:22 And, what you'll notice is that my
- 03:24 workbook is saving as a save as type Excel Workbook (*.xlsx).
- 03:29 Now, if you don't see the *.xlsx, that just means that you have file
- 03:32 extension types hidden on your computer, I'd recommend that you show them.
- 03:36 But, in this case, the big key is, when I go and click on that, you'll notice
- 03:41 that xlsx, which is what it's starting with, is the default save as type.
- 03:46 You'll also notice that the next one down is the one that actually allows Macro.
- 03:50 So we could save it here but if we try this,
- 03:53 it should give us a warning about whether or not, we wanna keep our Macros.
- 03:56 But the big key we wanted really focus on is, let's open the Macro-enabled workbook.
- 04:02 We'll say, call it Continuity Schedule, I'll get rid of the start here.
- 04:07 So Continuity Schedule, I can eat the end of this thing off,
- 04:09 it'll put the xlsm on for me.
- 04:12 We'll say, Save and now I've got my Continuity Schedule.xlsm, okay?
- 04:17 So that's kind of an important piece because now, I know,
- 04:20 that my macro is actually going to be in the file when I come back to it.
- 04:23 And that's really, really important.
Lesson notes are only available for subscribers.