To są moje notatki z mojego występu w 33 odcinku Call for Help w TechTV Canada.
Rejestrator makr VBA
Każda kopia programu Excel sprzedawana od 1995 roku zawiera niesamowicie potężny Visual Basic for Applications (VBA) ukryty za komórkami. Jeśli masz Excel, masz VBA.
VBA pozwala zautomatyzować wszystko, co możesz zrobić w programie Excel, a także zrobić więcej rzeczy, które zwykle nie są możliwe w programie Excel.
Na szczęście Microsoft dołączył do programu Excel rejestrator makr. Pozwala to pisać kod VBA bez bycia programistą. Niestety rejestrator makr jest wadliwy. Nie wytworzy kodu, który będzie działał za każdym razem. Rejestrator makr zbliży Cię do siebie, ale w wielu przypadkach musisz nieco poprawić kod, aby działał niezawodnie. W innych przypadkach musisz wiedzieć, kiedy użyć nagrywania względnego, aby makro działało.
Tracy Syrstad i ja napisaliśmy VBA i makra Microsoft Excel 2016, aby pomóc Ci zrozumieć ograniczenia zarejestrowanego kodu i nauczyć Cię, jak naprawić zarejestrowany kod w coś, co będzie działało za każdym razem.
W programie pokazałem proces nagrywania makra, które będzie działać idealnie, jeśli wiesz, jak używać przycisku Relative Reference. Ktoś dał mi arkusz programu Excel z setkami nazwisk i adresów. Chcieli wysłać etykiety pocztowe. Jeśli kiedykolwiek korzystałeś z funkcji korespondencji seryjnej Microsoft Word, wiesz, że potrzebujesz każdego pola w nowej kolumnie arkusza. Zamiast tego ten konkretny arkusz roboczy zawierał dane z kolumny A.
Proces naprawy pojedynczej etykiety jest dość żmudny.
- Zacznij od wskaźnika komórki na nazwie w kolumnie A.
- Naciśnij strzałkę w dół, aby przejść do adresu
- Ctrl + X, aby wyciąć
- Strzałka w górę, strzałka w prawo, aby przejść do kolumny B obok nazwy
- Ctrl + V, aby wkleić
- Strzałka w dół dwukrotnie, strzałka w lewo raz, aby przejść do miasta
- Ctrl + X, aby wyciąć
- Strzałka w górę dwukrotnie, strzałka w prawo trzykrotnie
- Ctrl + V, aby wkleić
- Strzałka w lewo dwukrotnie, strzałka w dół raz, aby przejść do pustego wiersza.
- Przytrzymaj klawisz Shift, dwukrotnie naciskając strzałkę w dół
- Alt + edr, aby usunąć puste wiersze
- Strzałka w górę i strzałka w dół, aby ponownie wybrać samą nazwę.
Oto animacja przedstawiająca te kroki:
Konfigurowanie programu Excel, aby zezwolić na makra
Chociaż każda kopia programu Excel zawiera VBA, firma Microsoft domyślnie wyłącza możliwość uruchamiania makr. Aby umożliwić uruchamianie makr, musisz dokonać jednorazowej korekty. Otwórz Excel. Z menu wybierz Narzędzia> Makro> Bezpieczeństwo. Jeśli poziom bezpieczeństwa makr jest obecnie ustawiony na wysoki, zmień go na Średni.
Przygotowanie do nagrania makra
Pomyśl o krokach niezbędnych do wykonania zadania. Chcesz się upewnić, że zaczynasz od wskaźnika komórki na nazwie i kończysz nim na następnej nazwie. Umożliwi to wielokrotne uruchamianie makra. Kiedy będziesz gotowy do wykonania kroków, włącz rejestrator makr. Z menu wybierz Narzędzia> Makro> Zarejestruj nowe makro.
Jeśli będziesz uruchamiać to makro przez wiele dni, powinieneś nadać mu przydatną nazwę. Jeśli jest to jednorazowe makro do zautomatyzowania jednorazowego zadania, pozostawienie nazwy makra jako Makro1 prawdopodobnie jest w porządku. Ważnym polem jest tutaj pole klawisza skrótu. Jeśli tworzysz makro do jednorazowego użytku, przypisz makro do klawisza skrótu, takiego jak Ctrl + a - naciśnięcie Ctrl + a jest dość łatwe, ponieważ klawisze są blisko siebie. Jeśli zamierzasz utworzyć makro, którego będziesz używać codziennie, to chcesz przypisać makro do klawisza, który nie jest już ważną kombinacją klawiszy skrótu. Ctrl + j lub Ctrl + k to dobry wybór.
Makra jednorazowego użytku powinny być przechowywane w ThisWorkbook. Jeśli chcesz używać makra wielokrotnie w wielu różnych skoroszytach, możesz je przechowywać w Osobistym skoroszycie makr.
Zostanie wyświetlony najmniejszy pasek narzędzi w całym programie Excel; pasek narzędzi Zatrzymaj nagrywanie. Ma tylko dwie ikony i wygląda tak:
Jeśli ten pasek narzędzi stanie Ci na drodze, nie naciskaj X, aby go zamknąć. Zamiast tego chwyć niebieski pasek i przeciągnij pasek narzędzi w nowe miejsce. Czynność przesuwania paska narzędzi nie jest rejestrowana w makrze. Jeśli przypadkowo zamkniesz pasek narzędzi, przywróć go za pomocą opcji Widok> Paski narzędzi> Zatrzymaj nagrywanie. Ta akcja zostanie jednak zarejestrowana.
Pierwszy przycisk na pasku narzędzi to przycisk „Zatrzymaj nagrywanie”. To jest oczywiste. Po zakończeniu nagrywania makra naciśnij pasek narzędzi zatrzymywania nagrywania.
Ważniejszym (i rzadziej rozumianym) przyciskiem jest przycisk „Względne odniesienie”.
W naszym obecnym przykładzie przed rozpoczęciem należy nacisnąć przycisk Odniesienie względne. Jeśli zarejestrujesz makro z włączonymi odwołaniami względnymi, program Excel zarejestruje następujące kroki:
- Przenieś jedną komórkę w dół
- Wytnij bieżącą komórkę
- Przenieś jedną komórkę w górę
- Przejdź o jedną komórkę w prawo
- Pasta
- itp.
Jeśli zamiast tego chcesz zarejestrować makro bez odniesień względnych, makro zarejestruje następujące kroki:
- Przejdź do komórki A4
- Wytnij komórkę A4
- Przejdź do komórki A3
- Przejdź do komórki B3
- Wklej do komórki B3
- itp.
Byłoby to strasznie źle - potrzebujemy makra do działania na komórkach, które są 1 i 2 komórki od punktu początkowego makra. Podczas wielokrotnego uruchamiania makra punktem początkowym będzie wiersz 4, wiersz 5, wiersz 6 itd. Nagranie makra bez włączonych odniesień względnych spowoduje, że będzie ono zawsze uruchamiane w wierszu 3 jako punkt początkowy.
Wykonaj następujące kroki:
- Wybierz przycisk odniesienia względnego na pasku narzędzi zatrzymania nagrywania
- Wskaźnik komórki powinien już znajdować się na nazwie w kolumnie A.
- Naciśnij strzałkę w dół, aby przejść do adresu
- Ctrl + X, aby wyciąć
- Strzałka w górę, strzałka w prawo, aby przejść do kolumny B obok nazwy
- Ctrl + V, aby wkleić
- Strzałka w dół dwukrotnie, strzałka w lewo raz, aby przejść do miasta
- Ctrl + X, aby wyciąć
- Strzałka w górę dwukrotnie, strzałka w prawo trzykrotnie
- Ctrl + V, aby wkleić
- Strzałka w lewo dwukrotnie, strzałka w dół raz, aby przejść do pustego wiersza.
- Przytrzymaj klawisz Shift, dwukrotnie naciskając strzałkę w dół
- Alt + edr, aby usunąć puste wiersze
- Strzałka w górę i strzałka w dół, aby wybrać następną nazwę na liście.
- Naciśnij pasek narzędzi Zatrzymaj nagrywanie
- Zapisz skoroszyt
- Przetestuj makro, naciskając klawisz skrótu przypisany powyżej. Powinien idealnie naprawić następną nazwę na liście
Gdy zobaczysz, że makro działa zgodnie z oczekiwaniami, możesz przytrzymać Ctrl + a, aby szybko naprawić kilka nazw na sekundę. Całą listę 500 nazwisk można naprawić w ciągu minuty lub dwóch.
Wskazówka bonusowa - nie w programie
Możesz łatwo zawinąć makro w prostą pętlę, aby naprawić wszystkie 500 nazw bez konieczności naciskania Ctrl + a kilkaset razy.
Naciśnij klawisz Alt + F11, aby otworzyć edytor makr.
Jeśli nie widzisz okienka Projekt - VBAProject, naciśnij Ctrl + r.
Kliknij prawym przyciskiem myszy Module1 i wybierz opcję Wyświetl kod. Zobaczysz kod, który wygląda następująco:
Teraz nie musisz rozumieć, co robi ten kod, ale wiesz, że chcemy uruchomić wszystko w tym makrze raz dla każdej nazwy, którą mamy. Jeśli wiesz, że są 462 nazwy, możesz dodać 2 wiersze, aby uruchomić kod 462 razy. W górnej części makra wstaw nowy wiersz ze słowami „ For i = 1 to 462”. U dołu makra wstaw wiersz z napisem „ Next i”. To mówi VBA, aby uruchomił kod wewnątrz 462 razy.
Wniosek
Po tym prostym makrze pokazałem przykład na pokazie bardzo złożonego makra. To makro utworzyło tabele przestawne i wykresy dla 46 działów firmy. Ten raport zajmował 40 godzin każdego miesiąca. Makro VBA działa teraz i tworzy wszystkie 46 raportów w mniej niż 2 minuty.
Książka VBA and Macros Microsoft Excel 2016 poprowadzi Cię przez krzywą uczenia się, dzięki czemu możesz przejść od nagrywania prostych makr, takich jak ten, do tworzenia bardzo złożonych raportów, które mogą zaoszczędzić setki godzin rocznie. Oczywiście, jeśli nie chcesz uczyć się VBA, zatrudnij konsultanta Excela, aby zaprojektował dla Ciebie raport.
