Module 1 - Getting Started in VBA
Lesson 1.1 - Your First Program
Topic 1.1.3 - Saving Your Code

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!

Files Needed

You can use the file you have created over the previous parts of this lesson.

Completed Code

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:

Save 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:

Save 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:

Save As

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:

Warning about save

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:

Save as 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.

Save 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:

Enable content

Click Enable Content to make sure that you can run your code.

If you have the VBE open, the message is much more obvious:

Enable Macros

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.

This page has 0 threads Add post