Jak określić zakres danych do formatowania przez użytkownika?

Z lekcji dowiesz się:

• W jaki sposób wskazywać zakres danych do edytowania przez użytkownika?

• Jak stworzyć makro?

• Do czego służy funkcja Aplication.InputBox?

Z lekcji dowiemy się jak otrzymać możliwy zakres modyfikacji w pliku Excel. Będziemy chcieli w nim zmienić przykładowe dane, tzn. dołożyć odpowiednie formatowanie, krawędzie itp. Zadanie to wykonamy na podstawie przykładowych danych z rys. 1.

(Rys. 1) Przykładowe dane

Zaznaczamy zakres danych, który chcemy zmienić, czyli komórki B2:E13, a następnie wybieramy polecenie Zarejestruj makro z karty Deweloper (rys. 2).

(Rys. 2) Polecenie Zarejestruj makro

Otworzy się okno Rejestracji makra, gdzie w polu Nazwa makra zostawiamy domyślną nazwę Makro1 oraz analogicznie w polu „Przechowuj makro w” zostawiamy domyślnie Ten skoroszyt. Zatwierdzamy rejestrowanie makra przyciskiem OK (rys. 3).

(Rys. 3) Okno rejestrowania makra

Możemy przejść do edycji. Na przykład możemy pogrubić dane za pomocą ikony na karcie Narzędzia główne (rys. 4).

(Rys. 4) Ikona do pogrubiania czcionki

Następnie możemy użyć wypełnienia danych dowolnym kolorem za pomocą ikony na karcie Narzędzia główne (rys. 5).

(Rys. 5) Wypełnianie danych kolorem

Możemy również nałożyć odpowiednie formatowanie, w tym celu wybieramy formatowanie walutowe, rozwijając pole formatowania w grupie poleceń Liczba, na karcie Narzędzie główne (rys. 6).

(Rys. 6) Zmiana formatowania na walutowe

Dodatkowo możemy dołożyć odpowiednie obramowanie. W tym celu rozwijamy ikonkę obramowań na karcie Narzędzia główne, następnie wybieramy obramowania na wszystkich krawędziach (rys. 7).

(Rys. 7) Obramowanie wszystkich krawędzi

Te działania pokazują ile różnych działań możemy wykonać na zakresie, który poda nam użytkownik. Dane po zmianach zostały przedstawione na rys. 8.

(Rys. 8) Dane po wprowadzeniu zmian

Przechodzimy do polecenia Zatrzymaj makro z karty Deweloper (rys. 9).

(Rys. 9) Polecenie Zatrzymaj makro

Otrzymaliśmy dane po zmianach, ale teraz za pomocą skrótu klawiszowego Ctrl+Z cofamy wszystkie wprowadzone zmiany, aby otrzymać dane sprzed modyfikacji. Używając skrótu klawiszowego Alt+F11, przychodzimy do kodu VBA. Przygotowane makro zostało zarejestrowane w folderze Modules – Module1 (zaznaczone zieloną strzałką na rys. 10).

(Rys. 10) Kod zarejestrowanego makra w VBA

Pojawia się informacja, że makro pracowało na zaznaczonym zakresie (selection). W pierwszej kolejności wykonaliśmy pogrubienie czcionki, zapis dla tej czynności w VBA brzmi Selection.Fond.Bold=True. Następnie wykonywaliśmy wypełnianie komórek kolorem, gdzie Excel pokazuje dużo więcej informacji, szczególnie kiedy skorzystamy z gotowego motywu. Zapis dla tej czynności jest dużo bardziej rozbudowany:

With Selection.Interior

• Pattern = xlSolid

• PatternColorIndex = xlAutomatic

• ThemeColor = xlThemeColorDark1

• TintAndShade = -0.149998474074526

• PatternTintAndShade = 0

End with

Następnie w kodzie widnieje zapis nakładania formatowania walutowego domyślnego, czyli z dwoma miejscami po przecinku. Musimy pamiętać, że VBA jest programem amerykańskim, więc mimo, iż w danych mamy złotówki, to w kodzie zapis dotyczy dolarów (rys. 11):

Selection.NumberFormat = "#,## 0.00 $"

(Rys. 11) Fragment kodu dotyczący nakładania formatowania walutowego na dane

Teraz przechodzimy do części kodu dotyczącej dodawania obramowania, który jest bardzo długi. Postaramy się teraz go skrócić, ponieważ w każdej części:

With Selection.Border (xlEdgeLeft)

• LineStyle = xlContinuos

• ColorIndex = 0

• TintAndShade = 0

• Weight = xlThin

End With

jest opisana każda możliwa krawędź występująca w obramowaniu (z lewej, prawej, z góry i dołu). Nie jest to potrzebne. Jeśli skasujemy z kodu zapis xlEgdeLeft sprawimy, że kod będzie dotyczył wszystkich krawędzi oprócz diagonalnych, których się nie definiuje (Selection.Borders (xlDiagonalDown).LineStyle = xlNone, Selection.Borders (xlDiagonalUp).LineStyle = xlNone).

Podsumowując po zmianie (usunięciu zapisu xlEgdeLeft) resztę kodu dotyczącą poszczególnych krawędzi możemy usunąć. Do obramowania zaznaczonego zakresu wystarczy zapis przedstawiony na rys. 12.

(Rys. 12) Fragment kodu obramowania zaznaczonego zakresu

Zamiast słowa Selection będziemy chcieli w kod wstawić zakres podany przez użytkownika. Dalszą część makra dopiszemy ręcznie:

Sub PodajZakres ()

End Sub

W ten sposób stworzymy makro. Następnie musimy zadeklarować wartość zmienną (dim r). Zapis będzie wyglądał następująco:

Sub PodajZakres ()

Dim r As Range

End Sub

Mamy już zadeklarowaną zmienną r - chcemy tej zmiennej przypisać wynik zapytań do użytkownika, jaki zakres chce on podać. Użyjemy tutaj funkcji InputBox, ale nie możemy jej po prostu wpisać, ponieważ nie będzie mieć interesującego nas argumentu Typ (nie rozróżnia typów danych). Wystarczy użyć tej funkcji z Aplication, wtedy w argumentach pojawi się argument Type (rys. 13).

(Rys. 13) Argument Type w funkcji Aplication.InputBox

Argument ten mamy rozpisany w osobnym arkuszu, żeby było wiadomo, co oznacza wpisana przez nas liczba (rys. 14).

(Rys. 14) Rozpisany argument Type

Interesuje nas Zakres (Range), czyli musimy wpisać wartość 8, oznacza to odwołanie do zakresu komórek. Zapis będzie wyglądał następująco:

Sub PodajZakres ()

Dim r As Range

Set r = Aplication.InputBox

End Sub

Zajmiemy się teraz podaniem argumentów funkcji Aplication.InputBox. Pierwszym argumentem funkcji jest Prompt as string, czyli tekst. Wystarczy w cudzysłowie podać odpowiedni tekst, mianowicie „Podaj zakres, który chcesz sformatować”. Aby móc kontynuować formułę w następnej linijce musimy wpisać znak _.

Kolejny argument to Title, czyli tytuł okienka, które będzie się pojawiało na ekranie, podamy tutaj „Zakres do podania”.

Trzeci argument to zmienna Default, czyli to co będzie się domyślnie wyświetlało w polu wypełnienia, podamy tutaj „A1” (zakres komórek).

Kolejne argumenty dotyczące położenia okna nas nie interesują, nie będziemy więc ich określać. Aby nie wstawiać kilku przecinków, musimy napisać w kodzie Type:=, co będzie oznaczało, że przechodzimy od razu do argumentu Type.

Sub PodajZakres ()

Dim r As Range

Set r = Aplication.InputBox ("Podaj zakres, który chcesz sformatować", _

"Zakres do podania", "A1", Type:=8)

End Sub

Możemy uruchomić makro za pomocą ikonki Play (zaznaczone strzałką na rys. 15).

(Rys. 15) Uruchamianie napisanego makra

Otworzy się okno Zakres do podania, gdzie w polu Podaj zakres, który chcemy sformatować, musimy podać odpowiedni zakres (rys. 16).

(Rys. 16) Okno Zakresu do podania

Możemy zakres wpisać ręcznie, zaznaczyć go myszką lub nawet zaznaczyć kilka zakresów przytrzymując klawisz Ctrl. Podany zakres zatwierdzamy przyciskiem OK. Warto zauważyć, że w polu Podaj zakres podamy zakres bądź nazwę, której Excel nie rozpozna. Wyświetli się więc komunikat, w którym Excel będzie prosił o podanie prawidłowego zakresu (rys. 17).

(Rys. 17) Komunikat Excela o zmianie zakresu danych

Pozostaje jeszcze naprawienie kolejnego błędu polegającego na pozostawieniu pustej komórki. W takiej sytuacji wyświetli się komunikat o nieprawidłowej formule (rys. 18).

(Rys. 18) Komunikat Excela o nieprawidłowej formule

Wyświetli się komunikat Excela, a nie z VBA. Dopiero kiedy zamkniemy okienko Zakresu do podania, wyświetli się komunikat o błędzie w VBA (rys. 19).

(Rys. 19) Komunikat o błędzie w VBA

Nie chcemy aby w takiej sytuacji Excel odsyłał do VBA, więc należy napisać obsługę tego błędu. Wystarczy w kodzie zapisać sformułowanie On error resume next. Zapis formuły powinien wyglądać następująco:

Sub PodajZakres ()

Dim r As Range

On Error Resume Next

Set r = Aplication.InputBox ("Podaj zakres, który chcesz sformatować", _

"Zakres do podania", "A1", Type:=8)

End Sub

Taki zapis sprawi, że w sytuacji błędu, VBA ma przejść dalej i wykonać kolejny etap. Musimy jednak wrócić zapisem On Eror GoTo 0, co sprawi, że wyświetli się komunikat o debagowaniu dla programisty. Jeśli zamkniemy ten komunikat to nasza zmienna r nie zostanie przypisana. Musimy określić, co ma zrobić VBA w sytuacji, kiedy nie zostanie określona zmienna r (wyjść z makra, zakończyć jego pracę). Zapis powinien wyglądać następująco:

Sub PodajZakres ()

Dim r As Range

On Error Resume Next

Set r = Aplication.InputBox ("Podaj zakres, który chcesz sformatować", _

"Zakres do podania", "A1", Type:=8)

On Error GoTo 0

If r Is Nothing Then Exit Sub

End Sub

Podsumowując - dzięki takiemu zapisowi, jeśli użytkownik zamknie działanie makra przyciskiem x, to makro zakończy działanie i wróci bez żadnego komunikatu do naszego kodu. Teraz będziemy chcieli wykorzystać kod, który zarejestrowaliśmy w karcie Deweloper (pogrubienie czcionki, wypełnienie kolorem oraz obramowanie danych), czyli kopiujemy cały ten kod i wklejamy go do napisanego kodu. W każdym miejscu gdzie było rejestrowane Selection, musimy zmienić kod i wstawić parametr r. Zapis powinien wyglądać następująco:

Sub PodajZakres ()

Dim r As Range

On Error Resume Next

Set r = Aplication.InputBox ("Podaj zakres, który chcesz sformatować", _

"Zakres do podania", "A1", Type:=8)

On Error GoTo 0

If r Is Nothing Then Exit Sub

r.FontBond= True

With r.Interior

• Pattern = xlSolid

• PatternColorIndex = xlAutomatic

• ThemeColor = xlThemeColorDark1

• TintAndShade = -0.149998474074526

• PatternTintAndShade = 0

End with

r.NumberFormat = "#,## 0.00 $"

With Selection.Border ()

• LineStyle = xlContinuos

• ColorIndex = 0

• TintAndShade = 0

• Weight = xlThin

End With

End sub

Tak przygotowane makro zapisujemy i możemy je przetestować na prostym zakresie danych z arkusza 2018. Wybieramy polecenie Makra na karcie Deweloper (rys. 20).

(Rys. 20) Polecenie Makra

Otworzy się okno, w którym możemy wybrać makro, którego chcemy użyć. Ponadto po wybraniu odpowiedniego makra, jeśli użyjemy przycisku Opcje, otworzy się okno Opcji makra, gdzie możemy przypisać skrót klawiszowy do uruchamiania tego makra. Użyjemy skrótu Ctrl+Shift+D. Przypisany skrót klawiszowy zatwierdzamy przyciskiem OK (rys. 21).

(Rys. 21) Przypisywanie skrótu klawiszowego do uruchamiania makra

Wracamy do okna Makr, gdzie wybrane makro uruchamiamy za pomocą przycisku Uruchom (rys. 22).

(Rys. 22) Okno Makra

Wyświetli się okienko makra Zakres do podania, gdzie podajemy zakres (zaznaczamy), np. kolumny B (Przychód). Zaznaczony zakres do sformatowania zatwierdzamy przyciskiem OK (rys. 23).

(Rys. 23) Okno Zakresu do podania

Otrzymamy sformatowany zakres, który zaznaczyliśmy (rys. 24).

(Rys. 24) Sformatowany zakres

Do uruchomienia makra użyjemy zdefiniowanego skrótu klawiszowego Ctrl+Shift+D. Otworzy się okno Zakresu do podania, gdzie po raz kolejny wprowadzamy zakres, który chcemy sformatować. Zaznaczymy tym razem kolumnę C (Koszt) oraz przytrzymując klawisz Ctrl kolumnę E (Plan sprzedaży). Tak podane zakresy zatwierdzamy przyciskiem OK (rys. 25).

(Rys. 25) Zaznaczone dwa zakresy do sformatowania

Otrzymamy sformatowane dane w dwóch kolumnach (rys. 26).

(Rys. 26) Sformatowane dane w dwóch zakresach

Przejdziemy teraz do arkusza 2019, gdzie zaznaczymy fragment zakresu i nazwiemy go Zakres, następnie zatwierdzimy tę czynność przyciskiem Enter (zaznaczone strzałką na rys. 27).

(Rys. 27) Nazwany zakres

Uruchamiamy makro za pomocą skrótu klawiszowego Ctrl+Shift+D. Pojawi się okienko Zakresu do podania, gdzie zamiast zaznaczać zakres wpiszemy nazwę wcześniej zdefiniowanego zakresu (Zakres) – rys. 28.

(Rys. 28) Podanie nazwanego zakresu, który chcemy sformatować

Po zatwierdzeniu zakresu, który chcemy sformatować, otrzymamy odpowiednio sformatowane dane przedstawione na rys. 29.

(Rys. 29) Sformatowany nazwany zakres

Makro zadziałało poprawnie ponieważ wpisaliśmy nazwę zakresu, który faktycznie istnieje. Pokazaliśmy działanie makra, które służy do sformatowania zakresu wskazanego przez użytkownika. Jest to bardzo przydatne narzędzie, które pomoże nam zaoszczędzić sporo czasu. Wszystko to jest możliwe dzięki funkcji Aplication.InputBox, która pozwala określić typ danych, na których chcemy pracować (zakres - Range).

Adam Kopeć

Polecamy także:

Jak stworzyć tabelę aktualizującą się danymi z Internetu?

Jak sprytnie przeklejać wartości w puste pola?