© Roy Cox (2005 - 2020)

.
Where to put your Excel VBA Code

Page Title

Making Excel work for you.
When you find snippets of Excel VBA code that you want to add to your own workbook, it’s important to put that code in the correct place, and this article will help you decide what that place should be. Although about Excel, most of what is described here can be applied to other Microsoft programs. Please note that all screenshots and instructions are based on Microsoft Excel 2019 and may vary slightly for other versions. In VBA the correct term for where your code is stored is a Module. There are four basic types of Module: 1 . Standard Modules are used to store functions, procedures and variables. The procedures in the module can be called by procedures in other modules (including the other four types of module that are discussed below) if they are declared as public by using the Public keyword. 2 . UserForm Modules are custom dialog boxes that you build to collect information from users maybe to input data to a sheet or as a Wizard to lead the user through the stages of a complicated procedure. 3 . Event Modules. As implied by the name these modules store code that it triggered by an event in the WorkBook or WorkSheet, e.g. Opening a workbook, before closing a workbook, changing a selection on a worksheet and many more. 4 . Class Modules are quite advanced programming and basically allow you to create 'Objects' which can be used within your programs

Add your code to a Standard Module

This is the most common type of code that you will use. Typically it will look something like this:
Option Explicit Sub SayHello() MsgBox "Hello World" End Sub

Basic Module

This type of code can include Functions and should be placed in a Standard Module. It will always start with Sub or Private Sub then the code. The last line will be End Sub. If it is a Function then the same applies but replace Sub with Function. Note: the use of the keyword Private before Sub prevents a macro from showing in Macros Dialog Box (Alt+F8). When this is used they can be called by other subs in that Module, or the best way to call them from other Modules would be to use Application.Run "your macro name" Copy the Excel VBA code that you want to use. Open the workbook in which you want to store the Excel VBA code. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor. From the Insert menu choose Module. Where the cursor is flashing, choose Edit -> Paste

How to run your macro

There are several ways to run your macro. I will highlight the main ways, but because this article is to explain the correct places to add your code I isuggest that you read the related articles listed at the right. Creating a short-cut key - you are probably familiar with short cut keys such as Ctrl+ C to copy and Ctrl+V to paste. You can actually add a shortcut key to your own macro. The simplest way to do this is to use the Macro Recorder to record a macro, once you have started the process stop recording and you should have an empty macro with short cut key to edit by re-naming and pasting in your code. Adding a button on your worksheet Assigning the macro to a picture or shape Adding a button to the Quick Access toolbar Create your own button in the Ribbon

Add Code to a Workbook Module with Copy and Paste.

Sometimes you might want a specific action to occur when, for example, the Workbook Opens. This is known as Workbook Event code, the Open event is only one of many events that can be utilised in your VBA Projects. Others include the Before Save event which you might want to use to force the user to save his work before closing, there are other events that can be used t o ensure an action happens automatically on every sheet i n the Workbook. This type of code should be added to the Workbook code module: Copy the Excel VBA code that you want to use Hold the Alt key, and press the F11 key, to open the Visual Basic Editor. Select the workbook in which you want to store the code. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects. Right-click on the ThisWorkbook object, and choose View Code. Make sure that Workbook is selected in the left hand drop down. The default event is the WorkBook_Open event. Explore the available events for the Workbook by clicking the right hand drop down, they are reasonably self explanatory. Paste your code where I have written MsgBox "Hello World". This message will now appear whenever the workbook is opened and macros are enabled.

Add Excel VBA Code to a WorkSheet Module with Copy and Paste.

Another type of code is WorkSheet Event code, and will run automatically when a specific action occurs in the worksheet. For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored in the worksheet module, but it will only work on the sheet that contains the code. To add it to your worksheet, do the following: Copy the code that you want to use. Select the worksheet in which you want the code to run. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor. Make sure that the left hand drop down has Worksheet selected. The right hand drop down is used to select the event that you want to use. By default it will be the Worksheet_SelectionChange event. Note: Never try to type the event name manually. It is so easy to make a mistake and the code will not work. Always select it from the right hand drop down. You cannot have multiple event codes of the same type in any sheet, for example you can only have one WorkSheet_Change event in any one sheet. You can use this event in any other sheet. This also applies to the WorkBook module. If you try to use the same event twice you will receive an error message - "Ambiguous name detected, Workbook_Open", you have tried to add two Workbook_Open events.

Class Modules

A Class Module is a special type of module that allow you to create your own customised objects. You can define the methods and properties for them and even write event handlers. When you create a VBA class module, you can create objects with their own properties. Creating Class Modules is not for a beginner, but if you find one online that you want to use copy it as you would any other code but instead of inserting a Standar Module chose a Class module. and paste the code there. Read the accompanying notes on the source web site.

UserForms

UserForms are a special kind of Class Module. They are usually used to control data entry, but have many other uses. I have several examples of UserForms and will be adding articles to help you create your own UserFors. See the related articles list on this page.

Downloaded VBA code - strange file extensions (.bas, .cls, .frm, .frmx)

Sometimes when you click download on a site you may find the code you have downloaded is a *.bas file. This is not a problem file, *.bas is simply the file format that Excel exports macro code as. You can open the file to view the contents by right clicking on the file and selecting Open with and selecting NotePad. Note: if NotePad is not in the immediate list then click more apps to find it.

The main type of Exported Macro Files

1 . *.bas file: If you export a macro from the VB Editor then all of the code is stored in a text file with this file extension. 2 . *.cls files : Similarly any Class Modules that you export will be stored with the *.cls extension. 3 . *.frm file and *.frx file: When you export your UserForm from a project, it is saved in two files with the *.frm and *frx extension. The frm file holds the description of all objects and their properties for each of the Userform's controls, as well as the basic code that you have written to respond to it's events. These are also referred to as form modules. When exporting a UserForm two files are generated, the second being a *.frx file which stores icons and graphics much like the *.frm file.

Import or Export your VBA code.

After creating a UserForm or Macro for one Excel application, you may decide that you want to use it again, maybe with a few modifications, in another project. You do not need to recreate the UserForm or Macro from scratch, simply export the code to a file that you can import when required for a new application.

Export your VBA code:

1 . Open the workbook that contains the code you want to export. 2 . Press Alt + F11 to open the VB Editor. 3 . Right-click UserForm or Macro in the Project Explorer. 4 . Select Export File. 5 . Navigate to the folder where you want to save your form. 6 . Change the file name if you want and click Save.

Import your VBA code:

1 . Open the workbook that contains the code for your new application. 2 . Press Alt + F11 to open the VB Editor. 3 . Right-click the project name in the VBA Project window. 4 . Select Import File. 5 . Navigate to the file you want to import. 6 . Click Open. 7 . You can use the above steps to export or import any Excel VBA Project object.

Drag and Drop VBA code from one Workbook to another

Many Excel users are unaware that you can copy all the code in a module by doing the following: 1 . Open both workbooks. 2 . Hold the Alt key, and press the F11 key, to open the Visual Basic Editor. 3 . In the Project Explorer, find your workbook, and the workbook with the code that you want to copy. 4 . In the workbook containing the code, click the + sign to view the list of Modules. Click on the module that you want to copy, and drag it to the project where you'd like the copy placed. Release the mouse button, and a copy of the module will appear in the destination workbook. NOTE: The drag and drop method can also be used with UserForms. You cannot do this with Event Code
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee