VBA Format: alle Formatcodes für Zahlen, Datum und Text

Mit der Funktion Format wandelt VBA Zahlen und Datumswerte in Text mit einer bestimmten Darstellung um: 4.711,00 statt 4711, „Sonntag, 18. Oktober 2020“ statt einer Datumszahl, eine Kalenderwoche für einen Dateinamen. Diese Seite fasst die wichtigsten Codes mit Beispielen zusammen.

Die Kurzlösung

Dim text As String
text = Format(4711, "#,##0.00")              ' 4.711,00
text = Format(Date, "dd.mm.yyyy")            ' zum Beispiel 18.10.2020
text = Format(Now, "yyyy-mm-dd_hh-nn")       ' für Dateinamen: 2020-10-18_09-51
text = Format("Test", ">")                   ' TEST

Format liefert immer einen Text (String). Der Formatcode verwendet die englischen Zeichen: Punkt als Dezimaltrennzeichen, Komma als Tausendertrennzeichen. Im Ergebnis setzt VBA die Zeichen aus den Ländereinstellungen von Windows ein, auf einem deutschen System also 4.711,00.

Zahlen

Alle Beispiele mit dem Wert 4711, sofern nicht anders angegeben:

CodeErgebnisBedeutung
"0.00"4711,00Ziffer, wird mit 0 aufgefüllt
"#,##0.00"4.711,00mit Tausendertrennzeichen
"#,##0" bei 4711,54.712gerundet auf ganze Zahl
"00000" bei 4200042führende Nullen
"#.##" bei 0,5,5# zeigt keine führende Null
"0.00" bei 0,50,500 zeigt die führende Null
"#,##0," bei 12345671.235Komma am Ende teilt durch 1.000
"0.0 %" bei 0,471147,1 %Prozent (mal 100)
"#,##0.00;(#,##0.00)" bei -4711(4.711,00)eigenes Format für negative Zahlen
"#,##0.00;-#,##0.00;\-" bei 0-drittes Format für die Null
"#,##0.00 ""Dollar"""4.711,00 DollarText in doppelten Anführungszeichen
"Currency"4.711,00 €Währungsformat aus Windows
"Standard"4.711,00Tausendertrennzeichen, zwei Nachkommastellen
"Percent" bei 0,471147,11%Prozent mit zwei Nachkommastellen

Format rundet kaufmännisch: Format(2.5, "0") ergibt 3. Die VBA-Funktion Round rundet dagegen zur geraden Zahl: Round(2.5, 0) ergibt 2, Round(3.5, 0) ergibt 4.

Datum

Alle Beispiele mit Sonntag, 18.10.2020, 09:51:05 Uhr:

CodeErgebnisBedeutung
"d" / "dd"18Tag ohne / mit führender Null
"ddd"SoWochentag abgekürzt
"dddd"SonntagWochentag ausgeschrieben
"ddddd" oder "Short Date"18.10.2020kurzes Datum aus Windows
"dddddd" oder "Long Date"Sonntag, 18. Oktober 2020langes Datum aus Windows
"m" / "mm"10Monat ohne / mit führender Null
"mmm"OktMonat abgekürzt
"mmmm"OktoberMonat ausgeschrieben
"q"4Quartal
"yy" / "yyyy"20 / 2020Jahr zwei- oder vierstellig
"y"292Tag des Jahres
"w"1Wochentag als Zahl, 1 = Sonntag
"w" mit vbMonday7Wochentag als Zahl, 1 = Montag
"ww"43Woche nach US-Zählung
"ww" mit vbMonday, vbFirstFourDays42Kalenderwoche nach ISO 8601
"dd.mm.yyyy"18.10.2020frei zusammengesetzt
"yyyy-mm-dd"2020-10-18sortierbar, gut für Dateinamen

Für die deutsche Kalenderwoche geben Sie die beiden optionalen Argumente an:

kw = Format(Datum, "ww", vbMonday, vbFirstFourDays)

Ohne sie zählt Format die Wochen ab Sonntag und ab dem 1. Januar, das ergibt hier 43 statt 42. An manchen Jahreswechseln liefert aber auch diese Kombination eine falsche Woche: Für Montag, 31.12.2007, und Montag, 29.12.2003, ergibt sie 53 statt 1. Wo es genau darauf ankommt, rechnen Sie mit der Tabellenfunktion: Application.WorksheetFunction.IsoWeekNum(Datum) liefert in beiden Fällen richtig 1.

Uhrzeit

CodeErgebnisBedeutung
"h" / "hh"9 / 09Stunde
"n" / "nn"51Minute (n, nicht m)
"s" / "ss"5 / 05Sekunde
"hh:nn"09:51Stunden und Minuten
"Short Time"09:51kurze Zeit aus Windows
"Long Time" oder "ttttt"09:51:05lange Zeit aus Windows
"hh:nn AM/PM"09:51 AM12-Stunden-Anzeige

Minuten haben in VBA den Code n. Steht mm direkt nach hh, erkennt Format ebenfalls Minuten, "hh:mm" ergibt also auch 09:51. Sicherer und eindeutig ist nn.

Text

CodeBeispielErgebnis
">"TestTEST
"<"Testtest
"@@@@@@@@"4711„ 4711“ (rechtsbündig auf 8 Zeichen)
"!@@@@@@@@"4711„4711 “ (linksbündig auf 8 Zeichen)

Für Groß- und Kleinbuchstaben sind UCase und LCase lesbarer als Format.

Unterschiede zum Zahlenformat in Excel

Format in VBA und das Zahlenformat einer Zelle sehen ähnlich aus, sind aber nicht gleich:

  • Minuten: VBA verwendet n, Excel mm (in der deutschen Oberfläche MM).
  • Stunden über 24: [h]:mm funktioniert im Zahlenformat einer Zelle, in VBA-Format nicht. Format(1.5, "[h]:nn") ergibt „:00“ statt 36:00.
  • Schrägstrich: In VBA ersetzt Format den Schrägstrich durch das Datumstrennzeichen aus Windows. Format(Datum, "dd/mm/yyyy") ergibt auf einem deutschen System 18.10.2020. Für einen echten Schrägstrich schreiben Sie "dd\/mm\/yyyy".
  • Zellen formatieren: Wer eine Zelle formatieren will, statt Text zu erzeugen, nimmt Range("A1").NumberFormat = "#,##0.00". Der Wert bleibt dann eine Zahl, mit der Excel weiterrechnen kann.

Typische Fehler

  • Mit dem Ergebnis rechnen: Format liefert Text, auch wenn er wie eine Zahl aussieht. Für Zellen ist NumberFormat der richtige Weg, weil der Wert dann eine Zahl bleibt.
  • Deutsche Formatzeichen: Format(Datum, "TT.MM.JJJJ") funktioniert in VBA nicht. Die Codes sind immer englisch: d, m, y, h, n, s.
  • Kalenderwoche ohne Zusatzargumente: Siehe oben, ohne vbMonday, vbFirstFourDays weicht die Woche von der deutschen Zählung ab.
  • Leerer Wert: Format(Null, "0.00") liefert einen leeren Text. Prüfen Sie Zellen bei Bedarf vorher mit IsEmpty, damit eine leere Zelle nicht unbemerkt als 0 erscheint.

Die Ergebnisse auf dieser Seite gelten für ein deutsches Windows. Mit anderen Ländereinstellungen setzt VBA andere Trennzeichen und Monatsnamen ein.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.