Easier for a Camel to go through eye of the Needle than to pass strings from VBA to C++ Dll

It is easier for a Camel to go through eye of the needle than to pass strings from VBA to C++ Dll or at least that is how it has felt for the last day or so.

I have found a good StackOverflow question centred on the issue Passing strings from VBA to C++ DLL. The questioner solves the problem in his question and asks for an explanation before supplying his own. Oddly, the questioner's question is upvoted to 6 whilst his answer is downvoted. Anyway, the response from Ben carries a wonderful nugget. The Declare Function foo lib "bar" interface is constrained by Visual Basic 3 compatibility!

So, the strings get marshalled as narrow ANSI strings! As such, for them to be of any use they need to be converted to Unicode and for this we use the A2W conversion macro from Active Template Library (ATL).

I've put together some sample code and it is given below it demonstrates (1) passing a string as an input argument and (2) passing a string as a buffer for returning a string from the Dll.

A run through as to how create the Dll. In Visual Studio create a new C++ Win32 project.

Then in the application wizard select a Dll and also select 'Export symbols'

Click Finish and let Visual Studio create the project skeleton, when it has finished add a Source.def file, do this via New Item and search for "def" in templates, see diagram.

Edit the Source.def file to contain the following

LIBRARY MyDll  
EXPORTS  
   PassingStrings @1

In the PassStrings.cpp make the code like the following

#include <atlbase.h>

PASSSTRINGS_API  void __stdcall PassingStrings(
 LPCSTR pcsStringIn,
 LPCSTR* pcsBufferToWriteOut,
 INT* charsWritten
 )
{
 /* First Argument, using a string passed in
  * To do something useful with the string you need to convert to a wide
  * the following code demonstrates a dll filepath being loaded
  */
 USES_CONVERSION;
 LPCWSTR w = A2W(pcsStringIn);
 HMODULE hModule = 0;
 hModule = ::LoadLibrary(w);

 /* Second Argument, we can write to the buffer allocated by caller
  * We can use 3rd argument charsWritten to signal how many we've 
  * used or extra will need
  */
 LPCSTR csBufferToWriteOut = *pcsBufferToWriteOut;
 LPCSTR csCreatedInCPlusPlus = LPCSTR(W2A(L"Created in C++"));

 int lenArgIn = strlen(csBufferToWriteOut);
 int lenArgOut = strlen(csCreatedInCPlusPlus);
 if (lenArgIn < lenArgOut)
 {
  // we got a problem, then buffer is not big enough
  // signal size of the gap via *charsWritten
  *charsWritten = lenArgIn - lenArgOut;
 }
 else
 {
  // need to use the following because older functions throw security compilation errors
  strncpy_s((char *) csBufferToWriteOut, lenArgIn, csCreatedInCPlusPlus, lenArgOut);

  *charsWritten = lenArgOut;
 }
}

Where PASSSTRINGS_API is a macro that flips between import and export, this is created by Visual Studio at project creation and will be different for you and will in fact be based on the name of the project.

#ifdef PASSSTRINGS_EXPORTS
#define PASSSTRINGS_API __declspec(dllexport)
#else
#define PASSSTRINGS_API __declspec(dllimport)
#endif

Build the project with F7. Resolve any compilations errors like typos. When built ok proceed to test client.

You'll need a VBA Client, we'll use a macro-enabled Excel workbook, the best location wouild be same directory as the built dll, thjis will be in the Debug folder. To get to the Debug folder from the Solution Explorer right-click menu take 'Open Folder in File Explorer', go up one folder and down into Debug. There create a macro-enabled Excel workbook called TestClient.xlsm.
In TestClient.xlsm add the following code to either ThisWorkbook or a new standard module.

Option Explicit

Private Declare Sub PassingStrings Lib "PassStrings.dll" ( _
        ByVal pcsStringIn As String, _
        ByRef pcsBufferToWriteOut As String, _
        ByRef charsWritten As Long)

Private Declare Function LoadLibrary Lib "Kernel32" Alias "LoadLibraryA" _
(ByVal lpLibFileName As String) As Long


Sub TestPassingStrings()
    '* ensures workbook in the right place
    Debug.Assert Dir(ThisWorkbook.Path & "\PassStrings.dll") = "PassStrings.dll"
    
    '* calling LoadLibrary to manually load library allows our Declare
    '* Function statement to skip the full path to the dll
    '* also allows flexibility in deployment
    Call LoadLibrary(ThisWorkbook.Path & "\PassingStrings.dll")
    
    '* and now the client logic
    Dim sFilePath As String, sBuffer As String, lCharsWritten As Long
    
    sFilePath = "C:\Windows\System32\scrrun.dll"
    
    '* create a buffer for the C++ to write into
    '* it is important we create it and will will free it
    sBuffer = String(20, " ")
    lCharsWritten = 0
    
    '* call the Dll
    Call PassingStrings(sFilePath, sBuffer, lCharsWritten)
    
    '* either buffer was big enough and lCharsWritten > 0
    '* or not and lCharsWritten < 0
    If lCharsWritten > 0 Then
        Debug.Print "'" & Left$(sBuffer, lCharsWritten) & "'"
    Else
        Debug.Print _
         "You need to dimension a buffer larger by '" & Abs(lCharsWritten) & "'"
    End If
End Sub

The code should run. You can play with the size of the buffer to test the buffer overrun defence.

To debug and step though the C++ code you'll need to set the startup program and command line arguments thus.


Now set a break point on the first line of code, switch to the VBA and run the VBA, the C++ break point should hit. At this point you can see the strings passed in in the Locals/Autos window.