Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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: