W naszym kursie VBA przedstawiliśmy już podstawowe operacje związane z przetwarzaniem zakresów, pobieraniem danych etc. Nadszedł czas, aby pomyśleć o wydajności. Nie jest bowiem sztuką przetworzenie każdej komórki w zaznaczeniu - jeśli zaznaczymy całą kolumnę w Excelu 2007 - mamy do przetworzenia milion komórek, a jeśli kilka kolumn - kilka milionów komórek. To wszystko zabiera zbyt dużo czasu. Pora zatem na wydajne przetworzenie zakresów.
Nie zawsze piszemy makro, które ma wykonać akcję na całym zaznaczonym zakresie - częściej jest tak, że wśród zaznaczonych komórek poszukujemy najpierw pewnych wartości, zanim zastosujemy na nich jakieś działanie. Np. wśród wszystkich zaznaczonych komórek poszukujemy tylko wartości liczbowych aby np. te ujemne zaznaczyć w specjalny sposób.
Idea jest następująca: zamiast przetwarzać wszystkie komórki w zaznaczeniu/na wejściu naszego makra, spróbujmy ograniczyć liczbę komórek do tych potencjalnie przeznaczonych do przetworzenia.
Znacie polecenie arkusza Przejdź do - specjalnie...? Umożliwia ono zaznaczenie w danym obszarze komórek tylko z komentarzami lub tylko z wartościami liczbowymi lub pustych itd. (zachęcam tych z Was, którzy z tym nie pracowali do eksperymentów - to naprawdę bardzo użyteczne narzędzie). Właśnie ten mechanizm wykorzystamy poprawienia wydajności naszych makr.
Załóżmy, że do danych wejściowych jak na poniższym rysunku, chcemy zastosować makro, które podkreśli i pokoloruje te liczby z kolumny zawierającej Wartość, które są ujemne.
Cały zakres zaznaczony za pomocą właściwości CurrentRegion lub UsedRange będzie przetwarzany przez makro. Ale już na pierwszy rzut oka widać, że przecież nie wszystkie komórki musimy przetwarzać: możemy przecież pominąć komórki puste i te, które zawierają tekst. Mamy zatem pomysł jak pominąć zbędne komórki, czas na praktyczną realizajcę.
Sub ZaznaczUjemne() Dim KomorkiStale As Range Dim PojedynczaKomorka As Range On Error Resume Next Set KomorkiStale = ActiveSheet.UsedRange. SpecialCells(xlCellTypeConstants, xlNumbers) On Error GoTo 0 If Not KomorkiStale Is Nothing Then For Each PojedynczaKomorka In KomorkiStale If PojedynczaKomorka.Value < 0 Then With PojedynczaKomorka.Font .ColorIndex = 3 .Underline = xlUnderlineStyleSingle End With End If Next PojedynczaKomorka End If End Sub
Wykorzystaliśmy tutaj metodę SpecialCells będącą odpowiednikiem narzędzie Przejdź do - specjalnie.... Jej składnia jest następująca:
SpecialCells(Typ, Wartość)
gdzie:
- Typ - typ komórek jaki chcemy zidentyfikować.
- Wartość - (opcjonalny) określa jakich wartości szukamy.
Na końcu tego artykułu podaję listę wszystkich typów i wartości dla tej metody.
Konstrukcja On Error Resume Next... On Error GoTo 0 służy zapobieganiu występieniu błędu makra, kiedy metoda SpecialCells nie znajdzie żadnych komórek spełniających naszego kryterium. Musimy się również zabezbieczyć na wypadek, gdyby zakres KomorkiStale okazał się pusty, stosując instrukcję If Not KomorkiStale Is Nothing Then... End If. Następnie przeglądamy komórkę po komórce naszego podzbioru (For Each PojedynczaKomorka in KomorkiStale... Next PojedynczaKomorka) i po wystąpieniu wartości ujemnej podkreślamy ją i zaznaczamy na czerwono.
Gdzie jest nasz zysk na wydajności? Gdybyśmy przeglądali cały zakres początkowy, mielibyśmy do sprawdzenia 39 komórek, natomiast jeśli ograniczymy się tylko do stałych liczbowych, będziemy mieć tylko 22 komórki liczbowe. Nasz zysk to ponad 43% mniej pracy. Dla tak małego zakresu to prawie nic, ale dla arkuszy z kilkunastotysięcznymi wierszami to naprawdę wiele. Sprawdzcie sami.
Na zakończenie podaję listę wszystkich stałych określających typy komórki oraz ich wartości.
Stałe typów komórek
- xlCellTypeAllFormatConditions Komórki z dowolnym formatowaniem (-4172)
- xlCellTypeAllValidation Komórki z kryteriami walidacji (-4174)
- xlCellTypeBlanks Puste komórki (4)
- xlCellTypeComments Komórki z komentarzami (-4144)
- xlCellTypeConstants Komórki zawierające stałe (a nie formuły) (2)
- xlCellTypeFormulas Komórki z formułami (-4123)
- xlCellTypeLastCell Ostatnia komórka w bieżącym zakresie danych (11)
- xlCellTypeSameFormatConditions Komórki tak samo sformatowane (-4173)
- xlCellTypeSameValidation Komórki mające takie same kryteria walidacji (-4175)
- xlCellTypeVisible Wszystkie widoczne komórki (12)
Stałe wartości
- xlErrors Tylko błędy (16)
- xlLogical Wartości logiczne (4)
- xlNumbers Liczby (1)
- xlTextValues Tekst (2)