Zaawansowany przewodnik VBA dla MS Excel

Jeśli dopiero zaczynasz z VBA, będziesz chciał zacząć studiować nasz przewodnik VBA dla początkujących. Ale jeśli jesteś doświadczonym ekspertem VBA i szukasz bardziej zaawansowanych rzeczy, które możesz zrobić z VBA w Excel, a następnie czytaj dalej.

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

To ustawia aktywny arkusz roboczy Arkusz 1, wyczyszcza arkusz, łączy się z plikiem za pomocą ścieżki pliku zdefiniowanego za pomocą Strfile zmienna, a następnie Z pę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 są importowane do pustego arkusza kalkulacyjnego, w Arkusz 1.

Importowanie to tylko pierwszy krok. Następnie chcesz utworzyć nowy nagłówek dla kolumny, która 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ń powinien podjąć Twój kod:

1. Utwórz kolumnę Nowe wyniki o nazwie podatki.
2. Pętla przez Sprzedane jednostki kolumn i oblicz podatek od sprzedaży.
3. Napisz wyniki oblicz do odpowiedniego wiersza w arkuszu.

Poniższy kod dokona wszystkich tych kroków.

Dim Lastrow tak długo
Dim startcell jako zakres
Dim RowCounter jako liczba całkowita
Dim RNG jako zakres, komórka jako zakres
Dim flttax jako podwójny
Set startCell = Range („a1”)
„Znajdź ostatni rząd i kolumnę
Lastrow = WS.Komórki (WS.Wydziwianie.Licz, startcell.Kolumna).Koniec (xlup).Wiersz
Ustaw rng = WS.Zakres (WS.Komórki (2, 4), WS.Komórki (Lastrow, 4))
RowCounter = 2
Komórki (1, 5) = „Podatki”
Dla każdej komórki w RNG
flttax = komórka.Wartość * 0.05
Komórki (RowCounter, 5) = flttax
RowCounter = RowCounter + 1
Następna komórka

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 rządem danych. Następnie kod pętle przez każdą z tych komórek wykonuje obliczenia podatkowe 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 otwierasz arkusz Excel, automatycznie wyjdzie i otrzyma najświeższą kopię danych z pliku CSV. Następnie wykonuje obliczenia i zapisze wyniki na arkuszu. Nie musisz już nic robić ręcznie!

Oblicz wyniki z przycisku Naciśnij

Jeśli wolisz mieć bardziej bezpośrednią kontrolę po uruchomieniu obliczeń, zamiast działać automatycznie po otwarciu arkusza, możesz zamiast tego użyć przycisku sterowania.

Przyciski sterujące są przydatne, jeśli chcesz kontrolować, które obliczenia są używane. Na przykład w tym samym przypadku co powyżej, co jeśli chcesz użyć 5% stawki podatkowej dla jednego regionu i 7% stawki podatkowej dla innego?

Możesz pozwolić, aby ten sam kod importowy CSV działał automatycznie, ale pozostaw kod obliczeń podatkowych, gdy naciśnij odpowiedni przycisk.

Korzystając z tego samego arkusza kalkulacyjnego jak powyżej, wybierz Deweloper karta i wybierz Wstawić z Sterownica grupa w wstążce. Wybierz naciśnij przycisk Kontrola ActiveX z menu rozwijanego.

Narysuj przyciski na dowolnej części arkusza z dala od miejsca, w którym pójdą dowolne dane.

Kliknij przycisk prawym przyciskiem myszy i wybierz Nieruchomości. W oknie właściwości zmień podpis na to, co chcesz wyświetlić na użytkownika. W takim przypadku może to być Oblicz 5% podatków.

Zobaczysz ten tekst odbity na samym przycisku. Zamknij nieruchomości okno i kliknij dwukrotnie sam pushbutton. To otworzy okno edytora kodu, a twój kursor będzie w funkcji, która będzie działać, gdy użytkownik naciśnie pushButton.

Wklej kod obliczeń podatkowych z powyższej sekcji do tej funkcji, utrzymując mnożnik stawki podatkowej na 0.05. Pamiętaj, aby dołączyć następujące 2 wiersze w celu zdefiniowania aktywnego arkusza.

Dim WS jako arkusz roboczy, Strfile jako ciąg
SET WS = ActiveWorkbook.Arkusze („arkusz 1”)

Teraz powtórz proces ponownie, tworząc drugi przycisk. Zrób podpis Oblicz 7% podatków.

Kliknij dwukrotnie ten przycisk i wklej ten sam kod, ale zrób mnożnik podatkowy 0.07.

Teraz, w zależności od przycisku, kolumna podatków zostanie odpowiednio obliczona.

Po zakończeniu oba przyciski nacisku na arkuszu. Każdy z nich zainicjuje inne obliczenia podatkowe i zapisze różne wyniki w kolumnie wyników.

Aby to wysłać, wybierz Deweloper menu i wybierz Tryb projektowania uformuj grupę kontroli w wstążce, aby wyłączyć Tryb projektowania. To aktywuje przyciski.

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

Wyniki obliczeń e -mail dla kogoś

Co jeśli chcesz wysłać wyniki w arkuszu kalkulacyjnym do kogoś za pośrednictwem e -maila?

Możesz utworzyć inny przycisk wywołany Arkusz e -mail do szefa stosując tę ​​samą procedurę powyżej. Kod tego przycisku będzie obejmował użycie obiektu Excel CDO do konfigurowania ustawień e-maili SMTP i wysyłania e-maila do wyników w formacie odczytującego użytkownika.

Aby włączyć tę funkcję, musisz wybrać Narzędzia i referencje. Przewiń w dół do Microsoft CDO dla biblioteki Windows 2000, Włącz i wybierz OK.

Istnieją trzy główne sekcje kodu, które musisz utworzyć, aby wysłać wyniki wiadomości e -mail i osadzenia arkusza kalkulacyjnego.

Pierwszym z nich jest konfiguracja zmiennych do utrzymywania tematu, adresów i na podstawie ciała e -mail.

Dim CDO_mail jako obiekt
Dim cdo_config jako obiekt
Dim smtp_config jako wariant
Dim strsubject jako ciąg
Dim strfrom jako ciąg
Dim strto jako ciąg
Dim Strcc jako ciąg
Dim Strbcc jako ciąg
Dim Strbody jako ciąg
Dim Lastrow tak długo
Dim startcell jako zakres
Dim RowCounter jako liczba całkowita
Dim RNG jako zakres, komórka jako zakres
Dim flttax jako podwójny

SET WS = ActiveWorkbook.Arkusze („arkusz 1”)
strSubject = „Podatki zapłacone w tym kwartale”
strfrom = "[email protected] "
strto = "[email protected] "
strcc = ""
STRBCC = ""
strbody = "Poniżej znajduje się awaria podatków płaconych od sprzedaży w tym kwartale."

Oczywiście ciało musi być dynamiczne w zależności od wyników w arkuszu, więc tutaj musisz dodać pętlę przechodzi przez zakres, wyodrębnia dane i zapisuje linię na raz do ciała.

Set startCell = Range („a1”) „Znajdź ostatni wiersz i kolumnę Lastrow = WS.Komórki (WS.Wydziwianie.Licz, startcell.Kolumna).Koniec (xlup).Zestaw wierszy rng = WS.Zakres (WS.Komórki (2, 4), WS.Komórki (Lastrow, 4)) RowCounter = 2 Strbody = Strbody & Vbcrlf dla każdej komórki w RNG Strbody = Strbody & Vbcrlf Strbody = Strbody & „We SPRZEDAŻ” i Komórki (RowCounter, 3).Wartość i „of” i komórek (RowCounter, 1).Wartość _ & „for” i komórek (RowCounter, 4).Wartość i „i płatne podatki” i komórek (RowCounter, 5).Wartość & ".„RowCounter = RowCounter + 1 Następna komórka

Następna sekcja obejmuje konfigurowanie ustawień SMTP, abyś mógł wysłać wiadomość e -mail za pośrednictwem serwera SMTP. Jeśli używasz Gmaila, zazwyczaj jest to Twój adres e -mail Gmail, hasło Gmail i serwer Gmail SMTP (SMTP.Gmail.com).

SET CDO_mail = CreateObject ("CDO.Komunikat ") na błędach goto erser_handling Set CDO_CONFIG = CreateObject (" CDO.Konfiguracja ") CDO_CONFIG.Załaduj -1 Ustaw SMTP_CONFIG = CDO_CONFIG.Pola z SMTP_CONFIG .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/sensing ”) = 2 .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/smtpserver ") =" SMTP.Gmail.com " .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/smtpauthenticate ") = 1 .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/sendusername ") =" e -mail@strona internetowa.com " .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/sendPassword ") =" hasło " .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/smtpserverport ") = 465 .Pozycja („schematy http: //.Microsoft.com/cdo/configuration/smtpusessl ") = true .Zaktualizuj koniec z zestawem CDO_mail .Konfiguracja = cdo_config koniec z

Wymień e -mail@strona internetowa.com i hasło z własnymi szczegółami konta.

Wreszcie, aby zainicjować wysłanie e -maila, wstaw następujący kod.

CDO_mail.Temat = strsubject
CDO_mail.Od = strfrom
CDO_mail.Do = Strto
CDO_mail.TextBody = Strbody
CDO_mail.CC = Strcc
CDO_mail.BCC = STRBCC
CDO_mail.Wysłać
Error_handling:
Jeśli err.Opis „” następnie msgbox err.Opis

Notatka: Jeśli widzisz błąd transportu podczas próby uruchomienia tego kodu, jest to prawdopodobne, że konto Google blokuje „mniej bezpieczne aplikacje” przed uruchomieniem. Musisz odwiedzić stronę Ustawienia mniej bezpiecznych aplikacji i włączyć tę funkcję.

Po tym, jak zostanie włączony, Twój e -mail zostanie wysłany. Tak to wygląda dla osoby, która otrzymuje automatycznie wygenerowane wyniki e -mail.

Jak widać, jest wiele, które możesz zautomatyzować z Excel VBA. Spróbuj zagrać z fragmentami kodu, o których nauczyłeś się w tym artykule i stwórz własne unikalne automatyczne automatyczne automatyczne.