Jeśli dopiero zaczynasz korzystać z VBA, zacznij studiować nasze Przewodnik VBA dla początkujących. Ale jeśli jesteś doświadczonym ekspertem od VBA i szukasz bardziej zaawansowanych rzeczy, które możesz zrobić z VBA w Excelu, czytaj dalej.
Możliwość użycia kodowania VBA w Excelu otwiera cały świat automatyzacji. Możesz zautomatyzować obliczenia w programie Excel, przyciskami, a nawet wysyłać wiadomości e-mail. Istnieje więcej możliwości automatyzacji codziennej pracy z VBA, niż możesz sobie wyobrazić.
Zaawansowany przewodnik VBA dla Microsoft Excel
Głównym celem pisania kodu VBA w Excelu jest umożliwienie wyodrębnienia informacji z arkusza kalkulacyjnego wykonaj na nim różne obliczenia, a następnie zapisz wyniki z powrotem do arkusza kalkulacyjnego
Poniżej przedstawiono najczęściej używane zastosowania VBA w programie Excel.
Dzięki tym trzem przykładom powinieneś być w stanie napisać wiele własnych zaawansowanych kodów Excel VBA.
Importowanie danych i wykonywanie obliczeń
Jedną z najczęstszych rzeczy, z których ludzie korzystają w Excelu wykonuje obliczenia na danych, które istnieją poza programem Excel. Jeśli nie korzystasz z VBA, oznacza to, że musisz ręcznie zaimportować dane, uruchomić obliczenia i wyprowadzić te wartości do innego arkusza lub raportu.
In_content_1 all: [300x250] / dfp : [640x360]->Za pomocą VBA możesz zautomatyzować cały proces. Na przykład, jeśli masz nowy plik CSV pobrany do katalogu na twoim komputerze w każdy poniedziałek, możesz skonfigurować kod VBA, aby był uruchamiany przy pierwszym otwarciu arkusza kalkulacyjnego we wtorek rano.
Poniższy kod importowy zostanie uruchom i zaimportuj plik CSV do arkusza kalkulacyjnego Excel.
Dim ws As Worksheet, strFile As StringSet 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 Sheet1. Z menu rozwijanych obiektów i metod wybierz Arkusz pracyi Aktywuj. Spowoduje to uruchomienie kodu przy każdym otwarciu arkusza kalkulacyjnego.
Spowoduje to utworzenie funkcji Sub Worksheet_Activate (). Wklej powyższy kod do tej funkcji.
Ustawia aktywny arkusz na Arkusz1, czyści arkusz, łączy się z plikiem przy użyciu ścieżki pliku zdefiniowanej za pomocą zmiennej strFile, a następnie Zpętla przechodzi przez każdą linię w pliku i umieszcza dane w arkuszu, zaczynając od komórki A1.
Jeśli uruchomisz ten kod, zobaczysz, że dane pliku CSV jest importowany do pustego arkusza kalkulacyjnego, w Arkuszu1.
Importowanie to tylko pierwszy krok . Następnie chcesz utworzyć nowy nagłówek kolumny, który będzie zawierał wyniki obliczeń. W tym przykładzie powiedzmy, że chcesz obliczyć 5% podatków zapłaconych od sprzedaży każdego przedmiotu.
Kolejność działań, które powinien wykonać Twój kod, to:
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 znajduje ostatni wiersz w arkuszu danych, a następnie ustawia zakres komórek (kolumna z cenami sprzedaży) zgodnie z pierwszym i ostatnim wierszem danych. Następnie kod przechodzi przez każdą z tych komórek, oblicza podatek i zapisuje wyniki w nowej kolumnie (kolumna 5).
Wklej powyższy kod VBA poniżej poprzedniego kodu i uruchom skrypt. Zobaczysz wyniki pojawiające się w kolumnie E.
Teraz za każdym razem, gdy otworzysz arkusz programu Excel, automatycznie wyjdzie on i pobierze najświeższą kopię danych z pliku CSV. Następnie wykona obliczenia i zapisze wyniki w arkuszu. Nie musisz już nic robić ręcznie!
Oblicz wyniki za pomocą przycisku
Jeśli wolisz mieć większą bezpośrednią kontrolę nad uruchomieniem obliczeń , zamiast uruchamiać się automatycznie po otwarciu arkusza, możesz zamiast tego użyć przycisku sterowania.
Przyciski sterowania 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 zastosować stawkę podatku 5% dla jednego regionu i stawkę podatku 7% dla innego?
Możesz zezwolić, aby ten sam kod importu CSV uruchom automatycznie, ale pozostaw kod obliczania podatku do uruchomienia po naciśnięciu odpowiedniego przycisku.
Korzystając z tego samego arkusza kalkulacyjnego co powyżej, wybierz kartę Deweloperi wybierz Wstawz grupy Sterowaniena wstążce. Wybierz przycisk Formant ActiveX z menu rozwijanego.
Narysuj przycisk na dowolnej części arkusza, z dala od miejsca, w którym trafią dane.
Kliknij prawym przyciskiem myszy przycisk i wybierz Właściwości. W oknie Właściwości zmień Podpis na to, co chcesz wyświetlić użytkownikowi. W tym przypadku może to być Oblicz 5% podatku.
Zobaczysz ten tekst odzwierciedlony na samym przycisku. Zamknij okno właściwościi kliknij dwukrotnie sam przycisk. Spowoduje to otwarcie okna edytora kodu, a kursor znajdzie się w funkcji, która zostanie uruchomiona, gdy użytkownik naciśnie przycisk.
Wklej kod obliczania podatku z powyższej sekcji do tej funkcji, utrzymując mnożnik stawki podatkowej na poziomie 0,05. Pamiętaj, aby dołączyć 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. Utwórz podpis Oblicz 7% podatku.
Kliknij dwukrotnie ten przycisk i wklej ten sam kod, ale ustaw mnożnik podatku na 0,07.
Teraz, w zależności od tego, który przycisk naciśniesz, kolumna podatków oblicz odpowiednio.
Po zakończeniu będziesz mieć oba przyciski na arkuszu. Każdy z nich rozpocznie inne obliczenia podatkowe i zapisze inne wyniki w kolumnie wyników.
Aby wysłać SMS-a, wybierz menu Deweloperi wybierz Tryb projektowaniaz grupy Kontrolki na wstążce, aby wyłączyć Tryb projektowania. To aktywuje przyciski.
Spróbuj wybrać każdy przycisk, aby zobaczyć, jak zmienia się kolumna wyniku „podatki”.
Wyniki obliczeń e-mailem do kogoś
Co jeśli chcesz wysłać wyniki z arkusza kalkulacyjnego komuś za pośrednictwem poczty e-mail?
Możesz utworzyć kolejny przycisk o nazwie Arkusz e-mail do szefa, postępując zgodnie z powyższą procedurą. Kod tego przycisku będzie wymagał użycia obiektu Excel CDO do skonfigurowania ustawień e-mail SMTP i wysłania wyników pocztą e-mail w formacie czytelnym dla użytkownika.
Aby włączyć tę funkcję, musisz wybrać Narzędzia i Referencje. Przewiń w dół do Microsoft CDO dla Windows 2000 Library, włącz ją i wybierz OK.
W kodzie są trzy główne sekcje, które należy utworzyć, aby wysłać wiadomość e-mail i osadzić wyniki arkusza kalkulacyjnego.
Pierwszym z nich jest skonfigurowanie zmiennych do przechowywania temat, adresy Do i Od oraz treść wiadomości e-mail.
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 linię do ciała.
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 obejmuje konfigurację ustawień SMTP, aby można było wysyłać wiadomości e-mail za pośrednictwem serwera SMTP. Jeśli korzystasz z Gmaila, zazwyczaj jest to adres e-mail Gmaila, hasło Gmaila i serwer SMTP Gmaila (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 ze szczegółami konta.
Aby rozpocząć wysyłanie wiadomości e-mail, wstaw 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: jeśli podczas próby uruchomienia tego kodu pojawi się błąd transportu, prawdopodobnie przyczyną jest zablokowanie przez Twoje konto Google „mniej bezpiecznych aplikacji”. Musisz odwiedzić mniej bezpieczna strona ustawień aplikacji i włączyć tę funkcję.
Po włączeniu twój e-mail zostanie wysłany. Tak wygląda osoba otrzymująca automatycznie wygenerowany e-mail z wynikami.
Jak widać, istnieje wiele rzeczy, które można zautomatyzować za pomocą Excel VBA. Spróbuj bawić się fragmentami kodu, o których dowiedziałeś się w tym artykule i utwórz własne unikalne automatyzacje VBA.