File Types & Saving

Quick reference

• 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.