Sonntag, 29. Dezember 2013
14:45 Uhr
14:45 Uhr
Excel Umgang mit Makros und Visual Basic for Applications (VBA)
Dieser Artikel stellt anhand eines Beispiels eine Einführung in das Thema VBA und Makros (inklusive der persönlichen Makroarbeitsmappe) in Excel dar. Eine Einführung zum Thema Makros und VBA in der Datenbankverwaltung Access ist im Artikel "Dynamisches Importieren von Daten aus Excel nach Access unter Abfrage Speicherpfad per Makro und VBA" beschrieben.
Ausgangslage
In einen Bericht werden einzelne Zeilen ausgegeben, sofern ein Datensatz vorhanden ist, ansonsten bleibt die Zeile in Excel leer. Dieses funktioniert über eine Auswertung die im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche beschrieben wurde.
Hier wird im Tabellenblatt "Endtabelle" eine Vorlage für eine CSV Datei erstellt, die später in ein anderes System eingebunden werden soll.
Das Ergebnis sieht dabei wie folgt aus:
Über die Wenn Funktion werden hier nur Zeilen ausgegeben, wenn entsprechende Daten auch vorhanden sind. Im Beispiel sind daher die Zeilen 5 und 9 ohne Wert bzw. leer.
Bei einer Einspielung in ein anderes System würde der Einspielvorgang bei einer Leerzeile abgebrochen werden. Daher müssten die Leerzeilen aufwändig gelöscht werden.
Hier bietet Excel jedoch die Möglichkeit über Makros diesen Vorgang zu automatisieren.
Makro aufzeichnen
Die Makrofunktion von Excel ist an zwei Stellen versteckt. Zum einen findet sich diese in der Registerkarte Ansicht in der Gruppe Makros:
als auch in der sogenannten Entwicklerleiste.
Dieses Symbolleiste mit verschiedenen Symbolen zur Entwicklung von Makros und VBA Elementen kann wie folgt aktiviert werden:
Hier kann nun die Funktion "Makro aufzeichnen" gewählt werden.
Dieses funktioniert am einfachsten tatsächlich innerhalb der Symbolleiste "Entwicklertools" über die Funktion "Makro aufzeichnen".
Im erscheinenden Dialog kann eine Makroname und eine Beschreibung des Makros gewählt werden.
EXKURS: Persönliche Makroarbeitsmappe
Eine Besonderheit ist noch der Ort an dem das Makro gespeichert werden soll. In der Regel dürfte dieses die vorhandene Arbeitsmappe sein. Daher kann hier die Option "Diese Arbeitsmappe" bestehen bleiben.
Alternativ kann aber auch als Speicherort "Persönliche Arbeitsmappe" gewählt werden.
Die Makros in der persönlichen Arbeitsmappe stehen immer zur Verfügung und sind nicht an einer geöffneten Excelmappe gebunden.
Hierzu muss die Mappe jedoch einmalig eingerichtet werden. Dieses ist am einfachsten möglich indem ein "Dummy-Makro" (ein beliebiges Makro) aufgezeichnet wird und in der persönlichen Arbeitsmappe gespeichert wird.
Darauf werden unter C:\Users\Benutzername\AppData\Roaming\Microsoft\Excel\XLSTART die Datei PERSONAL.XLSB angelegt, welche beim Start von Excel für den entsprechenden Benutzername als ausgeblendete Arbeitsmappe angelegt und kann entweder direkt geöffnet werden oder über die Symbolleiste Ansicht in der Gruppe Fenster über die Funktion Einblenden angezeigt werden.
Ein in meinen Augen sehr sinnvolles Makro für die Persönliche Makroarbeitsmappe ist das Makro Excel Blattschutz aufheben, Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter oder auch die im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel beschriebene Makros, welche nicht in der Zieldatei gespeichert werden müssen aber sinnvollerweise immer einmal wieder angewendet werden können.
Nach der Auswahl der Funktion "Makro aufzeichnen" wird die Schaltfläche mit der Funktion "Aufzeichnung beenden" ausgetauscht.
Dieses gilt sowohl für die Entwicklersymbolleiste:
als auch in der allgemeinen Makrofunktion in der Symbolleiste Ansicht
Während die Makroaufzeichnung läuft können verschiedene Funktionen ausgewählt werden.
Im Beispiel wird das Tabellenblatt Endtabelle kopiert und die darin befindlichen Daten nach über die Spalte "BUKR" sortiert und als Export.CSV umbenannt.
Danach wird die Aufzeichnung über die Funktion "Aufzeichnung beendet" abgeschlossen.
Über die Schaltfläche Makros kann nun das Makro aufgerufen werden.
Im Beispiel hat das Makro nun die Bezeichnung Export und kann entweder direkt ausgeführt werden oder noch im VBA Editor bearbeitet werden.
Der entsprechende VBA Code für das Export Makro lautet dabei wie folgt:
Wird nun das Makro aufgerufen, wird direkt ein neues Blatt angefügt, welches von der Bezeichnung her den entsprechenden Buchungskreis mit in der Bezeichnung hat.
Als Ergebnis kann dann dieses Arbeitsblatt mit dieser Bezeichnung als CSV Datei gespeichert werden und entsprechend weiter verarbeitet werden.
Formularsteuerelemente einfügen
Um die Handhabung der Makros zu verbessern ist es nicht zwingend erforderlich die Makros über die Schaltfläche Makros zu starten sondern es besteht auch die Möglichkeit entsprechende Schaltflächen (oder auch Bilder) mit einen Makro zu verknüpfen.
Hierzu kann wiederum in der Symbolleiste "Entwicklertools" über die Schaltfläche Einfügen in der Gruppe "Steuerelemente" eine Schaltfläche eingefügt werden.
Hier kann nun eine entsprechende Schaltfläche gezeichnet werden und danach ein Makro dieser Schaltfläche zugewiesen werden.
Danach kann über diese Schaltfläche das entsprechende Makro direkt gestartet werden.
Über die rechte Maustaste kann der Text der Schaltfläche bearbeitet werden.
Denkbar ist beispielsweise eine entsprechende Arbeitsanweisung für dieses Makro.
Somit ist die Tabelle mittlerweile fertig.
Hinweis: Speichern Excel Arbeitsmappe mit Makros
Mit Einführung von Excel 2007 ist es erforderlich Excel-Arbeitsmappen mit Makros nicht mehr als XLS (beziehungsweise XSLX) zu speichern sondern als XLSM "Excel-Arbeitsmappe mit Makros".
Diese aus Sicherheitsgründen eingeführte Funktion bedarf daher, dass diese Mappe dann auch als XLSM Datei gespeichert wird. Entsprechend ist das Dateisymbol von Makroarbeitsmappen mit einen warnenden Ausrufezeichen versehen.
Die Kombination aus VBA und Makros ist eine tatsächliche Bereicherung von Excel und ermöglicht verschiedene Arbeitsschritte zu automatisieren aber auch Arbeitsmappen um weitere Funktionen zu erweitern.
Ein weiteres schönes Makro ist im Artikel "Urlaubsplaner und dynamischer Kalender mit Monatsansicht" beschrieben.
Darüberhinaus helfen diverse Makros auch beim Zusammenführen von Daten aus SAP nach Excel. Dieses ist im Artikel "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel" beschrieben.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
Ausgangslage
In einen Bericht werden einzelne Zeilen ausgegeben, sofern ein Datensatz vorhanden ist, ansonsten bleibt die Zeile in Excel leer. Dieses funktioniert über eine Auswertung die im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche beschrieben wurde.
Hier wird im Tabellenblatt "Endtabelle" eine Vorlage für eine CSV Datei erstellt, die später in ein anderes System eingebunden werden soll.
Das Ergebnis sieht dabei wie folgt aus:
Über die Wenn Funktion werden hier nur Zeilen ausgegeben, wenn entsprechende Daten auch vorhanden sind. Im Beispiel sind daher die Zeilen 5 und 9 ohne Wert bzw. leer.
Bei einer Einspielung in ein anderes System würde der Einspielvorgang bei einer Leerzeile abgebrochen werden. Daher müssten die Leerzeilen aufwändig gelöscht werden.
Hier bietet Excel jedoch die Möglichkeit über Makros diesen Vorgang zu automatisieren.
Makro aufzeichnen
Die Makrofunktion von Excel ist an zwei Stellen versteckt. Zum einen findet sich diese in der Registerkarte Ansicht in der Gruppe Makros:
als auch in der sogenannten Entwicklerleiste.
Dieses Symbolleiste mit verschiedenen Symbolen zur Entwicklung von Makros und VBA Elementen kann wie folgt aktiviert werden:
- Excel 2010
- Registerkarte Datei
- Optionen
- Menüband anpassen
- Unter der Auswahl Menüband anpassen
Hauptregisterkarten - Aktivieren des Hacken bei Entwicklertools
- Excel 2007
- Microsoft Office Schaltfläche(Windows Ball linke Ecke)
- Excel Optionen
- Häufig verwendet
- Aktivieren von "Entwicklerregisterkarte in der Multifunktionsleiste anzeigen"
Hier kann nun die Funktion "Makro aufzeichnen" gewählt werden.
Dieses funktioniert am einfachsten tatsächlich innerhalb der Symbolleiste "Entwicklertools" über die Funktion "Makro aufzeichnen".
Im erscheinenden Dialog kann eine Makroname und eine Beschreibung des Makros gewählt werden.
EXKURS: Persönliche Makroarbeitsmappe
Eine Besonderheit ist noch der Ort an dem das Makro gespeichert werden soll. In der Regel dürfte dieses die vorhandene Arbeitsmappe sein. Daher kann hier die Option "Diese Arbeitsmappe" bestehen bleiben.
Alternativ kann aber auch als Speicherort "Persönliche Arbeitsmappe" gewählt werden.
Die Makros in der persönlichen Arbeitsmappe stehen immer zur Verfügung und sind nicht an einer geöffneten Excelmappe gebunden.
Hierzu muss die Mappe jedoch einmalig eingerichtet werden. Dieses ist am einfachsten möglich indem ein "Dummy-Makro" (ein beliebiges Makro) aufgezeichnet wird und in der persönlichen Arbeitsmappe gespeichert wird.
Darauf werden unter C:\Users\Benutzername\AppData\Roaming\Microsoft\Excel\XLSTART die Datei PERSONAL.XLSB angelegt, welche beim Start von Excel für den entsprechenden Benutzername als ausgeblendete Arbeitsmappe angelegt und kann entweder direkt geöffnet werden oder über die Symbolleiste Ansicht in der Gruppe Fenster über die Funktion Einblenden angezeigt werden.
Ein in meinen Augen sehr sinnvolles Makro für die Persönliche Makroarbeitsmappe ist das Makro Excel Blattschutz aufheben, Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter oder auch die im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel beschriebene Makros, welche nicht in der Zieldatei gespeichert werden müssen aber sinnvollerweise immer einmal wieder angewendet werden können.
Nach der Auswahl der Funktion "Makro aufzeichnen" wird die Schaltfläche mit der Funktion "Aufzeichnung beenden" ausgetauscht.
Dieses gilt sowohl für die Entwicklersymbolleiste:
als auch in der allgemeinen Makrofunktion in der Symbolleiste Ansicht
Während die Makroaufzeichnung läuft können verschiedene Funktionen ausgewählt werden.
Im Beispiel wird das Tabellenblatt Endtabelle kopiert und die darin befindlichen Daten nach über die Spalte "BUKR" sortiert und als Export.CSV umbenannt.
Danach wird die Aufzeichnung über die Funktion "Aufzeichnung beendet" abgeschlossen.
Über die Schaltfläche Makros kann nun das Makro aufgerufen werden.
Im Beispiel hat das Makro nun die Bezeichnung Export und kann entweder direkt ausgeführt werden oder noch im VBA Editor bearbeitet werden.
Der entsprechende VBA Code für das Export Makro lautet dabei wie folgt:
- Sub Export()
' Dieses weist den Namen Export zu
Sheets("Endtabelle").Select
Sheets("Endtabelle").Copy After:=Sheets(4)
' Diese Anweisung selektiert das Blatt Endtabelle und kopiert es hinter das 4. Arbeitsblatt
Sheets("Endtabelle (2)").Select
Sheets("Endtabelle (2)").Name = "Export.csv"
' Nun wird die Kopie in Export.csv umbenannt
ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Add Key:=Range( _
"A2:A1840"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Export.csv").Sort
.SetRange Range("A1:G1840")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Nun werden die Zellen A1 bis G1840 nach der Spalte A ab Zeile 2 bis 1840 sortiert
' Soweit stammt der Code auch aus der Makroaufzeichnung. Nun wird dieser noch um eine Anweisung ergänzt, dass das Tabellenblatt unter Verwendung des Buchungskreises in der Spalte A umbenannt wird
ActiveSheet.Name = "Export_" & Range("A2") & ".csv"
' Die Bezeichnung wird aus der Zelle A2 des gerade sortierten Tabellenblatt übernommen
'Durch End Sub kann der Code dann abgeschlossen werden
End Sub
Wird nun das Makro aufgerufen, wird direkt ein neues Blatt angefügt, welches von der Bezeichnung her den entsprechenden Buchungskreis mit in der Bezeichnung hat.
Als Ergebnis kann dann dieses Arbeitsblatt mit dieser Bezeichnung als CSV Datei gespeichert werden und entsprechend weiter verarbeitet werden.
Formularsteuerelemente einfügen
Um die Handhabung der Makros zu verbessern ist es nicht zwingend erforderlich die Makros über die Schaltfläche Makros zu starten sondern es besteht auch die Möglichkeit entsprechende Schaltflächen (oder auch Bilder) mit einen Makro zu verknüpfen.
Hierzu kann wiederum in der Symbolleiste "Entwicklertools" über die Schaltfläche Einfügen in der Gruppe "Steuerelemente" eine Schaltfläche eingefügt werden.
Hier kann nun eine entsprechende Schaltfläche gezeichnet werden und danach ein Makro dieser Schaltfläche zugewiesen werden.
Danach kann über diese Schaltfläche das entsprechende Makro direkt gestartet werden.
Über die rechte Maustaste kann der Text der Schaltfläche bearbeitet werden.
Denkbar ist beispielsweise eine entsprechende Arbeitsanweisung für dieses Makro.
Somit ist die Tabelle mittlerweile fertig.
Hinweis: Speichern Excel Arbeitsmappe mit Makros
Mit Einführung von Excel 2007 ist es erforderlich Excel-Arbeitsmappen mit Makros nicht mehr als XLS (beziehungsweise XSLX) zu speichern sondern als XLSM "Excel-Arbeitsmappe mit Makros".
Diese aus Sicherheitsgründen eingeführte Funktion bedarf daher, dass diese Mappe dann auch als XLSM Datei gespeichert wird. Entsprechend ist das Dateisymbol von Makroarbeitsmappen mit einen warnenden Ausrufezeichen versehen.
Die Kombination aus VBA und Makros ist eine tatsächliche Bereicherung von Excel und ermöglicht verschiedene Arbeitsschritte zu automatisieren aber auch Arbeitsmappen um weitere Funktionen zu erweitern.
Ein weiteres schönes Makro ist im Artikel "Urlaubsplaner und dynamischer Kalender mit Monatsansicht" beschrieben.
Darüberhinaus helfen diverse Makros auch beim Zusammenführen von Daten aus SAP nach Excel. Dieses ist im Artikel "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel" beschrieben.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel Umgang mit Makros und Visual Basic for Applications (VBA)« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 29.12.2013, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=493 (Abgerufen am 24.1.2025)
Ein Kommentar - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn