MS-Excel / General Formatting

---

Working with Modules

You've seen so far that modules are where most of the VBA action takes place. True, you've also seen that much VBA work happens within user form windows and the Properties window, but modules are really the heart of VBA. Given that, it will help to have a few module manipulation techniques under your belt. To that end, the next four sections show you how to rename, export, import, and remove modules.

Renaming a Module

When you insert a new module, VBA gives it an uninspiring name such as Module1. That's fine if you'll just be using the one module in your project, but if you'll be working with multiple modules, you should consider giving meaningful names to each module to help differentiate them.

To rename a module, follow these steps:

1. Select the module in the Project Explorer.
2. In the Properties window, use the (Name) property to rename the module. Make sure the name you use begins with a letter, contains no spaces or punctuation marks (underscores are acceptable, however), and is no longer than 31 characters.

Exporting a Module

The procedures and functions in a module are usually specific to the application in which the project was created. For example, procedures in a Word-based module usually reference Word-specific objects such as bookmarks and paragraphs. However, you might have generic procedures and functions that can be used in different contexts. How, then, can you share code between applications?

One way to do it would be to use the Clipboard to copy data from one module and paste it in a module in a different application. Another way is to export the module to a BAS (.bas) file. In the next section, I'll show you how to import BAS files into your VBA projects. The BAS (Basic) file format is the one used by Visual Basic modules (which means you could use your VBA code in a Visual Basic project), but it's really just a simple text file. Here are the steps to follow to export a module:

1. In the Project Explorer, click the module you want to export.
2. Choose File, Export File, or press Ctrl+E. The Visual Basic Editor displays the Export File dialog box.
3. Select a location and type a filename for the BAS file.
4. Click Save. The Visual Basic Editor creates the new BAS file.

Importing a Module

If you exported a module to a BAS file, you can import that file as a module in another application's VBA project. Also, if you've used Visual Basic before, you can leverage your existing code by importing Visual Basic modules into your project. Here are the steps to follow:

1. If you have multiple projects open, use the Project Explorer to click any object in the project you want to use to store the imported file.
2. Choose File, Import File, or press Ctrl+M to display the Import File dialog box.
3. Click the BAS file that you want to import.
4. Click Open. The Visual Basic Editor adds a new module for the BAS file.

Removing a Module

If you no longer need a module, you should remove it from your project to reduce the clutter in the Project Explorer. Use the following technique:

1. Click the module in the Project Explorer.
2. Choose File, Remove Module, where Module is the name of the module.
3. The Visual Basic Editor asks if you want to export the module before removing it:
   • If you want to export the module first, click Yes and use the Export File dialog box to export the module to a BAS file.
   • Otherwise, click No to remove the module.