Autor: Peter Haserodt --- Aus Excel VBA - Gruppe: Tutorials
Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben
Arbeiten mit Bereichen - Range
Autor: Peter Haserodt - Erstellt: -- - Letzte Revision: --
Das Arbeiten mit Feldern, Bereichen aus Excel wird oftmals zu kompliziert angegangen.
Nehmen wir an, dass Sie einen Bereich in Excel in VBA bearbeiten wollen:
Stellen wir uns die seltsame Aufgabe vor, jeden Wert um 10 zu erhöhen und das Ergebnis in B11 bis C17 auszugeben. (Ich weiß, man könnte dies auch direkt machen, aber dies dient zur Erklärung)
| ||||||||||||||||||||||||||||||||||||||||||||||||||
Stellen wir uns die seltsame Aufgabe vor, jeden Wert um 10 zu erhöhen und das Ergebnis in B11 bis C17 auszugeben. (Ich weiß, man könnte dies auch direkt machen, aber dies dient zur Erklärung)
Option Explicit
Sub FelderBearbeiten()
Dim vX As Variant, i As Long, k As Integer
vX = Range("B2:C8")
For i = 1 To 7
For k = 1 To 2
vX(i, k) = vX(i, k) + 10
Next k
Next i
Range("b11:c17") = vX
End Sub
Vorraussetzung ist erstmal eine Variable, die ich als Variant deklariere und nicht dimensioniere.
Dieser kann ich einfach den Bereich zuweisen.
Wichtig ist hier zu verstehen, dass bei dieser Zuweisung die Variable automatisch dimensioniert wird und zwar von der Zeilenanzahl und der Spaltenanzahl des Bereiches her.
In unserem Beispiel erhalte ich also eine Variable die letztendlich wie folgt definiert ist:
(1 to 7, 1 to 2)
Felder in VBA sind defaultmäßig immer 0 basiert. (Dies muss man bei eigenen Feldern beachten)
Dies kann man zwar ändern mit der Option Base 1 Anweisung, sollte man aber vermeiden (Die Zukunft VBA.Net wird dies nicht mehr anbieten)
Excel ist aber 1 Basiert und so werden die Werte beginnend ab den Indizes 1 in das Feld geschrieben. (und der Lbound ist 1)
Beim Rückschreiben der Felder muss man darauf achten, dass der Bereich so groß ist, wie das Feld jeweils von 1 bis Ubound des Index.
Eindimensional ist Zweidimensional!
Was soll uns dies sagen?
Ganz einfach, auch wenn ich nur eine Spalte oder eine Zeile einlese erhalte ich ein zweidimensionales Feld.
Beispiele:
Dieser kann ich einfach den Bereich zuweisen.
Wichtig ist hier zu verstehen, dass bei dieser Zuweisung die Variable automatisch dimensioniert wird und zwar von der Zeilenanzahl und der Spaltenanzahl des Bereiches her.
In unserem Beispiel erhalte ich also eine Variable die letztendlich wie folgt definiert ist:
(1 to 7, 1 to 2)
Felder in VBA sind defaultmäßig immer 0 basiert. (Dies muss man bei eigenen Feldern beachten)
Dies kann man zwar ändern mit der Option Base 1 Anweisung, sollte man aber vermeiden (Die Zukunft VBA.Net wird dies nicht mehr anbieten)
Excel ist aber 1 Basiert und so werden die Werte beginnend ab den Indizes 1 in das Feld geschrieben. (und der Lbound ist 1)
Beim Rückschreiben der Felder muss man darauf achten, dass der Bereich so groß ist, wie das Feld jeweils von 1 bis Ubound des Index.
Eindimensional ist Zweidimensional!
Was soll uns dies sagen?
Ganz einfach, auch wenn ich nur eine Spalte oder eine Zeile einlese erhalte ich ein zweidimensionales Feld.
Beispiele:
Sub EineSpalte()
Dim vX As Variant
vX = Range("B2:B8")
MsgBox vX(1, 1)
MsgBox vX(2, 1)
MsgBox vX(3, 1)
End Sub
Sub EineZeile()
Dim vX As Variant
vX = Range("B2:C2")
MsgBox vX(1, 1)
MsgBox vX(1, 2)
End Sub
Es ist aber manchmal ganz wünschenswert, ein eindimensionales Feld aus einer Spalte oder Zeile zu erhalten.
Dies kann man mit einem kleinen Trick erreichen:
Dies kann man mit einem kleinen Trick erreichen:
Sub EinDimAusSpalte()
Dim vX As Variant
vX = WorksheetFunction.Transpose(Range("B2:B8"))
MsgBox UBound(vX)
MsgBox vX(1)
End Sub
Sub EinDimAusZeile()
Dim vX As Variant
vX = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range("B2:c2")))
MsgBox UBound(vX)
MsgBox vX(1)
End Sub
Performance:
Was bei der ganzen Sache relativ unbekannt ist, ist die Tatsache, dass diese Vorgehensweise unglaublich schnell ist.
Das Rückschreiben eines Bereiches über ein 2 dimensionales VariantFeld ist zig-mal schneller, als einzelne Zellen zu füllen.
Damit Sie es glauben, für Sie zwei Prozeduren zum Testen,
nehmen Sie sich dafür am besten eine neue, leere Arbeitsmappe.
Was bei der ganzen Sache relativ unbekannt ist, ist die Tatsache, dass diese Vorgehensweise unglaublich schnell ist.
Das Rückschreiben eines Bereiches über ein 2 dimensionales VariantFeld ist zig-mal schneller, als einzelne Zellen zu füllen.
Damit Sie es glauben, für Sie zwei Prozeduren zum Testen,
nehmen Sie sich dafür am besten eine neue, leere Arbeitsmappe.
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub Langsam()
Dim i As Long, k As Integer, iZeit As Long
'Achtung Inhalt aktuelles Blatt wird gelöscht
Cells.ClearContents
iZeit = GetTickCount
For i = 1 To 100
For k = 1 To 100
Cells(i, k) = "Z" & i & " " & "S" & k
Next k
Next i
MsgBox "Millisekunden: " & GetTickCount - iZeit
End Sub
Sub Schnell()
Dim i As Long, k As Integer, fFeld(99, 99), iZeit As Long
'Achtung Inhalt aktuelles Blatt wird gelöscht
'Man beachte hier die Dimensionierung
Cells.ClearContents
iZeit = GetTickCount
For i = 0 To 99
For k = 0 To 99
fFeld(i, k) = "Z" & i + 1 & " " & "S" & k + 1
Next k
Next i
Range(Cells(1, 1), Cells(100, 100)).Value = fFeld
MsgBox "Millisekunden: " & GetTickCount - iZeit
End Sub
Hier sei noch auf etwas aufmerksam gemacht:
In unserem Schnellbeispiel haben wir das Feld selbst deklariert und nicht aus Excel heraus erhalten.
Deswegen ist es 0 Basiert und wir müssen den Index um eines kleiner setzen.
Also von 0 bis 99 um 100 Einheiten zu erhalten.
In unserem Schnellbeispiel haben wir das Feld selbst deklariert und nicht aus Excel heraus erhalten.
Deswegen ist es 0 Basiert und wir müssen den Index um eines kleiner setzen.
Also von 0 bis 99 um 100 Einheiten zu erhalten.
Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben
rechte seite