Walkthrough: Creating and populating a worksheet from your add-in

This walkthrough demonstrates how to create a new worksheet from your add-in function, and how to populate its cells.

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 CreateSheet in the Project name box. Under Location, enter an appropriate directory in which to create the project.
3. On the first page of the XLL+ AppWizard, set the display name of the add-in to be CreateSheet demo (instead of "New Xll").
4. For all the other settings in both pages of the XLL+ AppWizard, accept the default.

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 CreateSheet in the Name box. Under Location, enter an appropriate directory in which to create the project.
3. In the XLL+ .NET/2005 AppWizard, set the XLL friendly name to CreateSheet demo (instead of "CreateSheet").
4. For all the other settings in in the XLL+ .NET/2005 AppWizard, accept the default.

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

Adding code

Creating the add-in function

1. In your application's main source file, CreateSheet.cpp, Create a new add-in function , using the code below.

   #include <xlfuncs.h>
   
   // Function:    OpenSheet
   // Purpose:     No description provided
   
   //{{XLP_SRC(OpenSheet)
       // NOTE - the FunctionWizard will add and remove mapping code here.
       //    DO NOT EDIT what you see in these blocks of generated code!
   IMPLEMENT_XLLFN2(OpenSheet, "R", "OpenSheet", "", "User Defined",
       "No description provided", "", "", 2)
   
   extern "C" __declspec( dllexport )
   LPXLOPER OpenSheet()
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       if (CXlMacros::WorkbookInsert() == 0)
       {
           CXlOper xloValue, xloRef;
           USHORT i;
   
           // Create an array of values and populate it
           xloValue.AllocArray(6, 2);
           for (i = 0; i < 6; i++)
           {
               xloValue.Cell(i, 0) = "Text";
               xloValue.Cell(i, 1) = (double)i;
           }
   
           // Create a reference to a cell range and set its value
           xloRef.MakeRef("A1:B6");
           xloRef.SetValue(xloValue);
       }
   
       return xloResult.Ret();
   }

2. After you have inserted the code, use the XLL+ Function Wizard to inspect the function's signature. Note that the Macro function check box is checked, and the Worksheet function check box is cleared. This indicates that the function is available as a macro function - i.e. from menus or buttons etc - but not from a worksheet.
3. For more information on calling Excel methods, see the documentation for the CXlMacros and CXlFuncs classes. See also the CallExcel sample.
4. Build the project.

Testing the add-in

To test the add-in

1. Start Excel, and use File - Open to open the built add-in file CreateSheet.xll in the Debug sub-directory of your project directory.
2. Use the Tools - Macro - Macros... menu option (or Alt+F8) to display the Macro dialog.
3. Type the name of the add-in function, OpenSheet, into the Macro name field, and click Run.
4. A new sheet is created in the active workbook, and its top left cells are populated with a mixed array of data.