Sprytne makra - ebook

Wstęp

Możliwości makr są praktycznie nieograniczone. Umiejętność programowania w VBA umożliwia nie tylko wyposażenie Excela w nowe funkcje, ale także pozwala na zautomatyzowanie najróżniejszych, czasochłonnych czynności. Dobrym przykładem jest wypełnianie wszelkiej maści formularzy, co jest żmudnym zajęciem. Dlatego pokazujemy, na przykładzie szablonu zaświadczenia o zarobkach, jak przygotować bardzo przydatne narzędzie służące do szybkiego wypełniania formularzy. Mając takie narzędzie, kilkoma kliknięciami myszy sporządzisz potrzebny dokument.

Istotnym elementem pracy w arkuszu kalkulacyjnym jest zaprezentowanie danych w tabelach w taki sposób, aby zawarte informacje były bardziej czytelne dla odbiorcy, a także żeby arkusz przedstawiał się efektownie. Do tego celu najczęściej stosowane jest formatowanie warunkowe. Narzędzie to ma jednak słabą stronę, ponieważ po zmianie danych w tabeli formatowanie zostaje zagubione i należy je ponownie definiować. W tym przypadku również bardzo pomocne są odpowiednie makra.

Tysiące kolumn i wierszy danych to problem, z którym mierzy się na co dzień wielu użytkowników Excela. Prawie zawsze pojawia się problem nawigacji po tak obszernych arkuszach. Paski przewijania nie są zbyt wygodne, ponieważ trudno jest ustawić odpowiedni obszar za pomocą myszy. Istnieją wprawdzie pewne niestandardowe rozwiązania, ale nie do końca spełniają oczekiwania użytkowników. Dlatego ponownie proponujemy sięgnąć po proste makra i tak zmodyfikować swój roboczy arkusz, aby praca z nim była szybsza i wygodniejsza.1. Formatowanie warunkowe z wykorzystaniem makr

Istotnym elementem pracy w arkuszu kalkulacyjnym jest zaprezentowanie danych w tabelach w taki sposób, aby zawarte informacje były bardziej czytelne dla odbiorcy, a także żeby arkusz przedstawiał się efektownie. Do tego celu najczęściej stosowane jest formatowanie warunkowe. Narzędzie to ma jednak słabą stronę, ponieważ po zmianie danych w tabeli formatowanie zostaje zagubione i należy je ponownie definiować. Można tego uniknąć, jeśli zastosujesz makro. W tym rozdziale wyjaśniamy, w jaki sposób to zrobić.

1.1. Naprzemienne formatowanie wierszy tabeli

Przyjmijmy, że jest zestawienie sprzedaży kilku produktów w różnych miesiącach roku, przy czym tabelę należy sformatować w taki sposób, aby zaznaczone kolorem były zawsze dwa sąsiadujące ze sobą wiersze, a dwa kolejne pozostały bez formatowania. Przykład ilustruje rysunek.

Rysunek 1.1. Dwa sąsiadujące wiersze zostały zaznaczone kolorem

Formatowanie warunkowe funkcjonuje w taki sposób, że obejmuje zawsze wszystkie dane w tabeli. Jeśli więc zostaną usunięte lub dodane dodatkowe wiersze, sposób formatowania nie ulegnie zmianie.

Kolejny pokazuje, jak zachowa się formatowanie zakresu komórek po dodaniu kilku dodatkowych wierszy w obrębie tabeli.

Rysunek 1.2. Efekt dodania wierszy

Takie automatyczne dopasowanie zakresu formatowania warunkowego do zmieniającego się arkusza często jest pożądane, ale bywa, że istotne jest utrzymanie formatowania tylko w określonym zakresie. Do tego celu należy wykorzystać makro.

1.2. Warunki formatowania za pomocą VBA

Informacje o sposobie formatowania każdej z komórek Excela przechowywane są w klasie FormatConditions. Za pomocą metody Add można nadać komórce (lub zakresowi komórek) nowe formatowanie warunkowe. Załóżmy, że chcesz je zdefiniować w komórce A1 aktywnego arkusza w taki sposób, że jeśli wpisana w niej wartość będzie równa 50, zostanie wyświetlona na czerwonym tle i wytłuszczona czcionką (boldem). Dla takich założeń zastosuj metodę Add w następujący sposób:

ActiveSheet.Range(„Al”).FormatConditions.Add

Type:=xlCellValue, Operator:=xlEqual, Formula1:=50

Jak widać, metodzie Add podałeś trzy właściwości. Pierwsza (Type) określa, czy formatowanie warunkowe sprawdzać będzie wartość w komórce, czy zapisaną w niej formułę. Z pewnością rozpoznajesz te określenia z ręcznego definiowania formatowania warunkowego w Excelu. Po uruchomieniu okna dialogowego formatowania Excel pozwala wybrać jeden z dwóch typów warunków: Wartość komórki jest lub Formuła jest. Poprzez nadanie właściwości Type wartości xlCellValue określasz tym samym opcję Wartość komórki jest. Excel będzie wówczas porównywał zawartość komórki z wartością porównawczą określoną w dalszej części makropolecenia.

We właściwości Operator będziesz definiował, w jaki sposób porównywana będzie zawartość komórki z wartością porównawczą. W przykładzie zastosowano wartość xlEqual, co oznacza równa. Można oczywiście zastosować jeden z innych dostępnych operatorów, które przedstawia tabela.

Tabela 1.1. Opis operatorów

---------------- --------------------
Operator Znaczenie
xlBetween Między
xlEqual równa
xlGreater większy niż
xlGreaterEqual większa lub równa
xlLess mniejsza niż
xlLessEqual mniejsza lub równa
xlNotBetween nie między
xlNotEqual nie równa
---------------- --------------------

Ostatnia właściwość Formula1 pozwala na podanie wartości porównawczej, którą Excel przyrównywał będzie do zawartości komórki. Założenia przykładu mówią o zmianie formatowania komórki, w momencie gdy zawierać będzie ona wartość 50, stąd też właśnie tę liczbę należy wprowadzić we właściwości Formula1.

1.3. Porównanie zawartości komórki z dwiema wartościami

Jeśli w formatowaniu warunkowym sprawdzana będzie wartość wpisana w komórce, należy we właściwości Type zawsze używać parametru xlCellValue. Poprzedni przykład prezentował sprawdzenie warunku polegającego na porównaniu zawartości komórki z jedną wartością. Jeśli natomiast zamiast operatora xlEual (czyli równa) użyjesz operatora wymagającego porównania z dwiema wartościami (np. xlBetween lub xlNotBetween), musisz także zdefiniować w jakiś sposób drugą wartość porównawczą. Zrobisz to za pomocą właściwości Formula2. Przedstawiony dalej fragment makropolecenia przedstawia taką sytuację. Według podanego kodu w formatowaniu warunkowym sprawdzane będzie, czy wartość w komórce A2 leży pomiędzy wartościami 50 a 100.

ActiveSheet.Range(“A2”).FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=50, Formula2:=100

Oczywiście zdefiniowane za pomocą VBA formatowanie musi odpowiadać dokładnie temu, co można wyznaczyć w oknie z warunkami formatowania. Aby to sprawdzić, uruchom przedstawione makropolecenie, a następnie wywołaj w Excelu okno dialogowe formatowania warunkowego. Będzie ono wyglądało następująco:

Rysunek 1.3. Ten warunek został zdefiniowany za pomocą VBA

1.4. Zarządzanie formatowaniami warunkowymi

Jeśli przy korzystaniu z metody Add zdefiniujesz dla wybranej komórki lub zakresu własne formatowanie warunkowe, może się zdarzyć, że wcześniej już zostały dla tego zakresu zdefiniowane inne warunki. Aby więc uniknąć sytuacji, gdy kolejne warunki są ze sobą sprzeczne lub to samo sprawdzenie wykonywane będzie wielokrotnie, należy najpierw zadbać o wyczyszczenie poprzednich warunków nałożonych na daną komórkę lub zakres.

W prosty sposób wykonasz to za pomocą metody Delete. Dzięki temu będziesz miał pewność, że definiowane formatowanie warunkowe dotyczyć będzie „czystego” obszaru. Poniższy fragment kodu makropolecenia demonstruje zastosowanie metody Delete dla komórki A1.

ActiveSheet.Range(“A1”).FormatConditions.Delete

Darmowy fragment