Eigene mit Visual Studio 2022 erstellte DLL (64 bit)
in Excel VBA einbinden

Tutorial

 

Einleitung

In diesem Tutorial sollen zwei ganz unterschiedliche Wege beschrieben werden, eine mit Visual Studio 2022 selbst erstellte 64-bit-DLL in Excel VBA einzubinden. Die Beschreibung passt auch für Visual Studio 2019 und 2015. Im ersten Kapitel handelt es sich um eine mit C++ erstellte DLL, im zweiten Kapitel um eine mit C# erstellte DLL. Ein besonderes Augenmerk wird in beiden Fällen auf die Parameterübergabe von Arrays gelegt.

Im ersten Fall (C++) genügt es, die DLL in dasselbe Verzeichnis zu kopieren, in der die Excel-Datei liegt. Im zweiten Fall (C#) wird die DLL in der Windows-Registry registriert (als sog. COM-Komponente), so dass sie in Excel VBA unter "Extras/Verweise" erscheint und durch Anklicken eingebunden wird.

Anleitungen zu diesem Thema gibt es relativ viele, jedoch sind die meisten bereits mehr als 10 Jahre alt und manches ist nicht mehr so kompliziert wie damals. Zum Beispiel muss die Registrierung als COM-Komponente nicht mehr mit "regasm.exe" von einer Kommandozeile aus durchgeführt werden, sondern Visual Studio erledigt dies automatisch mit. Ein Klick an der richtigen Stelle genügt. Brauchbare Hinweise zur Parameterübergabe von Arrays sind zudem nur schwer zu finden.

Download

1.  DLL mit C++ erstellen und in Excel VBA einbinden

Gründe, eine bestimmte Funktionalität in C++ auszulagern, gibt es mehrere. Einer davon ist der Geschwindigkeitsvorteil. In diesem Beispiel geht es darum, die Primfaktorzerlegung einer Zahl zu berechnen.

Die Sicherheit der Verschlüsselungsverfahren auf der ganzen Welt beruht darauf, dass bisher jeder Supercomputer Jahrzehnte oder Jahrhunderte für die Primfaktorzerlegung bräuchte, wenn die Zahl selbst groß genug gewählt wird (mehrere hundert Stellen) und sich nur aus zwei ebenfalls sehr großen Primfaktoren zusammensetzt. Würde man ein effizientes Verfahren entdecken, die beiden großen Primfaktoren in kürzerer Zeit zu berechnen, wäre das der Super-GAU für alle verschlüsselten Daten auf der Welt.

In Excel werden maximal 15 Stellen einer Ganzzahl auf dem Bildschirm dargestellt. Die Primfaktorzerlegung einer solchen fünfzehnstelligen Zahl erledigt C++ in Sekundenschnelle.

1.1  Projekt in Visual Studio 2022 erstellen

Nach dem Klick auf "Neues Projekt" erscheint das Fenster mit den angebotenen Projektvorlagen:

Rechts oben kann man nach "C++", "Windows" und "Bibliothek" filtern.
Man wählt die Projektvorlage "Dynamic Link Library (DLL)". Als Projektnamen habe ich "PrimeFactors" gewählt.

Im Editor wird die automatisch erzeugte Datei "dllmain.cpp" angezeigt.

Man fügt nun drei Dateien hinzu:

• primefact.h
• primefact.cpp
• primefact.def (im Ordner 'Quelldateien')

Die Datei 'primefact.h'

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

Die Datei 'primefact.def'

LIBRARY PrimeFactors
EXPORTS
primeFactors
isPrimeNumber

Die Datei 'primefact.def' sorgt dafür, dass die Einspringpunkte für die exportierten Funktionen 'primeFactors' und 'isPrimeNumber' in die DLL-Datei eingefügt werden.

Auf dieser Seite wird von Microsoft darauf hingewiesen, dass man bei Verwendung von "__declspec(dllexport)" keine DEF-Datei braucht:


In diesem Fall entspricht das nicht meiner Erfahrung. Ohne DEF-Datei funktioniert es nicht. Dagegen kann ich auf den Zusatz "__declspec(dllexport)" verzichten. Auch der Zusatz "__stdcall" und das "#include <windows.h>" der 32-bit-DLLs entfällt bei 64-bit-DLLs.

Die Datei '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;
}

Der Funktion 'primeFactors' wird die zu zerlegende Zahl 'z' (64 bit Ganzzahl) übergeben sowie das Array 'pn' mit den ersten 1000000 (1 Mio.) Primzahlen und eine Zahl n, die die Länge des Arrays 'pn' angibt. In dem Array 'pf' wird dann die Liste der berechneten Primfaktoren zurückgegeben. Die beiden Array-Parameter erwarten einen Pointer auf das erste Array-Element (siehe Excel-VBA-Code).

Die Liste der Primzahlen ist in der aufrufenden Excel-Datei dauerhaft gespeichert. Sie wurde mit der in Kapitel 2 vorgestellten C#-Funktion 'searchPrimes' erstellt. Die größte gespeicherte Primzahl ist 15.485.863. Sollte bei der Primfaktorzerlegung ein noch größerer Primfaktor auftreten, wird 'pf[0]' auf -1 gesetzt und in der Excel-Datei erscheint der Text 'OVERFLOW'.

Eintragung der DEF-Datei in die Projekt-Eigenschaften

Damit die DEF-Datei verwendet wird, muss der Dateiname in den Projekt-Eigenschaften eingetragen sein. Dazu die Projekt-Eigenschaften öffnen - z. B. Menü "Projekt ↠ PrimeFactors-Eigenschaften".
Es erscheint dieses Fenster:

Links "Linker ↠ Eingabe" auswählen, rechts unter "Moduldefinitionsdatei" den Dateinamen "primefact.def" eintragen.

ACHTUNG:  Wenn man ein Release erstellt, muss die Moduldefinitionsdatei ebenfalls für den Release-Modus eingetragen werden! Links oben bei "Konfiguration" den Release-Modus auswählen und rechts bei "Moduldefinitionsdatei" den Dateinamen "primefact.def" eintragen.

Eintragung für das Debuggen

Im Fenster 'Projekt-Eigenschaften' links auf "Debugging" klicken und rechts oben auf "Befehl". Mit einem Klick auf die Schaltfläche am rechten Ende und auf "Durchsuchen" kann man zum Ordner navigieren, in dem die Excel-Anwendungsdatei 'EXCEL.EXE' gespeichert ist. Hier muss der vollständige Pfad eingetragen sein.

Das Debuggen funktioniert so, dass man die Excel-Datei schließt, in der CPP-Datei einen Haltepunkt setzt und im Menü "Debuggen ↠ Debuggen starten" wählt. Visual Studio startet jetzt Excel. Dort öffnet man die Datei "Primfaktoren.xlsm" und löst einen Aufruf der DLL-Datei aus - zum Beispiel durch Ändern der Zelle C4 auf dem Tabellenblatt 'Primfaktorzerlegung'. Der Visual Studio Editor kommt automatisch in den Vordergrund und die Ausführung der DLL stoppt am Haltepunkt.

Erstellen einer 64-bit-DLL

Ganz wichtig ist es, vor dem Erstellen der Projektmappe oben unter der Menüzeile das "x86" in "x64" zu ändern:

Vergisst man dies - so wie ich anfangs - kann einen dies zur Verzweiflung treiben. Denn man bekommt den Laufzeitfehler 48:


Dieser hat den irreführenden Text "Datei nicht gefunden". So kann es passieren, dass man tagelang verschiedene Dateispeicherorte und Registrierung in der Registry ausprobiert - ohne Erfolg. Bis man irgendwann auf die Idee kommt nachzuschauen, was dieser Laufzeitfehler eigentlich bedeutet. Und eine dieser Bedeutungen kann sein, dass die Bit-Version nicht übereinstimmt.

Löscht man probehalber die DLL-Datei, so erhält man den Laufzeitfehler 53:


Dieser zeigt ebenfalls den Text "Datei nicht gefunden" an. Nur weist der Text diesmal in die richtige Richtung.

1.2  Excel-Datei erstellen

In einen Modul der XLSM-Datei fügt man den folgenden VBA-Code ein:

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 = "Primzahlen"
    
    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

Die Funktion 'isPrime' wird auf dem Tabellenblatt 'Primzahlprüfung' als sog. UDF (User Defined Function) direkt in die Zelle C4 eingegeben: "=isprime(B4)". Ein direkter Aufruf der C++ Funktion 'isPrimeNumber' funktioniert nicht. Die C++ Aufrufe müssen immer innerhalb einer VBA-Function oder VBA-Sub erfolgen. Deshalb ist die Function 'isPrimeNumber' durch die Function 'isPrime' gewrappt.

In der Function 'primeFacts' sollen zunächst die auf dem Tabellenblatt 'Primzahlen' gespeicherten 1 Million Primzahlen in das Array "pn" geladen werden, um sie dann an die C++ Funktion 'primeFactors' zu übergeben. Würde man in einer FOR-Schleife 1 Million Zellen auslesen, würde das je nach CPU-Performance 5 bis 30 sec dauern. Weist man den gesamten Bereich A1:A1000001 dem Array "arr" (muss vom Typ Variant sein) zu, ist dies im Bruchteil einer Sekunde erledigt. Vom Array "arr" werden die Primzahlen dann ins Array "pn" vom Typ LongLong (64 bit Ganzzahl) übertragen.

Der Aufruf der C++ Funktion lautet:
primeFactors z, pn(0), pf(0), n
Dabei übergibt das VBA mit den Parametern pf(0) und pn(0) zwei Zeiger (pointer), die jeweils auf das erste Element des Arrays zeigen - so wie es C++ bei Arrays erwartet.

Eine Besonderheit:
Die VBA-Funktion 'primeFacts' gibt nicht das Array "pf" zurück, sondern das zweidimensionale Array "ret". Zweidimensionale Arrays können in Excel VBA direkt einem passenden Bereich auf dem Tabellenblatt zugewiesen werden. Die Werte werden dann mit maximaler Geschwindigkeit in die entsprechenden Zellen übertragen - ungleich viel schneller als durch eine FOR-Schleife. Das Array "ret" besteht sozusagen aus 64 Zeilen und 1 Spalte. Die 64 Zeilen reichen aus, denn bei einer 64-bit-Ganzzahl können nicht mehr als 64 Primfaktoren auftreten (erst die Zahl 2^65 hätte 65 mal den Primfaktor 2).

Die VBA-Funktion 'primeFacts' gibt also 64 Werte in einer Spalte angeordnet zurück. Deshalb kann man einen Bereich mit 64 Zeilen und 1 Spalte selektieren (hier der Bereich E4:E67) und die Funktion "=primeFacts(C4)" als Matrixfunktion in die Bearbeitungszeile eintragen. Das hat den Vorteil, dass bei jeder Änderung der Zelle C4 sofort die neuen Primfaktoren erscheinen.

WICHTIGE HINWEISE:

• In der Anweisung "Declare PtrSafe ..." muss immer der vollständige Pfad eingetragen sein! Relative Pfade funktionieren nicht.
• Die DLL-Datei kann sich in einem beliebigen Verzeichnis befinden. Eine Registrierung in der Windows-Registry ist nicht erforderlich.
• Bei den Parametern darf das "ByVal" nicht vergessen werden!

2.  DLL mit C# erstellen und in VBA einbinden

Bei dem hier beschriebenen Beispiel wird die erzeugte DLL als COM-Komponente in Windows registriert und über die Verweise in VBA integriert. Die Funktionen der DLL werden objektorientiert als Methoden einer Klasse bereitgestellt. In VBA wird ein Objekt dieser Klasse erzeugt, über das die gewünschten Methoden dann aufrufbar sind.

2.1  Projekt mit Visual Studio 2022 erstellen

Nach dem Klick auf "Neues Projekt" erscheint das Fenster mit den angebotenen Projektvorlagen:

Rechts oben kann man nach "C#", "Windows" und "Bibliothek" filtern.
Man wählt nun die Projektvorlage "Klassenbibliothek (.NET Framework)".

Als Projektnamen habe ich "SearchPrimes" gewählt. Unter 'Framework' habe ich das vorgeschlagene "Framework 4.7.2" übernommen.

Im Editor wird die automatisch erzeugte Datei "Class1.cs" angezeigt.

In der Datei 'Class1.cs' wird der vorhandene Code durch den folgenden ersetzt:

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;
        }
    }
}

Der GUID (Globally Unique Identifier) der Klasse 'Calc' ist nur ein Beispiel. Hier muss jeder seinen eigenen GUID erzeugen. Visual Studio bietet dazu eine Möglichkeit an:
Menü "Extras ↠ GUID erstellen".

Unter 'GUID-Format' die Nummer 5 auswählen, auf 'Kopieren' und anschließend auf 'Beenden' klicken. Die Code-Zeile
"[Guid("8FF76343-4D55-4841-A9FA-483E72BF7D22")]"
befindet sich nun in der Zwischenablage und kann in den Quellcode eingefügt werden.

Der GUID wird für den Eintrag der COM-Komponente in die Windows-Registry benötigt.
Die Zeile "[ComVisible(true)]" bewirkt, dass die Klasse "Calc" für VBA sichtbar ist.
Mit der Zeile "using System.Runtime.InteropServices;" werden die InteropServices eingebunden, die diese Schritte beim Erstellen des Projektes automatisch erledigen.

 

Die erste Methode der Klasse 'Calc' hat den Namen 'Quot'. Sie berechnet den Quotienten der beiden übergebenen Parameter.

Die zweite Methode mit dem Namen 'SearchPrimes' sucht ab der Zahl <start> die nächsten <anz> Primzahlen und gibt die gefundenen Primzahlen im Parameter 'p' zurück.

Der Parameter 'p' ist in VBA als Array von 64-bit-Ganzzahlen deklariert. Um eine funktionierende Parameterübergabe zu C# zu schaffen, bekommt der entsprechende Parameter 'p' in der C#-Methode den Typ "Referenz auf ein Objekt". In der Zeile "long[] arr = (long[]) p;" wird dann das übergebene Objekt durch den Casting-Operator '(long[])' in den Typ "Array of long" konvertiert. In 64-bit-C# repräsentiert der Typ "long" eine 64-bit-Ganzzahl und ist damit kompatibel zum Typ "LongLong" in VBA.

Die dritte Methode mit dem Namen 'IsPrimeNumber' ist als "Private" deklariert und deshalb für VBA nicht sichtbar.

 

Notwendige Einträge in die Projekt-Eigenschaften

Zum Öffnen der Projekt-Eigenschften im Menü "Projekt ↠ SearchPrimes-Eigenschaften" auswählen. Es erscheint dieses Fenster:

Links auf "Anwendung" und rechts auf "Assemblyinformationen" klicken. Nächstes Fenster:

Auf "Assembly COM-sichtbar machen" klicken.

Im Fenster 'Projekt-Eigenschaften' nun links auf "Build" klicken.

Unten auf "Für COM-Interop registrieren" klicken. Dies bewirkt eine automatische Registrierung in der Windows Registry beim Erstellen des Projekts.

Zur Vorbereitung des Debuggens in den Projekt-Eigenschaften links auf "Debuggen" klicken:

Unter "Externes Programm starten" muss der vollständige Pfad der Datei "EXCEL.EXE" eingetragen sein.
Der Ablauf des Debuggings ist genau so wie in Kap. 1.1 beschrieben.

 

Zusatzbemerkung (kann übersprungen werden):

Microsoft empfiehlt auf dieser Seite, das Interface dieser COM-Komponente nicht automatisch generieren zu lassen. Mit der zusätzlichen Zeile "[ClassInterface(ClassInterfaceType.None)]" kann dies deaktiviert werden. Nun kann man sein eigenes Interface formulieren und hat mehr Kontrolle darüber, was von dieser COM-Komponente nach außen sichtbar ist und was nicht.

Der Code sieht dann zum Beispiel so aus:

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  Excel-Datei erstellen

In einen Modul der XLSM-Datei fügt man den folgenden VBA-Code ein:

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

Der VBA-Code für die optische Gestaltung der Primzahl-Ausgabe wurde weggelassen.

In der Funktion "quo" ganz oben wird in den ersten beiden Zeilen zunächst ein Objekt der Klasse "Calc" erstellt. Es wurde hier mit dem Namen "c" deklariert. Anschließend wird die Methode "quot" der Klasse "Calc" aufgerufen.

In der zweiten VBA-Funktion namens "nextPrimes" wird mit der Zeile
"c.SearchPrimes p, start, anz" auf die DLL zugegriffen.

Als erster Parameter wird der Name des Arrays, also "p", eingesetzt. Dadurch bekommt die C#-Methode "SearchPrimes", die als ersten Parameter ein Objekt erwartet, einen Zeiger (pointer) übergeben, der an den Anfang des Arrays zeigt. Durch die Konvertierung des Variablen-Typs mit Hilfe des Casting-Operators "(long[])" im C#-Code ist dann geklärt, dass es sich bei dem übergebenen Objekt-Pointer um ein Array von 64-bit-Ganzzahlen handelt.

In der Zelle F4 steht die Formel "=quo(B4;D4)". Dadurch wird bei jeder Änderung in den Zellen B4 und D4 das Ergebnis sofort aktualisiert.

Die Aktualisierung der gefundenen Primzahlen wird durch das Change-Ereignis des Worksheets "Tabelle1" ausgelöst:

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

Man sieht, dass im Bereich größerer Zahlen (hier ab 500 Billionen aufwärts) die Primzahldichte deutlich abnimmt.
Zwischen 1 und 100 findet man noch 25 Primzahlen. Oberhalb von 500 Billionen findet man in einer Spanne von 100 Zahlen nur noch 0 bis 5 Primzahlen.