So I had some grief recently with Declare Function foo Lib "bar" and passing strings but after discovering that they got cast to narrow ANSI strings I found a
workaround. The explanation was found at a SO question
Passing strings from VBA to C++ DLL. Also found here was the tantalising suggestion that MIDL.exe (Microsoft's IDL compiler) is meant to be used to replace Declare Function. Eagerly I set bounty to draw out a fuller response. But I have managed to crack the problem myself. Here I will show the code.
I knew a thing or two about Interface Definition Language (IDL) from working with Active Template Library (ATL) which is a Microsoft C++ library for building COM components for Automation clients like VBA. I always knew ATL could be used for COM classes but the SO question tipped that there are other keywords in the IDL language that could define functionality outside of classes.
The key is
IDL's Module keyword It allows a Module to be defined and functions defined inside the module. Actually, VBA developers are familiar with this construct since many of the VBA functions are defined in Modules:Strings,DateTime, Math etc.
Let's look at some IDL in OLEView.exe. The joined green elipses should demonstrate how the library in OLEView ties in with VBA's Tools References dialog box. Within the VBA's IDL, I have selected the math module as will drill into this in a moment.
The second exhibit is a screenshot of OLEView.exe and Dependency Walker (Depends.exe). Dependency Walker shows a DLL's exported functions and we can see that VBE7.DLL exports a function called rtcAbsVar which has an entry point ordinal of 656. In the IDL you can also see 656 in the entry(656) attribute for the Abs function and so it can be seen this is how we tie "ABS" in VBA code to call into VBE7.DLL's rtcAbsVar.
Also at top of the IDL we can see dllname("VBA6.DLL") and I confess I do not know why this does not say VBE7.DLL, I could not find a file called VBA6.DLL on my machine. No matter, the code I will give works. We will replicate this technique.
I opened Microsoft Visual Studio 2013 with Administrator rights and I created a new C++, ATL Win32 project and I called it IDLForModules.
Then I click Finish and let VS2013 create the default project. I like to compile it fresh just to ensure no problems (if you forgot to open VS2013 with admin rights you'll get an 'unable to register' error). Once compiled happily then we need to rewrite the IDL code. So open IDLForModules.idl from the Solution Explorer.
Change the IDL to the following (you ought to regenerate your own GUIDs)
// IDLForModules.idl : IDL source for IDLForModules
//
// This file will be processed by the MIDL tool to
// produce the type library (IDLForModules.tlb) and marshalling code.
import "oaidl.idl";
import "ocidl.idl";
[
helpstring("Idl For Modules"),
uuid(EA8C8803-2E90-45B1-8B87-2674A9E41DF1),
version(1.0),
]
library IDLForModulesLib
{
importlib("stdole2.tlb");
[
/* dllname attribute https://msdn.microsoft.com/en-us/library/windows/desktop/aa367099(v=vs.85).aspx */
dllname("IdlForModules.dll"),
uuid(4C1884B3-9C24-4B4E-BDF8-C6B2E0D8B695)
]
module Math{
/* entry attribute https://msdn.microsoft.com/en-us/library/windows/desktop/aa366815(v=vs.85).aspx */
[entry(656)] /* map function by entry point ordinal */
Long _stdcall Abs([in] Long Number);
}
module Strings{
[entry("pUpper")] /* map function by entry point name */
BSTR _stdcall Upper([in] BSTR Number);
}
};
You may compile again at this point as it should still compile. Next, we will write some C++ code, so open IDLForModules.cpp. At the top, add the following Standard libraries to the list of includes
#include <string>
#include <algorithm>
And then at the bottom of the module paste in the following code
INT32 __stdcall _MyAbs(INT32 Number) {
return abs(Number);
}
BSTR __stdcall pUpper(BSTR sBstr)
{
// Get the BSTR into the wonderful world of std::wstrings immediately
std::wstring sStd(sBstr);
// Do some "Mordern C++" iterator style op on the string
std::transform(sStd.begin(), sStd.end(), sStd.begin(), ::toupper);
// Dig out the char* and pass to create a return BSTR
return SysAllocString(sStd.c_str());
}
Compile again as code is still valid but not yet complete. Finally open IDLForModules.def from the Solution Explorer and make the code look like this
; MidlForModules.def : Declares the module parameters.
LIBRARY
EXPORTS
DllCanUnloadNow PRIVATE
DllGetClassObject PRIVATE
DllRegisterServer PRIVATE
DllUnregisterServer PRIVATE
DllInstall PRIVATE
_MyAbs @656
pUpper
Code is now complete and should compile. Next is to write a test client, we will create a macro-enabled Workbook called TestClient.xlsm. This should be housed in the same directory as the created Dll. (Please note there is a type library created but in a different place, don't worry for time being). Use "Open Folder in Explorer" right-click menu option in Solution Explorer then go up one directory and down into Debug (so that's "..\Debug" ) and you should find the created IDLForModules.dll, put TestClient.xlsm there.
Inside TestClient.xlsm open the ThisWorkbook module and write the following code
Option Explicit
Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Private Sub Workbook_Open()
'* next line establishes relative position of Dll
Debug.Assert Dir(ThisWorkbook.Path & "\IDLForModules.dll") = "IDLForModules.dll"
'* next line loads the Dll so we can avoid very long Lib "c:\foo\bar\baz\barry.dll"
LoadLibrary ThisWorkbook.Path & "\IDLForModules.dll"
'* next go to Tools References are check "Idl For Modules"
'* "Idl For Modules" Iis set in the IDL with helpstring("Idl For Modules")
End Sub
The above code loads your new Dll upon workbook opening and this helps the system find the Dll. You could hard code the path into the DllName attribute in the IDL (bad idea) or there must be some sort of DLL search path feature but I have not found that yet. Run this code to ensure you have workbook in the right place, i.e.
same directory as the Dll.
Then you go to the Tools References dialog box and you check "Idl For Modules", this is the type library created by your project.
Finally, in a new standard module add the following code and then run it. It should work.
Option Explicit
Sub TestAbs()
Debug.Print IDLForModulesLib.Math.Abs(-5)
End Sub
Sub TestUpper()
Debug.Print IDLForModulesLib.Strings.Upper("foobar")
End Sub
If you want to step through the code then in the Project Properties set up Excel.exe as the Command and your TestClient.xlsm as a command argument. Same as bottom of this
article.
Just to recap what just happened, we created an ATL project that exported some C++ functions and then we wrote some IDL to map some symbols useable in VBA to the C++ functions, so we did not need the same function name. An example is given of mapping by entry point ordinal as found in the DEF file or by name also as found in the DEF file.
So we can now throw away Declare Function foo lib "bar" as a mechanism.
