Walkthrough: Calling an Excel built-in function from your add-in

You can call built-in Excel functions from your add-in function. This walkthrough demonstrates how to call Excel functions and how to interpret the results of a built-in function.

Our add-in function will call built-in functions so that it returns the equivalent of:

="Copyright Megacorp Pty, " + ROMAN(YEAR(TODAY))

Creating the project

To create the project using Visual Studio 6

1. From the File menu, select New to show the New dialog.
2. Select the XLL+ AppWizard 4 project template from the list in the Projects tab, and enter BuiltinFn in the Project name box. Under Location, enter an appropriate directory in which to create the project.
3. Accept all the default settings in both pages of the XLL+ AppWizard.

To create the project using Visual Studio .NET or Visual Studio 2005

1. From the File menu, select New and then Project to open the New Project dialog.
2. Select the XLL+ Excel Add-in project template from the list of Visual C++ Projects, and enter BuiltinFn in the Name box. Under Location, enter an appropriate directory in which to create the project.
3. Accept all the default settings in the XLL+ .NET/2005 AppWizard.

For more details about creating projects, see Creating an add-in project in the XLL+ User Guide.

Adding the code

1. Add a new add-in function to BuiltinFn.cpp, as shown below.

   // Function:    CopyrightNotice
   // Purpose:     Return a copyright notice
   
   //{{XLP_SRC(CopyrightNotice)
       // NOTE - the FunctionWizard will add and remove mapping code here.
       //    DO NOT EDIT what you see in these blocks of generated code!
   IMPLEMENT_XLLFN2(CopyrightNotice, "R", "CopyrightNotice", "", "User Defined",
       "Return a copyright notice", "", "", 1)
   
   extern "C" __declspec( dllexport )
   LPXLOPER CopyrightNotice()
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       long ldtToday, lYear;
       CString strRoman;
   
       // Get the current date
       // Get the year of the current date
       // Convert year to roman numerals
       if (CXlFuncs::Today(ldtToday) == 0
        && CXlFuncs::Year(lYear, ldtToday) == 0
        && CXlFuncs::Roman(strRoman, lYear) == 0)
       {
           // Construct copyright notice
           xloResult.Format("Copyright Megacorp Pty, %s", (LPCSTR)strRoman);
       }
       else
       {
           // Return error
           xloResult = xlerrNA;
       }
       
       return xloResult.Ret();
   }

2. The return value of each CXlFuncs method is checked after each call. A non-zero result indicates an error, so the function returns #NA.
3. Sometimes the function you want to call will not be implemented by CXlFuncs or CXlMacros. In these cases you will need to use the technique shown for calling NORMDIST in the walkthrough: Grouping multiple arguments into a single Excel range.

Testing the add-in

To test the add-in

1. Build the project.
2. Start Excel, and use File - Open to open the built add-in file BuiltinFn.xll in the Debug sub-directory of your project directory.
3. In a spreadsheet cell, enter the following formula:

   =CopyrightNotice()

See Also

Walkthroughs | Excel built-in function numbers