Excel VBA Programming - Getting Started | 4 - Excel File Types
Summary
TLDRThis lesson explains the importance of saving Excel workbooks with macro code in the correct file format. The default '.xlsx' does not support macros, so it's crucial to use '.xlsm' to preserve code. The tutorial demonstrates how to save in '.xlsm' format and shows how to set this as the default to prevent accidental loss of macros. It also advises on toggling this setting based on Excel usage outside the course.
Takeaways
- 📚 The default Excel file extension 'xlsx' does not support macro code.
- 🔄 To preserve macro code, workbooks should be saved in a specific file type.
- 💡 Use the 'Control Plus S' keyboard shortcut to access the save prompt in Excel.
- 📁 The 'Excel Workbook' file type is the standard default, but it prohibits macros.
- 🚫 Saving macros in a regular '.xlsx' file will result in a warning from Excel, and macros will be lost upon reopening.
- 🔑 The correct file format for macro-enabled workbooks is '.xlsm', which stands for 'macro'.
- 🌟 '.xlsm' files look and function the same as regular Excel workbooks but allow macros to be written and preserved.
- 🛠️ Throughout the course, it's recommended to use the 'Excel Macro-Enabled Workbook' format for saving files.
- ⚙️ There's an optional setting to change the default file type to '.xlsm' for ease of use in the course.
- 🔄 The 'Excel Options' pane allows you to change the default save format to 'Excel Macro-Enabled Workbook'.
- 🔄 This setting is global for Excel and can be changed back if needed for other uses outside the course.
Q & A
What is the default file extension for Excel workbooks?
-The default file extension for Excel workbooks is 'xlsx'.
Does the 'xlsx' file extension support macro code?
-No, the 'xlsx' file extension does not support macro code.
What is the recommended file type to save Excel workbooks containing macro code?
-The recommended file type to save Excel workbooks containing macro code is 'xlsm', which stands for Excel Macro-Enabled Workbook.
What happens if you save macro code in an Excel workbook with the 'xlsx' extension?
-If you save macro code in an Excel workbook with the 'xlsx' extension, Excel will give a warning that it does not support macros. If you ignore the warning and save, the macro code will be lost when the workbook is reopened.
How can you ensure your macro-enabled workbook is saved in the correct format?
-To ensure your macro-enabled workbook is saved in the correct format, choose 'Excel Macro-Enabled Workbook' from the 'Save as type' dropdown when saving the file.
What is the keyboard shortcut to bring up the save prompt in Excel?
-The keyboard shortcut to bring up the save prompt in Excel is 'Control Plus S'.
What is the difference between a regular Excel workbook and a macro-enabled workbook in terms of functionality?
-A regular Excel workbook and a macro-enabled workbook have the same functionality. The only difference is that macro-enabled workbooks allow macros to be written and preserved.
How can you change the default save file type in Excel?
-You can change the default save file type in Excel by going to 'File' > 'Options' > 'Save', then selecting 'Excel Macro-Enabled Workbook' from the 'Save files in this format' dropdown and clicking 'OK'.
Why might you not want to change the default save file type to 'xlsm' for all Excel uses?
-You might not want to change the default save file type to 'xlsm' if you use Excel for purposes outside of macro programming, as it could lead to accidentally saving non-macro workbooks in a format that supports macros.
Is changing the default save file type a permanent setting in Excel?
-No, changing the default save file type is a global setting in Excel, but you can change it back if needed for other uses.
What is the significance of the 'm' in the 'xlsm' file extension?
-The 'm' in the 'xlsm' file extension stands for 'macro', indicating that the file format allows for the inclusion and preservation of macro code.
Outlines
📊 Excel File Types for Macros
The lesson focuses on the importance of selecting the correct file type in Excel to preserve macro code. The default Excel file extension 'xlsx' does not support macros, so a specific file type is necessary. The instructor demonstrates how to access the 'Save As' dialog and navigate to the 'Save as type' dropdown to choose 'Excel Macro-Enabled Workbook' with the '.xlsm' extension. This format allows macros to be written and preserved. The instructor also explains how to change the default save type to '.xlsm' in Excel options to streamline the process and prevent accidental loss of macro code.
Mindmap
Keywords
💡Excel
💡File Types
💡Macro Code
💡Save
💡Backstage View
💡VBA Course Folder
💡Save As Type
💡Extension
💡Macro Enabled Workbook
💡Excel Options
💡Default Save Type
Highlights
Default Excel file extension xlsx does not support macro code.
Workbooks must be saved in a specified file type to preserve and run macro code.
Using the keyboard shortcut Control Plus S brings up the save prompt.
The standard Excel workbook type prohibits macros, with xlsx as its extension.
Excel warns if you try to save macro code in a standard workbook.
Ignoring the warning can result in the loss of macro code upon reopening the workbook.
To preserve macros, workbooks should be saved as 'Excel Macro-Enabled Workbook' with the xlsm extension.
The 'm' in xlsm stands for macro, indicating macro-enabled workbooks.
Macro-enabled workbooks have identical functionality to regular Excel workbooks, with the added ability to write and preserve macros.
Throughout the course, the xlsm file format will be used for all workbooks.
Changing the default save type to xlsm simplifies the saving process and reduces errors.
Accessing the 'Options' in the backstage window allows for changing the default file type.
Selecting 'Excel Macro-Enabled Workbook' as the default save type ensures macros are preserved.
This setting change is a global Excel setting and can be reverted if needed.
Using xlsm as the default save type adds efficiency and prevents accidental loss of macro work.
The key takeaway is that all macro-enabled workbooks must have the xlsm extension.
Transcripts
in this lesson I want to talk about the different file types available in Excel as it turns out the
default Excel file extension xlsx does not actually support macro code so we'll need to
save our workbooks in a specified file type in order to ensure that our code is preserved and
that it can run the next time we open the workbook on my screen here I have a brand new unsaved Excel
workbook so let's go ahead and get to the save prompt I'm just going to press the keyboard
shortcut Control Plus s that'll bring us to the backstage View and in this case I'm just going
to select anything that's going to bring up the save prompt we're not actually going to be saving
so it doesn't matter right now I'm just going to bring up the VBA course folder on my desktop
where I asked you to unzip all of the course files right down here we'll see this option save as type
and you can see the option that's listed here is Excel workbook that is the standard default file
type and in this standard default file type macros are actually prohibited it looks like right here
Excel is actually omitting the extension here but the extension for an Excel workbook a plain one
is xlsx and it is no good if we write our macro code in here and then we save it Excel will give
us a warning that says hey you're trying to save macro code in here I don't support it if you for
some reason ignore that warning which is highly likely because many times we just kind of save
and click yes and don't actually read the prompt on the screen if we accidentally save code in a
regular Excel workbook the next time we open it the code will be completely gone and all of our
work will be lost so it's very important that we save our work in the proper format if we select
this drop down what we really want to do is always save our workbooks at least in this course in this
format the Excel macro enabled workbook this has a file extension of xlsm you can always think of the
m at the end as being short for macro these macro enabled workbooks are identical to the regular
plain Excel workbooks they look exactly the same they have all the same functionality the only
difference is that they allow macros to be written and preserved we're going to be sticking with this
file format throughout the entire course so there is one thing we can do to simplify the process
of choosing this option as the default save type otherwise every single time you'll save a workbook
it's going to default to excel workbook and you might not properly save it in the right format
it's a little bit of a hassle to keep selecting the right choice with that said if you're using
Excel for other reasons outside this course you may not want to select this one as the default so
what I'm about to show you is an optional setting this is setting the default file type as xlsm you
don't have to do this but it's going to help make the course a little bit easier and make sure that
you don't have to remember to do this every single time we save a file I'm going to quit out of my
save as window right here let's just click cancel and on the left side of my backstage window we
have this options option let's select it this is going to open up this Excel options pane it looks
like it took me back to excel as well and on the left side here I have save I want to select that
and right here we see save files in this format and the default xlsx extension is listed here
that's the Excel workbook so if we want to change the default save type we simply click this select
Excel macro enabled workbook and click OK and that will from this point forward if I try to
save again ensure that the right file format is listed here we go I just brought up the save as
window and we can see it says macro enabled workbook now this is a global Excel setting
but you can go ahead and change it back if you ever need to again if you're using Excel for
other reasons and you don't want to accidentally save your workbooks as macro enabled workbooks you
might not want to choose this optional setting but if you want to add a little bit of efficiency the
through to your progression throughout the course this might be worth taking some time to enable
that's all there is to cover here the key takeaway is all workbooks that have macro code must have
the Excel SM extension which is depicted here with the file type Excel macro enabled workbook
関連動画をさらに表示
Excel VBA Programming - Getting Started | 8 - Absolute vs Relative References I
Excel Like a Boss: Create Macros with ChatGPT
Learn HTML favicons in 2 minutes! 🗿
How to Create MIS Report in excel | Impressive and Interactive MIS Report In Excel | Simplilearn
Excel VBA - Compare Two Lists and Remove Duplicates
Learn CSS in Hindi with animations
5.0 / 5 (0 votes)