excelpogodzinach-logo
Makra - różne wartości - Excel Po Godzinach

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.
 
Makra - różne wartości - Excel Po Godzinach
 
Makra - różne wartości - Excel Po Godzinach
KROK 2: W oknie dialogowym Opcje programu Excel wybierz kategorię Dostosowywanie Wstążki.
 
Makra - różne wartości - Excel Po Godzinach
KROK 3: W prawym oknie Karty główne zaznacz kartę Deweloper klikając w mały kwadracik umieszczony z lewej strony.
 
Makra - różne wartości - Excel Po Godzinach
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.
 
Makra - różne wartości - Excel Po Godzinach
KROK 2: W grupie Kontrolki ActiveX kliknij Przycisk polecenia.
 
Makra - różne wartości - Excel Po Godzinach
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.
 
Makra - różne wartości - Excel Po Godzinach

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.
 
Makra - różne wartości - Excel Po Godzinach
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.
 
Makra - różne wartości - Excel Po Godzinach

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.
 
Makra - różne wartości - Excel Po Godzinach

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.
 
Makra - różne wartości - Excel Po Godzinach
 
Makra - różne wartości - Excel Po Godzinach

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.
 
Makra - różne wartości - Excel Po Godzinach
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.
 
Makra - różne wartości - Excel Po Godzinach
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.
 
Makra - różne wartości - Excel Po Godzinach

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.

   Wyświetlanie dwóch różnych wartości – Excel Po Godzinach

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.

Podobał Ci się ten artykuł?

Jeśli tak, to dołącz do grona Czytelników bloga Excel Po Godzinach, którzy każdego tygodnia otrzymują sprawdzone porady, wskazówki oraz rozwiązania nietypowych problemów.

4 myśli na temat “Wyświetlanie dwóch różnych wartości za pomocą makra

  1. Dzień dobry p. Michale dziękuje za dobrą lekcję-przykład. Wszystko dobrze wytłumaczone bez nie domówień. Jeśli chodzi o Makro jest to ciężki temat. Brak dobrych książek w tym zakresie , a jeśli już są to nie pisane jeżykiem prostym bardziej naukowym, gdzie nie można pojąć o chodzi. Również trzeba brać pod uwagę poziom nauczania w naszych uczelniach nie mówiąc o szkołach średnich. Dla młodzieży jest to czarna magia VBA jak i dla zwykłego użytkownika Excel-a.Pozdrawiam i wielki szacunek za zaangażowanie w prowadzeniu bloga

    1. Witaj Jankesd (Darku)! :) Cieszę się, że podoba Ci się sposób w jaki przedstawiam excelową wiedzę. Co do makr VBA to zawsze na początku wydają się być „czarną magią”, ale wcale tak nie jest. Faktycznie pozycji książkowych przyzwoicie tłumaczących zasady programowania VBA jest jak na lekarstwo. Sugerowałbym zacząć od vademecum Johna Walkenbacha – Excel 2013 PL. Programowanie w VBA, gdzie omówionych jest wiele tematów na ciekawych przykładach. Również w książce Excel 2013 PL. Biblia, tego samego autora, znaleźć można opis podstawowych instrukcji, pętli, wyjaśnienie jak tworzyć formularze, jak debugować procedury itp. A później pozostaje już samodzielna nauka.
      Poziom nauczania, który pozostawia wiele do życzenia, może wynikać między innymi z niezrozumienia przekazywanej wiedzy, albo co gorsza z braku świadomości jak duże znaczenie na rynku pracy ma znajomość Excela (np. w branży finanse, ekonomia, księgowość w ponad 35% wszystkich ofert pracy wymaga się bardzo dobrej znajomości tego programu). Dlatego też moim celem jest nie tylko przekazywanie technicznej wiedzy, ale również, a może i przede wszystkim uświadamianie, że znajomość arkusza kalkulacyjnego, którego na co dzień używa ponad 750 milionów (oficjalne dane Microsoftu) ludzi na całym świecie jest w dzisiejszych czasach wręcz koniecznością.

      Pozdrawiam

  2. Mam kolejne pytanie i może nie jest związane z opisanym tematem.Panie Michale ponownie zawracam Panu głowę lecz nurtuje mnie jeden problem w Excelu. Przy robieniu makr chciałbym mieć ewentualną możliwość jeśli taka jest opcja-cofania zmian wywołanych makrą.Pozdrawiam Jankesd

    1. Witaj ponownie Jankesd! Widzę, że cały czas pracujesz z Excelem i wciąż pojawiają się coraz to ciekawsze pytania. Tak trzymaj! :) Jeżeli zaś chodzi o cofanie zmian, które zaszły w arkuszu po uruchomieniu kodu makra, to niestety muszę Cię zmartwić. Jednym z głównych ograniczeń Excela jest to, że wykonanie dowolnego kodu makra uniemożliwia użytkownikowi skorzystanie z opcji cofnięcia zmian i powrotu do stanu sprzed. Uruchomione makro niszczy zapisy cofania/powtarzania czynności, czego efektem jest brak możliwości powrotu do stanu poprzedniego.

      Oczywiście są sposoby zaradzenia tego (przynajmniej w pewnym stopniu). Pierwszy z nich wymaga zapisania skoroszytu przed uruchomieniem makra i jeśli po zakończeniu jego działania zmiany będą niezadowalające, zawsze możesz zamknąć skoroszyt bez jego zapisywania, a następnie otworzyć go ponownie. Nadal będziesz miał stan pierwotny. Drugi sposób to stworzenie odpowiedniego makra (jest to skomplikowane wyzwanie i wymaga dużego nakładu pracy), które pozwala na cofnięcie wielu, ale nie wszystkich zmian będących wynikiem działania uruchamianego przez Ciebie makra. Nie wiem czy takiej odpowiedzi spodziewałeś się, ale takie są fakty.

      Pozdrawiam

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *