VBA Interoperability And Extensibility

1. Install Visual Studio

Download and install Microsoft Visual Studio and install Visual C/C++ during the install process. It is fine to use the community version which is free. In this tutorial Microsoft Visual Studio Community 2019 is used but other versions of Visual Studio should be very similar.

2. Create Project

Create an Empty C++ project or a C++ DLL project in Visual Studio.

3. Configure Project

Make sure that your project is targeting the correct platform. Release mode 64-bit will be used most of the time.

Configure the project to be a DLL and click Apply. From the Project properties navigate to Configuration Properties → General → Configuration Type and change the configuration type to Dynamic Library.

4. Add Code Files

Add a cpp file. Optionally add header file.

5. Specify Calling Convention

Set the calling convention to __stdcall. Configuration Properties → C/C++ → Advanced → Calling Convention: __stdcall. This can also be specified in code using __stdcall in the function signature.

6. Export DLL Functions

To export functions from a DLL use __declspec(dllexport) with exported functions. To avoid name-mangling when exporting C++ functions use extern "C" for each exported function.

#define DLLExport __declspec(dllexport)

extern "C" {

    DLLExport int __stdcall CppAddTwoNumbers(int num1, int num2) {
        return num1 + num2;
    }

}

A Module Definition File (.def) can be used instead of using __declspec(dllexport) and extern "C". When using a .def file specify the file name in the Project Properties: Configuration Properties → Linker → Input → Module Definition File.

int __stdcall CppAddTwoNumbers(int num1, int num2) {
    return num1 + num2;
}

LIBRARY ExampleDLL
EXPORTS
    CppAddTwoNumbers

7. Build Project

Build the project to create the DLL file.

If needed, DLLs can be compiled as C code. Change .cpp files to .c files and change the project setting: Configuration Properties → C/C++ → Advanced → Compile As: Compile As C Code (/TC).

Note: When compiling code as C, extern "C" does not need to be used.

8. Declare Function in VBA

Use the Declare statement to declare the DLL procedure in VBA.

Option Explicit

Public Declare PtrSafe Function CppAddTwoNumbers _
Lib "C:\ExcelFunctions\ExcelFunctions\x64\Release\ExcelFunctions.dll" ( _
ByVal Num1 As Long, ByVal Num2 As Long) As Long


Public Sub Example()
    
    Dim Result As Long
    
    Result = CppAddTwoNumbers(1, 2)
    
    Debug.Print Result
    
End Sub

Error 453

Error 453 occurs when the path to a DLL file is valid but the procedure name is not valid. Make sure the function name declared in VBA matches the C++ function name and make sure that name mangling did not occur by using extern "C", a module definition (.def) file, or compile as C code.

Error 53

Error 53 can occur when the DLL file cannot be found or something is preventing VBA from accessing the file. Try checking the file path specified with the Lib statement, move the DLL to a folder specified in your environment path variable such as C:\Windows\System32, or disable software that may be preventing access to the DLL or folder.

Error 49

Error 49 occurs when a calling convention other than __stdcall is used. VBA requires the calling convention to be __stdcall. The calling convention can be set using __stdcall in the function signature or by changing the project property setting Configuration Properties → C/C++ → Advanced → Calling Convention: __stdcall.

Error 48

Error 48 can have several causes. Most commonly this error is caused by the DLL using a different bit version than the target machine or by a dependency issue. First, ensure that the bitness of the DLL and target machine are the same. If the bitness is correct and the error persists a number of steps can be taken to try to resolve any dependency issues. First, make sure all dependencies exist on the target machine and are available in a folder specified in your path variable. Use Dependency Walker to check the dependencies of the DLL. The DUMPBIN command can also be used specifying /DEPENDENTS from the available command options. If all dependencies appear to be present and in the correct place but the error persists, installing the Visual C++ redistributables or installing Visual Studio including Visual C++ on the target machine may resolve the issue. If the issue persists it may help to statically link the C++ runtime by setting C/C++ → Code Generation → Runtime Library to Multi-Threaded (/MT). Any manifest file can also be removed by setting Linker → Manifest File → Generate Manifest to No (/MANIFEST:NO) and setting Manifest Tool → Input and Output → Embed Manifest to No. If the issue persists you can try not linking the runtime at all provided that it is not used in your code. To not link the runtime set Linker → Input → Ignore All Default Libraries to Yes (/NODEFAULTLIB) and set Linker → Advanced → No Entry Point to Yes (/NOENTRY). It may also be necessary to set C/C++ → Code Generation → Security Check to Disable Security Check (/GS-). If the issue still persists check if there is a program preventing access to the DLL dependencies or if there is an issue with the Windows registry such as a corrupted registry or an invalid key.

Types

Equivalent data types need to be matched between VBA and C++. Include wtypes.h to access the C++ types in this table. C++ type sizes for int, long, long long, and double may vary depending on platform. Include stdint.h to gain access to integer types that specify the number of bits specifically.

Procedure Type

When a C++ function has the return type void, the function does not return a value. C++ functions that do not return a value should be declared as Subs in VBA. Procedures that do return a value should be declared as Functions in VBA.

Pointers

When a function parameter expects a pointer in C++, the parameter should be declared ByRef in VBA, otherwise the parameter should be declared ByVal.

Pointers can also be declared in VBA using ByVal and the LongPtr type. Long can be used for pointers on 32-bit platforms when VBA6 is used. VBA provides pointer functions which can be used to retrieve pointers to variables. In VBA7 these functions return a LongPtr and in VBA6 they return Long. These functions can be used to pass pointers to a parameter expecting a pointer.

Pointer Functions:

• ObjPtr - Returns a pointer to an object.
• StrPtr - Returns a pointer to the string portion of a VBA string variable.
• VarPtr - Returns a pointer to a variable.

Arrays

When passing an array argument to a C++ DLL procedure, pass the first element of the array ByRef. Passing the first element of the array ByRef is essentially passing a pointer to the first element of the array. In C++ the identifier of an array is a pointer to the first element in the array. In the C++ function below, the "nums" parameter is essentially a pointer to the first element of an array of type int. A pointer to an int could be used instead and the code would still work.

// C++ 64-bit ExcelFunctions.dll

#define DLLExport __declspec(dllexport)

extern "C" {

    DLLExport int __stdcall CppSum(int nums[], int n) {
        int theSum = 0;
        for (int i = 0; i < n; ++i) {
            theSum += nums[i];
        }
        return theSum;
    }

}

Option Explicit

Declare PtrSafe Function CppSum Lib "C:\ExcelFunctions.dll" ( _
ByRef Nums As Long, ByVal N As Long) As Long

Public Sub Example()

    Dim Arr() As Long
    ReDim Arr(0 To 4)

    Arr(0) = 1
    Arr(1) = 2
    Arr(2) = 3
    Arr(3) = 4
    Arr(4) = 5

    Dim ArrSize As Long
    ArrSize = UBound(Arr) - LBound(Arr) + 1

    Dim Result As Long
    Result = CppSum(Arr(LBound(Arr)), ArrSize)

    Debug.Print Result

End Sub

Structs

To pass a struct to a DLL procedure, create a corresponding user-defined type and pass the UDT to the DLL. UDTs must be passed by reference so C++ DLL functions must take a pointer to the struct as its parameter. To return a struct simply return the struct in the C++ procedure and in VBA return the corresponding type. Alternatively, the UDT can be passed to the procedure and its members can be mutated in C++ since the UDT is passed by reference.

#define DLLExport __declspec(dllexport)

struct RECTANGLE {
    int s1;
    int s2;
};

extern "C" {

    DLLExport int __stdcall CppRectangleArea(RECTANGLE* rect) {
        return rect->s1 * rect->s2;
    }

    DLLExport int __stdcall CppRectanglePerimeter(RECTANGLE* rect) {
        return 2 * rect->s1 + 2 * rect->s2;
    }

    DLLExport RECTANGLE __stdcall CppGetRectangle(int s1, int s2) {
        RECTANGLE rect;
        rect.s1 = s1;
        rect.s2 = s2;
        return rect;
    }

}

Option Explicit

Public Type TRectangle
    S1 As Long
    S2 As Long
End Type

Public Declare PtrSafe Function CppRectangleArea _
    Lib "C:\ExcelFunctions.dll" (ByRef Rect As TRectangle) As Long

Public Declare PtrSafe Function CppRectanglePerimeter _
    Lib "C:\ExcelFunctions.dll" (ByRef Rect As TRectangle) As Long

Public Declare PtrSafe Function CppGetRectangle _
    Lib "C:\ExcelFunctions.dll" (ByVal S1 As Long, ByVal S2 As Long) As TRectangle

Public Sub Example()

    Dim Rect As TRectangle
    Rect.S1 = 10
    Rect.S2 = 20

    Dim Area As Long
    Area = CppRectangleArea(Rect)
    Debug.Print "Area: " & Area

    Dim Perimeter As Long
    Perimeter = CppRectanglePerimeter(Rect)
    Debug.Print "Perimeter: " & Perimeter

    Dim Rect1 As TRectangle
    Rect1 = CppGetRectangle(5, 10)
    Debug.Print Rect1.S1, Rect1.S2

End Sub

Function Pointers

The AddressOf operator is used to pass a function pointer to a DLL procedure.

// C++ 64-bit ExampleDLL.dll

#define DLLExport __declspec(dllexport)

extern "C" {

    DLLExport void __stdcall CppCallProcedure(void (*funcPtr)()) {
        funcPtr();
    }

}

Option Explicit

Public Declare PtrSafe Sub CppCallProcedure _
    Lib "ExcelFunctions.dll" (ByVal Func As LongPtr)

Public Sub Example()
    Call CppCallProcedure(AddressOf HelloWorld)
End Sub

Public Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Variants

VBA Variants can be used with C++. The C++ VARIANT structure can store a variable whose type is determined by the VARENUM.

Note: VT_BYREF and VT_ARRAY are bitwise or'd with the data type associated with the variable or array.

Strings

When passing strings from VBA to C++ DLL procedures it is recommended to use a Variant. Strings can also be passed as strings but there are some things to consider. Strings in VBA are internally the BSTR type. BSTRs contain a 4-byte integer prefix specifying the length of the string in bytes, a string of 2-byte characters, and a 2-byte null character to terminate the string. When allocating and releasing BSTRs in C++ use the SysAllocString, SysAllocStringByteLen, SysAllocStringLen, SysReAllocStringLen and SysFreeString functions to properly allocate and release memory. When strings are passed from VBA to a DLL procedure they are converted from 2-byte character strings to 1-byte character strings. When strings are returned to VBA from a DLL procedure they are converted to 2-byte strings. This is demonstrated in the example below where StrConv is used to correct for strings being converted by VBA. A parameter in a C++ function that is declared with the LPSTR type can receive a VBA string passed ByVal because it looks the same to C++ as the pointer to the string part of the BSTR. If the C++ parameter expects a LPWSTR, declare the parameter as a LongPtr and pass a pointer to a VBA string ByVal using the StrPtr function. At times, a C++ DLL procedure may expect a string or a null value. In this case, pass the vbNullString constant.

#define DLLExport __declspec(dllexport)

#include <wtypes.h>

extern "C" {

    DLLExport void __stdcall CppBSTRByVal(BSTR arg) {
        MessageBoxW(NULL, arg, L"BSTR passed ByVal", MB_OK);
        SysFreeString(arg);
    }

    DLLExport void __stdcall CppBSTRByRef(BSTR* arg) {
        MessageBoxW(NULL, *arg, L"BSTR passed ByRef", MB_OK);
    }

    DLLExport BSTR __stdcall CppReturnBSTR() {
        return SysAllocString(L"Hello, World!");
    }

    DLLExport void __stdcall CppLPSTR(LPSTR arg) {
        MessageBoxA(NULL, arg, "LPSTR", MB_OK);
    }

    DLLExport void __stdcall CppLPWSTR(LPWSTR arg) {
        MessageBoxW(NULL, arg, L"LPWSTR", MB_OK);
    }

    DLLExport void __stdcall CppPassVariant(VARIANT arg) {
        if (arg.vt == VT_BSTR) {
            MessageBoxW(NULL, arg.bstrVal, L"BSTR passed ByVal", MB_OK);
            SysFreeString(arg.bstrVal);
        }
    }

    DLLExport VARIANT __stdcall CppReturnVariant() {
        VARIANT v;
        v.vt = VT_BSTR;
        v.bstrVal = SysAllocString(L"Hello, World!");
        return v;
    }

}

Option Explicit

Public Declare PtrSafe Sub CppBSTRByVal _
    Lib "C:\ExcelFunctions.dll" (ByVal Arg As String)

Public Declare PtrSafe Sub CppBSTRByRef _
    Lib "C:\ExcelFunctions.dll" (ByRef Arg As String)

Public Declare PtrSafe Function CppReturnBSTR _
    Lib "C:\ExcelFunctions.dll" () As String

Public Declare PtrSafe Sub CppLPSTR _
    Lib "C:\ExcelFunctions.dll" (ByVal Arg As String)

Public Declare PtrSafe Sub CppLPWSTR _
    Lib "C:\ExcelFunctions.dll" (ByVal Arg As LongPtr)

Public Declare PtrSafe Sub CppPassVariant _
    Lib "C:\ExcelFunctions.dll" (ByVal Arg As Variant)

Public Declare PtrSafe Function CppReturnVariant _
    Lib "C:\ExcelFunctions.dll" () As Variant

Public Sub Example()

    Dim Message As String
    Message = "Hello, World!"

    Dim VariantMessage As Variant
    VariantMessage = "Hello, World!"

    'Pass BSTR ByVal
    CppBSTRByVal StrConv(Message, vbUnicode)

    'Pass BSTR ByRef
    CppBSTRByRef StrConv(Message, vbUnicode)

    'Return BSTR
    Dim BStrResult As String
    BStrResult = CppReturnBSTR()
    MsgBox StrConv(BStrResult, vbFromUnicode), Title:="Returned BSTR"

    'Pass BSTR ByVal which looks like LPSTR to C++
    CppLPSTR Message

    'Pass pointer to 2-byte character string
    CppLPWSTR StrPtr(Message)

    'Pass variant containing BSTR
    CppPassVariant VariantMessage

    'Return variant containing BSTR
    Dim VariantResult As Variant
    VariantResult = CppReturnVariant()
    MsgBox VariantResult, Title:="Returned Variant BSTR"

End Sub

1. Start Visual Studio

Download and install Visual Studio including the .NET Framework and C#. Run Visual Studio as an Administrator (Shift + right-click on application).

2. Create Project

Create a new project: C# Class Library (.Net Framework). There are other C# library options but choose .NET Framework.

3. Create Class

Create a class and add members to it.

4. Add a GUID for Class

Navigate to Tools → CreateGUID in Visual Studio to create a new GUID.

Add the GuidAttribute to the class. To use the GuidAttribute add a using clause for System.Runtime.InteropServices. Add the ComVisible(true) attribute to the class as well.

5. Configure Project

Set the project to release mode. For 64-bit set the target platform to x64.

Navigate to the project properties:

• Application → Assembly Information → Check Make Assembly COM Visible.
• Build → Check Register for COM interop.
• Signing → Sign the Assembly → Add new Key file and provide name and optional password.

6. Build Project

Build the project to create the DLL.

7. Generate Type Library (.tlb)

Run the RegAsm command as an administrator in the Visual Studio terminal on the DLL file with the options /codebase and /tlb.

The command line should look something like:

C:\> RegAsm /codebase /tlb ExcelFunctions.dll

Microsoft .NET Framework Assembly Registration Utility version 4.8.4084.0
for Microsoft .NET Framework version 4.8.4084.0
Copyright (C) Microsoft Corporation.  All rights reserved.
                       
Types registered successfully
Assembly exported to 'C:\ExcelFunctions.tlb', and the type library was registered successfully

8. Check Registry

The classes in the library must be registered in the Windows Registry. Check the registry for the GUID of the class using the regedit application.

If using a 64-bit machine and the project was built for 64-bit, or using a 32-bit machine and the project was built for 32-bit, check:

• HKEY_CLASSES_ROOT\CLSID
• HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID

If using a 64-bit machine and the project was built for 32-bit or "Any" platform check:

• HKEY_CLASSES_ROOT\WOW6432Node\CLSID

1. Set Reference in VBA

To use the class library in VBA set a reference to the .tlb file from Tools → References in the office application. Use the Browse button and select the .tlb file.

2. Instantiate and Use Class

Once a reference is set, the classes defined in the library can be instantiated and used in VBA. Note that intellisense will not be available by default.

Option Explicit

Public Sub Example()

    Dim EF As ExcelFunctions.ExcelFunctions
    Set EF = New ExcelFunctions.ExcelFunctions
    
    EF.HelloWorld
    
    Set EF = Nothing
    
End Sub

Class not registered

If a "Class not registered" error occurs in VBA, it could be because the DLL was registered in the "HKEY_CLASSES_ROOT\WOW6432Node\CLSID" node of the windows registry. This is to allow 32-bit systems to find the class. 64-bit Excel will look in the "HKEY_CLASSES_ROOT\CLSID" node for the class. Try compiling the DLL as 64-bit only.

Not a valid .NET assembly

If an error occurs when using RegAsm that states, "RegAsm : error RA0000 : Failed to load 'C:\ExcelFunctions.dll' because it is not a valid .NET assembly", make sure an older version of RegAsm.exe is not being used and make sure the .dll path is valid. Quotes around the file name can also cause this error.

1. Create VSTO Project in Visual Studio

Application-level or Document-level projects can be created. Application-level projects will be available for the entire Office application whereas Document-level projects are only available for one particular Office file.

2. Develop

Custom functions, ribbons, and forms can be added to the add-in to provide functionality and user interface. Navigate to View → Toolbox to see controls that can be added to ribbons and forms.

To test the add-in while developing click the Run button on the toolbar.

3. Deploy

A VSTO solution can be deployed using an installer or using ClickOnce.

1. Installs

• Node.js (The npm package manager will be installed also)
• Yeoman Generator

2. Create Project

Type "yo office" in the cmd command line and follow the steps to create a project.

C:\> yo office

    _-----_     ╭──────────────────────────╮
   |       |    │   Welcome to the Office  │
   |--(o)--|    │   Add-in generator, by   │
  `---------´   │ @OfficeDev! Let's create │
   ( _´U`_ )    │    a project together!   │
   /___A___\   /╰──────────────────────────╯
    |  ~  |
  __'.___.'__
´   `  |° ´ Y `

 Continue
? Choose a project type: Office Add-in Task Pane project
? Choose a script type: JavaScript
? What do you want to name your add-in? ExampleAddIn
? Which Office client application would you like to support? Excel

To start developing the Add-In in VS Code navigate to the location where the Add-In was created, Start a local web server using the command: "npm start", and open the project in VS Code using the command: "code .".

3. Develop Add-In

Depending on the type of project use HTML, CSS, JavaScript, TypeScript, and JavaScript Frameworks to develop a web application. Use the Office JavaScript API to access the Object Models of Office applications.

4. Deploy Add-In

Deploy and Publish the add-in according to the appropriate environment.