how to handle variable # of parameters (C# paramArrayAttribute)

[Yinru Hou]

optional.dna does NOT cover what I want to achieve.

optional.dna handles optional parameters, not variable number of parameters

 

This is my UDF in VBA

 

InVBA, my udf is like this

 

Public Function MyUDF(

                           RelationList, ColumnList, _
                            execUnit, _
                            startDate, _
                            endDate, _
                            ParamArray OptionalParameter() As Variant) _
                      As String
    MyUDF  = "desc"
End Function

 

In C#, I have this

        [ExcelFunction(Description = "MyUDF Desc", Category = "MyAddIn2", IsMacroType = true)]
        public static string MyUDF(
            [ExcelArgument(AllowReference = true)]object rels,
            [ExcelArgument(AllowReference = true)]objectcols,
            [ExcelArgument(AllowReference = true)]objectexcc_unit,
            [ExcelArgument(AllowReference = true)]objectstartDate,
            [ExcelArgument(AllowReference = true)]string endDate, params string[] options) 

 

Excel_DNA failed to export MyUDF.

I can see AutoOpen is called, but after that, no MyAddIn2 category or MyUDF shows up in excel function list

If I remove "params string[] options" from function signature, then everything works fine.

Pls help to address the issue

 

thanks!

 

On Wed, May 18, 2011 at 5:33 PM, Govert van Drimmelen <gov...@icon.co.za> wrote:

Hi Yinru,

For handling optional parameters, see the new Optional.dna example
here: http://exceldna.codeplex.com/SourceControl/changeset/view/66649#1198221.


You will not be able to write to cells during a UDF call. This is
enforced by Excel itself. So I presume your web service is called on a
separate thread, and you are trying to write to Excel from this other
thread when the web call completes.

There can be few issues in this scenario.
You need to get an instance of the Application object on the right
thread, so you should call ExcelDnaUtil.Application from the new
thread before you start to do work.

Then it is possible that Excel is busy, for example the user is
editing a cell or pressing the mouse button. In that case any call to
the Excel object model will fail and cause an exception. Your code
needs to be ready for this.

The best approach I know of, is to call only
Application.Run("MyMacro") from the other thread, and do the real work
of updating in "MyMacro". You need the error checking when invoking
Application.Run, but in the macro itself you'll be on the main thread
of Excel again, and won't run into the funny exceptions.
An example of doing it this way this is the AsyncRunMacro function
ArrayResizer.dna example.

-Govert

On May 18, 11:57 pm, Yinru Hou <yinru...@gmail.com> wrote:
> Hi,
> in my UDF,  I call a web service to get data based on input parameters.
> After I get data back from web service, I plot it in Excel Cells (cells
> including caller cell & non-caller cells, in some case, I need update caller
> cell with proper format) with certain layout,
> attriRange.Value2 = headerArray throws an exception "Exception has been
> thrown by the target of an invocation", inner exception is "Exception from
> HRESULT: 0x800A03EC" where attriRange is of type
> Microsoft.Office.Interop.Excel.Range, 3 rows by 1 column and headerArray is
> 2-D array [3,1]
> This code used to work fine
>
> So I wonder excel_DNA locks out Excel cells during eveluation of a funciton?
>
> another question I have is
> My UDF will have required parameters plus optional parameters in form of
> key/value pair,  totally 10+ optional parameters.
> but users do not have to specify them in MyUDF. so they can call it
> like MyUDF(A1, B1, "optionalParam1=somevalue1",
> "optionalParam2=somevalue12") ,
> MyUDF(A1,B1), MyUDF(A1,B1, "optionalParam10=somevalue10",
> "optionalParam3=somevalue3", "optionalparam1=somevalue1"), etc.
> Wonder how to handle this using Excel_DNA & C#
>
> thanks

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Yinru,

Excel-DNA doesn't currently have built-in support for such param
arrays. You can simulate these by adding as many 'object' type
parameters as you need, and then processing these to check a value was
passed in or whether they are missing (of type ExcelMissing).

Note that string arrays (type string[]) are also not supported, but
object arrays are.

Regards,
Govert

[Yinru Hou]

thank you, Govert.  
I add 70 options in MyUDF object option1, .. object option70, now it can export to excel

However,  two issues

1. I click formula icon to bring up insert function screen, I select MyUDF from the list, then on function arguments screen I notice last parameter shown is O, the second last is option25,  pls see pitucre attached lastparameter.png

2. I did not enter anything on Function Arguments screen and simply click OK.  Then I click formula icon again, this time, on Function arguments screen, there is only one argument bar there, see SecondTimeClick.png

 

Help is my funcition

 

        //helpTop
        [ExcelFunction(Description = "MyUDF", Category = "My Test AddIn2", IsMacroType = true)]

        public static string MyUDF(
            [ExcelArgument(AllowReference = true)]object rels,
            [ExcelArgument(AllowReference = true)]object cols,
            [ExcelArgument(AllowReference = true)]object excc_unit,
            [ExcelArgument(AllowReference = true)]object startDate,

            [ExcelArgument(AllowReference = true)]object endDate,
            object option1, object option2, object option3, object option4,object option5,
                object option6, object option7, object option8, object option9, object option10,
            object option11, object option12, object option13, object option14, object option15,
                object option16, object option17, object option18, object option19, object option20,
            object option21, object option22, object option23, object option24, object option25,
                object option26, object option27, object option28, object option29, object option30,
            object option31, object option32, object option33, object option34, object option35,
                object option36, object option37, object option38, object option39, object option40,
            object option41, object option42, object option43, object option44, object option45,
                object option46, object option47, object option48, object option49, object option50,
            object option51, object option52, object option53, object option54, object option55,
                object option56, object option57, object option58, object option59, object option60,
            object option61, object option62, object option63, object option64, object option65,
                object option66, object option67, object option68, object option69, object option70
            )
        {
            if(cols is ExcelReference)
            {
                //XlCall.xlfCaller
                var formula = XlCall.Excel(XlCall.xlfGetCell, 6, cols);
            }
            return "testing...testing, experiement...";
        }
    }

[Govert van Drimmelen]

Hi Yinru,

1. Excel has a 255 character limit on the total length of the
parameter name string (including the ','s). So if you had shorter
parameter names, the wizard would display a few more. The display in
the function wizard does not affect how the function is calculated.
This issue was also discussed in this thread:
http://groups.google.com/group/exceldna/browse_thread/thread/83a6c34fbd2b151a.
Current versions of Excel-DNA enforce this limit, so this limit has
changed in Excel 2010 I would not have noticed yet.

2. I had not seen the behaviour you describe in the second issue
before, but I can recreated exactly what you see in my Excel 2007 too.
It also seems to happen only when the total parameter name string
would be longer than 255.

So there definitely is a trade-off between the total parameter name
length, and getting correct behaviour in the wizard.

I've only been trying with Excel 2007, so I'll try to test a bit with
Excel 2010 at some stage, and if these problems are there too I can
try to report them to the Excel team for fixing in some future
version.

There are other approaches you can take to passing a large number of
flags to a function. I noticed that the FinAnSu add-in (http://
code.google.com/p/finansu/) passes a configuration string to some
function, where the string contains whatever flags are needed, and
there are some utility functions to help build up the string. Another
approach is to make a single options argument that takes an array, and
you call the function as =MyUDF(first, params, {option1, option2,
option3}) or something.
I guess this can be a bit tricky to the user, but not much more tricky
than dealing with a large number of parameters to a function.

-Govert

>  SecondTimeClick.png
> 50KViewDownload
>
>  lastParameter.png
> 61KViewDownload

[Yinru Hou]

Thanks again, Govert.

I use shorter parameter names so I can pass 30 parameters in UDF which is enough for a little while.

But in the future we probably add more parameters, then this will be an issue.

 

Alternative ways you mentioned will not applicable to us b/c we want to maintain backward compatibilty.

since eariler version & current version of My AddIn pass key/value pair parameters and we want to keep that way.

 

So I wonder if you are going to address paramsArray in excel_DNA in future release, that way we can pass as many optional parameters as need.

 

thanks

[Govert van Drimmelen]

Hi Yinru,

Indeed I would like to add some support for param arrays in Excel-DNA
future.
However, this will not be able to address any of the problems you see
in the function wizard. At best I can automatically generate and
register the many-parameter function you currently type out yourself.
Excel does not support a way to register optional or variable numbers
of parameters through the .xll interface, and the function wizard
limitations you've seen are limitations and bugs in Excel.

Like with your current code, Excel-DNA could thus generate the 255
parameters which would work correctly in calculations from the sheet,
but might have some problems in the wizard.

If you have a Microsoft field rep, it might help if you raised these
issues with them, for improving future versions of Excel.

-Govert

On May 20, 12:45 am, Yinru Hou <yinru...@gmail.com> wrote:
> Thanks again, Govert.
> I use shorter parameter names so I can pass 30 parameters in UDF which is
> enough for a little while.
> But in the future we probably add more parameters, then this will be an
> issue.
>
> Alternative ways you mentioned will not applicable to us b/c we want to
> maintain backward compatibilty.
> since eariler version & current version of My AddIn pass key/value pair
> parameters and we want to keep that way.
>
> So I wonder if you are going to address paramsArray in excel_DNA in future
> release, that way we can pass as many optional parameters as need.
>
> thanks
>
> On Thu, May 19, 2011 at 4:00 PM, Govert van Drimmelen <gov...@icon.co.za>wrote:
>
>
>
>
>
>
>
> > Hi Yinru,
>
> > 1. Excel has a 255 character limit on the total length of the
> > parameter name string (including the ','s). So if you had shorter
> > parameter names, the wizard would display a few more. The display in
> > the function wizard does not affect how the function is calculated.
> > This issue was also discussed in this thread:
>

> >http://groups.google.com/group/exceldna/browse_thread/thread/83a6c34f...
> > .

> ...
>
> read more »