Prüfziffern per VBA berechnen und prüfen

Im Beitrag „Reguläre Ausdrücke in VBA, Teil 2: Praxis-Rezepte“ (www.access-im-unternehmen.de/1617) haben wir mit einem regulären Ausdruck geprüft, ob eine IBAN formal richtig aufgebaut ist. Das schützt aber nur vor groben Schnitzern – ein Zahlendreher in der Kontonummer geht anstandslos durch. Genau dafür gibt es Prüfziffern: eine oder zwei zusätzliche Stellen, aus den übrigen berechnet, die beim Vertippen fast immer nicht mehr passen. Dieser Beitrag zeigt die Verfahren hinter IBAN, ISBN, GTIN, Kreditkartennummer und Umsatzsteuer-Identifikationsnummer und liefert sie als fertige Funktionen, die Sie in Formularen und Abfragen einsetzen können.

Was eine Prüfziffer leistet – und was nicht

Eine Prüfziffer enthält redundante Information. Sie enthält keine neue Information, sondern bildet in verdichteter Form ab, was in den übrigen Stellen bereits enthalten ist. Weicht sie ab, war ein Fehler im Spiel.

Die gängigen Verfahren erkennen einzelne falsch eingegebene Ziffern und viele typische Zahlendreher. Welche Fehler garantiert erkannt werden, hängt jedoch vom jeweiligen Verfahren ab: Das für die GTIN verwendete Modulo-10-Verfahren lässt beispielsweise bestimmte Vertauschungen benachbarter Ziffern unentdeckt, während die Modulo-11- und Modulo-97-Verfahren hier strenger sind.

Was eine Prüfziffer dagegen nicht leistet: Sie sagt nichts darüber aus, ob es das Konto, das Buch oder die Firma tatsächlich gibt. Eine IBAN mit korrekter Prüfziffer kann problemlos frei erfunden sein.

Und sie ersetzt auch nicht die Formatprüfung – beide Schritte gehören zusammen.

Das gemeinsame Muster

So unterschiedlich die Verfahren im Detail sind, der Ablauf ist immer derselbe. Erst wird der Wert normalisiert, dann die Struktur geprüft und zuletzt gerechnet. Beim Normalisieren dürfen allerdings nur die für den jeweiligen Nummerntyp zulässigen Trennzeichen entfernt werden. Alle anderen Zeichen müssen zur Ablehnung der Eingabe führen.

Das klingt nach Erbsenzählerei, ist aber sicherheitsrelevant. Eine Funktion, die unterschiedslos jedes nicht alphanumerische Zeichen wegwirft, würde die Eingabe DE89!3704 0044 0532 0130 00 klaglos als gültige IBAN durchwinken – das Ausrufezeichen verschwindet ja vor der Prüfung. Für eine Validierung ist das zu großzügig.

Wir trennen deshalb in zwei Schritte. Zuerst eine Funktion, die tatsächlich nur Ziffern und Großbuchstaben übrig lässt – siehe Listing 1.

Public Function NurAlphanumerisch(ByVal strWert As String) As String
    Dim lngPos As Long
    Dim strZeichen As String
    Dim strErgebnis As String
    For lngPos = 1 To Len(strWert)
        strZeichen = UCase(Mid(strWert, lngPos, 1))
        Select Case Asc(strZeichen)
            Case 48 To 57, 65 To 90
                strErgebnis = strErgebnis & strZeichen
        End Select
    Next lngPos
    NurAlphanumerisch = strErgebnis
End Function

Listing 1: Entfernen aller Trenn- und Sonderzeichen

Ein Aufruf im Direktbereich zeigt, was die Funktion tut:

Debug.Print NurAlphanumerisch("DE89 3704 0044")
Debug.Print NurAlphanumerisch("0-8044-2957-x")

Ergebnis:

DE8937040044
080442957X

Die Prüfung über die ASCII-Codes der Zeichen statt über einen Vergleich der Buchstaben ist beabsichtigt. Unter Option Compare Database würde ein Ausdruck wie strZeichen >= „A“ auch Umlaute einschließen, was hier unerwünscht wäre.

Und darüber liegt eine zweite Funktion, die zuerst kontrolliert, ob im Rohwert überhaupt nur erlaubte Zeichen vorkommen. Ist das nicht der Fall, liefert sie einen Leerstring zurück – und jede nachfolgende Strukturprüfung schlägt fehl:

Private Function Bereinigt( _
        ByVal strWert As String, _
        ByVal strErlaubt As String) As String
    If Not RegExTest(strWert, strErlaubt) Then Exit Function
    Bereinigt = NurAlphanumerisch(strWert)
End Function

Der Unterschied wird sofort sichtbar. Die eckigen Klammern im Aufruf dienen nur dazu, den Leerstring im Direktbereich erkennbar zu machen:

Debug.Print "[" & Bereinigt("DE89 3704", _
    "^[A-Z0-9 ]+$") & "]"
Debug.Print "[" & Bereinigt("DE89!3704", _
    "^[A-Z0-9 ]+$") & "]"

Ergebnis:

[DE893704]
[]

Welche Trennzeichen erlaubt sind, entscheidet jedes Verfahren für sich: bei der IBAN nur Leerzeichen, bei ISBN und GTIN zusätzlich Bindestriche. Die Funktion RegExTest stammt aus dem vorigen Beitrag dieser Reihe; wer sie nicht zur Hand hat, kann die Vorprüfung auch mit einer Schleife über die Zeichen erledigen.

Rechnen ohne Überlauf: das Modulo-97-Verfahren

Die IBAN verwendet ein Verfahren, das auf den ersten Blick in VBA gar nicht umsetzbar scheint. Man ersetzt jeden Buchstaben durch eine zweistellige Zahl, hängt die ersten vier Zeichen hinten an und bildet vom Ergebnis den Rest bei der Division durch 97. Das Problem: Bei einer deutschen IBAN entsteht dabei eine Zahl mit über 20 Stellen.

Ein Long reicht nur bis 2.147.483.647, und ein Double kann sehr lange Ganzzahlen nicht mehr zifferngenau darstellen.

Der Ausweg ist so einfach wie elegant. Statt die ganze Zahl zu bilden, arbeiten wir uns Ziffer für Ziffer vor und nehmen nach jedem Schritt den Rest. Weil dieser höchstens 96 sein kann, bleibt der Zwischenwert immer unter 970 und passt damit mühelos in ein Long (siehe Listing 2).

Public Function Modulo97(ByVal strZiffern As String) As Long
    Dim lngPos As Long
    Dim lngRest As Long
    For lngPos = 1 To Len(strZiffern)
        lngRest = (lngRest * 10 + CLng(Mid(strZiffern, lngPos, 1))) Mod 97
    Next lngPos
    Modulo97 = lngRest
End Function

Listing 2: Rest der Division durch 97 für beliebig lange Zahlen

Dass die Funktion auch mit Zahlen umgeht, die kein VBA-Datentyp mehr fassen kann, lässt sich leicht nachprüfen:

Debug.Print Modulo97("100")
Debug.Print Modulo97("12345678901234567890")

Ergebnis:

3
3

Dieses schrittweise Vorgehen lässt sich auf jeden Modulus übertragen – die Funktion selbst ist allerdings fest auf 97 programmiert.

Merken Sie sich das Muster unabhängig vom Thema Prüfziffern: Es ist immer dann Gold wert, wenn Sie mit Zahlen jenseits der VBA-Grenzen hantieren müssen.

Rezept 1: Die IBAN

Nun die Umsetzung der Zeichen in Ziffern. Buchstaben werden dabei durch ihre Position im Alphabet plus 9 ersetzt, also A durch 10 bis Z durch 35. Da der Zeichencode von A bei 65 liegt, genügt eine Subtraktion von 55 (siehe Listing 3).

Public Function IbanInZiffern(ByVal strIban As String) As String
    Dim strUmgestellt As String
    Dim lngPos As Long
    Dim lngCode As Long
    Dim strErgebnis As String
    ' Die ersten vier Zeichen wandern ans Ende
    strUmgestellt = Mid(strIban, 5) & Left(strIban, 4)
    For lngPos = 1 To Len(strUmgestellt)
        lngCode = Asc(Mid(strUmgestellt, lngPos, 1))
        If lngCode >= 48 And lngCode <= 57 Then
            strErgebnis = strErgebnis & Chr(lngCode)
        Else
            strErgebnis = strErgebnis & CStr(lngCode - 55)
        End If
    Next lngPos
    IbanInZiffern = strErgebnis
End Function

Listing 3: Umsetzen der IBAN in eine reine Ziffernfolge

Damit lässt sich das Verfahren Schritt für Schritt nachvollziehen. Das D wird zu 13, das E zu 14, und der Rest muss am Ende 1 ergeben:

Debug.Print IbanInZiffern("DE89370400440532013000")
Debug.Print Modulo97( _
    IbanInZiffern("DE89370400440532013000"))
370400440532013000131489
1

Damit ist die eigentliche Prüffunktion kurz. Der Rest muss am Ende genau 1 betragen – das ist die Kernaussage des Verfahrens nach ISO 7064, das wir in der Funktion IstIban abgebildet haben (siehe Listing 4).

Public Function IstIban(ByVal strIban As String) As Boolean
    Dim strWert As String
    Dim lngLaenge As Long
    ' Erlaubt sind nur Buchstaben, Ziffern, Leerzeichen
    strWert = Bereinigt(strIban, "^[A-Z0-9 ]+$")
    ' Grundstruktur pruefen
    If Not RegExTest(strWert, "^[A-Z]{2}\d{2}[A-Z0-9]{11,30}$") Then
        Exit Function
    End If
    ' Land muss bekannt und die Laenge korrekt sein
    lngLaenge = IbanLaenge(Left(strWert, 2))
    If lngLaenge = 0 Then Exit Function
    If Len(strWert) <> lngLaenge Then Exit Function
    IstIban = (Modulo97(IbanInZiffern(strWert)) = 1)
End Function

Listing 4: Prüfen einer IBAN

Vier Aufrufe zeigen, welche Fehler die Funktion abfängt:

Debug.Print IstIban("DE89 3704 0044 0532 0130 00")
Debug.Print IstIban("DE89 3704 0044 0532 0130 01")
Debug.Print IstIban("DE89!3704 0044 0532 0130 00")
Debug.Print IstIban("XX89 3704 0044 0532 0130 00")
Wahr
Falsch
Falsch
Falsch

Der zweite Aufruf scheitert an der Prüfziffer, der dritte am eingeschmuggelten Ausrufezeichen und der vierte am unbekannten Ländercode.

Die Längenprüfung ist wichtiger, als sie aussieht. Ohne sie würde eine deutsche IBAN, bei der eine Ziffer fehlt, unter Umständen trotzdem als gültig durchgehen, weil das Modulo-Verfahren die Länge nicht kennt. Die Zuordnung von Land zu Länge erledigt eine schlichte Fallunterscheidung, die wir in der Funktion IbanLaenge erledigen (siehe Listing 5).

Public Function IbanLaenge(ByVal strLand As String) As Long
    Select Case UCase(strLand)
        Case "DE", "GB", "BG": IbanLaenge = 22
        Case "AT", "LT", "LU", "EE": IbanLaenge = 20
        Case "CH", "LI", "HR": IbanLaenge = 21
        Case "FR", "IT", "GR": IbanLaenge = 27
        Case "ES", "SE", "CZ", "SK", "RO": IbanLaenge = 24
        Case "NL", "DK", "FI": IbanLaenge = 18
        Case "BE": IbanLaenge = 16
        Case "NO": IbanLaenge = 15
        Case "SI": IbanLaenge = 19
        Case "PT": IbanLaenge = 25
        Case "TR": IbanLaenge = 26
        Case "PL", "HU": IbanLaenge = 28
        Case Else: IbanLaenge = 0
    End Select
End Function

Listing 5: Ermitteln der Länge einer IBAN

Die Funktion liefert zum Beispiel folgende Ergebnisse:

Debug.Print IbanLaenge("DE")
Debug.Print IbanLaenge("CH")
Debug.Print IbanLaenge("US")

Ergebnisse:

22
21
0

Der Rückgabewert 0 bedeutet, dass das Land von der Funktion nicht unterstützt wird. Die IBAN wird in diesem Fall abgelehnt, weil ihre vorgeschriebene Länge nicht geprüft werden kann – sonst könnte eine formal passende Nummer mit einem gar nicht vergebenen Ländercode als gültig durchgehen.

Die Liste deckt die für den deutschsprachigen Raum wichtigsten Länder ab. Insgesamt gibt es rund 80 IBAN-Länder; die vollständige und verbindliche Aufstellung pflegt die Organisation SWIFT im sogenannten IBAN-Register. Wenn Sie international arbeiten, ergänzen Sie die Liste entsprechend – oder lagern sie in eine Tabelle aus.

Der umgekehrte Weg ist ebenso nützlich: aus Ländercode und der bereits zusammengesetzten nationalen Kontokennung, der BBAN, die vollständige IBAN erzeugen. Dazu setzt man wie in der Funktion IbanErzeugen aus Listing 6 zunächst zwei Nullen als Platzhalter ein und zieht den ermittelten Rest von 98 ab:

Public Function IbanErzeugen(ByVal strLand As String, ByVal strBban As String) As String
    Dim strRoh As String
    Dim lngLaenge As Long
    Dim lngRest As Long
    Dim strPruefziffern As String
    strLand = UCase(Bereinigt(strLand, "^[A-Z]{2}$"))
    If Len(strLand) <> 2 Then Exit Function
    lngLaenge = IbanLaenge(strLand)
    If lngLaenge = 0 Then Exit Function
    strBban = Bereinigt(strBban, "^[A-Z0-9 ]+$")
    If Len(strBban) <> lngLaenge - 4 Then Exit Function
    If strLand = "DE" Then
        If Not RegExTest(strBban, "^\d{18}$") Then
            Exit Function
        End If
    End If
    strRoh = strLand & "00" & strBban
    lngRest = Modulo97(IbanInZiffern(strRoh))
    strPruefziffern = Format(98 - lngRest, "00")
    IbanErzeugen = strLand & strPruefziffern & strBban
End Function

Listing 6: Erzeugen einer IBAN aus Ländercode und BBAN

Debug.Print IbanErzeugen("DE", _
    "3704 0044 0532 0130 00")
Debug.Print "[" & IbanErzeugen( _
    "DE", "370400440532013") & "]"

Ergebnisse:

DE89370400440532013000
[]

Der zweite Aufruf liefert einen Leerstring, weil die übergebene BBAN nur 15 statt 18 Stellen hat.

Beachten Sie, dass die Funktion keine Bankleitzahl und Kontonummer entgegennimmt, sondern die bereits korrekt zusammengesetzte BBAN. Wie diese aufgebaut ist, legt jedes Land selbst fest. In Deutschland besteht sie aus der achtstelligen Bankleitzahl und der Kontonummer, die dabei links mit führenden Nullen auf zehn Stellen aufgefüllt werden muss.

Für die Bankleitzahl 37040044 und die Kontonummer 532013000 ergibt das die BBAN 370400440532013000, und der Aufruf IbanErzeugen(„DE“, „370400440532013000“) liefert die IBAN DE89370400440532013000.

Die Funktion berechnet die IBAN-Prüfziffer, validiert aber nicht den landesspezifischen Aufbau der BBAN. Für Deutschland müsste zusätzlich geprüft werden, ob die BBAN ausschließlich aus 18 Ziffern besteht – genau das erledigt die Abfrage auf den Ländercode im Listing. Für andere Länder wären entsprechende Regeln zu ergänzen, denn manche BBAN-Formate enthalten tatsächlich Buchstaben.

Rezept 2: ISBN-10

Bis Ende 2006 wurden ISBNs zehnstellig vergeben; seit 2007 werden ausschließlich dreizehnstellige ISBNs verwendet. Bei älteren Büchern begegnet Ihnen deshalb häufig noch die ISBN-10. Ihr Verfahren arbeitet mit dem Modulus 11 und gewichtet jede Stelle mit ihrer Position. Die Summe aller zehn gewichteten Ziffern muss glatt durch 11 teilbar sein.

Weil ein Rest von 10 auftreten kann, dieser aber nicht in eine Ziffer passt, wird er als X geschrieben. Das ist der Grund für die auf den ersten Blick verwunderlichen ISBN-Nummern, die mit einem Buchstaben enden.

Die Funktion zum Prüfen einer ISBN namens IstIsbn10 finden wir in Listing 7. Hier sind Beispiele zu dieser Funktion:

Public Function IstIsbn10(ByVal strIsbn As String) As Boolean
    Dim strWert As String
    Dim lngPos As Long
    Dim lngZiffer As Long
    Dim lngSumme As Long
    ' Ziffern, X sowie Bindestrich und Leerzeichen
    strWert = Bereinigt(strIsbn, "^[0-9X \-]+$")
    If Not RegExTest(strWert, "^\d{9}[\dX]$") Then
        Exit Function
    End If
    For lngPos = 1 To 10
        If Mid(strWert, lngPos, 1) = "X" Then
            lngZiffer = 10
        Else
            lngZiffer = CLng(Mid(strWert, lngPos, 1))
        End If
        lngSumme = lngSumme + lngPos * lngZiffer
    Next lngPos
    IstIsbn10 = (lngSumme Mod 11 = 0)
End Function

Listing 7: Prüfen einer zehnstelligen ISBN

Debug.Print IstIsbn10("0-306-40615-2")
Debug.Print IstIsbn10("0-8044-2957-X")
Debug.Print IstIsbn10("0-306-40615-3")

Ergebnisse:

Wahr
Wahr
Falsch

Der zweite Aufruf zeigt eine ISBN mit dem X als Prüfziffer, der dritte dieselbe Nummer wie der erste mit einer verfälschten letzten Stelle.

Das X darf ausschließlich an der letzten Stelle stehen, was das Muster im regulären Ausdruck bereits sicherstellt.

Rezept 3: GTIN und ISBN-13

Die Identifikationsnummern GTIN-8, GTIN-12, GTIN-13 und GTIN-14 sowie die heutige ISBN-13 verwenden dasselbe Prüfziffernverfahren. GS1 bezeichnet die Varianten heute durchgängig als GTIN; die ältere Bezeichnung EAN begegnet Ihnen in älteren Datenbeständen aber weiterhin. Und noch eine Unterscheidung lohnt sich: Die GTIN ist die Nummer, der Strichcode lediglich ihr grafischer Datenträger.

Bei diesem Verfahren werden die Stellen abwechselnd mit 1 und 3 gewichtet, und zwar so, dass die Stelle unmittelbar links von der Prüfziffer das Gewicht 3 erhält.