Search code examples
c#exceladd-inuser-defined-functionsexcel-dna

How to reference a range of cells in UDF

c#, ExcelDNA, in my UDF, I want to pass a range of cells, MyUDF(A1:A2)

I tried MyUDF(object param1), if I type in myUDF(A1:A2), the MyUDF is never called I also tried MyUDF(string param1), MyUDF(string[] param1) neither works.

What type of the parameter should be? thanks

Solution

  • The format public static object MyUDF(object param1) {...} should work fine, and your formula with =MyUDF(A1:A2) will pass in an object array with the values in those cells.

    If you want to allow the argument to be a reference to the passed in range, you'd declare it as

    public static object MyUDF(
    [ExcelArgument(AllowReference=true)] object param1) {...}

    The extra attribute tells Excel to pass in the actual reference when called as =MyUDF(A1:A2) - the reference will be of type ExcelDna.Integration.ExcelReference, from where you can get the values or make further calls to the Excel API to get the address or anything.