W literaturze poświęconej VBA możemy przeczytać, że operowanie na komórkach arkusza (np. w pętli) jest bardzo wolne. Skuteczniejszym rozwiązaniem jest zgranie danych do tablicy, przetworzenie ich i zapis do arkusza.
Problem
Zgranie zakresu komórek do tablicy jest bardzo proste. Wystarczy zadeklarować zmienną typu Variant i przypisać jej konkretny zakres (obiekt Range).
Problem pojawia się gdy chcemy przerzucić do tablicy pojedynczą komórkę. Excel nie tworzy bowiem tablicy jednoelementowej (nawet gdy użyjemy transpozycji), ale traktuje tą komórkę jako wartość typu Variant. Taka sytuacja może przyczyniać się do powstawania błędów.
Rozwiązanie
Aby temu zaradzić możemy skorzystać z wbudowanej funkcji Array. Poniższa funkcja VBA realizuje to zadanie.
Procedura RangeToArray pokazuje, że możemy za pomocą takiej samej pętli przejść po wszystkich elementach tablicy. Bez znaczenia czy zawiera ona jeden element czy więcej.
Function CreateArray(Target As Range) As Variant
Dim vaTemp As Variant
' Gdy targetem jest jedna komórka
' przekonwertuj ją na jednoelementową tablicę.
' W przeciwnym wypadku przetransponuj zakres
If Target.Cells.Count = 1 Then
vaTemp = Array(Target)
Else
vaTemp = WorksheetFunction.Transpose(Target)
End If
' Wynikiem funkcji jest nowa tablica
CreateArray = vaTemp
End Function
Sub RangeToArray()
Dim vaNewArray As Variant
Dim iCounter As Integer
' Utwórz tablicę z jednej komórki
vaNewArray = CreateArray(Sheet1.Range("A1"))
' Przejdź w pętli po elementach tablicy (jeden element)
For iCounter = LBound(vaNewArray) To UBound(vaNewArray)
Debug.Print vaNewArray(iCounter)
Next iCounter
' Utwórz tablicę z zakresu komórek
vaNewArray = CreateArray(Sheet1.Range("A1:A3"))
' Przejdź w pętli po wszystkich elementach tablicy
For iCounter = LBound(vaNewArray) To UBound(vaNewArray)
Debug.Print vaNewArray(iCounter)
Next iCounter
End Sub
Ja jednak nadal będę się wystrzegał Arraya przez to nieszczęsne Lbound = 0. Nie chce mi się za każdym razem dopisywać Option Base 1.
Maciek, nie widzę potrzeby korzystania z Option Base – to właśnie tej instrukcji trzeba się wystrzegać bo wprowadza chaos. Jeżeli korzystasz z funkcji LBound i UBound to nie interesuje Cię czy tablica zaczyna się od 0 czy od 1 :-). Podaj mi naprawdę jakiś konkretny przykład, w którym to zero by przeszkadzało.