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.

Outlines

00:00

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

Keywords

💡Excel

Excel is a widely used spreadsheet program developed by Microsoft for Windows, macOS, Android, and iOS. It is part of the Microsoft Office suite and is known for its ability to organize, analyze, and manipulate data using a grid of cells. In the video, Excel is the platform where the user is working with macros, indicating its use for more complex data management tasks.

💡File Types

In the context of the video, file types refer to the different formats in which an Excel workbook can be saved. Each file type has specific features and compatibility with certain functionalities, such as the ability to support macro code.

💡Macro Code

Macro code in Excel refers to a sequence of instructions that automate repetitive tasks within the program. It is written in a programming language such as VBA (Visual Basic for Applications). The script emphasizes the importance of saving macro-enabled workbooks in the correct file type to preserve this code.

💡Save

The save function in Excel is used to store the current state of a workbook to a file. The video discusses the importance of using the save function with the correct file type to ensure that macro code is not lost upon reopening the workbook.

💡Backstage View

The backstage view in Excel is a hidden menu that provides access to file management options, such as saving, opening, and printing. The script mentions pressing 'Control Plus S' to bring up the backstage view and access the save prompt.

💡VBA Course Folder

The VBA course folder mentioned in the script is a directory on the desktop where the user has unzipped the course files related to learning VBA in Excel. It serves as a workspace for the user to practice and save their macro-enabled workbooks.

💡Save As Type

This refers to the option in Excel that allows users to choose the file format in which they want to save their workbook. The script discusses the importance of selecting 'Excel Macro-Enabled Workbook' to preserve macro code.

💡Extension

An extension in computing is the suffix at the end of a file name that indicates the file type or format. In the script, 'xlsx' is the default extension for Excel workbooks without macro support, while 'xlsm' is used for macro-enabled workbooks.

💡Macro Enabled Workbook

A macro-enabled workbook is an Excel file that has the capacity to store and run VBA macro code. The script highlights that this type of workbook uses the '.xlsm' extension and is essential for users who work with macros.

💡Excel Options

Excel options refer to the settings within the Excel application that allow users to customize various aspects of the program. The script describes how to access these options to change the default file type to 'Excel Macro-Enabled Workbook' for convenience.

💡Default Save Type

The default save type is the file format that Excel automatically selects when the user saves a workbook. The video provides instructions on how to change this setting to 'Excel Macro-Enabled Workbook' to streamline the saving process for users working with macros.

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