How to Nest Function Macros

Last reviewed: November 2, 1994
Article 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

KBCategory: kbusage
KBSubcategory:
Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00
4.0 4.00 4.0a 4.00a 5.0 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.