Now that you've successfully written a program, you'll want to save the code so that you can use it again in the future. This part of the lesson describes how to choose the correct file type to ensure that your code is saved properly.
You don't need to save your code before you run it, but it's good practice to do so. If your code does something unexpected, it's nice to have a backup copy of your file to return to!
You can use the file you have created over the previous parts of this lesson.
You can click here to download a file containing the completed code.
Choosing to Save Your Code
When you save the VBA code that you have written, it is saved along with the workbook into which you inserted the module. You can choose to save your code in either the Excel window or the VBE window. To save in the VBE:
In the Project Explorer, select an item which belongs to the file you want to save and then click the button shown. You can also choose File | Save from the menu or press Ctrl + S to save the file.
To save your code in Excel:
In Excel, select the workbook that you want to save and then click the button shown. You can also choose File | Save from the ribbon or press Ctrl + S to save the file.
Assuming that this is the first time the file has been saved, you'll be presented with the Save As dialog box. Here you can provide your file with a name, choose a location and set the file type:
Once you've chosen a location and entered a file name you can click Save to save the file.
If you attempt to save your file using the standard file type - Excel Workbook (*.xlsx) - you'll encounter a warning message:
You can't save VBA code in a normal workbook. Click No to return to the Save As dialog box.
Choosing a Different File Type
If you see the message shown above you should click No to return to the Save As dialog box so that you can choose a different file type:
You can save VBA code in an Excel Macro-Enabled Workbook, an Excel Binary Workbook, or an Excel 97-2003 Workbook.
Once you've chosen the appropriate file type (for our example we'll choose Excel Macro-Enabled Workbook), click Save to save the file.
Here we've selected Excel Macro-Enabled Workbook.
Opening Files Containing Code
The first time you open a workbook in which you've saved VBA code, you'll be asked to confirm that you want to enable the code. This is a security feature designed to prevent malicious code from running automatically when you open a Microsoft Office document. The way in which you enable your code depends on whether you have the VBE open. If the VBE is closed, you'll see a small banner appear just below the ribbon:
Click Enable Content to make sure that you can run your code.
If you have the VBE open, the message is much more obvious:
Click Enable Macros on this dialog box to ensure that you can run your code.
If you forget to enable your code, when you choose to run it (as described in the next part of this lesson) you'll see a warning message:
You'll see this message when you attempt to run disabled code.
To resolve this you'll need to close the workbook, reopen it and choose to enable your code when prompted.
You don't have much to practise yet! Move on to the next part of this lesson to learn how to run your code.