Excel VBA Programming - Getting Started | 4 - Excel File Types

Summary

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