Wenn Sie gerade erst mit VBA anfangen, müssen Sie zunächst die Grundlagen studieren. Eine Einführung in VBAAber wenn Sie ein erfahrener VBA-Experte sind und nach fortgeschritteneren Möglichkeiten suchen, VBA in Excel einzusetzen, lesen Sie weiter.
Die Möglichkeit, VBA-Code in Excel zu verwenden, eröffnet Ihnen völlig neue Automatisierungsmöglichkeiten. Sie können Excel-Berechnungen, Schaltflächen und sogar den E-Mail-Versand automatisieren. Die Möglichkeiten zur Automatisierung Ihrer täglichen Arbeit mit VBA sind vielfältiger, als Sie vielleicht denken.
Erweiterter VBA-Leitfaden für Microsoft Excel
Der Hauptzweck des Schreibens von VBA-Code in Excel besteht darin, Informationen aus einer Tabellenkalkulation zu extrahieren, verschiedene Berechnungen damit durchzuführen und die Ergebnisse anschließend wieder in die Tabellenkalkulation zurückzuschreiben.
Nachfolgend sind die häufigsten Anwendungsfälle von VBA in Excel aufgeführt.
- Daten importieren und Berechnungen durchführen
- Berechnung der Ergebnisse basierend auf den Klicks der Benutzer auf die Schaltflächen
- Die Ergebnisse der Berechnungen per E-Mail an jemanden senden
Anhand dieser drei Beispiele sollten Sie in der Lage sein, verschiedene eigene fortgeschrittene Excel-VBA-Codes zu schreiben.
Daten importieren und Berechnungen durchführen
Eine der häufigsten Anwendungen von Excel ist die Durchführung von Berechnungen mit Daten außerhalb von Excel. Ohne VBA bedeutet dies, dass die Daten manuell importiert, die Berechnungen durchgeführt und die Ergebnisse in ein anderes Tabellenblatt oder einen Bericht ausgegeben werden müssen.
Mithilfe von VBA lässt sich der gesamte Prozess automatisieren. Wenn Sie beispielsweise jeden Montag eine neue CSV-Datei in ein Verzeichnis auf Ihrem Computer herunterladen, können Sie Ihren VBA-Code so konfigurieren, dass er beim ersten Öffnen der Tabelle am Dienstagmorgen ausgeführt wird.
Der folgende Importcode wird ausgeführt und die CSV-Datei wird in Ihre Excel-Tabelle importiert.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Öffnen Sie den Excel-VBA-Editor und wählen Sie das Objekt „Sheet1“ aus. Wählen Sie in den Dropdown-Listenfeldern „Objekt“ und „Methode“ die entsprechende Option aus. Arbeitsblatt Und Mehr erfahrenDadurch wird der Code jedes Mal ausgeführt, wenn Sie die Tabelle öffnen.
Dadurch wird eine Funktion erstellt Sub Worksheet_Activate()Fügen Sie den obigen Code in diese Funktion ein.
Dadurch wird das aktive Arbeitsblatt festgelegt auf Sheet1Scannen Sie das Blatt und stellen Sie eine Verbindung zur Datei her, indem Sie den in der Variablen angegebenen Dateipfad verwenden. strDateiDann die Schleife Mit Indem man jede Zeile der Datei durchgeht und die Daten ab Zelle A1 auf dem Tabellenblatt einfügt.
Wenn Sie diesen Code ausführen, werden Sie sehen, dass die Daten aus der CSV-Datei in Ihre leere Tabellenkalkulation importiert werden. Sheet1.
Der Import ist nur der erste Schritt. Als Nächstes erstellen Sie eine neue Spaltenüberschrift, die die Ergebnisse Ihrer Berechnungen enthält. In diesem Beispiel möchten Sie beispielsweise die 5%ige Steuer berechnen, die beim Verkauf jedes Artikels anfällt.
Die Reihenfolge der Aktionen, die Ihr Code ausführen sollte, ist:
- Erstellen Sie eine neue Ergebnisspalte mit dem Namen Steuern.
- In einer Spalte wiederholen Einheiten verkauft Und berechnen Sie die Umsatzsteuer.
- Tragen Sie die Ergebnisse der Berechnungen in die entsprechende Zeile auf dem Papier ein.
Der folgende Code führt alle diese Schritte aus.
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") 'Letzte Zeile und Spalte suchen 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
Dieser Code ermittelt die letzte Zeile Ihres Datenblatts und legt anschließend den Zellbereich (die Spalte mit den Verkaufspreisen) anhand der ersten und letzten Datenzeile fest. Danach durchläuft der Code jede dieser Zellen, berechnet die Steuer und schreibt das Ergebnis in Ihre neue Spalte (Spalte 5).
Fügen Sie den oben stehenden VBA-Code unterhalb des vorherigen Codes ein und führen Sie das Skript aus. Die Ergebnisse werden in Spalte E angezeigt.
Jedes Mal, wenn Sie nun Ihr Excel-Arbeitsblatt öffnen, lädt das Programm automatisch die aktuellsten Daten aus der CSV-Datei. Anschließend werden die Berechnungen durchgeführt und die Ergebnisse in das Arbeitsblatt eingetragen. Sie müssen nichts mehr manuell tun!
Berechnen Sie die Ergebnisse durch Drücken der Taste
Wenn Sie die Ausführung von Berechnungen lieber direkt steuern möchten, anstatt sie automatisch beim Öffnen des Arbeitsblatts auszuführen, können Sie stattdessen eine Steuerungsschaltfläche verwenden.
Steuerungsschaltflächen sind nützlich, wenn Sie die verwendeten Berechnungen steuern möchten. Was wäre beispielsweise im oben genannten Szenario, wenn Sie für eine Region einen Steuersatz von 5 % und für eine andere einen Steuersatz von 7 % verwenden möchten?
Sie können den gleichen CSV-Importcode automatisch ausführen lassen, den Steuerberechnungscode jedoch erst dann ausführen, wenn die entsprechende Schaltfläche gedrückt wird.
Wählen Sie mithilfe der oben genannten Datentabelle die Registerkarte aus. Entwickler:in / Unternehmen, und wählen Sie Insert Aus einer Gruppe Steuerelemente Wählen Sie im Menüband ein Steuerelement aus. Druckknopf Wählen Sie im Dropdown-Menü „ActiveX“ aus.
Zeichnen Sie den Druckknopf an einer beliebigen Stelle auf dem Papier, die sich außerhalb des Bereichs befindet, in dem Daten angezeigt werden.
Klicken Sie mit der rechten Maustaste auf den Druckknopf und wählen Sie aus Eigenschaften im VergleichÄndern Sie im Eigenschaftenfenster die Beschriftung so, wie sie dem Benutzer angezeigt werden soll. In diesem Fall könnte sie beispielsweise lauten: Berechnen Sie die 5% Steuer.
Dieser Text wird auf der Schaltfläche selbst angezeigt. Fenster schließen EigenschaftenDoppelklicken Sie auf dieselbe Schaltfläche. Dadurch öffnet sich ein Code-Editor-Fenster, und der Cursor befindet sich innerhalb der Funktion, die ausgeführt wird, wenn der Benutzer auf die Schaltfläche klickt.
Fügen Sie den Steuerberechnungscode aus dem obigen Abschnitt in diese Funktion ein und behalten Sie den Steuermultiplikator bei 0.05 bei. Vergessen Sie nicht, die folgenden zwei Zeilen einzufügen, um das aktive Tabellenblatt zu kennzeichnen.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
Wiederholen Sie nun den Vorgang und erstellen Sie eine zweite Schaltfläche. Beschriften Sie diese. Berechnung der 7%igen Steuer.
Doppelklicken Sie auf diese Schaltfläche und fügen Sie denselben Code ein, aber stellen Sie den Steuermultiplikator auf 0.07 ein.
Je nachdem, welchen Knopf Sie drücken, wird die Steuerspalte entsprechend berechnet.
Sobald Sie fertig sind, befinden sich beide Schaltflächen auf Ihrem Tabellenblatt. Jede Schaltfläche startet eine andere Steuerberechnung und zeigt unterschiedliche Ergebnisse in der Ergebnisspalte an.
Um dies zu testen, wählen Sie eine Liste aus. Entwickler, und wählen Sie Designmodus Aus der Gruppe der Steuerelemente im Menüband auswählen, die deaktiviert werden sollen DesignmodusDadurch werden die Schaltflächen aktiviert.
Probieren Sie aus, jede Schaltfläche anzuklicken, um zu sehen, wie sich die Spalte „Steuern“ in den Ergebnissen ändert.
Die Kontoergebnisse per E-Mail an jemanden senden
Was wäre, wenn Sie die Ergebnisse aus der Tabelle per E-Mail an jemanden senden möchten?
Sie können eine weitere Schaltfläche erstellen mit dem Namen E-Mail-Formular an den Chef Nach dem gleichen Verfahren wie oben beschrieben, wird der Code für diese Schaltfläche ein Excel-CDO-Objekt verwenden, um die SMTP-E-Mail-Einstellungen zu konfigurieren und die Ergebnisse in einem benutzerfreundlichen Format per E-Mail zu versenden.
Um diese Funktion zu aktivieren, müssen Sie Folgendes auswählen: Werkzeuge und Referenzen. Scrollen Sie nach unten zu Microsoft CDO für Windows 2000 BibliothekAktivieren Sie es und wählen Sie aus OK.
Es gibt drei Hauptabschnitte des Codes, die Sie erstellen müssen, um eine E-Mail zu versenden und Tabellenergebnisse einzufügen.
Die erste Maßnahme besteht darin, Variablen einzurichten, um Betreff, Absender- und Empfängeradresse sowie den E-Mail-Text zu speichern.
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 = "Gezahlte Steuern in diesem Quartal" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Im Folgenden finden Sie die Aufschlüsselung der in diesem Quartal gezahlten Umsatzsteuern."
Selbstverständlich muss der Text dynamisch sein und sich nach den Ergebnissen der Arbeit richten. Daher müssen Sie hier eine Schleife hinzufügen, die den Bereich durchläuft, die Daten extrahiert und jedes Mal eine Zeile in den Text schreibt.
Set StartCell = Range("A1"). vbCrLf For Each cell In rng strBody = strBody & vbCrLf strBody = strBody & "Wir haben verkauft " & Cells(rowCounter, 3).Value & " von " & Cells(rowCounter, 1).Value _ & " für " & Cells(rowCounter, 4).Value & " und Steuern in Höhe von " & Cells(rowCounter, 5).Value & " bezahlt." rowCounter = rowCounter + 1 Next cell
Im nächsten Abschnitt geht es um die Einrichtung von SMTP, damit Sie E-Mails über Ihren SMTP-Server versenden können. Wenn Sie Gmail verwenden, sind dies üblicherweise Ihre Gmail-E-Mail-Adresse, Ihr Gmail-Passwort und der Gmail-SMTP-Server (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@website.com" .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 Set .Configuration = CDO_Config End With
ersetzen email@website.com Das Passwort enthält Ihre Kontodaten.
Um die E-Mail zu versenden, geben Sie abschließend den folgenden Code ein.
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 Fehlerbehandlung: Wenn Err.Description <> "" Dann MsgBox Err.Description
ملاحظةWenn beim Ausführen dieses Codes ein Übertragungsfehler auftritt, blockiert Ihr Google-Konto wahrscheinlich die Ausführung von „weniger sicheren Apps“. Sie müssen [link/reference] besuchen. Einstellungsseite für weniger sichere Apps Aktivieren Sie diese Option.
Nach der Aktivierung wird Ihre E-Mail versendet. So sieht sie für den Empfänger Ihrer automatisch generierten E-Mail aus.
Wie Sie sehen, lässt sich mit Excel VBA vieles automatisieren. Probieren Sie die Codebeispiele aus diesem Artikel aus und erstellen Sie Ihre eigenen, individuellen VBA-Automatisierungen.
Kommentare sind geschlossen.