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

Question

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)

Answer 1

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:

• http://mikejuniperhill.blogspot.co.za/2014/03/interfacing-c-and-vba-with-exceldna-no.html

• http://mikejuniperhill.blogspot.co.za/2014/03/interfacing-c-and-vba-with-exceldna_16.html

Answer 2

You could use a “wrapper”

Function SumCalculation(val1 As Double, val2 As Double) as Variant
    SumCalculation = Application.Run("SumCalculation", 1, 1)
End Function

So in your code you can type

Result = SumCalculation(1, 1)