Redakcja

Autorzy:

Katarzyna Kaczanowska, Piotr Dynia

Kierownik grupy wydawniczej:

Agnieszka Konopacka-Kuramochi

Wydawca:

Weronika Wota

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład i łamanie:

Norbert Bogajczyk

Projekt okładki:

Magdalena Huta

Druk: Miller

ISBN: 978-83-269-6392-6

Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o.

Warszawa 2017

Wydawnictwo Wiedza i Praktyka sp. z o.o.

03-918 Warszawa, ul. Łotewska 9a

tel. 22 518 29 29, faks 22 617 60 10

NIP: 526-19-92-256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

„Sprytne makra” wraz z przysługującymi Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w książce „Sprytne makra” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Publikacja „50 praktycznych formuł na każdą okazję” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Sprytne makra” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Sprytne makra” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przypadków.

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

Aby mieć pewność, że unikniesz konfliktu przy definiowaniu warunków formatowania, zawsze używaj tej linii na początku kodu swojego makropolecenia.

1.5. Definiowanie formatu komórki po spełnieniu warunków

Wiesz już, jak w kodzie makropolecenia zapisywać warunki, które muszą być spełnione, aby zastosowana została zmiana formatowania komórki. Po oprogramowaniu warunków formatowania czas teraz na właściwe działanie, czyli nadanie komórce odpowiedniego formatu. Jeśli chciałbyś zmienić czcionkę w komórce (zestaw znaków, wyróżnienie, kolor itp.), użyj właściwości Font. Żeby wpłynąć na kolor tła lub deseń komórki, skorzystaj z właściwości Interior.

W przykładzie użytym w tym tekście założono, że jeśli w komórce A1 pojawi się wartość 50, powinna ona zostać wyświetlona na czerwonym tle i wytłuszczoną czcionką (boldem). Założenia takie zrealizujesz następującym makropoleceniem:

Sub A1CzerwonaBold()

With ActiveSheet.Range(“A1”)

.FormatConditions.Delete

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=50

.FormatConditions(1).Font.Bold = True

.FormatConditions(1).Interior.ColorIndex = 3

End With

End Sub

Uruchom teraz to makropolecenie, żeby sprawdzić poprawność działania. Dopóki w komórce A1 będzie cokolwiek innego niż liczba 50, dopóty formatowanie nie zostanie zmienione. Wprowadzenie i zatwierdzenie liczby 50 spowoduje natychmiastową zmianę formatowania komórki, co przedstawia rysunek.

Rysunek 1.4. Po wprowadzeniu wartości 50 zmienia się formatowanie

1.6. Poprawne przyporządkowywanie kolorów

Jak z pewnością zauważyłeś w kodzie makra, we właściwości Font ustawiony został parametr Bold na wartość True (czyli Prawda). Skutkowało to zmianą czcionki w komórce na pogrubioną.

Aby spowodować zmianę koloru tła komórki, musisz użyć we właściwości Interior parametru ColorIndex wraz z odpowiednią wartością reprezentującą jeden z dostępnych kolorów. Wartość 3 odpowiada kolorowi czerwonemu w standardowej palecie kolorów Excela. Zawiera 56 kolorów, których użycie wiąże się z zastosowaniem odpowiedniego numeru koloru w parametrze ColorIndex. Aby wylistować wszystkie dostępne kolory wraz z odpowiadającymi im wartościami liczbowymi, skorzystaj z następującego makropolecenia:

Sub PokazKolory()

Dim i As Integer

With Workbooks.Add.Worksheets(1)

For i = 1 To 56

.Cells(i, 1) = i

.Cells(i, 2).Interior.ColorIndex = i

Next i

End With

End Sub

Makro utworzy nowy skoroszyt, w którym wyświetlone zostaną wszystkie dostępne kolory standardowej palety kolorów Excela.

Rysunek 1.5. Dostępne kolory zostały wylistowane

W kolumnie A znajdą się kolejne liczby, natomiast w kolumnie B komórki w odpowiadających liczbom kolorach tła. Kolejny rysunek prezentuje fragment utworzonego dokumentu. Jeśli zastosujesz teraz we właściwości ColorIndex odpowiednią wartość liczbową, otrzymasz żądany kolor tła komórki.

1.7. Stosowanie formuł w formatowaniu warunkowym

W formatowaniu warunkowym można sprawdzać nie tylko wartości wpisane w komórkach, ale także formuły. Zastosowanie zdefiniowanego formatowania nastąpi wówczas, gdy wynik działania formuły spełni zadany warunek. Aby zbudować warunek oparty na formule, wstaw we właściwości Type parametr xlExpression zamiast xlCellValue.

W przypadku stosowania warunku związanego z formułą nie należy używać właściwości Operator.

Przedstawione dalej makropolecenie definiuje formatowanie warunkowe polegające na zmianie czcionki w komórce A1 na wytłuszczoną oraz tła na czerwone, jeśli w komórce B1 pojawi się wartość 50.

Sub A1CzerwonaBoldGdyB1()

With ActiveSheet.Range(“A1”)

.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, Formula1:=”=$B$1=50”

.FormatConditions(1).Font.Bold = True

.FormatConditions(1).Interior.ColorIndex = 3

End With

End Sub

Najważniejszym elementem kodu, odróżniającym go od poprzedniego przykładu, jest sposób podania formuły w metodzie Add. Zastosowana została formuła:

=$B$1=50

sprawdzająca zgodność zawartości komórki B1 (używaj adresowania bezwzględnego) z wartością 50.

1.8. Definiowanie formatowania warunkowego dla zakresów

Przy definiowaniu formatowania warunkowego za pomocą VBA można wygodnie określić, na jakim zakresie powinno ono funkcjonować.

Dzięki temu łatwo nadasz różne formatowania dla kilku różnych zakresów komórek. Poniższy kod dotyczy formatowania zakresu komórek A3:F20:

Sub ZakresA3F20()

With Worksheets(“Formatowanie”).Range(“A3:F20”)

.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, Formula1:=”=LUB(MOD(WIERSZ(A3);4)=2;MOD(WIERSZ(A3);4)=1)”

.FormatConditions(1).Interior.ColorIndex = 2

.FormatConditions.Add Type:=xlExpression, Formula1:=”=LUB(MOD(WIERSZ(A3);4)=0;MOD(WIERSZ(A3);4)=3)”

.FormatConditions(2).Interior.ColorIndex = 35

End With

End Sub

W tym makrze użyte zostały dwa warunki formatowania. Pierwszy dotyczy komórek, które powinny mieć białe tło (ColorIndex=2). Drugi związany jest natomiast z formatowaniem tła komórek na kolor jasnozielony (ColorIndex=35).

Zastosowane formuły działają na następujących zasadach:

•funkcja WIERSZ odczytuje numer wiersza bieżącej komórki;

•za pomocą funkcji MOD obliczana jest reszta z dzielenia numeru wiersza przez 4;

•jeśli wynik działania funkcji MOD równy jest 1 lub 2, będziesz miał do czynienia z wierszami o numerach 5, 6, 9, 10, 13, 14 itd., czyli dwa co drugie wiersze w arkuszu;

•jeśli działanie tej formuły jest spełnione, tła komórek będą miały biały kolor.

Drugi warunek działa analogicznie, z tym że wynik reszty z dzielenia przyrównywany jest do wartości 0 lub 3. Dzięki temu możliwe jest wyznaczenie wierszy o numerach 3, 4, 7, 8, 11, 12 itd., w których komórki otrzymają tło jasnozielone. Ponieważ adres komórki A3 podany został jako odniesienie względne, Excel w każdym kolejnym obliczeniu dopasuje adres do aktualnego wiersza w całym zakresie A3:F20. Dzięki temu makropolecenie działa tak, jak powinno: formatuje bloki dwóch wierszy naprzemiennie – dwa białe i dwa jasnozielone. Zdefiniowane w ten sposób formatowanie warunkowe funkcjonuje poprawnie, również jeśli w zakresie tabeli zostaną usunięte lub dodane nowe wiersze.

2. Sprytne zaznaczanie komórek

Zaznaczenie komórek według nietypowych kryteriów może sprawić sporą trudność. W tym rozdziale pokazujemy, jak poradzić sobie z tego typu problemami.

2.1. Zaznaczanie komórek zawierających określoną wartość

Mamy zestawienie kosztów wydziałowych w stosunku do kosztów robocizny w poszczególnych działach na przełomie roku. Niektóre komórki, dla których nie określono wielkości, zostały oznaczone wpisem b.d.

Rysunek 2.1. Zestawienie wysokości kosztów wydziałowych w poszczególnych działach

Chcesz wyróżnić komórki, do których wpisano tekst b.d., wypełnieniem tła w kolorze szarym. W obszernym zestawieniu trudno jest wyszukać takie komórki, a zmiana wypełnienia pojedynczych komórek jest dosyć pracochłonna. Makro, które zaprezentujemy w dalszej części tego rozdziału, jest pomocne właśnie w takich przypadkach. Narzędzie można wykorzystać w różny sposób, ponieważ efektem jego działania jest zaznaczanie komórek o określonej wartości.

Otwórz Edytor Visual Basic, wstaw nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczRówne()

Dim i As Long

Dim wartość As Variant

Dim zakres1 As Range

Dim zakres2 As Range

wartość = InputBox(„Podaj szukaną wartość”)

If wartość = “” Then Exit Sub

If IsNumeric(wartość) Then wartość = wartość * 1

Set zakres1 = Application.Intersect(Selection, ActiveSheet.UsedRange)

For i = 1 To zakres1.Count

If zakres1(i) = wartość Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1(i)

Else

Set zakres2 = Application.Union(zakres2, zakres1(i))

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych komórek.”

Else

zakres2.Select

End If

End Sub

Wykorzystujemy tutaj cztery zmienne: licznik pętli, zmienną przechowującą wartość, jaką mają mieć komórki, zmienną zakres1 zawierającą sprawdzany zakres komórek i zmienną zakres2 przechowującą obszar komórek spełniających kryteria. Na początku makra wywołujemy okno do wprowadzania szukanej wartości i podaną wartość przypisujemy do zmiennej wartość. Jeśli użytkownik nie wpisał żadnej wartości lub nacisnął przycisk Cancel, to kończymy wykonanie makra poprzez wywołanie instrukcji Exit Sub.

Jeśli użytkownik wpisał tekst przypominający wartość liczbową, to w następnej instrukcji wartość zmiennej wartość modyfikujemy na wartość liczbową poprzez pomnożenie jej przez liczbę 1. W kolejnej instrukcji do zmiennej zakres1 przypisujemy zakres komórek stanowiący część wspólną zakresu zaznaczonego przed uruchomieniem makra i bieżącego obszaru w arkuszu.

Bieżący obszar określony przez właściwość UsedRange to ciągły zakres wszystkich komórek, które zawierają jakąś wartość lub formatowanie.

W dalszej części makra utworzona jest pętla, wewnątrz której po kolei wszystkie komórki w zakresie określonym przez zmienną zakres1 sprawdzane są pod kątem zawartości. Jeśli wartość komórki jest równa zawartości zmiennej wartość, to w kolejnej instrukcji powiększany jest zakres zmiennej zakres2 o tę komórkę dzięki zastosowaniu metody Union (umożliwiającej tworzenie nieciągłych zakresów danych). Na końcu makra wyświetlana jest informacja o braku komórek spełniających kryterium lub zaznaczane są znalezione komórki.

Jeśli chcesz zaznaczyć szarym wypełnieniem tła komórki z tekstem b.d. w arkuszu zawierającym wielkość kosztów wydziałowych, to podświetl zakres B3:M12 i uruchom makro ZaznaczRówne. Pojawi się okno do wpisania szukanej wartości widoczne na rysunku. Wpisz w nim tekst, który ma zawierać komórka: b.d. i naciśnij OK.

Rysunek 2.2. Okno do wpisywania szukanej wartości

Następnie makro wykona pozostałe instrukcje i zaznaczy wszystkie komórki w podanym obszarze, które mają wartość zgodną z podaną. Efekt zastosowania narzędzia prezentuje rysunek.

Rysunek 2.3. Arkusz po modyfikacji

Jeśli chcesz, aby pojawiła się informacja o liczbie znalezionych komórek spełniających kryterium, to na końcu makra tuż za linią z instrukcją:

zakres2.Select

dodaj linię:

MsgBox „Znaleziono „ & zakres2.Count & „ komórek.”

Po zaznaczeniu komórek pojawi się komunikat jak na rysunku.

Rysunek 2.4. Informacja o liczbie znalezionych komórek spełniających kryteria

Aby używać symboli wieloznacznych w oknie do wpisywania szukanej wartości, zmodyfikuj linię, w której sprawdzasz, czy dana komórka ma tę samą wartość co zmienna. Zastąp znak równości operatorem Like:

If zakres1(i) Like wartość Then

W przypadku takiej modyfikacji można w oknie dialogowym wpisać np. tekst: b* i także zostaną zaznaczone komórki zawierające tekst b.d.

W Excelu można wykorzystać dwa podstawowe znaki wieloznaczne: symbol gwiazdki (*) zastępuje dowolny ciąg znaków, a znak zapytania (?) – pojedynczy znak.

Jeśli chcesz, aby przy szukaniu wartości tekstowych wielkość liter nie miała znaczenia, to w linii sprawdzającej wartość komórki użyj funkcji LCase. Jej zadaniem jest zamiana wszystkich liter na małe:

If LCase(zakres1(i)) = LCase(wartość) Then

2.2. Zaznaczanie komórek zawierających wartość większą lub równą zadanej wartości

W arkuszu, z którego korzystaliśmy w poprzednim rozdziale, chcesz teraz zaznaczyć te komórki, które zawierają koszty wydziałowe na poziomie większym lub równym 100%. W takim przypadku warto także zastosować makro.

Rysunek 2.5. Zestawienie kosztów wydziałowych dla poszczególnych komórek

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczWiększeRówne()

Dim i As Long

Dim wartość As Variant

Dim zakres1 As Range

Dim zakres2 As Range

wartość = InputBox(„Podaj minimalną wartość szukanych komórek”)

If wartość = “” Then Exit Sub

If IsNumeric(wartość) Then

wartość = wartość * 1

Else

MsgBox „Nie wpisano wartości liczbowej”, vbExclamation

Exit Sub

End If

Set zakres1 = Application.Intersect(Selection, ActiveSheet.UsedRange)

For i = 1 To zakres1.Count

If zakres1(i) >= wartość And IsNumeric(zakres1(i)) And Not IsEmpty(zakres1(i)) Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1(i)

Else

Set zakres2 = Application.Union(zakres2, zakres1(i))

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych komórek.”

Else

zakres2.Select

MsgBox „Znaleziono „ & zakres2.Count & „ komórek.”

End If

End Sub

W tym kodzie – w porównaniu do przedstawionego w poprzednim rozdziale – wprowadzono kilka modyfikacji. Oprócz zmiany tekstu komunikatu wyświetlanego w oknie, do którego wpisywana będzie wartość, użyto dodatkowego sprawdzenia, czy wpisaną wartość można uznać za wartość liczbową. Jeśli nie, to wyświetlana będzie informacja o tym, że nie wpisano liczby i dalsze instrukcje makra nie będą wykonywane. Ponadto zamiast sprawdzenia, czy wartość komórki jest równa zawartości zmiennej wartość, w tym makrze wprowadzone są trzy warunki: czy wartość komórki jest równa lub większa niż zawartość zmiennej wartość, czy sprawdzana komórka zawiera wartość liczbową oraz czy komórka nie jest pusta. Po zakończeniu makra zaznaczone będą tylko komórki, które spełniają wszystkie warunki.

Rysunek 2.6. Wpisywanie minimalnej wartości, jaką powinny mieć komórki

Jeśli chcesz zaznaczyć na pomarańczowo komórki z kosztami wydziałowymi większymi lub równymi 100%, to zaznacz komórki B3:M12 i uruchom makro ZaznaczWiększeRówne.

Pojawi się okno do wpisania minimalnej wartości. Wpisz w nim liczbę odpowiadającą 100%: czyli 1 i naciśnij OK.

W oknie dialogowym należy wpisać liczbę. W przypadku zastosowania odpowiedniego formatu liczbowego komórki, np. wartości procentowej, w oknie należy wpisać liczbę odpowiadającą sformatowanej wartości.

Rysunek 2.7. Arkusz po zmianie koloru wypełniania komórek

Makro zaznaczy wszystkie komórki w zaznaczonym obszarze danych, które mają wartość większą lub równą 100%. Po zakończeniu działania narzędzia można wybrać polecenie zmiany wypełniania tła komórki.

Jeśli po zakończeniu działania makra chcesz pokazywać także informację o liczbie znalezionych komórek, to na końcu makra tuż za linią z instrukcją:

zakres2.Select

dodaj linię:

MsgBox „Znaleziono „ & zakres2.Count & „ komórek.”

Aby makro zaznaczało komórki, które mają wartość mniejszą niż wpisana do okna dialogowego, to zmień nazwę makra w pierwszej linii kodu tak, aby oddawała aktualną funkcję:

Sub ZaznaczMniejsze()

Następnie dostosuj tekst komunikatu okna dialogowego:

wartość = InputBox(„Podaj wartość, poniżej której mają mieć szukane komórki”)

i później zmień operator porównawczy wartości komórki i zmiennej wartość:

If zakres1(i) < wartość And IsNumeric(zakres1(i)) And Not IsEmpty(zakres1(i)) Then

2.3. Zaznaczanie komórek zawierających ustawienia sprawdzenia poprawności danych

Przyjmijmy, że przesłano zestawienie faktur przedstawione na rysunku.

Rysunek 2.8. Zestawienie faktur

Chcesz wiedzieć, które komórki mają nałożone reguły sprawdzania poprawności danych w komórce. Program Excel nie ma wbudowanego polecenia do tego typu operacji, a zaznaczanie po kolei każdej komórki osobno i wybieranie polecenia sprawdzania poprawności danych jest żmudnym zajęciem. Użyj zatem makra.

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczSprawdzaniePoprawności()

Dim i As Long

Dim zakres1 As Range

Dim zakres2 As Range

Set zakres1 = ActiveSheet.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

On Error Resume Next

For i = 1 To zakres1.Count

If IsError(zakres1(i).Validation.Type) Then

Else

If zakres2 Is Nothing Then

Set zakres2 = zakres1(i)

Else

Set zakres2 = Application.Union(zakres2, zakres1(i))

End If

End If

Next

On Error GoTo 0

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych komórek.”

Else

zakres2.Select

End If

End Sub

Struktura kodu jest podobna do przedstawionych w poprzednich dwóch rozdziałach. Właściwość UsedRange zawiera obszar niepustych komórek, a sprawdzanie poprawności danych może być ustawione także dla komórki, która nie ma wartości. Dlatego korzystamy z innego sposobu i obszar zaznaczonych komórek ograniczamy do fragmentu arkusza zawierającego się pomiędzy komórką A1 a ostatnią komórką.

Ostatnia komórka to taka, która znajduje się najdalej od komórki A1 i była kiedykolwiek wykorzystywana w tym arkuszu. Jeśli chcesz sprawdzić, jaka aktualnie jest ostatnia komórka w arkuszu, to na karcie Narzędzia główne, w grupie poleceń Edycja wybierz Znajdź i zaznacz/Przejdź do – specjalnie), w oknie Przechodzenie do – specjalnie wybierz opcję Ostatnia komórka i naciśnij OK.

Przed pętlą wpisana jest instrukcja wyłączająca obsługę błędów wykonania kodu, a zaraz po zakończeniu pętli obsługa ponownie jest uaktywniana. Wewnątrz pętli sprawdzane są poszczególne komórki pod kątem tego, czy nie zawierają reguł sprawdzania poprawności danych. Gdy komórka spełni to kryterium, to zostanie dopisana do zakresu komórek zawartych w zmiennej zakres2.

Jeśli chcesz zaznaczyć komórki zawierające ustawienia sprawdzania poprawności danych, to podświetl cały arkusz, np. poprzez kliknięcie spojenia nagłówków wierszy i kolumn, a następnie uruchom makro ZaznaczSprawdzaniePoprawności.

W arkuszu zostaną zaznaczone komórki w kolumnie G, które mają nałożoną regułę (listę rozwijaną).

Aby po zakończeniu działania makra wyświetlić informację o liczbie znalezionych komórek, to na końcu kodu tuż za linią z instrukcją:

zakres2.Select

dodaj linię:

MsgBox „Znaleziono „ & zakres2.Count & „ komórek.”

Jeśli chcesz, aby makro sprawdzało cały obszar komórek zaznaczonych przed uruchomieniem makra, a nie tylko obszar ograniczony ostatnią komórką, to w linii, w której przypisujesz obszar komórki do zmiennej zakres1, dodaj obiekt Selection:

Set zakres1 = Selection

W przypadku takiej modyfikacji musisz pamiętać, aby przed uruchomieniem makra nie były zaznaczone wszystkie komórki czy nawet całe wiersze lub kolumny, ponieważ makro w takim wypadku będzie długo działać.

Rysunek 2.9. Arkusz po uruchomieniu makra

2.4. Zaznaczanie komórek zawierających formatowanie warunkowe

Przyjmijmy, że w arkuszu przedstawionym w poprzednim rozdziale chcesz odszukać komórki, które zawierają formatowanie warunkowe. Podobnie jak poprzednio można ułatwić sobie to zadanie dzięki zastosowaniu makra.

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczFormatowanieWarunkowe()

Dim i As Long

Dim zakres1 As Range

Dim zakres2 As Range

Set zakres1 = ActiveSheet.Range(ActiveSheet.Range(“A1”),ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

For i = 1 To zakres1.Count

If zakres1(i).FormatConditions.Count > 0 Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1(i)

Else

Set zakres2 = Application.Union(zakres2, zakres1(i))

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych komórek.”

Else

zakres2.Select

End If

End Sub

Rysunek 2.10. Arkusz z danymi faktur, w którym chcesz znaleźć komórki zawierające formatowanie warunkowe

Tutaj, w porównaniu do kodu z poprzedniego rozdziału, nie ma potrzeby wyłączać obsługi błędów w czasie instrukcji wykonywanych w pętli.

Sprawdzanie, czy dana komórka zawiera formatowanie warunkowe, odbywa się za pomocą właściwości Count obiektu FormatConditions. Jeśli jest równy zero, to nie zostały dla tej komórki ustawione żadne kryteria formatowania warunkowego.

Jeśli chcesz sprawdzić, które komórki zawierają formatowanie warunkowe, to zaznacz cały arkusz i uruchom makro ZaznaczFormatowanieWarunkowe.

Zostaną podświetlone komórki w kolumnie F z nałożoną regułą formatowania dat.

Rysunek 2.11. Arkusz po uruchomieniu makra

Aby wyświetlać informację o liczbie znalezionych komórek po zaznaczeniu, to na końcu makra, tuż za linią z instrukcją:

zakres2.Select

dodaj linię:

Set zakres1 = Selection

W przypadku takiej modyfikacji musisz pamiętać, aby przed uruchomieniem makra nie były zaznaczone wszystkie komórki czy nawet całe wiersze lub kolumny, ponieważ makro będzie długo działać.

2.5. zaznaczanie niezablokowanych komórek

Pracujesz w arkuszu z danymi faktur i chcesz wprowadzić ochronę arkusza. Nie wiesz jednak, które komórki są niezablokowane. Taka informacja okaże się pomocna, aby niepotrzebnie nie udostępniać niektórych komórek do edycji oraz nie próbować ponownie zmieniać opcji Zablokuj dla komórek, które chcesz udostępnić do edycji. Do takiej operacji wykorzystaj makro.

Rysunek 2.12. Zestawienie faktur

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczNiezablokowane()

Dim i As Long

Dim zakres1 As Range

Dim zakres2 As Range

Set zakres1 = ActiveSheet.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

For i = 1 To zakres1.Count

If zakres1(i).Locked = False Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1(i)

Else

Set zakres2 = Application.Union(zakres2, zakres1(i))

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych komórek.”

Else

zakres2.Select

End If

End Sub

Kod jest bardzo podobny do poprzednio przedstawionych makr. Jedyna zmiana polega na tym, że za pomocą pętli każda komórka jest sprawdzana pod kątem tego, czy jej właściwość Locked jest ustawiona na wartość False. Jeśli tak, to komórka będzie niezablokowana do edycji, w przypadku gdy zostanie założona ochrona arkusza. Gdy sprawdzana komórka jest niezablokowana, makro spowoduje, że komórka ta powiększa zakres zmiennej zakres2.

Jeśli chcesz znaleźć w arkuszu z danymi faktur wszystkie komórki, które nie będą zablokowane do edycji po założeniu ochrony arkusza, to podświetl cały arkusz i uruchom makro ZaznaczNiezablokowane. Po uruchomieniu makra zostaną zaznaczone wszystkie komórki, które mają ustawioną niezaznaczoną opcję Zablokuj.

Aby pojawiała się informacja o liczbie znalezionych komórek po zaznaczeniu, to na końcu makra tuż za linią z instrukcją:

zakres2.Select

dodaj linię:

MsgBox „Znaleziono „ & zakres2.Count & „ komórek.”

Rysunek 2.13. Arkusz po zaznaczeniu niezablokowanych komórek

Jeśli chcesz zaznaczyć komórki zablokowane, to w linii, w której sprawdzana jest wartość zmiennej Locked, zamiast wartości False podaj wartość True:

If zakres1(i).Locked = True Then

Dodatkowo zmień nazwę makra:

Sub ZaznaczZablokowane()

aby opisywała aktualne działanie makra.

Jeżeli sprawdzany ma być cały obszar komórek zaznaczonych przed uruchomieniem makra, a nie tylko obszar ograniczony ostatnią komórką, to na początku kodu w linii, w której przypisujesz obszar komórki do zmiennej zakres1, przypisz obiekt Selection:

Set zakres1 = Selection

W przypadku takiej modyfikacji musisz pamiętać, aby przed uruchomieniem makra nie były zaznaczone wszystkie komórki czy nawet całe wiersze czy kolumny, ponieważ makro będzie długo działać.

2.6. Zaznaczanie pustych wierszy

Jest arkusz zawierający budżet roczny z rozbiciem na poszczególne składniki oraz kwartały. Poszczególne grupy rozdzielone są pustymi wierszami. Potrzebujesz je usunąć. Sprawdzenie, czy w danym wierszu rzeczywiście nie ma żadnych wypełnionych komórek, jest dosyć trudne, dlatego przedstawimy pomocne makro, które zaznaczy wszystkie puste wiersze w zestawieniu.

Rysunek 2.14. Fragment arkusza z budżetem rocznym jednostki

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczPusteWiersze()

Dim i As Long

Dim zakres1 As Range

Dim zakres2 As Range

Set zakres1 = ActiveSheet.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

For i = 1 To zakres1.Rows.Count

If WorksheetFunction.CountA(zakres1.Rows(i).EntireRow) = 0 Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1.Rows(i).EntireRow

Else

Set zakres2 = Application.Union(zakres2, zakres1.Rows(i).EntireRow)

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych wierszy.”

Else

zakres2.Select

End If

End Sub

Na początku kodu przypisujemy do zmiennej zakres1 obszar komórek znajdujący się pomiędzy A1 a ostatnią komórką arkusza.

Ostatnia komórka to komórka znajdująca się najdalej od komórki A1, która była kiedykolwiek wykorzystywana w tym arkuszu.

Następnie w pętli sprawdzane są poszczególne wiersze tego obszaru po kątem liczby niepustych komórek. W przypadku gdy liczba ta określona za pomocą metody CountA jest równa 0, to zakres komórek przechowywany w zmiennej zakres2 jest powiększany o taki wiersz. Na końcu makra zaznaczane są komórki znajdujące się wewnątrz zakresu określonego przez zmienną zakres2. W przypadku gdy nie zostały znalezione żadne puste wiersze, wyświetlana jest odpowiednia informacja.

Jeśli chcesz usunąć puste wiersze w arkuszu budżetu, to zaznacz dowolną komórkę i uruchom makro ZaznaczPusteWiersze. Zostaną zaznaczone wszystkie puste wiersze znajdujące się wewnątrz zestawienia. Puste wiersze znajdujące się pod ostatnimi wypełnionymi komórkami nie będą podświetlone.

Aby usunąć wiersze, na karcie Narzędzia główne, w grupie poleceń Komórki wybierz Usuń/Usuń komórki.

Rysunek 2.15. Arkusz po usunięciu pustych wierszy

Jeśli chcesz, żeby makro automatycznie usuwało puste wiersze, aby nie korzystać dodatkowo z polecenia Usuń, to pod koniec makra zamiast metody Select przy zmiennej zakres2 wpisz metodę [Delete]:

zakres2.[Delete]

i dodatkowo w pierwszej linii zmień nazwę makra, aby odpowiadała jego aktualnej funkcji:

Sub UsuńPusteWiersze()

Jeśli chcesz, aby makro automatycznie ukrywało puste wiersze, to pod koniec makra zmodyfikuj linię z wywołaniem metody Select w następujący sposób:

zakres2.EntireRow.Hidden = True

i dodatkowo w pierwszej linii zmień nazwę makra, aby odpowiadała ona jego aktualnej funkcji:

Sub UkryjPusteWiersze()

Jeśli chcesz, aby nie były podświetlane wszystkie puste wiersze zestawienia, ale tylko wiersze znajdujące się w obszarze komórek zaznaczonych przed uruchomieniem makra, to na początku makra w linii, w której przypisujesz obszar komórki do zmiennej zakres1, przypisz obiekt Selection:

Set zakres1 = Selection

W przypadku takiej modyfikacji musisz pamiętać, aby przed uruchomieniem makra nie były zaznaczone wszystkie komórki arkusza czy całe kolumny, ponieważ program będzie długo działać.

2.7. Zaznaczanie pustych kolumn

Mamy arkusz zawierający dane budżetu. Teraz chcesz w nim usunąć puste kolumny. Sprawdzenie tego – podobnie jak w przypadku wierszy – nie jest łatwe i można niepotrzebnie skasować jakieś dane, dlatego warto posłużyć się makrem, które zaznacza puste kolumny w arkuszu.

Rysunek 2.16. Arkusz z budżetem zawierający puste kolumny

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZaznaczPusteKolumny()

Dim i As Long

Dim zakres1 As Range

Dim zakres2 As Range

Set zakres1 = ActiveSheet.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

For i = 1 To zakres1.Columns.Count

If WorksheetFunction.CountA(zakres1.Columns(i).EntireColumn) = 0 Then

If zakres2 Is Nothing Then

Set zakres2 = zakres1.Columns(i).EntireColumn

Else

Set zakres2 = Application.Union(zakres2, zakres1.Columns(i).EntireColumn)

End If

End If

Next

If zakres2 Is Nothing Then

MsgBox „Nie znaleziono żadnych kolumn.”

Else

zakres2.Select

End If

End Sub

Kod tego makra bardzo przypomina poprzednio przestawione zaznaczające puste wiersze znajdujące się pomiędzy komórką A1 a ostatnią komórką arkusza.

Tutaj sprawdzamy po kolei każdą kolumnę w obszarze komórek określonym przez zmienną zakres1. Jeśli w kolumnie nie występują żadne niepuste komórki, to cała kolumna powiększa zakres komórek przypisany do zmiennej zakres2.

Rysunek 2.17. Arkusz po usunięciu pustych kolumn

Jeśli nie znaleziono żadnych kolumn spełniających ten warunek, to na zakończenie makra wyświetlany jest odpowiedni komunikat, a w przeciwnym razie zaznaczane są znalezione kolumny.

Jeśli chcesz usunąć kolumny zawierające tylko puste komórki, to w arkuszu budżetu zaznacz dowolną komórkę i uruchom makro ZaznaczPusteKolumny. Zostaną podświetlone wszystkie puste kolumny znajdujące się pomiędzy komórką A1 a ostatnią komórką. Aby usunąć niepotrzebne kolumny, na karcie Narzędzia główne, w grupie poleceń Komórki wybierz Usuń/Usuń komórki).

Jeśli chcesz, aby makro zawsze automatycznie usuwało puste kolumny, a nie tylko zaznaczało je, to pod koniec makra zamiast metody Select przy zmiennej zakres2 wpisz metodę [Delete]:

zakres2.[Delete]

i dodatkowo w pierwszej linii zmień nazwę makra:

Sub UsuńPusteKolumny()

Jeśli chcesz, aby makro automatycznie ukrywało kolumny zawierające same puste komórki, to pod koniec makra zmodyfikuj linię z wywołaniem metody Select:

zakres2.EntireColumn.Hidden = True

i dodatkowo w pierwszej linii zmień nazwę makra, aby odpowiadała jego aktualnej funkcji:

Sub UkryjPusteKolumny()

Jeśli chcesz, aby nie były zaznaczane wszystkie puste kolumny w arkuszu, a tylko należące do obszaru komórek zaznaczonych przed uruchomieniem makra, to w linii, w której przypisujesz obszar komórki do zmiennej zakres1, przypisz obiekt Selection:

Set zakres1 = Selection

3. Automatyczne generowanie szablonów dokumentów na podstawie przygotowanego wzoru

Wielu użytkowników Excela pracuje ze skoroszytami składającymi się z dużej liczby arkuszy o identycznej strukturze. Takie pliki mogą zawierać np. karty pracy, w których każdy arkusz mieści w sobie formularz dla jednego pracownika. Najczęściej raz w miesiącu zachodzi potrzeba utworzenia nowego skoroszytu zawierającego zestaw arkuszy o tym samym układzie, a różniących się jedynie szczegółami (nazwą pracownika czy datą w nagłówku). Każdorazowe ręczne tworzenie kopii wzorcowego formularza jest pracochłonne. Okazuje się, że można to zautomatyzować za pomocą makra.

3.1. Jak działa generator arkuszy

Załóżmy, że jest stworzony arkusz ze wzorem miesięcznej karty pracy pracownika, w której odnotowywane są przepracowane godziny, informacje o pracy w godzinach nadliczbowych czy urlopach. Rysunek przedstawia fragment przykładowego arkusza zawierającego wzór takiej karty.

Rysunek 3.1. Przykładowy arkusz ze wzorem karty pracy

Każdego miesiąca musisz utworzyć skoroszyt z kartami pracy dla pracowników. Początkowo każdy z arkuszy wypełniany jest tylko trzema informacjami: nazwiskiem i imieniem pracownika, oznaczeniem roku oraz miesiąca. Rysunek przedstawia fragment gotowego do użytku arkusza, wypełnionego tymi danymi.

Rysunek 3.2. Przykładowy gotowy do użytku arkusz karty pracy

Wszystkie arkusze powinny natomiast znaleźć się w jednym skoroszycie, który zawiera karty pracy z danego miesiąca, a zakładki poszczególnych arkuszy są nazwane nazwiskami pracowników, co zdecydowanie ułatwi poruszanie się po skoroszycie. Dla przykładowych pięciu pracowników arkusze skoroszytu wyglądają jak na rysunku 3.3.

Rysunek 3.3. Zakładki kart pracy w skoroszycie miesięcznym, nazwane nazwiskami pracowników

Każdorazowe tworzenie miesięcznego zestawu kart pracy jest czynnością żmudną, jeśli musisz wykonywać ją ręcznie. W firmie zatrudniającej kilkudziesięciu pracowników wymaga to wykonania wielu operacji kopiowania arkusza oraz ręcznego wpisania imienia i nazwiska pracownika.

Zdecydowanie wygodniejszym rozwiązaniem byłoby utworzenie zestawienia kart pracy na podstawie istniejącej listy pracowników. Rysunek 3.4 przedstawia taką przykładową listę wraz z dodatkowymi danymi i przyciskiem generującym skoroszyt kart pracy. Każdego miesiąca pracownik działu kadr musi jedynie wpisać nowy miesiąc w komórce B4, a raz do roku zmienić rok w komórce D4. Po upewnieniu się, że lista pracowników jest aktualna, wystarczy tylko kliknąć przycisk Utwórz skoroszyt, a Excel automatycznie stworzy skoroszyt zawierający arkusze wypełnione jak na rysunkach.

Cały proces tworzenia miesięcznego zestawienia może wówczas zająć kilka sekund, a jednocześnie jest gwarancja, że dane na karcie pracy w polach Miesiąc/rok oraz nazwiska pracowników zostaną uzupełnione bezbłędnie.

W podanym przykładzie karta pracy jest wypełniana tylko trzema informacjami, ale nietrudno sobie wyobrazić bardziej skomplikowany formularz – w takiej sytuacji korzyść z automatyzacji tworzenia skoroszytu jest jeszcze większa.

Rysunek 3.4. Lista z danymi, na podstawie której tworzony jest miesięczny skoroszyt kart pracy

3.2. Samodzielne przygotowanie narzędzia

Wzór karty pracy lub innego dokumentu, który chcesz kopiować, trzeba umieścić w oddzielnym skoroszycie. W naszym przykładzie arkusz, w którym go wstawimy, będzie nosił nazwę Wzór.

Aby skopiować aktywny arkusz do nowego skoroszytu, można go zaznaczyć, wciskając na klawiaturze [Ctrl]+[A], utworzyć nowy skoroszyt, wciskając na klawiaturze [Ctrl]+[N], i wkleić z zachowaniem formatowania źródłowego.

Rysunek 3.5. Wklejanie skopiowanego arkusza z zachowaniem formatowania źródłowego

Aktywny arkusz można szybko skopiować do nowego skoroszytu także w inny sposób. Aby to zrobić, kliknij prawym przyciskiem myszy na nazwie skoroszytu widocznej na dolnej belce Excela. Z wyświetlonego menu wybierz Przenieś lub kopiuj… (rysunek 3.5), w kolejnym oknie zaznacz pole Utwórz kopię i z rozwijanej listy wybierz (nowy skoroszyt). Kliknij OK.

Rysunek 3.6. Kopiowanie arkusza do nowego skoroszytu

W tym samym skoroszycie tworzymy drugi arkusz zawierający listę pracowników oraz wartości wspólne dla każdej kopii szablonu. W naszym przykładzie będą to pola: Miesiąc i Rok. Widok tak przygotowanego arkusza został pokazany na rysunku 3.6. Dane stałe zostały w nim umieszczone w komórkach B4 i D4. Lista pracowników, dla których należy utworzyć arkusze, rozpoczyna się w komórce B7.

Kolejną czynnością będzie przygotowanie makra automatyzującego tworzenie miesięcznego skoroszytu z kartami pracy pracowników.

W tym celu:

1. Uruchom Edytor VBA, wciskając równocześnie klawisze lewy [Alt]+[F11].

2. Wstaw nowy moduł makr za pomocą polecenia Module z menu Insert.

3. Wprowadź kod przedstawiony na listingu.

Sub Kopiowanie_arkuszy()

Dim Szablon As Worksheet

Dim Dane As Worksheet

Dim wiersz As Long

Set Szablon = Sheets(“Wzór”)

Set Dane = ActiveSheet

Szablon.Copy

wiersz = 7

Do While Dane.Range(“B” & wiersz).Value <> “”

With ActiveSheet

.Range(“H6”).Value = Dane.Range(“B4”).Value

.Range(“I6”).Value = Dane.Range(“D4”).Value

.Range(“G4”).Value = Dane.Range(“B” & wiersz).Value

.Name = Dane.Range(“B” & wiersz).Value

End With

wiersz = wiersz + 1

If Dane.Range(“B” & wiersz).Value <> “” Then Szablon.Copy after:=ActiveSheet

Loop

ActiveWorkbook.SaveAs Filename:=”C:\Kadry\Zestawienia\Karty Pracy” & Dane.Range(“D4”).Value & “-” & Dane.Range(“B4”).Value & “.xls”

End Sub

Wyjaśnienie działania makra:

Na podstawie arkusza o nazwie Wzór, za pomocą instrukcji Szablon.Copy, kopiującej arkusz Wzór, tworzony jest nowy skoroszyt. Następnie dzięki zastosowaniu pętli Do Loop, począwszy od komórki B7 w arkuszu Lista (przedstawionym na rysunku 3.4), sczytywane są kolejne nazwiska. Jeżeli komórka z nazwiskiem nie jest pusta, arkusz kart pracy w nowym skoroszycie zostanie nazwany zgodnie z imieniem i nazwiskiem na liście oraz wypełniane są komórki G4, H6 i I6 wartościami odpowiednich komórek arkusza Lista.

Gdy karta pracy dla danego pracownika zostanie wypełniona, makro sprawdza, czy na liście pracowników znajduje się następna pozycja. Jeśli tak, w skoroszycie kart pracy dodawana jest nowa kopia arkusza Wzór i wypełniana analogicznie do pierwszej.

Makro będzie tworzyło kolejne karty pracy do momentu, gdy napotka puste pole w kolumnie B, po czym kończy swoje działanie. Po tym fakcie aktywnym skoroszytem jest skoroszyt z kartami pracy. Nie ma on nazwy i powinien jeszcze zostać zapisany przez użytkownika.

Liczba arkuszy, które można dodać w jednym skoroszycie, jest ograniczona pamięcią operacyjną komputera. Może nie być możliwe utworzenie skoroszytu zawierającego kilkaset arkuszy.

Makro można uruchamiać za pośrednictwem okna dialogowego Makra. Wygodniejszym sposobem jest jednak korzystanie z przycisku wstawionego do arkusza.

Narzędzia do obsługi makr oraz wstawiania przycisków znajdziesz w karcie Deweloper. Domyślnie jest ukryta. Aby ją wyświetlić, w skoroszycie kliknij przycisk Plik i z menu wybierz Opcje. Następnie wskazujemy Dostosowanie wstążki i na liście Dostosuj wstążkę zaznacz pole Deweloper. Kliknij OK.

Aby go utworzyć:

1. Włącz Widok karty Deweloper, kliknij nią, a następnie na przycisk Wstaw i z wyświetlonego menu wybierz Przycisk (formant formularza) [w Excel 2003 z menu Widok wybierz polecenie Paski narzędzi, a następnie Formularze i w wyświetlonym pasku narzędziowym wciśnij Przycisk (formant formularza)].

Rysunek 3.7. Wstawianie przycisku do arkusza

2. Wskaż miejsce w arkuszu, w którym chcesz wstawić nasz przycisk, i przytrzymując wciśnięty przycisk myszy, zaznacz niewielki obszar.

3. Jeśli skoroszyt, w którym umieszczasz przycisk, zawiera makra, od razu pojawi się okno Przypisywanie Makra. Wybierz z listy utworzone wcześniej makro Kopiowanie_arkuszy, kliknij OK.

4. Po kliknięciu formantu prawym przyciskiem myszy można edytować jego nazwę, a także zmieniać rozmiar, czcionkę tekstu i przemieszczać po arkuszu według naszych preferencji.

5. Jeśli umieścisz przycisk, a dopiero później wprowadzisz kod VBA, przycisk pozostanie nieaktywny dopóty, dopóki ręcznie nie przypiszesz do niego makra. Aby to zrobić, kliknij wstawiony formant prawym przyciskiem myszy i z wyświetlonego menu wybierz polecenie Przypisz makro jak na rysunku 3.8. Dalej postępuj tak, jak to zostało opisane w punkcie 3 tej wskazówki.

Rysunek 3.8. Dodawanie makra do przycisku osadzonego w arkuszu

3.3. Dostosowanie generatora arkuszy do swoich potrzeb

Przedstawiony skoroszyt generujący karty pracy jest jedynie przykładowy. Zaproponowane narzędzie można modyfikować na wiele sposobów, dostosowując jego strukturę do indywidualnych potrzeb naszych i naszej formy. Najczęstsze takie modyfikacje wiążą się z inną liczbą pól do wypełnienia i ich rozmieszczeniem. Przykładowo, jeśli pozycja Miesiąc w arkuszu z listą pracowników byłaby w komórce C3 zamiast B4, natomiast do wzorca karty pracy wpisywano by ją nie do komórki H6, ale do komórki J4, wówczas linię kodu:

.Range(“H6”).Value = Dane.Range(“B4”).Value

należałoby zmienić następująco:

.Range(„J4”).Value = Dane.Range(„C3”).Value

Każda taka linia kodu kopiuje jedną wartość z arkusza listy do każdego utworzonego arkusza karty pracy, przy czym pierwsza wartość zakresu (J4) odpowiada komórce na karcie pracy, a druga (C3) komórce w arkuszu z listą pracowników. W makrze Kopiowanie_arkuszy znajdują się 3 przypisania tego typu, ale w praktyce może ich być dowolna liczba.

W przedstawionym przykładzie generatora skoroszytu arkusz zawierający wzorcową kartę pracy nazywa się Wzór. Czytelniejsze może być nazwanie go pełną nazwą, np. Karta pracy. Jeśli zmienimy nazwę tego arkusza, trzeba również zmienić kod makra, zastępując linię:

Set Szablon = Sheets(“Wzór”)

kodem:

Set Szablon = Sheets(„Karta pracy”)

Tworzony skoroszyt zawierający karty pracy nie jest nazwany i pozostaje niezapisany na dysku. Można to zrobić automatycznie, nazywając go danymi z arkusza listy, np. skoroszyt dla roku 2014 i miesiąca 5 będzie się nazywał Karty pracy 2014-5. W tym celu w makrze przed ostatnią linią zawierającą kod End Sub należy dodać następującą linię kodu:

ActiveWorkbook.SaveAs Filename:=”Karty pracy “ & Dane.Range(“D4”).Value & “-” & Dane.Range(“B4”).Value & “.XLS”

Skoroszyt Karty pracy 2014-5 zostanie wówczas zapisany w domyślnym folderze do zapisywania pliku Excela. Jeśli chcesz podać alternatywną ścieżkę, powinniśmy ją umieścić w nazwie pliku, czyli zamiast fragmentu „Karty pracy” można wpisać np. „C:\Kadry\Zestawienia\Karty Pracy „. Warto tylko pamiętać, żeby przed automatycznym utworzeniem skoroszytu utworzyć wskazany w makrze folder do zapisu dokumentów.

4. Formanty formularza – nawigacja po arkuszu może być wygodniejsza

Tysiące kolumn i wierszy mogą obejmować dane, z którymi na co dzień przychodzi się zmierzyć. 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. Proponujemy sięgnąć po proste makra i tak zmodyfikować swój roboczy arkusz, aby praca z nim była szybsza i wygodniejsza.

Przyjmijmy, że pracujesz z zestawieniem utargów przedstawionym na rysunku.

Rysunek 4.1. Długa lista znajdująca się w Arkuszu1

Dość często musisz porównywać te dane z kolejnymi, które znajdują się w innym arkuszu oraz w odrębnym skoroszycie. Na końcu tego zestawienia zostało wstawione podsumowanie. Jego wynik chcielibyśmy także od czasu do czasu skontrolować.

Aby wygodnie przechodzić między tymi lokalizacjami, proponujemy zastosowanie 3 prostych makr. Przypiszemy je do przycisków, które wstawimy na początku arkusza widocznego na rysunku.

4.1. Szybki podgląd podsumowania na końcu tabeli

W pierwszej kolejności sprawdź, jak za pomocą makra szybko zaznaczyć określoną komórkę w aktywnym arkuszu. W naszym przykładzie, w komórce C91 zostało wstawione podsumowanie kwot z kolumny C. Aby szybko do niego przeskoczyć, na początku arkusza wstawiamy przycisk uruchamiający makro.

W tym celu:

1. Uaktywnij kartę Deweloper.

2. Rozwiń przycisk Wstaw i wybierz ikonę przycisku z grupy Formanty ActiveX.

Nasz arkusz powinien teraz wyglądać jak na rysunku.

Rysunek 4.2. Przycisk został wstawiony

Obecnie, jeżeli klikniesz przycisk w arkuszu, nie zostanie wykonana żadna operacja. Należy więc utworzyć makro i przypisać je do formantu.

Aby to zrobić:

1. Kliknij kontrolkę prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Wyświetl kod. Zostanie uruchomiony Edytor Visual Basic i otwarty moduł, a w nim widoczne 2 linie kodu.

2. Wpisz pomiędzy nimi następujący kod:

ActiveSheet.Range(„C91”).Select

W miejsce adresu komórki C91 można oczywiście wedle potrzeb wpisać każdy inny istniejący adres.

Kod naszego makra powinien wyglądać jak na rysunku.

Rysunek 4.3. Uzupełniony kod makra

Pierwsza linia kodu jest wstawiana automatycznie i zawiera aktualną nazwę przycisku. Nazwa CommandButton może mieć inny numer, np. CommandButton4. Nie zmieniajmy jej.

3. Zapisz i zamknij Edytor Visual Basic.

Na koniec warto jeszcze dostosować etykietę przycisku, aby jasno opisywała, co stanie się po kliknięciu formantu.

W tym celu:

1. Kliknij go prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Obiekt CommandButton/Edit.

2. Przytrzymaj klawisz [Delete], aby wyczyścić dotychczasowy wpis, i podaj nową etykietę przycisku, np. Do sumy.

Rysunek 4.4. Zmiana etykiety przycisku

3. Wciśnij klawisz [Esc], aby wyjść z trybu edycji etykiety przycisku.

Można nadać inny wygląd wstawionej kontrolce, np. zmienić kolor. Aby to zrobić, kliknij formant prawym przyciskiem myszy i wybierz Właściwości. Znajdziesz tam zestaw opcji (w języku angielskim), które można dostosować do swoich potrzeb. Kolor przycisku definiujemy w polu BackColor, a czcionki w ForeColor.

Na pierwszy rzut oka wydaje się, że działanie przycisku zostało zdefiniowane. Niestety, po kliknięciu formantu docelowa komórka zawierająca podsumowanie nie zostanie zaznaczona. Pozostała jeszcze ostatnia operacja do wykonania: wyłączenie trybu projektowania. Kliknij ikonę Zakończ tryb projektowania.

Od tego momentu po kliknięciu przycisku Do sumy zostaniemy przeniesieni do komórki C91.

Rysunek 4.5. Efekt kliknięcia przycisku Do sumy

W zasadzie należałoby teraz w okolicy komórki C91 umieścić przycisk służący do przeskakiwania na początek zestawienia. Nie traćmy na to czasu. Aby przechodzić do komórki A1, skorzystaj z kombinacji klawiszy [Ctrl]+[Home].

4.2. Szybkie uaktywnianie innego arkusza

Równie często wykonywaną operacją w trakcie pracy w zbiorczym raporcie jest przechodzenie do określonego arkusza, aby np. porównać dane. Oczywiście jeśli w naszym skoroszycie jest kilka arkuszy o krótkich nazwach, to ich karty mieszczą się na ekranie i nie ma potrzeby wprowadzania żadnych usprawnień. W tym przypadku przechodzenie między zakładkami jest szybkie i wygodne. Problem pojawi się wówczas, gdy nasz raport będzie podzielony na kilkanaście bądź kilkadziesiąt arkuszy o długich nazwach. Na ekranie widocznych będzie zaledwie kilka i za każdym razem będziemy zmuszeni korzystać z suwaka u dołu okna, który pozwoli odkryć kolejny zestaw kart arkuszy.

Warto w tego typu raportach zastosować przyciski, które będą uaktywniały dany arkusz i zaznaczały określony zakres komórek. Sposób ich przygotowania poznamy na przykładzie naszej listy utargów.

W tym celu:

1. Wstaw nowy przycisk obok już istniejącego.

2. Kliknij go prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Wyświetl kod. Zostanie uruchomiony Edytor Visual Basic i otwarty moduł, a w nim widoczne 2 linie kodu.

3. Pomiędzy nimi wpisz następujący kod:

Worksheets(“Arkusz2”).Activate

ActiveSheet.Range(“C4:C8”).Select

Oczywiście wartości ujęte w cudzysłów (nazwa arkusza i adres zakresu komórek) można dostosować do swoich potrzeb.

Kod makra wstawiony poniżej poprzedniego powinien wyglądać jak na rysunku.

Rysunek 4.6. Makra przypisane do przycisków

4. Zapisz i zamknij Edytor Visual Basic.

5. Kliknij formant prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Obiekt CommandButton/Edit.

6. Usuń dotychczasową nazwę i wpisz nową: np. C4:C8 w Arkuszu2.

7. Na koniec wyłącz tryb projektowania i kliknij nowo wstawiony przycisk.

Uaktywniony zostanie Arkusz2, a w nim zaznaczony zakres komórek C4:C8, jak jest to przedstawione na rysunku.

Rysunek 4.7. Przycisk spełnia swoją funkcję

4.3. Przycisk otwierający dowolny skoroszyt

Do arkusza warto wstawić jeszcze jeden przycisk, po którego kliknięciu zostanie otwarty plik Excela znajdujący się w dowolnej lokalizacji na dysku naszego komputera. Ustawisz go w identyczny sposób co poprzednio przedstawione przyciski. Po kliknięciu polecenia Wyświetl kod pomiędzy wiersze Sub i End Sub wstawiamy następującą linię kodu Visual Basic:

Workbooks.Open “C:\Raporty\Czerwiec_2010.XLS”

Oczywiście zarówno ścieżkę dostępu do pliku, jak i jego nazwę dostosowujemy do swoich potrzeb. Przedstawione na rysunku 8 makro spowoduje uruchomienie skoroszytu Excela o nazwie Czerwiec_2010.XLS, który znajduje się na dysku C, w folderze Raporty.

Rysunek 4.8. Makro dla trzeciego przycisku

4.4. Wygodna obsługa przycisków

Ostatnia propozycja modyfikacji skoroszytu polega na zablokowaniu wierszy arkusza w taki sposób, aby przyciski były cały czas widoczne bez względu na to, który zakres komórek aktualnie przeglądamy.

W tym celu:

1. Zaznacz komórkę A4 i na karcie Widok, w grupie Okno wybierz polecenie Zablokuj okienka.

Efekt zastosowania polecenia przedstawia rysunek. Mimo że arkusz został przewinięty w dół, przyciski są widoczne.

Rysunek 4.9. Zablokowane wiersze z przyciskami

5. Szybkie wystawianie zaświadczeń o zarobkach z użyciem makr

Ręczne wypisywanie zaświadczeń o zarobkach jest dość żmudnym zajęciem, dlatego pokazujemy, jak przygotować bardzo przydatne narzędzie służące do szybkiego tworzenia dokumentów potwierdzających dochody pracowników. Mając takie narzędzie, kilkoma kliknięciami myszy sporządzisz zaświadczenie o zarobkach danego pracownika.

5.1. Przygotowanie arkusza do wprowadzania i edycji danych

Gotowe narzędzie można pobrać ze strony internetowej (instrukcja została zamieszczona na 6 stronie) lub wykonać je samodzielnie zgodnie z pokazaną w tym rozdziale instrukcją.

Aby utworzyć skoroszyt do drukowania zaświadczeń o zarobkach:

1. Utwórz nowy skoroszyt. Zostaw w nim tylko 2 arkusze. Pozostałe usuń – na karcie Narzędzie główne, w grupie poleceń Komórki wybierz Usuń/Usuń arkusz. Można jednocześnie zaznaczyć wiele arkuszy. W tym celu zaznacz pierwszy z arkuszy do usunięcia, a następnie, trzymając wciśnięty klawisz [Shift], wskaż ostatni arkusz do usunięcia. Na koniec wybierz polecenie Usuń arkusz.

2. Zmień nazwę pierwszego arkusza poprzez dwukrotne kliknięcie jego zakładki i wpisanie nowej nazwy: Główny. W ten sam sposób drugi arkusz nazwij Zaświadczenie. Umieszczenie w osobnych arkuszach danych i formularza, do którego pobierane są dane z pierwszego arkusza, jest korzystne. Można ukryć arkusz formularza i drukować go za pomocą makra. Unikniesz w ten sposób sytuacji, że ktoś samowolnie zmodyfikuje arkusz formularza.

3. W arkuszu Główny utwórz tabelę danych pracowników i sformatuj komórki zgodnie z rysunkiem 5.1.

Rysunek 5.1. Arkusz Główny po sformatowaniu komórek tabeli

W kolumnie G będziemy wpisywać tylko cztery rodzaje umów o pracę. Aby ułatwić sobie to zadanie, utwórz w tej kolumnie listę rozwijaną, umożliwiającą wybór jednego z tych czterech wpisów. W tym celu:

1. Zaznacz kolumnę G i na karcie Dane, w grupie poleceń Narzędzia danych wybierz Poprawność danych. W zakładce Ustawienia, w polu Dozwolone wybierz Lista i w polu Źródło wpisz obok siebie cztery rodzaje umów o pracę oddzielone średnikiem:

•umowa na czas nieokreślony;

•umowa na czas określony;

•umowa zlecenia;

•umowa o dzieło.

2. Wprowadzone zmiany zatwierdź, klikając OK.

Zwróć uwagę, że zawartość listy rozwijanej można zdefiniować bezpośrednio w oknie Sprawdzanie poprawności danych lub skorzystać z odwołania do zakresu komórek w arkuszu. Przykładowe, wymienione rodzaje umów można wpisać w zakresie komórek Z1:Z4, a następnie odwołać się do tego zakresu.

Rysunek 5.2. Utworzenie listy rozwijanej z rodzajami umów o pracę

Teraz można wypełnić tabelę danymi pracowników, wpisując informacje zgodnie z nazwami kolumn. Można ten proces przeprowadzić później lub też wprowadzić dane, wykorzystując narzędzia do ich importu z zewnętrznego źródła.

Zaznacz wiersze od 1 do 8 i wybierz na karcie Narzędzie główne, w grupie poleceń Komórki Wstaw/Wstaw wiersze arkusza. Można również użyć skrótu klawiaturowego CtrlShift + lub [Ctrl] +, jeśli używamy klawiatury numerycznej. W ten sposób na górze arkusza pojawi się 8 pustych wierszy. Te wiersze należy wypełnić i sformatować zgodnie z rysunkiem 5.3.

Rysunek 5.3. Wzór formatowania i wypełnienia komórek w wierszach od 1 do 8

Teraz w arkuszu wstawimy przyciski wywołujące makra. W tym celu:

1. Wybierz na karcie Deweloper, w grupie poleceń Formanty/Wstaw i w sekcji Formanty formularza wskaż Przycisk, a następnie w wierszu 2. zaznacz miejsce, gdzie ma zostać wstawiony przycisk.

Karta Deweloper jest domyślnie ukryta. Aby ją uaktywnić, naciśnij Przycisk pakietu Office lub menu Plik. Następnie kliknij Opcje programu Excel i w zakładce Popularne zaznacz opcję Pokaż kartę Deweloper na wstążce.

2. Pojawi się okno Przypisywanie makra. Naciśnij przycisk Anuluj, gdyż makro przypiszemy później.

3. Kliknij dwukrotnie wstawiony przycisk i zmień jego opis na: Drukuj zaświadczenie o zarobkach. Następnie zaznacz ten tekst i na karcie Narzędzia główne, w grupie poleceń Czcionka wybierz Pogrubienie. Można również użyć skrótu klawiaturowego [Ctrl] B.

Rysunek 5.4. Przycisk polecenia po dodaniu i odpowiednim sformatowaniu

4. W analogiczny sposób dodaj kolejne przyciski i sformatuj je zgodnie z rysunkiem 5.5.

Opis przycisku można zmienić także w inny sposób. Kliknij formant prawym przyciskiem myszy i wybierz z menu kontekstowego polecenie Edytuj tekst. Zwróć uwagę, że później po przypisaniu makra do przycisku nie będzie można edytować tekstu po dwukrotnym kliknięciu przycisku.

Rysunek 5.5. Arkusz po dodaniu wszystkich przycisków

Teraz zdefiniujemy dynamiczny zakres, który będzie zwiększał się wraz z dopisywaniem kolejnych pracowników. W tym celu:

1. Wybierz na karcie Formuły, w grupie poleceń Nazwy zdefiniowane Menedżer nazw i naciśnij Nowy.

2. W oknie dialogowym w polu Nazwa wpisz: Pracownicy, a w polu Odwołuje się do wpisz formułę:

=PRZESUNIĘCIE(Główny!$A$10;;;ILE.NIEPUSTYCH(Główny!$A:$A)-3)

i naciśnij OK, a następnie Zamknij, aby zamknąć okno Menedżer nazw.

Rysunek 5.6. Definiowanie nazwy

Dzięki funkcji PRZESUNIĘCIE w polu definiowania nazw utworzymy dynamiczny zakres komórek, tj. zakres automatycznie zwiększający się, gdy zostaną dodane nowe wiersze z danymi pracowników. Będzie to zakres zaczynający od imienia i nazwiska pierwszego pracownika (komórka A10), a kończący się na danych ostatniego pracownika. Za pomocą funkcji PRZESUNIĘCIE można utworzyć nazwany zakres komórek, który będzie automatycznie dopasowywał się do aktualnej ilości danych źródłowych.

Zaznacz komórkę B1 i wybierz na karcie Dane, w grupie poleceń Narzędzia danych funkcję Poprawność danych. W nowym oknie w polu Dozwolone wybierz Lista, a w polu Źródło wpisz formułę:

=Pracownicy

i naciśnij przycisk OK.

Rysunek 5.7. Ustawienie listy rozwijanej w komórce B1

Zaznacz komórkę E1 i ponownie otwórz okno Sprawdzanie poprawności danych. W polu Dozwolone wybierz Lista, a w polu Źródło wpisz elementy listy rozwijanej w komórce oddzielone średnikiem: netto; brutto i naciśnij przycisk OK.

Zaznacz komórkę G1 i utwórz listę rozwijaną podobnie jak dla komórki E1, wpisując sposoby obliczania zarobków oddzielone średnikami: z ostatniego miesiąca; na podstawie śr. z ost. 3 miesięcy; na podstawie śr. z ost. 6 miesięcy.

Jeżeli zajdzie taka potrzeba, można w każdej chwili dopisać dodatkowe pozycje do listy. Każda powinna być oddzielona średnikiem. Listę można również utworzyć, wskazując zakres komórek w arkuszu.

5.2. Przygotowanie wzoru formularza zaświadczenia

Teraz pokażemy, jak przygotować właściwy wzór zaświadczenia. Należy przejść do drugiego arkusza o nazwie Zaświadczenie, który będzie służył jako formularz wydruku zaświadczenia o zarobkach. W tym celu:

1. Wypełnij komórki tekstem i sformatujmy je zgodnie z rysunkiem 5.8.

2. W komórce C9 wpisz formułę:

=Główny!$B$1

3. W komórce C10 wpisz formułę:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;3;0))

Rysunek 5.8. Arkusz z formularzem wydruku zaświadczenia

Wykorzystujemy dodatkowo funkcję JEŻELI, aby sprawdzić, czy komórka B1 nie jest pusta. Jeśli tak jest, to w komórce z formułą zostanie wyświetlony pusty ciąg znaków.

W podobny sposób wpisz formuły do kolejnych komórek, za każdym razem zmieniając numer kolumny w trzecim argumencie funkcji WYSZUKAJ.PIONOWO. Formuły dla odpowiednich komórek będą wyglądały następująco:

Rysunek 5.9. Arkusz z formularzem wydruku zaświadczenia po wyborze pracownika

C11:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;2;0))

C12:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;4;0))

D14:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;6;0))

W komórce D14 należy zmienić format na datę.

B15:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;7;0))

C17:

=JEŻELI(Główny!$B$1=””;””;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;5;0))

Teraz w komórce D15 wpisz formułę:

=JEŻELI(B15=”umowa na czas określony”;” do dnia:”;””)

natomiast w komórce E15 formułę:

=JEŻELI(B15=”umowa na czasokreślony”;WYSZUKAJ.PIONOWO(Główny!$B$1;Główny!$A$10:$H$1000;8;0);””)

Następnie w komórce A19 wpisz formułę:

=”Zarobki „&Główny!E1&” wyliczone „&Główny!G1&” wynoszą:”

Teraz po wybraniu danego pracownika w komórce B1 arkusza Główny formularz w arkuszu Zaświadczenie zostanie wypełniony jego danymi (rysunek 5.9).

Ponieważ arkusz z formularzem zaświadczenia nie powinien być widoczny, kliknij kartę Narzędzia główne i w grupie poleceń Komórki naciśnij Format/Ukryj i odkryj/Ukryj arkusz.

Jeśli drukujemy jakiś formularz z wpisanymi formułami pobierającymi dane z innego arkusza, lepiej jest go zabezpieczyć albo przynajmniej go ukryć. Może się zdarzyć, że użytkownicy będą wpisywać dane bezpośrednio do tego arkusza i formuły przestaną działać.

5.3. Utworzenie makr i przypisanie ich do przycisków

Aby utworzyć odpowiednie makra i przypisać je do przycisków:

1. Otwórz Edytor Visual Basic, klikając kartę Deweloper. Następnie w grupie poleceń Kod naciśnij Visual Basic. Można również użyć kombinacji klawiszy [Alt]+[F11].

2. W nowym oknie wybierz polecenie menu Insert/Module, aby wstawić moduł standardowy.

3. Na środku pojawi się okno kodu tego modułu. Na początku wpisz kod procedury Drukuj, która będzie odpowiedzialna za drukowanie formularza.

Aby wywołać kod VBA w celu przetestowania, będąc w edytorze VBA należy ustawić kursor w dowolnym miejscu kodu danego makra, a następnie wcisnąć klawisz [F5]. Jeśli chcesz przetestować działanie makra krok po kroku, należy użyć przycisku [F8]. Trzeba go wciskać po wykonaniu każdego wiersza.

Sub Drukuj()

Dim komunikat As String

komunikat = vbNewLine& „Zaświadczenie nie zostanie wydrukowane.”

If Worksheets(“Główny”).Range(“B1”) = “” Then

MsgBox „W komórce B1 nie wybrano pracownika.” &komunikat

Exit Sub

End If

If Worksheets(“Główny”).Range(“E1”) = “” Then

MsgBox „W komórce E1 nie określono rodzaju zarobków.” &komunikat

Exit Sub

End If

If Worksheets(“Główny”).Range(“G1”) = “” Then

MsgBox „W komórce G1 nie wybrano sposobu obliczania zarobków.” &komunikat

Exit Sub

End If

Application.ScreenUpdating = False

With Worksheets(“Zaświadczenie”)

.Visible = xlSheetVisible

.PrintOut

.Visible = xlSheetHidden

End With

Application.ScreenUpdating = True

End Sub

Na początku makro Drukuj sprawdza, czy komórki B1, E1 i G1 są wypełnione. W przypadku gdy są puste, wyświetlany jest komunikat nieco inny dla każdej z tych komórek. W dalszej części makra drukowany jest arkusz zaświadczenie. Ponieważ docelowo arkusz ten będzie ukryty, to przed drukowaniem arkusz jest odkrywany, a po wydruku ukrywany.

Aby użytkownik skoroszytu nie zorientował się, że inny arkusz będzie przez chwilę aktywny, w makrze zmieniana jest właściwość ScreenUpdating powodująca zamrożenie widoku ekranu programu na czas pracy z innym arkuszem.

Jeśli w kilku instrukcjach z rzędu wykonujemy czynności dotyczące jednego obiektu, np. arkusza, to zasadne jest użycie instrukcji With jak w przedstawionym makrze. Odniesienia do tego obiektu można wpisywać, poprzedzając właściwość lub metodę tego obiektu tylko kropką. Użycie instrukcji With nie wpływa na proces wykonania makra, a tylko na jego krótszy zapis w edytorze.

Poniżej procedury Drukuj w tym samym oknie modułu w oknie edytora Visual Basic wpisz kod kolejnego makra, którego zadaniem będzie dopisywanie danych nowego pracownika lub zmiany danych osoby wskazanej w komórce D7.

Sub DodajZmieńDane()

Dim wiersz As Long

Dim komunikat As String

komunikat = vbNewLine& „Nie dokonano żadnych zmian.”

With Worksheets(“Główny”)

If WorksheetFunction.CountBlank(.Range(“A5:G5”)) > 0 Then

MsgBox „Nie wypełniono wszystkich komórek w zakresie A5:G5.” &komunikat

Exit Sub

End If

If .Range(“G5”) = “umowanaczasokreślony” And .Range(“H5”) = “” Then

MsgBox „Dla umowy na czas określony wymagane jest wypełnienie komórki H5.” &komunikat

Exit Sub

End If

If WorksheetFunction.CountIf(.Range(“A10:A1000”), .Range(“A5”)) > 0 Then

wiersz = WorksheetFunction.Match(.Range(“A5”), .Range(“A10:A1000”), 0) + 9

.Range(“A5:H5”).Copy

.Cells(wiersz, “A”).PasteSpecialxlPasteValues

Application.CutCopyMode = False

MsgBox „Zmodyfikowano dane nowego pracownika”

Else

wiersz = .Range(“A65536”).End(xlUp).Row + 1

.Range(“A5:H5”).Copy

.Cells(wiersz, “A”).PasteSpecialxlPasteValues

Application.CutCopyMode = False

MsgBox „Wprowadzono dane nowego pracownika”

End If

End With

End Sub

W tej procedurze, podobnie jak w poprzedniej, na początku sprawdzamy, czy wszystkie potrzebne komórki są wypełnione. Jeśli nie są, jest wyświetlany odpowiedni komunikat i działanie kodu zostaje przerwane.

W drugiej części makra sprawdzamy, czy pracownik wpisany w komórce A5 znajduje się w tabeli danych pracowników. Jeśli tak, to kopiujemy dane z komórek A5:H5 do odpowiedniego wiersza. Jeżeli pracownika nie ma jeszcze w tabeli, to dane nowego pracownika z wiersza 5. przenoszone są do pierwszego wolnego wiersza na dole tabeli.

Kod makra przewiduje, że tabela danych pracowników będzie sięgała maksymalnie do 1000. wiersza. Jeśli osób będzie więcej, należy zmodyfikować kod makra, zwiększając zakres A10:A1000.

W tym samym oknie modułu wpisz kody dwóch następnych, nieco krótszych procedur.

Sub Modyfikuj()

Dim wiersz As Long

With Worksheets(“Główny”)

If .Range(“D7”) = “” Then

MsgBox „W komórce D7 nie wybrano pracownika.”

Exit Sub

End If

wiersz = WorksheetFunction.Match(.Range(“D7”), .Range(“A10:A1000”), 0) + 9

.Range(.Cells(wiersz, “A”), .Cells(wiersz, “H”)).Copy

Application.CutCopyMode = False

End With

End Sub

Sub Wyczyść()

Worksheets(“Główny”).Range(“A5:H5”).ClearContents

End Sub

W kodzie procedury Modyfikuj, po sprawdzeniu na początku zawartości komórki D7, w tabeli na dole arkusza wyszukiwane są dane pracownika wskazanego w komórce D7 i kopiowane do komórek w wierszu 5. Procedura Wyczyść usuwa zawartość komórek A5:H5.

Teraz można wpisać kod ostatniej procedury.

Sub Sortuj()

Dim wiersz As Long

Dim arkusz As Worksheet

Dim tekst As String

Dim i As Long

Application.ScreenUpdating = False

wiersz = Worksheets(“Główny”).Range(“A65536”).End(xlUp).Row

Set arkusz = Worksheets.Add

Worksheets(“Główny”).Range(Worksheets(“Główny”).Range(“A10”), Worksheets(“Główny”).Cells(wiersz, “H”)).Copy

arkusz.Range(“B1”).PasteSpecialxlPasteValues

Application.CutCopyMode = False

For i = 1 To wiersz 9

tekst = arkusz.Cells(i, 2)

arkusz.Cells(i, 1) = Right(tekst, Len(tekst) InStrRev(tekst, “ “))

Next

arkusz.Range(“A1”).Sort arkusz.Range(“A1”), Header:=xlNo

Worksheets(“Główny”).Range(“A10”).PasteSpecialxlPasteValues

Application.CutCopyMode = False

Application.DisplayAlerts = False

arkusz.[Delete]

Application.DisplayAlerts = True

Application.ScreenUpdating = False

End Sub

Ta procedura dodaje nowy arkusz, kopiuje tabelę danych pracowników z arkusza Główny do nowego arkusza do obszaru zaczynającego się od komórki B1. Następnie w kolumnie A tego arkusza wydziela nazwiska z tekstu zawierającego imię i nazwisko. W dalszej części procedura sortuje zestawienie alfabetycznie według kolumny A i kopiuje dane z pominięciem kolumny A z powrotem do arkusza Główny, począwszy od komórki A10. Arkusz pomocniczy na końcu zostaje usunięty. Ponieważ ostatnia operacja powoduje wyświetlenie komunikatu potwierdzającego wykonanie tej czynności, dlatego przed tą operacją i po niej dokonywana jest zmiana właściwości Display Alerts, aby komunikat się nie wyświetlał. Także w tym makrze zmieniana jest właściwość ScreenUpdating, ponieważ nie chcesz, aby użytkownik zorientował się, że uaktywniany jest inny arkusz.

Mając napisane wszystkie makra, można się zająć przypisaniem ich do przycisków. W tym celu:

1. Zamknij Edytor Visual Basic, przejdź do arkusza Główny i kliknij prawym przyciskiem myszy przycisk Drukuj zaświadczenie o zarobkach.

2. Z menu podręcznego wybierz polecenie Przypisz makro, w oknie Przypisywanie makra zaznacz makro Drukuj i naciśnij OK.

Rysunek 5.10. Przypisywanie makra do przycisku

W analogiczny sposób przypiszmy makro Dodaj Zmieńmy Dane do przycisku Dodaj nowego pracownika/Zapisz zmiany danych pracownika, makro Modyfikuj do przycisku Modyfikuj dane pracownika, makro Wyczyść do przycisku Czyść komórki, a makro Sortuj do przycisku Sortuj wg nazwisk.

5.4. Dostosowanie narzędzia do własnych potrzeb

Jeśli chcesz zrezygnować z przycisku Modyfikuj dane pracownika i automatycznie kopiować dane pracownika do komórek w wierszu 5., należy usunąć formant i dodać kod zdarzenia uruchamiającego się przy zmianie komórek.

Aby to zrobić:

1. Kliknij prawym przyciskiem myszy przycisk Modyfikuj dane pracownika i naciśnij klawisz [Esc], aby ukryć menu podręczne.

2. Wciśnij klawisz [Delete], aby usunąć przycisk.

3. W komórce C7 wpisz: Wybierz pracownika, aby edytować jego dane. Następnie pogrub czcionkę tej komórki, wyrównaj tekst do prawej w poziomie i do środka w pionie. W tym celu kliknij kartę Narzędzia główne i w grupie poleceń Komórki naciśnij Format/Formatuj komórki.

Rysunek 5.11. Widok arkusza po usunięciu przycisku

4. Kliknij prawym przyciskiem myszy kartę arkusza na dole i z menu podręcznego wybierz polecenie Wyświetl kod.

5. Otworzy się Edytor Visual Basic. W oknie kodu wpisz następujący kod:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address<> “$D$7” Then Exit Sub

If Target.Value<> “” Then Modyfikuj

End Sub

6. Zamknij okno Edytora Visual Basic.

Teraz po wybraniu pracownika w komórce D7 automatycznie zostaną wstawione jego dane do komórek w wierszu 5.

Jeśli w firmie tylko jedna osoba wypisuje dane pracowników i kwotę zarobków, można zmodyfikować skoroszyt w taki sposób, aby tę kwotę również wpisywać w arkuszu Główny, a później umieszczać ją w szablonie zaświadczenia o zarobkach.

Aby to zrobić:

1. W komórce H1 wpisz tekst: Kwota zarobków:, a następnie sformatuj komórki H1 i I1 tak, jak pozostałe komórki w wierszu 1. Pomoże w tym Malarz formatów dostępny na karcie Narzędzia główne.

2. Wybierz kartę Narzędzia główne i w grupie poleceń Komórki naciśnij Format/Ukryj i odkryj/Odkryj arkusz. Na liście ukrytych arkuszy wybierz arkusz Zaświadczenie i naciśnij OK.

3. W arkuszu Zaświadczenie w komórce B21, w której ma być wpisywana kwota, wpisz formułę:

=Główny!I1

i ewentualnie ustaw dla tej komórki format liczbowy (CtrlShift `).

4. Wybierz kartę Narzędzia główne i w grupie poleceń Komórki naciśnij Format/Ukryj i odkryj/Ukryj arkusz, aby ukryć arkusz Zaświadczenie.

Teraz w komórce H1 można wpisać kwotę zarobków, która pojawi się na wydrukowanym zaświadczeniu o zarobkach.

Rysunek 5.12. Arkusz po modyfikacji przygotowany do wydruku zaświadczenia z kwotą zarobków

5.5. Korzystanie z gotowego narzędzia do wystawiania zaświadczeń

Po otwarciu przygotowanego pliku pojawi się jedyny widoczny arkusz Główny. Na samej górze znajdują się przyciski oraz powiązane z nimi komórki. W dolnej części arkusza, począwszy do 9. wiersza, znajduje się baza danych pracowników.

Rysunek 5.13. Niezmodyfikowany skoroszyt zaświadczenia o zarobkach po otwarciu

Aby wypełnić zaświadczenie o zarobkach:

1. Kliknij komórkę B1. Obok prawego dolnego rogu komórki pojawi się mały przycisk ze strzałką – kliknij go. Pod dolną krawędzią komórki pojawi się lista. Wybierz z niej pracownika, dla którego chcesz wypełnić zaświadczenie o zarobkach.

2. W podobny sposób wybierz z listy rozwijanej w komórce E1 jedną spośród dwóch pozycji: netto i brutto, aby określić, czy kwota, która będzie widniała na zaświadczeniu o zarobkach, będzie kwotą netto czy brutto.

Rysunek 5.14. Wybieranie pracownika z listy rozwijanej

3. W komórce G1 wybierz z listy rozwijanej sposób obliczania zarobków.

Rysunek 5.15. Komórki przygotowane do druku zaświadczenia

4. Naciśnij przycisk Drukuj zaświadczenie o zarobkach znajdujący się poniżej wypełnionych komórek, a zaświadczenie zostanie wydrukowane (rysunek 5.16).

Wystarczy teraz podpisać wydrukowane zaświadczenie i przekazać je do działu wynagrodzeń w celu wpisania kwoty zarobków.

Rysunek 5.16. Wydruk zaświadczenia o zarobkach

Jeśli któraś z 3 komórek: B1, E1 lub G1 nie jest wypełniona, to zostanie wyświetlony komunikat informujący o braku wypełnienia komórki i wstrzymaniu wydruku zaświadczenia.

Rysunek 5.17. Komórka B1 nie została wypełniona

Czasem trzeba zmienić dane pracownika w tabeli znajdującej się w dolnej części arkusza, np. skończyła się umowa na czas określony i podpisano z nim umowę na czas nieokreślony. W tym celu można odszukać danego pracownika w tabeli poniżej i ręcznie poprawić dane. Jest także szybszy sposób, bez konieczności żmudnego szukania i sprawdzania, czy dokonano zmian w prawidłowym wierszu tabeli.

Aby z niego skorzystać:

1. Kliknij komórkę D7 i z listy rozwijanej wybierz pracownika, którego dane chcesz zmodyfikować, a następnie naciśnij przycisk Modyfikuj dane pracownika.

2. Komórki w 5. wierszu zostaną wypełnione danymi pracownika z tabeli poniżej.

Rysunek 5.18. Arkusz po naciśnięciu przycisku Modyfikuj dane pracownika

Jeśli w komórce D7 nie wybrano żadnego pracownika i komórka jest pusta, to po naciśnięciu przycisku Modyfikuj dane pracownika pojawi się komunikat informujący, że należy wybrać pracownika w komórce D7.

Zmień wpisy w komórkach wiersza 5. tak, aby uaktualnić dane, np. w komórce G5 z listy rozwijanej wybierz umowa na czas nieokreślony i wyczyść komórkę H5. Naciśnij przycisk Dodaj nowego pracownika/Zapisz zmiany danych pracownika.

Jeśli nie wszystkie komórki w zakresie A5:G5 będą wypełnione, pojawi się stosowny komunikat i dane nie zostaną zmodyfikowane. Jeśli w komórce G5 została wybrana umowa na czas określony, to dodatkowo musi być wypełniona komórka H5. Jeśli zmodyfikujesz imię lub nazwisko pracownika, to zmienione dane pojawią się w tabeli jako nowy wiersz, a poprzednie dane nie zostaną usunięte automatycznie. Trzeba będzie zaznaczyć wiersz ze starymi danymi i na karcie Narzędzia główne, w grupie poleceń Komórki wybierz Usuń/Usuń wiersze arkusza.

W przypadku przyjęcia nowego pracownika można w prosty sposób dodać jego dane osobowe do bazy. W tym celu:

1. Naciśnij przycisk Czyść komórki, aby usunąć zawartości komórek w zakresie A5:H5.

2. Wypełnij komórki w zakresie A5:H5 danymi nowego pracownika i naciśnij przycisk Dodajmy nowego pracownika/Zapisz zmiany danych pracownika. Trzeba wypełnić wszystkie komórki w zakresie A5:G5 i dodatkowo komórkę H5, jeśli w komórce G5 wybrano pozycję umowa na czas określony. Jeśli tego nie zrobisz, to dane nie zostaną dodane do tabeli i pojawi się stosowny komunikat.

3. Dane nowego pracownika wpisane w komórkach w wierszu 5. zostaną skopiowane na dole arkusza jako kolejny wiersz. Można później posortować dane wg nazwisk pracowników za pomocą przycisku Sortuj wg nazwisk.

6. Inne pomocne makra

Możliwości makr są praktycznie nieograniczone. W tym rozdziale pokazujemy kilka przykładów, jak szybko pozbyć się niepotrzebnych elementów z zestawień i szablonów.

6.1. Kopiowanie arkusza wiele razy

Załóżmy, że potrzebujesz przygotować karty pracy dla pracowników Twojego działu. Jest szablon tego dokumentu w postaci pojedynczego arkusza. Jednak Ty musisz utworzyć 12 takich arkuszy. Program Excel nie ma wbudowanego polecenia do tworzenia wielu kopii arkusza za jednym razem, a wybieranie 12 razy polecenia kopiującego arkusz jest zajęciem czasochłonnym. Zdecydowanie lepiej w takim przypadku skorzystać z makra, którego kod przedstawimy dalej.

Rysunek 6.1. Fragment arkusza z szablonem karty pracy

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub KopiujArkuszXRazy()

Dim ile As Variant

Dim i As Long

Dim arkusz As Worksheet

ile = InputBox(„Proszę podać, ile razy ma być skopiowany arkusz”)

If ile = “” Then Exit Sub

If IsNumeric(ile) Then

ile = Fix(ile)

Set arkusz = ActiveSheet

For i = 1 To ile

arkusz.Copy after:=ActiveSheet

ActiveSheet.Name = arkusz.Name & “ “ & i

Next

Else

MsgBox „Nie podano liczby”

End If

End Sub

W kodzie tym korzystasz z trzech zmiennych: pierwszej określającej, ile razy ma być kopiowany arkusz, licznika pętli oraz zmiennej odwołującej się do obiektu aktywnego arkusza. Na początku makra wywołujemy okno do wpisywania wartości i liczbę wpisaną w tym oknie przez użytkownika przypisujemy do zmiennej ile. Jeśli nic nie wprowadzono lub kliknięty został przycisk Cancel w oknie dialogowym, to dalsze instrukcje nie są wykonywane. W kolejnej instrukcji sprawdzamy, czy tekst wpisany do okna dialogowego jest liczbą. Jeśli tak, to na wszelki wypadek usuwane są wszystkie cyfry po przecinku za pomocą funkcji Fix, a następnie w pętli aktywny arkusz jest kopiowany taką liczbę razy, jaką użytkownik podał w oknie dialogowym. Za każdym razem zmieniana jest także nazwa skopiowanego arkusza, w taki sposób, że do nazwy pierwotnego arkusza dopisywana jest po spacji kolejna liczba. W przypadku gdy w oknie dialogowym nie podano liczby, to wyświetlany jest komunikat informujący o tym użytkownika.

Jeśli chcesz skopiować 12 razy arkusz zawierający szablon karty pracy, to kliknij ten arkusz, aby go uaktywnić, a następnie uruchom makro KopiujArkuszXRazy. Pojawi się okno z zapytaniem, ile razy skopiować ten arkusz. Wpisz w nim liczbę 12 i naciśnij OK.

Rysunek 6.2. Wpisywanie liczby kopii arkusza, jakie ma wykonać makro

Arkusz z kartą pracy zostanie skopiowany 12 razy i wszystkie skopiowane arkusze będą miały taką samą nazwę jak arkusz aktywny przed uruchomieniem makra, z tym że do tej nazwy będzie dopisany odpowiedni numer od 1 do 12.

Jeśli będziesz kopiować arkusz o długiej nazwie, to może pojawić się błąd przy wywołaniu makra, ponieważ nazwa arkusza może mieć maksymalnie 31 znaków. Dotyczy to arkuszy o nazwie przekraczającej 28 znaków. Błąd przy nadawaniu nazwy może także powstać, jeśli jakiś arkusz ma nazwę taką samą jak ta, którą chcesz nadać kopiowanemu arkuszowi.

Rysunek 6.3. Skopiowane arkusze w skoroszycie

Jeśli nie chcesz zmieniać nazw kopiowanych arkuszy i mają im być nadane domyślne oznaczenia, to usuń w makrze następującą linię:

ActiveSheet.Name = arkusz.Name & „ „ & i

6.2. Kopiowanie arkuszy na podstawie nazw

Przygotowujesz karty pracy innego działu firmy i jego kierownik zażyczył sobie, aby nazwy arkuszy kart pracy poszczególnych pracowników zawierały ich imiona i nazwiska.

Kopiowanie arkusza szablonu karty pracy wielokrotnie i później zmiana nazw skopiowanych arkuszy są czasochłonne, dlatego przygotowaliśmy dla Ciebie makro, które ułatwi to zadanie. Po wskazaniu komórek zawierających nazwy arkuszy skopiuje arkusz kart pracy tyle razy, ile będzie zaznaczonych komórek, i wprowadzi odpowiednie nazwy.

Rysunek 6.4. Arkusz szablonu kart pracy, który masz zamiar powielić

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub KopiujArkuszPrzezNazwy()

Dim zakres As Range

Dim i As Long

Dim arkusz As Worksheet

On Error Resume Next

Set zakres = Application.InputBox(„Proszę zaznaczyć zakres komórek określających nazwy nowych arkuszy”, Type:=8)

On Error GoTo 0

If zakres Is Nothing Then Exit Sub

Set arkusz = ActiveSheet

For i = 1 To zakres.Count

arkusz.Copy after:=ActiveSheet

ActiveSheet.Name = Left(zakres(i), 31)

Next

End Sub

Na początku kodu wywołujemy okno dialogowe do zaznaczania komórek arkusza i przypisujemy podświetlone komórki do zmiennej zakres.

Rysunek 6.5. Wybieranie komórek do nadania nazw skopiowanym arkuszom

Przed instrukcją wywołującą to okno dialogowe jest wyłączana obsługa błędów, a tuż po niej obsługa jest z powrotem włączana. Naciśnięcie przycisku Anuluj w oknie dialogowym skutkuje bowiem wywołaniem błędu wykonania makra.

Gdy użytkownik naciśnie przycisk Anuluj, to nie będą wykonywane już żadne instrukcje.

W innym przypadku do zmiennej arkusz przypisywany jest aktywny arkusz, a następnie w pętli kopiowany jest on tyle razy, ile zostało zaznaczonych komórek.

Po skopiowaniu każdemu arkuszowi nadawana jest nazwa zgodna z wartością z komórki. Tekst komórki jest obcinany do 31 znaków, ponieważ nazwa arkusza nie może mieć ich więcej.

Jeśli chcesz skopiować arkusz zawierający szablon kart pracy i utworzyć karty pracy dla poszczególnych pracowników wpisanych w arkuszu Nazwy arkuszy, to przejdź do arkusza z szablonem kart pracy i uruchom makro KopiujArkuszPrzezNazwy.

Pojawi się okno dialogowe do wyboru komórek. Przejdź do arkusza Nazwy arkuszy i zaznacz w nim komórki zawierające imiona i nazwiska pracowników, a następnie naciśnij przycisk OK.

Później makro skopiuje arkusz kart pracy dla każdego pracownika i nada nowym arkuszom odpowiednie imiona oraz nazwiska.

Rysunek 6.6. Skoroszyt po uruchomieniu makra

Jeśli chcesz, aby makro wpisywało również imię i nazwisko pracownika do komórki I3 każdego arkusza, to w kodzie makra tuż przed linią kończącą pętlę z instrukcją Next dodaj linię:

ActiveSheet.Range(“I3”) = zakres(i)

W podobny sposób można wpisywać imię i nazwisko do innych komórek arkusza. Zmień w tym celu adres komórki podany w nawiasie.

6.3. Wyświetlanie informacji o ukrytych wierszach

Mamy zestawienie z danymi zamówień, ale niektóre wiersze są ukryte. Chcesz sprawdzić, które z nich są niewidoczne. Excel nie ma do takiej czynności odpowiedniego polecenia, dlatego poniżej przedstawimy makro, które wyświetli informację o ukrytych wierszach.

Rysunek 6.7. Zestawienie zamówień

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub UkryteWiersze()

Dim i As Long

Dim tekst As String

Dim pierwsza As String

tekst = „W aktywnym arkuszu są ukryte następujące wiersze: „

For i = 1 To ActiveSheet.Rows.Count

If ActiveSheet.Rows(i).Hidden = True Then

If pierwsza = “” Then

pierwsza = i

End If

Else

If pierwsza <> “” Then

tekst = tekst & vbNewLine & pierwsza & „:” & i 1

pierwsza = “”

Else

If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then

Exit For

End If

End If

End If

Next

MsgBox tekst

End Sub

W kodzie tym wykorzystywane są trzy zmienne: licznik pętli, zmienna przechowująca tekst komunikatu i zmienna przechowująca numer pierwszego ukrytego wiersza w zakresie. Na początku kodu makra do zmiennej tekst przypisujemy początek tekstu komunikatu. Następnie w pętli sprawdzamy po kolei wszystkie wiersze, które z nich są ukryte. W przypadku gdy dany wiersz jest ukryty, to sprawdzana jest wartość zmiennej pierwsza i jeśli do tej zmiennej nie została przyporządkowana żadna wartość, to przypisywany jest do niej numer wiersza. Jeżeli analizowany w pętli wiersz nie jest ukryty i zmienna pierwsza ma przypisywany jakiś numer wiersza, to wykonywane są takie operacje:

•do zmiennej tekst dopisywany jest w nowym wierszu numer wiersza przechowywany w zmiennej pierwsza oraz

•numer poprzednio sprawdzanego wiersza oddzielony średnikiem, a także usuwany jest tekst zawarty w zmiennej pierwsza.

Jeśli sprawdzany wiersz nie jest ukryty i znajduje się poniżej ostatniej komórki arkusza, to pętla jest opuszczana.

Ostatnia komórka to ta, która znajduje się najdalej od komórki A1 i była kiedykolwiek wykorzystywana w tym arkuszu.

Na końcu makra pokazywany jest tekst komunikatu zawarty w zmiennej tekst.

Jeśli w zestawieniu zamówień chcesz wyświetlić informację, jakie wiersze są aktualnie ukryte, to uaktywnij ten arkusz i uruchom makro UkryteWiersze. Zostanie wyświetlona informacja o ukrytych wierszach, widoczna na rysunku.

Rysunek 6.8. Komunikat o ukrytych wierszach wyświetlany przez makro

Jeśli aktywny arkusz nie zawiera ukrytych wierszy, to po uruchomieniu makr pojawi się tylko pierwszy wiersz komunikatu, bez linii zawierających adresy ukrytych wierszy. Ten komunikat może być trochę mylący. Jeśli chcesz, aby makro pokazywało inny komunikat, to dodaj deklarację jeszcze jednej zmiennej zawierającej informację, czy znaleziono ukryty wiersz:

Dim czyJestUkryta As Boolean

Następnie tuż za linią:

If ActiveSheet.Rows(i).Hidden = True Then

dopisz linię zmieniającą wartość zmiennej czyJestUkryta:

czyJestUkryta = True

Dodatkowo na końcu kodu tuż przed linią:

MsgBox tekst

dodaj linię zmieniającą tekst komunikatu, jeśli nie znaleziono żadnego ukrytego wiersza:

If czyJestUkryta = False Then tekst = „W arkuszu nie ma ukrytych wierszy”

Jeśli chcesz pokazać informacje o ukrytych kolumnach, to zmień nazwę makra, aby oddawała aktualne działanie makra, oraz zmień początkowy tekst komunikatu. W kodzie również wszystkie wystąpienia właściwości Rows zmień na właściwość Columns, a także dostosuj linię dopisującą do treści komunikatu adresy ukrytych komórek. Cały zmodyfikowany kod makra przedstawiony jest na listingu.

Sub UkryteWiersze()

Dim i As Long

Dim tekst As String

Dim pierwsza As String

tekst = „W aktywnym arkuszu są ukryte następujące kolumny: „

For i = 1 To ActiveSheet.Columns.Count

If ActiveSheet.Columns(i).Hidden = True Then

If pierwsza = “” Then

pierwsza = i

End If

Else

If pierwsza <> “” Then

tekst = tekst & vbNewLine & pierwsza & „:” & i 1

pierwsza = “”

Else

If i > ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column Then

Exit For

End If

End If

End If

Next

MsgBox tekst

End Sub

Po uruchomieniu zmodyfikowanego makra wyświetlony zostanie komunikat zawierający adresy ukrytych kolumn.

6.4. Odpowiednie formatowanie komórek

Przyjmijmy, że w dalszym ciągu pracujesz w arkuszu przedstawionym w poprzednim rozdziale i zawierającym zestawienie zleceń. Zawiera formatowanie komórek, które nie odpowiada i chcesz je zmienić, aby ujednolicić wygląd wszystkich arkuszy. Podobne formatowanie chcesz w przyszłości wykorzystywać do formatowania komórek w innych arkuszach.

Zmiana wielu opcji formatowania jest dosyć czasochłonna, a w dodatku można się pomylić i zastosować nieco inne ustawienia formatowania dla kolejnego arkusza.

Aby operacja przebiegała szybko, najlepiej zastosować makro, które ustawi odpowiedni format dla zaznaczonych komórek.

Rysunek 6.9. Arkusz, w którym chcesz inaczej sformatować komórki

Otwórz Edytor Visual Basic, utwórz nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpisz w nim kod przedstawiony na listingu.

Sub ZastosujWłasnyFormat()

If TypeName(Selection) <> “Range” Then Exit Sub

With Selection

.HorizontalAlignment = xlHAlignCenter

.VerticalAlignment = xlVAlignCenter

.WrapText = True

.Borders.LineStyle = xlContinuous

.Borders.Weight = xlThin

.Borders.Weight = xlThin

.Interior.ColorIndex = xlColorIndexAutomatic

.Columns.AutoFit

End With

End Sub

Na początku kodu sprawdzamy, czy zaznaczone są komórki. Jeśli przed uruchomieniem makra użytkownik zaznaczył inny element arkusza, np. wykres, to makro zostanie zakończone. W przeciwnym razie zaznaczone komórki są odpowiednio formatowane – kolejno:

•teksty w komórkach są wyrównywane do środka w poziomie i do środka w pionie,

•zawijanie tekstu zostaje włączone,

•wszystkie krawędzie komórek są obramowane normalną linią ciągłą w kolorze czarnym,

•wypełnienie kolorem jest usuwane oraz dopasowywana jest szerokość kolumn do wpisanego tekstu.

Jeśli chcesz zastosować odpowiednie formatowanie komórek, to zaznacz odpowiednie komórki: A1:E20 i uruchom makro ZastosujWłasnyFormat. Efekt zastosowania narzędzia widać na rysunku.

Rysunek 6.10. Arkusz po uruchomieniu makra

Jeśli chcesz, aby makro wyrównywało teksty komórek w poziomie do prawej strony, to zmodyfikuj linię odpowiadającą za to formatowanie. Zmień stałą, jaka jest przypisywana do właściwości HorizontalAlignment, w następujący sposób:

.HorizontalAlignment = xlHAlignRight

Jeśli chcesz wyrównać tekst do lewej, to użyj stałej xlHAlignLeft, a gdy wyjustować, to użyj stałej: xlHAlignJustify.

Aby makro wyrównywało teksty komórek w pionie do dołu, to zmodyfikuj linię odpowiadającą za to formatowanie. Zmień stałą, jaka jest przypisywana do właściwości VerticalAlignment, w następujący sposób:

.VerticalAlignment = xlVAlignBottom

Jeśli chcesz wyrównać do góry, to użyj stałej xlHAlignTop.

Aby jakakolwiek komórka miała włączone zawijanie tekstu, to w linii z właściwością WrapText przypisz do niej wartość False:

.WrapText = False

Dodatkowo warto na końcu makra przed instrukcją:

End With

dodaj linię dopasowującą wysokość wierszy do zawartości komórek:

.Rows.AutoFit

Natomiast jeśli chcesz, aby ustawienia zawijania tekstu we wszystkich komórkach pozostały bez zmian, to usuń całą linię ze zmianą właściwości WrapText.

Jeśli chcesz zastosować do obramowania komórek grubą linię przerywaną w kolorze niebieskim, to zmodyfikuj instrukcje odpowiadające za format linii:

.Borders.LineStyle = xlDash

.Borders.Color = vbBlue

.Borders.Weight = xlMedium

Przypisanie do właściwości LineStyle wartości xlDoshDot spowoduje narysowanie obramowania linią punktową, a wartość xlDouble odpowiada linii podwójnej. Można do właściwości Color przypisać inny kolor. Excel daje osiem zdefiniowanych stałych koloru. Oprócz czarnego i niebieskiego można zastosować następujące: vbWhite (biały), vbRed (czerwony), vbGreen (zielony), vbYellow (żółty), vbMagenta (różowy) i vbCyan (błękitny). Pozostałe odcienie można przypisać do właściwości Color za pomocą funkcji RGB. Podaj w argumentach tej funkcji kolejno nasycenie kolorami: czerwonym, zielonym i niebieskim w skali od 0 do 255. Można zastosować także inne grubości linii przypisanych do właściwości Weight: stała xlHairline oznacza linię cienką, natomiast stała xlThick – linię bardzo grubą.

Jeśli chcesz zastosować grubą kreskę obramowania tylko dookoła zaznaczonego obszaru, to przed instrukcją End With dodaj linię:

.BorderAround xlContinuous, xlMedium, vbBlack

W pierwszym argumencie metody BorderAround można wpisać także inny styl linii, w drugim – grubość linii, a w trzecim – kolor. Wartości, jakie można podać w tych argumentach, dokładnie odpowiadają wartościom przypisywanym kolejno do właściwości: LineStyle, Weight i Color.

Jeśli chcesz wyróżnić pierwszy wiersz zaznaczonego obszaru pogrubieniem, kursywą i wypełnieniem w kolorze błękitnym, to na samym końcu makra, przed linią:

End Sub

dodaj następujące instrukcje:

.Rows(1).Font.Bold = True

.Rows(1).Font.Italic = True

.Rows(1).Interior.Color = vbCyan

Jeśli chcesz ustawić inne formatowanie pierwszej kolumny, to zamiast właściwości Rows w tych trzech liniach użyj właściwości Columns.

Aby wprowadzić inne formatowanie ostatniego wiersza, to zmień cyfrę 1 w argumencie właściwości Rows na liczbę wszystkich zaznaczonych wierszy, np.:

.Rows(.Rows.Count).Font.Bold = True

6.5. Ważna komórka wyróżniona kolorowym miganiem

Czasami w skoroszycie potrzebujesz wyróżnić komórkę zawierającą ważną informację. Samo dodanie koloru, czy powiększenie czcionki może być wtedy niewystarczające – szczególnie gdy arkusz zawiera inne pola wyróżnione kolorem. Dobrym rozwiązaniem jest stworzenie komórki cyklicznie zmieniającej kolor. Będziemy mieli wtedy niemalże pewność, że tak oznaczona komórka nie zostanie przeoczona przez użytkownika otwierającego arkusz.

Migającą komórkę można stworzyć za pomocą nieskomplikowanego kodu Visual Basic. Zobaczmy zatem, w jaki sposób można to zrobić na przykładzie arkusza zawierającego datę następnego przeglądu.

W arkuszu o nazwie Przypomnienie przedstawionym na rysunku 1 w komórce B4 podany jest termin następnego przeglądu, natomiast w komórce B7, za pomocą prostej formuły widocznej na rysunku, obliczane są pozostałe dni do następnego przeglądu. Chcesz, aby po otwarciu skoroszytu niniejszy arkusz był arkuszem aktywnym, a komórka B7 cyklicznie zmieniała kolor, gdy tylko termin przeglądu ma być w najbliższym tygodniu.

Rysunek 6.12. Arkusz z przypomnieniem terminu przeglądu

Aby to wykonać:

1. Otwórz Edytor Visual Basic. Kliknij kartę Deweloper i w grupie poleceń Kod naciśnij Visual Basic lub poprzez naciśnięcie klawiszy [Alt]+[F11]. Jeśli karta Deweloper nie jest widoczna, to wybierz Przycisk pakietu Office, naciśnij Opcje programu Excel, w zakładce Popularne zaznacz Pokaż kartę Deweloper na wstążce i naciśnij OK.

2. W oknie Edytora Visual Basic wybierz polecenie menu Insert/Module, aby wstawić moduł standardowy.

3. W otwartym oknie kodu wpisz deklaracje zmiennych i kod makra zgodnie z listingiem.

Public czy_włączony As Boolean

Public czas As Date

Sub Miganie()

czas = Now + TimeValue(“00:00:01”)

If Worksheets(“Przypomnienie”).Range(“B7”).Interior.Color = vbYellow Then

Worksheets(“Przypomnienie”).Range(“B7”).Interior.Color = vbRed

Else

Worksheets(“Przypomnienie”).Range(“B7”).Interior.Color = vbYellow

End If

Application.OnTime czas, “Miganie”

End Sub

W powyższym kodzie u góry zadeklarowane są dwie zmienne. Pierwsza o nazwie czy_włączony jest typu Boolean, czyli może przyjmować tylko wartości True lub False. Będzie ona zawierała informacje o tym, czy miganie komórek jest włączone. Druga zmienna o nazwie czas będzie zawierała informacje o kolejnym terminie uruchomienia makra Miganie.

W dalszej części kodu znajduje się kod makra o nazwie Miganie. Na początku kodu makra do zmiennej czas przypisujemy czas o jedną sekundę większy od aktualnego czasu. W dalszej części za pomocą instrukcji warunkowej If sprawdzamy, czy komórka B7 jest aktualnie wypełniona kolorem żółtym. Jeśli taki jest aktualnie kolor wypełnienia tej komórki, to zmieniamy go na kolor czerwony. W przeciwnym wypadku ustawiamy żółty kolor wypełnienia tej komórki.

Do właściwości Color można przypisywać predefiniowane stałe kolorów. Takich stałych jest osiem. Oprócz stałych wymienionych w powyższym kodzie można użyć jeszcze stałych: vbWhite (biały), vbBlack (czarny), vbGreen (zielony), vbBlue (niebieski), vbCyan (turkusowy), vbMagenta (różowy). Do właściwości Color można też przypisać rezultat funkcji RGB. W trzech argumentach funkcji RGB można wpisywać wartości od 0 do 255, gdzie im większa wartość, tym większe nasycenie kolorem kolejno: czerwonym, zielonym i niebieskim.

Jeśli chcesz, aby migał tekst komórki zamiast tła komórki, to musisz zmodyfikować zapisy w powyższym kodzie i zamiast słowa Interior użyć słowa Font we wszystkich wystąpieniach w tym kodzie.

Na końcu kodu uruchamiana jest metoda OnTime obiektu Application, która powoduje uruchomienie makra określonego w drugim argumencie w czasie wpisanym w pierwszym argumencie tej metody.

4. W okienku Project – VBProject kliknij dwukrotnie moduł Ten_skoroszyt.

5. Zostanie otwarte okno kodu modułu skoroszytu. W tym oknie wpisz kod przedstawiony na listingu.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If czy_włączony Then

Application.OnTime czas, “Miganie”, schedule:=False

End If

End Sub

Private Sub Workbook_Open()

Worksheets(“Przypomnienie”).Activate

If Worksheets(“Przypomnienie”).Range(“B7”).Value <= 7 Then

Miganie

czy_włączony = True

Else

Worksheets(“Przypomnienie”).Range(“B7”).Interior.ColorIndex = xlColorIndexNone

End If

End Sub

Powyższy kod zawiera dwie procedury zdarzeń powiązanych ze skoroszytem. Pierwsza procedura będzie uruchamiana tuż przed zamknięciem skoroszytu. Sprawdzamy w niej, czy miganie tekstu jest uruchomione. W takim przypadku w kolejnej instrukcji anulowane będzie następne uruchomienie makra Miganie. Jest to realizowane za pomocą uruchomienia metody OnTime zawierającej pierwsze dwa argumenty ustawione tak samo, jak argumenty użyte poprzednio dla następnego uruchomienia makra, ale dodatkowo z argumentem Schedule ustawionym na False.

Druga procedura zdarzenia uruchamiana będzie po każdym otwarciu skoroszytu. Na początku tej procedury aktywowany jest arkusz Przypomnienie. Następnie sprawdzana jest wartość komórki B7. Jeśli ta wartość jest mniejsza lub równa 7, to uruchamiana jest procedura Miganie oraz zmienna czy_włączony ustawiana jest na wartość True. W przeciwnym wypadku ustawiany jest brak koloru wypełnienia komórki B7 za pomocą zmiany właściwości ColorIndex na wartość xlColorIndexNone.

6. Zamykamy Edytor Visual Basic.

Po zapisaniu skoroszytu i jego ponownym otwarciu kod Visual Basic zawarty w skoroszycie będzie powodował aktywację arkusza Przypomnienie oraz automatyczne sprawdzenie wartości komórki B7 w tym arkuszu. Jeśli pozostało 7 lub mniej dni do kolejnego przeglądu, to co chwila kolor wypełnienia tej komórki będzie zmieniany na przemian na kolor żółty i czerwony.

6.6. Sumowanie warunkowe według koloru

Na pewno nieraz zdarzyło Ci się korzystać z funkcji SUMA.JEŻELI, która sumuje dane z jednej kolumny na podstawie odpowiadających im wartości w innej kolumnie (kryteriów). Czasami jednak kryteria nie są określone wartościami, ale kolorami. Takiego obliczenia funkcja SUMA.JEŻELI nie wykona.

Proponujemy zatem przygotowanie własnej funkcji w języku VBA. Przykładowe dane do podsumowania przedstawia rysunek 6.12.

Rysunek 6.12. Niektórzy klienci zostali oznaczeni żółtym kolorem

Aby przygotować odpowiednią funkcję:

1. Otwórz Edytor VBA, wciskając kombinację klawiszy [Alt]+[F11].

2. Z menu Insert wybierz polecenie Module.

3. Wpisz następujący kod:

Function SumaKolorow(ByVal r As Range)

Dim j As Range

Dim k As Double

For Each j In r

If j.Offset(0, – 1).Interior.ColorIndex = 6 Then k = k + j.Value

Next j

SumaKolorow = k

End Function

4. Zapisz i zamknij Edytor VBA.

5. Zaznacz komórkę B12 i wprowadź następującą formułę:

=SumaKolorow(B2:B11)

Mamy gotowy wynik.

Rysunek 6.13. Suma kwot, które odnoszą się do klientów oznaczonych kolorem

Jeżeli komórki oznaczone kolorem (kryteria) są oddalone od kwot o dwie kolumny w lewą stronę, wpisz – 2 w drugim argumencie funkcji Offset. Jeśli leżałyby po prawej stronie, użyj dodatniej liczby określającej przesunięcie.

Funkcja uwzględnia jedynie komórki, których kolor tła jest żółty. Jeżeli użyto innego, zmień liczbę określającą kolor przy właściwości Interior.ColorIndex. Przykładowo: liczba 1 to numer koloru czarnego, 2 – białego, 3 – czerwonego, 4 – zielonego, 5 – niebieskiego, 7 – różowego, 8 – błękitnego, a 9 – brązowego.

6.7. Błędy generowane przez makro w zabezpieczonym arkuszu

Załóżmy, że masz arkusz zawierający dane dotyczące produktów składowanych w magazynie. Odpowiednie makro przy każdej zmianie wartości w danym wierszu wpisuje aktualną datę i czas do kolumny F. Makro wykorzystuje procedurę Worksheet_Change i w zasadzie wszystko działa dobrze do czasu zabezpieczenia arkusza i ograniczenia dostępu do pierwszych pięciu kolumn. Od tego momentu przy każdej zmianie komórek pojawia się błąd kodu makra.

Rysunek 6.14. Arkusz z danymi materiałów w magazynie

Makro wykorzystujące procedurę zdarzenia Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 6 Then

Me.Cells(Target.Row, “F”) = Now

End If

End Sub

Jest możliwe, aby dany zakres komórek nie był zmieniany przez użytkownika arkusza, ale jedynie przez makro.

Rysunek 6.15. Komunikat błędu przy zmianie wartości komórki

Aby to zrobić:

1. Otwórz Edytor Visual Basic, kliknij kartę Deweloper i w grupie poleceń Kod kliknij Visual Basic. Można również użyć kombinacji klawiszy [Alt]+[F11].

2. W oknie przeglądarki projektu VBA kliknij dwukrotnie moduł Ten_skoroszyt.

3. W oknie, które się pojawi, wpisz następujący kod:

Private Sub Workbook_Open()

Me.Worksheets(“Materiały”).Protect “a”, UserInterfaceOnly:=True

End Sub

W tym kodzie wykorzystane zostaje zdarzenie otwierania skoroszytu. W procedurze tego zdarzenia wywoływana jest metoda Protect zakładająca hasło ochrony arkusza. Hasło to podane w pierwszym argumencie metody Protect musi być takie samo, jak wpisane za pomocą polecenia Chroń arkusz. Ustawiając argument UserInterfaceOnly na wartość True, spowodujemy, że ochrona nie będzie dotyczyć zmian wprowadzanych za pomocą kodu Visual Basic.

Musisz pamiętać, że argument UserInterfaceOnly może być ustawiony jedynie za pomocą makra. Dlatego jeżeli usuniemy, a następnie nałożymy ochronę arkusza za pomocą polecenia Recenzja/Chroń arkusz, to jego specyficzne działanie zostanie anulowane i będzie przywrócone dopiero po zamknięciu i ponownym otwarciu skoroszytu.

4. Zapisz i zamknij skoroszyt. Teraz zmiany nie będą powodowały wyświetlania komunikatu błędu i makro zadziała poprawnie.

6.8. Podział scalonych komórek z kopiowaniem wartości

Przyjmijmy, że mamy kilkanaście arkuszy z listą zamówień, w których komórki w kolumnie Rok zostały scalone. Widok arkusza przedstawia rysunek.

Potrzebujemy jednak standardowego układu danych, np. w celu stworzenia tabeli przestawnej. Wykonanie tej operacji bez pomocy makra jest nieco kłopotliwe. Otwórzmy więc Edytor Visual Basic, utwórzmy nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpiszmy w nim następujący kod:

Sub Podziel_i_Kopiuj()

Dim adres As String

adres = ActiveCell.MergeArea.Address

If adres <> ActiveCell.Address Then

ActiveCell.UnMerge

ActiveCell.Copy

ActiveSheet.Paste ActiveSheet.Range(adres)

Application.CutCopyMode = False

End If

End Sub

Rysunek 6.16. Lista zamówień ze scaloną komórką w kolumnie Rok

Jeśli chcemy podzielić scaloną komórkę zawierającą rok i wstawić do tak utworzonych komórek jej zawartość, to zaznaczmy komórkę B2:B17 i włączmy makro Podziel_i_Kopiuj.

W kodzie zadeklarowano tylko jedną zmienną. Przypisany jest do niej adres zakresu scalonych komórek, których częścią jest komórka aktywna. Aktywna komórka to ta z zaznaczonych, której adres po zaznaczeniu będzie widniał w polu nazwy. Jeśli aktywna komórka nie jest scalona, to do zmiennej będzie przypisany tylko jej adres. W dalszej kolejności makro sprawdza, czy komórka jest scalona poprzez porównanie adresów aktywnej komórki i adresu zawartego w zmiennej adres. Gdy aktywna komórka nie jest scalona, to do zmiennej będzie przypisany tylko jej adres, w przeciwnym razie – adres wielu komórek. Jeśli komórka jest scalona, to poprzez polecenie UnMerge powodujemy jej podział. Następnie jest kopiowana i wklejana do zakresu komórek określonych w zmiennej adres.

Po kopiowaniu ustawiono właściwość CutCopyMode na wartość False, aby opuścić tryb kopiowania.

Arkusz z listą zamówień zostanie zmodyfikowany, co zostało przedstawione na rysunku.

Rysunek 6.17. Lista zamówień po uruchomieniu makra

Scalona komórka może zawierać wiele komórek zarówno w pionie, jak i w poziomie. Jej wartość zostanie wstawiona do wszystkich komórek utworzonych przez dokonany podział. Scalona komórka może zawierać formułę. W takim przypadku po uruchomieniu makra ta formuła zostanie wstawiona do komórek utworzonych przez podział z zachowaniem odwołań względnych, o ile nie zawiera znaków odwołań bezwzględnych $.

Jeśli chcemy, aby makro można było jednocześnie zastosować w wielu scalonych komórkach zaznaczonego obszaru, to dodajmy deklarację zmiennej, która będzie pełniła funkcję licznika pętli:

Dim i As Long

Przed pierwszą pętlą dodajmy linię rozpoczynającą pętlę po wszystkich zaznaczonych komórkach:

For i = 1 To Selection.Count

Po zakończeniu bloku warunkowego if dopiszmy linię kończącą pętlę:

Next

Poza tym zamiast odwołania do aktywnej komórki ActiveCell wpiszmy odwołanie do kolejnej komórki w zaznaczonym obszarze: Selection.(i). Cały kod zmodyfikowanego makra:

Sub Podziel_i_Kopiuj()

Dim adres As String

Dim i As Long

For i = 1 To Selection.Count

adres = Selection(i).MergeArea.Address

If adres <> Selection(i).Address Then

Selection(i).UnMerge

Selection(i).Copy

ActiveSheet.Paste ActiveSheet.Range(adres)

Application.CutCopyMode = False

End If

Next

End Sub

Pętla, która przebiega po każdej komórce zaznaczonego obszaru komórek, powoduje, że pozostałe polecenia makra są uruchamiane dla każdej komórki w podświetlonym zakresie.

Zaznaczony obszar może zawierać także niescalone komórki, które będą przez makro ignorowane. Uruchomienie makra bez tej modyfikacji przy zaznaczeniu wielu scalonych komórek spowoduje podział tylko jednej – aktywnej komórki.

6.9. Scalenie komórek w pionie, jeśli następne komórki są puste

Załóżmy, że mamy arkusz z przychodami poszczególnych sklepów firmy. Sklepy znajdujące się w jednym mieście są wpisane obok siebie, ale nazwa miasta podana jest tylko w pierwszym wierszu. Kolejne sklepy nie mają zatem wypełnionej kolumny B.

Rysunek 6.18. Arkusz z listą sklepów, który chcemy zmodyfikować

Teraz chcemy zmodyfikować to zestawienie w taki sposób, aby scalić puste komórki w kolumnie B wraz z nazwą miasta występującą powyżej. To sprawi, że zestawienie będzie bardziej czytelne. Scalanie poszczególnych grup komórek może trochę potrwać, dlatego warto przygotować makro, które ułatwi modyfikację arkusza.

Otwórzmy Edytor Visual Basic, utwórzmy nowy moduł standardowy za pomocą polecenia menu Insert/Module i wpiszmy następujący kod:

Sub ScalPionowoPuste()

Dim i As Long

Dim ile As Long

ile = 0

For i = Selection.Rows.Count To 1 Step – 1

If Selection.Cells(i, 1) = “” Then

ile = ile + 1

ElseIf ile > 0 Then ActiveSheet.Range(Selection.Cells(i, 1), Selection.Cells(i + ile, 1)).Merge

Selection.Cells(i, 1).VerticalAlignment = xlVAlignCenter

ile = 0

End If

Next

End Sub

Jeśli chcemy scalić zakres w kolumnie Miasto w taki sposób, aby pozbyć się pustych komórek, zaznaczmy obszar B2:B17 i włączmy makro ScalPionowoPuste. Komórki zostaną scalone w odpowiedni sposób, a wynik działania tego makra przedstawiony jest na rysunku.

Gdy zaznaczymy wiele kolumn zawierających puste komórki, to po uruchomieniu makra scalone zostaną komórki tylko w pierwszej kolumnie zaznaczonego obszaru. Jeśli pierwsza komórka w lewym górnym rogu podświetlonego zakresu będzie pusta, to nie zostanie scalona, a także nie zostaną połączone puste komórki znajdujące się bezpośrednio pod nią.

Rysunek 6.19. Widok arkusza po uruchomieniu makra

W kodzie są zdefiniowane dwie zmienne – jedna z licznikiem pętli, a druga z licznikiem pustych, występujących po sobie komórek. Na początku zerujemy zmienną ile zliczającą liczbę pustych komórek. Następnie w pętli przechodzimy po kolejnych komórkach w pierwszej kolumnie zaznaczonego obszaru, począwszy od tej położonej najniżej.

Dla każdej takiej komórki sprawdzamy, czy jest pusta. Jeśli tak jest, to zwiększamy wartość zmiennej ile. Gdy komórka nie jest pusta, a zmienna ile ma wartość większą od zera, to scalamy komórki znajdujące się pod tą komórką – z nią włącznie. Liczba scalanych komórek jest zależna od wartości zmiennej ile.

Na końcu pętli, dzięki ustawieniu właściwości VerticalAlignment, stosujemy wyśrodkowanie scalonej komórki, a następnie zerowana jest wartość zmiennej przechowującej liczbę pustych komórek.

Jeśli nie chcemy, aby teksty scalonych komórek były wyśrodkowane w pionie, to usuńmy następującą linię:

Selection.Cells(i, 1).VerticalAlignment = xlVAlignCenter

Jeśli makro ma działać w wielu kolumnach, to w deklaracji zmiennych dodajmy linię:

Dim j As Long

Następnie przed pętlą przechodzącą przez wszystkie zaznaczone komórki w pierwszej kolumnie wpiszmy linię rozpoczynającą pętlę przechodzącą przez kolejne kolumny zaznaczonego obszaru:

For j = 1 To Selection.Columns.Count

Na samym końcu dodajmy linię kończącą pętlę:

Next

We wszystkich wystąpieniach obiektu Cells, w drugim argumencie, zamiast liczby 1 wpiszmy zmienną j, np.: Selection.Cells(i, j).

Całość zmodyfikowanego kodu:

Sub ScalPionowoPuste()

Dim i As Long

Dim j As Long

Dim ile As Long

ile = 0

For j = 1 To Selection.Columns.Count

For i = Selection.Rows.Count To 1 Step – 1

If Selection.Cells(i, j) = “” Then

ile = ile + 1

ElseIf ile > 0 Then ActiveSheet.Range(Selection.Cells(i, j), Selection.Cells(i + ile, j)).M