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:
| Code | Ergebnis | Bedeutung |
|---|---|---|
"0.00" | 4711,00 | Ziffer, wird mit 0 aufgefüllt |
"#,##0.00" | 4.711,00 | mit Tausendertrennzeichen |
"#,##0" bei 4711,5 | 4.712 | gerundet auf ganze Zahl |
"00000" bei 42 | 00042 | führende Nullen |
"#.##" bei 0,5 | ,5 | # zeigt keine führende Null |
"0.00" bei 0,5 | 0,50 | 0 zeigt die führende Null |
"#,##0," bei 1234567 | 1.235 | Komma am Ende teilt durch 1.000 |
"0.0 %" bei 0,4711 | 47,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 Dollar | Text in doppelten Anführungszeichen |
"Currency" | 4.711,00 € | Währungsformat aus Windows |
"Standard" | 4.711,00 | Tausendertrennzeichen, zwei Nachkommastellen |
"Percent" bei 0,4711 | 47,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:
| Code | Ergebnis | Bedeutung |
|---|---|---|
"d" / "dd" | 18 | Tag ohne / mit führender Null |
"ddd" | So | Wochentag abgekürzt |
"dddd" | Sonntag | Wochentag ausgeschrieben |
"ddddd" oder "Short Date" | 18.10.2020 | kurzes Datum aus Windows |
"dddddd" oder "Long Date" | Sonntag, 18. Oktober 2020 | langes Datum aus Windows |
"m" / "mm" | 10 | Monat ohne / mit führender Null |
"mmm" | Okt | Monat abgekürzt |
"mmmm" | Oktober | Monat ausgeschrieben |
"q" | 4 | Quartal |
"yy" / "yyyy" | 20 / 2020 | Jahr zwei- oder vierstellig |
"y" | 292 | Tag des Jahres |
"w" | 1 | Wochentag als Zahl, 1 = Sonntag |
"w" mit vbMonday | 7 | Wochentag als Zahl, 1 = Montag |
"ww" | 43 | Woche nach US-Zählung |
"ww" mit vbMonday, vbFirstFourDays | 42 | Kalenderwoche nach ISO 8601 |
"dd.mm.yyyy" | 18.10.2020 | frei zusammengesetzt |
"yyyy-mm-dd" | 2020-10-18 | sortierbar, 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
| Code | Ergebnis | Bedeutung |
|---|---|---|
"h" / "hh" | 9 / 09 | Stunde |
"n" / "nn" | 51 | Minute (n, nicht m) |
"s" / "ss" | 5 / 05 | Sekunde |
"hh:nn" | 09:51 | Stunden und Minuten |
"Short Time" | 09:51 | kurze Zeit aus Windows |
"Long Time" oder "ttttt" | 09:51:05 | lange Zeit aus Windows |
"hh:nn AM/PM" | 09:51 AM | 12-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
| Code | Beispiel | Ergebnis |
|---|---|---|
">" | Test | TEST |
"<" | Test | test |
"@@@@@@@@" | 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, Excelmm(in der deutschen OberflächeMM). - Stunden über 24:
[h]:mmfunktioniert 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, vbFirstFourDaysweicht 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 mitIsEmpty, 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.
