Heute begrüße ich in den Gastbeiträgen den Fachbuchautor Ernst Fischer.

Excel-Automatisierung

MS-Excel, 1987 erstmals ausgeliefert, nach 10 Jahren totgesagt, weil SAP und ähnliche alles abdecken sollen, erfreut sich nach nunmehr weiteren zwei Jahrzehnten allgemeiner Beliebtheit und Unentbehrlichkeit. In der Praxis fristet es gleichwohl ein Schattendasein – gemessen an den Möglichkeiten.

Dies hat mehrere Gründe:

Die etablierte Softwareindustrie weiß sich zu verkaufen und trifft in der Praxis auf emotionale Entscheidungsvoraussetzungen. Dagegen setze ich meine Billig-Vision: Preisgünstige Standardlösungen für Standardaufgaben (Buchhaltung etc.) und alles Spezielle mit Excel. Einige Controlling-Experten, die ich als solche erkenne, stimmen mir zu.
Die Zielgruppe für Excel sind nicht die Tüftler, sondern die in offener Tabellenarbeit mit den im Bildschirm angeordneten Werkzeugen Arbeitenden. Damit landen sie alsbald in der bekannten Excel-Sackgasse: Alles ist so komplex, keiner kennt sich richtig aus, und wenn einer eine Formel löscht, bricht alles zusammen.
Excel-Bücher und Internet-Hilfen gibt es zuhauf, mit vielen Tipps zur einfachen Tabellenarbeit. Praktische Lösungsbeispiele für größere Datenmengen sind selten oder für den Einstieg eine Zumutung, weil von Programmierern für Programmierer geschrieben.

Excel bietet die große Möglichkeit, ohne besondere Programmierkenntnisse Programme zu entwickeln. Der Nutzen besteht in der Individualisierung, der flexiblen Änderbarkeit und in einer enormen Arbeitserleichterung bei Wiederholungsarbeiten, dem größten Teil im Bereich Statistik und Kalkulation. Excel ist ein fertiges Rechenprogramm mit integriertem Automatisierungswerkzeug. Deshalb kann bei der Automatisierung – anders als in anderen Programmierungen – die Excel-Funktionalität voll genutzt werden. Die Programmierung reduziert sich auf das Anlegen von Formeln, Schaltflächen und Makros.

Aufgrund der Erfahrung, dass der Sprung in die Intensivierung – der Einstieg in die Makrosprache VBA – in der Praxis eine große Hürde darstellt, sind nachfolgend die ersten Schritte zur Automatisierung (Excel 2010) kurz beschrieben. Weitergehende Anleitungen finden Sie in 9 Büchern, die ich bislang zum Themenbereich „Excel im Controlling“ veröffentlicht habe.

Die Automatisierung besteht aus 3 Teilen:

Eigene Bedieneroberfläche mit bunten Schaltflächen
Formeldepot
VBA-Makros

Eine automatisierte Mausklicklösung wird ausschließlich über individuelle Schaltflächen bedient. Die vielen im Bildschirm angeordneten Excel-Standardinstrumente kommen nicht mehr zum Einsatz und könnten eliminiert werden. Dies hat sich in der Praxis jedoch nicht bewährt, weil Excel seine Stärken nur in der offenen Nutzung voll ausspielen kann. Lassen Sie Excel wie es ist, und betrachten Sie die Automatisierung als Weiterentwicklung. Offen heißt nicht ungeschützt. In der Praxis genügen jedoch einige einfache Schutzroutinen.

Eigene Bedieneroberfläche:

Schaltflächen-Einrichtung:

Legen Sie oben im Bildschirm ein Rahmenelement an: Datei – Optionen – Symbolleiste für den Schnellzugriff – Alle Befehle – Rechteck – Hinzufügen – OK. Das Rechteck ist dann oben im Bildschirm angeordnet.

Schaltflächen-Anordnung:

Um das oben angeordnete Rechteck auf eine Zelle zu ziehen, klicken Sie das Symbol mit der linken Maustaste an, lassen die Maustaste los, gehen mit der linken Maustaste in die Zelle und ziehen den Rahmen auf die Zelle.

Schaltflächen-Modifizierung:

Variante 1: Text und Farbe in der Zelle:

Mit der rechten Maustaste in den Rahmen klicken und Form formatieren – Füllung – Keine Füllung – Linienfarbe – Keine Linie auswählen. Die Schaltfläche liegt unsichtbar auf der mit Text und Musterfarbe bestückten Zelle und kann durch Anklicken mit der rechten Maustaste modifiziert, kopiert und mit einer Makrozuweisung versehen werden. Die Größe der Darstellung unter der Schaltfläche wird durch Spaltenbreite und Zeilenhöhe bestimmt.

Variante 2: Text und Farbe in der Schaltfläche:

Anstelle der Texteingabe in eine Zelle kann der Text auch in der Schaltfläche stehen: rechte Maustaste in die Schaltfläche – Text bearbeiten, und die Füllung und den Text farblich gestalten: Füllung – Einfarbige Füllung bzw. Text bearbeiten. Die Größe wird durch Anklicken und anschließendes Ziehen am Rand oder über die rechts oben im Bildschirm angezeigten Größenzahlen festgelegt. Über Eigenschaften kann festgelegt werden, dass das Element von der Zellposition und Größe unabhängig sein soll. Dann bleibt es erhalten, wenn die Spaltenbreite oder Zeilenhöhe in der Tabelle geändert wird. Außerdem kann festgelegt werden, ob das Element gedruckt werden soll.

Welche der beiden Varianten Sie bevorzugen, ist freigestellt. Es gibt auch noch zahlreiche weitere Elemente, die als Schaltfläche zum Einsatz kommen können. Achten Sie darauf, nur Elemente auszuwählen, die eine Makrozuweisung gestatten.

Vorschlag:

Variante 1 für mehrere gleich große Schaltflächen unter- oder nebeneinander.

Variante 2 für alle Zurück-Schaltflächen, oben rechts in der Tabelle angeordnet.

Formeldepot:

Legen Sie für jeden in sich geschlossenen Aufgabenbereich einen Ordner an. Erstellen Sie darin eine Startdatei mit einem aussagefähigen Dateinamen, z. B. 1StartKundenstatistik.xlsm an. Aus dieser Startdatei wird die Anwendung über Schaltflächen bedient.

Legen Sie in jeder Datei Ihrer Anwendung ein Registerblatt mit dem Namen Formel an.

Hinterlegen Sie in diesem Blatt alle Formeln, zumindest die mit relativen Bezügen (mitlaufende Formeln).

Die Formeln werden makrogesteuert in die Tabellen gesetzt und gleich in Werte umgeformt, wenn eine Auswertung angeklickt wird. Denn wenn alles fehlerfrei läuft, interessieren nur noch die Resultate. Großflächig mit Formeln bestückte und gespeicherte Tabellen sind Ballast, sobald es um größere Datenmengen geht, und außerdem eine Fehlerursache, wenn unabsichtlich Formeln gelöscht werden – ein Problem aus der Excel-Kinderstube, auf das alle Excel-Gegner so gerne verweisen. Deshalb das Formeldepot (mit Blattschutz).

Allgemeine Hinweise:

Vermeiden Sie Dateiverknüpfungen, arbeiten Sie stattdessen im Kopiermodus. Dateiverknüpfungen erkennen Sie über Daten – Verknüpfungen bearbeiten. Falls sich unbeabsichtigt eine Verknüpfung einschleicht: Verknüpfung löschen und speichern.

Reservieren Sie in der Startdatei im Blatt Formel folgende Zellen:

Eine Zelle für die Pfad(Ordner)-Erzeugung (Makrobefehl s. nächste Seite)., sowie einen Zellblock für die Dateinamen der aus der Startdatei aufzurufenden Folgedateien. Verknüpfen Sie diese Dateinamen auf die Zelle mit dem Pfadnamen. Dann funktioniert Ihre Anwendung auch, wenn Sie sich in einem anderen Ordner befinden. Dies ist von Bedeutung, wenn Sie für das Öffnen der Startdatei ein Icon in der Windows-Ebene anlegen (nur dann ist das Mausklicksystem vollkommen, andernfalls muss die Startdatei in Excel über Datei-öffnen oder im Explorer gesucht werden).

Eine ausführliche Beschreibung zum Aufbau eines Mausklicksystems finden Sie im Buch „Excel-Automatisierung“, ISBN 978-3-7322-3847-7

Eine Auflistung wichtiger Makrobefehle und nicht aufgezeichneter Makrobefehle im Buch „Von Excel das Beste“, ISBN 978-3-8423-2605-7

VBA-Makros:

Stellen Sie über Datei – Optionen – Sicherheitscenter – Einstellungen für das Sicherheitscenter – Einstellungen für Makros – Alle Makros aktivieren ein (Sie lesen richtig: wir empfehlen die nicht empfohlene Einstellung, die noch aus der Zeit ohne Virenschutz/Firewall stammt).

Öffnen Sie eine neue Arbeitsmappe. Diese wird mehrere Blätter enthalten, z. B. Tabelle1 und Tabelle2. Tabelle1 ist aktiv.

Starten Sie über Ansicht – Makros – Makro aufzeichnen – OK die Makroaufzeichnung.

Aktivieren Sie mit der Maus das Blatt Tabelle2.

Beenden Sie die Makroaufzeichnung mit Aufzeichnung beenden.

Starten Sie erneut über Ansicht – Makros – Makro aufzeichnen – OK die Makroaufzeichnung.

Aktivieren Sie mit der Maus das Blatt Tabelle1.

Beenden Sie die Makroaufzeichnung mit Aufzeichnung beenden.

Legen Sie im Blatt Tabelle1 eine Schaltfläche mit dem Text Auswertung an.

Klicken Sie mit der rechten Maustaste in die Schaltfläche und weisen Sie dieser über Makro zuweisen – Linke Maustaste auf Makro1 – OK das Makro1 zu.

Legen Sie im Blatt Tabelle2 eine Schaltfläche mit dem Text Zurück an.

Klicken Sie mit der rechten Maustaste in die Schaltfläche und weisen Sie dieser über Makro zuweisen – Linke Maustaste auf Makro2 – OK das Makro2 zu.

Nun haben Sie ein Mausklicksystem aufgebaut, mit dem Sie den Blattwechsel automatisiert haben und zwischen den Blättern hin und her klicken können. Nach diesem Prinzip funktioniert die Automatisierung. Jedes Blatt erhält eine Schaltfläche, und die Bedienung erfolgt ausschließlich über diese Schaltflächen. Vorbei die Zeit des ständigen Suchens von Dateien, Blättern und Tabellen – dem Erkennungsmerkmal unrationeller Excel-Arbeit.

Ein Blattwechsel ist natürlich nichts Besonderes. Es geht jedoch nicht nur darum, dass das Anklicken einer Schaltfläche einfacher ist als das Anklicken eines Registerblattes, weil das Suchen des Blattes entfällt. Vielmehr geht es um die Automatisierung aller weiteren Arbeiten, die beim Erstellen einer Tabelle anfallen: Quelldaten in die Tabelle setzen (andere Datei öffnen oder aus einem anderen Blatt), Formeln in die Tabelle setzen, Zeilenformat, usw. Es geht darum, dass alle sich wiederholenden Auswertungen per Mausklick auf eine Schaltfläche fertig aufgestellt, durchgerechnet und formatiert im Bildschirm stehen.

Makrobefehle für die Pfaderzeugung:

Range(„C18“).Select

ActiveCell.Formula = ActiveWorkbook.Path

Formel zur Dateinamen-Erzeugung inkl. Pfad, z. B.:

=C18&“\Kundenstatistik_“&C13&“.xlsm“

In C13 steht die Zahl 2016, in Verknüpfung zum Blatt Wahl auf die dort angelegte Jahreszahl.

Wenn Ihr Arbeitsordner z. B. C:\Daten\Kunden heißt, ergibt die Formel