What are macro functions?
When an add-in function is specified as a worksheet function, it can (as the name implies!) be called from a cell in a worksheet. To comply with the requirements of Excel's recalculation, a worksheet function is forbidden to change values in workbooks or to do a number of other actions such as finding the active cell or worksheet.
A function that acts on a workbook, rather than just returning a value, is called a macro function. It can use the Excel SDK API to manipulate workbooks in a variety of ways, just like a VBA macro. However, a macro written in C++ differs from one written in VBA in two significant ways:
- C++ macros run significantly faster than VBA macros, especially when setting cell values.
- VBA macros have access to a much richer programming model, via the Excel automation interface.
Calling a macro function
A macro function can be called in one of three ways:
- Through a GUI device, such as a menu or button. Macros called in this way cannot take any arguments.
- From VBA, using
Application.ExecuteExcel4Macro(MacroName)orApplication.Run(MacroName, Arg1, ...). - After being installed as an event-handler.
Note that a macro function cannot be called from a worksheet cell, unless it is also specified to be a worksheet function.
Specifying a macro function
In the Function Wizard, you can specify that a function be a worksheet function, a macro function, or a hidden function, using the Availability option group on the Features tab.
You can also specify the availability of a macro function in the Properties window, by setting the Availability property.
Finally, you can create a macro function in one step using the New Macro command, which is located on the toolbar of the XLL Add-ins window.