Przeglądając makra napisane przez różnych programistów możemy zauważyć, że różnią się one m.in. sposobem odwoływania się do zakresów.
Jedni preferują korzystanie z właściwości Range, inni wykorzystują w tym celu Cells. Który sposób jest lepszy? Każdy ma swoje zalety i wady, które opiszę na konkretnym przykładzie.
Właściwość Range
W pierwszym przypadku ogromnym plusem jest elastyczność definiowania zakresów wielokomórkowych. Możemy wpisać np. Range(”A1:A10”) lub zbudować cały adres za pomocą złączeń tekstu.
Możemy podać górną i dolną granicę zakresu osobno – również w formie adresu lub jako odwołanie do obiektu (także poprzez nazwę) np. Range(”A1”,”E10”) lub Range(”A1”, Range(”OstNiepusta”)).
W końcu, możemy w prosty sposób tworzyć odwołania do wielu różnych zakresów jednocześnie (zakres nieciągły) np. Range(”A1,B2:C5,E10”).
Właściwość Cells
Zaletą drugiego podejścia jest fakt, że możemy odwoływać się do kolumn na dwa sposoby.
- przez litery (tak jak w przypadku Range)
- przez liczby (zamiast ”C” możemy wpisać 3) co jest niezwykle użyteczne w sytuacji gdy chcemy przejść w pętli np. przez wszystkie kolumny zakresu.
Przykład 1
Screen poniżej pokazuje wyniki sprzedaży handlowców w danym dniu.
Screen
Celem jest dodanie do kolumny C (a konkretnie do zakresu ”C2:C6”) dzisiejszej daty. W tym przypadku mamy do czynienia tylko z jedną zmienną (ostatni wiersz zakresu).
Kod Makra
Public Sub DodajPierwszeDane()
Dim lKoniec As Long
'Sczytaj do zmiennej ostatni wiersz w tabeli
lKoniec = OstatniWiersz(Arkusz1.Range("A:A"))
'Róźne sposoby odwołania się do zakresu z datą (jedna zmienna)
'1 - Range (złączenie tekstów)
'2 - Range (górna i dolna komórka zakresu)
'3 - Range(właściwość Cells)
'4 - Range(właściwość Resize)
'5 - Cells(właściwość Resize)
'1
Arkusz1.Range("C2:C" & lKoniec) = Date
'2
Arkusz1.Range("C2", "C" & lKoniec) = Date
'3a
With Arkusz1
.Range(.Cells(2, "C"), .Cells(lKoniec, "C")) = Date
End With
'3b
With Arkusz1
.Range(.Cells(2, 3), .Cells(lKoniec, 3)) = Date
End With
'4
Arkusz1.Range("C2").Resize(lKoniec - 1, 1) = Date
'5a
Arkusz1.Cells(2, "C").Resize(lKoniec - 1, 1) = Date
'5b
Arkusz1.Cells(2, 3).Resize(lKoniec - 1, 1) = Date
End Sub
Public Function OstatniWiersz(Kolumna As Range) As Long
Dim lOstatniTekst As Long
Dim lOstatniaLiczba As Long
'Sprawdź pozycję ostatniego tekstu i ostatniej liczby
On Error Resume Next
lOstatniTekst = WorksheetFunction.Match("żżż", Kolumna, 1)
lOstatniaLiczba = WorksheetFunction.Match(9.99999999999999E+307, Kolumna, 1)
On Error GoTo 0
'Ostatni wiersz jest w tym przypadku wartością większą
OstatniWiersz = WorksheetFunction.Max(lOstatniTekst, lOstatniaLiczba)
End Function
Analiza Kodu
Wydaje się, że najlepsze rozwiązanie znajduje się pod numerem 1. Szybkie złączenie tekstu sprawnie załatwia temat. Sam najczęściej stosuję takie właśnie podejście.
Podobnie sytuacja wygląda w drugim przypadku. Musimy jednak wpisać znak cudzysłowu aż cztery razy, a dwukropek zastąpiony jest przecinkiem.
Rozwiązania 3a i 3b mimo dużej elastyczności, są bardzo nieczytelne (zwłaszcza 3b, w którym podajemy numer kolumny). W sytuacji, gdy wpisujemy cyfrę 3 wiadomo, że mamy do czynienia z kolumną ”C”, natomiast wpisując np. 54 zapewne nikt nie będzie wiedział, że chodzi o kolumnę ”BB”. Poza tym trzeba pamiętać o wstawieniu kropek przed słowem Cells, tak aby odwołać się do właściwego arkusza.
W trzech ostatnich rozwiązaniach korzystamy z właściwości Resize. Jest to o tyle dobre, że wystarczy określić tylko początek zakresu. Problemem jest to, że musimy wyliczyć ilość komórek, do których ma zostać wprowadzona data. Jeżeli jednak pod ten wynik podstawimy konkretną zmienną (może być bardzo przydatne!) wówczas rozwiązanie wyda się jeszcze bardziej interesujące.
Przykład 2
Załóżmy, że następnego dnia dopisaliśmy do naszej tabeli kilka nowych wierszy i znów chcemy wstawić makrem dzisiejszą datę.
Screen
Tym razem chcemy ją wstawić do zakresu C7:C10, ale oba wiersze są w tym przypadku zmiennymi.
Kod Makra
Public Sub DodajNastepneDane()
Dim lStart As Long
Dim lKoniec As Long
'Sczytaj do zmiennych numery wierszy
lStart = OstatniWiersz(Arkusz1.Range("C:C")) + 1
lKoniec = OstatniWiersz(Arkusz1.Range("A:A"))
'Róźne sposoby odwołania się do zakresu z datą (dwie zmienne)
'1 - Range (złączenie tekstów)
'2 - Range (górna i dolna komórka zakresu)
'3 - Range(właściwość Cells)
'4 - Range(właściwość Resize)
'5 - Cells(właściwość Resize)
'1
Arkusz1.Range("C" & lStart & ":C" & lKoniec) = Date
'2
Arkusz1.Range("C" & lStart, "C" & lKoniec) = Date
'3a
With Arkusz1
.Range(.Cells(lStart, "C"), .Cells(lKoniec, "C")) = Date
End With
'3b
With Arkusz1
.Range(.Cells(lStart, 3), .Cells(lKoniec, 3)) = Date
End With
'4
Arkusz1.Range("C" & lStart).Resize(lKoniec - lStart + 1, 1) = Date
'5a
Arkusz1.Cells(lStart, "C").Resize(lKoniec - lStart + 1, 1) = Date
'5b
Arkusz1.Cells(lStart, 3).Resize(lKoniec - lStart + 1, 1) = Date
End Sub
Analiza Kodu
Tym razem najkrótsze okazuje się drugie rozwiązanie i jest ono dość czytelne.
Pierwsze rozwiązanie traci na czytelności przez fakt, że musimy dostawić dwukropek. Jest mało intuicyjne.
Rozwiązanie trzecie prezentuje się lepiej niż w przypadku z jedną zmienną. W zasadzie nie musieliśmy prawie niczego zmieniać -> cyfrę 2 zamieniliśmy na zmienną lStart.
Rozwiązania z właściwością Resize trochę się wydłużyły, ponieważ zmienną lStart musieliśmy wpisać dwa razy.
