How to pass discontinuous range of cells from Excel to ExcelDNA function

Question

Consider such ExcelDNA function definition:

[ExcelFunction(Name = "Fnc1", Description = "Fnc1")]
public static object Fnc1(
    [ExcelArgument(Name = "Arg1", Description = "Arg1", AllowReference = true)]
    object rng)
{
    // ...
}

It works fine when called with a single cell like this =Fnc1(A1) or with continuous range of cells like this =Fnc1(A1:A3).
But it doesn't work when called with discontinuous range of cells e.g. =Fnc1(A1,A5,A10). The error #VALUE! is returned.

Is there a way how to call ExcelDNA function with discontinuous range of unknown amount of cells?

I have tryied to declare the paramter like this params object[] rng but no luck as well.

C. Augusto Proiete · Accepted Answer · 2020-02-05T22:32:36.997

In order to have an Excel-DNA function that allows passing in an unknown number of arguments at run-time, you need to use params object[] in your function arguments.

public static class MyFunctions
{
    [ExcelFunction]
    public static object Hello(params object[] values)
    {
        return "Hello " + DateTime.Now;
    }
}

Then it doesn't matter if you call it with hard-coded values e.g. =Hello(10, 20) or if you use cell references e.g. =Hello(A1,A5,A10).

However, variable number of arguments is not supported out-of-the-box by Excel-DNA, and as such you'll have to use the ExcelDna.Registration helper library in order to register your functions.

Install the ExcelDna.Registration NuGet package, then inside of your .dna file, mark your add-in assembly reference to use ExplicitRegistration e.g.:

<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="My Add-In" (...)>
  <ExternalLibrary Path="MyAddIn.dll" ExplicitRegistration="true" (...) />
</DnaLibrary>

Then, in your AutoOpen, you register the functions with a ProcessParamsRegistrations call... e.g.

public class AddIn : IExcelAddIn
{
    public void AutoOpen()
    {
        ExcelRegistration
            .GetExcelFunctions()
            .ProcessParamsRegistrations()
            .RegisterFunctions();

        // ...
    }

    public void AutoClose()
    {
        // ...
    }
}

Implicit vs Explicit Registration of functions

By default, Excel-DNA searches for every public static method in your assembly and registers them as functions with Excel. That's the implicit registration process.

ExplicitRegistration="true" turns off the implicit registration and thus nothing gets registered automatically - you have to do it yourself - which is what I'm doing in the AutoOpen above with the ... RegisterFunctions() call. If you don't turn off the implicit registration, then functions end-up being registered twice (once by the implicit process, then again by your code) and you get error messages

Yes that worked, thank you! What is the difference between implicit and explicit registration? What else is implicit registration doing behind the scene? — glick, Feb 05 '20 at 20:56
By default, Excel-DNA searches for every `public static` method in your assembly and registers them as functions with Excel. That's the _implicit_ registration process. `ExplicitRegistration="true"` turns off the _implicit_ registration and thus nothing will get registered automatically - you have to do it yourself - which is what I'm doing in the `AutoOpen` above with the `...RegisterFunctions()` call. If you don't turn off the _implicit_ registration, then functions end-up being registered twice (once by the _implicit_ process, then again by your code) and you get error messages. — C. Augusto Proiete, Feb 05 '20 at 21:20
I see, thanks a lot! Could you add your comment to the answer please? This seems to be very useful to me! — glick, Feb 05 '20 at 21:24
@barpa Added "Implicit vs Explicit Registration of functions" to the answer — C. Augusto Proiete, Feb 05 '20 at 22:39

score 2 · Answer 2 · answered Feb 07 '20 at 10:44

2

The others answers are useful if you'd like to allow multiple parameters, and perhaps easiest for an end user to use. But you could also pass the discontinuous ranges directly into the single AllowReference=true parameter you start with, by adding parentheses in the formula:

=Fnc1((A1,A5,A10:A12))

The single ExcelReference you get will have multiple InnerReferences for the disjoint parts.

The parentheses disambiguate between the use of the comma as a range union operator and as the parameter separator in a function call.

answered Feb 07 '20 at 10:44

Govert

16,387
4
60
70

Thanks for your answer! Yes I found and tested the solution with parenthesis already. This worked but nobody will probably know why discontinued cells/ranges do not work and nobody will use the parenthesis. So the solution with `params object[]` is preferred in this case for me. – glick Feb 07 '20 at 12:22
@glick How did you designate the that `params object[]` elements were references? – Terry Sep 13 '21 at 16:33

How to pass discontinuous range of cells from Excel to ExcelDNA function

2 Answers2

Linked