Ein fortgeschrittener VBA-Leitfaden für MS Excel: Automatisierung meistern

Wenn Sie gerade erst mit VBA anfangen, sollten Sie zunächst den VBA-Einführungsleitfaden studieren . Falls Sie jedoch bereits Erfahrung mit VBA haben und nach fortgeschritteneren Anwendungsbeispielen für VBA in Excel suchen, 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-Menüs „Objekt“ und „Methode“ die Option „Arbeitsblatt“ und anschließend „Aktivieren“ . Dadurch wird der Code bei jedem Öffnen der Tabelle ausgeführt.

Dadurch wird eine Subfunktion Worksheet_Activate() erstellt . Fügen Sie den obigen Code in diese Funktion ein.

Dadurch wird das aktive Arbeitsblatt auf Sheet1 gesetzt , das Blatt wird durchsucht, eine Verbindung zur Datei wird über den mit der Variablen strFile angegebenen Dateipfad hergestellt , und anschließend durchläuft die With -Schleife jede Zeile der Datei und fügt die Daten ab Zelle A1 in das Arbeitsblatt ein.

Wenn Sie diesen Code ausführen, werden Sie sehen, dass die Daten aus der CSV-Datei in Ihre leere Tabelle, in Blatt1 , importiert werden.

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:

1. Erstellen Sie eine neue Ergebnisspalte mit dem Namen Steuern.
2. In einer Spalte wiederholen Einheiten verkauft Und berechnen Sie die Umsatzsteuer.
3. 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.

Öffnen Sie dieselbe Tabelle wie oben erwähnt, wählen Sie die Registerkarte „Entwicklertools “ und anschließend in der Gruppe „Steuerelemente “ des Menübands die Option „Einfügen “. Wählen Sie im Dropdown-Menü das ActiveX -Schaltflächensteuerelement 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 die Schaltfläche und wählen Sie „Eigenschaften“ . Ändern Sie im Eigenschaftenfenster die Beschriftung in den Text, der dem Benutzer angezeigt werden soll. In diesem Fall könnte es „ 5 % Steuer berechnen“ sein.

Dieser Text wird direkt auf der Schaltfläche angezeigt. Schließen Sie das Eigenschaftenfenster und doppelklicken Sie auf die Schaltfläche. Dadurch öffnet sich ein Code-Editor-Fenster, und der Cursor befindet sich in der Funktion, die beim Klicken auf die Schaltfläche ausgeführt wird.

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 mit „ 7% Steuerberechnung“.

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 im Menü „Entwicklertools“ die Option „Entwurfsmodus“ aus der Gruppe der Steuerelemente im Menüband, um den Entwurfsmodus zu deaktivieren . Dadurch 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 mit der oben beschriebenen Vorgehensweise eine weitere Schaltfläche namens „Tabelle an Chef senden“ erstellen . Der Code für diese Schaltfläche verwendet ein Excel-CDO-Objekt, 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, wählen Sie „Tools und Verweise“ . Scrollen Sie nach unten zu „Microsoft CDO für Windows 2000 Bibliothek“ , aktivieren Sie sie und wählen Sie „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 Sie email@website.com und password durch Ihre eigenen Zugangsdaten.

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

Hinweis : Sollte beim Ausführen dieses Codes ein Übertragungsfehler auftreten, blockiert Ihr Google-Konto wahrscheinlich die Option „Weniger sichere Apps“. Sie müssen die Einstellungsseite für weniger sichere Apps aufrufen und diese Option aktivieren.

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.