Exposing ExcelDNA static functons to VBA

[Kevin Roche]

Hello,

I have a fairly large ExcelDNA project that successfully exposes a number of functions to Excel; however, I also have a requirement to expose those same functions for use within VBA code.

My Main project derives from IExcelAddIn and my UDFs are created as public static object XX(...) with the [ExcelFunction] attribute.

In attempting to expose these functions for use in VBA I realise that COM can't see static functions. I followed a method detailed on the "Tools for Finance" website here (http://mikejuniperhill.blogspot.co.uk/2014/03/interfacing-c-and-vba-with-exceldna_16.html), which allows me to "see" the functions within my VBA code, but which fails with a 429 error when I attempt to instantiate the COM-exposed library.

The details from the above mentioned website are quite old now, is there a newer method (or some completely different method) of achieving the desired outcome?

Regards.

[Govert van Drimmelen]

Hi Kevin,

The Excel-DNA worksheet functions can be called directly from VBA with Application.Run. That's the simplest if you just want to call the functions, and don't need a rich object model.

You can't export static methods through COM directly, so if you take this route you need some wrapper classes to give access to the static methods.

Most of the guidance from Mike's website is still good, though it doesn't use the NuGet packages to make the basic add-in project, which is a lot simpler.

If you're struggling to get the ComServer aspect to work with your add-in, there are some basic checks:
* ComServer flag in the .dna file.
* Class must be ComVisible (public, parameterless constructor, ComVisible attribute etc.)
* A call to DllRegisterServer or doing

-Govert

[Kevin Roche]

Thanks Govert,

The Application.Run route should be good enough - this is replacing an existing VBA/VB6 add-in so I think we'd need to "fix" any existing VBA usage scenarios anyway.

Thanks again

Kevin

[Kevin Roche]

Update

Played around a bit more with your other suggestion, in particular following your instructions here (https://github.com/Excel-DNA/Samples/tree/master/DnaComServer), and it works like a dream.

Thanks very much for this, it'll greatly minimise the requirements for changes to existing code.

Kevin

[anass.ham...@gmail.com]

Hi Govert,

Unfortunately in some use cases, Application.Run is very unsafe. As more and more functions are taking advantage of asynchronous calls, automatic resize for array formula. One could have array results from calls to Application.Run randomly show up on the worksheet and delete/override other worksheet functions or data. Do you have any other idea in mind of a safer way to call Excel-DNA xll functions in VBA? maybe generating a VBA module with properly defined Declare statement for these xll functions?

[Govert van Drimmelen]

Unsafe mechanisms like the array resizer are likely to cause trouble if things are called from VBA, no matter how the integration is done. Basically you need a version of your functions that are always safe to call via Application.Run, either by passing some flag that makes them synchronous and cautious, or by having a function with a different name.

Excel-DNA does support publishing classes from the add-in as COM object that can be used from VBA. But that's not automatically going to address the concern you have here.

More information and an example here: https://github.com/Excel-DNA/Samples/tree/master/DnaComServer

Can you say a bit about the barriers to moving more stuff out of VBA into a Visual Studio / .NET world?

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of anass.ham...@gmail.com [anass.ham...@gmail.com]
Sent: 26 July 2018 12:09 PM
To: Excel-DNA
Subject: [ExcelDna] Re: Exposing ExcelDNA static functons to VBA

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/eMf9d7gTgV4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[anass.ham...@gmail.com]

This is the situation basically:

 - We buy data from a third party data provider, who also provides us with an Excel Addin to expose functions to help retrieve data. These functions are ok, to visualize or do simple tasks but for heavier data processing, we need to use them in an API fashion. unfortunately on end user computers like us, the only "development environment" available is VBE/VBA. I think it is not a unique case, as Excel-DNA is used frequently by data vendors in financial industry to provide this kind of functionality.

To unsubscribe from this group and all its topics, send an email to exceldna+...@googlegroups.com.
To post to this group, send email to exc...@googlegroups.com.