C++ / VBA - How to send a COM object from VBA to a C++ DLL via PInvoke

Article
10/21/2016

Today I would like to present a quite uncommon scenario, which involves requesting a COM object from VBA and forwarding it
through PInvoke to another C++ DLL.

The puzzling part is that if we work with managed COM DLLs, everything runs properly, but if we're using C++ DLLs, Office will
crash with an Access Violation!

Here's some background info. about the components involved ...

COM object originates from a simple C++ COM DLL

This is the Interface from which VBA gets its COM object. I created it based on based on a MSDN sample: In-process C++ COM server (CppDllComServer).

Besides the usual COM interfaces (IUnknown, IDispatch), it exposes an interface named ISimpleObject and a COM Class
that implements it.

 /****************************** Module Header ******************************\Module Name: SimpleObject.hProject:     CppDllCOMServerCopyright (c) Microsoft Corporation.This source is subject to the Microsoft Public License.See https://www.microsoft.com/en-us/openness/resources/licenses.aspx#MPL.All other rights reserved.THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIEDWARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.\***************************************************************************/#pragma once#include #include "CppDllCOMServer_h.h" class SimpleObject : public ISimpleObject{public: // IUnknownIFACEMETHODIMP QueryInterface(REFIID riid, void **ppv);IFACEMETHODIMP_(ULONG) AddRef();IFACEMETHODIMP_(ULONG) Release(); // IDispatchIFACEMETHODIMP GetTypeInfoCount(UINT *pctinfo);IFACEMETHODIMP GetTypeInfo(UINT itinfo, LCID lcid, ITypeInfo **pptinfo);IFACEMETHODIMP GetIDsOfNames(REFIID riid, LPOLESTR *rgszNames, UINT cNames, LCID lcid, DISPID* rgdispid);IFACEMETHODIMP Invoke(DISPID dispidMember, REFIID riid, LCID lcid, WORD wFlags, DISPPARAMS *pdispParams,                         VARIANT *pvarResult, EXCEPINFO *pExcepInfo, UINT *puArgErr); // ISimpleObjectIFACEMETHODIMP get_FloatProperty(FLOAT *pVal);IFACEMETHODIMP put_FloatProperty(FLOAT newVal);IFACEMETHODIMP HelloWorld(BSTR *pRet);IFACEMETHODIMP GetProcessThreadID(LONG *pdwProcessId, LONG *pdwThreadId);SimpleObject();

VBA accesses the COM DLL via add Tools > Reference

 Sub test()Dim comObj As CppDllCOMServerLib.SimpleObjectSet comObj = New SimpleObjectDebug.Print comObj.HelloWorldEnd Sub

Finally, VBA forwards the COM Object to a different C++ DLL via PInvoke

It's just a simple C++ DLL which exposes a function that takes in an ISimpleObject COM pointer.

Pinvoke_CppDllCOMServer.h

 #include #include namespace SimplePInvokeDLL{  #import "C:\\..\\C++ COM DLL\Debug\\CppDllCOMServer.dll"  using namespace CppDllCOMServerLib;  extern "C"  {   __declspec(dllexport) int __stdcall API_DummyCOMCall(ISimpleObject* iso);   // Returns random number   __declspec(dllexport) long API_DummyCall();  }}

Pinvoke_CppDllCOMServer.cpp

 #include "stdafx.h"#include "Pinvoke_CppDllCOMServer.h"#include <stdlib.h> #include <time.h>  using namespace std;namespace SimplePInvokeDLL{  __declspec(dllexport) long SimplePInvokeDLL::API_DummyCall() {  /* initialize random seed: */  srand(GetTickCount());  /* generate a random number*/  return rand(); } __declspec(dllexport) int __stdcall API_DummyCOMCall(ISimpleObject* iso) {  //_bstr_t Class  https://msdn.microsoft.com/en-us/library/zthfhkd6.aspx  /*  A _bstr_t object encapsulates the BSTR data type. The class manages resource  allocation and deallocation through function calls to SysAllocString and SysFreeString  and other BSTR APIs when appropriate. The _bstr_t class uses reference counting to  avoid excessive overhead.  */       _bstr_t strComResult = iso->HelloWorld();  _bstr_t strLocal = _bstr_t(L"HelloWorld");   return strComResult == strLocal; }}

As I wrote before, the goal of our exercise is to obtain a COM pointer from the 1st DLL and forward it to the 2nd DLL, via VBA ..

 Declare Function API_DummyCOMCall Lib "C:\...\Debug\Pinvoke_CppDllCOMServer.dll" _                (simpleObj As CppDllCOMServerLib.SimpleObject) As IntegerSub test()Dim comObj As CppDllCOMServerLib.SimpleObjectSet comObj = New SimpleObjectDebug.Print comObj.HelloWorldDebug.Print API_DummyCOMCall(comObj)End Sub

... the first part works, and we're getting a valid COM object from CppDllCOMServerLib:

Now, if we attempt to send this COM object via a Pinvoke call...

Let's attach to the PInvoke DLL and see why we crash

First, we need to restart Excel, and pause the code just before we make the PInvoke call.

Then we have to open the Visual Studio PInvoke C++ DLL project, and attach to the running instance of Excel. You'll also need to add a breakpoint on the PInvoke function which is used by VBA to send the COM pointer.

Finally, we switch back to Excel and resume executing the code. We'll soon hit the PInvoke C++ callback function and if we
take a closer look at the input parameter, we'll see it points at an IUnknown VTable.

The only problem here is that this VTable has a couple of NULL pointers inside ... now I don't know enough about COM to say
for sure that those bad addresses are the cause, but if we step through the code, we'll see that when trying to execute the
COM call, we end up trying to execute code from address zero, which is not very nice.

My idea was to find a proper way of sending COM pointers from VBA, and avoid NULL VTable pointers inside the PInvoke DLL
callback function. After asking around, I was told that VBA has a special operator which returns the address of an object:

How To Get the Address of Variables in Visual Basic [...] ObjPtr

 ObjPtr takes an object variable name as a parameter and obtains the address of the interface referenced bythis object variable. One scenario of using this function is when you need to do a collection of objects. By indexing the object using its address as the key, you can get faster access to the object than walkingthe collection and using the Is operator. In many cases, the address of an object is the only reliable thing to use as a key.Example:objCollection.Add MyObj1, CStr(ObjPtr(MyObj1))objCollection.Remove CStr(ObjPtr(MyObj1))

So, we will have to modify our PInvoke declaration and the way we send the COM object like this. Notice that since ObjPtr
returns an address, we need to change the PInvoke method's input parameter's type from SimpleObject to LongPtr.

 Declare Function API_DummyCOMCall Lib "C:\...\Debug\Pinvoke_CppDllCOMServer.dll" _                (ByVal simpleObj As LongPtr) As IntegerSub test()Dim comObj As CppDllCOMServerLib.SimpleObjectSet comObj = New SimpleObjectDebug.Print comObj.HelloWorldDebug.Print API_DummyCOMCall(ObjPtr(comObj))End Sub

What's that? VBA complains about the calling convention ... hmmm.

After some research I found that PInvoke C++ DLL functions which take parameters must have the " __stdcall" calling convention, so that cleaning up the Stack gets done by the callee. With this occasion we're losing our nicely formatted function
names and we'll get decorated names instead …

Let's fix the VBA code to reflect that change in calling conventions.

 Declare Function API_DummyCOMCall Lib "C:\...\Debug\Pinvoke_CppDllCOMServer.dll" _                 Alias "_API_DummyCOMCall@4"(ByVal simpleObj As LongPtr) As IntegerSub test()Dim comObj As CppDllCOMServerLib.SimpleObjectSet comObj = New SimpleObjectDebug.Print comObj.HelloWorldDebug.Print API_DummyCOMCall(ObjPtr(comObj))End Sub

Success! We can now send COM objects from VBA to C++ DLLs and not crash Office in the process :).

Source code download link: Example - C++ COM DLL send COM pointer from VBA with PInvoke

Thank you for reading my article! If you have liked it, please use the rating button.

P.S. I can’t always manage to reply to your comments as fast as I’d like. Just drop me an email at cristib-at-microsoft-dot-com,
should you need help with understanding or getting something in my blog to work.

 DISCLAIMER:Please note that the code I have offered is just a proof of concept and should not be put into production without a thorough testing! Microsoft is not responsible if your users will lose data because of this programming solution. It’s your responsibility to test it before deployment in your organization.THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHEREXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce anddistribute the object code form of the Sample Code, provided that. You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded;and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits,including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

C++ / VBA - How to send a COM object from VBA to a C++ DLL via PInvoke

Additional resources