You need to distinguish between managed (.NET) libraries
, which you can directly reference from your .NET code, and can pack inside your .xll, and
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
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
Some more explanation...
There are two issues with using native libraries with your Excel-DNA add-in:
- Locating and loading the library.
- 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
, 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
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:
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.