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 STL vector object
2. From an array of doubles

You can find a copy of all the code used in this walkthrough in the Walkthroughs/VectorRes folder in your XLL+ installation, along with a demonstration spreadsheet, VectorRes.xls.

Creating the project

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+ 6 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 information about creating projects, see Creating an add-in project in the XLL+ User Guide.

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.

Note: If you do not know how to start the Function Wizard, or you cannot find the command to open it, look at Invoke the Function Wizard in the User Guide.

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 as shown below.

   

   Name	VectorToArray
   Return type	CXlOper
   Category	User Defined
   Description	Returns a numeric array using an STL vector

2. Fill in the function's category and description. Note that the return type must be CXlOper

   Add a new argument Count of type Unsigned Short Int by typing into the arguments grid, as shown below. .

   Type	Name	Description
   Unsigned Short Int	Count	Number of items in result

   

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

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

   CopyC++

   CXlOper* VectorToArray_Impl(CXlOper& xloResult, unsigned short int Count)
   {
       // End of generated code 
   //}}XLP_SRC 
       // TODO - set the value of xloResult, or return another value 
       //          using CXlOper::RetXXX() or throw a CXlRuntimeException. 
       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.)

   CopyC++

   CXlOper* VectorToArray_Impl(CXlOper& xloResult, unsigned short int Count)
   {
       // End of generated code 
   //}}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.

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.

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 as shown below.

   

   Name	PointerToArray
   Return type	CXlOper
   Category	User Defined
   Description	Returns a numeric array using a C array of doubles

2. Fill in the function's category and description.

   Add a new argument Count of type Unsigned Short Int by typing into the arguments grid, as shown below.

   Type	Name	Description
   Unsigned Short Int	Count	Number of items in result

   

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

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

   CopyC++

   CXlOper* PointerToArray_Impl(CXlOper& xloResult, unsigned short int Count)
   {
       // End of generated code 
   //}}XLP_SRC 
       // TODO - set the value of xloResult, or return another value 
       //          using CXlOper::RetXXX() or throw a CXlRuntimeException. 
       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.

   CopyC++

   CXlOper* PointerToArray_Impl(CXlOper& xloResult, unsigned short int Count)
   {
       // End of generated code 
   //}}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 FromDoubleArray(). This populates xloResult with a copy of the numeric array ad. We can then safely delete ad, and return the contents of xloResult.

Testing the add-in

1. Build the project, using the Build/Build Solution menu or Shift+Ctrl+B keyboard short-cut.
2. When the project has been built successfully, use the Debug/Start Debugging menu or F5 keyboard short-cut to run your add-in under Excel.
3. If Excel puts up any message boxes asking whether you trust the add-in, say yes.

4. 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

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