Benutzerdefinierte Formatierungen

Ich bin heute über eine Schwäche der Format-Funktion gestolpert: Ein Kunde möchte, dass seine Mitarbeiter verschiedene Daten wie Bankleitzahlen, Kundennummern et cetera nach eigenen Wünschen formatieren können. Mit der Format-Funktion gelingt dies nicht immer reibungslos. wie dieser Artikel zeigt - und auch einen Lösungsvorschlag finden Sie in den folgenden Abschnitten.

Probleme der Format-Funktion

Die Format-Funktion erlaubt etwa bei Zahlenwerten das Anzeigen von Tausendertrennzeichen nach folgendem Schema:

? format("123132123", "#,###")
123.132.123

Das Komma als Tausendertrennzeichen braucht dabei nur einmalig angegeben zu werden, und es spielt keine Rolle, an welcher Position es landet. Wenn Sie einen Punkt als Dezimaltrennzeichen einbauen, sieht das Ergebnis so aus:

? format("123132123", "#,###.00")
123.132.123,00

Hinter dem Tausendertrennzeichen müssen allerdings Nullen statt Rauten angegeben werden, damit diese auch bei Zahlen ohne Dezimalanteil angezeigt werden.

Wenn der Punkt nun nicht als Tausendertrennzeichen, sondern an beliebiger Stelle erscheinen soll, fügen Sie ihn mit vorangestelltem Backslash ein:

Debug.Print Format(123123123, "#\.##\.##\.##")
123.12.31.23

Der Nachteil wird schnell auffallen. Wenn die zu formatierende Zahl nämlich weniger Stellen als der Formatausdruck Rauten aufweist, werden zwar die Platzhalter nicht gefüllt, die Punkte aber dennoch ausgegeben - was wirklich nicht gut aussieht:

Debug.Print Format(123, "#\.##\.##\.##")
..1.23

Lösung

Eine mit der üblichen Format-Funktion zu realisierende Lösung ist uns nicht eingefallen. Stattdessen haben wir eine kleine Ersatzfunktion geschrieben, die wie folgt aussieht:

Public Function FormatX(strExpression As String, strFormat As String, Optional bolReverse As Boolean = False)
    Dim i As Integer
    Dim str As String
    Dim intZero As Integer
    str = strExpression
    If bolReverse Then
        Do While Len(strFormat) - Len(Replace(Replace(strFormat, "#", ""), "0", "")) < Len(str)
             strFormat = strFormat & "0"
        Loop
        Do While (Right(strFormat, 1) = "#" Or Mid(strFormat, Len(strFormat) - 1, 1) = "\")
            strFormat = Mid(strFormat, 1, Len(strFormat) - 1)
            strFormat = Left(strFormat, Len(strFormat) - 2)
        Loop
        str = format(str, strFormat)
        Do While Not IsNumeric(Right(str, 1))
            str = Mid(str, Len(str) - 1)
        Loop
    Else
        Do While Len(strFormat) - Len(Replace(strFormat, "#", "")) > Len(str)
            If Left(strFormat, 1) = "#" Then
                strFormat = Mid(strFormat, 2)
            End If
            If Left(strFormat, 1) = "\" Then
                strFormat = Mid(strFormat, 3)
            End If
        Loop
        str = format(str, strFormat)
        Do While Not IsNumeric(Left(str, 1))
            str = Mid(str, 2)
        Loop
    End If
    FormatX = str
End Function

Der Aufruf ähnelt dem der eingebauten Format-Funktion. Die ersten beiden Parameter sind gleich, der dritte ist neu: Er ermöglicht das Anordnen der Formatierungs-Zeichen von der ersten Stelle der als Ausdruck angegebenen Zahl an. Einige Beispiele:

Debug.Print FormatX("12345", "0\.###", True)
1.2345

Debug.Print FormatX("12345678", "0\.000", True)
1.2345678

Debug.Print FormatX("123", "000\.#", True)
123

Einschränkungen

Die Funktion FormatX arbeitet nur mit der 0 und der Raute (#) als Platzhalter für Ziffern. Alle Formatierungszeichen müssen mit dem Backslash eingeleitet werden.

Möglicherweise arbeitet die Funktion noch nicht für alle Ausdrücke perfekt. Falls Ihnen Fehler auffallen, schicken Sie uns einfach per Kontaktformular einen entsprechenden Hinweis.

Verbesserung

Wenn Sie nur "normal" formatieren möchten, also von hinten nach vorne, ist die folgende Funktion deutlich einfacher:

Public Function Formatx(strExpression As Variant, strFormat As String) As String
    Dim str As String
    str = strExpression
    str = format(str, strFormat)
    Do While Not IsNumeric(Left(str, 1))
        str = Mid(str, 2)
    Loop
    Formatx = str
End Function

Diese formatiert mit der herkömmlichen Format-Funktion und löscht von links ausgehend einfach alle Zeichen bis zur ersten Zahl.