calling an external DLL from my add-in XLL

Nov 13, 2013 at 3:34 AM
First, what a great tool. I successfully created an add-in!

Now I received a DLL from another developer, with some exposed api, that I can successfully call from VB code in Excel like this:
Private Declare Function license_initialize Lib "C:\my path\license_tools.dll" Alias "_license_initialize@0" () As Long

But I can't figure out how to call into the same DLL from inside my add-in XLL. The function is supposed to return 0 or 1, but when calling it from my add-in I get random return values. I tried putting this in my .dna file:
<Reference Path="C:\my path\license_tools.dll" Pack="true" />

but should I keep the Private Declare Function statement?
Coordinator
Nov 13, 2013 at 9:48 AM
Hi,

You need to distinguish between managed (.NET) libraries, which you can directly reference from your .NET code, and can pack inside your .xll, and native libraries which you need to you the P/Invoke mechanism of .NET to interact with.

It looks like your library is a native library, so you won't be able to pack it in the .xll file (you'll have to copy it alongside the .xll) and you can't Reference it from your .NET code, so the <Reference> tag is no help. So the short answer is that you can get rid of the <Reference> tag, and need to keep the Declare Function declartion, and that should work fine (though you might need to change Long to Integer.

Some more explanation...

There are two issues with using native libraries with your Excel-DNA add-in:
  1. Locating and loading the library.
  2. Declaring the P/Invoke signatures to call the exports of your library.
Issue 1 occurs because the .NET runtime will have to locate the .dll, and might not search in the same path as the .xll by default. To work around this, you can either put the full path explicitly in the P/Invoke declaration, or explicitly load the library before you need to use it with a call to the Windows LoadLibrary function, passing in the directory of the add-in & the .dll name. For simplicity, you can keep the full path in your Declare Function, but it's good to know that there is an alternative if you want to be able to deploy the add-in to other (unknown) locations.

Issue 2 has to do with the signature in your Declare Function statement. There are two ways to declare the signature in VB.NET: the Declare Function style which is mostly compatible with VBA, and using a <DllImport> attribute, which matches the P/Invoke usage in C#. Both are fine, but if you see a sample in C# which uses [DllImport] you should know that you can do it the same way in VB.NET too. (See this discussion: http://stackoverflow.com/questions/937064/dllimport-vs-declare-in-vb-net)

There are however some differences in how the types in VBA and VB.NET work, and I think your problem might be there. The type called "Long" in VBA is a 32-bit integer type, and is called "Integer" in VB.NET. In addition, VB.NET defines a 64-bit integer type, called "Long". I think your .dll function is returning a 32-bit integer, and so your P/Invoke declaration should read "As Integer".
The main other type issues you might need to take some care with relate to strings.

OK - that should give you a start. Please write back if you're still stuck.

Regards,
Govert
Nov 13, 2013 at 4:42 PM
You sir, are a gentleman and a scholar.

It works now after removing the Reference tag and changing from Long to Integer.

Cheers!