Using xll functions in VBA macro

Dec 9, 2010 at 6:53 PM

Hi,

I've inherited some old functions in excel, which I am trying to bring up to date.  I've very successfully ported them to .net and packaged them up with excel-dna, which has worked brilliantly - so straightforward!

However, I've discovered these functions are used in quite a lot of macros, which will be painful to locate and then change to use "application.run" to call the new functions.  I've read that I can call into a dll directly from VBA using by including the line - "Declare Function <functionName> Lib <NameOfXLLFile> (signature of function) <output type>.

However I have not found much about it and also have not got it to work successfully yet.

Have you ever tried this method, and if so, what's the trick to getting it to work!

Thanks for a brilliant library,

Miles

Coordinator
Dec 9, 2010 at 8:54 PM

Hi Miles,

The direct access from VB through Declare won't work for Excel-Dna add-ins. There's quite a lot of smoke-and-mirrors behind the scenes to make the .xll export the functions from the .NET code, so the functions you want to get to are not available from the .xll unless it has been loaded and initialized by Excel as an Excel add-in.

I would suggest that you migrate the macros to .NET too. Using the Automation interfaces from VB.NET works well; you might only have to make some small changes in the port, for example you get hold of the root Application object using ExcelDnaUtil.Application. There is a bit of an issue with the different versions of the Interop libraries to reference from the VB.NET project - basically you should either call the Automation API late-bound or use the version of the interop library that matches the oldest version of Excel you want to support.

But I also understand that you might not be ready to move everything to .NET under Excel-Dna yet. In that case, I you should make a helper module in your VBA that wraps all the Application.Run calls. This way you keep the existing macros as they are and don't pollute your macro code with lots of Application.Run calls, rather having a single place where all the ugliness is contained. If you move everything to VB.NET later, you just don't take along the run helper.

Hope this makes sense,

Govert