Spis treści
Nawet jeśli nie wiesz, jak programować, jest wiele miejsc (książki, strony internetowe, fora), w których znajdziesz gotowy kod makr VBA dla ogromnej liczby typowych zadań w Excelu. Z mojego doświadczenia wynika, że większość użytkowników prędzej czy później zbiera swoją osobistą kolekcję makr w celu zautomatyzowania rutynowych procesów, niezależnie od tego, czy chodzi o tłumaczenie formuł na wartości, wyświetlanie sum w słowach, czy sumowanie komórek według kolorów. I tu pojawia się problem – kod makr w Visual Basic musi być gdzieś przechowywany, aby mógł być używany później w pracy.
Najłatwiejszą opcją jest zapisanie kodu makra bezpośrednio w pliku roboczym, przechodząc do edytora Visual Basic za pomocą skrótu klawiaturowego inny+F11 i dodanie nowego pustego modułu za pomocą menu Wstaw – moduł:
Jednak ta metoda ma kilka wad:
- Jeśli plików roboczych jest dużo, a wszędzie potrzebne jest makro, takie jak makro do konwersji formuł na wartości, będziesz musiał skopiować kod w każdej książce.
- Nie wolno zapomnieć zapisz plik w formacie z obsługą makr (xlsm) lub w binarnym formacie książki (xlsb).
- Otwierając taki plik ochrona makro za każdym razem wyda ostrzeżenie, które należy potwierdzić (no cóż, lub całkowicie wyłączy ochronę, co nie zawsze jest pożądane).
Bardziej eleganckim rozwiązaniem byłoby stworzenie własny dodatek (dodatek programu Excel) – osobny plik w specjalnym formacie (xlam) zawierający wszystkie Twoje „ulubione” makra. Zalety tego podejścia:
- To wystarczy podłącz dodatek raz w Excelu – i możesz używać jego procedur i funkcji VBA w dowolnym pliku na tym komputerze. Ponowne zapisywanie plików roboczych w formatach xlsm i xlsb nie jest więc wymagane, ponieważ. kod źródłowy nie będzie w nich przechowywany, ale w pliku dodatku.
- ochrona nie będą ci też przeszkadzać makra. dodatki są z definicji zaufanymi źródłami.
- może zrobić oddzielna zakładka na wstążce Excela z ładnymi przyciskami do uruchamiania makr dodatków.
- Dodatek jest osobnym plikiem. Jego łatwy do przeprowadzenia z komputera na komputer, dzielić się nim z kolegami, a nawet sprzedawać 😉
Prześledźmy krok po kroku cały proces tworzenia własnego dodatku do programu Microsoft Excel.
Krok 1. Utwórz plik dodatku
Otwórz Microsoft Excel z pustym skoroszytem i zapisz go pod dowolną odpowiednią nazwą (na przykład Mój dodatek do programu Excel) w formacie dodatku za pomocą polecenia Plik – Zapisz jako lub klawisze F12, określając typ pliku Dodatek Excela:
Pamiętaj, że domyślnie Excel przechowuje dodatki w folderze C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ale w zasadzie możesz określić dowolny inny folder, który jest dla Ciebie wygodny.
Krok 2. Łączymy utworzony dodatek
Teraz dodatek, który stworzyliśmy w ostatnim kroku Mój dodatek do programu Excel musi być połączony z programem Excel. Aby to zrobić, przejdź do menu Plik – Opcje – Dodatki (Plik — Opcje — Dodatki), kliknij przycisk O nas (Iść) na dole okna. W oknie, które się otworzy, kliknij przycisk Review (Przeglądaj) i określ lokalizację naszego pliku dodatku.
Jeśli zrobiłeś wszystko dobrze, to nasz Mój dodatek do programu Excel powinien pojawić się na liście dostępnych dodatków:
Krok 3. Dodaj makra do dodatku
Nasz dodatek jest połączony z Excelem i działa pomyślnie, ale nie ma w nim jeszcze ani jednego makra. Zapełnijmy to. Aby to zrobić, otwórz edytor Visual Basic za pomocą skrótu klawiaturowego inny+F11 lub przyciskiem Visual Basic Zakładka wywoływacz (Deweloper). Jeśli karty wywoływacz niewidoczny, można go wyświetlić przez Plik – Opcje – Konfiguracja wstążki (Plik — Opcje — Dostosuj wstążkę).
W lewym górnym rogu edytora powinno znajdować się okno Projekty (jeśli nie jest widoczny, włącz go w menu) Widok — Eksplorator projektu):
W tym oknie są wyświetlane wszystkie otwarte skoroszyty i działające dodatki programu Microsoft Excel, w tym nasze. Projekt VBA (MójDodatekExcel.xlam) Wybierz go myszką i dodaj do niego nowy moduł za pomocą menu Wstaw – moduł. W tym module będziemy przechowywać kod VBA naszych makr dodatków.
Możesz albo wpisać kod od zera (jeśli umiesz programować), albo skopiować go z gotowego miejsca (co jest znacznie prostsze). Do testów wpiszmy kod prostego, ale przydatnego makra do dodawanego pustego modułu:
Po wpisaniu kodu nie zapomnij kliknąć przycisku zapisu (dyskietki) w lewym górnym rogu.
Nasze makro FormułyDoWartości, jak łatwo sobie wyobrazić, konwertuje formuły na wartości z wcześniej wybranego zakresu. Czasami te makra są również nazywane procedury. Aby go uruchomić, musisz zaznaczyć komórki z formułami i otworzyć specjalne okno dialogowe Makra z zakładki wywoływacz (Deweloper — Makra) lub skrót klawiaturowy inny+F8. Zwykle to okno pokazuje dostępne makra ze wszystkich otwartych skoroszytów, ale makra dodatków nie są tutaj widoczne. Mimo to w polu możemy wpisać nazwę naszego zabiegu nazwa makra (Nazwa makra)a następnie kliknij przycisk run (biegać) – a nasze makro zadziała:
| |
Tutaj możesz również przypisać skrót klawiaturowy, aby szybko uruchomić makro – odpowiada za to przycisk parametry (Opcje) w poprzednim oknie Macro:
Przypisując klawisze, należy pamiętać, że rozróżniana jest wielkość liter i układ klawiatury. Więc jeśli przypiszesz kombinację, taką jak Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftdostać wielką literę.
Dla wygody możemy również dodać przycisk dla naszego makra do paska szybkiego dostępu w lewym górnym rogu okna. Aby to zrobić, wybierz Plik – Opcje – Pasek narzędzi szybkiego dostępu (Plik — Opcje — Dostosuj pasek narzędzi szybkiego dostępu), a następnie na liście rozwijanej u góry okna opcja Makra. Po tym nasze makro FormułyDoWartości można umieścić na panelu za pomocą przycisku Dodaj (Add) i wybierz dla niego ikonę przyciskiem zmiana (Edytować):
Krok 4. Dodaj funkcje do dodatku
Ale makroprocedury, istnieje również makra funkcji lub jak się je nazywa UDF (Funkcja zdefiniowana przez użytkownika = funkcja zdefiniowana przez użytkownika). Stwórzmy osobny moduł w naszym dodatku (polecenie menu Wstaw – moduł) i wklej tam kod funkcji:
Łatwo zauważyć, że funkcja ta jest potrzebna do odliczenia podatku VAT od kwoty zawierającej podatek VAT. Oczywiście nie jest to dwumian Newtona, ale wystarczy nam jako przykład pokazać podstawowe zasady.
Zauważ, że składnia funkcji różni się od procedury:
- konstrukcja jest używana Funkcja …. Koniec funkcji zamiast Sub … Koniec Sub
- po nazwie funkcji jej argumenty są podane w nawiasach
- w treści funkcji wykonywane są niezbędne obliczenia, a następnie wynik jest przypisywany do zmiennej o nazwie funkcji
Należy również zauważyć, że ta funkcja nie jest potrzebna i nie można jej uruchomić tak, jak poprzednia procedura makr w oknie dialogowym Makra i przycisk run. Taką funkcję makra należy stosować jako standardową funkcję arkusza (SUM, IF, VLOOKUP…), czyli wystarczy wpisać w dowolnej komórce, podając wartość kwoty z VAT jako argumentem:
… lub wejdź przez standardowe okno dialogowe w celu wstawienia funkcji (przycisk fx na pasku formuły), wybierając kategorię Określony przez użytkownika (Określony przez użytkownika):
Jedynym nieprzyjemnym momentem jest tutaj brak zwykłego opisu funkcji na dole okna. Aby go dodać, musisz wykonać następujące czynności:
- Otwórz Edytor Visual Basic za pomocą skrótu klawiaturowego inny+F11
- Wybierz dodatek w panelu Projekt i naciśnij klawisz F2aby otworzyć okno Przeglądarki obiektów
- Wybierz swój projekt dodatku z listy rozwijanej u góry okna
- Kliknij prawym przyciskiem myszy na pojawiającą się funkcję i wybierz polecenie Właściwości.
- Wprowadź opis funkcji w oknie Opis
- Zapisz plik dodatku i uruchom ponownie program Excel.
Po ponownym uruchomieniu funkcja powinna wyświetlić wpisany przez nas opis:
Krok 5. Utwórz zakładkę dodatków w interfejsie
Ostatnim, choć nie obowiązkowym, ale przyjemnym akcentem będzie stworzenie osobnej zakładki z przyciskiem do uruchamiania naszego makra, która pojawi się w interfejsie Excela po podłączeniu naszego dodatku.
Informacje o domyślnie wyświetlanych kartach są zawarte w książce i muszą być sformatowane w specjalnym kodzie XML. Najłatwiej napisać i edytować taki kod przy pomocy specjalnych programów – edytorów XML. Jednym z najwygodniejszych (i darmowych) jest program Maxima Novikova Edytor XML wstążki.
Algorytm pracy z nim wygląda następująco:
- Zamknij wszystkie okna programu Excel, aby nie było konfliktu plików podczas edycji kodu XML dodatku.
- Uruchom program Ribbon XML Editor i otwórz w nim nasz plik MyExcelAddin.xlam
- Z przyciskiem tabs w lewym górnym rogu dodaj fragment kodu dla nowej karty:
- Musisz wstawić puste cytaty id naszą zakładkę i grupę (dowolne unikalne identyfikatory) oraz w etykieta – nazwy naszej zakładki i grupy przycisków na niej:
- Z przyciskiem przycisk w lewym panelu dodaj pusty kod przycisku i dodaj do niego tagi:
- etykieta czy tekst na przycisku?
— obrazMso — jest to warunkowa nazwa obrazu na przycisku. Użyłem czerwonej ikony przycisku o nazwie AnimationCustomAddExitDialog. Nazwy wszystkich dostępnych przycisków (a jest ich kilkaset!) można znaleźć na wielu stronach w Internecie, jeśli wyszukujesz słowa kluczowe „imageMso”. Na początek możesz udać się tutaj.
- w akcji – tak nazywa się procedura callback – specjalne krótkie makro, które uruchomi nasze główne makro FormułyDoWartości. Możesz nazwać tę procedurę, jak chcesz. Dodamy to trochę później.
- Możesz sprawdzić poprawność wszystkiego, co zrobiłeś, używając przycisku z zielonym haczykiem na górze paska narzędzi. W tym samym miejscu kliknij przycisk z dyskietką, aby zapisać wszystkie zmiany.
- Zamknij edytor XML wstążki
- Otwórz Excela, przejdź do edytora Visual Basic i dodaj procedurę wywołania zwrotnego do naszego makra ZabijFormułytak, aby uruchamiał nasze główne makro do zastępowania formuł wartościami.
- Zapisujemy zmiany i wracając do Excela sprawdzamy wynik:
To wszystko – dodatek jest gotowy do użycia. Wypełnij go własnymi procedurami i funkcjami, dodaj piękne przyciski – a korzystanie z makr w Twojej pracy stanie się znacznie prostsze.
- Czym są makra, jak ich używać w pracy, skąd wziąć kod makr w Visual Basic.
- Jak zrobić ekran powitalny podczas otwierania skoroszytu w programie Excel
- Co to jest osobista książka makr i jak z niej korzystać