Excel-Funktion EANCode()

Diese benutzerdefinierte Funktion berechnet die Prüfziffer eines EAN-Codes und hängt sie an die Nutzziffern an.

Anforderung:

In seinem Beitrag »Wie du dynamisch die Prüfziffer eines Barcodes berechnest« 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, der ursprünglich hier als Gastbeitrag erschienen ist.

Lösung:

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.

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

Berechnung EAN-Code

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 & Right(CStr(10 - intSumme Mod 10), 1))

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.

Diese Funktion arbeitet natürlich auch in Excel:mac.

Sie wissen nicht, was eine benutzerdefinierte Funktion ist und wie man sie erstellt? Dann erkläre ich es Ihnen hier kurz.

Download

Prüfziffer für EAN-Code berechnen für Excel 2007 (und neuer)
(Datei: Pruefziffer.zip, ca. 19 KB)