Reguläre Ausdrücke in VBA, Teil 2: Praxis-Rezepte

Im ersten Teil dieser Reihe haben wir das RegExp-Objekt eingebunden, seine vier Klassen kennengelernt und die Mustersprache durchgearbeitet. Nun wird es konkret. Dieser Beitrag liefert fertige Muster für die Prüfung von E-Mail-Adressen, Telefonnummern, Postleitzahlen und Kfz-Kennzeichen, dazu Funktionen zum Herauslösen von Daten aus Freitextfeldern, zum Bereinigen kopierter Texte und zum Füllen von Textvorlagen. Alle Beispiele setzen die drei Hilfsfunktionen aus dem ersten Teil voraus – und am Ende zeigen wir eine Falle, die eine Access-Anwendung zuverlässig zum Stillstand bringt.

Muster gehören in Konstanten

Bevor wir loslegen, eine organisatorische Empfehlung: Legen Sie ein Standardmodul an, beispielsweise namens mdlRegExPatterns, in dem alle Muster als Konstanten stehen (siehe Listing 1). Der Grund ist weniger die Schreibarbeit als die Wartung – wenn sich herausstellt, dass Ihr Muster für Telefonnummern eine Schreibweise nicht erkennt, wollen Sie das an genau einer Stelle korrigieren und nicht an mehreren anderen Stellen.

Option Compare Database
Option Explicit
Public Const cstrRegExEMail As String = _
    "^[A-Za-z0-9_%+\-]+(?:\.[A-Za-z0-9_%+\-]+)*" & _
    "@[A-Za-z0-9](?:[A-Za-z0-9\-]*[A-Za-z0-9])?" & _
    "(?:\.[A-Za-z0-9](?:[A-Za-z0-9\-]*[A-Za-z0-9])?)+$"
Public Const cstrRegExPlzDe As String = "^\d{5}$"
Public Const cstrRegExIban As String = _
    "^[A-Z]{2}\d{2}[A-Z0-9]{11,30}$"
Public Const cstrRegExKennzeichen As String = _
    "^[A-ZÄÖÜ]{1,3}-[A-ZÄÖÜ]{1,2} ?\d{1,4}[EH]?$"
Public Const cstrRegExUrl As String = _
    "^https?://[A-Za-z0-9]" & _
    "(?:[A-Za-z0-9\-]*[A-Za-z0-9])?" & _
    "(?:\.[A-Za-z0-9]" & _
    "(?:[A-Za-z0-9\-]*[A-Za-z0-9])?)+" & _
    "(?::\d{1,5})?(?:[/#?][^\s]*)?$"

Listing 1: Die Muster gesammelt an einer Stelle

Beachten Sie den maskierten Bindestrich in den Zeichenklassen. Innerhalb eckiger Klammern hat der Bindestrich die Bedeutung eines Bereichs, weshalb wir ihm mit einem vorangestellten Schrägstrich diese Rolle nehmen. Alternativ dürfen Sie ihn auch unmaskiert an die letzte Position der Klasse setzen.

Die Muster prüfen jeweils nur den formalen Aufbau. Bei einer IBAN werden weder die länderspezifische Länge noch die Prüfziffer kontrolliert. Auch das Kennzeichenmuster deckt bewusst nur die üblichen deutschen Standard-, Elektro- und Historienkennzeichen ab. Saison-, Wechsel- und Sonderkennzeichen benötigen eigene Erweiterungen.

Rezept 1: E-Mail-Adressen prüfen

Vorweg eine Einordnung, die Ihnen später Ärger erspart: Ein regulärer Ausdruck, der wirklich jede nach den Internetstandards zulässige E-Mail-Adresse korrekt beurteilt, ist mehrere tausend Zeichen lang und in der Praxis kaum sinnvoll einzusetzen.

Ein pragmatisches Muster ist deshalb meist die bessere Wahl: Es fängt grobe Eingabefehler wie ein fehlendes At-Zeichen oder einen vergessenen Punkt in der Domain ab, bleibt aber verständlich und wartbar.

Was ein Muster grundsätzlich nicht leisten kann, ist die Prüfung, ob das Postfach tatsächlich existiert. Verlässlich klären lässt sich das nur über eine Bestätigungsnachricht.

Mit dieser Einschränkung im Hinterkopf eignet sich das Muster aus cstrRegExEMail gut als Plausibilitätsprüfung:

Debug.Print RegExTest("andre@access-im-unternehmen.de", _
    cstrRegExEMail)
Debug.Print RegExTest("andre@@example.de", cstrRegExEMail)
Debug.Print RegExTest("andre.example.de", cstrRegExEMail)

Die Ausgabe im Direktbereich:

Wahr
Falsch
Falsch

In einem Formular setzen Sie die Prüfung sinnvollerweise im Ereignis Vor Aktualisierung des Textfelds ein. Wichtig ist die vorherige Prüfung auf einen leeren Wert: Ist die Eingabe optional, soll ein leergelassenes Feld nicht als ungültige E-Mail-Adresse beanstandet werden (siehe Listing 2).

Private Sub txtEMail_BeforeUpdate(Cancel As Integer)
    If Len(Nz(Me.txtEMail, "")) = 0 Then Exit Sub
    If Not RegExTest(Me.txtEMail, cstrRegExEMail) Then
        MsgBox "Bitte geben Sie eine gültige E-Mail-Adresse ein.", vbExclamation
        Cancel = True
    End If
End Sub

Listing 2: Prüfung der E-Mail vor Aktualisierung des Steuerelements

Eine Einschränkung noch: In der Gültigkeitsregel eines Tabellenfelds lässt sich diese Prüfung nicht unterbringen, weil dort keine selbst geschriebenen Funktionen erlaubt sind. Sie gehört daher ins Formular – oder in eine Auswahlabfrage, mit der Sie den Bestand nachträglich auf fehlerhafte Werte untersuchen.

Rezept 2: Telefonnummern prüfen und vereinheitlichen

Telefonnummern sind der Klassiker unter den unsauberen Daten. In einer gewachsenen Tabelle finden sich Schreibweisen wie 0203/1234567, + 203 1234567, (0203) 12 34 56 7 und 0203-1234567 nebeneinander. Ein Muster, das alle diese Varianten erkennt und gleichzeitig Unsinn zurückweist, wird schnell unübersichtlich.

Der praxistauglichere Weg besteht aus zwei Schritten: Zuerst prüfen wir grob auf erlaubte Zeichen, dann zählen wir die Ziffern. Das ist robuster als ein einzelnes Monstermuster und deutlich leichter zu warten (siehe Listing 3).

Public Function IstTelefonnummer(ByVal strNummer As String) As Boolean
    Dim strZiffern As String
    ' Nur erlaubte Zeichen?
    If Not RegExTest(strNummer, "^[\d \-/\(\)\+]+$") Then Exit Function
    ' Wie viele Ziffern bleiben übrig?
    strZiffern = RegExErsetzen(strNummer, "\D", "")
    IstTelefonnummer = (Len(strZiffern) >= 6) And (Len(strZiffern) <= 15)
End Function

Listing 3: Zweistufige Prüfung einer Telefonnummer

Die zweite Zeile der Funktion ist gleich noch für etwas anderes gut. Der Ausdruck \D trifft alles, was keine Ziffer ist – und weil wir ihn durch einen Leerstring ersetzen, bleiben genau die Ziffern übrig.

Auf dieser Idee baut die Vereinheitlichung auf, mit der wir alle Nummern in das internationale Format überführen (siehe Listing 4).

Public Function TelefonNormalisieren(ByVal strNummer As String, Optional ByVal strLand As String = "") As String
    Dim strZiffern As String
    strZiffern = RegExErsetzen(strNummer, "\D", "")
    If Left(strZiffern, 2) = "00" Then
        ' 00203... -> 203...
        strZiffern = Mid(strZiffern, 3)
    ElseIf Left(strZiffern, 1) = "0" Then
        ' 0203... -> 203...
        strZiffern = strLand & Mid(strZiffern, 2)
    End If
    TelefonNormalisieren = "+" & strZiffern
End Function

Listing 4: Umwandlung in das internationale Format

Ein Testlauf zeigt, dass die vier eingangs genannten Schreibweisen alle beim gleichen Ergebnis landen. Hier die Aufrufe:

Debug.Print TelefonNormalisieren("0203/1234567")
Debug.Print TelefonNormalisieren("+ 203 1234567")
Debug.Print TelefonNormalisieren("(0203) 12 34 56 7")
Debug.Print TelefonNormalisieren("0203-1234567")

Das Ergebnis lautet einheitlich:

+2031234567
+2031234567
+2031234567
+2031234567

Genau das brauchen Sie, wenn Sie Dubletten finden oder Nummern an eine Telefonanlage übergeben wollen. Die Funktion setzt allerdings voraus, dass nationale Nummern mit einer führenden Null und internationale Nummern mit + oder 00 gespeichert sind. Eine Nummer wie 2031234567 lässt sich ohne weitere Information keinem Land sicher zuordnen. Als Aktualisierungsabfrage über einen entsprechend geprüften Bestand räumt die Funktion in einem Durchgang auf.

Rezept 3: Daten aus Freitext herauslösen

Häufiger als das Prüfen ist das Extrahieren. In fast jeder gewachsenen Datenbank gibt es ein Bemerkungsfeld, in dem über Jahre Informationen gelandet sind, die eigentlich in eigene Felder gehören. Mit RegExGruppe aus dem ersten Teil holen wir sie heraus.

Der Klassiker ist die Adresszeile, in der Postleitzahl und Ort zusammenstehen. Das Muster trennt beides und liefert je nach Parameter den einen oder den anderen Teil:

Public Function PlzAusAdresse( _
        ByVal strZeile As String) As String
    PlzAusAdresse = RegExGruppe(strZeile, _
        "\b(\d{5})\s+(.+)$", 1)
End Function
Public Function OrtAusAdresse( _
        ByVal strZeile As String) As String
    OrtAusAdresse = RegExGruppe(strZeile, _
        "\b(\d{5})\s+(.+)$", 2)
End Function

Ebenso oft gesucht: der Betrag in einem Text. Deutsche Beträge sind mit ihrem Punkt als Tausendertrennzeichen und dem Komma als Dezimaltrenner etwas eigenwillig, aber gut zu fassen. Der erste Teil des Musters beschreibt die Variante mit Tausenderpunkten, der zweite die ohne (siehe Listing 5).

Public Function BetragAusText(ByVal strText As String) As Variant
    Dim strTreffer As String
    strTreffer = RegExGruppe(strText, "\d{1,3}(?:\.\d{3})+,\d{2}|\d+,\d{2}", 0)
    If Len(strTreffer) = 0 Then
        BetragAusText = Null
    Else
        BetragAusText = CCur(strTreffer)
    End If
End Function

Listing 5: Herauslösen eines Geldbetrags

Der Aufruf mit dem Text Rechnungsbetrag 1.234,50 Euro netto liefert den Währungswert 1234,5 zurück. Beachten Sie die Reihenfolge der beiden Alternativen: Die Suchmaschine nimmt die erste, die passt – stünde die einfache Variante vorn, bekämen wir bei 1.234,50 nur die 234,50 zu sehen. Diese Reihenfolge ist bei Alternativen fast immer entscheidend, und der Fehler ist tückisch, weil das Ergebnis ja plausibel aussieht.

Bis hierhin haben wir immer nur den ersten Treffer geholt. Oft brauchen wir aber alle: sämtliche E-Mail-Adressen in einem Verteiler, alle Rechnungsnummern in einer Bemerkung, jeden Platzhalter einer Textvorlage. Dafür lohnt sich eine vierte Hilfsfunktion namens RegExAlleTreffer, die wir zu den dreien aus dem ersten Teil stellen (siehe Listing 6). Sie folgt demselben Bauprinzip und erwartet diese Parameter:

Public Function RegExAlleTreffer(ByVal strText As String, ByVal strMuster As String, _
       Optional ByVal strTrenner As String = "; ") As String
    Static objRegEx As Object
    Dim objTreffer As Object
    Dim objMatch As Object
    Dim strErgebnis As String
    If objRegEx Is Nothing Then
        Set objRegEx = CreateObject("VBScript.RegExp")
    End If
    objRegEx.Pattern = strMuster
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    Set objTreffer = objRegEx.Execute(strText)
    For Each objMatch In objTreffer
        If Len(strErgebnis) > 0 Then
            strErgebnis = strErgebnis & strTrenner
        End If
        strErgebnis = strErgebnis & objMatch.Value
    Next objMatch
    RegExAlleTreffer = strErgebnis
End Function

Listing 6: Alle Fundstellen als Zeichenkette

• strText: der zu durchsuchende Text.
• strMuster: der reguläre Ausdruck.
• strTrenner: optional, Vorgabewert ist ein Semikolon mit Leerzeichen. Damit werden die gefundenen Stellen zu einer Zeichenkette verkettet.

Die Eigenschaft Global steht hier zwingend auf True – ohne sie liefert die Suche genau einen Treffer, und die ganze Funktion wäre sinnlos. Weil das Ergebnis eine einfache Zeichenkette ist, lässt sich die Funktion unmittelbar in der Feldliste einer Abfrage einsetzen.

Ein durchgehendes Beispiel: die Verteilerliste

Nehmen wir eine Aufgabe, die in der Praxis regelmäßig anfällt. Ein Mitarbeiter kopiert den Verteiler einer E-Mail in das Bemerkungsfeld eines Kundendatensatzes. Was dort ankommt, ist alles andere als einheitlich – mal mit Namen und spitzen Klammern, mal mit runden Klammern, getrennt durch Semikola, Kommata oder Zeilenumbrüche:

Dim strVerteiler As String
strVerteiler = _
    "Anna Meier <a.meier@example.de>; " & _
    "Bernd Schulz (b.schulz@example.de), " & vbCrLf & _
    "c.mueller@example.de;" & vbCrLf & _
    "Dieter Weiß <d.weiss@beispiel.at>"

Für die Suche brauchen wir eine zweite Variante unseres E-Mail-Musters. Das Muster von weiter oben trägt die beiden Anker und beschreibt deshalb einen Text, der ausschließlich aus einer Adresse besteht.

Zum Herauslösen aus einem längeren Text müssen die Anker weg:

Public Const cstrRegExAdresse As String = _
    "[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}"

Das ist ein Punkt, an dem viele hängen bleiben, deshalb sei er ausdrücklich festgehalten: Prüfen und Extrahieren brauchen unterschiedliche Muster. Beim Prüfen wollen wir wissen, ob der gesamte Text eine Adresse ist – beim Extrahieren suchen wir Adressen irgendwo darin. Verwenden Sie versehentlich das verankerte Muster zum Suchen, finden Sie nie etwas; verwenden Sie das ankerlose Muster zum Prüfen, geht jeder Unsinn durch, der irgendwo ein Klammeraffe enthält.

Der Aufruf liefert dann eine saubere Liste:

Debug.Print RegExAlleTreffer(strVerteiler, _
    cstrRegExAdresse)
a.meier@example.de; b.schulz@example.de;
c.mueller@example.de; d.weiss@beispiel.at

Bemerkenswert ist, was die Funktion alles ignoriert hat: die Namen davor, die spitzen und runden Klammern, die unterschiedlichen Trennzeichen und die Zeilenumbrüche. Genau das ist die Stärke regulärer Ausdrücke gegenüber einer selbstgeschriebenen Zerlegung – wir mussten kein einziges dieser Sonderzeichen vorhersehen.

Von der Zeichenkette zum Array

Für die Anzeige ist eine verkettete Zeichenkette ideal, für die Weiterverarbeitung nicht. Wenn wir jede Adresse einzeln in eine Tabelle schreiben oder als Empfänger an eine E-Mail hängen wollen, brauchen wir ein Array. Die Variante aus Listing 7 liefert genau das.

Public Function RegExTrefferArray(ByVal strText As String, ByVal strMuster As String) As Variant
    Static objRegEx As Object
    Dim objTreffer As Object
    Dim strErgebnis() As String
    Dim i As Long
    If objRegEx Is Nothing Then
        Set objRegEx = CreateObject("VBScript.RegExp")
    End If
    objRegEx.Pattern = strMuster
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    Set objTreffer = objRegEx.Execute(strText)
    ' Kein Treffer: leeres Array zurueckgeben
    If objTreffer.Count = 0 Then
        RegExTrefferArray = Array()
        Exit Function
    End If
    ReDim strErgebnis(objTreffer.Count - 1)
    For i = 0 To objTreffer.Count - 1
        strErgebnis(i) = objTreffer(i).Value
    Next i
    RegExTrefferArray = strErgebnis
End Function

Listing 7: Die Trefferliste als Array

Der Rückgabewert im Fall ohne Treffer verdient Beachtung. Wir liefern mit Array() ein leeres Array zurück, dessen obere Grenze bei -1 liegt. Eine anschließende Schleife von 0 bis UBound läuft damit schlicht null Mal durch – ohne dass der Aufrufer den Sonderfall abfangen müsste. Hätten wir stattdessen Null oder ein nicht dimensioniertes Array zurückgegeben, bräuchte jeder Aufruf eine zusätzliche Prüfung.

Die Adressen in eine Tabelle übernehmen

Nun setzen wir alles zusammen. Die folgende Funktion durchläuft alle Kunden mit einem gefüllten Bemerkungsfeld, holt daraus sämtliche Adressen und schreibt sie in eine Tabelle tblEMailAdressen mit den Feldern KundeID und EMail. Dubletten überspringt sie (siehe Listing 8).

Public Function AdressenUebernehmen() As Long
    Dim db As DAO.Database
    Dim rstQuelle As DAO.Recordset
    Dim rstZiel As DAO.Recordset
    Dim objBekannt As Object
    Dim varTreffer As Variant
    Dim strAdresse As String
    Dim lngNeu As Long
    Dim i As Long
    Set db = CurrentDb
    Set objBekannt = CreateObject("Scripting.Dictionary")
    objBekannt.CompareMode = 1
    Set rstQuelle = db.OpenRecordset("SELECT KundeID, Notiz FROM tblKunden " & "WHERE Notiz Is Not Null")
    Set rstZiel = db.OpenRecordset("tblEMailAdressen", dbOpenDynaset, dbAppendOnly)
    Do While Not rstQuelle.EOF
        varTreffer = RegExTrefferArray(rstQuelle!Notiz, cstrRegExAdresse)
        For i = 0 To UBound(varTreffer)
            strAdresse = LCase(varTreffer(i))
            If Not objBekannt.Exists(strAdresse) Then
                objBekannt.Add strAdresse, True
                rstZiel.AddNew
                rstZiel!KundeID = rstQuelle!KundeID
                rstZiel!EMail = strAdresse
                rstZiel.Update
                lngNeu = lngNeu + 1
            End If
        Next i
        rstQuelle.MoveNext
    Loop
    rstZiel.Close
    rstQuelle.Close
    Set rstZiel = Nothing
    Set rstQuelle = Nothing
    AdressenUebernehmen = lngNeu
End Function

Listing 8: Adressen aus Freitext in eine eigene Tabelle überführen

Für drei Details lohnt sich ein zweiter Blick. Die Zuweisung CompareMode = 1 stellt das Dictionary auf einen Vergleich um, der Groß- und Kleinschreibung ignoriert – zusammen mit dem LCase beim Einlesen erwischen wir damit auch Adressen, die in unterschiedlicher Schreibweise vorliegen. Beachten Sie außerdem, dass CompareMode gesetzt sein muss, bevor der erste Eintrag hinzugefügt wird.

Zweitens wirkt das Dictionary über den gesamten Durchlauf. Eine Adresse, die bei zwei Kunden auftaucht, landet also nur einmal in der Zieltabelle – beim erstgefundenen Kunden. Soll die Dublettenprüfung stattdessen nur innerhalb eines Kunden greifen, rufen Sie zu Beginn jedes Schleifendurchlaufs objBekannt.RemoveAll auf.

Und drittens ein beruhigender Hinweis zum Scripting.Dictionary: Es stammt aus der Bibliothek scrrun.dll und ist von der im ersten Teil beschriebenen Abkündigung von VBScript nicht betroffen. Sie dürfen es also bedenkenlos weiterverwenden.

Nur zählen statt sammeln

Manchmal interessiert uns gar nicht, was gefunden wurde, sondern nur, wie oft. Wie viele Adressen stecken in einer Bemerkung? Wie viele Platzhalter enthält eine Vorlage? Dafür genügt ein Einzeiler, der auf dem Array aufsetzt:

Public Function RegExAnzahl( _
        ByVal strText As String, _
        ByVal strMuster As String) As Long
    RegExAnzahl = UBound( _
        RegExTrefferArray(strText, strMuster)) + 1
End Function

Weil das leere Array die obere Grenze -1 hat, liefert die Funktion im Fall ohne Treffer sauber den Wert 0 zurück – ganz ohne Sonderbehandlung.

Damit lässt sich nun endlich die Frage beantworten, bei welchen Kunden überhaupt mehr als eine Adresse im Bemerkungsfeld steckt. Hier lauert allerdings eine Falle: In Abfragen können Sie keine VBA-Konstanten verwenden.