Walkthrough: Returning a matrix result

This walkthrough demonstrates how to use the CXlOper class to return a 2-dimensional array (vector) from an add-in function.

Two such functions are created, which construct the array result in two different ways:

1. From an array of pointers to double
2. "By hand", to create an array of mixed type

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 MatrixRes 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 MatrixRes 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+ AppWizard.

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

Returning a numeric array using pointers

Use the XLL+ Function Wizard to create a new add-in function and to add a pair of unsigned short integer arguments.

Note: If you do not know how to start the Function Wizard, or you cannot find the tool-bar, look at Installing the Function Wizard under Developer Studio 6 or Installing the Function Wizard under Visual Studio .NET or Visual Studio 2005.

1. Open the source file MatrixRes.cpp and click on the New XLL+ Function menu item or tool-button, to show the New Function dialog, and fill in the name, category and description as shown below.

   

2. Add new arguments Rows and Cols of type UShort (unsigned short integer), by typing into the arguments grid, as shown below. Note that the return type must be CXlOper.

   

   Click on the OK button to close the Function Wizard and save the function.

3. The following function has been added to MatrixRes.cpp.

   extern "C" __declspec( dllexport )
   LPXLOPER NumericArray(USHORT Rows, USHORT Cols)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       // TODO - Set the value of xloResult
       return xloResult.Ret();
   }
   
   

4. Our task is going to be to populate xloResult with a numeric array.

   Add the code below to set up a two-dimensional array of numbers. In the real world this would be a more useful data set, probably returned by a previously written function.

   extern "C" __declspec( dllexport )
   LPXLOPER NumericArray(USHORT Rows, USHORT Cols)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       if (Rows == 0 || Cols == 0) 
       {
           xloResult = xlerrNum;
           return xloResult.Ret();
       }
       
       // Create and populate a 2-dimensional array of numbers
       double** apd = new double*[Rows];
       for (USHORT i = 0; i < Rows; i++) {
           apd[i] = new double[Cols];
           for (USHORT j = 0; j < Cols; j++) {
               apd[i][j] = (double)((i*100) + j);
           }
       }
   
       // Set the value of xloResult
       xloResult.FromDoubleArray(apd, Rows, Cols, TRUE);
   
       // Clean up temporary array
       for (i = 0; i < Rows; i++)
           delete[] apd[i];
       delete[] apd;
   
       // Return contents of xloResult
       return xloResult.Ret();
   }
   
   

   The important function here is of course FromDoubleArray(). This populates xloResult with a copy of the numeric array apd. The fourth argument to FromDoubleArray(), BOOL bByRows, is set to TRUE, so that the array is laid out with rows representing the continuous data held in each pointer member of apd[]. To transpose the array, set the argument to FALSE. We can then safely delete apd, and return the contents of xloResult.

Returning a mixed array

Use the XLL+ Function Wizard to create a new add-in function and to add an unsigned short integer arguments.

1. Activate the source file MatrixRes.cpp and click on the New XLL+ Function menu item or tool-button, to show the New Function dialog, and fill in the name, category and description as shown below.

   

2. Add a new argument Rows of type UShort (unsigned short integer), by typing into the arguments grid, as shown below. Note that the return type must be CXlOper.

   

   Click on the OK button to close the Function Wizard and save the function.

3. The following function has been added to MatrixRes.cpp.

   extern "C" __declspec( dllexport )
   LPXLOPER MixedArray(USHORT Rows)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       // TODO - Set the value of xloResult
       return xloResult.Ret();
   }
   
   

4. Our task is going to be to populate xloResult with a mixed array.

   Add the code below to set up a two-dimensional array of cells. Each row of cells contains a number and a string.

   extern "C" __declspec( dllexport )
   LPXLOPER MixedArray(USHORT Rows)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       if (Rows == 0) 
       {
           xloResult = xlerrNum;
           return xloResult.Ret();
       }
       
       xloResult.AllocArray(Rows, 2);
       for (USHORT i = 0; i < Rows; i++) 
       {
           xloResult.Cell(i, 0) = (double)i;
           xloResult.Cell(i, 1) = "A string";
       }
       return xloResult.Ret();
   }
   
   

   First we test that Rows is non-zero, and fail if it is not.

   Then we use AllocArray() to turn xloResult into an array with Rows rows and 2 columns.

   Next, we populate each row of xloResult, using the Cell() method to access each cell.

   Finally we return the contents of xloResult to Excel, as usual.

Testing the add-in

1. Build the project.
2. Start Excel, and use File - Open to open the built add-in file MatrixRes.xll in the Debug sub-directory of your project directory. If you are using XLL+ for VS.NET or XLL+ for VS 2005, then you will not need to open the add-in; the debug settings have been set to open it for you automatically.

3. In a worksheet, select a range containing two rows and three columns, and type in the formula =NumericArray(2,3), as shown below.

   

   Then press the key combination Shift+Ctrl+Enter to enter the formula as an array formula.

See Also

Walkthroughs | CXlOper::FromDoubleArray method | CXlOper::AllocArray method | CXlOper::Cell method