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.

Answer 1

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