Can ParamArray be used in Function declarations?

Dec 14, 2012 at 12:13 AM
Edited Dec 14, 2012 at 2:31 AM

Given the following code

<DnaLibrary>
<![CDATA[
Public Module Strings
	Function MySubst( ByVal sPattern As String, ByVal ParamArray sArgs() As Object) As String
		dim i as integer
		for i = LBound(sArgs()) to UBound(sArgs())
			sPattern = Replace( sPattern, "[" & CStr(i+1) & "]", sArgs(i))
		Next
		MySubst = sPattern
	End Function
End Module
]]>
</DnaLibrary>

why do I get the following error?

There were errors when compiling project: 
C:\Users\BruceAxtens\AppData\Local\Temp\kt--lech.0.vb(4,0) : error BC30105: Number of indices is less than the number of dimensions of the indexed array.
C:\Users\BruceAxtens\AppData\Local\Temp\kt--lech.0.vb(4,0) : error BC30105: Number of indices is less than the number of dimensions of the indexed array.

Kind regards,

Bruce/bugmagnet

P.S. I can convince it to compile by changing the second parameter to 

ByVal ParamArray sArgs As Object()

However that doesn't make it "work".

Coordinator
Dec 14, 2012 at 7:44 AM

Hi Bruce,

Excel-DNA does not export functions with ParamArray parameters to Excel. The compile error you see is because the LBound(sArgs()) should probably be LBound(sArgs) without the extra brackets.

If your syntax is correct, your code should compile, but the function should not be visible in Excel.

For the 'variable' number of parameters, all you can do is to make the maximum number of parameters explicitly, and then check them for ExcelMissing.Value or not. 

-Govert

Dec 15, 2012 at 10:18 AM

G'day Govert

When you say that ParamArray isn't supported, does that also extend to the C# equivalent? 

public static double SSum(params double[] list)

And I take it that the undocumented stuff like 

      public static double SSum2(__arglist)
  

won't work either?

 

Bruce/bugmagnet

Coordinator
Dec 15, 2012 at 11:42 AM
Edited Dec 15, 2012 at 11:53 AM

Hi Bruce,

Yes - Excel-DNA currently has no special handling of the variable parameter arrays, or of optional parameters.

The Excel C API itself (which Excel-DNA uses) has no built-in way to support variable numbers of parameters, so Excel-DNA is not restricting the functions that could be registered by your add-in with Excel. By declaring the maximum number of parameters (255 under Excel 2007+), all of type 'object', and dealing with each parameter value, the resulting function would be as flexible as Excel allows. A future version of Excel-DNA could generate such a wrapper automatically around your function, and thus support ParamArray in your code that way, but it would not give the add-in user in Excel anything they can't get from your functions right now.

The only current workaround (apart from a custom Excel-DNA version) would be to generate your own wrapper methods and register them with a call to ExcelIntegration.RegisterMethods(...).

I'd suggest you just make a helper function that collected the parameters that are not of type ExcelMissing into an array, and returns that. Then your function could be something like:

public static double MySum(object val1, object val2. object val3, object val4. object val5, object val6, object val7, object val8)
{
    object[] paramValues = CollectValues(val1, val2, val3, val4, val5, val6, val7, val8);

    // process further here ...
}


private static object[] CollectValues(params object[] values)
{
    var nonMissing = new List<object>();
    foreach (var value in values)
    {
        if (!(value is ExcelMissing))
        {
            nonMissing.Add(value);
        }
    }
    return nonMissing.ToArray();
}

-Govert

Aug 20, 2013 at 1:52 PM
I also have UDFs that require a variable number of arguments, just like Excel's built-in SUM() function.

However, the performance overhead is huge with 20+ optional parameters. My workaround is joining multiple parameters into a string, and splitting that within the function.

public static object MyFunc(string args, string delimiter)

I found that calculations are many times faster when using a single parameter compared to many optional parameters or using function overloading. This workaround is not user-friendly. The user needs to write ugly formulas like MyFunc(A2&"|"&B1&"|"&B2, "|") Do you know if other providers such as Add-In Express have a more elegant solution?
Coordinator
Aug 20, 2013 at 2:24 PM
Hi Christso,

Excel offers no support for UDFs to have variable numbers of parameters, other than registering as 'Object' and checking whether they are Missing. Other tools like Add-In Express that also use the C API would be subject to the same limitations of the Excel API as Excel-DNA.

It would be possible to automatically generate a wrapper function with the optional parameters, but that would not address your performance concern.
Could you quantify the 'huge' overhead you see? E.g. How slow is a function with 100 'Object' parameters when you pass only the first five, vs. a function that has five parameters?

-Govert
Aug 21, 2013 at 1:02 PM
Thanks for the explanation. I have tested 3 different ways for implementing a UDF with a variable number of parameters. The UDF is calculated on 31,077 cells, and 5 parameters were passed to the UDFs. The results are as follows:

UDF with 30 parameters: 3.2 seconds
UDF with 5 parameters: 0.8 seconds
UDF with 2 parameters emulating 5 parameters with string.Split(): 0.4 seconds

I have attached the project used for this test. The spreadsheets are under the test folder. The project needs to be compiled by Visual Studio.
ExcelDnaManyParams.zip

I use the UDF to synchronise values between a spreadsheet and a relational database. The synchronisation is triggered by the user by clicking on a menu item.
Coordinator
Aug 21, 2013 at 2:06 PM
That's a great start - thanks.

Are you able to also test with IsMacroType=false and AllowReference=false. That might make a difference.

Maybe I can find to way to optimize that particular path through the marshaller. I've had in mind some changes to the marshaling for a while, so thins might be the kick I need...

Still - even in the worst case you are calculating 10,000 cells per second. Were the UDF to do some significant work, the 0.1 ms per call overhead should not dominate.

-Govert
Coordinator
Aug 21, 2013 at 9:00 PM
OK - removing the AllowReference flags makes the recalculation about 20% faster, but the per-parameter overhead is still large.

-Govert
Aug 23, 2013 at 6:23 AM
Thanks for the explanation. The overhead does dominate because the UDF simply sends SQL update and insert statements to a database, or insert a row in a worksheet. See video demonstration: CellRow

I have considered uploading a cell without using CalculateFull, however, the database would not know if a cell was deleted or replaced in a spreadsheet unless I implement some complex event handling.