Ein Makro liest Preise aus B7:C18. Dann fügt jemand eine Spalte ein, und das Makro rechnet mit falschen Zellen. Wer dem Bereich einen Namen gibt, etwa „Preisliste“, und im Code nur diesen Namen verwendet, hat das Problem nicht: Excel passt den Namen an, wenn Zeilen oder Spalten eingefügt werden.
Die Kurzlösung
Range("Preisliste").Cells(1, 2).Value = 9.95 ' erste Zeile, zweite Spalte schreiben
MsgBox Range("Preisliste").Rows.Count ' Anzahl Zeilen
Range("Preisliste").Columns(2).NumberFormat = "#,##0.00" ' ganze zweite Spalte formatieren
Einen Namen legen Sie über Formeln > Namensmanager > Neu an oder, schneller, indem Sie den Bereich markieren und den Namen links neben der Bearbeitungsleiste in das Namenfeld tippen.
Ganze Bereiche schnell verarbeiten
Für viele Werte ist es schneller, den Bereich auf einmal in ein Datenfeld zu lesen, im Speicher zu bearbeiten und in einem Schritt zurückzuschreiben. Die Funktion NamensBereich fängt außerdem ab, dass der Name fehlt. Vor dem Einlesen prüft das Makro, ob der Bereich zur Verarbeitung passt:
Option Explicit
' Liefert den Bereich hinter einem Namen oder Nothing, wenn es den Namen nicht gibt
' oder er auf keinen Zellbereich zeigt (etwa eine Konstante).
Public Function NamensBereich(ByVal name As String) As Range
On Error Resume Next
Set NamensBereich = ThisWorkbook.Names(name).RefersToRange
On Error GoTo 0
End Function
' Liest den Bereich "Preisliste" in ein Datenfeld, erhöht alle Preise in Spalte 2 um 3 Prozent
' und schreibt das Ergebnis in einem Schritt zurück.
Public Sub PreiseErhoehen()
Dim bereich As Range, daten As Variant, zeile As Long
Set bereich = NamensBereich("Preisliste")
If bereich Is Nothing Then
MsgBox "Der Name Preisliste fehlt oder zeigt nicht auf einen Zellbereich.", vbExclamation
Exit Sub
End If
' Das Datenfeld braucht einen zusammenhängenden Bereich mit mindestens zwei Zeilen und Spalten
If bereich.Areas.Count > 1 Or bereich.Rows.Count < 2 Or bereich.Columns.Count < 2 Then
MsgBox "Preisliste muss ein zusammenhängender Bereich mit mindestens zwei Zeilen und zwei Spalten sein.", vbExclamation
Exit Sub
End If
daten = bereich.Value ' zweidimensional: daten(zeile, spalte)
For zeile = 1 To UBound(daten, 1)
If IsNumeric(daten(zeile, 2)) And Not IsEmpty(daten(zeile, 2)) Then
daten(zeile, 2) = Application.WorksheetFunction.Round(daten(zeile, 2) * 1.03, 2) ' kaufmännisch
End If
Next zeile
bereich.Value = daten
End Sub
bereich.Value liefert ein zweidimensionales Datenfeld, das immer bei 1 beginnt: daten(1, 1) ist die linke obere Zelle. Deshalb die Prüfung vorher: Bei einer einzelnen Zelle liefert Value kein Datenfeld, bei mehreren Teilbereichen nur die Werte des ersten, und ohne zweite Spalte gibt es keine Preise. WorksheetFunction.Round rundet kaufmännisch, das VBA-eigene Round rundet bei einer genauen 5 auf die gerade Ziffer, aus 0,125 würde 0,12 statt 0,13. Aus 1,20 und 0,80 werden so die Preise 1,24 und 0,82. Der Text „auf Anfrage“ bleibt unverändert, weil die Prüfung mit IsNumeric ihn überspringt.
Namen auf Mappen- und Blattebene
| Schreibweise | Bedeutung |
|---|---|
ThisWorkbook.Names("Preisliste") | Name, der für die ganze Mappe gilt |
Worksheets("Preise").Names("Preisliste") | Name, der nur für dieses Blatt gilt |
Range("Preisliste") | sucht zuerst auf dem aktiven Blatt, dann in der Mappe |
[Preisliste] | Kurzschreibweise über Evaluate, schwer zu lesen und ohne Prüfung |
Für Makros, die zuverlässig laufen sollen, ist ThisWorkbook.Names(…).RefersToRange am eindeutigsten. Range("…") allein hängt davon ab, welches Blatt gerade aktiv ist.
Namen für Werte
Ein Name muss nicht auf Zellen zeigen. =0,19 als Name „Mwst“ ist eine Konstante, die überall in Formeln verwendet werden kann. Per VBA lesen Sie sie mit Application.Evaluate("Mwst"). RefersToRange gibt es dafür nicht, die Funktion oben liefert dann Nothing.
Typische Fehler
- Laufzeitfehler 1004: „Die Methode Range für das Objekt _Global ist fehlgeschlagen“ heißt meist, dass es den Namen nicht gibt oder er auf einem anderen Blatt nur lokal gilt. Prüfen Sie den Namen im Namensmanager.
- Name zeigt auf #BEZUG!: Wurde der benannte Bereich gelöscht, ist der Name ungültig. Im Namensmanager erkennen Sie das an #BEZUG! in der Spalte „Bezieht sich auf“.
- Zellen einzeln in Schleifen: Bei Tausenden Zellen ist das Lesen und Schreiben Zelle für Zelle deutlich langsamer als über ein Datenfeld.
- Tabelle statt Name: Wächst die Liste regelmäßig, ist eine Excel-Tabelle (Strg+T) oft besser. Per VBA erreichen Sie sie über
Worksheets("Preise").ListObjects("tblPreise").DataBodyRange, und sie wächst von selbst mit.
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.
