How to Make a VBA Macro Appear in Excel's Macro List
Question details
The user wants to run a copied VBA procedure, but it does not show up in the Macros dialog box.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user has copied a VBA procedure into a new workbook and is trying to execute it via the Macros menu.
- Observed behavior
- The procedure is missing from the Macros dialog box, likely because it was declared as a Function or placed in a worksheet/ThisWorkbook module instead of a standard module.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so your code can execute properly.
Move the Code to a Standard Module as a Public Sub
Excel only displays Public Sub procedures without arguments located in a standard module within the Macros dialog box.
Code placed in a worksheet module (e.g., Sheet1) or the 'ThisWorkbook' module is considered private to those specific workbook objects and will not appear in the general macro list.
Furthermore, procedures declared as 'Function' instead of 'Sub', or procedures that require parameter arguments, are hidden from the Macros dialog box.
Press ALT + F11 on your keyboard to open the Visual Basic Editor (VBE) in Excel.
In the Project Explorer pane on the left side, right-click your workbook's name. Select Insert, and then click Module to create a new standard module.
Double-click the newly created module (typically named Module1) to open its code editing window.
Paste your VBA code into the window. Ensure the first line begins with 'Public Sub YourMacroName()' and does not contain any parameters inside the parentheses.
Run and Manage VBA Macros with WPS Spreadsheet
WPS Office provides robust support for macros and VBA in its advanced versions, allowing you to automate repetitive tasks seamlessly. You can write, edit, and manage standard modules using an interface highly familiar to Excel users.
- 1. Open Your Macro-enabled File: Launch WPS Spreadsheet and open your existing .xlsm file.
- 2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon.
- 3. Open the VBA Editor: Click on 'Visual Basic' to open the built-in VBA editor.
- 4. Manage Your Modules: Insert a standard module and use 'Public Sub' declarations to ensure your macros appear in the Macros list when you press ALT + F8.
Frequently Asked Questions
Why doesn't a VBA 'Function' appear in the Macro list?
A 'Function' is designed to calculate and return a value, typically used directly within spreadsheet cells (like a custom formula). Because it cannot be executed standalone like an action script, Excel hides it from the Macros dialog box. Only 'Sub' procedures appear there.
Can I run a macro from the list if it requires arguments?
Macros that require arguments (e.g., 'Sub ProcessData(row As Integer)') will not appear in the Macros list because Excel cannot provide the required values when you click 'Run'. To fix this, you must create a wrapper macro without arguments to call your parameterized macro.
How do I trigger a macro stored in a worksheet module?
While worksheet macros do not appear in the standard ALT + F8 list, you can still trigger them using worksheet events (such as changing a cell's value or double-clicking) or by calling them from an active ActiveX control button placed on that specific sheet.
