Ein fortgeschrittener VBA -Leitfaden für MS Excel

Wenn Sie gerade erst mit VBA anfangen, möchten Sie unseren VBA -Leitfaden für Anfänger studieren. Wenn Sie jedoch ein erfahrener VBA -Experte sind und nach fortgeschritteneren Dingen suchen, die Sie mit VBA in Excel anfangen können, dann lesen Sie weiter.

Die Fähigkeit, VBA -Codierung in Excel zu verwenden, eröffnet eine ganze Welt der Automatisierung. Sie können Berechnungen in Excel, Pushbuttons automatisieren und sogar E -Mails senden. Es gibt mehr Möglichkeiten, Ihre tägliche Arbeit mit VBA zu automatisieren, als Sie vielleicht erkennen können.

Dies setzt das aktive Arbeitsblatt auf Blatt1, Löscht das Blatt und stellt mit dem mit dem definierten Dateipfad eine Verbindung zur Datei heran Strafile variabel und dann die Mit Schleifen fahren durch jede Zeile in der Datei und platziert die Daten in das Blatt, das in der Zelle A1 beginnt.

Wenn Sie diesen Code ausführen, werden Sie feststellen Blatt1.

Importing ist nur der erste Schritt. Als nächstes möchten Sie einen neuen Header für die Spalte erstellen, die Ihre Berechnungsergebnisse enthalten. Nehmen wir in diesem Beispiel an, Sie möchten die 5% -Steuern berechnen, die für den Verkauf jedes Artikels gezahlt wurden.

Die Reihenfolge der Aktionen, die Ihr Code ergreifen sollte, lautet:

1. Erstellen Sie eine Spalte neuer Ergebnisse aufgerufen Steuern.
2. Schleifen Sie durch die Einheiten verkauft Spalte und berechnen Sie die Umsatzsteuer.
3. Schreiben Sie die Berechnungsergebnisse in die entsprechende Zeile im Blatt.

Der folgende Code führt alle diese Schritte aus.

Dimer Lastrow so lange
Dim startcell als Bereich
Dimer Rowcounter als Ganzzahl
Dim Dim rng als Bereich, Zelle als Bereiche
Schwaches flttax als doppelt
Setzen Sie StartCell = Bereich ("A1")
'Finden Sie die letzte Zeile und Spalte
Lastrow = ws.Zellen (WS.Reihen.Zählen Sie, starten Sie.Spalte).Ende (XLUP).Reihe
RNG = WS einstellen.Bereich (WS.Zellen (2, 4), WS.Zellen (Lastrow, 4))
RowCounter = 2
Zellen (1, 5) = "Steuern"
Für jede Zelle in RNG
flttax = cell.Wert * 0.05
Zellen (Rowcounter, 5) = flttax
rowcounter = rowcounter + 1
Nächste Zelle

Dieser Code findet die letzte Zeile in Ihrem Datenblatt und legt dann den Bereich der Zellen (die Spalte mit den Verkaufspreisen) gemäß der ersten und letzten Datenreihe fest. Anschließend schleifen die Code durch jede dieser Zellen, führt die Steuerberechnung durch und schreibt die Ergebnisse in Ihre neue Spalte (Spalte 5).

Fügen Sie den obigen VBA -Code unter dem vorherigen Code ein und führen Sie das Skript aus. Sie werden sehen, dass die Ergebnisse in Spalte E angezeigt werden.

Jedes Mal, wenn Sie Ihr Excel -Arbeitsblatt öffnen, wird automatisch ausgeht und die frischeste Kopie von Daten aus der CSV -Datei erhalten. Dann wird die Berechnungen durchgeführt und die Ergebnisse in das Blatt geschrieben. Sie müssen nichts mehr manuell tun!

Berechnen Sie die Ergebnisse aus der Taste Drücken Sie

Wenn Sie lieber eine direktere Kontrolle haben möchten, wenn die Berechnungen ausgeführt werden, anstatt automatisch auszuführen, wenn sich das Blatt öffnet, können Sie stattdessen eine Steuertaste verwenden.

Steuertasten sind nützlich, wenn Sie steuern möchten, welche Berechnungen verwendet werden. In diesem Fall wie oben wie oben, was ist, wenn Sie einen Steuersatz von 5% für eine Region und einen Steuersatz von 7% für eine andere verwenden möchten?

Sie können zulassen, dass der gleiche CSV -Importcode automatisch ausgeführt wird. Lassen.

Wählen Sie die gleiche Tabelle wie oben mit derselben Tabelle aus Entwickler Registerkarte und auswählen Einfügung von dem Kontrollen Gruppe im Band. Wähle aus Druckknopf ActiveX -Steuerung aus dem Dropdown -Menü.

Zeichnen Sie den Pushbutton auf einen Teil des Blattes entfernt, wo Daten gehen werden.

Klicken Sie mit der rechten Maustaste auf die Schaltfläche und wählen Sie Eigenschaften. Ändern Sie im Eigenschaftenfenster die Bildunterschrift in das, was Sie dem Benutzer anzeigen möchten. In diesem Fall könnte es sein 5% Steuer berechnen.

Sie werden diesen Text sehen, der sich auf der Drucktaste selbst widerspiegelt. Schließe Eigenschaften Fenster, und doppelklicken Sie auf den Pushbutton selbst. Dadurch wird das Fenster Code -Editor geöffnet, und Ihr Cursor befindet sich in der Funktion, die ausgeführt wird, wenn der Benutzer auf den Pushbutton drückt.

Fügen Sie den Steuerberechnungscode aus dem obigen Abschnitt in diese Funktion ein und halten Sie den Steuersatzmultiplikator bei 0.05. Denken Sie daran, die folgenden 2 Zeilen einzuschließen, um das aktive Blatt zu definieren.

Dim WS als Arbeitsblatt, Strfile als Zeichenfolge
Setzen Sie WS = Active Workbook.Blätter ("Blatt1")

Wiederholen Sie nun den Vorgang erneut und erstellen Sie eine zweite Drucktaste. Die Bildunterschrift machen Berechnen Sie 7% Steuern.

Doppelklicken Sie auf diese Taste und fügen Sie denselben Code ein, machen Sie jedoch den Steuermultiplikator 0.07.

Je nachdem, welche Taste Sie drücken, wird die Steuernspalte entsprechend berechnet.

Sobald Sie fertig sind, haben Sie beide Knöpfe auf Ihrem Blatt drücken. Jeder von ihnen leitet eine andere Steuerberechnung ein und schreibt unterschiedliche Ergebnisse in die Ergebnisspalte.

Um dies zu schreiben, wählen Sie die aus Entwickler Menü und auswählen Designmodus Formen Sie die Kontrollgruppe im Band, um zu deaktivieren Designmodus. Dadurch werden die Taste -Tasten aktiviert.

Versuchen Sie, jede Drucktaste auszuwählen, um zu sehen, wie sich die Spalte „Steuern“ der Ergebnisse ändert.

E -Mail -Berechnungsergebnisse an jemanden

Was ist, wenn Sie die Ergebnisse in der Tabelle per E -Mail an jemanden senden möchten?

Sie können eine andere Schaltfläche mit dem Namen erstellen E -Mail -Blatt an Chef Verwenden Sie die gleiche Prozedur oben. Der Code für diese Schaltfläche umfasst die Verwendung des Excel-CDO-Objekts, um SMTP-E-Mail-Einstellungen zu konfigurieren und die Ergebnisse in ein benutzerlesbares Format zu senden.

Um diese Funktion zu aktivieren, müssen Sie auswählen Tools und Referenzen. Scrollen Sie nach unten zu Microsoft CDO für Windows 2000 -Bibliothek, Aktivieren Sie es und wählen Sie es aus OK.

Der Code enthält drei Hauptabschnitte, die Sie erstellen müssen, um eine E -Mail zu senden und Tabellenkalkulationsergebnisse einzubetten.

Das erste ist die Einrichtung von Variablen, um das Thema zu halten, von und von Adressen und dem E -Mail -Körper.

Dim CDO_Mail als Objekt
Dim CDO_Config als Objekt
Dim SMTP_Config als Variante
Dim Strsubject als String
Dim strfrom als Zeichenfolge
Dim Strto als Zeichenfolge
Dim Strcc als String
Dim strbcc als Zeichenfolge
Dunkelne Strom als String
Dimer Lastrow so lange
Dim startcell als Bereich
Dimer Rowcounter als Ganzzahl
Dim Dim rng als Bereich, Zelle als Bereiche
Schwaches flttax als doppelt

Setzen Sie WS = Active Workbook.Blätter ("Blatt1")
StreSubject = "Steuern, die in diesem Quartal bezahlt wurden"
strfrom = "[email protected] "
Strto = "[email protected] "
STRCC = ""
strbcc = ""
Strkody = "Das Folgende ist die Aufschlüsselung der Steuern, die in diesem Quartal den Umsatz gezahlt haben."

Natürlich muss der Körper dynamisch sein, je nachdem, welche Ergebnisse sich im Blatt befinden. Hier müssen Sie eine Schleife hinzufügen, die den Bereich durchläuft, die Daten extrahiert und jeweils eine Zeile zum Körper schreibt.

Setzen Sie startCell = Bereich ("a1") 'Finden Sie die letzte Zeile und Spaltenlastrow = WS.Zellen (WS.Reihen.Zählen Sie, starten Sie.Spalte).Ende (XLUP).Zeilensatz RNG = WS.Bereich (WS.Zellen (2, 4), WS.Zellen (Lastrow, 4)) Rowcounter = 2 strkodo = strkodo & vbcrlf für jede Zelle in RNG strkodo = strsbody & vbcrlf strkodo = strkodo & "Wir verkauften" und Zellen (Rowcounter, 3).Wert & "von" & Zellen (Rowcounter, 1).Wert _ & "für" & Zellen (Rowcounter, 4).Wert & "und bezahlte Steuern von" & Zellen (Rowcounter, 5).Wert & "."Rowcounter = RowCounter + 1 Nächste Zelle

Im nächsten Abschnitt werden die SMTP -Einstellungen eingerichtet, damit Sie E -Mails über Ihren SMTP -Server senden können. Wenn Sie Google Mail verwenden, ist dies in der Regel Ihre Google Mail -E -Mail -Adresse, Ihr Google Mail -Passwort und der Google Mail SMTP -Server (SMTP.Google Mail.com).

Setzen Sie CDO_MAIL = CREATEOBJECT ("CDO.Meldung ") Auf Fehler GOTO ERROR_HANDLING SET CDO_CONFIG = CREATEOBJECT (" CDO.Konfiguration ") CDO_Config.Laden Sie -1 Setzen Sie STP_CONFIG = CDO_CONfig.Felder mit SMTP_CONfig .Item ("http: // Schemas.Microsoft.com/cdo/configuration/sendusing ") = 2 .Item ("http: // Schemas.Microsoft.com/cdo/configuration/SMTPServer ") =" SMTP.Google Mail.com " .Item ("http: // Schemas.Microsoft.com/cdo/configuration/SMTPAuthenticate ") = 1 .Item ("http: // Schemas.Microsoft.com/cdo/configuration/sendusername ") =" E -Mail@ -Website.com " .Item ("http: // Schemas.Microsoft.com/cdo/configuration/sendPassword ") =" Passwort " .Item ("http: // Schemas.Microsoft.com/cdo/configuration/SMTPServerport ") = 465 .Item ("http: // Schemas.Microsoft.com/cdo/configuration/SMTPUSESSL ") = TRUE .Update End mit CDO_MAIL SET .Configuration = cdo_config Ende mit

E -Mail@ -Website ersetzen.Com und Passwort mit Ihren eigenen Kontodetails.

Um die E -Mail -Senden zu initiieren, fügen Sie schließlich den folgenden Code ein.

CDO_MAIL.Betreff = Strsubject
CDO_MAIL.Von = strfrom
CDO_MAIL.Bis = strto
CDO_MAIL.Textbody = strsbody
CDO_MAIL.CC = STRCC
CDO_MAIL.BCC = strbcc
CDO_MAIL.Schicken
Fehlerbehandlung:
Wenn er irrt.Beschreibung "" dann Msgbox err.Beschreibung

Notiz: Wenn Sie einen Transportfehler sehen, wenn Sie versuchen, diesen Code auszuführen. Sie müssen die Seite „Less Secure Apps Einstellungen" besuchen und diese Funktion einschalten.

Nachdem dies aktiviert ist, wird Ihre E -Mail gesendet. So sieht es für die Person, die Ihre automatisch generierten Ergebnisse E -Mail empfängt, aus.

Wie Sie sehen, gibt es viel, was Sie tatsächlich mit Excel VBA automatisieren können. Versuchen Sie, mit den Code -Snippets herumzuspielen, die Sie in diesem Artikel gelernt haben.