Zakresy nieciągłe są tematem mało popularnym. W arkuszu zazwyczaj operujemy na tabeli, a nie odrębnych zakresach. Ta sytuacja znajduje swoje odzwierciedlenie w VBA. Operowanie na zakresach nieciągłych przysparza więc trochę problemów. Niewiele osób zna bowiem taki obiekt jak Area.
Czyszczenie wybranych komórek
Czy są sytuacje, które wymagają od nas odniesienia się do komórek rozproszonych w arkuszu? Otóż tak!
Często tworzymy formatki, w których wyróżniamy kolorowym tłem określony typ komórek. Mam tu na myśli dane wejściowe, formuły, linki czy objaśnienia. Formuły zwykle są zablokowane, natomiast dane wejściowe są czyszczone. Excel posiada nawet wbudowane style, które pozwalają sformatować w konkretny sposób dany typ komórek.
Załóżmy, że formuły znajdują się w czterech różnych obszarach. Początkujący programiści napiszą cztery oddzielne linijki kodu. Ci bardziej doświadczeni, oprogramują tą operację w jednej linijce…
|
1 2 3 |
Arkusz1.Range("A1, A3:A5, B7, B12:B14").ClearContents |
Problem zliczania wierszy
Tematem, który rodzi pewne problemy jest zliczenie wszystkich wierszy i kolumn należących do zakresu nieciągłego.
Gdy dla powyższego zakresu spróbujemy zliczyć wiersze instrukcją Rows.Count, nie otrzymamy w wyniku 8 lecz… 1. W przypadku kolumn – Columns.Count będzie to nie 2… lecz także 1.
Co ciekawe, gdy zliczymy pojedyncze komórki – Cells.Count otrzymamy liczbę 8 – czyli tyle ile faktycznie jest komórek….
Niezły galimatias… Dlaczego tak się dzieje?
Excel traktuje każdy obszar (Area) jak oddzielny zakres typu Range.
Chcąc zliczyć wszystkie wiersze lub kolumny – musimy przebiec w pętli po wszystkich zakresach ciągłych. Powinniśmy zliczyć te informacje i dodać do zmiennej, która będzie sumować ten wynik narastająco. W przeciwnym razie, Excel zwróci nam wynik tylko dla pierwszego zakresu.
Kod makra
Poniżej wklejam makro, które w dużej mierze wyjaśnia, w jaki sposób Excel traktuje zakresy nieciągłe.
|
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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
Sub FindAreasInWorksheet() Dim rngSpecials As Range Dim rngArea As Range Dim lCounter As Long Dim sAddrArray() As String ' Makro sprawdza ile jest w arkuszu obszarów ciągłych dla wartości ' stałych. Przypisuje kolor wypełnienia dla zakresu na podstawie jego ' pozycji w kolekcji Areas, a także zmienia wartość dla całego zakresu ' Area. Sczytuje także adresy wszystkich zakresów ciągłych (Area) ' do tablicy, tak aby w przyszłości móc się do każego z nich łatwo odwołać. ' Ustawiam obsługę błędów ponieważ może się zdarzyć, że w arkuszu nie ma ' żadnych komórek, lub arkusz zawiera wyłącznie formuły. Przypisuję zmiennej ' rngSpecials wszystkie komórki z wartościami stałymi On Error GoTo ErrorHandle Set rngSpecials = Sheet1.UsedRange.SpecialCells(xlConstants) ' Sprawdzam czy zmienna rngSpecials faktycznie zawiera jakieś komórki. Jeżeli ' tak jest wówczas ustalam rozmiar tablicy, która będzie przechowywać każdy ' adres zakresu ciągłego. If Not rngSpecials Is Nothing Then ReDim sAddrArray(1 To rngSpecials.Areas.Count) ' Zmienna rngSpecials typu Range może być postrzegana jako kolekcja ' ciągłych zakresów w arkuszu. W tym celu wystarczy użyć słowa Areas. ' Tak naprawdę w pętli będziemy przechodzić po wszystkich ciągłych ' zakresach. Area to prostokątny i ciagły zakres komórek. For Each rngArea In rngSpecials.Areas ' pętla po zakresach lCounter = lCounter + 1 ' zwiększ licznik With rngArea sAddrArray(lCounter) = .Address ' sczytaj adres do tablicy .Value = lCounter ' zmień wartość dla zakresu .Interior.ColorIndex = lCounter + 1 ' zmień kolor wypełnienia End With Next rngArea ' koniec pętli End If ' W przypadku gdy wszystko poszło ok, makro zwalnia zmienne i kończy bieg. MacroExit: Set rngArea = Nothing Set rngSpecials = Nothing Exit Sub ' W przypadku gdy pojawił się jakiś błąd, makro wyświetla numer i opis błędu ' a następnie czyści zmienne i kończy bieg. ErrorHandle: MsgBox Err.Number & vbCr & Err.Description Resume MacroExit End Sub |
Screen
Plik XLSM
