Odwołanie do zakresu komórek można wykonać w VBA na wiele sposobów. Przeważnie w tym celu wykorzystuje się właściwości Range i Cells obiektu Worksheet oraz właściwości Resize, Offset obiektu Range. Który sposób jest lepszy? Wszystko zależy od konkretnej sytuacji.
Właściwość Range
Co jest istotnym atutem Range?
- Łatwość definiowania zakresu wielokomórkowego. Możemy wpisać np. Range(”A1:A10”) lub zbudować cały adres za pomocą operatora złączenia.
- Tworzenie odwołań do wielu zakresów jednocześnie. Chodzi tutaj o tzw. zakresy nieciągłe np. Range(”A1,B2:C5,E10”).
- Zbudowanie zakresu w oparciu o pierwszą i ostatnią komórkę. Możemy to zrobić poprzez adres np. Range(”A1”,”E10”) lub odwołanie Range(”A1”, Range(”OstNiepusta”)).
Właściwość Cells
Co jest istotnym atutem Cells?
- Odwołanie do kolumn przez litery. Tak jak w przypadku Range.
- Odwołanie do kolumn przez liczby. Zamiast ”C” możemy wpisać 3. Jest to bardzo przydatne w sytuacji gdy chcemy przejść w pętli np. przez wszystkie kolumny zakresu.
Przykład 1. Tylko dolna komórka
Celem makra jest dodanie dzisiejszej daty do zakresu ”C2:C6”.
W tym przypadku mamy do czynienia tylko z jedną zmienną – ostatni niepusty wiersz wyliczymy osobną funkcją.
1 – Kod VBA
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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) Arkusz1.Range("C2:C" & lKoniec) = Date '1 Arkusz1.Range("C2", "C" & lKoniec) = Date '2 With Arkusz1 .Range(.Cells(2, "C"), .Cells(lKoniec, "C")) = Date End With '3a With Arkusz1 .Range(.Cells(2, 3), .Cells(lKoniec, 3)) = Date End With '3b Arkusz1.Range("C2").Resize(lKoniec - 1, 1) = Date '4 Arkusz1.Cells(2, "C").Resize(lKoniec - 1, 1) = Date '5a Arkusz1.Cells(2, 3).Resize(lKoniec - 1, 1) = Date '5b End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
1 – Analiza kodu
- Najkrótsze i najlepsze rozwiązanie znajduje się – według mnie – pod numerem 1. Proste złączenie tekstu szybko załatwia temat. Osobiście sam stosuję takie podejście.
- W drugim przypadku musimy wpisać znak cudzysłowu aż cztery razy. Biorąc pod uwagę, że w kodzie VBA odwołujemy się do zakresów praktycznie non stop – to duża strata czasu.
- 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”. Wpisując 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 odwołanie do zakresu następuje przez Resize. Jest to o tyle dobre, że wystarczy podać 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 zmienną, wtedy rozwiązanie wyda się jeszcze bardziej interesujące. Czasami korzystam z opcji nr 4.
Przykład 2. Dolna i górna komórka
Załóżmy, że następnego dnia dopisaliśmy do naszej tabeli kilka nowych wierszy i znów chcemy wstawić makrem dzisiejszą datę.
Tym razem chcemy ją wstawić do zakresu C7:C10, ale obie komórki będą w tym przypadku zmiennymi. Sprawa więc mocno się komplikuje…
2 – Kod VBA
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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) Arkusz1.Range("C" & lStart & ":C" & lKoniec) = Date '1 Arkusz1.Range("C" & lStart, "C" & lKoniec) = Date '2 With Arkusz1 .Range(.Cells(lStart, "C"), .Cells(lKoniec, "C")) = Date End With '3a With Arkusz1 .Range(.Cells(lStart, 3), .Cells(lKoniec, 3)) = Date End With '3b Arkusz1.Range("C" & lStart).Resize(lKoniec - lStart + 1, 1) = Date '4 Arkusz1.Cells(lStart, "C").Resize(lKoniec - lStart + 1, 1) = Date '5a Arkusz1.Cells(lStart, 3).Resize(lKoniec - lStart + 1, 1) = Date '5b End Sub |
2 – Analiza kodu
- Pierwsze rozwiązanie traci na czytelności. Musimy dostawić dwukropek i aż trzy operatory złączenia. Jest to nieczytelne i mało intuicyjne.
- Drugie rozwiązanie jest bardzo zbliżone do pierwszego. Ciut czytelniejsze i najkrótsze. Można je rozważyć – ja go nie stosuję.
- Rozwiązanie trzecie prezentuje się znacznie lepiej niż w poprzednim teście. W zasadzie nie musieliśmy niczego zmieniać – zmienna lStart zastąpiła cyfrę 2. Ze względu na dużą elastyczność – używam właśnie konstrukcji 3a.
- Rozwiązania z właściwością Resize trochę się wydłużyły. Zmienną lStart musieliśmy wpisać dwa razy, do tego dodać zmienną lKoniec. Jest to dość nieczytelne i odpuściłbym ten sposób.
Podsumowanie
Jak widać, wszystko zależy od sytuacji.
W pierwszym teście, mieliśmy tylko jedną zmienną. I tutaj najlepiej sprawdza się prosta konstrukcja Range z operatorem złączenia. Dobrym sposobem jest także użycie właściwości Resize.
W drugim teście, mieliśmy dwie zmienne. I tutaj sytuacja wygląda z goła inaczej. Najlepsza i najbardziej elastyczna jest wg mnie konstrukcja z Range i podwójnym Cells.
