This simple example shows a value list in action.

Use the Function Wizard to create a new function with these properties:

Name:	DaysInStandardYear
Return type:	CXlOper
Category:	Date & Time
Description:	Returns the number of days in a year, according to the specified day count convention

Add a single scalar argument with the following properties:

Type:	Unsigned short int
Name:	DayCount
Description:	Day count method

Select DayCount in the Argument grid and press Alt+Enter to see the Argument details. Switch to the "Value List" tab, and enter the values below:

Value	Description
0	30/360 US
1	Actual/Actual
2	Actual/360
3	Actual/365
4	30/360 European
5	30/360 Italian
6	30/360 German

Tip: You can save some time by copying the text below, and using the Paste button to insert all the rows into the value list at once.

0	30/360 US
1	Actual/Actual
2	Actual/360
3	Actual/365
4	30/360 European
5	30/360 Italian
6	30/360 German

Set the check-boxes above the value list grid as follows:

List is exclusive:	Checked
Show list in Formula Wizard:	Checked

Press OK twice to return to Visual Studio.

Have a look at some of the code that has been generated. If the code is not visible, expand the hidden region by clicking on the small plus sign next to the text DaysInStandardYear support code.

Constraint object

An object of type CXlValueListConstraint is declared at global scope. This is initialized with the values you entered in the Value List editor.

unsigned short xlvlc__DaysInStandardYear__DayCount__values[] = { 0, 1, 2, 3, 4, 
    5, 6 };
CXlValueListConstraint<unsigned short> xlvlc__DaysInStandardYear__DayCount(
    sizeof(xlvlc__DaysInStandardYear__DayCount__values)/sizeof(unsigned short), 
    xlvlc__DaysInStandardYear__DayCount__values);

Code is generated within the add-in function to use this constraint object to validate the value of the DayCount input:

CXlOper* DaysInStandardYear_Impl(CXlOper& xloResult, unsigned short int DayCount)
{
    // Validate and translate inputs 
    xlvlc__DaysInStandardYear__DayCount.Validate(DayCount, _T("DayCount")); 
    // End of generated code 
//}}XLP_SRC
    ...
}

If the Validate method fails to match input value against any of the values in the constraint, a exception is thrwon, and an error message is returned to Excel.

Drop-down list in Formula Wizard

The code below creates a drop-down list in the Excel Formula Wizard.

CXlWizExUIArgumentListCreator xweuialc__DaysInStandardYear__0(
    L"DaysInStandardYear", 0, L"0,30/360 US;1,Actual/Actual;2,Actual/360;"
    L"3,Actual/365;4,30/360 European;5,30/360 Italian;6,30/360 German", L',', L';');

See Extending the Excel Formula Wizard for more about the extended user interface.

Complete the code

Add code so that your add-in function looks like this:

CXlOper* DaysInStandardYear_Impl(CXlOper& xloResult, unsigned short int DayCount)
{
    // Validate and translate inputs
    xlvlc__DaysInStandardYear__DayCount.Validate(DayCount, _T("DayCount"));
    // End of generated code 
//}}XLP_SRC 
    static double adDays[] = { 360.0, 365.25, 360.0, 365.0, 360.0, 360.0, 360.0 };
    xloResult = adDays[DayCount];                                                 
    return xloResult.Ret();
}

Note that no further validation of DayCount is required before using it as an index into the array adDays. If the code has passed the Validate method of the constraint object, then we know that the value is safe to use.

Test the function

If you build and open the add-in, you will see that an error message is returned if an unacceptable value for DayCount is entered.

If you open the function in the Excel Formula Wizard, you will find a drop-down box in the DayCount field, which you can use to conveniently select a valid value.

Next: Editing value lists >>