Integrate your own DLL (64 bit)
created with Visual Studio 2022 into Excel VBA

Tutorial

 

Introduction

This tutorial describes two very different ways of integrating a 64-bit DLL you have created yourself with Visual Studio 2022 into Excel VBA. The description also applies to Visual Studio 2019 and 2015. The first chapter is a DLL created with C++, the second chapter is a DLL created with C#. In both cases, special attention is paid to the parameter transfer of arrays.

In the first case (C++) it is sufficient to copy the DLL into the same directory as the Excel file. In the second case (C#), the DLL is registered in the Windows registry (as a so-called COM component) so that it appears in Excel VBA under "Tools/References" and is integrated by clicking on it.

There are quite a lot of instructions on this topic, but most of them are more than 10 years old and some things are not as complicated as they were back then. For example, the registration as a COM component no longer has to be carried out with "regasm.exe" from a command line, but Visual Studio does this automatically. One click in the right place is enough. It is also difficult to find useful information on passing array parameters.

Download

1.  Create DLL with C++ and integrate it into Excel VBA

There are several reasons to outsource a specific functionality to C++. One of them is the speed advantage. This example is about calculating the prime factorization of a number.

The security of the encryption methods all over the world is based on the fact that up to now every supercomputer would have needed decades or centuries for the prime factorization if the number itself is chosen large enough (several hundred digits) and consists of only two equally large prime factors. If one were to discover an efficient method to calculate the two large prime factors in a shorter time, it would be the worst case scenario for all encrypted data in the world.

In Excel, a maximum of 15 digits of an integer are displayed on the screen. C++ does the prime factorization of such a fifteen-digit number in a matter of seconds.

1.1  Create project in Visual Studio 2022

After clicking on "New project", the window with the available project templates appears:

At the top right you can filter for "C++", "Windows" and "Library".
Select the project template "Dynamic Link Library (DLL)". I chose "PrimeFactors" as the project name.

The automatically generated "dllmain.cpp" file is displayed in the editor.

Now add three files:

• primefact.h
• primefact.cpp
• primefact.def (same folder as cpp files)

The file 'primefact.h'

#pragma once
void primeFactors(long long x, long long* pn, long long* pf, long n);
bool isPrimeNumber(long long z);

The file 'primefact.def'

LIBRARY PrimeFactors
EXPORTS
primeFactors
isPrimeNumber

The 'primefact.def' file ensures that the entry points for the exported functions 'primeFactors' and 'isPrimeNumber' are inserted into the DLL file.

On this page Microsoft points out that you don't need a DEF file when using "__declspec(dllexport)":


In this case, that's not my experience. It doesn't work without a DEF file. On the other hand, I can do it without the addition "__declspec(dllexport)". Also the addition "__stdcall" and the "#include <windows.h>" used with 32-bit DLLs are not required for 64-bit DLLs.

The file 'primefact.cpp'

#include "pch.h"
#include "primefact.h"

void primeFactors(long long z, long long* pn, long long* pf, long n)
{
    long i = 0;
    long k = -1;
    bool overflow = false;

    while ((pn[i] * pn[i] <= z) && not overflow) {
        while (z % pn[i] == 0) {
            k++;
            pf[k] = pn[i];
            z /= pn[i];
        }
        if (z > 1) i++;
        if (i > n) overflow = true;
    }
    if (overflow) {
        pf[0] = -1;
    }
    else {
        if (z > 1) {
            k++;
            pf[k] = z;
        }
    }
}

bool isPrimeNumber(long long z)
{
    long long i;
    if (z < 2) return false;
    if (z == 2) return true;
    if (z % 2 == 0) return false;
    for (i = 3; i * i <= z; i += 2)
    {
        if (z % i == 0) return false;
    }
    return true;
}

The 'primeFactors' function is given the number 'z' (64-bit integer) to be decomposed, as well as the array 'pn' with the first 1000000 (1 million) prime numbers and a number n, which indicates the length of the array 'pn'. The list of calculated prime factors is then returned in the array 'pf'. The two array parameters expect a pointer to the first array element (see Excel VBA code).

The list of prime numbers is permanently stored in the calling Excel file. It was created using the C# searchPrimes function introduced in Chapter 2. The largest prime number stored is 15,485,863. If an even larger prime factor occurs during prime factorization, 'pf[0]' is set to -1 and the text 'OVERFLOW' appears in the Excel file.

Entry of the DEF file in the project properties

In order for the DEF file to be used, the file name must be entered in the project properties. To do this, open the project properties - e.g. "Project ↠ PrimeFactors Properties" menu.
This window appears:

Select "Linker ↠ Input" on the left, enter the file name "primefact.def" under "Module Definition File" on the right.

ATTENTION:  When you build a release, the module definition file must also be entered for the release mode! Select the release mode at the top left under “Configuration” and enter the file name “primefact.def” at the right under “Module Definition File”.

Entry for debugging

In the 'Project Properties' window, click on "Debugging" on the left and on "Command" at the top right. By clicking on the button at the right end and on "Browse" one can navigate to the folder where the Excel application file 'EXCEL.EXE' is stored. The full path must be entered here.

Debugging works by closing the Excel file, setting a breakpoint in the CPP file and choosing "Debug ↠ Start Debugging" from the menu. Visual Studio will now start Excel. There you open the "PrimeFactors.xlsm" file and trigger a call to the DLL file - for example by changing cell C4 on the 'Prime factorization' worksheet. The Visual Studio editor comes to the foreground automatically and the execution of the DLL stops at the breakpoint.

Building a 64-bit DLL

It is very important to change the "x86" to "x64" above under the menu bar before building the solution:

If you forget this - like I did in the beginning - this can drive you to despair. Because you get the runtime error 48:


This one has the misleading text "File not found". So it can happen that you try different file locations and registrations in the registry for days - without success. Until you eventually get the idea to look up what this runtime error actually means. And one of those meanings can be bit version mismatch.

If you delete the DLL file as a test, you get the runtime error 53:


This also displays the text "File not found". But this time the text points in the right direction.

1.2  Create Excel file

Paste the following VBA code into a module of the XLSM file:

Option Explicit

Declare PtrSafe Sub primeFactors Lib "E:\Excel\Excel_Calls_DLL\PrimeFactors.dll" _
(ByVal z As LongLong, ByRef pn As LongLong, ByRef pf As LongLong, ByVal n As Long)

Declare PtrSafe Function isPrimeNumber Lib "E:\Excel\Excel_Calls_DLL\PrimeFactors.dll" _
(ByVal z As LongLong) As Boolean

Dim primes(0 To 999999) As LongLong

Function isPrime(z As LongLong) As Boolean
    isPrime = isPrimeNumber(z)
End Function

Function primeFacts(z As LongLong)
    Const ADR_ZAHL = "C3"
    Const ROW_FACTORS = 3
    Const COL_FACTORS = 5
    Const MAX_CNT_FAC = 63
    Const WS_PRIME = "Prime Numbers"
    
    Dim arr
    Dim ret(0 To MAX_CNT_FAC, 1 To 1)
    Dim pn() As LongLong
    Dim pf() As LongLong
    Dim n As Long
    Dim k As Long
    Dim i As Long
     
    arr = Worksheets(WS_PRIME).Range("A1:A1000001")
    n = arr(1, 1) - 1
    
    ReDim pn(n)
    For i = 0 To n
        pn(i) = arr(i + 2, 1)
    Next
    
    ReDim pf(MAX_CNT_FAC)
    
    primeFactors z, pn(0), pf(0), n
    
    For i = 0 To MAX_CNT_FAC
        If pf(i) > 0 Then
            ret(i, 1) = pf(i)
        Else
            ret(i, 1) = ""
        End If
    Next
    If pf(0) = -1 Then ret(0, 1) = "OVERFLOW"
    primeFacts = ret
End Function

The 'isPrime' function is entered directly into cell C4 on the 'Primality Check' worksheet as a so-called UDF (User Defined Function): "=isprime(B4)". A direct call of the C++ function 'isPrimeNumber' does not work. The C++ calls must always be made within a VBA function or VBA sub. Therefore the function 'isPrimeNumber' is wrapped by the function 'isPrime'.

In the 'primeFacts' function, the 1 million prime numbers stored on the 'Prime Numbers' spreadsheet should first be loaded into the "pn" array, in order to then transfer them to the C++ function 'primeFactors'. If you were to read 1 million cells in a FOR loop, it would take 5 to 30 seconds, depending on CPU performance. If you assign the entire range A1:A1000001 to the array "arr" (must be of the Variant type), this is done in a fraction of a second. The prime numbers are then transferred from the "arr" array to the "pn" array of the LongLong (64-bit integer) type.

The call of the C++ function is:
primeFactors z, pn(0), pf(0), n
The VBA transfers two pointers with the parameters pf(0) and pn(0), each of which points to the first element of the array - just as C++ expects for arrays.

A special point:
The VBA function 'primeFacts' does not return the array "pf", but the two-dimensional array "ret". Two-dimensional arrays can be assigned directly to a suitable area on the spreadsheet in Excel VBA. The values are then transferred to the corresponding cells at maximum speed - much faster than with a FOR loop. The array "ret" consists of 64 rows and 1 column. The 64 rows are sufficient, because a 64-bit integer cannot have more than 64 prime factors (only the number 2^65 would have the prime factor '2' 65 times).

So the VBA function 'primeFacts' returns 64 values arranged in a column. You can therefore select an area with 64 rows and 1 column (here the area E4:E67) and enter the function "=primeFacts(C4)" as an array function in the editing line. This has the advantage that whenever cell C4 is changed, the new prime factors appear immediately.

IMPORTANT INSTRUCTIONS:

• The full path must always be entered in the "Declare PtrSafe ..." instruction! Relative paths don't work.
• The DLL file can be in any directory. Registration in the Windows registry is not required.
• The "ByVal" must not be forgotten in the parameters!

2.  Create DLL with C# and integrate it into VBA

In the example described here, the generated DLL is registered as a COM component in Windows and integrated in VBA via the references. The functions of the DLL are provided object-oriented as methods of a class. An object of this class is created in VBA, via which the desired methods can then be called.

2.1  Create project with Visual Studio 2022

After clicking on "New project", the window with the available project templates appears:

At the top right you can filter for "C#", "Windows" and "Library".
Now you select the "Class Library (.NET Framework)" project template.

I chose "SearchPrimes" as the project name. Under 'Framework' I accepted the suggested "Framework 4.7.2".

The automatically generated "Class1.cs" file is displayed in the editor.

In the Class1.cs file, the existing code is replaced with the following:

using System.Runtime.InteropServices;

namespace SearchPrimes
{
    [ComVisible(true)]
    [Guid("8FF76343-4D55-4841-A9FA-483E72BF7D22")]
    public class Calc
    {
        public double Quot(double a, double b)
        {
            return a / b;
        }
        public void SearchPrimes(ref object p, long start, long anz)
        {
            long[] arr = (long[])p;
            long i = 0;
            long z = start;
            while (i < anz)
            {
                if (IsPrimeNumber(z))
                {
                    arr[i] = z;
                    i++;
                }
                z++;
            }
        }
        private bool IsPrimeNumber(long z)
        {
            long i;
            if (z < 2) return false;
            if (z == 2) return true;
            if (z % 2 == 0) return false;
            for (i = 3; i * i <= z; i += 2)
            {
                if (z % i == 0) return false;
            }
            return true;
        }
    }
}

The GUID (Globally Unique Identifier) of the 'Calc' class is just an example. Here everyone has to create their own GUID. Visual Studio offers a way to do this:
Menü "Tools ↠ Create GUID".

Under 'GUID Format' select number 5, click 'Copy' and then click 'Exit'. The line of code
"[Guid("8FF76343-4D55-4841-A9FA-483E72BF7D22")]"
is now on the clipboard and can be pasted into the source code.

The GUID is required for the entry of the COM component in the Windows registry.
The line "[ComVisible(true)]" causes the class "Calc" to be visible to VBA.
With the line "using System.Runtime.InteropServices;" the InteropServices are integrated, which automatically complete these steps when the project is created.

 

The first method of the 'Calc' class has the name 'Quot'. It calculates the quotient of the two parameters passed.

The second method called 'SearchPrimes' searches from the number <start> the next <num> prime numbers and returns the found prime numbers in the 'p' parameter.

The 'p' parameter is declared in VBA as an array of 64-bit integers. In order to create a working parameter pass to C#, the corresponding parameter 'p' in the C# method gets the type "reference to an object". In the line "long[] arr = (long[]) p;" then the passed object is converted to the "Array of long" type by the casting operator '(long[])'. In 64-bit C#, the "long" type represents a 64-bit integer, making it compatible with the "LongLong" type in VBA.

The third method named 'IsPrimeNumber' is declared as "Private" and therefore not visible to VBA.

 

Necessary entries in the project properties

To open the project properties, select "Project ↠ SearchPrimes Properties" from the menu. This window appears:

Click "Application" on the left and click "Assembly information..." on the right. Next window:

Click on "Make assembly COM-Visible".

In the 'Project Properties' window, click on "Build" on the left.

Click "Register for COM interop" at the bottom. This causes an automatic registration in the Windows Registry when building the solution.

To prepare for debugging, click on "Debug" in the project properties on the left:

The full path of the "EXCEL.EXE" file must be entered under "Start external program:".
The debugging process is exactly the same as described in Chap. 1.1.

 

Additional remark (can be skipped):

Microsoft recommends on this page, not automatically generating the interface of this COM component. This can be deactivated with the additional line "[ClassInterface(ClassInterfaceType.None)]". Now you can formulate your own interface and have more control over what is visible to the outside of this COM component and what is not.

The code then looks like this, for example:

using System;
using System.Runtime.InteropServices;

namespace SearchPrimes
{
    [Guid("DE67C9DA-D796-456D-B3AA-84C6A15DCC42")]
    [ComVisible(true)]
    public interface ICalc
    {
        double Quot(double a, double b);
        void SearchPrimes(ref object p, long start, long anz);
    }

    [Guid("0633BFDF-F900-4BFA-9E46-3F469887A5D5")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComVisible(true)]
    public class Calc : ICalc
    {
        public double Quot(double a, double b)
        {
            return a / b;
        }
    . . .

2.2  Create Excel file

Paste the following VBA code into a module of the XLSM file:

Option Explicit

Function quo(a, b) As Double
    Dim c As SearchPrimes.Calc
    Set c = New SearchPrimes.Calc
    quo = c.quot(a, b)
End Function

Sub nextPrimes()
    Const COL_OUT = "J"
    Const ROW_OUT_START = 4
    
    Dim c As SearchPrimes.Calc
    Set c = New SearchPrimes.Calc
    Dim i As Long
    Dim start As LongLong
    Dim anz As Long
    Dim p() As LongLong
    
    start = Range("H4")
    anz = Range("I4")
    ReDim p(anz - 1)
    Range(COL_OUT & ":" & COL_OUT).Clear
    
    c.SearchPrimes p, start, anz
    For i = 0 To anz - 1
        Range(COL_OUT & (ROW_OUT_START + i)) = p(i)
    Next
End Sub

The VBA code for the visual design of the prime number output has been omitted.

In the "quo" function at the top, an object of the "Calc" class is created in the first two lines. It was declared here with the name "c". Then the "quot" method of the "Calc" class is called.

In the second VBA function called "nextPrimes" the line
"c.SearchPrimes p, start, num" accessed the DLL.

The name of the array, i.e. "p", is used as the first parameter. As a result, the "SearchPrimes" C# method, which expects an object as the first parameter, receives a pointer that points to the beginning of the array. By converting the variable type using the casting operator "(long[])" in the C# code, it is then clear that the object pointer passed is an array of 64-bit integers.

In cell F4 is the formula "=quo(B4;D4)". This will immediately update the result whenever there is a change in cells B4 and D4.

The update of the prime numbers found is triggered by the change event of the "Sheet1" worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("$H$4:$I$4")) Is Nothing) Then nextPrimes
End Sub

You can see that in the range of larger numbers (here from 500 billion upwards) the prime number density decreases significantly. There are 25 prime numbers between 1 and 100. Above 500 billion there are only 0 to 5 prime numbers in a range of 100 numbers.

• Imprint
• Privacy