VBA & Macro Problems

How to Call a VBA Function Using INDIRECT in Excel

Natalie Taylor Oct 13, 2026 869 views

Question details

The user needs a method to dynamically call and execute a specific VBA function from an Excel worksheet formula based on a text string, similar to how the INDIRECT function works.

How to Call a VBA Function from Excel Using INDIRECT

Product: Excel
Device & OS: not provided
Scenario: Attempting to execute VBA macros or custom functions dynamically from within worksheet formulas by referencing their names as text strings.
Observed behavior: Excel's native INDIRECT function evaluates text as a cell reference but cannot directly trigger or execute a VBA function by its name.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings so that the custom VBA code can execute properly.

Solution 1Recommended

Create a VBA Wrapper Function Using Application.Run

Since the built-in INDIRECT function cannot trigger VBA code, you can build a custom Master wrapper function that uses Application.Run to execute the desired procedure dynamically.

Excel's INDIRECT function is strictly designed for resolving cell references, not for executing VBA code. To achieve dynamic function execution based on a string name, you must use the Application.Run method in VBA.

By utilizing a ParamArray in your custom wrapper function, you can pass a variable number of arguments from the worksheet directly to the target VBA function you wish to execute.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a blank script window for your custom function.

Write the Wrapper Function

Define a new function (for example, MyMaster) that accepts the target function name as a String and a ParamArray for the arguments. Inside this function, use Application.Run(FunctionName, Arg1, Arg2) to execute the target code.

Use the Function in the Worksheet

Return to your Excel worksheet and type a formula like =MyMaster("MyFunc", "Hello World") to dynamically call the VBA function based on the provided string name.

Handling Variable Arguments: When dealing with variable arguments, your wrapper should count the number of supplied arguments in the ParamArray and pass them explicitly to Application.Run so they are mapped correctly.

Advanced VBA Support in WPS Spreadsheet

Run VBA Macros Smoothly with WPS Office

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to create wrapper functions and execute Application.Run just like in Microsoft Excel. It offers a familiar interface and high compatibility with complex Excel workbooks.

1. Download and Install WPS Office: Get WPS Office from the official website and complete the straightforward installation process.
2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the custom VBA wrapper code.
3. Access the Developer Tools: Navigate to the Developer tab on the ribbon to access the VBA Editor and adjust your macro security settings.
4. Run your Custom Functions: Execute your dynamic wrapper functions using Application.Run directly within the WPS worksheet formulas without any compatibility issues.

Fully compatible with Microsoft Excel .xlsm macro-enabled formatsIncludes a robust built-in VBA editor for writing custom functions and scriptsLightweight application that loads heavy macro-enabled workbooks quicklyFamiliar ribbon interface ensures zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the standard INDIRECT function to run a macro?

No, the standard INDIRECT function in Excel is strictly used for converting text strings into valid cell references or ranges. It cannot evaluate a string as a command to execute a macro or custom VBA function.

What is Application.Run in VBA?

Application.Run is a VBA method that executes a macro or calls a function dynamically using its name as a text string. It allows you to pass arguments to the called procedure at runtime, making it the perfect alternative to INDIRECT for VBA execution.

How do I handle a variable number of arguments in my wrapper function?

You can use a ParamArray parameter in your VBA function declaration. This allows the function to accept an arbitrary number of comma-separated arguments from the worksheet, which you can then iterate through or pass individually to Application.Run based on the array count.

Why does my worksheet formula return a #NAME? error when calling a custom function?

This error typically occurs if macros are disabled in your Trust Center settings, if the custom function is placed in a worksheet module instead of a standard module, or if the function name is misspelled in the formula itself.

Excel & Data Problems