Optional worksheet parameters and default values

Your custom worksheet functions can take optional parameters. The users of your worksheet can then decide whether to supply them or leave them blank. Optional parameters can be indicated in one of two ways, as with parameter help strings that were explained in a previous article. If an optional parameter is not supplied to the worksheet function then you get passed a zero value for all numeric types and an empty string for string types. Sometimes you may wish to provide your own default value for a missing optional parameter. In that case you can use the [DefaultValue] attribute or the DefaultValue parameter to the [ParameterDetails] attribute. See below: [WorksheetFunction] private int TakesDefaultArray( int a, [DefaultValue(new int[] { 1, 2, 3, 4 })] int[] b) { int result = 0; foreach (int i in b) { result += (a * i); } return result; } [WorksheetFunction] [ParameterDetails(Name = "b", DefaultValue = new int[] { 1, 2, 3, 4 })] private int TakesDefaultArray2( int a, int[] b) { int result = 0; foreach (int i in b) { result += (a * i); } return result; } Finally, you may prefer to indicate to your users which parameters are optional and which are not, you can do this by setting the OptionalParameterEmbelishment parameter of the [AddinClass] attribute to UseSquareBrackets which causes all optional parameters to be displayed in the function wizard within square brackets. You can download the source code for this example code from here. You will need a copy of JetXLL to be able to build and run the code.