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:
A macro function can be called in one of three ways:
Application.Run(MacroName, Arg1, ...).
Note that a macro function cannot be called from a worksheet cell, unless it is also specified to be a worksheet 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.