How to Nest Function Macros

ID: Q64094

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

SUMMARY

Function macros can be nested by either specifically stating the name of the submacro in the dependent function macro, or by dynamically passing the name of a function macro to the dependent macro as a parameter. (Note: this is true for version 5.0 macro sheets, but not modules.)

MORE INFORMATION

A function macro can call a second function macro by specifically calling the second function macro. This would cause the main function macro to execute, then call the second function macro. When the second function macro is finished, it will return the calculated value to the location where it was called.

For example:

Spreadsheet Call to the Function Macro "Function"


   =MACRO1.XLM!MAIN(1,2)

Function Macros on MACRO1.XLM


   A1:  Main                 B1:  Add
   A2:  =ARGUMENT("x",1)     B2:  =ARGUMENT("i")
   A3:  =ARGUMENT("y",1)     B3:  =ARGUMENT("j")
   A4:  =add(x,y)            B4:  =i+j
   A5:  =RETURN(A4)          B5:  =RETURN(B4)

A function macro can also call a second function macro dynamically by passing the name of the submacro to the main function macro. This is a more advanced feature of function macros. You can define your own special macro to be called through the main function macro. The name of the submacro is stored in a variable on the macro sheet. This variable can be used to call the actual submacro, which then follows the same method as stated above.

For example:

Spreadsheet Call to the Function Macro "Function"


   =MACRO1.XLM!MAIN(MACRO1.XLM!ADD,1,2)

Function Macros on MACRO1.XLM


   A1:  Main                    B1:  Add
   A2:  =ARGUMENT("myfunc",8)   B2:  =ARGUMENT("i")
   A3:  =ARGUMENT("x",1)        B3:  =ARGUMENT("j")
   A4:  =ARGUMENT("y",1)        B4:  =i+j
   A5:  =myfunc(x,y)            B5:  =RETURN(B4)
   A6:  =RETURN(A5)

NOTE: Both the macros, "Main" and "Add" must be defined as function macros by choosing Define Name from the Formula menu.

For more information on function macros, see Pages 176-182 of the "Microsoft Excel for Windows Functions and Macros" manual and the "Microsoft Excel for OS/2 Functions and Macros" manual.

REFERENCES

"Functions and Macros," versions 2.x, pages 176-182 "User's Guide," version 3.0, pages 567-576

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 4.0a 4.00a 5.0

Keywords :
Version :
Platform :
Issue type :