Throw away Declare Function with IDL for Modules

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.