Search code examples
excelvb.netvbaexcel-dna

Using Excel-DNA created functions on VBA without Application.run

I'm creating an Addin for Excel using VB.NET and the Excel-DNA Library. Functions created on my Addin project works perfectly when called as formulas on Worksheets. But I would also like them to be available when writing VBA code.

So, let's say I have a function SumCalculation created on Excel-DNA as below:

<ExcelFunction(Category:="Calculations", Description:="Sum values", Name:="")>
Public Function SumCalculation(<ExcelArgument("First Value to sum")> Value1 As Double,
                               <ExcelArgument("Second Value to sum")> Value2 As Double) As Double
    Return Value1 + Value2
End Function

When the XLL is generated and loaded on Excel, it can be called on VBA like this:

Result = Application.Run("SumCalculation", 1, 1)

But is there some way to call it directly as below?

Result = SumCalculation(1, 1)
Solution

  • Excel-DNA allows the .xll to serve as a COM server that you can reference and use from VBA. This won't allow you to directly call the function, but you can make an object that can be instantiated in a VBA routine, and your calculations can be methods on this object. This allows you to:

    • expose a rich object model to program against in VBA,
    • where the COM objects share the AppDomain (memory space) of the rest of the add-in , so caches etc. can be shared, and
    • registration is implemented in a way that does not require admin rights.

    Here is a step-by-step example of the COM server feature: https://github.com/Excel-DNA/Samples/tree/master/DnaComServer

    More details (though not using the simplre NuGet package for building the add-in) can also be found here: