4 Grundkonzepte der VBA- · PDF fileGrundkonzepte der VBA-Programmierung Microsoft Excel 97...

Grundkonzepte der VBA-Programmierung

Microsoft Excel 97 Programmierung 45

4 Grundkonzepte der VBA-Programmierung

Ziele dieses Kapitels:

¼� Sie erhalten eine Einführung in die Objektstruktur der Programmiersprache.

¼� Sie erfahren einiges über den Aufbau von VBA-Prozeduren und über dieverschiedenen Prozedurarten.

¼� Sie lernen, den Makro-Rekorder einzusetzen, um VBA-Code automatisch ineinem Modul aufzeichnen zu lassen.


46 © Microsoft GmbH 1997

VBA ist eine objekt- und ereignisorientierte Programmiersprache. Was mitEreignissen gemeint ist, haben Sie im letzten Kapitel bereits erfahren, indem Siekleine Prozeduren geschrieben haben, die auf Tabellenereignisse reagieren. DieTatsache, daß VBA und EXCEL automatisch bestimmte Aktionen registrierenund Ihnen die Möglichkeit bieten, individuell darauf zu reagieren, erleichtert dieEntwicklung von VBA-Lösungen ungemein. Noch wichtiger als der gezielteUmgang mit Programmereignissen ist das Verständnis der Objektstruktur. DieVBA-Programmierung besteht zu einem großen Teil aus der Manipulation vonObjekten. Zu wissen, was Objekte ausmacht und wie sie im Programmcodebehandelt werden, ist die Grundvoraussetzung für eine fehlerfreie und effektiveArbeit mit VBA.

4.1 Objekte

Der Vorteil einer objektorientierten Programmierung, wie sie VBA bietet, liegtdarin, daß die Elemente, die Sie über Ihren Programmcode manipulieren wollen,sehr leicht angesprochen und identifiziert werden können. Das setzt allerdingseine strikte Objekthierarchie voraus, an die Sie sich gewöhnen müssen. Zumbesseren Verständnis trägt ein Vergleich mit dem realen Leben bei, dennprinzipiell ist das, was Sie sich gemeinhin unter einem Objekt vorstellen, garnicht so weit von dem entfernt, was ein VBA-Objekt ausmacht.

Makler sprechen z.B. von Objekten, wenn Sie sich auf Grundstücke oder Häuserbeziehen. Daß auch Grundstücksobjekte einer Hierarchie unterworfen sind,merken Sie in dem Moment, in dem Sie versuchen, das Objekt näher zubezeichnen. Sie könnten dann z.B. eine Straße und eine Hausnummer angeben.Sehr präzise ist ein Straßenname natürlich nicht: In wievielen Städten wird eswohl eine "Große Straße" geben? Also geben Sie auch noch den Ort an. InDeutschland gehört eine Stadt wiederum zu einem Bundesland und dasBundesland zum Land Deutschland. Damit haben Sie bereits eine Reihe vonrealen Objekten und deren Hierarchie kennengelernt, die Sie auch nach untenweiter fortsetzen könnten. So gibt es in einem Haus eine oder mehrereWohnungen, in diesen Wohnungen leben Menschen, stehen Möbel usw. Wie Siesehen, lassen sich fast alle Dinge des täglichen Lebens durch eine Objekt-hierarchie beschreiben und identifizieren.

An dem Beispiel erkennen Sie auch, daß es verschiedene Objektgruppen gibt. Ineiner Stadt gibt es mehrere Straßen, in einer Straße mehrere Häuser. Zusammenergeben die einzelnen Objekte eine klar definierte Objektmenge: Häuser, Straßen,Städte usw. Das wirft das Problem auf, wie ein einzelnes Objekt exaktangesprochen werden kann. Um ein bestimmtes Haus zu finden, könnten Sie ineinem kleinen Ort z.B. folgende Beschreibung geben: In Adorf, erste Straße,zweites Haus. Das funktioniert in einer größeren Stadt nicht mehr. Sie habendeshalb die Möglichkeit, ein Haus namentlich zu beschreiben: Bstadt,Heinrichstraße, Hausnummer 12 (auch eine Hausnummer ist ein Name, da dieNummer 12 nicht unbedingt das zwölfte Haus in der Straße meint).



All diese Kennzeichnungsarten und -strukturen werden Sie in VBAwiederfinden. Einen Eindruck vermittelt folgende Abbildung, die diehierarchische Struktur der EXCEL-Objekte im Überblick zeigt:

Ausgehend vom APPLICATION-Objekt, was in diesem Fall EXCEL selbstkennzeichnet, können Sie z.B. zu einer geöffneten Arbeitsmappe (WORKBOOK-Objekt) und darin wiederum zu einem Tabellenblatt (WORKSHEET-Objekt)verzweigen. Als formales Trennzeichen zwischen den Verzweigungsebenenverwenden Sie in VBA einen Punkt (siehe nachfolgendes Beispiel). Objekt-gruppen, wie die Menge aller geöffneten Arbeitsmappen werden als Auflistungenbezeichnet. Um gezielt ein einzelnes Element in einer Auflistung anzusprechen,geben Sie entweder eine Indexnummer an oder den Namen des gewünschtenObjekts.

Angenommen, Sie hätten drei Arbeitsmappen in EXCEL geöffnet und wollteneine Änderung an der zweiten Mappe, die den Namen UMSATZ.XLS trägt,durchführen. Explizit wollen Sie Zelle B2 im zweiten Tabellenblatt mit derStandardbezeichnung TABELLE2 bearbeiten.

Abbildung 4.1: Übersicht über die EXCEL-Objekte



In der folgenden Abbildung sehen Sie die entsprechende Objekthierarchie.

Bei Verwendung der Indexmethode müßte Ihr VBA-Code zur Auswahl der Zellefolgendermaßen aussehen:

APPLICATION.WORKBOOKS(2).WORKSHEETS(2).ACTIVATE

RANGE("B2").SELECT

Diese Schreibweise wirft allerdings Probleme auf, wenn Sie weitereArbeitsmappen öffnen oder neue Tabellenblätter einfügen. Unter diesenVoraussetzungen stimmen womöglich die Indexnummern der Workbooks-und/oder Worksheets-Auflistungen nicht mehr. Besser ist es deshalb, immer mitnamentlichen Verweisen zu arbeiten. Zum gleichen Resultat wie das obigeBeispiel führt folgender Code, der nach Änderungen am Arbeitsbereich diegewünschte Zelle markiert:

APPLICATION.WORKBOOKS("UMSATZ.XLS").WORKSHEETS("TABELLE2").ACTIVATE

RANGE("B2").SELECT

4.1.1 Objekteigenschaften

Die Objekthierarchie ist nur ein Aspekt des gesamten Objektmodells, denn diebloße Beschreibung eines zu bearbeitenden Elements führt schließlich keinerleiÄnderungen herbei. Die eigentliche Manipulation eines Objekts erfolgt übergeeignete Objekteigenschaften und -methoden. Beide Begriffe lassen sich rechtgut anhand realer Objekte beschreiben.

Unter Eigenschaften versteht man im normalen SprachgebrauchAuszeichnungen, wie Größe, Farbe, Schnelligkeit usw. Bei genauererBetrachtung fällt auf, daß sich die genannten Merkmale nicht universell auf allerealen Objekte anwenden lassen. Ein Mensch kann schnell oder langsam sein, einHaus aber nicht. Es gibt auch Eigenschaften, die sowohl auf Menschen als auchauf Häuser zutreffen – z.B. die Größe.

Abbildung 4.2: Mit Hilfe des Range-Objekts lassen sich einzelne Zellen eines Tabellenblatts ansprechen



Prinzipiell läßt sich festhalten, daß alle Objekte einer Objektgruppe die gleichenEigenschaften haben können (theoretisch kann jedem Menschen ein Intelligenz-quotient zugeordnet werden und jedem Haus eine Farbe) und daß die möglichenEigenschaften von der Objektgruppe abhängen. Die Unterschiede zwischen denindividuellen Objekten ergeben sich durch die Wertzuweisungen. DieEigenschaft Farbe kann z.B. die Werte rot, grün oder blau annehmen.

Objekteigenschaften in VBA erfüllen denselben Zweck. Sie manipulieren einbestimmtes Merkmal des zuvor angegebenen Objekts. Wie Sie Wertzuweisungenim Programmcode verwenden, demonstriert das weiter unten folgende Beispiel.Zuvor soll noch der Unterschied zu den Objektmethoden hervorgehoben werden.

4.1.2 Objektmethoden

Das Gegenstück zu den eher als statisch zu charakterisierenden Objekt-eigenschaften sind die Objektmethoden, die einen eher aktiven Charakter haben.Grob gesehen lassen sie sich mit Objektanweisungen oder -tätigkeitengleichsetzen, was die Bezeichnung etwas verständlicher macht. Als Methodenbezeichnet man in VBA die Aktionen, die an Objekten vorgenommen werdenkönnen.

Auch in diesem Fall lassen sich Parallelen zum täglichen Leben ziehen.Menschen bieten da natürlich die meisten Vergleichsmöglichkeiten, da sie vieleTätigkeiten ausüben können, z.B. gehen, sprechen, essen, schlafen usw. Auchandere Realobjekte verfügen über bestimmte Methoden. Häuser können gebautwerden oder einstürzen, Autos können fahren oder bremsen usw. Wie bei denEigenschaften gilt, daß alle Objekte einer Objektklasse auf dieselbe Auswahl anMethoden zurückgreifen können. Aber nicht für alle Objektklassen stehendieselben Methoden zur Verfügung. Ein Mensch kann z.B. nicht einstürzen undein Haus kann nicht essen. Demgegenüber können alle Menschen essen und alleHäuser einstürzen.

Ein Beispiel veranschaulicht den Einsatz von Objekteigenschaften und-methoden am besten. Sie lernen dabei nicht nur den Unterschied zwischenEigenschaften und Methoden kennen, sondern auch die entsprechende VBA-Syntax.

Ziel:Sie geben ein VBA-Makro ein, in dem Sie den OFFICE-Assistenten mit Hilfeeiner Reihe von Eigenschaften und Methoden bearbeiten.

Lösungsweg:1. Legen Sie in EXCEL eine neue Arbeitsmappe an.

2. Rufen Sie mit r + k den Visual Basic-Editor auf.



3. Fügen Sie ein neues Modul in das VBA-Projekt der neuen Mappe ein, indemSie im Projekt-Explorer mit der rechten Maustaste auf den Projektnamenklicken und im daraufhin angezeigten Kontextmenü den Befehl EINFÜGEN →MODUL wählen.

4. Da sich der Cursor automatisch im zugehörigen Code-Fenster befindet,können Sie direkt mit der Code-Eingabe beginnen. Geben Sie folgendeProgrammzeilen ein:

SUB ASSISTENTOBJEKT() ASSISTANT.FILENAME = "GENIUS.ACT" ASSISTANT.REDUCED = FALSE

ASSISTANT.LEFT = 0 ASSISTANT.TOP = 0 ASSISTANT.VISIBLE = TRUE

APPLICATION.WAIT NOW + TIMEVALUE("00:00:03") ASSISTANT.MOVE 100, 200 ASSISTANT.ANIMATION = MSOANIMATIONCHARACTERSUCCESSMAJOR

END SUB

5. Setzen Sie den Cursor an eine beliebige Stelle innerhalb des ebeneingegebenen Programmcodes und drücken Sie e, um das VBA-Makroauszuführen.

6. Schließen Sie das Fenster des OFFICE-Assistenten manuell, nachdem er dieim Makro angegebene Aktion ausgeführt hat.

Abbildung 4.3: Mit geeigneten Eigenschaften und Methoden läßt sich der OFFICE-Assistent manipulieren



Ergebnis:Sie haben Eigenschaften und Methoden des OFFICE-Assistenten programmiert,um eine Animation auszulösen und das Fenster des Assistenten zu verschieben.

Anmerkung:In dem Beispiel sprechen Sie das ASSISTANT-Objekt an, mit dem Sie auf denOFFICE-Assistenten Bezug nehmen. Insgesamt haben Sie sechs Eigenschaftenund eine Methode des Assistenten verwendet.

Unter Berücksichtigung der oben genannten Erläuterungen werden Sie MOVE sehrschnell als die einzige hier eingesetzte Methode identifizieren. Sie führttatsächlich eine Aktion aus, während alle anderen Objektbearbeitungen nurbestimmte Merkmale des Assistenten definieren. Mit der FILENAME-Eigenschaftwird zunächst ein Assistent aus dem Katalog ausgewählt (GENIUS.ACTidentifiziert den Professor). Dadurch, daß Sie die REDUCED-Eigenschaft auf FALSE

(falsch) setzen, sorgen Sie dafür, daß das Fenster des Assistenten in dermaximalen Größe und nicht in der reduzierten Form angezeigt wird. LEFT undTOP positionieren das Fenster ganz oben links an den Bildschirmkoordinaten 0, 0.Erst dann machen Sie den Assistenten sichtbar, indem Sie die VISIBLE-Eigenschaftauf TRUE (wahr) setzen.

Dennoch würden Sie von der ursprünglichen Positionierung nicht viel sehen, dasofort die nächste Zeile abgearbeitet würde. Deshalb unterbricht dieeingeschobene WAIT-Methode (des APPLICATION-Objekts!) die Makroausführungfür 3 Sekunden. Anschließend verschiebt die MOVE-Methode (des ASSISTANT-Objekts) das Assistenten-Fenster an eine andere Position, und zum Schluß lassenSie den Assistenten mit der ANIMATION-Eigenschaft eine Aktion ausführen.

Generell bestehen die formalen Unterschiede zwischen Eigenschaften undMethoden darin, daß den Eigenschaften nach einem Gleichheitszeichen ein Wertzugewiesen wird – wobei es sich um Wahrheitswerte, Zahlenwerte, Konstantenoder auch um Zeichenfolge-Ausdrücke handeln kann – während die Objekt-methoden entweder für sich selbst stehen oder, wie im Fall von MOVE, mitParametern arbeiten. Bei der Betrachtung von Objektkennzeichnungen haben Sieübrigens schon zwei Methoden, die ohne Parameter auskommen, kennengelernt:ACTIVATE und SELECT.

Denken Sie bei der Programmierung daran, daß Sie in einer Programmzeileimmer nur eine einzige Eigenschaft oder eine einzige Methode verwendenkönnen. Jede Objektbearbeitung verlangt eine eigene Code-Zeile. Sie können einObjekt also nicht in derselben Zeile markieren und gleichzeitig formatieren.



4.1.3 Verweise

Als EXCEL-Programmierer können Sie nicht nur auf EXCEL-Objekte zugreifen.Sie haben eben schon mit einem Objekt gearbeitet, das gar nicht zu EXCELgehört: Der OFFICE-Assistent ist nicht in EXCEL, sondern in OFFICEintegriert, womit er in allen OFFICE 97-Anwendungen verfügbar ist.

VBA als standardisierte Entwicklungsumgebung erlaubt den Zugriff auf Objekteder unterschiedlichsten Anwendungen. Ermöglicht wird dies durch sogenannteObjektbibliotheken, die VBA mitteilen, welche Objekte in einer Anwendung zurVerfügung stehen und welche Eigenschaften und Methoden sie besitzen. Um ausder Programmierumgebung von EXCEL mit den Objekten einer anderenAnwendung arbeiten zu können, muß der Hersteller dieser Anwendung lediglicheine Objektbibliothek mitliefern, die von VBA interpretiert werden kann. WennSie das komplette OFFICE 97-Paket installiert haben, sind z.B. automatischBibliotheken für WORD, POWERPOINT, ACCESS und OUTLOOK auf IhreFestplatte kopiert worden.

Doch damit sind sie nicht sofort aus EXCEL heraus nutzbar. Sie müssen explizitangeben, welche Objektbibliotheken neben den Standard-Bibliotheken für dieEXCEL-Programmierung eingebunden werden sollen. Zu diesem Zweck stellenSie mit dem Befehl VERWEISE... aus dem Menü EXTRAS des Visual Basic-Editorseinen Verweis auf die gewünschte Bibliothek her. Anschließend haben SieZugriff auf alle darin enthaltenen Objekte inklusive ihrer Manipulations-möglichkeiten.

Ziel:Sie stellen einen Verweis auf die Objektbibliothek von OUTLOOK her.

Lösungsweg:1. Wählen Sie im Visual Basic-Editor im Menü EXTRAS den Befehl VERWEISE...

2. Suchen Sie im Listenfeld VERFÜGBARE VERWEISE den Eintrag MICROSOFT

OUTLOOK 8.0 OBJECT LIBRARY.

3. Aktivieren Sie das Kontrollkästchen für die OUTLOOK-Bibliothek.

4. Klicken Sie auf UU��VV.



Ergebnis:Sie haben die OUTLOOK-Objektbibliothek eingebunden und können im VBA-Code nun direkt auf die darin enthaltenen Objekte zugreifen.

Welche Objektbibliotheken in der Liste des Dialogfelds VERWEISE angezeigtwerden, richtet sich nach den auf Ihrem Rechner installierten Programmen, sodaß der Inhalt obiger Abbildung nicht unbedingt mit Ihren Bildschirminhaltenübereinstimmen muß.

4.1.4 Der Objektkatalog

Sofern Sie über entsprechende Programmierkenntnisse verfügen, können Sie dieObjektbezeichnungen aus der eben hinzugefügten OUTLOOK-Bibliothek sofortim VBA-Code einsetzen. Nur selten werden Sie auf Anhieb alle Objekte,Eigenschaften oder Methoden kennen. Und selbst bei EXCEL-Objekten wird esIhnen häufiger passieren, daß Zweifel an einzelnen Objektnamen bzw. dengültigen Eigenschaften und Methoden aufkommen. In diesem Fall hilft derObjektkatalog weiter, der einen schnellen Überblick über die Inhalte allergeladenen Bibliotheken verschafft. Dazu zählen übrigens auch die momentangeöffneten VBA-Projekte Ihrer EXCEL-Arbeitsmappen.

Ziel:Sie öffnen das Fenster des Objektkatalogs.

Abbildung 4.4: Verweis auf andere Objektbibliotheken



Lösungsweg:1. Wechseln Sie – falls nötig – in den Visual Basic-Editor.

2. Wählen Sie im Menü ANSICHT den Befehl OBJEKTKATALOG oder drücken Sie die

b-Taste.

Ergebnis:Sie haben den Objektkatalog geöffnet, in dem Sie die derzeit verfügbarenObjekte, ihre Eigenschaften und Methoden sowie anwendungsspezifischeFunktionen nachschlagen können.

Anfangs zeigt der Objektkatalog die Objektklassen und Elemente aller geladenenBibliotheken an. Falls Sie nur an den Inhalten einer einzelnen Bibliothekinteressiert sind, wählen Sie den gewünschten Eintrag aus dem Dropdown-FeldPROJEKT/BIBLIOTHEK aus. Daraufhin beschränken sich die Übersichten in denKlassen- und Element-Listenfeldern auf die zuvor getroffene Auswahl.

Ziel:Sie lassen sich die Einträge in der OUTLOOK-Objektbibliothek anzeigen.

Lösungsweg:1. Öffnen Sie das Dropdown-Feld PROJEKT/BIBLIOTHEK.

2. Klicken Sie den Eintrag OUTLOOK an.

Abbildung 4.5: Der Objektkatalog



Ergebnis:Der Objektkatalog enthält nur noch die Inhalte der OUTLOOK-Bibliothek.

Sie haben nun die Möglichkeit, Einträge in den Listenfeldern zu markieren, umsich nähere Informationen im Detailbereich am unteren Rand des Objektkatalogsanzeigen zu lassen. Die Elemente im rechten Listenfeld richten sich jeweils nachder im linken Listenfeld ausgewählten Objektklasse. Die Symbole neben denEinträgen geben Auskunft über die Art des Elements, was die Übersichtlichkeiterhöht und Verwechslungen zwischen eventuell gleichlautenden Bezeichnungenvon Objekten und Eigenschaften/Methoden ausschließt. Die folgende Tabelleführt die Symbole und ihre Bedeutungen auf:

Symbol Bedeutung

EIGENSCHAFT

STANDARDEIGENSCHAFT

METHODE

STANDARDMETHODE

EREIGNIS

KONSTANTE

MODUL

KLASSE

BENUTZERDEFINIERTER TYP

GLOBAL

BIBLIOTHEK

PROJEKT

INTEGRIERTE SCHLÜSSELWÖRTER UND TYPEN

AUFZÄHLUNG

Aus dem Detailbereich können Sie gegebenenfalls zu anderen Bibliotheken,Klassen oder Elementen verzweigen. Die jeweiligen Querverweise erscheinen ingrün und unterstrichen.



Außerdem erfahren Sie z.B. näheres über den Typ einer Eigenschaft oderEinzelheiten zu den Parameterwerten einer Methode. Sie könnten sich diezulässigen Konstanten der ANIMATION-Eigenschaft des ASSISTANT-Objekts anzeigenlassen.

Ziel:Sie schlagen im Objektkatalog die verfügbaren Konstanten der ANIMATION-Eigenschaft des ASSISTANT-Objekts nach.

Lösungsweg:1. Da Sie auch bei der Programmierung von OUTLOOK mit dem OFFICE-

Assistenten arbeiten können, brauchen Sie die zuvor eingestellte Objekt-bibliothek nicht zu wechseln. Markieren Sie im Listenfeld KLASSEN denEintrag APPLICATION.

2. Markieren Sie im rechten Listenfeld die Eigenschaft ASSISTANT.

3. Klicken Sie im Detailbereich auf den unterstrichenen Verweis ASSISTANT. DerObjektkatalog stellt daraufhin automatisch die Klassen der OFFICE-Bibliothek und die Elemente des ASSISTANT-Objekts dar.

4. Markieren Sie im rechten Listenfeld die Eigenschaft ANIMATION. ImDetailbereich erscheinen nähere Informationen zu der Eigenschaft.

5. Klicken Sie im Detailbereich auf den Verweis MSOANIMATIONTYPE.

Ergebnis:In der Elementliste des Objektkatalogs erscheinen alle verfügbarenAnimationsarten des OFFICE-Assistenten.

Abbildung 4.6: Im Objektkatalog finden Sie Konstanten, die Sie einer Eigenschaft zuweisen können



Anmerkung:Wenn Sie noch schneller ans Ziel kommen wollen oder nicht genau wissen, wieSie überhaupt zu einem bestimmten Element gelangen, machen Sie einfachGebrauch von der Suchfunktion des Objektkatalogs.

Dazu geben Sie das gesuchte Schlüsselwort im Feld SUCHTEXT (unter demDropdown-Feld PROJEKT/BIBLIOTHEK) ein und klicken anschließend auf dasSymbol (SUCHEN). Im Objektkatalog wird dann ein weiteres Unterfenster mitden Suchergebnissen eingeblendet, in dem Sie die aufgelisteten Elementemarkieren können, um sich nähere Informationen im Detailbereich anzeigen zulassen.

Ziel:Sie lassen das CALCULATE-Ereignis suchen, das Sie im letzten Kapitel bereitskennengelernt haben.

Lösungsweg:1. Wählen Sie im Dropdown-Feld PROJEKT/BIBLIOTHEK den Eintrag <ALLE

BIBLIOTHEKEN>, um die Suche nicht nur auf die momentan aktiveObjektbibliothek zu beschränken.

2. Geben Sie im Feld SUCHTEXT den Begriff Calculate ein.

3. Klicken Sie auf die Schaltfläche .

4. Markieren Sie im Unterfenster SUCHERGEBNISSE das Ereignis CALCULATE.

Ergebnis:Sie haben das CALCULATE-Ereignis ausfindig gemacht und können sich nähereInformationen im Objektkatalog ansehen.

Abbildung 4.7: Mit der Suchfunktion des Objektkatalogs erhalten Sie schnell Informationen zu einem Element



Um wieder in die normale Ansicht des Objektkatalogs umzuschalten, klicken Siedas Symbol (SUCHERGEBNISSE AUSBLENDEN) an. Die restlichen Symbol-schaltflächen des Objektkatalogs erklären sich durch die jeweiligen Quickinfos,weshalb wir sie nicht weiter berücksichtigen.

Verwenden Sie den Objektkatalog immer dann, wenn Sie Hilfe zu Objekten oderihren Eigenschaften/Methoden benötigen und Ihnen die INTELLISENSE-Funktionenim Code-Fenster, die ebenfalls alle verfügbaren VBA-Bezeichnungen auflisten,nicht ausreichen.

4.2 Ereignisse

Wie schon mehrfach erwähnt, zeichnet sich VBA auch durch eine ereignis-orientierte Programmierweise aus. Dieser Aspekt ist in VBA 5.0 deutlichverstärkt worden. Als Ereignisse bezeichnet man Aktionen, die an einem Objektvorgenommen werden und von dem Objekt erkannt werden. Dabei kann es sichz.B. um Benutzereingaben wie Mausklicks oder Tastatureingaben handeln oderum systeminterne Funktionen wie das Neuberechnen einer Tabelle.

VBA 5.0 stellt im Vergleich zur Vorgängerversion eine weit größere Auswahl anerkennbaren Ereignissen zur Verfügung, womit Sie noch flexibler auf bestimmteAktionen reagieren können. Alle Standard-Ereignisse lassen sich individuellprogrammieren. Dafür müssen Sie nicht einmal erst ein neues Modul einrichten,denn die von Ihnen erstellten Ereignisprozeduren sind fest mit dem jeweiligenObjekt verknüpft. Im letzten Kapitel hatten Sie z.B. zwei Ereignisse desWORKSHEET-Objekts programmiert. Es genügte, das Code-Fenster der Tabelle zuöffnen, das gewünschte Ereignis aus dem Dropdown-Feld PROZEDUR auszuwählenund den geeigneten VBA-Code einzugeben.

Praktisch und übersichtlich sind die objektbezogenen Ereignisprozeduren vorallem im Zusammenhang mit Formularen, deren Benutzerführung unterUmständen vollständig formularintern gesteuert werden kann. Im Code-Fensterdes Formulars können Sie z.B. genau festlegen, was passieren soll, wenn eineSchaltfläche angeklickt wird, wenn Eingaben in einem Textfeld vorgenommenwerden, wenn ein anderer Eintrag aus einem Listenfeld gewählt wird usw.Außerdem bietet VBA Initialisierungs- sowie Aktivierungs- und Deaktivierungs-ereignisse an, mit denen Sie das Formularverhalten beim Öffnen und Schließenregeln können.

Weitere Details werden Sie im Verlauf des Trainings anhand praktischerBeispiele kennenlernen. Schauen Sie sich gegebenenfalls noch einmal dasBeispiel zur Ereignisprogrammierung aus dem letzten Kapitel an, wenn SieFragen zur genauen Vorgehensweise haben.



4.3 Code-Strukturen

Sie wissen inzwischen, wie das Objektmodell von VBA aufgebaut ist und wieman Objekte mit Eigenschaften und Methoden manipuliert. Bislang sind wirallerdings noch nicht näher darauf eingegangen, wie die Anweisungenstrukturiert werden, damit sie überhaupt als zusammenhängender Programmcodeerkannt werden. Es genügt nicht, ein Modul anzulegen und beliebige VBA-Kommandos in das zugehörige Code-Fenster einzugeben. Sie müssen VBAzusätzlich mitteilen, wo Ihr VBA-Makro anfängt und wo es aufhört. In sichgeschlossene Programmblöcke werden als Prozeduren bezeichnet. Dabeiunterscheidet VBA zwischen SUB-, FUNCTION- und PROPERTY-Prozeduren. Diefolgenden Ausführungen beschäftigen sich vorerst nur mit den beiden erstenProzedurarten. PROPERTY-Prozeduren verlangen tiefgreifendere Programmier-kenntnisse, weshalb Sie entsprechende Erläuterungen erst in einem späterenKapitel finden werden.

Um die Eingabe eines Programmierbeispiels zu vereinfachen, werden Sie dieCodezeilen vom Makro-Recorder aufzeichnen lassen. Der resultierende VBA-Makro erlaubt dann einen ersten Blick auf die Code-Struktur von VBA.

4.3.1 Erste Schritte mit dem Makro-Recorder

Ein nützliches Werkzeug bei der Programmierung ist der Makro-Recorder vonEXCEL. Seine Funktion ist vergleichbar mit der eines Cassettenrecorders: Siestarten die Aufnahme und anschließend zeichnet der Makro-Recorder alleBefehle, die Sie über die Tastatur oder mit der Maus eingeben, auf. Sobald Siedie Aufzeichnung beenden, erscheint das Ergebnis in einem Modul des VBA-Projekts der aktiven Arbeitsmappe.

Der aufgezeichnete Makro läßt sich anschließend nach Belieben im Code-Fensterdes VBA-Editors bearbeiten oder ergänzen. Bei Bedarf können Sie auchTeilaufgaben eines größeren Projekts vom Makro-Recorder aufzeichnen lassen,um sie danach in eine komplexere Code-Struktur zu kopieren. Der Makro-Recorder kann auch erfahrenen Programmierern als Ausgangspunkt fürweitergehende VBA-Entwicklungen dienen, zumal er eine Menge Tipparbeiterspart.

Im Anschluß werden Sie den Makro-Recorder dazu einsetzen, ein VBA-Makroanzulegen, das als Grundlage für die weiteren Betrachtungen der Programmier-sprache dienen soll. Geben Sie zuerst einige Basisdaten in ein leeres Tabellen-blatt ein (z.B. in TABELLE1 der am Anfang des Kapitels geöffneten neuenArbeitsmappe).

Ziel:Sie richten eine Tabelle ein, um Bearbeitungen anschließend mit dem Makro-Recorder aufzeichnen zu können.



Lösungsweg:1. Wechseln Sie aus dem Visual Basic-Editor nach EXCEL.

2. Geben Sie die in der folgenden Abbildung gezeigten Zahlen- und Textwerteein, wobei Sie die Werte im Bereich B4:F7 als reine Zahlen eintragen. (Fürdie nachfolgenden Betrachtungen spielt es keine Rolle, ob die Gesamt-ergebnisse in Spalte F als fixe Zahlenwerte oder als Summenformeleingegeben werden.)

3. Formatieren Sie den Bereich B4:F7 im Währungsformat.

4. Speichern Sie die Datei mit Hilfe des Befehls DATEI → SPEICHERN UNTER... imunter dem Namen TRAINING.XLS. Sie werden auch im folgenden Kapitelnoch mit diesem Beispiel arbeiten.

Ergebnis:Sie haben eine Beispieltabelle für die folgenden Übungen eingerichtet.

Angenommen, Sie möchten die Quartalsergebnisse auswerten und dabei alleZellen rot und fett hervorheben, deren Werte geringer sind als imvorausgehenden Quartal. Da sich diese Aufgabe mehrfach wiederholt, bietet essich an, einen VBA-Makro aufzuzeichnen, der die Formatierung automatisch aufTastendruck ausführt. Mit dem Makro-Recorder ist das schnell erledigt.

Ziel:Sie zeichnen einen Makro zur Zellformatierung auf und weisen ihm eineTastenkombination zu.

Abbildung 4.8: Diese Beispieltabelle dient als Grundlage für die programmiertechnischen Erläuterungen



Lösungsweg:1. Setzen Sie den Cursor in die erste Zelle, deren Wert kleiner ist als der des

vorausgehenden Quartals (Zelle C4).

2. Wählen Sie im Menü EXTRAS den Befehl MAKRO und im Untermenü die OptionAUFZEICHNEN...

3. Füllen Sie das anschließende Dialogfeld folgendermaßen aus: MAKRONAME:RotFett, TASTENKOMBINATION: £ + U, BESCHREIBUNG: Zellformatierung - rotund fett.

4. Klicken Sie auf UU��VV. Von nun an werden Ihre Bearbeitungen aufgezeichnet.

5. Wählen Sie im Menü FORMAT den Befehl ZELLEN...

6. Klicken Sie die Registerkarte QQ��##**$$"",,RR an.

7. Markieren Sie im Feld SCHRIFTSCHNITT den Eintrag FETT, wählen Sie imDropdown-Feld FARBE den Eintrag ROT und klicken Sie auf UU��VV.

8. Klicken Sie in der kleinen Symbolleiste, die seit Beginn der Aufzeichnungeingeblendet wird, auf die Schaltfläche (AUFZEICHNUNG BEENDEN).

Ergebnis:Sie haben ein Makro aufgezeichnet, das eine Zelle fett und in rot formatiert.

Da Sie dem Makro eine Tastenkombination zugewiesen haben, können Sie esjederzeit auf Tastendruck ausführen. Setzen Sie den Cursor z.B. in Zelle E4 unddrücken Sie dann � + £ + 5. Ohne weitere Eingaben wird die Zellewunschgemäß formatiert.

Ziel der Makroaufzeichnung war es nicht, die Vorteile des Makro-Recordersherauszustellen. Ziel war es, die Code-Strukturen von VBA anhand einesBeispiels zu veranschaulichen. Wechseln Sie also in den Visual Basic-Editor undsehen Sie sich das Resultat der Aufzeichnung an. Dorthin gelangen Sie amschnellsten mit folgender Methode:

Abbildung 4.9: Dialog Neues Makro aufzeichnen



Ziel:Sie wechseln aus EXCEL in den Visual Basic-Editor und lassen sich den ebenaufgezeichneten Makro anzeigen.

Lösungsweg:1. Wählen Sie in EXCEL im Menü EXTRAS den Befehl MAKRO und im Untermenü

die Option MAKROS...

2. Markieren Sie in der Liste MAKRONAME den Eintrag ROTFETT (den Namen, denSie vor der Makroaufzeichnung festgelegt haben).

3. Klicken Sie auf UUMM!�!�**��!!$$,,!!((VV.

Ergebnis:Sie befinden sich im Visual Basic-Editor, in dem die Anweisungen desaufgezeichneten Makros in einem Code-Fenster angezeigt werden.

4.3.2 Sub-Prozeduren

SUB-Prozeduren setzen Sie ein, um bestimmte Aktionen auszuführen – in demaufgezeichneten Makro z.B., um eine Formatierung zuzuweisen. Im Gegensatzzum zweiten Prozedurtyp, den Funktionen, liefern SUB-Routinen keineAusgabewerte. Das ist zu beachten, wenn Sie mit VBA Berechnungendurchführen wollen. In Sub-Prozeduren könnten Sie höchstens einMeldungsfenster mit dem Ergebnis anzeigen lassen.

Abbildung 4.10: Beschreibungen und zugewiesene Tastenkombinationen eines aufgezeichneten Makros



Falls Sie das Ergebnis weiterverarbeiten wollen, sei es in einer anderen Prozeduroder in einer Tabelle, müssen Sie die Berechnungen innerhalb einer Funktiondurchführen.

Wenn Sie das zuvor aufgezeichnete Makro betrachten, erkennen Sie, daß es mitder Zeile

SUB ROTFETT()

eingeleitet wird. Das Schlüsselwort SUB kennzeichnet den Anfang einer Sub-Prozedur. Außerdem enthält die Zeile den Namen der Prozedur (= des Makros)sowie ein leeres Klammerpaar. Das Ende der Prozeduranweisungen, die unterdem Namen ROTFETT ausgeführt werden sollen, bildet die Zeile

END SUB

Damit wird jede SUB-Prozedur abgeschlossen, was gleichzeitig bedeutet, daß dieMakroausführung an dieser Stelle beendet wird. Nach einer END SUB-Zeile dürfenkeine weiteren Programmanweisungen in ein Modul eingegeben werden, es seidenn, sie sind in eine weitere Prozedur integriert oder es handelt sich umKommentarzeilen (Zeilen, die mit einem Hochkomma beginnen und bei derMakroausführung nicht berücksichtigt werden). Andersherum ausgedrücktbedeutet das, daß Sie mehrere Prozeduren in ein und dasselbe Modul eines VBA-Projekts eingeben können, solange sie korrekt eingeleitet und beendet werden.

Zusammenfassend ergibt sich folgende generelle Syntax für SUB-Prozeduren:

SUB PROZEDURNAME([ARGUMENTE]) ANWEISUNGEN

END SUB

ARGUMENTE ist in eckige Klammern gesetzt, da es sich um eine optionale Angabehandelt. Beispielsweise arbeitet die aufgezeichnete Prozedur ROTFETT ohneArgumente. Um zu verstehen, was mit Argumenten gemeint ist, müssen Siewissen, daß sich Prozeduren gegenseitig aufrufen können. In unserem Fallkönnten Sie das Makro ROTFETT etwa in ein anderes Makro einbinden, indemSie darin eine Zeile mit dem Makronamen einfügen. Denkbar wäre es dann, daßSie den Anwender in dem aufrufenden Makro zur Eingabe eines Zellbereichsauffordern, der mit den Befehlen aus der SUB-Prozedur ROTFETT formatiertwerden soll. Der angegebene Zellbereich könnte dann an die Prozedur übergebenwerden, so daß nicht wie im vorliegenden Fall automatisch die aktuelleMarkierung (Selection) sondern ein von der Benutzereingabe abhängiger Bereichformatiert würde. Damit diese Werteübergabe funktionieren kann, weisen Sie derSUB-Prozedur eine geeignete Argumenteliste zu. Deutlicher wird dieses Prinzipim nächsten Abschnitt, in dem Funktionen vorgestellt werden, die mitArgumenten arbeiten.



4.3.3 Funktionen (Function-Prozeduren)

Im Unterschied zu SUB-Prozeduren liefern FUNCTION-Prozeduren, die wir derEinfachheit halber einfach Funktionen nennen, Rückgabewerte. Diese Wertekönnen in anderen Prozeduren oder in einem Tabellenblatt weiterverarbeitetwerden. Somit unterscheiden sich FUNCTION-Prozeduren prinzipiell nicht von denin VBA oder EXCEL integrierten Funktionen. Sie könnten eine benutzer-definierte Funktion z.B. in einer SUB-Prozedur aufrufen, um ein Zwischen-ergebnis an die aufrufende Prozedur zurückzugeben. Oder Sie fügen dieFUNCTION-Prozedur genau wie eine normale Tabellenfunktion in eine Tabellen-zelle ein, um spezielle Berechnungen durchzuführen. Dabei können Sie Ihreeigenen Funktionen wie gewohnt über den Befehl FUNKTION... aus dem EXCEL-Menü EINFÜGEN in eine Tabelle einsetzen.

Im Gegensatz zu Sub-Prozeduren haben Funktionen folgenden syntaktischenAufbau:

FUNCTION PROZEDURNAME([ARGUMENTE]) ANWEISUNGEN

PROZEDURNAME = RÜCKGABEWERT

END FUNCTION

Eingeleitet werden sie durch das Schlüsselwort Function und das Ende derFunktion markiert die Zeile END FUNCTION. Wichtig ist, daß Funktionen eine Zeileenthalten müssen, in der dem Namen der Funktion ein Wert zugewiesen wird.Dies ist der Rückgabewert, der von der Funktion geliefert wird.

Ein einfaches Beispiel, in dem Sie per Funktion den Nettobetrag einesBruttowertes berechnen, soll den Umgang mit Funktionen veranschaulichen. AlsArbeitsumgebung dient wieder die zuvor angelegte Tabelle TRAINING.XLS, inder Sie das Makro ROTFETT aufgezeichnet hatten.

Ziel:Sie schreiben eine Funktion, die den Nettobetrag bei einem Mehrwertsteuersatzvon 15% berechnet.

Lösungsweg:1. Wechseln Sie in den Visual Basic-Editor und öffnen Sie das Code-Fenster mit

der aufgezeichneten Sub-Prozedur ROTFETT.

2. Fügen Sie unter der letzten Prozedurzeile eine leere Zeile ein.

3. Geben Sie folgenden Programmcode ein:

FUNCTION NETTO15(BRUTTOWERT) NETTO15 = BRUTTOWERT / 1.15END FUNCTION



4. Wechseln Sie in das Fenster von EXCEL und aktivieren Sie dieBeispieltabelle mit den Umsatzzahlen.

5. Markieren Sie Zelle B9 und geben Sie die Funktion =Netto15(B4) ein.

Ergebnis:EXCEL liefert als Ergebnis der Funktion den Nettobetrag 130078,2609.

Anmerkung:Das Beispiel zeigt gleich mehrere Verfahrensweisen. Erstens verdeutlicht es denEinsatz von Argumenten, die an eine Prozedur übergeben werden.

In diesem Fall verlangt die Funktion NETTO15 die Eingabe des ArgumentsBRUTTOWERT. Dabei kann es sich um einen Bezug auf die Zelle mit demBruttobetrag handeln. Ebensogut hätten Sie als Argument der Funktion einenreinen Zahlenwert eingeben können, z. B.: =Netto15(149590).

Zweitens sehen Sie am Funktionsaufbau, wie sich der Rückgabewert ergibt. Hierweisen Sie dem Funktionsnamen das Ergebnis der Berechnung BRUTTOWERT/1,15zu (in VBA arbeiten Sie aber immer mit dem amerikanischenDezimaltrennzeichen, dem Punkt). Sie verarbeiten also den an die Funktionübergebenen Argumentwert, um ein neues Ergebnis zu berechnen. Drittensdemonstriert das Beispiel den Einsatz einer benutzerdefinierten Funktion ineinem Tabellenblatt: FUNCTION-Prozeduren lassen sich genau wie integrierteTabellenfunktionen in eine Zelle eingeben.

Falls Sie auch mit einem Mehrwertsteuersatz von 7% arbeiten müssen, könnenSie eine zweite Funktion aufstellen, die das entsprechende Ergebnis liefert:

FUNCTION NETTO7(BRUTTOWERT) NETTO7 = BRUTTOWERT / 1.07END FUNCTION

Diese Funktion unterscheidet sich nur durch ihren Namen und durch dieBerechnungsformel von der Funktion NETTO15. Die beiden Funktionen erfüllenzwar ihre Aufgaben, besonders flexibel sind sie aber nicht. Praktischer wäre eineFunktion, die variable Mehrwertsteuersätze berücksichtigt. Zu diesem Zweckmüssen Sie die Argumentliste um ein zweites Argument, das denMehrwertsteuersatz übergibt, erweitern.

Ziel:Sie schreiben eine Funktion, die unter Berücksichtigung verschiedenerMehrwertsteuersätze den Nettobetrag ermittelt.



Lösungsweg:1. Wechseln Sie in das Code-Fenster mit der Funktion NETTO15 und fügen Sie

unter der END FUNCTION-Zeile eine leere Zeile ein.

2. Geben Sie folgenden Code ein:

FUNCTION NETTOX(BRUTTOWERT, SATZ) NETTOX = BRUTTOWERT / (1 + SATZ)END FUNCTION

3. Wechseln Sie wieder in das EXCEL-Fenster, aktivieren Sie gegebenenfallswieder die Beispieltabelle mit den Umsatzzahlen und markieren Sie ZelleC10.

4. Geben Sie 7% ein.

5. Markieren Sie Zelle B10 und geben Sie =NettoX(B4;C10) ein.

Ergebnis:EXCEL gibt den Wert 139803,7383 aus, den Nettobetrag des Wertes in Zelle B4bei einem Mehrwertsteuersatz von 7%.

Das Beispiel macht deutlich, wie Sie eine Funktion mit mehreren Argumentenentwickeln und einsetzen. Wenn Sie schon längere Zeit mit EXCEL arbeiten, istIhnen sicher bekannt, daß bei manchen integrierten Funktionen nicht unbedingtalle Argumente angegeben werden müssen. Auch das läßt sich mit FUNCTION-Prozeduren realisieren. Sie müssen dem entsprechenden Argument lediglich dasSchlüsselwort OPTIONAL voranstellen.

Angenommen, Sie verwenden standardmäßig einen Mehrwertsteuersatz von 15%und müßten nur in seltenen Fällen Nettobeträge mit 7% Mehrwertsteuerberechnen. Es wäre lästig, den Satz jedesmal als Argument an Ihre NETTO-Funktion zu übergeben. Mit einem optionalen Argument können Sie sich dieArbeit erleichtern:

Ziel:Sie schreiben eine NETTO-Funktion, die den Mehrwertsteuersatz nur bei Bedarfheranzieht.

Lösungsweg:1. Wechseln Sie wieder in das Code-Fenster mit den bislang eingegebenen

NETTO-Funktionen.



2. Fügen Sie unter der letzten Funktion eine Leerzeile ein und geben Sie dannfolgende Funktion ein:

FUNCTION NETTO(BRUTTOWERT, OPTIONAL SATZ) IF ISMISSING(SATZ) THEN SATZ = 0.15 NETTO = BRUTTOWERT / (1 + SATZ)END FUNCTION

3. Wechseln Sie wieder in die EXCEL-Tabelle mit den Umsatzzahlen.

4. Markieren Sie Zelle B11 und geben Sie die Formel =Netto(B4) ein.

5. Markieren Sie Zelle B12 und geben Sie =Netto(B4;C10) ein (vorausgesetztwird, daß C10 immer noch den Wert 7% enthält).

Ergebnis:Sie haben die Funktion NETTO einmal ohne das optionale Argument und einmalmit dem optionalen Argument eingegeben.

Anmerkung:Wie Sie im Programmcode erkennen, setzen Sie die VBA-Funktion ISMISSING ein,um zu überprüfen, ob das optionale Argument angegeben wurde oder nicht. AufAbfragen mit der IF-Funktion gehen wir im nächsten Kapitel näher ein. DieFunktion NETTO setzt einen Mehrwertsteuersatz von 15% (0,15) ein, wenn daszweite Argument fehlt, ansonsten behält das Argument den Wert, der an dieFunktion übergeben wurde.

4.3.4 Ausführen des Programmcodes

Wenn Sie eine Prozedur entwickelt haben, wollen Sie sie natürlich aucheinsetzen. Wie Funktionen im Rahmen einer EXCEL-Tabelle ausgeführt werden,haben Sie an den letzten Beispielen erfahren. SUB-Prozeduren können jedochnicht als Formeln in eine Tabellenzelle eingetragen werden. Um derartigeProzeduren auszuführen, müssen Sie explizit das Kommando zur Makro-ausführung geben. Zwei Methoden haben Sie bereits eingesetzt: Sie haben dieProzedur zur Manipulation des OFFICE-Assistenten gestartet, und Sie haben eineZelle mit dem aufgezeichneten Makro ROTFETT formatiert.

Solange Sie sich im Visual Basic-Editor befinden, können Sie eine SUB-Prozedurmit Hilfe der Taste e oder mit Hilfe der Symbolschaltfläche starten. Wennsich der Cursor in dem Moment gerade im Code-Fenster innerhalb einer SUB-Prozedur befindet, wird automatisch die entsprechende Prozedur ausgeführt.Ansonsten erscheint ein Dialogfeld, in dem Sie die zu startende Prozedurauswählen können. Markieren Sie einfach den Makronamen und klicken Sie aufUU��--++""LL##**!!((VV.



Die folgende Abbildung zeigt Ihnen die Vorgehensweise:

Zum Dialogfeld MAKROS gelangen Sie alternativ auch über das Menü EXTRAS undden Befehl MAKROS...

Nicht alle Prozeduren sollen direkt aus der Entwicklungsumgebung gestartetwerden. Wenn Sie erst einmal alle Prozeduren einer VBA-Lösung eingerichtethaben, ist es womöglich gar nicht wünschenswert, dem Anwender Einblicke inden Programmcode zu gestatten. Makros lassen sich deshalb auch aus EXCELheraus aufrufen. Dazu wählen Sie im EXCEL-Menü EXTRAS den Befehl MAKRO

und danach die Option MAKROS... Daraufhin erscheint das Fenster aus oben-stehender Abbildung, das in EXCEL zusätzlich eine Schaltfläche UU��XX,,$$))((!!((VVsowie einen Bereich BESCHREIBUNG enthält. Um einen Makro auszuführen, gehenSie genauso vor wie im Visual Basic-Editor.

Zur Beschleunigung des Makroaufrufs können Sie Tastenkombinationenzuweisen, wie Sie es bei der Aufzeichnung des Makros ROTFETT getan haben.Falls Sie eine SUB-Prozedur, die Sie komplett im Visual Basic-Editor geschriebenhaben, per Tastendruck aus EXCEL starten wollen, rufen Sie in EXCEL dasDialogfeld MAKROS auf und klicken auf die Schaltfläche UU��XX,,$$))((!!((VV. Sie erhaltendann ebenfalls Gelegenheit, ein Tastaturkürzel für die ausgewählte Prozeduranzugeben.

Damit stehen Ihnen die grundlegenden Informationen zum Schreiben undAusführen von VBA-Prozeduren zur Verfügung. Im nächsten Kapitel erfahrenSie mehr über komplexere Programmstrukturen, die mit variablen Wertenarbeiten und die die Makroausführung von Bedingungen abhängig machen.

Abbildung 4.11: In diesem Dialogfeld können Sie Makros zur Ausführung oder zur Bearbeitung auswählen

4 Grundkonzepte der VBA- · PDF fileGrundkonzepte der VBA-Programmierung Microsoft Excel 97...

Documents

Transcript of 4 Grundkonzepte der VBA- · PDF fileGrundkonzepte der VBA-Programmierung Microsoft Excel 97...