Excel VBA Tutorial - Schreiben von Code in eine Tabelle mit Visual Basic

Einführung

Dies ist ein Tutorial zum Schreiben von Code in Excel-Tabellen mit Visual Basic für Applikationen (VBA).

Excel ist eines der beliebtesten Produkte von Microsoft. 2016 sagte der CEO von Microsoft: "Denken Sie an eine Welt ohne Excel. Das ist für mich einfach unmöglich." Nun, vielleicht kann die Welt ohne Excel nicht denken.

  • Im Jahr 1996 gab es über 30 Millionen Benutzer von Microsoft Excel (Quelle).
  • Heute gibt es schätzungsweise 750 Millionen Benutzer von Microsoft Excel. Das ist etwas mehr als die Bevölkerung Europas und 25x mehr Nutzer als 1996.

Wir sind eine große, glückliche Familie!

In diesem Lernprogramm erfahren Sie mehr über VBA und das Schreiben von Code in eine Excel-Tabelle mit Visual Basic.

Voraussetzungen

Sie benötigen keine Programmiererfahrung, um dieses Tutorial zu verstehen. Sie benötigen jedoch:

  • Grundlegende bis mittlere Kenntnisse in Microsoft Excel
  • Wenn Sie den VBA-Beispielen in diesem Artikel folgen möchten, benötigen Sie Zugriff auf Microsoft Excel, vorzugsweise die neueste Version (2019), aber Excel 2016 und Excel 2013 funktionieren einwandfrei.
  • Die Bereitschaft, neue Dinge auszuprobieren

Lernziele

Im Verlauf dieses Artikels lernen Sie:

  1. Was ist VBA?
  2. Warum würden Sie VBA verwenden
  3. So richten Sie in Excel das Schreiben von VBA ein
  4. So lösen Sie einige reale Probleme mit VBA

Wichtige Konzepte

Hier sind einige wichtige Konzepte, mit denen Sie vertraut sein sollten, um dieses Tutorial vollständig zu verstehen.

Objekte : Excel ist objektorientiert, dh alles ist ein Objekt - das Excel-Fenster, die Arbeitsmappe, ein Blatt, ein Diagramm, eine Zelle. Mit VBA können Benutzer Aktionen mit Objekten in Excel bearbeiten und ausführen.

Wenn Sie keine Erfahrung mit objektorientierter Programmierung haben und dies ein brandneues Konzept ist, nehmen Sie sich eine Sekunde Zeit, um dies zu berücksichtigen!

Prozeduren : Eine Prozedur ist ein Teil des VBA-Codes, der im Visual Basic-Editor geschrieben wurde und eine Aufgabe ausführt. Manchmal wird dies auch als Makro bezeichnet (mehr zu Makros weiter unten). Es gibt zwei Arten von Verfahren:

  • Unterprogramme: Eine Gruppe von VBA-Anweisungen, die eine oder mehrere Aktionen ausführen
  • Funktionen: Eine Gruppe von VBA-Anweisungen, die eine oder mehrere Aktionen ausführen und einen oder mehrere Werte zurückgeben

Hinweis: Sie können Funktionen innerhalb von Unterprogrammen ausführen. Du wirst es später sehen.

Makros : Wenn Sie längere Zeit mit dem Erlernen erweiterter Excel-Funktionen verbracht haben, sind Sie wahrscheinlich auf das Konzept eines „Makros“ gestoßen. Excel-Benutzer können Makros aufzeichnen, die aus Benutzerbefehlen / Tastenanschlägen / Klicks bestehen, und diese blitzschnell wiedergeben, um sich wiederholende Aufgaben auszuführen. Aufgezeichnete Makros generieren VBA-Code, den Sie dann untersuchen können. Es macht wirklich Spaß, ein einfaches Makro aufzunehmen und dann den VBA-Code zu betrachten.

Bitte beachten Sie, dass es manchmal einfacher und schneller ist, ein Makro aufzuzeichnen, als eine VBA-Prozedur von Hand zu codieren.

Zum Beispiel arbeiten Sie vielleicht im Projektmanagement. Einmal pro Woche müssen Sie einen exportierten Rohbericht aus Ihrem Projektmanagementsystem in einen schön formatierten, sauberen Bericht für die Führung umwandeln. Sie müssen die Namen der Projekte mit übermäßigem Budget in fettem rotem Text formatieren. Sie können die Formatierungsänderungen als Makro aufzeichnen und diese ausführen, wann immer Sie die Änderung vornehmen müssen.

Was ist VBA?

Visual Basic für Applikationen ist eine von Microsoft entwickelte Programmiersprache. Jedes Softwareprogramm in der Microsoft Office-Suite wird ohne zusätzliche Kosten mit der VBA-Sprache gebündelt. Mit VBA können Microsoft Office-Benutzer kleine Programme erstellen, die in Microsoft Office-Softwareprogrammen ausgeführt werden.

Stellen Sie sich VBA wie einen Pizzaofen in einem Restaurant vor. Excel ist das Restaurant. Die Küche ist mit handelsüblichen Standardgeräten wie großen Kühlschränken, Öfen und normalen alten Öfen ausgestattet - all dies sind die Standardfunktionen von Excel.

Aber was ist, wenn Sie Pizza aus dem Holzofen machen möchten? Das geht in einem handelsüblichen Backofen nicht. VBA ist der Pizzaofen.

Yum.

Warum VBA in Excel verwenden?

Weil Holzofenpizza das Beste ist!

Aber ernsthaft.

Viele Menschen verbringen im Rahmen ihrer Arbeit viel Zeit in Excel. Die Zeit in Excel bewegt sich ebenfalls anders. Abhängig von den Umständen können sich 10 Minuten in Excel wie eine Ewigkeit anfühlen, wenn Sie nicht in der Lage sind, das zu tun, was Sie benötigen, oder 10 Stunden können sehr schnell vergehen, wenn alles gut läuft. Wann sollten Sie sich fragen, warum um alles in der Welt verbringe ich 10 Stunden in Excel?

Manchmal sind diese Tage unvermeidlich. Wenn Sie jedoch täglich 8 bis 10 Stunden in Excel verbringen, um sich wiederholende Aufgaben zu erledigen, viele der gleichen Prozesse zu wiederholen, nach anderen Benutzern der Datei zu bereinigen oder sogar andere Dateien zu aktualisieren, nachdem Änderungen an der Excel-Datei vorgenommen wurden, Ein VBA-Verfahren könnte die Lösung für Sie sein.

Sie sollten die Verwendung von VBA in Betracht ziehen, wenn Sie:

  • Automatisieren Sie sich wiederholende Aufgaben
  • Erstellen Sie einfache Möglichkeiten für Benutzer, mit Ihren Tabellen zu interagieren
  • Bearbeiten Sie große Datenmengen

Einrichten zum Schreiben von VBA in Excel

Registerkarte "Entwickler"

Um VBA zu schreiben, müssen Sie der Multifunktionsleiste die Registerkarte Entwickler hinzufügen, damit die Multifunktionsleiste wie folgt angezeigt wird.

So fügen Sie der Multifunktionsleiste die Registerkarte "Entwickler" hinzu:

  1. Gehen Sie auf der Registerkarte Datei zu Optionen> Multifunktionsleiste anpassen.
  2. Aktivieren Sie unter Multifunktionsleiste anpassen und unter Hauptregisterkarten das Kontrollkästchen Entwickler.

Nachdem Sie die Registerkarte angezeigt haben, bleibt die Registerkarte Entwickler sichtbar, es sei denn, Sie deaktivieren das Kontrollkästchen oder müssen Excel neu installieren. Weitere Informationen finden Sie in der Microsoft-Hilfedokumentation.

VBA-Editor

Navigieren Sie zur Registerkarte "Entwickler" und klicken Sie auf die Schaltfläche "Visual Basic". Ein neues Fenster wird geöffnet - dies ist der Visual Basic-Editor. Für die Zwecke dieses Lernprogramms müssen Sie lediglich mit dem Projekt-Explorer-Bereich und dem Eigenschaften-Eigenschaften-Bereich vertraut sein.

Excel VBA-Beispiele

Lassen Sie uns zunächst eine Datei erstellen, in der wir herumspielen können.

  1. Öffnen Sie eine neue Excel-Datei
  2. Speichern Sie es als makrofähige Arbeitsmappe (. XLSM)
  3. Wählen Sie die Registerkarte Entwickler
  4. Öffnen Sie den VBA-Editor

Lassen Sie uns mit einigen einfachen Beispielen rocken und rollen, damit Sie mit Visual Basic Code in eine Tabelle schreiben können.

Beispiel 1: Anzeigen einer Nachricht, wenn Benutzer die Excel-Arbeitsmappe öffnen

Wählen Sie im VBA-Editor Einfügen -> Neues Modul

Schreiben Sie diesen Code in das Modulfenster (nicht einfügen!):

Sub Auto_Open ()

MsgBox ("Willkommen in der XYZ-Arbeitsmappe.")

End Sub

Speichern, schließen Sie die Arbeitsmappe und öffnen Sie die Arbeitsmappe erneut. Dieser Dialog sollte angezeigt werden.

Ta da!

Wie macht es das?

Abhängig von Ihrer Programmierkenntnis haben Sie möglicherweise einige Vermutungen. Es ist nicht besonders komplex, aber es ist ziemlich viel los:

  • Sub (kurz für "Subroutine"): Denken Sie von Anfang an daran, "eine Gruppe von VBA-Anweisungen, die eine oder mehrere Aktionen ausführen".
  • Auto_Open: Dies ist die spezifische Unterroutine. Ihr Code wird automatisch ausgeführt, wenn die Excel-Datei geöffnet wird. Dies ist das Ereignis, das die Prozedur auslöst. Auto_Open wird nur ausgeführt, wenn die Arbeitsmappe manuell geöffnet wird. Es wird nicht ausgeführt, wenn die Arbeitsmappe über Code aus einer anderen Arbeitsmappe geöffnet wird (Workbook_Open erledigt dies, erfahren Sie mehr über den Unterschied zwischen den beiden).
  • Standardmäßig ist der Zugriff eines Unterprogramms öffentlich. Dies bedeutet, dass jedes andere Modul diese Unterroutine verwenden kann. Alle Beispiele in diesem Tutorial sind öffentliche Unterprogramme. Bei Bedarf können Sie Unterprogramme als privat deklarieren. Dies kann in einigen Situationen erforderlich sein. Weitere Informationen zu Subroutinen-Zugriffsmodifikatoren.
  • msgBox: Dies ist eine Funktion - eine Gruppe von VBA-Anweisungen, die eine oder mehrere Aktionen ausführen und einen Wert zurückgeben. Der zurückgegebene Wert ist die Meldung "Willkommen in der XYZ-Arbeitsmappe".

Kurz gesagt, dies ist eine einfache Unterroutine, die eine Funktion enthält.

Wann könnte ich das benutzen?

Möglicherweise haben Sie eine sehr wichtige Datei, auf die nur selten zugegriffen wird (z. B. einmal im Quartal), die jedoch täglich von einem anderen VBA-Verfahren automatisch aktualisiert wird. Wenn darauf zugegriffen wird, wird es von vielen Personen in mehreren Abteilungen im gesamten Unternehmen durchgeführt.

  • Problem: Wenn Benutzer auf die Datei zugreifen, sind sie meistens verwirrt über den Zweck dieser Datei (warum sie existiert), wie sie so oft aktualisiert wird, wer sie verwaltet und wie sie mit ihr interagieren sollten. Neueinstellungen haben immer jede Menge Fragen, und Sie müssen diese Fragen immer und immer wieder beantworten.
  • Lösung: Erstellen Sie eine Benutzernachricht, die eine präzise Antwort auf jede dieser häufig beantworteten Fragen enthält.

Beispiele aus der realen Welt

  • Verwenden Sie die MsgBox-Funktion, um eine Meldung anzuzeigen, wenn ein Ereignis vorliegt: Der Benutzer schließt eine Excel-Arbeitsmappe, der Benutzer druckt, ein neues Blatt wird zur Arbeitsmappe hinzugefügt usw.
  • Verwenden Sie die MsgBox-Funktion, um eine Nachricht anzuzeigen, wenn ein Benutzer eine Bedingung erfüllen muss, bevor er eine Excel-Arbeitsmappe schließt
  • Verwenden Sie die InputBox-Funktion, um Informationen vom Benutzer abzurufen

Beispiel 2: Benutzer darf eine andere Prozedur ausführen

Wählen Sie im VBA-Editor Einfügen -> Neues Modul

Schreiben Sie diesen Code in das Modulfenster (nicht einfügen!):

Sub UserReportQuery ()

Benutzereingabe so lange dimmen

Antwort als Ganzzahl dimmen

UserInput = vbYesNo

Answer = MsgBox ("XYZ-Bericht verarbeiten?", UserInput)

Wenn Answer = vbYes, dann ProcessReport

End Sub

Sub ProcessReport ()

MsgBox ("Vielen Dank für die Bearbeitung des XYZ-Berichts.")

End Sub

Speichern und navigieren Sie zurück zur Registerkarte Entwickler in Excel und wählen Sie die Option "Schaltfläche". Klicken Sie auf eine Zelle und weisen Sie der Schaltfläche das UserReportQuery-Makro zu.

Klicken Sie nun auf die Schaltfläche. Diese Meldung sollte Folgendes anzeigen:

Klicken Sie auf "Ja" oder drücken Sie die Eingabetaste.

Noch einmal, Tada!

Bitte beachten Sie, dass das sekundäre Unterprogramm ProcessReport alles sein kann . Ich werde in Beispiel 3 weitere Möglichkeiten demonstrieren. Aber zuerst...

Wie macht es das?

Dieses Beispiel baut auf dem vorherigen Beispiel auf und enthält einige neue Elemente. Lassen Sie uns die neuen Dinge durchgehen:

  • Dim UserInput As Long: Dim ist die Abkürzung für "dimension" und ermöglicht das Deklarieren von Variablennamen. In diesem Fall ist UserInput der Variablenname und Long der Datentyp. Im Klartext bedeutet diese Zeile "Hier ist eine Variable namens" UserInput "und es ist ein langer Variablentyp."
  • Antwort als Ganzzahl dimmen: Deklariert eine andere Variable namens "Antwort" mit dem Datentyp "Ganzzahl". Erfahren Sie hier mehr über Datentypen.
  • UserInput = vbYesNo: Weist der Variablen einen Wert zu. In diesem Fall vbYesNo, das die Schaltflächen Ja und Nein anzeigt. Es gibt viele Schaltflächentypen. Weitere Informationen finden Sie hier.
  • Answer = MsgBox ("XYZ-Bericht verarbeiten?", UserInput): Weist den Wert der Variablen Answer als MsgBox-Funktion und der UserInput-Variablen zu. Ja, eine Variable innerhalb einer Variablen.
  • Wenn Answer = vbYes Then ProcessReport: Dies ist eine "If-Anweisung", eine bedingte Anweisung, mit der wir sagen können, ob x wahr ist, dann tun Sie y. Wenn der Benutzer in diesem Fall "Ja" ausgewählt hat, führen Sie die Unterroutine ProcessReport aus.

Wann könnte ich das benutzen?

Dies könnte auf viele, viele Arten verwendet werden. Der Wert und die Vielseitigkeit dieser Funktionalität wird mehr durch die Funktionsweise der sekundären Unterroutine bestimmt.

Möglicherweise haben Sie eine Datei, mit der drei verschiedene wöchentliche Berichte erstellt werden. Diese Berichte werden auf dramatisch unterschiedliche Weise formatiert.

  • Problem: Jedes Mal, wenn einer dieser Berichte generiert werden muss, öffnet ein Benutzer die Datei und ändert Formatierung und Diagramme. und so weiter und so fort. Diese Datei wird mindestens dreimal pro Woche ausgiebig bearbeitet und dauert bei jeder Bearbeitung mindestens 30 Minuten.
  • Lösung: Erstellen Sie 1 Schaltfläche pro Berichtstyp, wodurch die erforderlichen Komponenten der Berichte automatisch neu formatiert und die erforderlichen Diagramme generiert werden.

Beispiele aus der realen Welt

  • Erstellen Sie ein Dialogfeld, in dem der Benutzer bestimmte Informationen automatisch über mehrere Blätter hinweg auffüllen kann
  • Verwenden Sie die InputBox-Funktion, um Informationen vom Benutzer abzurufen, die dann über mehrere Blätter verteilt werden

Beispiel 3: Hinzufügen von Zahlen zu einem Bereich mit einer For-Next-Schleife

For-Schleifen sind sehr nützlich, wenn Sie sich wiederholende Aufgaben für einen bestimmten Wertebereich ausführen müssen - Arrays oder Zellbereiche. Im Klartext sagt eine Schleife "für jedes x, mach y".

Wählen Sie im VBA-Editor Einfügen -> Neues Modul

Schreiben Sie diesen Code in das Modulfenster (nicht einfügen!):

Sub LoopExample ()

Dim X As Integer

Für X = 1 bis 100

Bereich ("A" & X) .Wert = X.

Weiter X.

End Sub

Speichern und navigieren Sie zurück zur Registerkarte Entwickler in Excel und wählen Sie die Schaltfläche Makros. Führen Sie das LoopExample-Makro aus.

Dies sollte passieren:

Usw. bis zur 100. Reihe.

Wie macht es das?

  • Dim X As Integer: Deklariert die Variable X als Datentyp von Integer.
  • Für X = 1 bis 100: Dies ist der Beginn der For-Schleife. Einfach ausgedrückt, weist es die Schleife an, sich so lange zu wiederholen, bis X = 100. X ist der Zähler . Die Schleife wird so lange ausgeführt, bis X = 100 ist, ein letztes Mal ausgeführt und dann gestoppt.
  • Bereich ("A" & X) .Wert = X: Dies gibt den Bereich der Schleife an und was in diesen Bereich eingefügt werden soll. Da anfangs X = 1 ist, ist die erste Zelle A1. An diesem Punkt setzt die Schleife X in diese Zelle.
  • Nächstes X: Dies weist die Schleife an, erneut ausgeführt zu werden

Wann könnte ich das benutzen?

Die For-Next-Schleife ist eine der leistungsstärksten Funktionen von VBA. Es gibt zahlreiche mögliche Anwendungsfälle. Dies ist ein komplexeres Beispiel, das mehrere Logikebenen erfordern würde, aber die Welt der Möglichkeiten in For-Next-Schleifen kommuniziert.

Möglicherweise haben Sie eine Liste aller in Ihrer Bäckerei verkauften Produkte in Spalte A, die Art des Produkts in Spalte B (Kuchen, Donuts oder Muffins), die Kosten für Zutaten in Spalte C und die durchschnittlichen Marktkosten für jeden Produkttyp in ein weiteres Blatt.

Sie müssen herausfinden, wie hoch der Verkaufspreis für jedes Produkt sein sollte. Sie denken, es sollten die Kosten für Zutaten plus 20% sein, aber wenn möglich auch 1,2% unter dem Marktdurchschnitt. Mit einer For-Next-Schleife können Sie diese Art der Berechnung durchführen.

Beispiele aus der realen Welt

  • Verwenden Sie eine Schleife mit einer verschachtelten if-Anweisung, um einem separaten Array nur dann bestimmte Werte hinzuzufügen, wenn sie bestimmte Bedingungen erfüllen
  • Führen Sie mathematische Berechnungen für jeden Wert in einem Bereich durch, z. B. berechnen Sie zusätzliche Gebühren und addieren Sie diese zum Wert
  • Durchlaufen Sie jedes Zeichen in einer Zeichenfolge und extrahieren Sie alle Zahlen
  • Wählen Sie zufällig eine Anzahl von Werten aus einem Array aus

Fazit

Nachdem wir über Pizza und Muffins gesprochen haben und wie man VBA-Code in Excel-Tabellen schreibt, machen wir eine Lernprüfung. Überprüfen Sie, ob Sie diese Fragen beantworten können.

  • Was ist VBA?
  • Wie richte ich mich ein, um VBA in Excel zu verwenden?
  • Warum und wann würden Sie VBA verwenden?
  • Welche Probleme könnte ich mit VBA lösen?

Wenn Sie eine gute Vorstellung davon haben, wie Sie diese Fragen beantworten können, war dies erfolgreich.

Egal, ob Sie ein gelegentlicher Benutzer oder ein Hauptbenutzer sind, ich hoffe, dieses Tutorial enthält nützliche Informationen darüber, was mit nur ein wenig Code in Ihren Excel-Tabellen erreicht werden kann.

Viel Spaß beim Codieren!

Lernmittel

  • Excel VBA-Programmierung für Dummies, John Walkenbach
  • Erste Schritte mit VBA, Microsoft Documentation
  • VBA in Excel lernen, Lynda

Ein bisschen über mich

Ich bin Chloe Tucker, eine Künstlerin und Entwicklerin in Portland, Oregon. Als ehemaliger Pädagoge bin ich ständig auf der Suche nach der Schnittstelle zwischen Lernen und Lehren oder Technologie und Kunst. Kontaktieren Sie mich auf Twitter @_chloetucker und besuchen Sie meine Website unter chloe.dev.