Andreas Unkelbach
Werbung


Sonntag, 29. Dezember 2013
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:
Grundliste mit Leerzeichen

Ü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:

Symbolleiste Ansicht - 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"
Nun ist neben der Registerkarte Ansicht auch eine Registerkarte "Entwicklertools" vorhanden.
Symbolleiste Entwicklertools

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".

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:

Aufzeichnung beenden auf Entwicklersymbolleiste

als auch in der allgemeinen Makrofunktion in der Symbolleiste Ansicht

Aufzeichnung beenden auf Ansichtsymbolleiste

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.

Excel - Makros verwalten

Im Beispiel hat das Makro nun die Bezeichnung Export und kann entweder direkt ausgeführt werden oder noch im VBA Editor bearbeitet werden.

VBA - Microsoft Visual Basic for Application Editor

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.

Ergebnis für CSV Export

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.

Steuerelement Schaltfläche einfügen

Hier kann nun eine entsprechende Schaltfläche gezeichnet werden und danach ein Makro dieser Schaltfläche zugewiesen werden.

Makro Schaltfläche zuweisen

Danach kann über diese Schaltfläche das entsprechende Makro direkt gestartet werden.

Über die rechte Maustaste kann der Text der Schaltfläche bearbeitet werden.

Text der Schaltfläche bearbeiten

Denkbar ist beispielsweise eine entsprechende Arbeitsanweisung für dieses Makro.

Beispiel Schaltfläche mit Text

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.

Dateityp mit Endung XLSM

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 Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

Ein Kommentar - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 17.6.2015 um 23:36 Uhr
Sehr hilfreich!


Kommentare und Anmerkungen

Für diesen Beitrag ist die Kommentarfunktion gesperrt. Sollten Sie eine Anmerkung oder Frage zu diesen Artikel haben freue ich mich über eine Rückmeldung per Mail. Meine Kontaktdaten finden Sie unter Kontakt aber auch am Ende dieser Seite. Neben Mail stehen hier auch verschiedene "soziale Netzwerke" zur Verfügung.



* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste