Zaawansowany przewodnik VBA dla MS Excel

Jeśli dopiero zaczynasz korzystać z VBA , powinieneś zacząć studiować nasz przewodnik VBA dla początkujących(VBA guide for beginners) . Ale jeśli jesteś doświadczonym ekspertem VBA i szukasz bardziej zaawansowanych rzeczy, które możesz zrobić za pomocą VBA w programie Excel , czytaj dalej.

Możliwość wykorzystania kodowania VBA w (VBA)Excelu(Excel) otwiera cały świat automatyzacji. Możesz zautomatyzować obliczenia w programie Excel(Excel) , przyciski, a nawet wysłać e-mail. Istnieje więcej możliwości zautomatyzowania codziennej pracy z VBA , niż myślisz.

Zaawansowany przewodnik VBA dla programu Microsoft Excel(Advanced VBA Guide For Microsoft Excel)

Głównym celem pisania kodu VBA w programie (VBA)Excel jest wyodrębnienie informacji z arkusza kalkulacyjnego, wykonanie na nim różnych obliczeń, a następnie zapisanie wyników z powrotem do arkusza kalkulacyjnego

Poniżej przedstawiono najczęstsze zastosowania języka VBA w programie Excel .

  • Importuj(Import) dane i wykonuj obliczenia
  • Oblicz(Calculate) wyniki po naciśnięciu przycisku przez użytkownika
  • Wyślij(Email) do kogoś wyniki obliczeń

Dzięki tym trzem przykładom powinieneś być w stanie napisać swój własny zaawansowany kod Excel VBA .

Importowanie danych i wykonywanie obliczeń(Importing Data and Performing Calculations)

Jedną z najczęstszych rzeczy, do których ludzie używają programu Excel(Excel) , jest wykonywanie obliczeń na danych, które istnieją poza programem Excel(Excel) . Jeśli nie używasz VBA , oznacza to, że musisz ręcznie zaimportować dane, uruchomić obliczenia i wyprowadzić te wartości do innego arkusza lub raportu.

Dzięki VBA możesz zautomatyzować cały proces. Na przykład, jeśli nowy plik CSV jest pobierany do katalogu na komputerze w każdy poniedziałek(Monday) , możesz skonfigurować kod VBA tak , aby był uruchamiany przy pierwszym otwarciu arkusza kalkulacyjnego we wtorek(Tuesday) rano.

Poniższy kod importu uruchomi i zaimportuje plik CSV do arkusza kalkulacyjnego Excel .

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Otwórz narzędzie do edycji Excel VBA i wybierz obiekt (Excel VBA)Sheet1 . Z pól rozwijanych obiektów i metod wybierz opcję Arkusz(Worksheet) i Aktywuj(Activate) . Spowoduje to uruchomienie kodu za każdym razem, gdy otworzysz arkusz kalkulacyjny.

Spowoduje to utworzenie funkcji Sub Worksheet_Activate() . Wklej powyższy kod do tej funkcji.

To ustawia aktywny arkusz na Arkusz1(Sheet1) , czyści arkusz, łączy się z plikiem przy użyciu ścieżki pliku zdefiniowanej za pomocą zmiennej strFile , a następnie pętla With przechodzi przez każdy wiersz w pliku i umieszcza dane w arkuszu, zaczynając od komórki A1 .

Jeśli uruchomisz ten kod, zobaczysz, że dane z pliku CSV są importowane do pustego arkusza kalkulacyjnego w (CSV)Arkuszu1(Sheet1) .

Importowanie to dopiero pierwszy krok. Następnie chcesz utworzyć nowy nagłówek kolumny, która będzie zawierać wyniki obliczeń. W tym przykładzie załóżmy, że chcesz obliczyć 5% podatku płaconego od sprzedaży każdego przedmiotu.

Kolejność działań, które powinien wykonać Twój kod, jest następująca:

  1. Utwórz nową kolumnę wyników o nazwie podatki(taxes) .
  2. Przejrzyj kolumnę sprzedanych jednostek i oblicz podatek od sprzedaży.(units sold)
  3. Zapisz wyniki obliczeń w odpowiednim wierszu w arkuszu.

Poniższy kod wykona wszystkie te kroki.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell

Ten kod wyszukuje ostatni wiersz w arkuszu danych, a następnie ustawia zakres komórek (kolumnę z cenami sprzedaży) zgodnie z pierwszym i ostatnim wierszem danych. Następnie kod przechodzi przez każdą z tych komórek, wykonuje obliczenia podatku i zapisuje wyniki w nowej kolumnie (kolumna 5).

Wklej powyższy kod VBA poniżej poprzedniego kodu i uruchom skrypt. Zobaczysz wyniki pojawią się w kolumnie E.

Teraz za każdym razem, gdy otworzysz arkusz programu Excel(Excel) , automatycznie wyjdzie on i pobierze najświeższą kopię danych z pliku CSV . Następnie wykona obliczenia i zapisze wyniki do arkusza. Nie musisz już nic robić ręcznie!

Oblicz wyniki z naciśnięcia przycisku(Calculate Results From Button Press)

Jeśli wolisz mieć bardziej bezpośrednią kontrolę nad uruchamianiem obliczeń, zamiast uruchamiać je automatycznie po otwarciu arkusza, możesz zamiast tego użyć przycisku kontrolnego.

Przyciski kontrolne(Control) są przydatne, jeśli chcesz kontrolować, które obliczenia są używane. Na przykład w tym samym przypadku, co powyżej, co zrobić, jeśli chcesz użyć 5% stawki podatku dla jednego regionu i 7% stawki podatku w innym?

Możesz zezwolić na automatyczne uruchamianie tego samego kodu importu CSV , ale pozostawić kod obliczania podatku uruchamiany po naciśnięciu odpowiedniego przycisku.

Korzystając z tego samego arkusza kalkulacyjnego co powyżej, wybierz kartę Deweloper(Developer) i wybierz Wstaw(Insert) z grupy Kontrolki(Controls) na wstążce. Wybierz przycisk (push button) ActiveX Control z menu rozwijanego.

Przeciągnij przycisk na dowolną część arkusza z dala od miejsca, w którym znajdą się dane.

Kliknij prawym przyciskiem myszy przycisk i wybierz Właściwości(Properties) . W oknie Właściwości(Properties) zmień Podpis na taki, jaki chcesz wyświetlić użytkownikowi. W tym przypadku może to być Calculate 5% Tax .

Zobaczysz ten tekst odzwierciedlony na samym przycisku. Zamknij okno właściwości(properties) i kliknij dwukrotnie sam przycisk. Spowoduje to otwarcie okna edytora kodu, a kursor znajdzie się wewnątrz funkcji, która zostanie uruchomiona, gdy użytkownik naciśnie przycisk.

Wklej kod obliczenia podatku z powyższej sekcji do tej funkcji, zachowując mnożnik stawki podatku na poziomie 0,05. Pamiętaj, aby uwzględnić następujące 2 wiersze, aby zdefiniować aktywny arkusz.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Teraz powtórz proces ponownie, tworząc drugi przycisk. Zrób podpis Calculate 7% Tax .

Kliknij dwukrotnie(Double-click) ten przycisk i wklej ten sam kod, ale ustaw mnożnik podatku 0,07.

Teraz, w zależności od tego, który przycisk naciśniesz, kolumna podatków zostanie odpowiednio obliczona.

Gdy skończysz, będziesz miał oba przyciski na swoim arkuszu. Każdy z nich zainicjuje inną kalkulację podatku i zapisze inne wyniki w kolumnie wynikowej. 

Aby to napisać, wybierz menu Deweloper(Developer) i wybierz Tryb projektowania(Design Mode) z grupy Kontrolki(Controls) na wstążce, aby wyłączyć Tryb projektowania(Design Mode) . To aktywuje przyciski. 

Spróbuj wybrać każdy przycisk, aby zobaczyć, jak zmienia się kolumna wyników „podatki”.

Wyślij e-mailem wyniki obliczeń do kogoś(Email Calculation Results to Someone)

Co zrobić, jeśli chcesz wysłać wyniki w arkuszu kalkulacyjnym do kogoś e-mailem?

Możesz utworzyć inny przycisk o nazwie Wyślij arkusz do szefa,(Email Sheet to Boss) korzystając z tej samej procedury powyżej. Kod tego przycisku będzie wymagał użycia obiektu CDO programu Excel(Excel CDO) do skonfigurowania ustawień poczty e-mail SMTP i wysłania wyników pocztą elektroniczną w formacie czytelnym dla użytkownika.

Aby włączyć tę funkcję, musisz wybrać Narzędzia i odniesienia(Tools and References) . Przewiń w dół do Microsoft CDO for Windows 2000 Library , włącz ją i wybierz OK .

Istnieją trzy główne sekcje kodu, które musisz utworzyć, aby wysłać wiadomość e-mail i osadzić wyniki w arkuszu kalkulacyjnym.

Pierwszym z nich jest skonfigurowanie zmiennych do przechowywania tematu, adresów Do i Od oraz treści wiadomości e-mail.(From)

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

Oczywiście treść musi być dynamiczna w zależności od wyników w arkuszu, więc tutaj musisz dodać pętlę, która przechodzi przez zakres, wyodrębnia dane i zapisuje wiersz na raz w treści.

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & 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
Next cell

Następna sekcja dotyczy konfiguracji ustawień SMTP , aby można było wysyłać wiadomości e-mail przez serwer SMTP . Jeśli używasz Gmaila(Gmail) , jest to zazwyczaj Twój adres e-mail Gmaila(Gmail) , hasło do Gmaila i serwer (Gmail)SMTP Gmaila(Gmail SMTP) (smtp.gmail.com).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With 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 protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Zastąp [email protected] i hasło danymi swojego konta.

Na koniec, aby rozpocząć wysyłanie wiadomości e-mail, wprowadź następujący 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

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Uwaga(Note) : jeśli podczas próby uruchomienia tego kodu zobaczysz błąd transportu, prawdopodobnie Twoje konto Google blokuje uruchamianie „mniej bezpiecznych aplikacji”. Musisz odwiedzić stronę ustawień mniej bezpiecznych aplikacji(less secure apps settings page) i WŁĄCZYĆ tę funkcję.

Po włączeniu, Twój e-mail zostanie wysłany. Tak to wygląda dla osoby, która otrzymuje wiadomość e-mail z automatycznie wygenerowanymi wynikami.

Jak widać, jest wiele rzeczy, które można zautomatyzować za pomocą Excel VBA . Spróbuj poeksperymentować z fragmentami kodu, o których dowiedziałeś się w tym artykule, i stwórz własne unikalne automatyzacje VBA .



About the author

Jestem ekspertem komputerowym z ponad 10-letnim doświadczeniem i specjalizuję się w pomaganiu ludziom w zarządzaniu komputerami w ich biurze. Napisałem artykuły na takie tematy, jak optymalizacja połączenia internetowego, konfiguracja komputera w celu uzyskania najlepszych wrażeń w grach i nie tylko. Jeśli szukasz pomocy we wszystkim, co dotyczy Twojej pracy lub życia osobistego, jestem osobą dla Ciebie!



Related posts