Autor: Peter Haserodt  --- Aus Excel VBA - Gruppe: Tutorials

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:
 
 ABCD
1    
2 8073 
3 8424 
4 6183 
5 2049 
6 2689 
7 3627 
8 4797 
9    
 

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: 

 
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: 
 
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
Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
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. 

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.


Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben
ToDo
Google Werbung