MrExcel.com - Your One Stop for Excel Tips & Solutions

Create A Regular Macro

May 14, 2021 - by

Create A Regular Macro

Challenge: You’ve found a cool VBA macro on the MrExcel message board. Your workbook doesn’t currently have any macros. How can you get the macro into your workbook?

Solution: You can type regular macros in a module in the VBA editor. Follow these steps to insert a new module:

  1. Open the workbook in which you want to save the macro.
  2. Switch to the VBA editor by pressing Alt+F11. Alternatively, you could select Tools, Macro, Visual Basic Editor in Excel 2003 or Developer, Visual Basic in Excel 2007.
  3. From the VBA editor menu, choose Insert, Module. A new blank code pane appears on the right side of the screen.
  4. Copy the code from the webpage. Click in the blank code module and choose Paste.

If it is not already visible, display the Project Explorer by pressing Ctrl+R. In the Project Explorer, you should be able to see the Module1 entry listed below your workbook. If you plan to have several macros, you can organize them into multiple modules. To rename a module from a generic name, follow these steps:

  1. Display the Properties window by pressing F4.
  2. Click Module1 in the Project Explorer.
  3. The Properties window shows only one property: (Name).
  4. Click in the text box for (Name) and type a new name, such ad ModuleReports.

As you add modules, you can name them to make it easier to locate particular macros later (Figure 122).

Figure 122. Organize your modules by changing their name in the Properties window.
Figure 122. Organize your modules by changing their name in the Properties window.

Tip: You may get to the point where you have many macros spread across many modules. You can quickly find a macro in the regular Excel interface by pressing Alt+F8 to bring up an alphabetical list of all the macros. Find the desired macro and click the Edit button. Excel switches to VBA, opens the correct module, and scrolls so this module is in view. Unfortunately, this trick does not work for macros in hidden workbooks, such as the Personal Macro Workbook.

Summary: You type regular VBA macros into standard modules in the VBA editor.

Title Photo: Sigmund on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.