Walkthrough: Returning a vector result

This walkthrough demonstrates how to use the CXlOper class to return a 1-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 doubles
2. From an STL vector object

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 VectorRes 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 VectorRes 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 an array of doubles

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

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 VectorRes.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 Count of type UShort (unsigned short integer), by typing into the arguments grid, as shown below. Note that the return type must be of type 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 PointerToArray(USHORT Count)
   {
       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 vector.

   Add the code below to set up a temporary 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 PointerToArray(USHORT Count)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       // Create and populate a 1-dimensional array of numbers
       double* ad = new double[Count];
       for (USHORT i = 0; i < Count; i++)
           ad[i] = (double)(i+1);
   
       // Set the value of xloResult
       xloResult.FromDoubleArray(ad, Count);
   
       // Clean up temporary array
       delete[] ad;
   
       // 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 ad. We can then safely delete ad, and return the contents of xloResult.

Returning a numeric array using STL vector

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

1. Open the source file VectorRes.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 Count of type UShort (unsigned short integer), by typing into the arguments grid, as shown below. Note that the return type must be of type 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 VectorToArray(USHORT Count)
   {
       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 vector.

   Add the code below to set up a temporary 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 VectorToArray(USHORT Count)
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       // Create and populate a vector
       std::vector<double> vec(Count);
       for (USHORT i = 0; i < Count; i++)
           vec[i] = (double)(i+1);
   
       // Set the value of xloResult
       xloResult = vec;
   
       // Return contents of xloResult
       return xloResult.Ret();
   }
   
   

   The important method here is the overloaded assignment operator of the CXlOper class. This populates xloResult with a copy of vec. We then return the contents of xloResult.

Testing the add-in

1. Build the project.
2. Start Excel, and use File - Open to open the built add-in file VectorRes.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 five rows and one column, and type in the formula =PointerToArray(5), as shown below.

   

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

   

See Also

Walkthroughs | CXlOper::FromDoubleArray method | CXlOper::operator =