W tym samouczku poznamy funkcję Excel VBA
1) Co to jest Visual Basic w programie Excel?
2) Jak korzystać z VBA w programie Excel?
3) Jak stworzyć funkcję zdefiniowaną przez użytkownika?
4) Jak napisać makro?
Jak napisać kod VBA
Excel zapewnia użytkownikowi duży zbiór gotowych funkcji, więcej niż wystarczająco, aby zadowolić przeciętnego użytkownika. O wiele więcej można dodać, instalując różne dostępne dodatki. Większość obliczeń można wykonać za pomocą tego, co jest dostarczone, ale nie minęło dużo czasu, zanim zaczniesz chcieć, aby istniała funkcja, która wykonała konkretną pracę, a na liście nie możesz znaleźć niczego odpowiedniego. Potrzebujesz UDF. UDF (funkcja zdefiniowana przez użytkownika) to po prostu funkcja, którą tworzysz samodzielnie za pomocą VBA. UDF są często nazywane „funkcjami niestandardowymi”. UDF może pozostać w module kodu dołączonym do skoroszytu, w którym to przypadku będzie zawsze dostępny, gdy skoroszyt jest otwarty. Alternatywnie możesz utworzyć własny dodatek zawierający jedną lub więcej funkcji, które można zainstalować w programie Excel, tak jak dodatek komercyjny. Dostęp do funkcji UDF można uzyskać również za pomocą modułów kodu. Często UDF są tworzone przez programistów do pracy wyłącznie w kodzie procedury VBA, a użytkownik nigdy nie jest świadomy ich istnienia. Jak każda funkcja, funkcja UDF może być tak prosta lub tak złożona, jak tylko chcesz. Zacznijmy od łatwego…
Funkcja do obliczania pola prostokąta
Tak, wiem, że możesz to zrobić w swojej głowie! Koncepcja jest bardzo prosta, więc możesz skoncentrować się na technice. Załóżmy, że potrzebujesz funkcji do obliczenia pola prostokąta. Przeglądasz kolekcję funkcji programu Excel, ale nie ma odpowiedniej. Oto obliczenia, które należy wykonać:
OBSZAR = DŁUGOŚĆ x SZEROKOŚĆ
Otwórz nowy skoroszyt, a następnie otwórz Edytor Visual Basic (Narzędzia > Makro > Edytor Visual Basic lub ALT+F11).
Będziesz potrzebował modułu, w którym możesz napisać swoją funkcję, więc wybierz Wstaw > Moduł. W pustym module wpisz: Obszar funkcji i naciśnij WEJŚĆ.Edytor Visual Basic uzupełnia linię za Ciebie i dodaje linię End Function, tak jakbyś tworzył podprogram. Jak dotąd wygląda to tak…
Obszar funkcji () Koniec funkcji
Umieść kursor w nawiasach po „Obszar”. Jeśli kiedykolwiek zastanawiałeś się, do czego służą wsporniki, wkrótce się dowiesz! Zamierzamy określić "argumenty", które przyjmie nasza funkcja (an argument to informacja potrzebna do wykonania obliczeń). Rodzaj Długość jako podwójna, Szerokość jako podwójna i kliknij pustą linię pod spodem. Zauważ, że podczas pisania wyskakuje okienko przewijania zawierające listę wszystkich rzeczy odpowiednich do tego, co piszesz.
Ta funkcja nazywa się Autolista członków. Jeśli nie pojawia się, jest wyłączony (włącz go o Narzędzia > Opcje > Edytor) lub mogłeś wcześniej popełnić błąd podczas pisania. Jest to bardzo przydatne sprawdzenie Twojej składni. Znajdź potrzebny element i kliknij go dwukrotnie, aby wstawić go do kodu. Możesz to zignorować i po prostu pisać, jeśli chcesz. Twój kod wygląda teraz tak…
Obszar funkcji (długość jako podwójna, szerokość jako podwójna) Funkcja końcowa
Deklarowanie typu danych argumentów nie jest obowiązkowe, ale ma sens. Mogłeś wpisać Długość, szerokość i zostawiłem to w ten sposób, ale ostrzeżenie programu Excel, jakiego typu danych może oczekiwać, pomaga przyspieszyć działanie kodu i wykrywa błędy w danych wejściowych. ten podwójnie typ danych odnosi się do liczby (która może być bardzo duża) i dopuszcza ułamki. Teraz sama kalkulacja. W pustym wierszu najpierw naciśnij PATKA klucz do wcięcia kodu (ułatwiający czytanie) i pisania Powierzchnia = długość * szerokość. Oto gotowy kod…
Obszar funkcji (długość jako podwójna, szerokość jako podwójna) Obszar = długość * szerokość Funkcja końcowa
Zauważysz, że podczas pisania pojawia się inna funkcja pomocy Edytora Visual Basic, Automatyczne szybkie informacje…
Tutaj nie ma to znaczenia. Jego celem jest pomoc w pisaniu funkcji w VBA, informując, jakie argumenty są wymagane. Możesz od razu przetestować swoją funkcję. Przejdź do okna Excela i wprowadź liczby dla długości i szerokości w osobnych komórkach. W trzeciej komórce wpisz swoją funkcję tak, jakby była jedną z wbudowanych. W tym przykładzie komórka A1 zawiera długość (17) a komórka B1 szerokość (6,5). W C1 wpisałem =obszar(A1,B1) a nowa funkcja obliczyła powierzchnię (110,5)…
Czasami argumenty funkcji mogą być opcjonalne. W tym przykładzie możemy zrobić Szerokość argument opcjonalny. Przypuśćmy, że prostokąt jest kwadratem, którego długość i szerokość są równe. Aby zaoszczędzić użytkownikowi konieczności wprowadzania dwóch argumentów, możemy pozwolić mu wprowadzić tylko długość i sprawić, by funkcja użyła tej wartości dwukrotnie (tj. Pomnóż długość x długość). Aby funkcja wiedziała, kiedy może to zrobić, musimy uwzględnić Oświadczenie IF by pomóc w podjęciu decyzji. Zmień kod tak, aby wyglądał tak…
Obszar funkcji (Długość jako podwójna, opcjonalna szerokość jako wariant) Jeśli brak (szerokość) Wtedy Obszar = Długość * Długość W przeciwnym razie Obszar = Długość * Szerokość Koniec Jeśli Zakończ funkcję
Zwróć uwagę, że typ danych dla Szerokość został zmieniony na Wariant aby zezwolić na wartości null. Funkcja pozwala teraz użytkownikowi wprowadzić tylko jeden argument, np. =obszar(A1).Instrukcja IF w funkcji sprawdza, czy podano argument Szerokość i odpowiednio oblicza…
Funkcja do obliczania zużycia paliwa
Lubię sprawdzać zużycie paliwa w moim samochodzie, więc kupując paliwo, notuję przebieg i ilość paliwa potrzebną do zatankowania. Tutaj w Wielkiej Brytanii paliwo sprzedawane jest w litrach. Milometr samochodu (OK, więc to licznik kilometrów) rejestruje odległość w milach. A ponieważ jestem za stary i głupi, żeby się zmienić, rozumiem tylko MPG (mile na galon). Teraz, jeśli myślisz, że to trochę smutne, co powiesz na to. Kiedy wracam do domu, otwieram Excela i wprowadzam dane do arkusza kalkulacyjnego, który oblicza dla mnie MPG i wykresy osiągów samochodu. Obliczenie to liczba mil, które samochód przejechał od ostatniego tankowania, podzielona przez liczbę litrów zużytego paliwa…
MPG = (MILE TEGO WYPEŁNIENIA - OSTATNIE MILE NAPEŁNIANIA) / GALONY PALIWA
ale ponieważ paliwo jest w litrach, a w galonie jest 4.546 litrów…
MPG = (MILE TEGO WYPEŁNIENIA - OSTATNIE MILE NAPEŁNIANIA) / LITRY PALIWA x 4,546
Oto jak napisałem funkcję…
Funkcja MPG(StartMiles jako liczba całkowita, FinishMiles jako liczba całkowita, litry jako pojedyncze) MPG = (FinishMiles - StartMiles) / litry * 4.546 Koniec funkcji
a oto jak to wygląda na arkuszu…
Nie wszystkie funkcje wykonują obliczenia matematyczne. Oto jeden, który dostarcza informacji…
Funkcja, która nadaje nazwę dnia
Często jestem pytany, czy istnieje funkcja daty, która podaje dzień tygodnia jako tekst (np. poniedziałek). Odpowiedź brzmi nie*, ale dość łatwo ją stworzyć. (*Uzupełnienie: Czy powiedziałem nie? Sprawdź poniższą notatkę, aby zobaczyć funkcję, o której zapomniałem!). Excel posiada funkcję WEEKDAY, która zwraca dzień tygodnia jako liczbę od 1 do 7. Możesz wybrać, który dzień to 1, jeśli nie lubisz domyślnego (niedziela). W poniższym przykładzie funkcja zwraca „5”, co, jak wiem, oznacza „czwartek”.
Ale nie chcę widzieć liczby, chcę zobaczyć „czwartek”. Mogłem zmodyfikować obliczenia, dodając funkcję WYSZUKAJ.PIONOWO, która odwołuje się do tabeli zawierającej listę liczb i odpowiednią listę nazw dni. Lub mógłbym mieć całość samowystarczalną z wieloma zagnieżdżonymi instrukcjami IF. Zbyt skomplikowane! Odpowiedzią jest funkcja niestandardowa…
Funkcja DayName(InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday(InputDate, vbSunday) Wybierz przypadek DayNumber Przypadek 1 DayName = "Niedziela" Przypadek 2 DayName = "Poniedziałek" Przypadek 3 DayName = "Wtorek" Przypadek 4 DayName = "Środa" Przypadek 5 DayName = „Czwartek” Przypadek 6 DayName = „Piątek” Przypadek 7 DayName = „Saturday” End Wybierz funkcję zakończenia
Nazwałem moją funkcję „DayName” i pobiera ona pojedynczy argument, który nazywam „InputDate”, który (oczywiście) musi być datą. Oto jak to działa…
- W pierwszym wierszu funkcji deklaruje się zmienną, którą nazwałem „Numer Dnia”, która będzie liczbą całkowitą (czyli liczbą całkowitą).
- Następny wiersz funkcji przypisuje wartość do tej zmiennej za pomocą funkcji DZIEŃ.TYG programu Excel. Wartość będzie liczbą z przedziału od 1 do 7. Chociaż domyślna wartość to 1=Niedziela, i tak dodałem ją dla jasności.
- Wreszcie Oświadczenie o sprawie sprawdza wartość zmiennej i zwraca odpowiedni fragment tekstu.
Oto jak to wygląda w arkuszu…
Dostęp do niestandardowych funkcji
Jeśli skoroszyt ma dołączony moduł kodu VBA, który zawiera funkcje niestandardowe, funkcje te można łatwo rozwiązać w tym samym skoroszycie, jak pokazano w powyższych przykładach. Używasz nazwy funkcji tak, jakby była jedną z wbudowanych funkcji programu Excel.
Możesz również znaleźć funkcje wymienione w Kreatorze funkcji (czasami nazywanym narzędziem Wklej funkcję). Użyj kreatora, aby wstawić funkcję w normalny sposób (Wstaw > Funkcja).
Przewiń listę kategorii funkcji, aby znaleźć Określony przez użytkownika i wybierz go, aby zobaczyć listę dostępnych UDF…
Widać, że funkcje zdefiniowane przez użytkownika nie mają żadnego opisu poza nieprzydatnym komunikatem „Brak dostępnej pomocy”, ale możesz dodać krótki opis…
Upewnij się, że jesteś w skoroszycie zawierającym funkcje. Iść do Narzędzia > Makro > Makra. Nie zobaczysz tutaj swoich funkcji, ale program Excel o nich wie! w Nazwa makra w górnej części okna dialogowego, wpisz nazwę funkcji, a następnie kliknij w oknie dialogowym Opcje przycisk. Jeśli przycisk jest wyszarzony, oznacza to, że nazwa funkcji została błędnie przeliterowana, znajduje się w niewłaściwym skoroszycie lub funkcja nie istnieje! Spowoduje to otwarcie kolejnego okna dialogowego, w którym można wprowadzić krótki opis funkcji. Kliknij ok aby zapisać opis i (to mylący bit) kliknij Anulować aby zamknąć okno dialogowe Makro. Pamiętaj, aby zapisać skoroszyt zawierający funkcję. Następnym razem, gdy przejdziesz do Kreatora funkcji, Twój UDF będzie miał opis…
Podobnie jak makra, funkcje zdefiniowane przez użytkownika mogą być używane w dowolnym innym skoroszycie, o ile skoroszyt je zawierający jest otwarty. Nie jest to jednak dobra praktyka. Wprowadzanie funkcji w innym skoroszycie nie jest proste. Musisz dodać nazwę skoroszytu hosta do nazwy funkcji. Nie jest to trudne, jeśli polegasz na Kreatorze funkcji, ale niezręczne jest ręczne pisanie. Kreator funkcji wyświetla pełne nazwy dowolnych funkcji UDF w innych skoroszytach…
Jeśli otworzysz skoroszyt, w którym użyto funkcji, w czasie, gdy skoroszyt zawierający funkcję jest zamknięty, w komórce, w której użyto funkcji, zostanie wyświetlony komunikat o błędzie. Excel o tym zapomniał! Otwórz skoroszyt hosta funkcji, przelicz i wszystko jest w porządku. Na szczęście jest lepszy sposób.
Jeśli chcesz napisać funkcje zdefiniowane przez użytkownika do użycia w więcej niż jednym skoroszycie, najlepszą metodą jest utworzenie programu Excel Dodać. Dowiedz się, jak to zrobić w samouczku Tworzenie dodatku programu Excel.
Uzupełnienie
Naprawdę powinienem wiedzieć lepiej! Nigdy, przenigdy nie mów nigdy! Powiedziawszy ci, że nie ma funkcji, która podaje nazwę dnia, przypomniałem sobie tę, która może. Spójrz na ten przykład…
Funkcja TEKST zwraca wartość komórki jako tekst w określonym formacie liczbowym. Więc w przykładzie, który mogłem wybrać =TEKST(A1;"ddd") zwrócić "Cz", =TEKST(A1;"mmmm") aby zwrócić "wrzesień" itp. Pomoc programu Excel zawiera więcej przykładów wykorzystania tej funkcji.
Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na witryna e-mail