How to call a MSVCpp2010x64-DLL from XL14x64

Belonging to the group of people that sociologists call "early adopters" usually is a burden with respect to newly invented technologies, and unfortunately, I cannot avoid this situation all of the time… Fortunately, nowadays it is possible to search for suitable keywords on the Internet, and quite often, this strategy is much more effective in order to solve a problem than reading through manuals. Rarely, the result of such a search will be a thick searchable manual that covers all aspects one is interested in. A recent example of this situation occurred when I wanted to call functions in a self-compiled C++ DLL from VBA7, i.e., the macro language than comes with the 64bit version of Microsoft Office 2010.

The first thing I realised was that it is impossible to call a 32bit DLL from a 64bit application directly (and vice versa). Then it turned out that the so-called express edition of Visual Studio 2010 does not contain 64bit versions of the compilers. Therefore, I had to follow the advice provided by MS and install the WindowsSDK version 7.1 in order to enable building x64 DLLs. Unfortunately, I got this finally working only after having previously de-installed all of the VS2010express stuff present on my PC, because otherwise, the installation of the WinSDK7.1 failed at an early stage (may be due to some updates…). After having re-installed all the desired languages in VS2010Express and afterwards the WinSDK_7p1 as well, I was happy to find VCpp2010Exp working as expected.

VCpp2010Exp provides a project template meant for creating DLLs: select file → new → project → Win32 → Win32-project, enter a name, and click ok. On the following welcome screen, do not click the finish but the continue button. A properties window will then be displayed in which you select the DLL radio button, and check the box labelled export symbols as well. Clicking the finish button will make VCpp2010Exp create a DLL project template that contains two files: the source code in *.cpp, and the header information in *.h . You’ll need to edit both of them later. Now the target platform has to be switched from Win32 to x64 in two steps. Firstly, select project → properties, change the content of the configuration button to all configurations, then select configuration properties → general in the list displayed on the left, change the entry for platform toolset in the general section of the list displayed on the right from v100 (or v90 or …) to Windows7.1SDK, and click the apply button. Secondly, after VCpp2010Exp has finished applying this change, click the configuration manger button and set the x64 platform (it does not matter whether the debug or release configuration is active) by clicking on platform, selecting new, changing the entry of new platform to x64, clicking the ok button, and finally clicking the close button of the configuration manager.

I decided to start with a very simple x64 DLL that only provides two different functions, i.e., a function of type integer (in VBA7, this corresponds to a function of type long), and another function of type void (corresponding to a sub in VBA7). Both functions calculate the product of two numbers provided as input variables; the integer function returns the result as its own value, whereas the void function returns it as value of a third variable, which consequently must be transferred by reference (in the declaration, an ampersand has to be noted directly in front of the variable’s name in Cpp, and the ByRef keyword in VBA7).

The C++ file named DLL_Test.cpp thus reads:

#include "stdafx.h"
#include "DLL_Test.h"

DLL_TEST_API int DLL_Mult_FNC(int a, int b)
return a*b;

DLL_TEST_API void DLL_Mult_SUB(int a, int b, int &c)
c = a*b;

The meaning of DLL_TEST_API is defined in the header file DLL_Test.h together with some other very important settings:

#define DLL_TEST_API __declspec(dllexport)
#define DLL_TEST_API __declspec(dllimport)

extern "C"
DLL_TEST_API int DLL_Mult_FNC(int a, int b);

DLL_TEST_API void DLL_Mult_SUB(int a, int b, int &c);

The block of macro-defining statements at the beginning has been added by VCpp2010Exp automatically, and saves a bit of typing if many functions are to be exported. The magic extern "C" block around the function declarations has been added manually after finding out that otherwise the names of the exported functions are so badly mangled that they cannot be called from VBA7. Imho, the tool Dependency Walker is quite valuable in such a situation. The hint that name mangling could be the reason for my DLL functions not yielding any result on the first try was mentioned in a post on Stack overflow, in which user arsane posted a link to a very comprehensive survey of calling conventions.

In VBA7, the functions need to be declared in a standard module; the name of a function in VBA7, which directly follows the function or sub keywords, has to be different from the name exported by the (C++) DLL, which has to be given directly after the Alias keyword.

Option Explicit

Declare PtrSafe Sub SUB_AmultB Lib "insert-correct-path-to-dll-here\DLL_Test.dll" Alias "DLL_Mult_SUB" (ByVal ia&, ByVal ib&, ByRef ic&)
Declare PtrSafe Function FNC_AmultB& Lib "insert-correct-path-to-dll-here\DLL_Test.dll" Alias "DLL_Mult_FNC" (ByVal ia&, ByVal ib&)

Function WS_DLL_Mult_SUB&(ByVal ia&, ByVal ib&)
Dim ic&
Call SUB_AmultB(ia, ib, ic)
WS_DLL_Mult_SUB = ic
End Function

Function WS_DLL_Mult_FNC&(ByVal ia&, ByVal ib&)
WS_DLL_Mult_FNC = FNC_AmultB(ia, ib)
End Function

Do not get confused by the completely different meanings of ampersands in C++ and VBA7, resp.: in C++, a leading ampersand denotes a call by reference, whereas in VBA7, a trailing ampersand defines the variable to be of type long.

In case you find this recipe is not working for you, please let me know…


Comments are closed.