Wyświetlanie dwóch różnych wartości za pomocą makra

Wyobraź sobie taką sytuację, w której przygotowujesz skoroszyt zawierający dwa arkusze. Jeden z nich to obszar roboczy, natomiast drugi zawiera informacje źródłowe zarówno w języku polskim jak i angielskim. Twoim zadaniem jest stworzenie przycisku w arkuszu roboczym, którego naciśnięcie spowoduje wyświetlenie wartości z ukrytego arkusza źródłowego w odpowiedniej wersji językowej. Istotne jest również, aby po każdym kliknięciu przycisku umieszczony w nim tekst zmieniał się i wyświetlał informację o języku, w którym można zaprezentować dane. Ciekawy scenariusz? Właśnie z takim zadaniem zgłosiła się do mnie Justyna, jedna z czytelniczek bloga.

Ponieważ zaprezentowany powyżej przypadek jest ciekawy, dlatego postanowiłem pokazać Ci w jaki sposób można go rozwiązać.

VBA – trzy magiczne litery

Oczywiście nie jest tajemnicą, że do uzyskania prawidłowego efektu końcowego będziemy musieli stworzyć przycisk oraz napisać krótkie makro VBA, które będzie odpowiadało zarówno za wyświetlanie odpowiednich wartości w arkuszu roboczym jak i napisu widniejącego na przycisku.

Jeżeli do tej pory nie korzystałeś z makr w Excelu, to tylko tytułem wstępu wyjaśnię, że są to małe programy, których zadaniem jest automatyzacja czynności wykonywanych w arkuszu kalkulacyjnym. Bardzo często makra stosuje się w przypadku powtarzających się, rutynowych zadań. Można je również stosować przy bardziej zaawansowanych projektach np. tworzeniu skomplikowanych raportów sprzedażowych.

Do tworzenia makr wykorzystuje się język VBA (Visual Basic for Applications), który jest dostępny w programach pakietu Office. Jeżeli więc nauczysz się lub już potrafisz pisać makra w Excelu, to zdobytą wiedzę będziesz mógł wykorzystać do tworzenia podobnych automatyzacji np. w edytorze tekstu Word lub kliencie poczty elektronicznej Outlook.

Karta Deweloper

Zanim przejdziemy do konkretów należy sprawdzić czy na wstążce Excela jest widoczna karta Deweloper. Jeżeli tak, to możesz przejść do dalszej części artykułu, w przeciwnym razie musisz kartę dodać.

Aby aktywować kartę Deweloper należy postępować według poniższej instrukcji.

KROK 1: Wybierz kartę Plik, a następnie w menu Backstage kliknij Opcje.

Wyświetlanie dwóch różnych wartości za pomocą makra

Wyświetlanie dwóch różnych wartości za pomocą makra

KROK 2: W oknie dialogowym Opcje programu Excel wybierz kategorię Dostosowywanie Wstążki.

Wyświetlanie dwóch różnych wartości za pomocą makra

KROK 3: W prawym oknie Karty główne zaznacz kartę Deweloper klikając w mały kwadracik umieszczony z lewej strony.

Wyświetlanie dwóch różnych wartości za pomocą makra

KROK 4: Kliknij przycisk OK.

Świetnie! Karta Deweloper jest już na swoim miejscu.

Przycisk polecenia

Teraz musimy wstawić przycisk. Umieścimy go w arkuszu roboczym, ponieważ zgodnie z założeniami zadania użytkownik nie będzie miał dostępu do danych źródłowych.

Wstawienie do arkusza Przycisku polecenia wymaga wykonania następujących czynności.

KROK 1: Wybierz kartę Deweloper, a następnie w grupie Formanty kliknij polecenie Wstaw.

Wyświetlanie dwóch różnych wartości za pomocą makra

KROK 2: W grupie Kontrolki ActiveX kliknij Przycisk polecenia.

Wyświetlanie dwóch różnych wartości za pomocą makra

Kursor myszy zmieni swój tradycyjny wygląd na znak plus (+) co oznacza, że Excel przeszedł w Tryb projektowania.
KROK 3: Wybierz miejsce, w którym chcesz wstawić Przycisk polecenia, a następnie przytrzymując wciśnięty lewy przycisk myszy ustal jego kształt. Zwolnienie lewego przycisku myszy zakończy etap wstawiania.

Wyświetlanie dwóch różnych wartości za pomocą makra

Jak zapewne zauważyłeś w arkuszu pojawił się przycisk zawierający napis CommandButton1 (lub kolejny numer jeśli wcześniej wstawiałeś inne przyciski).

Makro

Na tym etapie prac przechodzimy do najważniejszego elementu, czyli napisania procedury, która będzie uruchamiana po każdorazowym naciśnięciu wstawionego uprzednio przycisku.

Aby przejść od razu do trybu edycji należy dwukrotnie kliknąć wstawiony w arkuszu przycisk. Na ekranie komputera pojawi się edytor Microsoft Visual Basic for Applications, w którym możemy tworzyć nowe makra, modyfikować lub usuwać istniejące, dodawać formularze oraz analizować działanie kodu linia po linii.

Na potrzeby tego artykułu interesować nas będą następujące obszary:

  • Project – okno wyświetlające zawartość naszego skoroszytu,
  • Properties – okno, w którym możemy ustalić właściwości poszczególnych elementów takich jak skoroszyt, arkusz, przycisk itp., oraz
  • Code – okno, w którym umieszczone są procedury i funkcje zawierające instrukcje do wykonania.

Wyświetlanie dwóch różnych wartości za pomocą makra

W momencie, kiedy kliknąłeś przycisk CommandButton1 Excel automatycznie przeniósł Cię do edytora VBA, do arkusza roboczego, a następnie wstawił początek (Private Sub) i koniec procedury (End Sub) o nazwie CommandButton1_Click(). Jak sama nazwa wskazuje procedura ta będzie uruchamiana, kiedy przycisk zostanie naciśnięty przez użytkownika.

Wyświetlanie dwóch różnych wartości za pomocą makra

Przejdź teraz do okna Properties i we właściwości Caption zmień tekst z CommandButton1 na Wersja anglojęzyczna. Następnie kliknij w oknie Code, gdzie należy wprowadzić stosowne instrukcje (pomiędzy początek i koniec procedury) tak, aby po kliknięciu przycisku w arkuszu zaszły oczekiwane zmiany.

Wyświetlanie dwóch różnych wartości za pomocą makra

Poniżej znajdziesz pełen kod procedury, który należy przepisać lub przekopiować do edytora VBA.

Private Sub CommandButton1_Click()
 Application.ScreenUpdating = False
Select Case Sheets(“Arkusz1”).CommandButton1.Caption
Case “Wersja anglojęzyczna”
Sheets(“Arkusz1”).CommandButton1.Caption = “Wersja polskojęzyczna”
Range(“A5:B8”).Select
Selection.Clear
Sheets(“Arkusz2”).Activate
Sheets(“Arkusz2”).Range(“D1:E4”).Select
Selection.Copy
Sheets(“Arkusz1”).Select
Range(“A5”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns(“A:B”).EntireColumn.AutoFit
Range(“A1”).Select
Case “Wersja polskojęzyczna”
Sheets(“Arkusz1”).CommandButton1.Caption = “Wersja anglojęzyczna”
Range(“A5:B8”).Select
Selection.Clear
Sheets(“Arkusz2”).Activate
Sheets(“Arkusz2”).Range(“A1:B4”).Select
Selection.Copy
Sheets(“Arkusz1”).Select
Range(“A5”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns(“A:B”).EntireColumn.AutoFit
Range(“A1”).Select
End Select
Application.ScreenUpdating = True
End Sub

Kiedy wpisałeś poprawnie wszystkie instrukcje możesz przejść z powrotem do skoroszytu używając skrótu klawiaturowego Alt + F11, natomiast korzystając z kombinacji klawiszy Alt + Q zamkniesz edytor VBA i również przejdziesz do Excela.

Jeśli prawidłowo wykonałeś wszystkie opisane powyżej elementy naszego zadania, to teraz po naciśnięciu przycisku, na którym obecnie wyświetlany jest tekst Wersja anglojęzyczna, Excel wprowadzi do zakresu A5:B8 wartości z arkusza źródłowego w języku angielskim oraz zmieni tekst na przycisku zgodnie z poleceniami umieszczonymi w makrze.

Wyświetlanie dwóch różnych wartości za pomocą makra

Wyświetlanie dwóch różnych wartości za pomocą makra

Czas na przegląd

Oczywiście bardzo łatwo jest przekopiować gotowy kod marka, jednakże zależy mi, abyś zrozumiał co oznaczają poszczególne polecenia i jaki dają efekt końcowy. Dlatego też krótko i zwięźle prześledzimy razem najważniejsze elementy marka.

Application.ScreenUpdating – jest to polecenie, które włącza lub wyłącza odświeżanie ekranu w Excelu. Jeżeli chcesz ukryć pokazywanie wykonywanych przez makro czynności, to wtedy przypisujesz temu poleceniu wartość FALSE. W przeciwnym przypadku podaj wartość TRUE. Wyłączanie odświeżania ekranu przy dużych skoroszytach i ogromnej liczbie wykonywanych operacji na danych może znacznie poprawić szybkość działania makra. W naszym przypadku wyłączyliśmy odświeżanie na początku i włączyliśmy ponownie tuż przed zakończeniem procedury.

Select Case – jest to instrukcja, która w naszym przypadku pobiera wartość właściwości Caption z przycisku CommandButton1 i sprawdza czy jest ona taka sama jak tekst Wersja anglojęzyczna lub tekst Wersja polskojęzyczna. Oczywiście, w zależności od tego, który z warunków jest spełniony, instrukcja wykonuje jedną z dwóch grup poleceń.

Sheets(„Arkusz1”).CommandButton1.Caption = „Wersja polskojęzyczna” – to polecenie powoduje przypisanie tekstu Wersja polskojęzyczna do przycisku umieszczonego w arkuszu roboczym (w naszym przypadku jest to Arkusz1).

Range(„A5:B8”).Select – to polecenie zaznacza obszar od komórki A5 do komórki B8. Czasami dla określenia dokładnej lokalizacji zakresu polecenie poprzedzamy nazwą arkusza, np. Sheets(„Arkusz2”).Range(„D1:E4”).Select.

Selection.Clear – instrukcja wymuszająca wyczyszczenie uprzednio zaznaczonego obszaru.

Sheets(„Arkusz2”).Activate – to polecenie aktywuje arkusz o nazwie Arkusz2.

Selection.Copykopiuje aktywną komórkę lub zakres komórek.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False – to polecenie umożliwia wklejenie skopiowanego zakresu komórek jako wartości.

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False – tym razem nie wklejamy wartości komórek, tylko samo ich formatowanie.

Application.CutCopyMode = False – polecenie wyłączające tryb kopiowania.

Columns(„A:B”).EntireColumn.AutoFit – dzięki temu poleceniu możemy automatycznie dostosować szerokość kolumn A i B do wartości w nich umieszczonych.

Warto zapamiętać!
Jeśli podzielisz sobie ekran komputera w taki sposób, że z jednej strony będziesz miał podgląd na arkusz, a z drugiej strony na edytor VBA, to mając aktywne okno tego ostatniego możesz użyć klawisza F8 i zobaczyć jak jedna po drugiej są wykonywane instrukcje marka. Czasami w trakcie szukania błędów w kodzie uruchamianie makra krok po kroku pozwala wychwycić nieścisłości lub nieprawidłowości.

Ukryj głęboko

No i pozostała jeszcze jedna sprawa. Mianowicie musimy ukryć arkusz źródłowy w taki sposób, aby użytkownik nie mógł go odkryć za pomocą tradycyjnej techniki, czyli klikając prawym przyciskiem myszy na karcie widocznego arkusza i wybierając polecenie Odkryj.

Wyświetlanie dwóch różnych wartości za pomocą makra

To, co należy w takim przypadku zrobić, wymaga ponownego powrotu do edytora VBA. Następnie w oknie Project klikamy na Arkusz2, co spowoduje zmianę zawartości okna Properties. W tym ostatnim klikamy we właściwość Visible i zmieniamy ją na opcję 2 – xlSheetVeryHidden.

Wyświetlanie dwóch różnych wartości za pomocą makra

Jeśli teraz przejdziesz ponownie do Excela zauważysz, że dostępny jest tylko jeden arkusz. Próbując odkryć arkusz źródłowy zauważysz, że opcja Odkryj jest niedostępna.

Wyświetlanie dwóch różnych wartości za pomocą makra

Zapisz jako

W ten sposób dotarliśmy do końca naszego zadania. Ostatnią istotną rzeczą do wykonania jest oczywiście zapisanie skoroszytu przy czym należy pamiętać, o tym, aby zapisać go jako skoroszyt programu Excel z obsługą makr.

Przedstawiona powyżej propozycja stanowi jedno z możliwych rozwiązań zadania, które opisała Justyna. Oczywiście wszystko zależy od stopnia skomplikowania sprawy, dostępnych narzędzi i posiadanej wiedzy.

Mam nadzieję, że zaprezentowany przykład będzie stanowił dla Ciebie wstęp do tematyki makr i tworzenia ich z użyciem języka VBA. Tradycyjnie do artykułu dołączam przykładowy skoroszyt zawierający omawiane elementy.

Pobierz plik: Wyświetlanie dwóch różnych wartości

Na koniec chciałbym dowiedzieć się czy korzystasz z makr pracując w Excelu? Czy tworzysz je (nagrywasz) samodzielnie? Podziel się proszę swoimi doświadczeniami umieszczając komentarz pod artykułem. Jeżeli nie korzystasz z nich, to podaj główne powody takiej decyzji.