Jeśli dopiero zaczynasz korzystać z języka VBA, musisz zacząć od nauki Podręcznik dla początkujących dotyczący języka VBAJeśli jednak jesteś doświadczonym ekspertem VBA i szukasz bardziej zaawansowanych funkcji VBA w programie Excel, czytaj dalej.
Możliwość korzystania z znaczników VBA w Excelu otwiera przed Tobą cały świat automatyzacji. Możesz zautomatyzować obliczenia, przyciski, a nawet wysyłanie wiadomości e-mail w Excelu. Możliwości automatyzacji codziennej pracy z VBA są o wiele większe, niż mogłoby się wydawać.
Zaawansowany przewodnik po języku VBA dla programu Microsoft Excel
Głównym celem pisania kodu VBA w programie Excel jest umożliwienie wyodrębniania informacji z arkusza kalkulacyjnego, wykonywania na nim różnych obliczeń, a następnie zapisywania wyników z powrotem do arkusza kalkulacyjnego.
Poniżej przedstawiono najczęstsze zastosowania języka VBA w programie Excel.
- Importowanie danych i wykonywanie obliczeń
- Obliczanie wyników na podstawie kliknięć przycisków użytkownika
- Wysyłanie wyników obliczeń e-mailem do kogoś
Korzystając z tych trzech przykładów, powinieneś być w stanie napisać wiele różnych zaawansowanych kodów VBA w programie Excel.
Importowanie danych i wykonywanie obliczeń
Jednym z najczęstszych zastosowań Excela jest wykonywanie obliczeń na danych poza nim. Jeśli nie używasz VBA, oznacza to konieczność ręcznego zaimportowania danych, uruchomienia obliczeń i wyprowadzenia tych wartości do innego arkusza lub raportu.
Korzystając z VBA, możesz zautomatyzować cały proces. Na przykład, jeśli w każdy poniedziałek pobierasz nowy plik CSV do katalogu na swoim komputerze, możesz skonfigurować kod VBA tak, aby uruchamiał się przy pierwszym otwarciu arkusza kalkulacyjnego we wtorek rano.
Poniższy kod importu zostanie wykonany, a plik CSV zostanie zaimportowany do arkusza kalkulacyjnego Excel.
Przyciemnij ws jako arkusz roboczy, strFile jako ciąg Ustaw ws = ActiveWorkbook.Sheets("Arkusz1") Cells.ClearContents strFile = "c:temppurchases.csv" z ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh Koniec z
Otwórz edytor VBA w programie Excel i wybierz obiekt Arkusz1. Z listy rozwijanej Obiekt i Metoda wybierz Ćwiczenie و AktywujSpowoduje to, że kod będzie uruchamiany za każdym razem, gdy otworzysz arkusz kalkulacyjny.
To utworzy funkcję Podarkusz_Aktywuj()Wklej powyższy kod do tej funkcji.
Ustawia aktywny arkusz roboczy na Sheet1Zeskanuj arkusz i połącz się z plikiem, korzystając ze ścieżki dostępu określonej w zmiennej. strFileNastępnie pętla Niezależnie od tego, czy potrzebujesz kompletnej linii, czy pojedynczego urządzenia, Przechodząc przez każdy wiersz w pliku i umieszczając dane na arkuszu, zaczynając od komórki A1.
Jeśli uruchomisz ten kod, zobaczysz, że dane z pliku CSV zostaną zaimportowane do pustego arkusza kalkulacyjnego. Sheet1.
Importowanie to dopiero pierwszy krok. Następnie utwórz nowy nagłówek kolumny, który będzie zawierał wyniki obliczeń. W tym przykładzie załóżmy, że chcesz obliczyć 5% podatek od sprzedaży każdego produktu.
Kolejność działań, jakie powinien wykonać Twój kod, jest następująca:
- Utwórz nową kolumnę wyników o nazwie podatki.
- Powtórz w całej kolumnie sprzedanych sztuk I oblicz podatek od sprzedaży.
- Zapisz wyniki obliczeń w odpowiednim wierszu na papierze.
Poniższy kod zrealizuje wszystkie te kroki.
Przyciemnij LastRow As Long Przyciemnij StartCell As Range Przyciemnij rowCounter As Integer Przyciemnij rng As Range, cell As Range Przyciemnij fltTax As Double Ustaw StartCell = Range("A1") 'Znajdź ostatni wiersz i kolumnę LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Ustaw rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "taxes" Dla każdej komórki w rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Następna komórka
Ten kod wyszukuje ostatni wiersz arkusza danych, a następnie ustawia zakres komórek (kolumnę zawierającą ceny sprzedaży) zgodnie z pierwszym i ostatnim wierszem danych. Następnie kod iteruje po każdej z tych komórek, oblicza podatek i zapisuje wyniki w nowej kolumnie (kolumna 5).
Wklej powyższy kod VBA pod poprzednim kodem i uruchom skrypt. Wyniki pojawią się w kolumnie E.
Teraz za każdym razem, gdy otworzysz arkusz kalkulacyjny Excel, program automatycznie pobierze najnowszą wersję danych z pliku CSV. Następnie wykona obliczenia i zapisze wyniki w arkuszu. Nie musisz już nic robić ręcznie!
Oblicz wyniki naciskając przycisk
Jeśli wolisz mieć bezpośrednią kontrolę nad momentem wykonywania obliczeń, zamiast uruchamiać je automatycznie po otwarciu arkusza kalkulacyjnego, możesz skorzystać z przycisku sterowania.
Przyciski sterujące są przydatne, jeśli chcesz kontrolować stosowane obliczenia. Na przykład, w tym samym scenariuszu, o którym mowa powyżej, co by było, gdybyś chciał zastosować stawkę podatku 5% dla jednego regionu i 7% dla innego?
Możesz zezwolić na automatyczne uruchomienie tego samego kodu importu CSV, ale pozostawić uruchomienie kodu obliczania podatku po naciśnięciu odpowiedniego przycisku.
Używając tej samej tabeli danych, o której mowa powyżej, wybierz kartę Deweloperi wybierz wstawka Z grupy Sterowniki Na wstążce wybierz kontrolkę. naciśnij przycisk Wybierz ActiveX z menu rozwijanego.
Narysuj przycisk w dowolnym miejscu na papierze, z dala od miejsca, w którym powinny znaleźć się dane.
Kliknij prawym przyciskiem myszy przycisk i wybierz WłaściwościW oknie Właściwości zmień Podpis na taki, który chcesz wyświetlić użytkownikowi. W tym przypadku może to być Oblicz 5% podatku.
Zobaczysz ten tekst odbity na samym przycisku. Zamknij okno NieruchomościKliknij dwukrotnie ten sam przycisk. Spowoduje to otwarcie okna edytora kodu, a kursor znajdzie się wewnątrz funkcji, która zostanie wykonana po kliknięciu przycisku przez użytkownika.
Wklej kod obliczania podatku z powyższej sekcji do tej funkcji, utrzymując mnożnik stawki podatku na poziomie 0.05. Pamiętaj o dodaniu dwóch kolejnych wierszy w celu identyfikacji aktywnego arkusza.
Przyciemnij ws jako arkusz roboczy, strFile jako ciąg Ustaw ws = ActiveWorkbook.Sheets("Arkusz1")
Teraz powtórz proces jeszcze raz i stwórz drugi przycisk. Nazwij go Obliczanie podatku 7%.
Kliknij dwukrotnie ten przycisk i wklej ten sam kod, ale ustaw mnożnik podatku na 0.07.
Teraz, zależnie od tego, który przycisk naciśniesz, kolumna podatku zostanie odpowiednio obliczona.
Po zakończeniu na arkuszu pojawią się oba przyciski. Każdy z nich rozpocznie inne obliczenie podatku i wyświetli inne wyniki w kolumnie wyników.
Aby to przetestować, wybierz listę Deweloperi wybierz i pielęgniarka Z grupy elementów sterujących na wstążce, aby wyłączyć i pielęgniarkaSpowoduje to aktywację przycisków.
Spróbuj kliknąć każdy przycisk, aby zobaczyć, jak zmieni się kolumna wyników „Podatki”.
Wysyłanie wyników konta e-mailem do kogoś
A co, jeśli chciałbyś wysłać wyniki z arkusza kalkulacyjnego komuś e-mailem?
Możesz utworzyć kolejny przycisk o nazwie Arkusz e-mailowy do szefa Korzystając z tej samej procedury, którą opisano powyżej, kod tego przycisku będzie wymagał użycia obiektu CDO programu Excel do skonfigurowania ustawień poczty e-mail SMTP i wysłania wyników w przyjaznym dla użytkownika formacie.
Aby włączyć tę funkcję, należy wybrać Narzędzia i odniesieniaPrzewiń w dół do . Biblioteka Microsoft CDO dla systemu Windows 2000Włącz i wybierz OK.
Aby wysłać wiadomość e-mail i uwzględnić wyniki z arkusza kalkulacyjnego, należy utworzyć trzy główne sekcje kodu.
Pierwszym krokiem jest skonfigurowanie zmiennych, które będą przechowywać temat, adresy „do” i „od” oraz treść wiadomości e-mail.
Przyciemnij CDO_Mail jako obiekt Przyciemnij CDO_Config jako obiekt Przyciemnij SMTP_Config jako wariant Przyciemnij strSubject jako ciąg Przyciemnij strFrom jako ciąg Przyciemnij strTo jako ciąg Przyciemnij strCc jako ciąg Przyciemnij strBcc jako ciąg Przyciemnij strBody jako ciąg Przyciemnij LastRow jako długi Przyciemnij StartCell jako zakres Przyciemnij rowCounter jako liczba całkowita Przyciemnij rng jako zakres, komórka jako zakres Przyciemnij fltTax jako podwójna wartość
Ustaw ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "Podatki zapłacone w tym kwartale" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Poniżej przedstawiono zestawienie podatków zapłaconych od sprzedaży w tym kwartale."
Oczywiście tekst musi być dynamiczny w zależności od wyników podanych w artykule, dlatego tutaj trzeba dodać pętlę, która przechodzi przez zakres, wyodrębnia dane i za każdym razem zapisuje wiersz w tekście.
Ustaw StartCell = Range("A1"). vbCrLf Dla każdej komórki w rng strBody = strBody & vbCrLf strBody = strBody & "Sprzedaliśmy " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _ & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Następna komórka
Następna sekcja dotyczy konfiguracji protokołu SMTP, aby móc wysyłać wiadomości e-mail przez serwer SMTP. Jeśli korzystasz z Gmaila, zazwyczaj jest to Twój adres e-mail, hasło do Gmaila oraz serwer SMTP Gmaila (smtp.gmail.com).
Ustaw CDO_Mail = CreateObject("CDO.Message") W przypadku błędu Przejdź do Error_Handling Ustaw CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Ustaw SMTP_Config = CDO_Config.Fields z SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "hasło" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Ustaw .Configuration = CDO_Config Koniec z
wymienić e-mail@strona internetowa.com Hasło zawiera dane Twojego konta.
Na koniec, aby rozpocząć wysyłanie wiadomości e-mail, wprowadź poniższy kod.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Obsługa błędów: Jeśli Err.Description <> "" Then MsgBox Err.Description
OgłoszenieJeśli podczas próby uruchomienia tego kodu pojawi się błąd transferu, Twoje konto Google prawdopodobnie blokuje uruchamianie „mniej bezpiecznych aplikacji”. Musisz odwiedzić [link/odnośnik]. Strona ustawień mniej bezpiecznych aplikacji Włącz tę opcję.
Po włączeniu tej opcji Twój e-mail zostanie wysłany. Tak będzie wyglądał dla osoby, która otrzyma automatycznie wygenerowany e-mail.
Jak widać, za pomocą języka VBA w Excelu można zautomatyzować wiele czynności. Spróbuj pobawić się fragmentami kodu, które poznałeś w tym artykule, i stwórz własne, unikalne automatyzacje VBA.
Możliwość dodawania komentarzy nie jest dostępna.