Wenn Sie gerade erst mit VBA beginnen, sollten Sie zunächst unsere VBA-Leitfaden für Anfänger studieren. Wenn Sie jedoch ein erfahrener VBA-Experte sind und nach erweiterten Funktionen für VBA in Excel suchen, lesen Sie weiter.
Die Möglichkeit, VBA-Codierung in Excel zu verwenden, eröffnet eine ganze Welt der Automatisierung. Sie können Berechnungen in Excel, Drucktasten automatisieren und sogar E-Mails senden. Es gibt mehr Möglichkeiten, um Ihre tägliche Arbeit mit VBA zu automatisieren, als Sie vielleicht gedacht haben.
Erweitertes VBA-Handbuch für Microsoft Excel
Das Hauptziel beim Schreiben von VBA-Code in Excel besteht darin, Informationen zu extrahieren Führen Sie in einer Kalkulationstabelle eine Reihe von Berechnungen durch und schreiben Sie die Ergebnisse in die Kalkulationstabelle zurück.
Im Folgenden werden die häufigsten Verwendungszwecke von VBA in Excel aufgeführt.
Mit diesen drei Beispielen sollten Sie Folgendes tun Sie können eine Vielzahl von fortgeschrittenem Excel-VBA-Code schreiben.
Daten importieren und Berechnungen ausführen
Eine der häufigsten Aufgaben, für die Excel verwendet wird führt Berechnungen für Daten durch, die außerhalb von Excel existieren. Wenn Sie VBA nicht verwenden, müssen Sie die Daten manuell importieren, die Berechnungen ausführen und diese Werte auf einem anderen Blatt oder in einem anderen Bericht ausgeben.
In_content_1 all: [300x250] / dfp : [640 x 360]->Mit VBA können Sie den gesamten Prozess automatisieren. Wenn Sie beispielsweise jeden Montag eine neue CSV-Datei in ein Verzeichnis auf Ihrem Computer heruntergeladen haben, können Sie Ihren VBA-Code so konfigurieren, dass er beim ersten Öffnen Ihrer Tabelle am Dienstagmorgen ausgeführt wird.
Der folgende Importcode wird verwendet Führen Sie die CSV-Datei aus und importieren Sie sie in Ihre Excel-Tabelle.
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
Öffnen Sie das Excel VBA-Bearbeitungstool und wählen Sie das Sheet1-Objekt aus. Wählen Sie in den Dropdown-Feldern für Objekte und Methoden die Optionen Arbeitsblattund Aktivierenaus. Dadurch wird der Code jedes Mal ausgeführt, wenn Sie die Tabelle öffnen.
Dadurch wird eine Sub Worksheet_Activate ()-Funktion erstellt. Fügen Sie den obigen Code in diese Funktion ein.
Hiermit wird das aktive Arbeitsblatt auf Sheet1gesetzt, das Arbeitsblatt gelöscht, eine Verbindung mit der Datei unter Verwendung des Dateipfads hergestellt, den Sie mit der Variablen strFiledefiniert haben, und anschließend mit Mit derSchleife werden alle Zeilen in der Datei durchlaufen und die Daten beginnend mit Zelle A1 in das Arbeitsblatt eingefügt.
Wenn Sie diesen Code ausführen, werden die CSV-Dateidaten angezeigt wird in Ihr leeres Arbeitsblatt in Sheet1importiert.
Der Import ist nur der erste Schritt . Als Nächstes möchten Sie eine neue Überschrift für die Spalte erstellen, die Ihre Berechnungsergebnisse enthält. In diesem Beispiel möchten Sie beispielsweise die 5% igen Steuern berechnen, die für den Verkauf jedes Artikels gezahlt werden.
Die Reihenfolge der Aktionen, in der der Code ausgeführt werden soll, lautet:
Mit dem folgenden Code werden alle diese Schritte ausgeführt.
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
Mit diesem Code wird die letzte Zeile gefunden Klicken Sie in Ihrem Datenblatt auf und legen Sie dann den Zellenbereich (die Spalte mit den Verkaufspreisen) entsprechend der ersten und letzten Datenzeile fest. Anschließend durchläuft der Code 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 sehen die Ergebnisse in Spalte E.
Jetzt wird jedes Mal, wenn Sie Ihr Excel-Arbeitsblatt öffnen, automatisch die aktuellste Kopie der Daten aus der CSV-Datei abgerufen. Anschließend werden die Berechnungen ausgeführt und die Ergebnisse auf das Blatt geschrieben. Sie müssen nichts mehr manuell tun!
Ergebnisse aus Tastendruck berechnen
Wenn Sie die Ausführung von Berechnungen direkter steuern möchten Anstatt automatisch ausgeführt zu werden, wenn das Blatt geöffnet wird, können Sie stattdessen eine Steuerschaltfläche verwenden.
Steuerschaltflächen sind nützlich, wenn Sie steuern möchten, welche Berechnungen verwendet werden. Wie wäre es beispielsweise in diesem Fall, wenn Sie einen Steuersatz von 5% für eine Region und einen Steuersatz von 7% für eine andere Region verwenden möchten?
Sie könnten zulassen, dass derselbe CSV-Importcode verwendet wird Wird automatisch ausgeführt, aber der Steuerberechnungscode bleibt erhalten, wenn Sie auf die entsprechende Schaltfläche klicken.
Wählen Sie auf der gleichen Tabelle wie oben den Tab Entwickleraus und wählen Sie Einfügenaus der Gruppe Steuerelementeim Menüband. Wählen Sie im Dropdown-Menü die Drucktaste ActiveX-Steuerelement.
Zeichnen Sie die Drucktaste auf einen beliebigen Teil des Blatts, von dem aus keine Daten gespeichert werden sollen.
Klicken Sie mit der rechten Maustaste auf die Schaltfläche und wählen Sie Eigenschaften. Ändern Sie im Eigenschaftenfenster die Beschriftung so, wie Sie sie dem Benutzer anzeigen möchten. In diesem Fall könnte es 5% MwSt. Berechnensein.
Dieser Text wird auf dem Druckknopf selbst angezeigt. Schließen Sie das Fenster Eigenschaftenund doppelklicken Sie auf die Drucktaste. Dadurch wird das Code-Editor-Fenster geöffnet und der Cursor befindet sich innerhalb der Funktion, die ausgeführt wird, wenn der Benutzer die Taste 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 As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Wiederholen Sie nun den Vorgang erneut und erstellen Sie einen zweiten Druckknopf. Machen Sie die Beschriftung Berechnen Sie 7% Steuer.
Doppelklicken Sie auf diese Schaltfläche, und fügen Sie denselben Code ein. Nehmen Sie jedoch den Steuermultiplikator auf 0,07.
Je nachdem, welche Schaltfläche Sie drücken, wird nun die Steuerspalte angezeigt entsprechend berechnet werden.
Sobald Sie fertig sind, haben Sie beide Drucktasten auf Ihrem Blatt. Jeder von ihnen leitet eine andere Steuerberechnung ein und schreibt unterschiedliche Ergebnisse in die Ergebnisspalte.
Wählen Sie dazu das Menü Entwicklerund anschließend Entwurfsmodusaus der Gruppe Steuerelemente in der Multifunktionsleiste aus, um den Entwurfsmodus. Dadurch werden die Drucktasten aktiviert.
Wählen Sie die einzelnen Schaltflächen aus, um zu sehen, wie sich die Ergebnisspalte "Steuern" ändert.
E-Mail-Berechnungsergebnisse an jemanden
Was Möchten Sie die Ergebnisse in der Tabelle per E-Mail an eine andere Person senden?
Sie können auf die gleiche Weise wie oben eine weitere Schaltfläche mit dem Namen E-Mail-Tabelle an Boss sendenerstellen. Der Code für diese Schaltfläche umfasst die Verwendung des Excel-CDO-Objekts zum Konfigurieren der SMTP-E-Mail-Einstellungen und das Versenden der Ergebnisse per E-Mail in einem vom Benutzer lesbaren Format.
Um diese Funktion zu aktivieren, müssen Sie Extras und auswählen ReferenzenScrollen Sie zu Microsoft CDO für Windows 2000-Bibliothek, aktivieren Sie sie und wählen Sie OK.
Der Code, den Sie zum Versenden einer E-Mail und zum Einbetten von Tabellenergebnissen erstellen müssen, besteht aus drei Hauptabschnitten.
Der erste Schritt ist das Einrichten von Variablen für die Speicherung Betreff, An und Von-Adressen und E-Mail-Text.
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."
Natürlich muss der Text dynamisch sein, je nachdem, welche Ergebnisse vorliegen Im Arbeitsblatt müssen Sie also eine Schleife einfügen, die den Bereich durchläuft, die Daten extrahiert und jeweils eine Zeile in den Textkörper schreibt.
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
Im nächsten Abschnitt werden die SMTP-Einstellungen festgelegt, damit Sie E-Mails über Ihren SMTP-Server senden können. Wenn Sie Google Mail verwenden, sind dies in der Regel Ihre Google Mail-E-Mail-Adresse, Ihr Google Mail-Passwort und der Google Mail-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 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
Ersetzen [email protected] und Passwort mit Ihren eigenen Kontodaten.
Geben Sie abschließend den folgenden Code ein, um den E-Mail-Versand zu starten.
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
Hinweis: Wenn beim Versuch, diesen Code auszuführen, ein Transportfehler auftritt, kann dies daran liegen, dass Ihr Google-Konto die Ausführung von "weniger sicheren Apps" blockiert. Sie müssen die weniger sichere Apps Einstellungsseite besuchen und diese Funktion aktivieren.
Danach wird Ihre E-Mail gesendet. So sieht es für die Person aus, die Ihre automatisch generierte Ergebnis-E-Mail erhält.
Wie Sie sehen, können Sie mit Excel VBA eine Menge automatisieren. Probieren Sie die in diesem Artikel beschriebenen Codefragmente aus und erstellen Sie Ihre eigenen einzigartigen VBA-Automatisierungen.