Prüfziffer mit VBA ermitteln

Angeregt durch einen meiner Beiträge hat mein heutiger Gastautor Raphael Hein nach einer VBA-Lösung gesucht und sie auch gefunden. Dafür bedanke ich mich ganz herzlich.

Zu seiner Person findest du interessante Informationen am Ende des Beitrages.

Und hier nun sein Beitrag.

In seinem Beitrag „Wie du dynamisch die Prüfziffer eines Barcodes berechnest“ [1] hat Gerhard Pundt eine Lösung vorgestellt, wie man die Prüfziffer mit einer Formel berechnet. Nachteil dieser Lösung ist, dass die Formel auf eine bestimmte Anzahl an Stellen (Nutzziffern) begrenzt ist und mit jeder Erweiterung auf mehr Stellen anwächst.

In einem Kommentar meinte er: „Es muss noch bessere Lösungen geben. Mit VBA sollte es auf jeden Fall gehen.“. Und wie gut das mit VBA geht, zeige ich in diesem Beitrag.

 

UDF – was ist das?

Eine benutzerdefinierte Funktion, kurz UDF (User Defined Function), arbeitet in Excel wie eine Formel (Tabellenblattfunktion). Sie kann über den Funktions-Assistenten aufgerufen oder direkt in eine Zelle eingegeben werden. Name, Parameter und Rückgabewert werden aber vom Benutzer im VBA-Editor angelegt. Dort wird auch der eigentliche Ablauf der Funktion festgelegt.

 

Name / Eingabeparameter / Rückgabewert

Um die UDF zu erstellen, öffnen wir den Visual-Basic-Editor (VBE) und fügen Sie ein neues Modul ein. Dort legen wir die neue Funktion an:

Function EANCode(Zelle As Range) As Variant

End Function

Ich habe die Funktion EANCode genannt, da sich die vorgestellte Lösung auf die Berechnung für EAN-Codes beschränkt. Sie braucht als Eingabeparameter einen Zellbezug. Ich habe den Parameter „Zelle“ genannt und als Datentyp „Range“ (einen Zellbereich) definiert.

Da der Name beim Eingeben der Zelle als TipText („IntelliSense“) angezeigt wird, weiß man so direkt, was als Parameter gebraucht wird. Der Rückgabewert kann ein beliebiger Datentyp sein, also „Variant“. Eigentlich sollte es ja eine Zahl sein, aber warum wir manchmal keine Zahl zurückgeben können, sehen wir später.

 

Prüfziffer errechnen

Wie die Prüfziffer berechnet wird, lässt sich an diesem Beispiel leicht erkennen:

EANCode[2]

Um eine beliebige Anzahl an Stellen verarbeiten zu können, brauchen wir eine Schleife, die alle Ziffern durchläuft. Und zwar bei EAN-Codes von hinten nach vorne, also von der Länge unserer Eingabe bis zurück zur ersten Stelle:

   For i = Len(Zelle.Text) To 1 Step -1

   Next i

Jetzt müssen die einzelnen Ziffern abwechseln mit 3 und 1 multipliziert werden. Dazu legen wir eine Hilfsvariable an, die den aktuellen Multiplikator enthält. Sie wird zunächst auf den ersten Multiplikator von hinten, hier also die 3 gesetzt:

   Dim bytMultiplikator As Byte

   bytMultiplikator = 3

Jetzt erfolgt die eigentliche Rechnung, indem wir (abhängig von der Zählvariable i und der Hilfsvariable bytMultiplikator) die jeweilige Ziffer mit dem Multiplikator malnehmen und zur Prüfsumme (intSumme) addieren:

      intSumme = intSumme + bytMultiplikator * CByte(Mid(Zelle.Text, i, 1))

Damit die Multiplikatoren 3 und 1 abwechseln genommen werden, wird in der Schleife zuletzt noch der Wert der Hilfsvarialble gewechselt:

      If bytMultiplikator = 3 Then

         bytMultiplikator = 1

      Else

         bytMultiplikator = 3

      End If

Sind alle Ziffern abgearbeitet und ist die Prüfsumme gebildet, muss jetzt noch die Prüfziffer berechnet werden. Die Differenz zum nächsten Vielfachen von 10 ergibt die Prüfziffer.

Diese hängen wir einfach an die als Eingabeparameter übergebene Zahl an. Das machen wir als Text, da wir die Zahl ja nicht addieren wollen. Dann müssen wir diesen Text nur wieder in eine Zahl umwandeln und können sie direkt als Rückgabewert der Funktion übergeben:

   EANCode = CDbl(Zelle.Text & CStr(10 – intSumme Mod 10))

 

Was noch nötig ist

Das wäre es fast schon gewesen. Aber wir wollen als gute Formel-Entwickler dem Benutzer einen Hinweis geben, falls er eine falsche Eingabe macht. Dazu überlegen wir uns (oder probieren aus), was schief gehen könnte und fangen diese Fehler vor der eigentlichen Bearbeitung der Daten ab.

Womit die Prüfsummenberechnung nicht zurechtkommt, ist ein Text als Eingabeparameter statt einer Zahl. Also prüfen wir, ob der Eingabewert nummerisch ist. Ist er das nicht, geben wir eine der eingebauten Excel-Fehlerkonstanten aus (hier „#WERT!“) und beenden die Funktion gleich wieder.

If Not IsNumeric(Zelle.Text) Then

EANCode = CVErr(xlErrValue)

Exit Function

End If

Außerdem kommt bei 0 kein sinnvoller Wert heraus und bei negativen Zahlen ein Fehler. Also teilen wir dem Benutzer mit, dass er zwar den richtigen Datentyp (nämlich eine Zahl) eingegeben hat, dass deren Wert aber für diese Formel ungültig ist. Dazu geben wir diesmal „#ZAHL!“ zurück:

If CDbl(Zelle.Text) <= 0 Then

EANCode = CVErr(xlErrNum)

Exit Function

End If

Diese Fehlerwerte sind auch der Grund, warum der Rückgabewert unserer Funktion keine Zahl sein darf. Denn die Excel-Fehlerkonstanten sind keine Zahlen und damit wäre der Rückgabewert vom falschen Datentyp. Machen wir einem Fehler in der Funktion, gibt Excel immer „#WERT!“ zurück.

Zuletzt müssen wir Excel noch sagen, dass es die Funktion immer neu berechnen soll, wenn sich etwas im Tabellenblatt ändert:

Application.Volatile

Fertig ist die universelle Prüfziffernberechnung nach EAN für (fast) beliebig lange Ziffernfolgen. Eine Excel-Arbeitsmappe mit der kompletten, kommentierten Funktion und einigen Beispielen gibt es auf der Seite des Autors zum Download. [2]

 

Ausblick – Was könnte man noch tun?

Es gibt noch andere Methoden, um Prüfziffern zu berechnen. Dafür könnten wir uns eine Kopie der Funktion machen und die Prüfziffern für Code 25, dem Ident- und Leitcode berechnen.

Da die einzelnen Codes in Ihrer Länge festgelegt sind, könnte man noch die Mindest- und Maximallängen prüfen und einen entsprechenden Fehlercode zurückgeben.

Vielleicht bekommt ein Nutzer die Nutzziffern mit Leerzeichen vorformatiert aus einem anderen System geliefert. Dann könnte man diese mit der Replace()-Funktion vor der Verarbeitung entfernen.

Oder es soll das Ergebnis mit Leerzeichen und Bindestrichen formatiert als Text ausgegeben werden. Dann hilft die Format()-Funktion weiter.

Spätestens jetzt wird klar:  Manchmal ist eine eigene benutzerdefiniert Funktion mit VBA dann doch flexibler, als eine Kombination vieler eingebauter Excel-Formeln.

 

Über den Autor

Raphael Hein ist selbständiger Anwendungsentwickler und betreibt die Da|ten|bank|Ma|nu|fak|tur [3].

Seit 1995 programmiert er in Microsoft Office mit VBA. Er entwickelt Datenbankanwendungen mit Access und erstellt Add-Ins für Excel und Word, um komplexe, wiederkehrende Aufgaben zu automatisieren und zusätzliche Funktionen einzubauen.

Als erfahrener Programmierer löst er Aufgaben in Excel lieber über benutzerdefinierte Funktionen als über lange Bandwurmformeln. Seine (ursprünglich persönlichen) Linksammlungen zu Microsoft Access und Excel pflegt er seit 2001 als „Yaccess – Das Access-Portal“ [4] und „Yexcel – Das Excel-Portal“ [5].

 

[1]  https://clevercalcul.wordpress.com/2016/06/21/wie-du-dynamisch-die-pruefziffer-eines-barcodes-berechnest/

[2]  http://www.datenbankmanufaktur.de/file/Pruefziffer.zip

[3]  http://www.datenbankmanufaktur.de

[4]  http://www.yaccess.de

[5]  http://www.yexcel.de

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s