Richtig einsteigen: Excel VBA-Programmierung E-Book

Bernd Held

• Herausgeber: O'Reilly
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Deutsch
• Veröffentlichungsjahr: 2016

Seitenzahl: 318

Beschreibung

Dieses Buch hat sich als verständlicher Einstieg in Excel VBA außerordentlich bewährt. Auf Grundlage seiner Kurse und langjährigen Praxiserfahrung vermittelt Bernd Held das Wichtigste über Schleifen, Verzweigungen und die relevanten Objekte von Excel. In kurzer Zeit sind Sie in der Lage, Alltagsaufgaben erfolgreich zu lösen und sich das Leben erheblich zu erleichtern. Neben schnellen Erfolgserlebnissen bietet diese Einführung genau die richtige Dosis Know-why.

Leseprobe

Richtig einsteigen: Excel VBA-Programmierung

– Für Microsoft Excel 2007 bis 2016

Bernd Held

Bernd Held

Lektorat: Ariane Hesse

Korrektorat: Sibylle Feldmann

Satz: Gerhard Alfes, www.mediaservice.tv

Herstellung: Susanne Bröckelmann

Umschlaggestaltung: Michael Oréal, www.oreal.de            unter Verwendung eines Fotos von francescodemarco/Fotolia.com

Druck und Bindung: M.P. Media-Print Informationstechnologie GmbH,             www.mediaprint-druckerei.de

Bibliografische Information der Deutschen Nationalbibliothek

Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der

Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.d-nb.de abrufbar.

ISBN:

Print   978-3-96009-003-8

PDF   978-3-96010-027-0

epub   978-3-96010-028-7

mobi   978-3-96010-029-4

1. Auflage 2016

Dieses Buch erscheint in Kooperation mit O’Reilly Media, Inc. unter dem Imprint »O’REILLY«. O’REILLY ist ein Markenzeichen und eine eingetragene Marke von O’Reilly Media, Inc. und wird mit Einwilligung des Eigentümers verwendet.

Copyright © 2016 dpunkt.verlag GmbH

Wieblinger Weg 17

69123 Heidelberg

Die vorliegende Publikation ist urheberrechtlich geschützt. Alle Rechte vorbehalten. Die Verwendung der Texte und Abbildungen, auch auszugsweise, ist ohne die schriftliche Zustimmung des Verlags urheberrechtswidrig und daher strafbar. Dies gilt insbesondere für die Vervielfältigung, Übersetzung oder die Verwendung in elektronischen Systemen.

Es wird darauf hingewiesen, dass die im Buch verwendeten Soft- und Hardware-Bezeichnungen sowie Markennamen und Produktbezeichnungen der jeweiligen Firmen im Allgemeinen warenzeichen-, marken- oder patentrechtlichem Schutz unterliegen.

Die Informationen in diesem Buch wurden mit größter Sorgfalt erarbeitet. Dennoch können Fehler nicht vollständig ausgeschlossen werden. Verlag, Autoren und Übersetzer übernehmen keine juristische Verantwortung oder irgendeine Haftung für eventuell verbliebene Fehler und deren Folgen.

5 4 3 2 1 0

Inhalt

Einleitung

Wie dieses Buch aufgebaut ist

Download der Beispieldateien

Die Icons

Unterstützung für dieses Buch

Über den Autor

1     Die Entwicklungsumgebung von Excel

Excel für die Programmierung vorbereiten

Die Sicherheitsstufe heruntersetzen

Das Werkzeug Entwicklertools einblenden

Die Entwicklungsumgebung kennenlernen

Die Entwicklungsumgebung aufrufen

Der Projekt-Explorer

Das Eigenschaftenfenster

Das Codefenster

Das Direktfenster – die Testhilfe

Der Objektkatalog – das Nachschlagewerk

Der Makrorekorder – zu Beginn eine gute Hilfe

Die ersten Makros und deren Handhabung

Wert in eine Zelle einer Tabelle schreiben

Eine Meldung am Bildschirm ausgeben

Mehrzeilige Meldung am Bildschirm ausgeben

Eine Eingabe vom Anwender verlangen

Einen individuellen Tabellenkopf erstellen

Die wichtigsten Tastenkombinationen

Variablen und Konstanten

Variablen deklarieren

Konstanten einsetzen

Zusammenfassung

Die Lernkontrolle

2     Die wichtigsten Sprachelemente von Excel-VBA

Bedingungen erstellen und üben

Die Anweisungen If/Then/Else einsetzen

Die Anweisung Select Case einsetzen

Schleifen erstellen und verstehen

Die For...Next-Schleifen

Die For Each...Next-Schleifen

Die Schleife Do Until...Loop

Die Schleife Do While...Loop

Sonstige Sprachelemente

Die Struktur With

Zusammenfassung

Lernkontrolle

3     Das Objekt Range (Zellen und Bereiche programmieren)

Zellen und Bereiche formatieren

Zahlenformat und Schriftschnitt festlegen

Zellenfarbe und Schriftfarbe festlegen

Das Gitternetz und den Gesamtrahmen formatieren

Daten in Zellen konvertieren

Korrektur nach fehlerhaftem Datenimport

Unerwünschte führende und nachgestellte Leerzeichen entfernen

Bestimmte Zeichen in Zellen ersetzen/entfernen

Die Position des Minuszeichens umstellen

Verwendete Datumsformate vereinheitlichen

Daten in Zellen und Bereichen suchen

Suche nach exakter Übereinstimmung

Suche nach exakter Übereinstimmung (Schreibweise spielt keine Rolle)

Suche auch in Teilen der Zelle (Schreibweise spielt keine Rolle)

Daten anhand eines eindeutigen Schlüssels suchen

Bereiche Zelle für Zelle verarbeiten

Daten aus einem Bereich löschen

Extremwerte in einem Bereich ermitteln und kennzeichnen

Mehrere nicht zusammenhängende Bereiche verarbeiten

Zusammenfassung

Die Lernkontrolle

4     Das Objekt Worksheet (Tabellen programmieren)

Tabellen dokumentieren, filtern und durchsuchen

Tabelleninhaltsverzeichnis erstellen und verlinken

Tabellen durchsuchen und dokumentieren

Tabellen filtern mit einem Kriterium

Tabellen filtern mit mehreren Kriterien

Tabellen einrichten und schützen

Bildlaufbereiche für Tabellen festlegen

Bereiche in Tabellen sperren

Tabellenschutz für eine Tabelle einstellen und zurücksetzen

Alle Tabellen einer Mappe schützen

Tabellenblätter anlegen, drucken und exportieren

Tabellen anlegen und benennen

Eine Tabelle drucken

Alle sichtbaren Tabellen einer Mappe ausdrucken

Tabelle als PDF ausgeben

Eine Tabelle exportieren

Individuelle Kopf- und Fußzeilen erstellen

Tabellen verstecken oder löschen

Tabellen ein- und ausblenden

Alle Tabellen bis auf eine ausblenden

Tabellen löschen

Zusammenfassung

Die Lernkontrolle

5     Das Objekt Workbook (Arbeitsmappen programmieren)

Arbeitsmappen abarbeiten und schließen

Arbeitsmappe anlegen, verarbeiten, speichern und schließen

Dokumenteigenschaften abfragen und auswerten

Externe Verknüpfungen verarbeiten

Externe Verknüpfungen ermitteln

Verknüpfte Arbeitsmappen automatisch öffnen

Externe Verknüpfungen entfernen

Arbeitsmappe löschen

Sicherheitskopie einer Arbeitsmappe erstellen

Daten aus einer anderen Mappe synchronisieren

Zusammenfassung

Die Lernkontrolle

6     Standardfunktionen nutzen, eigene Funktionen schreiben

Die integrierten Tabellenfunktionen von Excel anzapfen

Einen Bereich summieren

Eine bedingte Summierung durchführen

Extremwerte ermitteln

Leere Tabellen aus einer Arbeitsmappe entfernen

Min- und Max-Wert in einem Bereich finden und einfärben

Leere Zeilen aus einer Tabelle entfernen

Eigene Funktionen schreiben

Der Aufbau einer Funktion

Aktuelle Arbeitsmappe ermitteln

Funktionen testen

Bestimmte Zeichen aus einer Zelle entfernen

Kalenderwoche nach DIN ermitteln

Die Existenz einer Tabelle prüfen

Die Existenz einer Datei prüfen

Die Existenz eines Verzeichnisses prüfen

Funktionen im Funktionsassistenten einsehen

Funktionen in eine andere Funktionskategorie hängen

Zusammenfassung

Die Lernkontrolle

7     Die Ereignisprogrammierung in Excel

Die Arbeitsmappenereignisse

Das Ereignis Workbook_Open

Das Ereignis Workbook_BeforeClose

Das Ereignis Workbook_BeforeSave

Das Ereignis Workbook_NewSheet

Das Ereignis WorkBook_BeforePrint

Die wichtigsten Ereignisse auf Arbeitsmappenebene

Die Tabellenereignisse

Das Ereignis Worksheet_Change

Das Ereignis Worksheet_SelectionChange

Das Ereignis Worksheet_BeforeDoubleClick

Die wichtigsten Tabellenereignisse im Überblick

Excel über Tastenkombinationen steuern

Formelzellen in Festwertzellen wandeln

Makros zeitgesteuert starten

Excel nach einer bestimmten Zeit beenden

Makro zu einer bestimmten Uhrzeit starten

Zusammenfassung

Die Lernkontrolle

8     Die Dialogprogrammierung mit Excel

UserForms einfügen, beschreiben und anzeigen

Die Eigenschaften einer UserForm festlegen

Die wichtigsten Steuerelemente

Das Steuerelement TextBox

Das Steuerelement ComboBox

Das Steuerelement ListBox

Das Steuerelement CheckBox

Das Steuerelement OptionButton

Das Steuerelement Image

Zusammenfassung

Die Lernkontrolle

9     Das Fehler-Handling

Die Laufzeitfehler von Excel

Typische Stolperfallen bei der Programmierung

Einen Laufzeitfehler abfangen

Laufzeitfehler bereits im Voraus verhindern

Zusammenfassung

Die Lernkontrolle

10   Fragen & Antworten

Kapitel 1

Kapitel 2

Kapitel 3

Kapitel 4

Kapitel 5

Kapitel 6

Kapitel 7

Kapitel 8

Kapitel 9

Index

Einleitung

Wie dieses Buch aufgebaut ist

Sie wollten schon immer einmal mit Makros arbeiten? Sie möchten noch tiefer in Excel einsteigen, um damit Abläufe zu automatisieren, zu sichern und zu optimieren?

Sie haben Freude daran, sich mit zukunftsträchtigen und wertvollen Techniken zu beschäftigen? Wenn ja, dann ist dieses Buch das Richtige für Sie!

Dieses Buch führt Sie Schritt für Schritt richtig in die VBA-Programmiersprache ein. Die Grundlage für dieses Buch ist ein dreitägiger VBA-Kurs, den ich seit über 10 Jahren fast jeden Monat in Vaihingen-Enz und an anderen Standorten in Deutschland durchführe.

Nach Durcharbeiten dieses Buchs werden Sie in der Lage sein, große Teile Ihrer täglichen Arbeit zu automatisieren und lästige Routinearbeiten über Makros erledigen zu lassen. Mit diesem Buch als Grundlage werden Sie viel Spaß daran haben, auf diesem Gebiet weiter zu machen. Da Excel in fast jeder Firma vertreten und Automatisierung beinahe immer notwendig ist, werden Sie sich durch Ihr neu erworbenes Know-how viele Freunde machen.

Lernen Sie zu Beginn des Buchs zunächst einmal Ihr Werkzeug kennen. Dabei erfahren Sie, wie Sie sich in der Entwicklungsumgebung von Excel zurechtfinden, und Sie werden erfolgreich Schleifen und Verzweigungen anwenden können. Danach lernen Sie die Programmierung der wirklich wichtigen Excel-Objekte (Zellen, Tabellen und Mappen) kennen. Erfahren Sie, welche Methoden und Eigenschaften dieser Objekte dringend gebraucht werden, um die wichtigsten Arbeiten automatisiert und schnell erledigen zu können.

Am Ende eines jeden Kapitels erwarten Sie Übungsaufgaben und Verständnisfragen, sodass Sie sicher sein können, den optimalen Nutzen aus diesem Buch zu ziehen.

Die Beispiele, die im Buch vorgestellt werden, wurden mit der Excel-Version 2016 geschrieben. Alle hier vorgestellten Makros können aber genauso gut in allen Versionen ab Excel 2002 genutzt werden.

Sollten Sie Fragen oder Anregungen haben, dann scheuen Sie sich nicht, mich unter meiner Mailadresse

[email protected]

zu kontaktieren.

Download der Beispieldateien

Die für dieses Buch bereitgestellten Dateien können Sie sich unter folgender Adresse herunterladen:

www.Held-office.de/Downloads/RichtigEinsteigen.zip

Sie finden sie auch direkt auf der Verlagsseite:

http://downloads.oreilly.de/9783960090038

Die Icons

Dieses Buch führt Sie in die VBA-Programmierung mit Excel ein. Schon nach kurzer Einarbeitungszeit werden Sie über das nötige Know-how verfügen, um ansprechende Ergebnisse zu erzielen. Zugleich erwerben Sie das entsprechende Know-why, das heißt, Sie erfahren immer auch, warum etwas in einer bestimmten Weise funktioniert. Hin und wieder empfiehlt es sich für eine bessere Übersicht, bestimmte Informationen in eigenen Absätzen auszuzeichnen.

Der Textmarker weist Sie auf etwas hin, worauf Sie unbedingt achten sollten.

Hier erfahren Sie, wie Sie etwas besonders einfach und elegant erledigen können.

Absätze mit diesem Icon geben Ihnen wichtige Hintergrundinformationen und erklären, warum etwas in einer bestimmten Weise funktioniert.

Unterstützung für dieses Buch

Der Verlag hat auch von seiner Seite alles unternommen, um die Richtigkeit des Buchinhalts sicherzustellen. Etwaige Korrekturen und Änderungen finden Sie unter folgender Adresse:

http://downloads.oreilly.de/9783960090038

Kommentare, Fragen und Anregungen können Sie ebenfalls an den Verlag schicken. Wenden Sie sich dazu an [email protected]

Bitte beachten Sie, dass der Verlag keinen Support für Software-Produkte anbieten kann.

Über den Autor

Mein Name ist Bernd Held. Ich wurde am 02.04.1969 geboren, bin verheiratet und Vater von zwei Kindern. Während meines Abiturs und in der Zeit danach war ich Leistungssportler, wurde unter anderem zwei Mal Deutscher Jugendmeister über 400 Meter Hürden und nahm an Europa- und Weltmeisterschaften teil. Nach meiner sportlichen Laufbahn habe ich mich ins Berufsleben gestürzt und mich auf die Themen Excel/Access, individuelle VBA-Schulungen/VBA-Kurse sowie VBA-Programmierung spezialisiert.

Von Haus aus bin ich gelernter Informatiker. Zunächst war ich zwei Jahre bei einer kleinen Softwarefirma in der Entwicklung und danach sechs Jahre bei T-Systems im Controlling beschäftigt. Dort war ich verantwortlich für das Berichtswesen, die Leistungsverrechnung, das Erstellen von betrieblichen Auswertungen und Wirtschaftlichkeitsrechnungen sowie für die Erstellung neuer Controlling-Tools auf Basis von Microsoft Office. Im Januar 2002 folgte dann der Schritt in die Selbstständigkeit.

Seit dieser Zeit konzentriere ich mich auf die Auftragsprogrammierung, die Unternehmensberatung sowie das Schreiben von Fachartikeln und Computerbüchern. Einige Bücher von mir wurden bereits ins Russische, Tschechische und Englische übersetzt. Weitere Aufgabengebiete sind das fachliche Überarbeiten von Computerbüchern sowie die Durchführung von VBASchulungen. Zu meinen Spezialgebieten zählen Excel, VBA-Programmierung und Access. Acht Jahre in Folge wurde ich als MVP (Most Valuable Professional) für den Bereich Excel von Microsoft ausgezeichnet.

Seit 2008 arbeite ich neben meinen drei Angestellten mit einem eigenen Team aus Experten erfolgreich zusammen. Wir führen in erster Linie Programmierprojekte und Schulungen durch, sind in der Beratung tätig und schreiben Bücher sowie Artikel für diverse Fachbuchverlage.

Kapitel 1Die Entwicklungsumgebung von Excel

In diesem Kapitel:

Excel für die Programmierung vorbereiten

Die Entwicklungsumgebung kennenlernen

Die ersten Makros und deren Handhabung

Die wichtigsten Tastenkombinationen

Variablen und Konstanten

Zusammenfassung

Die Lernkontrolle

Excel für die Programmierung vorbereiten

Bevor Sie richtig mit Excel-VBA einsteigen können, müssen Sie Microsoft Excel erst einmal für den Gebrauch der zukünftigen Makros einrichten.

Die Sicherheitsstufe heruntersetzen

Seit der Version Excel 2007 ist der Umgang mit Makros in Office etwas erschwert worden. Standardmäßig ist nach der Installation des Office-Pakets in Excel die höchste Sicherheitsstufe eingestellt. In dieser Einstellung sind die Nutzung und das Schreiben von Makros nicht möglich.

Bereiten Sie Excel für die zukünftigen Aufgaben einmalig wie folgt vor:

Starten Sie Microsoft Excel.

Klicken Sie im Menüband unter Datei auf den Befehl Optionen.

Im Dialog Excel-Optionen wählen Sie die Rubrik Trust Center aus.

Klicken Sie auf die Schaltfläche Einstellungen für das Trust Center.

Wählen Sie im Dialog Trust Center die Rubrik Makroeinstellungen aus.

Aktivieren Sie in den Makroeinstellungen die Option Alle Makros aktivieren.

Setzen Sie einen Haken in das Kontrollkästchen Zugriff auf das VBA-Projektmodell vertrauen.

Abbildung 1.1: Die Makroeinstellungen anpassen und den Zugriff auf das VBA-Objektmodell erlauben.

Bestätigen Sie diese Einstellung, indem Sie die geöffneten Dialoge jeweils mit OK beenden.

Nachdem Sie diese Einstellungen vorgenommen haben, können Sie zukünftig mit Makros arbeiten, außerdem können Sie Dateien, die Makros enthalten, ebenfalls nutzen.

Der Haken im Trust Center bei Zugriff auf das VBA-Projektmodell vertrauen versetzt Sie in die Lage, zu einem späteren Zeitpunkt, wenn Sie schon etwas geübter im Umgang mit VBA sind, Makros zu schreiben, mit denen Sie beispielsweise Makros aus anderen Excel-Dateien ersetzen und bearbeiten können. Sie können aufgrund dieser Trust-Einstellung dann im Prinzip zur Laufzeit auf Makros zugreifen sowie ganze Programmteile austauschen und durch neue Makros ersetzen.

Das Werkzeug Entwicklertools einblenden

Um praktikabel mit Makros umgehen zu können, bietet Microsoft eine eigene Registerkarte für die Verwaltung und Programmierung von Makros in der Oberfläche von Excel an, die zunächst jedoch dem Standardanwender verborgen bleibt. Diese Registerkarte trägt den Namen Entwicklertools. Mithilfe der Werkzeuge auf dieser Registerkarte können Sie beispielsweise Schaltflächen in Tabellen einfügen und diesen Schaltflächen Makros zuweisen, um diese später mit einem Klick auf die Schaltfläche starten zu können. Des Weiteren beinhaltet dieser Werkzeugkasten die Möglichkeit, Makros mittels des Makrorekorders automatisch aufzuzeichnen.

Blenden Sie das Menüband Entwicklertools wie folgt ein:

Klicken Sie im Menüband unter Datei auf den Befehl Optionen.

Im Dialog Excel-Optionen wählen Sie die Rubrik Menüband anpassen aus.

Aktivieren Sie im Feld Hauptregisterkarten das Kontrollkästchen Entwicklertools.

Abbildung 1.2: Das Werkzeug Entwicklertools einblenden.

Bestätigen Sie Ihre Einstellung mit einem Klick auf OK.

In der Excel-Oberfläche wird jetzt ein neues Menüband mit dem Namen Entwicklertools angeboten. Die wichtigsten Funktionen aus diesem Register werden auf den folgenden Seiten nach und nach beschrieben.

Abbildung 1.3: Das Register Entwicklertools steht für die Bearbeitung und Verwaltung der Makros bereit.

Die Entwicklungsumgebung kennenlernen

Erfahren Sie auf den nächsten Seiten alles, was Sie von Beginn an brauchen, damit Sie Ihre Makros schnell und sicher erfassen, starten und testen können.

Die fertig ausgefüllte Arbeitsmappe Start.xlsm mit allen folgenden Beispielen können Sie unter dieser Adresse herunterladen: http://downloads.oreilly.de/9783960090038.

Die Entwicklungsumgebung aufrufen

Wagen Sie den ersten Sprung in die Entwicklungsumgebung von Excel, indem Sie im Register Entwicklertools den Befehl Visual Basic anklicken. Alternativ dazu können Sie sich die Tastenkombination merken, die Sie ebenfalls direkt in die Entwicklungsumgebung von Microsoft Excel bringt.

Abbildung 1.4: Die Entwicklungsumgebung von Microsoft Excel.

Die Entwicklungsumgebung in Excel-VBA beinhaltet mehrere Fenster, über die Sie Makros einsehen, erfassen und testen können.

Der Projekt-Explorer

Standardmäßig oben links ist der sogenannte Projekt-Explorer zu finden. Sollte dieser Explorer nicht angezeigt werden, können Sie ihn über das Menü Ansicht und den Befehl Projekt-Explorer einblenden. Alternativ rufen Sie den Projekt-Explorer über die Tastenkombination auf.

Der Projekt-Explorer zeigt Ihnen alle aktuell geöffneten Arbeitsmappen sowie die darin enthaltenen Tabellen an.

Abbildung 1.5: Der Projekt-Explorer gibt Auskunft über den Inhalt einer Mappe.

Die beiden ersten Symbole Code anzeigen und Objekt anzeigen werden später für Sie von Interesse sein, wenn Sie beispielsweise eigene benutzerfreundliche Dialoge entwerfen und zwischen den beiden Ebenen Objekt und Code hin- und herspringen müssen.

Das doppelt belegte Symbol Ordner wechseln ist eine reine Ansichtsoption. Damit wird die Ansicht der Objekte in der Arbeitsmappe angezeigt. Die standardmäßig eingestellte Ansicht ordnet die Objekte schön ordentlich in Rubriken an. Mit einem weiteren Klick auf dieses Symbol werden alle Objekte alphabetisch und ohne Darstellung in Rubriken angezeigt. Hier ist der Übersichtlichkeit halber die Standardeinstellung zu empfehlen.

Der Projekt-Explorer arbeitet sehr eng mit dem darunterliegenden Fenster, dem Fenster Eigenschaften, zusammen. Diese beiden Werkzeuge müssen in einem Atemzug genannt werden, da sie sich einander bedingen. Genau aus diesem Grund sind beide Fenster standardmäßig untereinander angeordnet.

Das Eigenschaftenfenster

Direkt unterhalb des Projekt-Explorers befindet sich standardmäßig das Eigenschaftenfenster. Sollte es nicht eingeblendet sein, können Sie es über den Menübefehl Ansicht/Eigenschaftenfenster einblenden. Alternativ dazu können Sie auch die Taste drücken, um das Eigenschaftenfenster einzublenden.

Wie gerade schon erwähnt, sind Explorer und Eigenschaftenfenster als Einheit zu verstehen. Wenn Sie beispielsweise im Projekt-Explorer die Tabelle1 anklicken, werden im darunterliegenden Eigenschaftenfenster alle Eigenschaften zu dieser Tabelle angezeigt. Durch Setzen dieser Eigenschaften können Verhalten und Aussehen der Tabellen beeinflusst werden.

Generell können Eigenschaften entweder im Eigenschaftenfenster eingestellt oder auch direkt durch Makros gesetzt werden.

Die wichtigsten Eigenschaften lernen Sie anhand von praktischen Aufgaben auf den folgenden Seiten kennen.

Tabellen ein- und ausblenden

Mithilfe des Eigenschaftenfensters lassen sich in Excel Tabellen ein- und ausblenden. Dabei erleben Sie gleich zu Beginn eine kleine Überraschung. Achten Sie darauf, dass Ihre Arbeitsmappe mehr als nur eine Tabelle enthalten muss.

Gehen Sie wie folgt vor, um beispielsweise Tabelle1 auszublenden:

Klicken Sie im Projekt-Explorer auf das Objekt Tabelle1 (Tabelle1).

Klicken Sie im Eigenschaftenfenster ganz unten rechts neben die Eigenschaft Visible.

Abbildung 1.6: Die Eigenschaft Visible bietet drei Zustände der Sichtbarkeit an.

Wählen Sie aus dem Drop-down die Konstante 0 – xlSheetHidden.

Jetzt ist Tabelle1 ausgeblendet. Der Mauszeiger springt dabei, was zunächst vielleicht etwas irritiert, automatisch auf Tabelle2. Um den Status von Tabelle1 wieder einzusehen bzw. zu ändern, klicken Sie einfach auf das Objekt Tabelle1 (Tabelle1) im Projekt-Explorer und weisen im Eigenschaftenfenster wieder die Konstante -1- xlSheetVisible zu.

Sicher ist Ihnen nicht entgangen, dass das Eigenschaftenfenster bei der Eigenschaft Visible eine weitere Konstante anbietet: 2 - xlSheetVeryHidden. Diese Konstante sorgt dafür, dass die so eingestellte Tabelle sicher ausgeblendet wird. Das bedeutet, dass der »normale« Anwender keine Möglichkeit hat, diese Tabelle über die Oberfläche von Excel einzublenden. Tabellen werden in der Praxis gern sicher versteckt, wenn es darum geht, Daten zu verbergen, damit diese nicht gelöscht werden können.

In einer Arbeitsmappe muss mindestens eine Tabelle immer eingeblendet sein. Sie können daher nicht alle Tabellen ausblenden!

Standardspaltenbreite festlegen

Über die Eigenschaft StandardWidth im Eigenschaftenfenster können Sie die Standardspaltenbreite der Tabelle festlegen. Standardmäßig ist die Breite einer Spalte mit 10,71 festgelegt.

Eine Einheit der Spaltenbreite entspricht der Breite eines Zeichens in der standardmäßig eingestellten Schriftart. Je nach eingestellter Schriftart ist eine Umrechnung mehr oder weniger genau. Wenn Sie eine Umrechnung in Millimeter durchführen möchten, kann man gedanklich näherungsweise die Breite nach der Formel –0,71 + 5,1425 * Wunsch in mm / 10 berechnen. Hier wäre natürlich eine einfachere Umrechnung wünschenswert.

Zu jeder Eigenschaft können Sie übrigens die Taste drücken, um weiterführende Informationen in der Onlinehilfe abzurufen.

Die ScrollArea setzen

Unter einer ScrollArea versteht man den Bereich einer Tabelle, in dem sich der »normale« Anwender aufhalten darf. Dieser Aufenthaltsbereich kann über das Eigenschaftenfenster festgelegt werden.

Setzen Sie einmal testweise die Eigenschaft ScrollArea, indem Sie wie folgt vorgehen:

Erfassen Sie im Eigenschaftenfenster bei der Eigenschaft ScrollArea den Bezug A1:D10.

Bestätigen Sie die Eingabe mit . Die Eingabe wird augenblicklich mit absoluten Bezügen umfasst. So wird aus der Eingabe A1:D10 die Eingabe $A$1:$D$10.

Wechseln Sie, nachdem Sie die ScrollArea gesetzt haben, über die Tastenkombination in die Standardoberfläche von Microsoft Excel.

Wechseln Sie zu Tabelle1.

Versuchen Sie, den Bereich A1:D10 zu verlassen. Es wird Ihnen nicht gelingen.

In der Praxis wird diese Technik gern verwendet, um sensible Daten zu schützen. Daher können Sie, bevor Sie diese Eigenschaft verwenden, im Vorfeld Daten in einem entlegenen Teil der Tabelle erfassen und anschließend den Aufenthaltsbereich einschränken. So kommt der Standardanwender von Excel nicht an Ihre Daten und kann sie daher auch nicht ändern.

Leider wird ein geänderter Wert der Eigenschaft ScrollArea nicht aufrechterhalten, wenn Sie die Eigenschaft einstellen und danach die Arbeitsmappe speichern und schließen. Nach erneutem Öffnen der Arbeitsmappe ist die vorher gesetzte Eigenschaft wieder weg. Was nun tun, um sicherzustellen, dass diese Eigenschaft nach dem Öffnen der Arbeitsmappe gesetzt ist?

Die Lösung ergibt sich direkt aus dem Verhalten dieser Eigenschaft. Wenn der Eigenschaftswert nach dem Öffnen der Arbeitsmappe nicht mehr vorhanden ist, müssen Sie beim Öffnen dafür sorgen, dass er automatisch gesetzt wird.

Um die ScrollArea dauerhaft einzurichten, greifen Sie in die Trickkiste und richten ein sogenanntes Ereignis ein. Gehen Sie dabei wie folgt vor:

Klicken Sie im Projekt-Explorer doppelt auf das Objekt DieseArbeitsmappe.

Wählen Sie im rechts daneben eingeblendeten Fenster im linken Drop-down-Listenfeld den Eintrag Workbook. Daraufhin wird automatisch das Ereignis Workbook_Open eingestellt.

Ergänzen Sie den noch leeren Rahmen des Ereignisses wie in Abbildung 1.7 gezeigt.

Abbildung 1.7: Das Ereignis Workbook_Open wird automatisch beim Öffnen der Arbeitsmappe ausgeführt.

Speichern Sie Ihre Arbeitsmappe. Danach schließen und öffnen Sie die Arbeitsmappe erneut. Sie werden feststellen, dass die ScrollArea jetzt »dauerhaft« eingestellt ist.

Auf diese Art und Weise können Sie zu jeder Tabelle ganz individuell eine ScrollArea einrichten.

Möchten Sie beispielsweise eine Tabelle gänzlich unveränderbar gestalten, ist die kleine ScrollArea durch den Zellenbezug A1 zu definieren. In einer Tabelle, in der dieser Bezug angegeben wird, ist keinerlei »Bewegung« möglich – auch eine Art von Datenschutz, meinen Sie nicht auch?

Eine ScrollArea ist ein zusammenhängender Bereich. Sie können nicht mehrere ScrollAreas auf einer Tabelle definieren. Wie Sie diese Standardeinstellung umgehen können, verrate ich Ihnen in Kapitel 7 »Ereignisprogrammierung in Excel«.

Den Namen einer Tabelle festlegen

Wenn Sie im Eigenschaftenfenster genau hinsehen, erkennen Sie, dass es zwei Eigenschaften gibt, um den Namen einer Tabelle festzulegen – die beiden Eigenschaften Name und (Name) im Eigenschaftenfenster. Standardmäßig sind beide Eigenschaften mit dem gleichen Wert belegt. Dieser doppelte Name spiegelt sich auch im Projekt-Explorer wider. Was hat es nun auf sich mit den zwei Namen? Die Beantwortung dieser Frage ist derart wichtig, dass Sie dazu erst einmal ein Beispiel erhalten. Gehen Sie wie folgt vor:

Wechseln Sie aus der Entwicklungsumgebung heraus in die Normalansicht von Excel.

Klicken Sie direkt im Tabellenregister unten den Namen Tabelle1 doppelt an.

Erfassen Sie den Namen Test und bestätigen Sie mit .

Wechseln Sie über die Tastenkombination zurück in die Entwicklungsumgebung.

Betrachten Sie das Ergebnis in Abbildung 1.8.

Abbildung 1.8: Wir stellen eine Abweichung der Tabellennamen fest.

Wie Sie sehen können, haben wir nun zwei verschiedene Tabellennamen. Beide Namen können auch jederzeit direkt über das Eigenschaftenfenster geändert werden. Worin besteht nun der Unterschied zwischen den beiden Tabellennamen, und welchen Tabellennamen sollten Sie für die Programmierung verwenden?

Nun, merken Sie sich Folgendes: Benutzen Sie immer den Tabellennamen, der im Projekt-Explorer an erster Stelle steht. Bei diesem Namen spricht man vom sogenannten Codenamen der Tabelle. Durch die Benutzung des Codenamens ersparen Sie sich gleich zu Beginn Ihrer Karriere als Entwickler viel Arbeit und Ärger. Auf die Vor- und Nachteile gehe ich im weiteren Verlauf des Kapitels noch genauer ein.

Bevor Sie überhaupt mit der Programmierung von Makros beginnen, sollten Sie die Tabellen so benennen, dass beide Namen idealerweise wieder gleich lauten, dabei aber auch sprechend sind. In der Praxis hat es sich bewährt, beim Tabellennamen ein Kürzel vorzugeben, damit auch im Makro später direkt erkannt werden kann, dass es sich um eine Tabelle handelt. So könnten Sie beispielsweise die ehemalige Tabelle1 in beiden Eigenschaftenfeldern mit dem Namen tbl_ErsteMakros benennen.

Abbildung 1.9: Sinnvolle Namen für Tabellen vergeben.

Bei der Benennung von Tabellen gilt es, bestimmte Einschränkungen zu beachten. So dürfen Sie beispielsweise bestimmte Zeichenfolgen beim Codenamen der Tabelle (die obere Eigenschaft im Eigenschaftenfenster) nicht verwenden. Unter anderem sind das Sonderzeichen sowie das Leerzeichen. Das erste Zeichen beim Codenamen der Tabelle darf auch keine Zahl sein.

Das Codefenster

Das Codefenster befindet sich standardmäßig rechts neben dem Projekt-Explorer. Dieses Fenster wird dann sichtbar, wenn Sie ein Modul anlegen. Ein Modul ist vergleichbar mit einem Ordner, der im Prinzip unsere zukünftigen Makros beinhaltet.

Gehen Sie wie folgt vor, um ein Modul anzulegen:

Wählen Sie aus dem Menü Einfügen den Befehl Modul.

Ersetzen Sie den standardmäßig vorgegebenen Namen Modul1 durch einen sprechenden Namen, indem Sie das gerade eingefügte Modul im Projekt-Explorer markieren, danach den Namen mdl_ErsteMakros im Eigenschaftenfenster eintragen und das Ganze mit bestätigen.

Ich denke, dass es von Beginn an wichtig ist, Ordnung zu halten und klare Strukturen einzurichten. Daher geben Sie Ihren Tabellen, Modulen und auch Ihren Makros sprechende Namen. Sie machen es sich damit später leichter, Makros zu schreiben. Denken Sie dabei ebenfalls an Kollegen, die Ihre Makros eventuell verstehen und gegebenenfalls auch anpassen müssen, wenn Sie einmal im Urlaub sind. In diesem gerade angelegten Modul werden wir nachher unsere ersten Makros erfassen und starten.

Jedes Makro beginnt übrigens in VBA mit der Anweisung Sub. Der Begriff kommt aus dem Englischen und bedeutet so viel wie »Unter-(titel)«, was bedeutet, dass ein Makro unterhalb eines Moduls angesiedelt ist.

Abbildung 1.10: Auch Module sollten sprechende Namen haben.

Nach dem Schlüsselwort Sub folgt ein Leerzeichen. Direkt im Anschluss daran können Sie einen Namen für das Makro angeben. Bedenken Sie dabei, dass für die Benennung von Makros folgende Punkte beachtet werden müssen:

• Das erste Zeichen muss ein alphanumerisches Zeichen sein.

• Der Makroname darf keine Leerzeichen enthalten.

• Es dürfen keine Sonderzeichen wie /, %, -, $, [, ], ?, ! oder Ähnliche verwendet werden.

Nach dem Namen geben Sie ein rundes Klammernpaar ein und drücken die Taste . Das Makro wird jetzt automatisch um die Anweisung End Sub ergänzt.

Sub DasErsteMakro()End Sub

Momentan ist das Makro noch leer. Alle Anweisungen, die Sie innerhalb dieses Rahmens schreiben, werden abgearbeitet und nacheinander ausgeführt. Ergänzen Sie das Makro nun wie folgt, um beispielsweise den Namen des Anwenders auf dem Bildschirm auszugeben:

Sub DasErsteMakro()   MsgBox Environ("Username")End Sub

Listing 1.1: Den angemeldeten Anwender am Bildschirm ausgeben.

Möchten Sie das Makro starten, haben Sie dafür folgende Möglichkeiten:

• Setzen Sie den Mauszeiger auf die erste Zeile des Makros und drücken Sie die Taste .

• Setzen Sie den Mauszeiger auf die erste Zeile des Makros und wählen Sie aus dem Menü Ausführen den Befehl Sub/Userform ausführen.

• Setzen Sie den Mauszeiger auf die erste Zeile des Makros und klicken Sie in der Symbolleiste Voreinstellung auf das Symbol Sub/Userform ausführen.

• Wechseln Sie auf Ihre Excel-Arbeitsoberfläche und wählen Sie im Menüband Entwicklertools (alternativ im Menüband Ansicht) in der Gruppe Code das Symbol Makros. Im nun angezeigten Dialogfeld wählen Sie das Makro aus und klicken auf die Schaltfläche Ausführen.

In jeder beschriebenen Variante wird eine Meldung auf dem Bildschirm ausgegeben, in der der Anwendername angezeigt wird. Dies erreichen Sie, indem Sie die Funktion MsgBox einsetzen.

Das Direktfenster – die Testhilfe

Vielleicht zu Beginn noch nicht ganz so wesentlich, dafür später aber umso wichtiger ist das sogenannte Direktfenster. Dieses Fenster blenden Sie über das Menü Ansicht und den Befehl Direktfenster ein. Alternativ dazu können Sie auch die Tastenkombination drücken, um das Direktfenster einzublenden.

Abbildung 1.11: Das Direktfenster im unteren Bereich der Entwicklungsumgebung.

Über das Direktfenster können unter anderem Fehler in einem Makro gut gefunden und beseitigt werden. Wir werden im Verlauf des Buchs dieses Fenster häufiger einmal nutzen.

In der Praxis wird das Direktfenster auch gern eingesetzt, um Befehle direkt abzusetzen, ohne gleich ein eigenes Makro schreiben zu müssen. Daher folgen an dieser Stelle einmal einige Beispiele, wie Sie das Direktfenster verwenden können, um bestimmte Dinge abzufragen bzw. Aktionen zu starten.

Angemeldeten Nutzer ermitteln

Mit einem einzigen Befehl, den Sie bereits kennengelernt haben, können Sie abfragen, welcher Nutzer gerade in Windows angemeldet ist. Dazu verfahren Sie wie folgt:

Setzen Sie den Mauszeiger direkt in das Direktfenster.

Geben Sie als erstes Zeichen ein Fragezeichen an. Damit geben Sie bekannt, dass Sie nun eine Information benötigen.

Erfassen Sie als kompletten Befehl ?Environ("username").

Bestätigen Sie diesen Befehl mit .

Abbildung 1.12: Das Ergebnis wird direkt unterhalb des eingegebenen Befehls ausgegeben.

Erfassen Sie spaßeshalber noch den Befehl ?Environ("Computername"), um den Namen des Computers abzufragen, an dem Sie gerade sitzen. Diese beiden Befehle können später sehr gut eingesetzt werden, um eine Benutzerverwaltung in Excel aufzubauen. Diese werden wir gemeinsam in Kapitel 7, »Die Ereignisprogrammierung in Excel« erstellen.

Tabelle ein- und ausblenden

Erinnern Sie sich noch? Wir haben zu Beginn des Kapitels über das Eigenschaftenfenster eine Tabelle aus- und wieder eingeblendet. Diesen Vorgang können Sie auch über das Direktfenster durchführen. Gehen Sie dazu folgende Arbeitsschritte:

Setzen Sie den Mauszeiger direkt in das Direktfenster.

Geben Sie den Namen der Tabelle ein, die Sie ausblenden möchten.

Erfassen Sie direkt nach dem Tabellennamen einen Punkt.

Abbildung 1.13: Excel hilft bei der Eingabe der Befehle.

Nach der Eingabe des Tabellennamens und dem Setzen des Punkts klappt automatisch ein Drop-down herunter, in dem nur die Befehle angeboten werden, die für eine Tabelle überhaupt möglich sind.

Geben Sie als ersten Buchstaben das v ein.

Abbildung 1.14: Der Mauszeiger springt automatisch zum ersten Befehl, der mit dem Buchstaben v beginnt.

Drücken Sie die Taste , um den Befehl zu übernehmen.

Nach der Auswahl der Eigenschaft Visible erfassen Sie das Gleichheitszeichen. Daraufhin klappt automatisch ein Drop-down herunter, in dem alle möglichen Konstanten zu dieser Eigenschaft auswählbar sind.

Abbildung 1.15: Übernehmen Sie die gewünschte Konstante.

Klicken Sie die Konstante xlSheetHidden an, um Tabelle2 auszublenden.

Drücken Sie die Taste , um den Befehl abzusetzen. Die Tabelle wird augenblicklich ausgeblendet.

Vielleicht haben Sie bemerkt, dass, nachdem Sie den Namen der Tabelle erfasst hatten, in dem Drop-down unterschiedliche Symbole bei den angebotenen Befehlen angezeigt wurden, und zwar Handsymbole und grüne Radiergummis. Bei Erstgenannten handelt es sich um Eigenschaften, bei den Radiergummis geht es um Methoden.

Was ist der Unterschied zwischen Eigenschaften und Methoden?

Eigenschaften beschreiben und charakterisieren ein Objekt, Methoden führen ganz konkret Aktionen mit dem Objekt durch. Für unser gerade vorgeführtes Beispiel ist das Ein- und Ausblenden einer Tabelle eine typische Eigenschaft, die den Status einer Tabelle beschreibt.

Eine typische Methode für das Objekt Tabelle wäre beispielsweise das Einfügen (=Add) bzw. das Löschen einer Tabelle (=Delete). Diese beiden Methoden führen eine Aktion mit der Tabelle durch.

Diese Unterscheidung ist aber nicht das Wesentliche daran, wichtig ist der Unterschied in der Syntax, der es notwendig macht, zu wissen, ob es sich um eine Eigenschaft oder eine Methode handelt.

Dazu ein kleines Beispiel, das die Syntax von Eigenschaften und Methoden näherbringen soll.

Die Syntax einer Eigenschaft am Beispiel des Ausblendens einer Tabelle lautet:

Tabelle2.Visible=xlSheetHidden

Eine Eigenschaft wird gesetzt, indem mit einem Gleichheitszeichen eine Konstante zugewiesen wird.

Die Syntax einer Methode am Beispiel des Einfügens einer neuen Tabelle lautet:

Worksheets.Add Before:=Worksheets(1)

Methoden haben sehr oft weitere Parameter, die automatisch angezeigt werden, wenn man die Methode aus dem Drop-down übernimmt und die drückt. Dabei wird der Parametername übernommen, gefolgt von der Zeichenfolge :=.

In unserem Beispiel wird eine neue Tabelle zu Beginn der Arbeitsmappe eingefügt. Der Begriff Worksheets ist ein sogenanntes Auflistungsobjekt, d.h., in der Auflistung Worksheets sind automatisch alle Tabellen der Arbeitsmappe enthalten. Dieser Auflistung aller Tabellen fügen wir eine weitere Tabelle mithilfe der Methode Add hinzu. Die Methode Add enthält weitere Parameter, über die wir die Position der neuen Tabelle in der Mappe sowie die Anzahl der einzufügenden Tabellen festlegen können.

Wir werden im weiteren Verlauf des Buchs noch sehr oft mit diesem Thema in Berührung kommen.

Der Objektkatalog – das Nachschlagewerk

Innerhalb der Entwicklungsumgebung gibt es einen Katalog, in dem alle VBA-Befehle hinterlegt sind. Schauen wir uns diesen Objektkatalog einmal näher an, indem Sie die Taste drücken.

Abbildung 1.16: Der Objektkatalog gibt Auskunft über die verfügbaren Befehle von VBA.

Auf den ersten Blick wirkt dieser Katalog auf den Einsteiger erschlagend, da er Tausende von Befehlen enthält. Lassen Sie sich dadurch nicht abschrecken. Wie schon im Vorwort des Buchs erwähnt, reichen meiner Ansicht nach ca. 40 Befehle aus, um 90 % aller Aufgaben in Excel zu erledigen.

Schauen wir uns einmal das wichtigste Objekt in Excel an, die Zelle (=Range). Klicken Sie dazu in das Feld Klassen und geben Sie den Buchstaben r ein. Es werden jetzt alle Befehle angezeigt, die für das Objekt Zelle verfügbar sind.

Abbildung 1.17: Alle Methoden und Eigenschaften, die für das Objekt Range verfügbar sind.

Die Zelle selbst ist die kleinste Einheit in Excel. Viele Zellen zusammen ergeben einen Bereich, viele Zellen untereinander ergeben eine Spalte, viele Zellen nebeneinander repräsentieren eine Zeile. Alles das ist ein einziges Objekt. Dies bedeutet, dass wir für all diese Dinge die gleichen Befehle verwenden können, die in Abbildung 1.17 gezeigt werden.

Im Objektkatalog werden die gleichen Befehle angeboten wie auch schon vorher im Direktfensterbeispiel, als wir den Unterschied zwischen Methoden und Eigenschaften am Objekt Tabelle erklärten.

Vielleicht sehen wir uns an dieser Stelle bereits ein Beispiel für eine typische Eigenschaft bzw. eine Methode speziell für das Objekt Range an. Geben Sie die beiden folgenden Befehle testhalber direkt im Direktfenster der Entwicklungsumgebung ein und drücken Sie danach die Taste .

Die Syntax einer Eigenschaft am Beispiel einer Zellenadresse lautet:

?Activecell.Address

Die Adresse, also die Zellenkoordinate, ist eine typische Eigenschaft der Zelle, die eben beschreibt, welche Adresse die Zelle hat. Als Ergebnis wird bei Ihnen die Koordinate der aktiven Zelle ausgegeben.

Die Syntax einer Methode am Beispiel eines Zellenkommentars lautet:

Range("A1").AddComment Text:="Das ist eine Notiz"

Damit fügen Sie eine Zellennotiz in Zelle A1 der aktiven Tabelle ein. Die Methode AddComment ist eine typische Methode für das Objekt Zelle. Diese Methode hat den Parameter Text, der gefolgt von der Zeichenfolge := und dem eigentlichen Kommentartext angegeben werden muss.

Alternativ und kürzer würde auch folgende Syntax funktionieren:

Range("A1").AddComment "Das ist eine Notiz"

Gerade bei diesem Beispiel verhält sich Excel etwas sonderbar. Wenn ich versuche, diesen Befehl zweimal hintereinander abzusetzen, erhalte ich beim zweiten Mal eine Fehlermeldung.

Abbildung 1.18: Eine relativ allgemein gehaltene Fehlermeldung.

Hierbei handelt es sich um einen sogenannten Laufzeitfehler. Da die Fehlermeldungen von Excel eher nicht so aussagekräftig sind, ist diesem Thema ein extra Kapitel im Buch gewidmet. Machen Sie sich also keine Sorgen, wenn Sie gerade zu Beginn Ihres Lernens des Öfteren einmal einen LZF (Laufzeitfehler) erhalten.

Wie Sie mit solchen Fehlern umgehen können, erfahren Sie in Kapitel 9 »Das Fehler-Handling« am Ende des Buchs.

Der Makrorekorder – zu Beginn eine gute Hilfe

Für den Einsteiger in Excel-VBA bietet der eingebaute Makrorekorder eine gute Möglichkeit, sich schnell Befehle anzueignen und ein Gefühl für die VBA-Syntax zu bekommen. Der Makrorekorder ist in der Lage, automatisch die notwendigen Befehle aufzuzeichnen, während Sie händisch eine Aufgabe in Excel erledigen.

Doch eines vorweg. Sie sollten jede Makroaufzeichnung verbessern und ausdünnen. Aufzeichnungen des Makrorekorders produzieren gut drei- bis viermal so viel Quellcode, wie eigentlich notwendig wäre, um die jeweilige Aufgabe zu erledigen. Der Rekorder zeichnet im wahrsten Sinne des Wortes jede einzelne Handlung auf, selbst wenn diese nur im Hintergrund abläuft und eigentlich gar nicht aufgezeichnet werden sollte. Des Weiteren stößt der Makrorekorder an seine Grenzen, wenn es beispielsweise darum geht, Abfragen und Schleifen aufzuzeichnen – das kann er nämlich nicht.

Damit Sie ein Gespür dafür entwickeln, wie Sie den Makrorekorder einsetzen können, schauen Sie sich die folgenden typischen Aufgaben aus der Praxis einmal genauer an.

Bereich kopieren und eins zu eins einfügen

Im ersten Beispiel des Makrorekorders wollen wir einen Bereich aus einer Tabelle kopieren und ihn in eine andere Tabelle inklusive aller Formate und Formeln wieder einfügen. Diese händisch auszuführende Aufgabe werden wir jetzt mithilfe des Makrorekorders aufzeichnen lassen, anschließend analysieren wir den Quellcode und verbessern ihn Schritt für Schritt.

Gehen Sie wie folgt vor, um Ihr erstes Makro aufzuzeichnen:

Wechseln Sie in die Standardoberfläche von Excel.

Erfassen Sie in der Tabelle tbl_ErsteMakros im Bereich A1:A10 beliebige Zahlen.

Klicken Sie im Menüband Entwicklertools auf den Befehl Makro aufzeichnen.

Abbildung 1.19: Die erste Makroaufzeichnung wird ausgeführt.

Im Dialog Makro aufzeichnen belassen Sie die Standardeinstellungen und klicken auf OK, um die Aufzeichnung zu starten.

Markieren Sie den Bereich A1:A10 in der Tabelle tbl_ErsteMakros und drücken Sie die Tastenkombination , um den Bereich zu kopieren.

Wechseln Sie zu Tabelle2, setzen Sie den Mauszeiger in Zelle D1 und drücken Sie die Tastenkombination , um den Bereich ab dieser Zelle einzufügen.

Klicken Sie im Menüband Entwicklertools auf den Befehl Aufzeichnung beenden.

Wechseln Sie über die Tastenkombination in die Entwicklungsumgebung, um das Resultat Ihrer Aufzeichnung anzusehen.

Abbildung 1.20: Die Aufzeichnung wird in einem neuen Modul abgelegt.

Geben Sie dem Modul1 mithilfe des Eigenschaftenfensters den Namen mdl_Makrorekorder.

Wenn Sie sich das Makro aus Abbildung 1.20 ansehen, stellen Sie fest, dass hierbei sehr oft die Methode Select ausgeführt wurde. Genau das haben Sie bei der Aufzeichnung des Makros getan. Sie haben zunächst einen Bereich markiert, nach dem Kopieren Tabelle2 gewählt und anschließend vor dem Einfügen der Daten noch die Zielzelle selektiert. Alle diese Aktionen sind bei der Programmierung von Makros nicht notwendig und können ersatzlos gestrichen werden. Jeder Select-Befehl braucht außerdem Zeit, und wir wollen ja schnell sein.

Eine weitere Problematik ergibt sich dann, wenn Sie das Makro erneut starten, indem Sie in das Makro klicken und die Taste drücken. Aus der ersten Zeile des aufgezeichneten Makros geht nicht hervor, in welcher Tabelle der Bereich A1:A10 selektiert wird. Beim zweiten Starten des Makros befinden Sie sich ja schon in der Zieltabelle Tabelle2. Das bedeutet, dass Excel nun den Bereich genau aus dieser Tabelle nimmt, also einen aktuell leeren Bereich, und diesen ab Zelle D1 in derselben Tabelle einfügt. Damit würden Sie die vorher eingefügten Daten im Bereich D1:D10 löschen. Probieren Sie es einfach einmal aus. Makroaufzeichnungen sind also eine etwas wacklige Angelegenheit und müssen auch aus diesem Grund verbessert werden.

Wie viele Zeilen Quellcode braucht man wirklich, um diese Aufgabe zu lösen? Was schätzen Sie? Was habe ich zu Beginn zum Einsatz des Makrorekorders gesagt?

Seinen Sie mutig und sagen Sie: »Wir brauchen eine einzige Zeile, um diese Aufgabe zu lösen!«

Sie haben recht, und hier ist sie:

Abbildung 1.21: Sicherer, schneller und viel kürzer – die korrigierte Fassung.

Wenn Sie diese Zeile erfassen und nach der Bereichsangabe den Punkt setzen, klappt automatisch ein Drop-down herunter und bietet Ihnen alle verfügbaren Befehle an, die im Zusammenhang mit dem Range-Objekt möglich sind. Unter anderem ist das die Methode Copy, die durch ein grünes Radiergummisymbol gekennzeichnet wird. Immer wenn Sie im Drop-down einen Befehl wählen, der mit diesem Symbol gekennzeichnet ist, übernehmen Sie die Methode mit der Taste und drücken dann die . Danach werden zusätzliche Parameter für die Methode angezeigt, sofern es weitere Parameter für die jeweilige Methode gibt. Bei der Methode Copy ist es der Parameter Destination, über den Sie direkt angeben können, wo der gerade kopierte Bereich eingefügt werden soll.

Bereich kopieren und nur Werte einfügen

Die zweite Aufgabe gleicht der gerade beschriebenen, jedoch mit dem einen Unterschied, dass nur die Werte, also keine Formeln und Formate, eingefügt werden sollen. Bei der vorherigen Variante wurde der Bereich eins zu eins kopiert und eingefügt.

Um den Unterschied zur ersten Variante auf einen Blick zu sehen, färben Sie einmal die ersten fünf Zellen im Bereich A1:A10 mit der Farbe Gelb und erfassen Sie in die Zelle A10 die Formel =SUMME(A1:A9). Weisen Sie dieser Zelle den Schriftschnitt Fett zu. Am schnellsten geht das über die Tastenkombination . Starten Sie vorab noch einmal Ihr vorher korrigiertes Makro aus Abbildung 1.21, indem Sie den Mauszeiger an eine beliebige Stelle im Makro setzen und die Taste drücken. Kontrollieren Sie das Ergebnis in Tabelle2. Die Farben sowie die Formel sollten eins zu eins übertragen worden sein.

Zeichnen Sie jetzt das Makro auf, indem Sie folgende Arbeitsschritte nachvollziehen:

Klicken Sie im Menüband Entwicklertools auf den Befehl Makro aufzeichnen (Abbildung 1.22).

Bestätigen Sie den Dialog Makro aufzeichnen mit OK.

Abbildung 1.22: Die Ausgangssituation für die folgende Aufgabe.

Kopieren Sie in der Tabelle tbl_ErsteMakros den Bereich A1:A10.

Wechseln Sie zu Tabelle2 und setzen Sie den Mauszeiger in Zelle C1.

Klicken Sie im Menüband Start auf den Befehl Einfügen und anschließend auf das Symbol Werte (W).

Wechseln Sie zum Menüband Entwicklertools und wählen Sie den Befehl Aufzeichnung beenden.

Kontrollieren Sie das Ergebnis Ihrer Aufzeichnung, indem Sie über die Tastenkombination in die Entwicklungsumgebung wechseln.

Abbildung 1.23: Der eingefügte Bereich C1:C10 enthält keine Farben und keine Formeln.

Wenn Sie sich die Aufzeichnung von Makro2 einmal ansehen, werden Sie feststellen, dass der Makrorekorder auch hier verstärkt die Methode Select im Einsatz hat, die Sie ersatzlos streichen dürfen. Mit einem Einzeiler kommen wir aber leider dieses Mal nicht aus. Die korrigierte Fassung können Sie Abbildung 1.24 entnehmen.

Abbildung 1.24: Erst kopieren und dann entscheiden, was eingefügt werden soll.

Mithilfe der Methode PasteSpecial haben Sie die Möglichkeit, zu entscheiden, was Sie letztendlich aus dem kopierten Bereich übernehmen möchten. Über den Parameter Paste, gefolgt von der Zeichenfolge :=, können Sie elegant und bequem die dabei verfügbaren Konstanten aus dem Drop-down auswählen.

Haben Sie in Abbildung 1.24