CODEWITHSUNDEEP
c#excelexcel-formulaexcel-dna
glick
glick

Reputation: 102

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

Consider such ExcelDNA function definition:

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

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.

Upvotes: 2

Views: 794

Answers (2)

Govert
Govert

Reputation: 16907

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.

Upvotes: 2

C. Augusto Proiete
C. Augusto Proiete

Reputation: 27828

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

Upvotes: 7

Related Questions