Zum Online-Excel Forum
Excel Power: * P. Haserodt * B. Schmitz * U. Küstner * R. Beckerbauer * M. Kaffl * K. Oppermann * B. Strohhäcker *
Downloads  Programmierung  Schulung  Excel Jeanie Html  TB Jeanie  Code Jeanie 
 Home   Impressum   Autoren   Feedback   Forum  Peter´s Blog  Guckst Du  Kontakt  Bücher  Links  Trans 


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

Arbeiten mit Bereichen

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

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
Fragen stellen im Forum: Das Forum zu Online-Excel
Nach oben