Addison Wesley - VBA Mit Excel

VBA mit Excel

Unser Online-Tippfr noch mehr Wissen

... aktuelles Fachwissen rund um die Uhr zum Probelesen,

Downloaden oder auch auf Papier.

www.InformIT.de

VBA mit Excel

B E R N D H E L D

RRRR

leicht

RRRR

klar

RRRR

sofort

Mit kleinen Makros zaubern

Copyright-Hinweis Markt+Technik VerlagCopyrightDaten, Texte, Design und Grafiken dieses eBooks, sowie die eventuell angebotenen eBook-Zusatzdaten sind urheberrechtlich geschtzt. Dieses eBook stellen wir lediglich als persnliche Einzelplatz-Lizenz zur Verfgung!Jede andere Verwendung dieses eBooks oder zugehriger Materialien und Informationen, einschliesslich der Reproduktion, der Weitergabe, des Weitervertriebs, der Platzierung im Internet, in Intranets, in Extranets, der Vernderung, des Weiterverkaufs und der Verffentlichung bedarf der schriftlichen Genehmigung des Verlags.Bei Fragen zu diesem Thema wenden Sie sich bitte an: [email protected]

ZusatzdatenMglicherweise liegt dem gedruckten Buch eine CD-ROM mit Zusatzdaten bei. Die Zurverfgungstellung dieser Daten auf unseren Websites ist eine freiwillige Leistung des Verlags. Der Rechtsweg ist ausgeschlossen.

4

Bibliografische Information Der Deutschen BibliothekDie Deutsche Bibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet ber http://dnb.ddb.de abrufbar.

Die Informationen in diesem Produkt werden ohne Rcksicht auf einen eventuellen Patentschutz verffentlicht.Warennamen werden ohne Gewhrleistung der freien Verwendbarkeit benutzt.Bei der Zusammenstellung von Texten und Abbildungen wurde mit grter Sorgfalt vorgegangen.Trotzdem knnen Fehler nicht vollstndig ausgeschlossen werden.Verlag, Herausgeber und Autoren knnen fr fehlerhafte Angabenund deren Folgen weder eine juristische Verantwortung nochirgendeine Haftung bernehmen.Fr Verbesserungsvorschlge und Hinweise auf Fehler sind Verlag undHerausgeber dankbar.

Alle Rechte vorbehalten, auch die der fotomechanischen Wiedergabe und derSpeicherung in elektronischen Medien. Die gewerbliche Nutzung der in diesem Produkt gezeigten Modelle und Arbeiten ist nicht zulssig.

Fast alle Hardware- und Softwarebezeichnungen, die in diesem Buch erwhnt werden,sind gleichzeitig auch eingetragene Warenzeichen oder sollten als solche betrachtet werden.

Umwelthinweis:Dieses Buch wurde auf chlorfrei gebleichtem Papier gedruckt.

10 9 8 7 6 5 4 3 2 1

06 05 04

ISBN 3-8272-6634-3

2004 by Markt+Technik Verlag,ein Imprint der Pearson Education Deutschland GmbH,Martin-Kollar-Strae 1012, D-81829 Mnchen/GermanyAlle Rechte vorbehaltenCoverkonzept: independent Medien-Design, Widenmayerstr. 16, 80538 MnchenCoverlayout: adesso 21, Thomas Arlt, MnchenTitelbild: getty imagesLektorat: Rainer Fuchs, [email protected]: Monika Weiher, [email protected]: Ulrich Borstelmann, DortmundDruck und Verarbeitung: Ksel, Kempten (www.KoeselBuch.de)Printed in Germany

Inhaltsverzeichnis

5

Inhaltsverzeichnis

Liebe Leserin, lieber Leser! ..............................9

1

Erste Schritte mit VBA 10

Wie gehe ich von Anfang an richtig vor? .......12Die Entwicklungsumgebung von Excel ..........17Neue Module einfgen .................................19Die Symbolleiste Bearbeiten .........................20Automatische Syntaxprfung .........................24Befehle in der nchsten Zeile fortsetzen ........25Automatische Anpassung der einzelnen Befehle ..........................................................26Der Objektkatalog .........................................27Die Testumgebung ........................................29Die kleine Erfolgskontrolle ............................31

2

Programmieren mit Zellen und Bereichen 32

Der Einsatz von Farben, Rahmen und Schriftschnitten ......................................34Rechenoperationen mit Excel ........................43Navigationsbeispiele .....................................49Markierungen auslesen .................................53Kopieren und Einfgen von Daten .................57Zellen oder Bereiche lschen ........................59Zellenfunktionen anwenden ..........................61Zellen ausschneiden .....................................64Zahlenformate festlegen und ermitteln ..........65Arbeiten mit Kommentaren ...........................72Bereiche und Zellen benennen .....................80Zellen bearbeiten ..........................................86Die kleine Erfolgskontrolle ............................93

6

3

Der Zeilen-/Spalten-Workshop 94

Zeilen und Spalten markieren .......................96Zeilenhhe und Spaltenbreite einstellen ......109Zeile(n) einfgen bzw. lschen ...................112Spalte(n) einfgen und lschen ...................119Zeilen aus- und einblenden .........................123Spalten ein- und ausblenden .......................125Zeilen filtern ...............................................127Die kleine Erfolgskontrolle ..........................131

4

Mit Tabellen in VBA zaubern 132

Tabellenbltter einfgen ..............................134Tabellenbltter benennen ............................137Tabelle(n) lschen .......................................138Tabellen aktivieren ......................................140Tabellen aus- und einblenden .....................141Kopf- und Fuzeilen anlegen ......................143Tabellenbltter drucken ..............................148Tabellenblatt als E-Mail versenden ..............150Die kleine Erfolgskontrolle ..........................151

5

Der Arbeitsmappen-Workshop 152

Arbeitsmappen anlegen ...............................154Arbeitsmappen speichern ............................156Arbeitsmappen ffnen .................................158Arbeitsmappen schlieen ............................165Arbeitsmappen lschen ...............................167Arbeitsmappen und Dokument-eigenschaften ..............................................168Arbeitsmappen und Verknpfungen ............171Die kleine Erfolgskontrolle ..........................173

Inhaltsverzeichnis

7

6

Diagramme auf Knopfdruck 174

Diagramme erstellen ...................................176Diagramme lschen ....................................184Diagramme als Grafiken speichern .............185Die kleine Erfolgskontrolle ..........................187

7

Der totale Automatismus mithilfe von Ereignissen 188

Ereignisse fr die Arbeitsmappe ...................190Ereignisse fr das Tabellenblatt ...................199Die kleine Erfolgskontrolle ..........................209

8

Eigene Funktionen schreiben 210

Benutzerdefinierte Funktionen ....................212Modulare Funktionen schreiben ..................224Die kleine Erfolgskontrolle ..........................231

8

9

In Interaktion mit Excel 232

Die Bildschirmmeldung Msgbox .................234Die Eingabemaske Inputbox ........................238Integrierte Dialoge einsetzen .......................242Eigene Dialoge entwerfen und programmieren ...........................................246Die kleine Erfolgskontrolle ..........................261

10

Die Kr Leisten programmieren 262

Der Leisten-Workshop ................................264Menleisten programmieren .......................266Die Programmierung von Kontextmens .....272Die Programmierung von Symbolleisten .....277Die kleine Erfolgskontrolle 281

Anhang Antworten 282

Stichwortverzeichnis 289

9

Sie werden sich vielleicht fragen, warum Sie berhaupt die Programmier-sprache VBA lernen sollen. Nun, das Programm Excel ist technisch sehr aus-gereift und Sie werden auf den ersten Blick nicht viel vermissen, was Sie frIhre tgliche Arbeit brauchen. Mit Hilfe von VBA-Makros lassen sich abergerade tgliche lstige Routinearbeiten weitestgehend automatisieren. Siehaben einmalig den Aufwand mit der Erstellung der dazu bentigten Ma-kros, spter profitieren Sie davon, indem Sie viel mehr Zeit fr andere Dingezur Verfgung haben. Auerdem werden Sie immer mehr auf programmierteExcel-Lsungen stoen, die immer wieder verstanden und weiter angepasstwerden mssen. Sie haben dann den Vorteil, wenn Sie diese Arbeiten selbstdurchfhren und nicht extern vergeben mssen.

Das Buch ist in mehrere Workshops gegliedert, die Sie nacheinander abarbeitensollten. Am Ende des Buches sollten Sie in der Lage sein, schon recht effektiv mitVBA-Makros umzugehen ob Sie nun Excel 97, 2000, 2002 oder 2003 nutzen.

Bei Fachfragen und allgemeinem Feedback zu meinem Buch erreichen Siemich unter meiner Excel-Homepage http://held-Office.de oder unter meinerE-Mail-Adresse [email protected]. Besuchen Sie auch ruhig einmal dasExcel-Diskussionsforum news:microsoft.public.de.excel oder das neue VBA-Forum von Markt+Technik unter www.mut.de/main/main.asp?page =vbaforum.Sie knnen durch das Lesen in diesen Foren eine ganze Menge lernen.

brigens biete ich Ihnen auch Auftragsprogrammierung und VBA-Schulun-gen an. Auf meiner Homepage finden Sie aktuelle Schulungsangebote, dievon privaten Coachings bis hin zu Inhouse-Schulungen und Hotel-Schulun-gen reichen.

Viel Spa beim Lesen und bei der Programmierung Ihrer Excel-Arbeits-mappen.

Bernd Held

Liebe Leserin, lieber Leser!

Kapitel 1

Erste Schritte mit VBA

Im ersten Kapitel dieses Buches lernen Sie das Handwerkzeug fr die Programmierung kennen und erfassen die ersten Makros.

Ihr Erfolgsbarometer

11

Das lernen Sie neu:

Wie gehe ich von Anfang an richtig vor? 12

Die Entwicklungsumgebung von Excel 17

Neue Module einfgen 19

Die Symbolleiste Bearbeiten 20

Automatische Syntaxprfung 24

Befehle in der nchsten Zeile fortsetzen 25

Automatische Anpassung der einzelnen Befehle 26

Der Objektkatalog 27

Die Testumgebung 29

12

Die ersten Gedanken beschftigen sich erst einmal mit der Frage, warumman berhaupt VBA lernen sollte. Dabei sind folgende Punkte von Wichtig-keit, die ich gleich zu Beginn dieses Buches mitteilen mchte.

Sie programmieren, weil:

Sie Ihre tgliche Arbeit automatisieren mchten.

Sie Excel um weitere Funktionen erweitern mchten.

Sie Arbeitssicherheit und Arbeitserleichterung erreichen mchten. Geradelstige Routinearbeiten knnen mit VBA sicher und elegant ausgefhrtwerden.

Sie erhhte Arbeitsgeschwindigkeit durch den Einsatz von VBA erreichenmchten.

Sie Ihre eigene Anwendungen entwickeln mchten, die auch von ande-ren leicht zu bedienen sind.

VBA eine universelle Sprache ist, die im ganzen Office-Paket verwendetwird. Auerdem stellen mehr und mehr andere Microsoft-unabhngigeAnwendungen auf VBA um.

Wie gehe ich von Anfang an richtig vor?

Wichtig ist, dass Sie sich vorher berlegen, welche Aufgabe Sie mit VBA l-sen mchten. Schreiben Sie sich das ruhig in ein paar Stichworten auf. WennSie dann spter beginnen zu programmieren, schadet es nichts, die einzel-nen Befehle im Code selbst zu beschreiben. Im Verlauf des Lernens werdenSie feststellen, dass solche Aufzeichnungen immer wieder weiterhelfen wer-den. Vieles, was in Vergessenheit gert, muss so nicht noch einmal erarbeitetwerden. Ein kurzer Blick auf die Aufzeichnungen gengt und Sie sind wiedervoll im Bilde.

Tipp

Am meisten knnen Sie aus der Benutzung des Makrorekorders ler-nen. Excel bietet die Mglichkeit, automatisch Programmcode auf-zeichnen zu lassen. Das luft dann so ab, dass Sie den Makrorekorderstarten und die Aufgabe zunchst manuell durchfhren. Haben SieIhre Aufgabe ausgefhrt, beenden Sie den Makrorekorder und sehensich die automatisch aufgezeichneten Zeilen einmal an.


13

Den Makrorekorder einsetzen

Mit Hilfe des Makrorekorders knnen Sie, wie schon gesagt, einzelne Ar-beitsschritte aufzeichnen. Dabei mssen Sie noch keine einzige Zeile VBA-Code programmieren. Diese Aufgabe wird von Excel im Hintergrund durch-gefhrt. Als erste Aufgabe schreiben Sie in Zelle A1 des Tabellenblatts

T

ABELLE

1

das heutige Datum und ziehen das Ausfllkstchen (links unten inZelle A1) bis in Zelle D1. Dazu gehen Sie wie folgt vor:

1

Whlen Sie aus dem Men E

XTRAS

den Befehl M

AKRO

/A

UFZEICHNEN.

Das erste Makro aufzeichnen

2 Belassen Sie den voreingestellten Namen des Makros.3 Belassen Sie den Eintrag DIESE ARBEITSMAPPE, wenn Sie das Makro in der momentan geffneten Arbeitsmappe ablegen mchten. Wenn Sie hingegen ein Makro aufzeichnen, welches immer fr jede Arbeitsmappe verfgbar sein soll, dann whlen Sie aus dem Dropdown den Eintrag PERSNLICHE MAKROARBEITSMAPPE, ansonsten behalten Sie den standardmig eingestellten Eintrag DIESE ARBEITSMAPPE bei. In diesem Fall knnen Sie die Makros nur nutzen, wenn Sie die entsprechende Arbeitsmappe auch geffnet haben.

4 Starten Sie die Aufzeichnung mit einem Klick auf OK.5 Geben Sie in Zelle A1 das heutige Datum ein.6 Ziehen Sie das Ausfllkstchen von Zelle A1 nach rechts bis in Zelle D1.

7 Klicken Sie auf das Symbol AUFZEICHNUNG BEENDEN.

14

8 Schauen Sie sich das Ergebnis des Makrorekorders an, indem Sie den Befehl EXTRAS/MAKRO/ MAKROS auswhlen.

9 Markieren Sie das gerade aufgezeichnete Makro im Listenfeld.10Klicken Sie im Dialog Makro auf die Schaltflche BEARBEITEN.

Das erste aufgezeichnete Makro

Die Aufzeichnung des Makrorekorders ergnzenSie sehen schon, die Programmiersprache in VBA ist englisch. Jedes Makrofngt mit der Anweisung Sub an. Danach folgt ein Leerzeichen, gefolgt von ei-nem Namen, den Sie frei whlen knnen. Abschlieend geben Sie ein Klam-mernpaar ein und drcken auf (). Excel ergnzt Ihnen nun automatisch dieSchlusszeile des Makros mit der Zeile End Sub. Vermeiden Sie bei der Be-nennung Leer- und Sonderzeichen. Es empfiehlt sich, den Makros sprechendeNamen zu geben. So knnte ein sprechender Name fr das obige Makro lau-ten: Sub DatumEingebenUndAusfllen(). Damit wird schon im Titel desMakros klar, welche Aufgabe das Makro hat. Die Zeilen im Code, welche mit

Hinweis

Mchten Sie das Makro nun direkt aus der Entwicklungsumgebungheraus starten, dann setzen Sie den Mauszeiger auf die erste Zeile desMakros und drcken die Taste (F5).


15

einem einfachen Anfhrungszeichen beginnen, sind Kommentarzeilen. Stan-dardmig werden dabei der Name des Makros, das Erstelldatum und derMakro-Aufzeichner auf diese Art und Weise festgehalten. Der Makrorekorderliefert wertvolle Hinweise ber die Syntax der einzelnen Befehle, leider ver-schluckt er aber auch einige davon bzw. zeichnet Befehle auf, die gar nichtbentigt werden. Aus diesem Grund kann das nur der erste Schritt sein, um einMakro zu erstellen. Es ist auf jeden Fall noch Nacharbeit notwendig. Das obi-ge erste Makro knnte nach ein wenig berarbeitung wie folgt aussehen:

Sub Makro1()' Makro1 Makro' Makro am 20.03.2002 von Held aufgezeichnet

'Ausgangszelle angeben Range("A1").Select'Das aktuelle Datum eingeben ActiveCell.FormulaR1C1 = Date'Das Datum nach rechts ausfllen (Schrittweite 1 Tag) Selection.AutoFill Destination:=Range("A1:D1"), _ Type:=xlFillDefault Range("A1:D1").SelectEnd Sub

Listing 1.1: Das Makro wurde mit Kommentaren ergnzt

Wie Sie sehen, wurde das Makro um zustzliche Kommentarzeilen ergnzt,die den Zweck des Makros beschreiben. Auerdem wurde das heutige Da-tum mit der Funktion Date getauscht. Diese Standardfunktion liefert immerdas aktuelle Datum. Das angepasste Makro stellt immer noch nicht den Ide-alzustand dar, im Moment soll uns das aber reichen.

Wie starte ich ein Makro?Zum Starten eines Makros haben Sie mehrere Mglichkeiten:

Klicken Sie in der Entwicklungsumgebung in der Symbolleiste VOREIN-STELLUNG auf das Symbol MAKRO AUSFHREN.

Starten Sie das Makro vom Tabellenblatt aus ber den Befehl EXTRAS/MAKRO/MAKROS und die Auswahl des Makros im Listenfeld mit abschlie-endem Klick auf die Schaltflche AUSFHREN.

Starten Sie das Makro direkt im Codefenster, indem Sie den Mauszeigerauf die erste Zeile des Makros setzen und die Taste (F5) drcken.

Starten Sie das Makro ber eine Schaltflche auf Ihrem Tabellenblatt.

16

Um ein Makro ber eine Schaltflche auf Ihrem Tabellenblatt zu starten, ver-fahren Sie wie folgt:

1 Blenden Sie die Symbolleiste FORMULAR ein.2 Klicken Sie auf das Symbol SCHALTFLCHE und ziehen Sie diese auf Ihrer Tabelle an der gewnschten Position auf.

3 Klicken Sie im Dialog MAKRO ZUWEISEN auf das Makro MAKRO1 und dann auf die Schaltflche OK.

4 Geben Sie der Schaltflche einen geeigneten Namen.

Wie erfahre ich mehr ber die einzelnen Befehle?Der zweite Schritt bei der Programmierung sollte sein, mehr ber die ver-wendeten Befehle zu erfahren. Dazu knnen Sie die eingebaute Online-Hil-fe in Anspruch nehmen.

1 Setzen Sie im Listing einmal den Mauszeiger auf den Befehl DATE.2 Drcken Sie die Taste (F1).

Mehr Infos ber Befehle einholen

Die Entwicklungsumgebung von Excel

17

Die Entwicklungsumgebung von ExcelWie Sie bereits vorher erfahren haben, knnen Sie in die Entwicklungsum-gebung wechseln, indem Sie die Tastenkombination (Alt) + (F11) drcken.Alternativ zu dieser Vorgehensweise knnen Sie auch aus dem Men EXTRASden Befehl MAKRO/VISUAL-BASIC-EDITOR auswhlen, um in die Entwicklungs-umgebung zu gelangen.

Die Entwicklungsumgebung in Excel

In der linken oberen Ecke sehen Sie den Projekt-Explorer, der alle geffnetenArbeitsmappen VBAPROJECT (MAPPE1) sowie die darin enthaltenen Tabellenanzeigt. Sehen Sie sich das Eigenschaften-Fenster direkt unterhalb desProjekt-Explorers einmal an. Je nachdem, welches Objekt Sie im Projekt-Explorer markiert haben, werden im Eigenschaften-Fenster dazugehrende

Hinweis

Wenn Sie einen Klick auf den Hyperlink BEISPIEL ausfhren, dann kn-nen Sie sich ein Beispiel zu der Funktion ansehen.

18

Eigenschaften angezeigt. Diese Eigenschaften z.B. fr eine Tabelle knnenSie direkt einstellen, ohne ein Makro schreiben zu mssen.

Im Eigenschaften-Fenster knnen Sie die Einstellungen an den Komponenteneinstellen. So knnen Sie zum Beispiel in einer Tabelle den Bereich festle-gen, in dem der Anwender sich in der Tabelle bewegen kann.

Um beispielsweise einen festen Bereich A1:D20 einzustellen, verfahren Siewie folgt:

1 Whlen Sie im Projekt-Explorer den Eintrag TABELLE1.2 Setzen Sie im Eigenschaften-Fenster den Mauszeiger in das Feld SCROLLAREA.3 Geben Sie dort den Zellenbezug $A$1:$D$20 ein.4 Besttigen Sie mit OK.

Einen begrenzten Bereich definieren

Neue Module einfgen

19

Neue Module einfgenUm berhaupt programmieren zu knnen, mssen Sie zuerst einmal ein Mo-dulblatt einfgen. Dazu gehen Sie wie folgt vor:

1 Klicken Sie im Projekt-Explorer mit der rechten Maustaste und whlen aus dem Kontextmen den Befehl EINFGEN/MODUL.

2 Geben Sie nun Ihr erstes Makro im Code-Fenster ein. Orientieren Sie sich dabei an der folgenden Abbildung. Es reicht dabei, wenn Sie lediglich die erste Zeile erfassen und mit () besttigen. Excel ergnzt automatisch den Rest des Pro-grammrahmens.

Ein neues Makro anlegen

Klar, das Makro macht momentan noch gar nichts. Wie wre es, wenn dasMakro Ihnen die Uhrzeit anzeigen wrde?

Tipp

Sie haben jetzt nur die Mglichkeit, innerhalb der Zellen A1:D20 Ein-gaben vorzunehmen. Alle anderen Zellen sind fr Sie nicht aktivierbar.

20

Dazu erfassen Sie innerhalb des Makro die folgende Zeile:

Msgbox Time

Setzen Sie den Mauszeiger auf die erste Zeile des Makros und drcken dieTaste (F5). Als Ergebnis sehen Sie folgende Meldung auf dem Bildschirm:

Die Funktion Time meldet Ihnen die aktuelle Uhrzeit. ber die FunktionMsgbox geben Sie diese Zeitangabe auf dem Bildschirm aus.

Die Symbolleiste BearbeitenDie Symbolleiste BEARBEITEN enthlt Funktionen, die Ihnen helfen sollen, denProgrammcode schnell und sicher zu bearbeiten.

Die Symbolleiste Bearbeiten

Auf die wichtigsten Funktionen dieser Symbolleiste mchte ich kurz einge-hen.

Einzge vergrern bzw. verkleinernMit dieser Funktion knnen Sie einzelne Zeilen oder auch mehrere Zeilenauf einmal nach links einrcken. Dies macht den Programmcode leichterlesbar.

Analog zur vorherigen Funktion knnen Sie mit der Funktion EINZUG VERKLEI-NERN eingerckte Programmteile wieder nach links rcken und pro Klick je-weils den markierten Text um einen Tabstopp zurcksetzen.

Um einen Text bzw. auch einzelne Befehle einzurcken, verfahren Sie wiefolgt:


21

1 Markieren Sie die Zeilen, die Sie einrcken mchten.2 Klicken Sie danach auf eines der beiden Symbole EINZUG VERGRERN bzw. EINZUG VERKLEINERN.

Haltepunkte setzenWenn Sie ein Makro starten, welches einen Haltepunkt aufweist, dannstoppt es genau an diesem Haltepunkt. Hiermit knnen Sie Programm-zwischenstnde abchecken.

Um diese Funktion an einem Beispiel zu ben, wird im nchsten Makro derZellenbereich A1:A10 durchlaufen werden. Geben Sie dazu einmal folgen-den Code ein:

Sub Schleifendurchlufe()Range("A1").SelectFor i = 1 To 10 Debug.Print ActiveCell.Address ActiveCell.Offset(1, 0).SelectNext IEnd Sub

Listing 1.2: Schleifendurchlufe berwachen

Dabei wird der Mauszeiger zu Beginn des Makros auf Zelle A1 gesetzt.Danach wird eine Schleife durchlaufen, was genau zehn Mal wiederholtwird. ber die Anweisung Debug.Print geben Sie jeweils die Zellenadres-se im Direktfenster der Entwicklungsumgebung aus. Danach aktivieren Siedie nchste Zelle ber den Befehl

ActiveCell.Offset(1, 0).Select .

Bevor Sie dieses Makro jedoch starten, fgen Sie einen Haltepunkt ein. Da-bei gehen Sie wie folgt vor:

1 Setzen Sie den Mauszeiger zu Beginn der Zelle Activecell.Offset(1,0).Select.

2 Drcken Sie die Taste (F9), um den Haltepunkt zu setzen.

22

Haltepunkt setzen

3 Starten Sie jetzt das Makro, indem Sie den Mauszeiger auf die erste Zeile des Makros setzen und die Taste (F5) drcken.

4 Das Makro stoppt dann genau am Haltepunkt. Die Zeile mit dem Haltepunkt wird jetzt gelb hinterlegt. Sie knnen nun beispielsweise auf Ihre TABELLE1 wechseln und kontrollieren, ob der Mauszeiger auch korrekt nach unten wandert. Dazu kli-cken Sie das Symbol ANSICHT MICROSOFT EXCEL in der linken oberen Ecke der Ent-wicklungsumgebung.

5 Begeben Sie sich zurck in Ihre Entwicklungsumgebung, indem Sie die Tasten-kombination (Alt) + (F11) drcken.

6 Drcken Sie abermals die Taste (F5), um das Makro fortzusetzen. Mit jedem Schleifendurchlauf wird der Zellenzeiger genau eine Zeile nach unten versetzt.

Hinweis

ber die Anweisung Debug.Print in der Schleife haben Sie diejeweilige Zellenadresse im Direktfenster ausgegeben. Das Testfensterblenden Sie ein, indem Sie aus dem Men ANSICHT den Befehl DIREKT-FENSTER auswhlen.


23

Zellenkoordinaten ausgeben

Kommentare im Code hinterlegenHinterlegen Sie bitte ausreichend Kommentare in Ihren Makros. Es fllt Ih-nen dadurch spter leichter, die einzelnen Befehle nachzuvollziehen. Auchnderungen am Makro selbst knnen auf diese Art und Weise festgehaltenwerden.

Einen Kommentar hinterlegen Sie, indem Sie ein einfaches Anfhrungszei-chen vor den eigentlichen Befehl oder Text eingeben. Die Befehlszeilenimmt dann standardmig die Schriftfarbe grn an. Diese so kommentier-ten Zeilen werden beim Makrolauf nicht ausgewertet. Sie knnen ganzeKommentarzeilen anlegen oder auch innerhalb einer Zeile am Ende einenKommentar anfgen.

Eine der am hufigsten gebrauchten Funktionen ist die Funktion fr dasschnelle Auskommentieren von Makrobefehlen. Wenn Sie im spteren Ver-lauf des Buches richtig anfangen zu programmieren, werden Sie mit Sicher-heit auch mal etwas auf die Schnelle probieren wollen. Dazu werden Sie abund zu auch einzelne bzw. mehrere Zeilen vorbergehend deaktivieren.Klar, die schnellste Methode ist, die Zeilen zu lschen. Der Nachteil daran

Tipp

Bei lngeren Makros empfiehlt es sich, mehrere Haltepunkte zu set-zen. Einen Haltepunkt knnen Sie brigens wieder entfernen, indemSie den Mauszeiger darauf setzen und die Taste (F9) drcken.

24

ist, dass diese Zeilen dann weg sind. Einfacher ist es, die momentan nichtgebrauchten Zeilen als Kommentar zu definieren.

Dazu gehen Sie wie folgt vor:

1 Geben Sie am Anfang der Zeile ein einfaches Anfhrungszeichen mit der Tasten-kombination () + (#) ein. Damit wird die Zeile als Kommentar betrachtet und Excel ignoriert diese Zeile bei der Programmausfhrung. Sicher, wenn es sich um einzelne Zeilen handelt, geht das auch ganz gut so umstndlicher wird es aber, wenn Sie gleich blockweise Zeilen auskommentieren mchten.

2 Um gleich mehrere Zeilen zu kommentieren, markieren Sie den Bereich, den Sie auskommentieren mchten.

3 Klicken Sie auf das Symbol BLOCK AUSKOMMENTIEREN. Standardmig formatiert Excel auskommentierte, also von Excel ignorierte Zeilen mit der Schriftfarbe grn.

Automatische SyntaxprfungDer VBA-Editor untersttzt Sie schon bei der Eingabe von Befehlen sehr tat-krftig. Sehen Sie sich dazu einmal das nchste Beispiel an.

Hinweis

Mchten Sie hingegen einzelne Zeilen oder auch einen ganzen Blockwieder aktiv werden lassen, dann markieren Sie die entsprechende(n)Zeile(n) und klicken auf das Symbol AUSKOMMENTIERUNG DES BLOCKSAUFHEBEN.

Befehle in der nchsten Zeile fortsetzen

25

Automatische Syntaxprfung

Begonnen haben Sie im Makro TabelleHinzufgen damit, das ObjektWorkSheets einzugeben. Sobald Sie den Punkt eintippen, bietet Ihnen derVBA-Editor an, was er an Befehlen genau fr dieses Objekt im Angebot hat.Sie brauchen nichts weiter zu tun, als im obigen Beispiel die Methode ADDaus dem Dropdown zu whlen. Mit diesem Automatismus stellt der VBA-Editor sicher, dass Sie keine Eigenschaften oder Methoden verwenden, dieden Objekten fremd sind.

Befehle in der nchsten Zeile fortsetzenSelbstverstndlich knnen Sie lngere Befehlsketten in einer Zeile eingeben.Der Nachteil daran ist, dass Sie irgendwann so weit nach rechts scrollenmssen, dass Sie den Anfang der Zeile nicht mehr sehen. Hier empfiehltsich, ber ein Trennzeichen dem Editor mitzuteilen, dass der Befehl in dernchsten Zeile weitergeht.

Hinweis

Grobe syntaktische Fehler bemerkt Excel, sobald Sie die Taste ()drcken. Die entsprechende Zeile wird dann in roter Schriftfarbe dar-gestellt und es erscheint eine Meldung auf dem Bildschirm, welchedie Fehlerursache bekannt gibt.

26

Auch hier leistet die automatische Syntaxprfung in Excel hervorragendeHilfe, denn nicht jeder Befehl lsst sich an einer beliebigen Stelle trennen.Um einen Befehl in der nchsten Zeile fortzusetzen, verfahren Sie wie folgt:

1 Drcken Sie am Ende der Zeile zunchst die Leertaste und geben anschlieend das Unterstrich-Zeichen _ ein. Damit wei der Editor, dass der Befehl in der nchs-ten Zeile fortgesetzt werden muss.

2 Schreiben Sie in der nchsten Zeile weiter.

Fortsetzungszeichen einsetzen

Das Verkettungszeichen & wird in der Meldung verwendet, um einen weite-ren Befehl, nmlich den zur Ermittlung des Blattnamens, einzubauen.

Automatische Anpassung der einzelnen BefehleSicher haben Sie schon gemerkt, dass der Editor Ihre eingegebenen Befehleautomatisch umsetzt. Wenn Sie beispielsweise folgende Zeile eingeben:

worksheets("tabelle1").range("a1").select

Hinweis

Selbstverstndlich brauchen Sie dieses Zeichen nur, wenn Sie einenzusammengehrigen Befehl zwecks der besseren Lesbarkeit in zweiZeilen ausgeben mchten.

Der Objektkatalog

27

erfolgt folgende Umsetzung:

WorkSheets("Tabelle1").Range("A1").Select

Bei allen Befehlen, die Excel bekannt sind, werden jeweils die Anfangsbuch-staben grogeschrieben. Geben Sie deshalb ruhig alle Befehle in der Klein-schreibweise ein. An der automatischen Umsetzung erkennen Sie dann, obbei den eingegebenen Befehlen auch keine Schreibfehler gemacht wurden.

Eine weitere automatische Anpassung erkennen Sie, wenn Sie zum Beispielfolgende Zeile eingeben:

ergebnis=1000*1,016

Dabei werden zwischen den Operatoren automatisch Leerzeichen einge-fgt:

Ergebnis = 1000 * 1,016

Der ObjektkatalogDie Entwicklungsumgebung stellt Ihnen einen Objektkatalog zur Verfgung,in dem Sie sich ber Objekte, Methoden und Eigenschaften informieren kn-nen. Lassen Sie mich an dieser Stelle etwas ber die gerade genanntenFachwrter sagen.

Hinweis

Dieser Automatismus hat jedoch keine funktionale Bedeutung, erdient lediglich der bersichtlicheren Darstellung der Befehle.

Was verbirgt sich hinter Objekten, Eigenschaften und Methoden?

Objekte: Als Objekt bezeichnet man alle Teile, die Sie in Excelsehen knnen. Die wichtigsten Objekte sind die Arbeitsmappe,das Tabellenblatt, die Zeilen bzw. Spalten und die Zelle alskleinste Einheit in Excel.

Eigenschaften: Darunter verbergen sich die Merkmale einesObjektes. So ist zum Beispiel die Formatierung einer Zelle eineEigenschaft des Objekts Zelle.

28

Den Objektkatalog ntzen

Wie wird der Objektkatalog aufgerufen? Dazu haben Sie mehrere Mg-lichkeiten:

Drcken Sie die Taste (F2). Klicken Sie in der Symbolleiste VOREINSTELLUNG auf das Symbol OBJEKT-

KATALOG.

Klicken Sie im Code-Fenster mit der rechten Maustaste und whlen ausdem Kontextmen den Befehl OBJEKTKATALOG.

Whlen Sie den Menbefehl ANSICHT/OBJEKTKATALOG.

Methoden: Wenn wir von Methoden sprechen, fragen Sie sicham besten immer: Was kann ich mit den einzelnen Objektenmachen? Angewandt auf eine Arbeitsmappe wren das dieMethoden fr das ffnen, Drucken, Speichern und Schlieen.

Die Testumgebung

29

Die TestumgebungIm Verlauf der Programmierung wird es nicht ausbleiben, dass Fehler auftre-ten. Wichtig ist aber immer zu wissen, wie weit das Programm lief, und vorallem, ob es bis zum Fehler richtig lief.

Hilfe im DirektfensterSie knnen beispielsweise bestimmte Inhalte von Variablen, die Sie imnchsten Kapitel kennen lernen werden, im Direktfenster ausgeben. ErfassenSie zunchst einmal einen kleinen Beispielcode.

Sub Testmakro()Dim i As IntegerFor i = 1 To 10 Debug.Print "Schleifendurchlauf: " & INext IEnd Sub

Listing 1.3: Schleife mehrmals durchlaufen

Im obigen Makro wird eine Schleife genau zehn Mal durchlaufen. Bei jedemSchleifendurchlauf wird dabei ein Eintrag ins Direktfenster geschrieben wer-den. Setzen Sie den Mauszeiger auf die Sub-Zeile und drcken die Taste(F5), um das Makro zu starten. Kontrollieren Sie jetzt einmal das Ergebnisim Direktfenster. Dabei befolgen Sie folgende Arbeitsschritte:

1 Whlen Sie den Menbefehl ANSICHT/DIREKTFENSTER oder drcken Sie die Tas-tenkombination (Strg) + (G).

2 Das Direktfenster wird daraufhin angezeigt.

Hinweis

Alle in VBA zur Verfgung stehenden Objekte werden in Bibliothekenverwaltet. Standardmig ist im ersten Dropdown-Men der EintragALLE BIBLIOTHEKEN ausgewhlt.

30

Abbildung 1.13: Makroablufe im Direktfenster kontrollieren

Der Schleifenzhler i wird bei jedem Schleifenzhler hoch gezhlt und imDirektfenster ausgegeben.

Wenn Sie viel mit dem Direktfenster arbeiten, dann sollten Sie die Inhaltedes Direktfensters auch hin und wieder lschen. Dazu verfahren Sie wiefolgt:

1 ffnen Sie das Direktfenster.2 Markieren Sie die Texteintrge im Direktfenster.3 Drcken Sie die Taste (Entf).4 Schlieen Sie das Direktfenster, indem Sie auf das Kreuzsymbol des Direktfens-ters (rechts oben) klicken.

Code Schritt fr Schritt durchlaufen lassenEine weitere Mglichkeit der Fehlersuche in Excel ist es, das Makro Zeile frZeile abzuarbeiten. Dabei gehen Sie folgendermaen vor:

Die kleine Erfolgskontrolle

31

1 Setzen Sie den Mauszeiger auf die erste Zeile des Makros.2 Drcken Sie die Taste (F8). Alternativ dazu knnen Sie ebenso den Menbefehl DEBUGGEN/EINZELSCHRITT whlen. Das Makro hlt nun nach jedem Befehl an. Sie knnen dann prfen, ob das Makro auch das Gewnschte ausfhrt, indem Sie die Entwicklungsumgebung kurzfristig verlassen und auf Ihrem Tabellenblatt die Ergeb-nisse kontrollieren.

Die kleine ErfolgskontrolleZum Abschluss dieses Kapitels beantworten Sie bitte die folgenden Fragen:

1. Wie knnen Sie den Makrorekorder einsetzen?

2. Welche VBA-Funktion liefert Ihnen das aktuelle Tagesdatum?

3. Wie knnen Sie die Adresse der aktiven Zelle im Direktfenster ausge-ben?

4. Wie knnen Sie den Objektkatalog in der Entwicklungsumgebung aufru-fen?

Kapitel 2

Programmieren mit Zellen und Bereichen

Nachdem wir die Entwicklungsumgebung von VBA bereits kennen, knnen wir nun loslegen und unsere ersten Makros schreiben. Dabei fangen wir mit der kleinsten Einheit in Excel, der Zelle, an. Von der Formatierung ber das Fllen und Auslesen von Zellen mit Formeln und Texten bis hin zum Einfgen von Kommentaren und der Benennung einzelner Zellen finden Sie in diesem Kapitel eine Menge von praxisnahen Beispielen Schritt fr Schritt erklrt.

Ihr Erfolgsbarometer

33

Das knnen Sie schon:

Erste Schritte in VBA 10

Das lernen Sie neu:

Rechenoperationen mit Excel 43

Markierungen auslesen 53

Kopieren und Einfgen von Daten 57

Zellen und Bereiche lschen 59

Zellenfunktionen anwenden 61

Zellen ausschneiden 64

Zahlenformate festlegen und ermitteln 65

Arbeiten mit Kommentaren 72

Zellen bearbeiten 86

34

Der Einsatz von Farben, Rahmen und SchriftschnittenSie haben in Excel die Mglichkeit, den Zellenhintergrund, die Schriftfarbeund die Rahmen farblich hervorzuheben. Als Farben stehen Ihnen die 56Farben aus der Farbpalette von Excel zur Verfgung. Jede Farbe hat eine ein-deutige Nummer.

Hintergrundfarbe einstellenIm nchsten Beispiel soll die aktive Zelle einen roten Hintergrund erhalten.Der Farbindex von Rot entspricht dem Index 3. Wenden Sie diesen Indexnun an, indem Sie die nchsten Arbeitsschritte befolgen:

1 Wechseln Sie ber die Tastenkombination (Alt) + (F11) in die Entwicklungs-umgebung.

2 Fgen Sie ein neues Modul ein, indem Sie aus dem Men EINFGEN den Befehl MODUL auswhlen.

3 Erfassen Sie das Makro aus Listing 2.1.Sub ZelleEinfrben() Sheets("Tabelle1").Activate ActiveCell.Interior.ColorIndex = 3End Sub

Listing 2.1: Die aktive Zelle einfrben

Mit der Eigenschaft ColorIndex weisen Sie dem Hintergrund der Zelle dieFarbe ROT zu.

Starten Sie das Makro, indem Sie wie folgt vorgehen:

1 Wechseln Sie jetzt einmal auf Ihre Tabelle und setzen den Mauszeiger auf eine beliebige Zelle.

2 Starten Sie das Makro, indem Sie aus dem Men EXTRAS den Befehl MAKRO/MAKROS whlen.

3 Im Listenfeld setzen Sie den Mauszeiger auf das Makro ZelleEinfrben und klicken die Schaltflche AUSFHREN.

Der Einsatz von Farben, Rahmen und Schriftschnitten

35

Zellenhintergrund einfrben

Das Makro aus Listing 2.1 knnen Sie ausbauen, indem Sie alle markiertenZellen mit der Hintergrundfarbe ROT formatieren. Das dazugehrige Makrolautet:

Sub AlleMarkiertenZellenFrben()Dim Zelle As Range

For Each Zelle In Selection Zelle.Interior.ColorIndex = 3Next ZelleEnd Sub

Listing 2.2: Alle markierten Zellen einfrben

Im ersten Schritt definieren Sie eine Variable vom Typ Range. Nun, was istdenn damit gemeint?

Hinweis

Sicher werden Sie sich fragen, warum Sie Variablen in der Program-mierung brauchen. Variablen werden unter anderem dazu gebraucht,um Daten zwischenzuspeichern. Mit Variablen knnen Sie dauerhaftarbeiten, d.h. Sie knnen jederzeit darauf zugreifen, diese abfragenoder verndern und zum Schluss ausgeben.

Wenn Sie Variablen einsetzen, mssen Sie sich dabei an bestimmteKonventionen fr deren Benennung halten:

36

Im nchsten Schritt setzen Sie eine Schleife auf. Dabei besteht eine Schleifeimmer aus einem Kopf, einem Mittelteil und einem Ende. Im Kopfteil wirddie Bedingung fr die Schleife formuliert. In unserem Fall sollen alle Zellen,die markiert sind, nacheinander gefrbt werden. Die Zeile For each Zellein Selection stellt dabei den Schleifenkopf dar. Innerhalb der Schleifewird die jeweils aktive Zelle ber die Eigenschaft ColorIndex gefrbt. DasEnde der Schleife muss mit der Zeile Next Zelle abgeschlossen werden.Damit wird die nchste Zelle, die sich in der Markierung befindet, ange-sprungen. Die Schleife wird so lange wiederholt, bis alle markierten Zellenabgearbeitet wurden.

Um das Makro aus Listing 2.2 einmal zu testen, gehen Sie wie folgt vor:

1 Markieren Sie in einer Tabelle die Zellen A1, B3, C5 und D7. Halten Sie bei der Markierung die Taste (Strg) gedrckt.

Zellen markieren

Das erste Zeichen muss aus einem Buchstaben bestehen. Alsfolgende Zeichen knnen Sie Buchstaben, Zahlen und einigeSonderzeichen verwenden.

Sie drfen keine Leerzeichen in einem Variablennamen ver-wenden. Wenn Sie einzelne Worte trennen mchten, verwen-den Sie dazu das Unterstrichzeichen (Dim Miete_Januar asCurrency).

Sonderzeichen wie #, %, &, ! oder ? sind nicht erlaubt.


37

2 Whlen Sie aus dem Men EXTRAS den Befehl MAKRO/MAKROS.3 Whlen Sie im Listenfeld das Makro ALLEMARKIERTENZELLENFRBEN aus.4 Klicken Sie danach auf die Schaltflche AUSFHREN, um das Makro zu starten.

Alle markierten Zellen wurden rot eingefrbt

Schriftfarbe whlenWas beim Zellenhintergrund geht, funktioniert auch bei der Schriftfarbe.Versuchen Sie einmal der Zelle B1 die Schriftfarbe BLAU zuzuweisen. DasGanze sieht wie folgt aus:

Sub TextFarbeZuweisen() Sheets("Tabelle2").Activate Range("B1").Select With ActiveCell .Value = "Texteintrag" .Font.ColorIndex = 5 .Font.Bold = True End WithEnd Sub

Listing 2.3: Einen Text erfassen und farblich hervorheben

Sorgen Sie im ersten Schritt dafr, dass die richtige Tabelle, auf der Sie dieTexteingabe per Makro vornehmen mchten, eingestellt ist. Dazu verwen-den Sie die Methode Activate. Danach setzen Sie den Mauszeiger auf dieZelle, die formatiert werden soll. ber die Anweisung With sparen Sie sichSchreibarbeit. Da zuerst ein Text in die aktive Zelle eingefgt und danachdieser Text farbig formatiert werden soll, brauchen Sie nicht bei jedem Be-fehl die volle Syntax zu verwenden. Es reicht, wenn Sie anstelle der Eigen-

38

schaft ActiveCell einen Punkt erfassen. Denken Sie daran, dass Sie dieWith-Anweisung mit einem End-With abschlieen. Innerhalb dieser Anwei-sung setzen Sie die Eigenschaft Value ein, um der Zelle einen Text zuzuwei-sen. Zustzlich setzen Sie die Eigenschaft Font ein. ber die EigenschaftColorIndex weisen Sie dem Objekt Font die Farbe BLAU zu, die den Farb-index 5 hat. Formatieren Sie den Text dann noch mit dem Schriftschnitt FETTund setzen fr diesen Zweck die Eigenschaft Bold ein, der Sie den WertTrue zuweisen.

Einen Test eingeben, frben und fetten

Zellenhintergrund ermittelnSie haben bereits erfahren, dass Sie unter 56 Farbwerten in Excel auswhlenknnen. Welche Farbe nun aber welchen Farbwert hat, knnen Sie ermit-teln, indem Sie eine bestimmte Zelle mit einer gewnschten Hintergrundfar-be belegen und danach das Makro aus Listing 2.4 starten.

Sub FarbIndexHerausnden()MsgBox "Die aktive Zelle " & ActiveCell.Address & _" hat den Farbindex " & ActiveCell.Interior.ColorIndexEnd Sub

Listing 2.4: Den Hintergrund einer Zelle ermitteln

Mit Hilfe der Funktion Msgbox knnen Sie eine Bildschirmmeldung ausge-ben. Dabei ermitteln Sie ber die Eigenschaft Address die genaue Zellen-adresse der markierten Zelle.

Um das Makro aus Listing 2.4 zu testen, gehen Sie wie folgt vor:


39

1 Belegen Sie eine beliebige Zelle mit der Hintergrundfarbe ORANGE.2 Setzen Sie den Mauszeiger auf diese Zelle.3 Starten Sie das Makro aus Listing 2.4.

Den Hintergrund der Zelle ermitteln

Schriftfarbe erkennenAnalog zum Makro aus Listing 2.4 knnen Sie ber das folgende Makro ausListing 2.5 die Schriftfarbe erkennen. Geben Sie als Vorarbeit einen Text ineine beliebige Zelle ein und formatieren diese mit der Schriftfarbe MEERES-GRN.

Sub SchriftFarbenIndexHerausnden()MsgBox "Die aktive Zelle " & ActiveCell.Address & _" hat den Schriftfarbenindex " & _ ActiveCell.Font.ColorIndexEnd Sub

Listing 2.5: Die Schriftfarbe der aktiven Zelle erkennen

Um die Schriftfarbe der aktiven Zelle zu ermitteln, setzen Sie die AnweisungFont.ColorIndex ein.

Hinweis

Ist die Zelle mit keinem farbigen Hintergrund belegt, dann gibt ExcelIhnen den Farbindex 4142 aus.

40

Die Schriftfarbe der aktiven Zelle ermitteln

Weitere SchrifteigenschaftenNeben der Schriftfarbe und dem Schriftschnitt FETT gibt es eine ganze Reiheweiterer Eigenschaften, die Sie einsetzen knnen. Entnehmen Sie diese Ei-genschaften der folgenden Tabelle.

Tabelle 2.1: Die Eigenschaften bezglich der Schriftformatierung

Eigenschaft Erklrung

Bold Schrift in Fettdruck

Colorindex Farbe der Schriftart festlegen

Italic Schrift in Kursivdruck

Size Schriftgre festlegen

Strikethrough Schrift horizontal durchstrichen darstellen

Subscript Schrift mit tiefgestellten Zeichen

Superscript Schrift mit hochgestellten Zeichen

Underline Schrift mit Unterstreichung


41

Schriftart einstellen

Rahmenart und -farbe bestimmenKommen wir nun zum Rahmen einer Zelle. Dieser Rahmen kann sowohl be-zglich der Farbe als auch der Rahmenart unterschiedlich gestaltet werden.Sehen Sie im Beispiel RAHMENFESTLEGEN, wie Sie einen grn gepunkteten Rah-men erzeugen knnen.

Sub RahmenFestlegen()Sheets("Tabelle4").Activate Range("C2:D2").Select With Selection.Borders .ColorIndex = 10 .LineStyle = xlDot End WithEnd Sub

Listing 2.6: Den Rahmentyp sowie die Rahmenfarbe festlegen

Tipp

Damit Sie sich leichter zu Recht finden, knnen Sie auch einmal denDialog ZELLEN FORMATIEREN in Excel aufrufen. Sie finden dort alleSchriftschnitte auf der Registerkarte SCHRIFT.

42

Aktivieren Sie im ersten Schritt die gewnschte Tabelle. Danach markierenSie den Bereich, der mit einem Rahmen umgeben werden soll. Jetzt wendenSie die Anweisung With an, um sich wieder viel Schreibarbeit zu sparen.Geben Sie mit dieser With Anweisung einmal an, auf welches Objekt sichdie nachfolgenden Befehle beziehen sollen, und geben innerhalb der With-Anweisung fr dieses Objekt jeweils einen Punkt an. Innerhalb dieser An-weisung setzen Sie die Eigenschaft ColorIndex ein, der Sie den Wert 10 zu-weisen. Damit frben Sie den Rahmen hellgrn. Danach wenden Sie dieEigenschaft LineStyle an und weisen dieser Eigenschaft die Konstante xl-Dot zu. Damit wird der Rahmen in kleinen Punkten dargestellt. SchlieenSie die With-Anweisung am Ende mit der Anweisung End With ab.

Einen gepunkteten Rahmen einfgen

Neben der gepunkteten Form gibt es eine ganze Reihe weiterer Rahmen-typen, die Sie in der nchsten Tabelle sehen knnen.

Rahmen-Konstante Erklrung

xlContinuous Durchgezogene Linie

XlDash Gestrichelte Linie

xlDashDot Linie aus Strichen und Punkten

xlDashDotDot Linie aus Strich-Punkt-Punkt

XlDot Gepunktete Linie

xlDouble Linie doppelt

Tabelle 2.2: Die verschiedenen Rahmentypen

Rechenoperationen mit Excel

43

Rahmentyp festlegen

Rechenoperationen mit Excel Beim Rechnen mit Excel verwenden Sie dieselben Operatoren, die Sie vomTaschenrechner her schon kennen. Auch bei den Rechenregeln rechnetExcel nach den allgemein gltigen Regeln.

xlSlantDashDot Linie aus Wellenzeichen und Punkt

xlLineStyleNone Keine Linie

Rahmen-Konstante Erklrung

Tabelle 2.2: Die verschiedenen Rahmentypen (Forts.)

Tipp

Auch hier machen Sie es sich leichter, wenn Sie den Dialog ZELLENFORMATIEREN aufrufen und auf die Registerkarte RAHMEN wechseln. Diedort angebotenen Rahmentypen knnen Sie auch ber den Einsatzvon VBA verwenden.

44

Werte addierenIm ersten Beispiel wird der aktuelle Zellenwert von A2 in die Variable i ge-schrieben. Danach wird die Variable um den Wert eins erhht. Zuletzt wirddie genderte Variable zurck in Zelle A2 geschrieben.

Sub WerteAddieren()Dim i As Single

Sheets("Tabelle5").Activate i = Range("A2").Value i = i + 1 Range("A2").Value = IEnd Sub

Listing 2.7: Werte addieren

Aktivieren Sie zu Beginn das gewnschte Tabellenblatt, auf dem Sie die Re-chenoperation durchfhren mchten. Danach speichern Sie den momenta-nen Inhalt der Zelle A2 in der Variablen i. Dann erhhen Sie den Inhalt derVariablen um den Wert eins und schreiben diesen neuen Wert in die Zellezurck.

Bei der Definition der Variablen i wurde der Datentyp Single verwendet.Je nachdem, welche Aufgabe Sie in Excel ausfhren mchten, stehen Ihnendie folgenden Variablentypen zur Verfgung.

Variablen-Typ Wertebereich/Speicherbedarf

Byte Ganze Zahlen zwischen 0 und 255 (1Byte)

Boolean Wahrheitswert, entweder True oder False (2 Bytes)

Currency Whrungs-Datentyp: Festkommazahlen mit 15 Stellen vor und 4 Stellen nach dem Komma (8 Bytes)

Date Datums- und Zeit-Datentyp (8 Bytes)

Double Fliekommazahlen mit einer Genauigkeit von 16 Stellen hinter dem Komma (8 Bytes)

Integer Ganze Zahlen zwischen -32.768 und +32.767 (2 Bytes)

long Ganze Zahlen im Wertebereich von -2.147.483.648 und + 2.147.483.647 (4 Byte)

Tabelle 2.3: Die verschiedenen Variablentypen


45

Werte subtrahierenAuf dieselbe Zelle wie gerade eben fhren Sie nun eine Subtraktion durch.Wieder speichern Sie den momentanen Wert von Zelle A2 in der Variablen i,subtrahieren den Wert um eins und schreiben ihn dann in die Zelle zurck.Ist der Wert Null in der Zelle erreicht, geht es weiter ins Negative.

Sub WerteSubtrahieren()Dim i As Single

Sheets("Tabelle5").Activate i = Range("A2").Value i = i 1 Range("A2").Value = IEnd Sub

Listing 2.8: Werte subtrahieren

Werte multiplizierenBeim Multiplizieren von Werten definieren Sie zuerst eine Konstante, in der Sieden Faktor, mit dem Sie im nchsten Beispiel eine Erhhung um 16% durch-fhren mchten, festlegen. Danach speichern Sie wiederum den momentanenZustand der Zelle A5 in der Konstante Betrag. Jetzt wird mit dem Faktor mul-tipliziert und das Ergebnis daraus in die Nebenzelle B5 geschrieben.

Sub WerteMultiplizieren()Dim Betrag As Currency

Const Faktor = "1,16"Sheets("Tabelle5").Activate Range("B5").Select

object Datentyp gibt einen Verweis auf ein Objekt wieder. (4 Bytes)

single Fliekommazahlen mit einer Genauigkeit von 8 Stellen hinter dem Komma (4 Bytes)

string Der Datentyp fr alle Texte (10 Bytes)

variant Standarddatentyp. Wird automatisch gewhlt, wenn kein anderer Datentyp definiert ist. (16 Bytes)

Variablen-Typ Wertebereich/Speicherbedarf

Tabelle 2.3: Die verschiedenen Variablentypen (Forts.)

46

Betrag = Range("A5").Value Betrag = Betrag * Faktor ActiveCell.Offset(0, 1).Value = BetragEnd Sub

Listing 2.9: Werte multiplizieren

Geben Sie die prozentuale Erhhung in der Konstante Betrag gleich zu Be-ginn des Makros an. Aktivieren Sie danach sowohl die Tabelle als auch diegewnschte Zelle. Lesen Sie den momentanen Zellwert in die VariableBetrag. Diese Variable multiplizieren Sie mit Ihrem vorher definiertenFaktor und schreiben diesen neuen Wert in die Nebenzelle. Dabei setzenSie die Eigenschaft Offset ein.

Mit Hilfe der Eigenschaft Offset knnen Sie eine Verschiebung des Zellen-zeigers erreichen, indem Sie im ersten Argument die Anzahl der Zeilen an-geben und im zweiten Argument die Anzahl der Spalten, um die derZellenzeiger verschoben werden soll. Da es die Nebenzelle sein soll, mussdas erste Argument den Wert 0 und das zweite Argument den Wert 1 haben.Erfahren Sie dazu mehr im weiteren Verlauf des Kapitels.

Werte multiplizieren

Werte dividierenBei der Division von Werten gehen Sie vor wie bei der Multiplikation, auerdass Sie statt einer Multiplikation eine Division durchfhren. Achten Sie dar-auf, dass Sie nie auf die Idee kommen, als Divisor den Wert Null zu verwen-den, das mag Excel nmlich gar nicht und reagiert dann prompt mit einerFehlermeldung. Als typisches Beispiel fhren Sie einmal eine Whrungsum-


47

rechnung durch. Dabei sollen alte Excel-DM-Daten in die Euro-Whrungumgerechnet werden.

Sub WerteDividieren() Const divisor = 1.95583

Sheets("Tabelle6").Activate Range("A2").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(0, 1).Value = _ ActiveCell.Value / divisor ActiveCell.Offset(1, 0).Select LoopEnd Sub

Listing 2.10: Werte dividieren

Zu Beginn des Makros aus Listing 2.10 geben Sie den Umrechnungsfaktoran. Danach aktivieren Sie die gewnschte Tabelle und positionieren denMauszeiger auf die Zelle, in der die Umrechnung beginnen soll. Danachbasteln Sie sich eine Schleife, die so lange durchlaufen wird, bis die ersteleere Zelle erreicht ist. Setzen Sie fr diesen Zweck eine Do Until-Schleifeein. Bei dieser Art von Schleife wird jeweils zu Beginn eines jeden Schlei-fendurchlaufs geprft, ob das Endekriterium (hier die erste leere Zelle) er-reicht ist. Wenn ja, dann wird die Schleife verlassen. Innerhalb der Schleifegeben Sie zuerst an, in welche Zelle das Ergebnis der Division geschriebenwerden soll. Dazu verweisen Sie ber die Eigenschaft Offset genau eineSpalte weiter nach links und schreiben das Ergebnis der Division dort hinein.

Achtung

Vergessen Sie danach nicht, den Zellenzeiger eine Zelle weiter nachunten zu setzen, da Sie sonst eine Endlosschleife erzeugen. Setzen Siedazu in der Eigenschaft Offset das erste Argument auf den Wert 1.

48

Die Umrechung von DM in Euro

Werte potenzierenBei der Potenzierung von Werten verwenden Sie das kleine Dach auf derTastatur. Dazu drcken Sie auf die Taste (^) und anschlieend auf die Taste(Leer), um das Zeichen auf den Bildschirm zu bringen.

Im folgenden Beispiel geben Sie ber eine Maske eine Zahl zwischen einsund zehn ein. Diese Zahl wird dann mit der Zahl Zwei potenziert. Das Makrofr diese Aufgabe knnen Sie in Listing 2.11 sehen.

Sub WertePotenzieren()Dim i As SingleConst pot = 2

i = InputBox _("Geben Sie eine Zahl zwischen 1 und 10 ein!", _ "EINGABE")If i = 0 Then Exit Subi = i ^ potMsgBox "Das Ergebnis der Potenzierung lautet: " & iEnd Sub

Listing 2.11: Werte potenzieren

Navigationsbeispiele

49

Definieren Sie zu Beginn des Makros aus Listing 2.11 eine Variable, in der Siedie Eingabe des Benutzers speichern. In der Konstante pot legen Sie als Po-tenz den Wert 2 fest. Rufen Sie im Anschluss daran die Funktion Inputboxauf. Mit Hilfe dieser Funktion knnen Sie eine Eingabemaske aufrufen und deneingegebenen Wert abfragen. Der eingegebene Wert wird in der Variablen igespeichert. Wurde ein Wert eingegeben, dann potenzieren Sie diesen undgeben das Ergebnis dieser Rechenoperation mit Hilfe der Funktion Msgbox aufdem Bildschirm aus.

Eingabe ber einen Dialog vornehmen

NavigationsbeispieleUnter Navigation in Excel versteht man das Bewegen des Mauszeigers aufeiner Tabelle oder auch innerhalb einer Arbeitsmappe.

Erste freie Zelle in Spalte anspringenIm ersten Beispiel versuchen Sie einmal die letzte Zelle in einer Spalte anzu-steuern. Dazu setzen Sie die Eigenschaft End ein und gelangen mit der Ei-genschaft Offset eine Zelle weiter nach unten in die erste leere Zelle dieserSpalte. Anschlieend geben Sie die Zellenadresse in einer Bildschirmmel-dung aus.

Sub LetzteZelleAktivieren() Sheets("Tabelle6").Activate Range("A65536").End(xlUp).Offset(1, 0).Select MsgBox "Die erste freie Zelle in Spalte A lautet: " & _ ActiveCell.AddressEnd Sub

Listing 2.12: Die erste freie Zelle einer Spalte ermitteln

50

Die erste freie Zelle in Spalte A ermitteln

Letzte Zelle im verwendeten Bereich ansteuernIm nchsten Beispiel mchten Sie die letzte Zelle im verwendeten Bereichermitteln. Dazu setzen Sie die Methode Specialcells ein, der Sie die Kon-stante xlCellTypeLastCell mitgeben. Die Lsung dieser Aufgabe sehenSie in Listing 2.13.

Sub LetzteZelleImBenutztenBereichErmitteln() Sheets("Tabelle6").Activate

MsgBox "Die letzte Zelle im benutzten Bereich lautet: " & _ Selection.SpecialCells(xlCellTypeLastCell).AddressEnd Sub

Listing 2.13: Letzte Zelle im benutzen Bereich ermitteln

Navigationsbeispiele

51

Die letzte verwendete Zelle ermitteln

Weitere interessante Konstanten dieser Methode entnehmen Sie der folgen-den Tabelle.

Tabelle 2.4: Die Konstanten der Methode SpecialCells

Konstante Erklrung

xlCellTypeAllFormatConditions Zellen mit Formatierung

xlCellTypeAllValidation Zellen mit Gltigkeitsregeln

xlCellTypeBlanks Leere Zellen

xlCellTypeComments Zellen mit Kommentaren

xlCellTypeConstants Zellen mit Konstanten

xlCellTypeFormulas Zellen mit Formeln

xlCellTypeLastCell Letzte Zelle im benutzten Bereich

xlCellTypeSameFormatConditions Zellen mit gleichem Format

xlCellTypeSameValidation Zellen mit gleichen Gltigkeits-Kriterien

xlCellTypeVisible Alle sichtbaren Zellen

52

Auf Zellen zugreifen, ohne darauf zu sitzenWie Sie an bestimmte Stellen des Tabellenblattes springen, wissen Sie jetzt.Wie aber verbleiben Sie auf der momentanen Zellenposition und sprechendennoch andere Zellen an? Dazu verwenden Sie die Eigenschaft Offset,die schon im Verlauf dieses Kapitels verwendet wurde. Das erste Argumentder Eigenschaft Offset ist die Anzahl der Zeile(n), auf die Bezug genommenwerden soll. Das zweite Argument steht fr die Anzahl der Spalten, auf dieBezug genommen werden soll.

Im nchsten Beispiel aus Listing 2.14 werden Sie auf einer noch leerenTabelle einzelne Eingaben vornehmen. Dabei verbleibt der Mauszeigerwhrend der ganzen Zeit auf Zelle A1.

Sub ZellenAnsprechen() Sheets("Tabelle7").Activate Range("A1").Select Range("A1").Value = "Text1 1. Zeile" ActiveCell.Offset(0, 1).Value = "Text2 1. Zeile" ActiveCell.Offset(0, 2).Value = "Text3 1. Zeile" ActiveCell.Offset(0, 3).Value = "Text4 1. Zeile" ActiveCell.Offset(1, 0).Value = "Text5 2. Zeile" ActiveCell.Offset(1, 1).Value = "Text6 2. Zeile"End Sub

Listing 2.14: Mit Offset einzelne Zellen fllen

Navigationsbeispiel fr die Eigenschaft Offset

Markierungen auslesen

53

Markierungen auslesenWenn Sie eine Markierung bzw. den benutzten Bereich auf einem Tabellen-blatt auslesen mchten, stellen sich je nach Aufgabe folgende Fragen:

Wie viele Zellen enthlt der markierte Bereich? Wie lautet die Adresse der letzten Zelle im markierten Bereich? Wie lautet die Adresse der ersten Zelle im markierten Bereich? Wie lauten die Eckdaten einer Markierung?

Zellen in der Markierung zhlenMarkieren Sie auf der TABELLE7 ein paar Zellen und starten danach das Makroaus Listing 2.15.

Sub AnzahlMarkierterZellenErmitteln()Dim l As Long

Sheets("Tabelle7").Activatel = Selection.CountMsgBox "Es sind genau " & l & " Zellen markiert!"End Sub

Listing 2.15: Markierte Zellen zhlen und ausgeben

Die Methode Count ermittelt die Anzahl der Objekte im markierten Bereichund speichert diese in einer Variable vom Typ Long. Diese Anzahl geben Sieber die Funktion Msgbox am Bildschirm aus.

54

Zellen in Markierung zhlen

Letzte Zelle in Markierung ermittelnMarkieren Sie im nchsten Beispiel ein paar Zellen in der TABELLE7. StartenSie danach das Makro aus Listing 2.16.

Sub LetzteZelleInMarkierung() Sheets("Tabelle1").Activate MsgBox _"Die letzte Zelle in der Markierung lautet: " & _ Selection.(Selection.Count).AddressEnd Sub

Listing 2.16: Letzte Zelle in Markierung ermitteln

Setzen Sie die Methode SpecialCells ein und bergeben dieser Methodedie Konstante xlCellTypeLastCell. Damit erhalten Sie die letzte Zelle inder Markierung, wenn Sie diese Methode auf die Eigenschaft Selection an-wenden.

Markierungen auslesen

55

Die letzte Zelle in der Markierung ermitteln

Erste Zelle der Markierung ermittelnBeim Ermitteln der ersten Zelle eines markierten Bereichs ist es relativ ein-fach, diese Zelle zu ermitteln. Beim Markieren eines Bereichs bleibt die ersteZelle automatisch gekennzeichnet. Das sehen Sie, wenn Sie sich die folgendeAbbildung auf S. 56 ansehen. Dort ist die erste Zelle in der Markierung miteinem weien Hintergrund belegt. Diese Zelle knnen Sie mit dem Makroaus Listing 2.17 ermitteln und auf dem Bildschirm ausgeben.

Sub ErsteZelleInMarkierung() Sheets("Tabelle7").Activate MsgBox "Die erste Zelle in der Markierung lautet: " & _ ActiveCell.AddressEnd Sub

Listing 2.17: Die erste Zelle in einer Markierung ermitteln

Mit Hilfe der Eigenschaft Address, die Sie auf die aktive Zelle der Markie-rung, nmlich der ersten Zelle der Markierung, anwenden, knnen Sie dieZellenadresse ermitteln. Geben Sie diese ber die Funktion Msgbox am Bild-schirm aus.

56

Die erste Zelle einer Markierung ermitteln

Die Eckdaten einer Markierung abfragenUm die erste Zelle der Markierung links oben zu ermitteln, brauchen Sie nurdie Address-Eigenschaft der aktiven Zelle auszugeben. Die letzte Zelle inder Markierung bekommen Sie, indem Sie die Methode Count auf die Mar-kierung anwenden.

Sub EckpositionenDerMarkierungErmitteln() Sheets("Tabelle7").Activate MsgBox "Start Zelle: " & ActiveCell.Address & Chr(10) _ & "Ende Zelle: " & Selection(Selection.Count).AddressEnd Sub

Listing 2.18: Die Zellenkoordinaten einer Markierung ermitteln

Kopieren und Einfgen von Daten

57

Start- und Endzelle einer Markierung ermitteln

Kopieren und Einfgen von DatenDas Kopieren von Zellen funktioniert mit Hilfe der Methode Copy. Dabeiknnen eine einzelne Zelle oder auch ein Bereich aus mehreren Zellen ko-piert werden. Die Methode Paste fgt den Inhalt der Zwischenablage in dasTabellenblatt ein.

Kopieren und alles einfgenIm folgenden Beispiel kopieren Sie den Zellenbereich B1:B11 und fgen die-sen Bereich ab Zelle C1 ein.

Erfassen Sie fr diesen Zweck das Makro aus Listing 2.19.

Sub KopierenUndEinfgen() Sheets("Tabelle6").Activate Range("B1:B11").Copy Range("C1").Select ActiveSheet.Paste Application.CutCopyMode = FalseEnd Sub

Listing 2.19: Bereich kopieren und einfgen

58

Das Ergebnis der Kopieraktion in Spalte C

Die Eigenschaft CutCopyModus gibt den Status des Ausschneide- oder Ko-piermodus zurck bzw. stellt diesen ein. Indem Sie diese Eigenschaft auf denWert False setzen, deaktivieren Sie den Ausschneide- bzw. Kopiermodusund entfernen somit den Laufrahmen, der nach dem Einfgen normalerwei-se erhalten bleiben wrde. ber die Methode Paste fgen Sie den Inhalt derZwischenablage in den Zielbereich ein.

Nur kopierte Werte einfgenIm nchsten Beispiel werden Zellen, die teilweise Formeln und Formatierun-gen enthalten, kopiert. Dabei werden jeweils nur die Werte der kopiertenZellen eingefgt.

Sehen Sie sich dazu das Makro aus Listing 2.20 an.

Sub KopierenEinfgenNurWerte() Sheets("Tabelle8").Activate Range("B1:B13").Copy Range("D1").Select Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = FalseEnd Sub

Listing 2.20: Nur Werte kopieren und einfgen

Zellen oder Bereiche lschen

59

Nur Werte kopieren

Mit Hilfe der Methode PasteSpecial knnen Sie selbst bestimmen, was Sieim Zielbereich einfgen mchten. Folgende Optionen sind dabei mglich:

Tabelle 2.5:Die Konstanten der Methode PasteSpecial

Zellen oder Bereiche lschenDas Lschen von Zelleninhalten erreichen Sie ber die Methode Clear. ImListing 2.21 wird ein Bereich auf einer Tabelle ohne weitere Rckfragegelscht.

Konstante Erklrung

xlPasteAll Alles wird eingefgt.

xlPasteFormulas Nur Formeln werden eingefgt.

xlPasteValues Nur Werte werden eingefgt.

xlPasteFormats Nur Formate werden bertragen.

xlPasteNotes Nur Kommentare werden eingefgt.

xlPasteAllExceptBorders Alles auer Rahmen wird eingefgt.

60

Sub BereichLschen() Sheets("Tabelle8").Activate Range("D1:D13").ClearEnd Sub

Listing 2.21: Bereich lschen

Mchten Sie eine Sicherheitsabfrage einbauen, bevor Sie einen Bereichlschen, dann nehmen Sie das Makro aus Listing 2.22.

Sub BereichLschenNachRckfrage()Dim i As Integer Sheets("Tabelle8").Activatei = MsgBox _("Wollen Sie den Bereich wirklich lschen?", vbOKCancel)If i = 1 Then Range("D1:D13").Clear Else _MsgBox "Sie haben die Aktion abgebrochen!"End Sub

Listing 2.22: Lschabfrage einbauen

Mit Hilfe der Variablen i speichern Sie den Rckgabewert des Meldungs-fensters. Wurde die Schaltflche OK geklickt, dann ist der Wert der Variab-len 1. Klickt der Anwender jedoch die Schaltflche ABBRECHEN, dann steht inder Variablen i der Wert 2. Genau diese Rckmeldung knnen Sie in einerIf-Anweisung abfragen und entsprechend reagieren. Die If-Anweisung be-steht aus zwei unterschiedlichen Zweigen, einem Then-Zweig sowie einemElse-Zweig. Wird die Schaltflche OK geklickt, dann wird die Aktion durch-gefhrt, die im Then-Zweig steht. Im anderen Fall wird der Else-Zweig an-gesteuert, der eine Bildschirmmeldung mit einem Abbruchhinweis anzeigt.

Zellenfunktionen anwenden

61

Bereich erst nach Rckfrage lschen

Zellenfunktionen anwendenSicher kennen Sie die wohl am hufigsten verwendete Zellenfunktion Summe.Diese knnen Sie in VBA wie folgt einsetzen:

Sub BereichSummieren() Sheets("Tabelle9").Activate Range("A10").Value = WorksheetFunction.Sum _ (Range("A1:A9"))End Sub

Listing 2.23: Zellenbereich summieren

Beim Beispiel in Listing 2.23 wurde die Funktion Sum angewendet. DieseFunktion wird Ihnen durch die Eigenschaft WorksheetFunction bereitge-stellt. Das Ergebnis liegt bei diesem Beispiel als Festwert vor.

Hinweis

Weitere wichtige Worksheet-Funktionen sind Max, Min, Average,welche den hchsten, den kleinsten und den Durchschnittswert einerListe ermitteln.

62

Einen Summenwert mit VBA bilden

Eine variable Funktion einfgenMchten Sie in Zelle A10 keinen festen Wert, sondern die FunktionSUMME(A1:A9) stehen haben, dann lautet die Lsung dieser Aufgabe wiefolgt:

Sub BereichSummierenMitFunktion() Sheets("Tabelle9").Activate Range("A10").FormulaLocal = "=SUMME(A1:A9)"End Sub

Listing 2.24: Eine Funktion in eine Zelle einfgen

Setzen Sie die Eigenschaft FormulaLocal ein und weisen Sie dieser Eigen-schaft die gewnschte Tabellenfunktion zu.

Zellenfunktionen anwenden

63

Die Funktion bleibt in Zelle A10 erhalten

Variable SummenbildungMchten Sie selbst entscheiden, welche Bereiche Ihrer Tabelle Sie summie-ren mchten, dann knnen Sie einen Bereich ber eine Inputbox markierenund dann summieren.

Setzen Sie den Mauszeiger auf die Zelle, die die Summenfunktion erhaltensoll, und starten Sie danach das Makro aus Listing 2.25.

Sub SummeInMarkiertemBereich()Dim Bereich As RangeDim s_Zelle As String

Sheets("Tabelle10").Activate s_Zelle = ActiveCell.Address Set Bereich = Application.InputBox _ ("Bereich auswhlen", Type:=8) Range(s_Zelle).Activate ActiveCell.FormulaLocal = _ "=SUMME(" & Bereich.Address & ")"End Sub

Listing 2.25: Den zu summierenden Bereich ber eine Inputbox markieren

Wenn Sie bei der Funktion Inputbox den Typ 8 angeben, dann haben Siedie Mglichkeit, einen Bereich in Ihrer Tabelle frei auszuwhlen. Der aus-

64

gewhlte Datenbereich wird dann automatisch in die Inputbox bernom-men. Sie brauchen nur noch mit OK zu besttigen. Im Anschluss daran wirddie Zielzelle, deren Adresse Sie vorher in einer Variablen gespeichert haben,aktiviert und die Summenfunktion wird dann eingefgt. Dabei setzen Sie dieFunktion aus mehreren Bestandteilen zusammen. Zum einen geben Sie dieFunktion =SUMME als Text an, zum anderen greifen Sie ber die EigenschaftAddress auf den Bereich zu, den Sie ber die Inputbox ausgewhlt haben.Verbinden Sie diese Bestandteile mit Hilfe des Operators &.

Summen variabel bilden

Zellen ausschneidenVerwandt mit dem Kopieren der Zellen, welches weiter oben im Kapitel be-reits angesprochen wurde, ist das Ausschneiden der Zellen mit dem Unter-schied, dass beim Kopieren die Ausgangszelle erhalten bleibt, whrendbeim Ausschneiden die Zelle nach dem Einfgen verschwindet. Die Metho-de fr das Ausschneiden von Zellen heit Cut.

Im folgenden Beispiel wird ein Zellenbereich ausgeschnitten und um eineSpalte weiter nach rechts versetzt.

Sub ZellenAusschneiden() Sheets("Tabelle10").Activate Range("D2:D12").Select Selection.Cut ActiveCell.Offset(0, 1).Select

Zahlenformate festlegen und ermitteln

65

ActiveSheet.Paste Application.CutCopyMode = FalseEnd Sub

Listing 2.26: Bereich ausschneiden

Aktivieren Sie im ersten Schritt die Tabelle und markieren den Zellenbe-reich, der ausgeschnitten werden soll. Danach wenden Sie die Methode Cutgerade auf diese Markierung an, die in der Eigenschaft Selection verzeich-net ist. Versetzen Sie jetzt den Mauszeiger genau eine Zeile weiter nachrechts und wenden die Methode Paste an, um den ausgeschnittenen Inhaltdorthin einzufgen. Setzen Sie die Eigenschaft CutCopyMode auf den WertFalse, um den Laufrahmen, der nach dieser Aktion leider nicht von alleinverschwindet, zu entfernen.

Bereich ausschneiden und einfgen

Zahlenformate festlegen und ermittelnIn Excel knnen Sie ber den Dialog FORMAT aus zahlreichen Formaten diepassende Formatierung fr Ihre Daten auswhlen. Des Weiteren sind Sie inder Lage, eigene, benutzerdefinierte Formate anzulegen. Wie aber knnenSie ber VBA eine Zelle mit einem Format belegen? Diese Aufgabe knnenSie erledigen, indem Sie die gewnschte Zelle markieren, den Dialog ZELLENFORMATIEREN aufrufen und auf die Registerkarte ZAHLEN wechseln.

66

Zahlenformat festlegen

Zahlenformate festlegenIm nchsten Beispiel weisen Sie der Zelle A1 der TABELLE11 ein Zahlenformatohne Nachkommastellen zu. Geben Sie fr diesen Zweck die Zahl 1500,99ein. Excel msste nach dem Ablauf des Makros aus Listing 2.27 die Zahl1501 anzeigen.

In Zelle A2 erfassen Sie zunchst einmal nichts.

Sub ZahlenformateFestlegen() Sheets("Tabelle11").Activate Range("A1").NumberFormat = "#,##0" Range("A2").NumberFormat = "@"End Sub

Listing 2.27: Zellen mit Zahlenformaten formatieren

Das Zahlenformat wird in Excel ber die Eigenschaft NumberFormat zuge-wiesen.

Geben Sie jetzt in Zelle A2 eine Telefonnummer ein. Standardmigschluckt Excel fhrende Nullen bei Telefonnummern. Nach dem Ablauf desMakros aus Listing 2.27 bleiben jedoch diese Nullen in Fhrungsposition er-halten.


67

Zellen mit Zahlenformaten belegen

Datumsformate festlegenIm nchsten Beispiel wird einem Zellenbereich ein bestimmtes Datumsfor-mat zugewiesen. Dabei soll der Monat des Datums ausgeschrieben werden.

Sub ZelleMitDatumFormatieren()Dim Zelle As RangeDim Bereich as Range

Sheets("Tabelle12").ActivateSet Bereich = Range("A2:A10")For Each Zelle In Bereich

Zelle = (Format(Zelle, "DD MMMM YYYY"))Next ZelleEnd Sub

Listing 2.28: Datumsformatierung vornehmen

Definieren Sie zuerst einmal einen Bereich, in dem die Datumsformatierungvorgenommen werden soll, und speichern diesen in der Variablen Bereich.Danach setzen Sie eine Schleife auf, die alle Zellen dieses Bereiches ansteu-ert und die gewnschte Formatierung vornimmt. Zur Formatierung verwen-den Sie die Funktion Format, der Sie im ersten Argument die jeweilige Zelleund im zweiten Argument die gewnschte Formatierung bekannt geben.Dabei wird mit Krzeln gearbeitet. Das Krzel D steht fr den Tag, das M frden Monat und das Y fr das Jahr. Je nachdem wie oft Sie das Krzel ange-ben, wird das Datum entsprechend ausgegeben.

68

Datumsangaben formatieren

Formate bertragenSicher kennen Sie das Pinsel-Symbol aus der Symbolleiste STANDARD, mitdem Sie Formate bertragen knnen. Das bertragen von Formaten ist einKopiervorgang, bei dem nur die Formate ber die Methode PasteSpecialeingefgt werden.

Im folgenden Beispiel soll das Datumsformat aus Zelle A1 auf die ZellenB1:G1 bertragen werden. Sehen Sie sich dazu einmal die Ausgangssitua-tion an.

Die Formatierung aus Zelle A1 soll auf die Zellen B1:G1 bertragen werden


69

Um diese Aufgabe zu lsen, setzen Sie das Makro aus Listing 2.29 ein.

Sub Formatbertragen()Sheets("Tabelle13").ActivateRange("A1").Copy Range("B1:G1").Select Selection.PasteSpecial _ Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = FalseEnd Sub

Listing 2.29: Formate bertragen

Mit Hilfe der Methode Copy kopieren Sie die Zelle A1. Danach markierenSie den Bereich, auf den Sie die Formatierung anwenden mchten. SetzenSie die Methode PasteSpecial ein, der Sie die Konstante xlFormats ber-geben. Damit wird klar, dass Excel nur das Format bertragen soll. WeisenSie der Eigenschaft CutCopyMode den Wert False zu, um den Laufrahmen,der bei jedem Kopieren automatisch um die kopierte Zelle bzw. den kopier-ten Bereich gelegt wird, zu entfernen.

Die Formate wurden bertragen, die Spalten sind aber noch zu klein

Die Gartenzune in den Zellen bedeuten hier, dass die Zellen zu schmalsind, um die bertragene Formatierung aufzunehmen. Manuell knnen Siedies korrigieren, indem Sie einen Doppelklick auf die einzelnen Spaltenbe-grenzungen durchfhren. Selbstverstndlich knnen Sie diese Aufgabe auchelegant durch ein Makro ausfhren. Ergnzen Sie daher das Makro aus Lis-ting 2.29 wie folgt:

70

Sub Formatbertragen()Sheets("Tabelle13").ActivateRange("A1").Copy Range("B1:G1").Select Selection.PasteSpecial _ Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("B1:G1").Columns.AutoFitEnd Sub

Listing 2.30: Formate bertragen mit anschlieender Spaltenanpassung

Mit Hilfe der Methode AutoFit passen Sie die Spalten automatisch an. DieseSpalten, die Sie angeben mssen, werden dann so vergrert, dass derenInhalte vollstndig angezeigt werden.

Die Spalten B1:G1 wurden automatisch angepasst

Formate entfernenEntfernen knnen Sie Formate mit Hilfe der Methode ClearFormats. Im Lis-ting 2.31 werden auf der TABELLE14 im Bereich B2:B10 alle Zellenformatie-rungen entfernt.

Sehen Sie sich vorher die Ausgangsituation an.


71

Die Spalte B wurde mit _ und zwei Nachkommastellen formatiert

Entfernen Sie jetzt alle Formatierungen in Spalte B und setzen fr diese Auf-gabe das Makro aus Listing 2.31 ein.

Sub FormateLschen() Sheets("Tabelle14").Activate Range("B2:B10").ClearFormatsEnd Sub

Listing 2.31: Formate entfernen

Die Methode ClearFormats lscht jegliche Formatierung einer Zelle bzw.eines Zellenbereiches.

72

Alle Formate in Spalte B wurden entfernt

Arbeiten mit Kommentaren Kommentare knnen Sie in Excel u. a. einsetzen, um wichtige Vermerke zubestimmten Zelleninhalten zu setzen oder auch, um nderungen an Zellenzu dokumentieren. Ob fr eine Zelle ein Kommentar vorgenommen wurde,erkennen Sie normalerweise am roten Dreieck in der rechten oberen Eckeder Zelle, sofern die Ansicht dieses Indikators unter EXTRAS/OPTIONEN/ANSICHTin Excel eingestellt ist.

Achtung

Aufpassen mssen Sie, wenn Sie die Formatierung von Datumszellenentfernen. Dabei werden die Datumsangaben in Zahlenwerte umge-wandelt. Intern rechnet Excel Datumsangaben in Zahlenwerte um. Sostellt das Datum 21.03.2002 die Zahl 37338 dar. Excels Standardda-tumsrechnung beginnt am 01.01.1900. Die Zahl 37338 bedeutet, dassgenau so viele Tage in der Zwischenzeit vergangen sind.

Arbeiten mit Kommentaren

73

Kommentare einstellen

Wenn Sie den Mauszeiger auf eine Zelle positionieren, wird automatisch einkleines Textfenster eingeblendet, welches den Kommentar beinhaltet. SehenSie nun anhand einiger Praxisbeispiele, wie Sie mit Kommentaren in Excelarbeiten.

Kommentare einfgenWenn Sie Kommentare in Excel erfassen, wird standardmig oberhalb dereigentlichen Notiz der Name des Bearbeiters im Kommentar eingetragen.Besser wre es, in einem Kommentar noch zustzlich das Datum und dieUhrzeit zu erfassen, um Rckschlsse auf die Aktualitt der Kommentareziehen zu knnen. Oft schlummern Kommentare jahrelang in Zellen, ohneberhaupt noch relevant zu sein. Um Anwender nicht zu verunsichern, soll-te in Kommentaren daher neben den eigentlichen Erfassern der Notiz immerauch eine Datumsangabe stehen.

74

Einen Kommentar erfassen

Das folgende Makro in Listing 2.32 fgt einen solchen Kommentar in dermomentan aktiven Zelle ein.

Sub KommentarErfassen()Dim Kom As CommentDim s As String

Sheets("Tabelle15").Activate s = InputBox _ ("Geben Sie Ihren Kommentar ein!", _ "Kommentar erfassen") If s = "" Then Exit Sub Set Kom = ActiveCell.AddComment Kom.Text Application.UserName & Chr(10) _ & Date & Chr(10) & Time & " Uhr" & Chr(10) & s With Kom.Shape.TextFrame .Characters.Font.Name = "Courier" .Characters.Font.Size = 12 .AutoSize = True End WithEnd Sub

Listing 2.32: Kommentar in aktive Zelle einfgen


75

Definieren Sie zuerst eine Variable vom Typ Comment. Danach geben Sie ineiner Inputbox Ihren Kommentar ein. Mit Hilfe der Methode AddCommentfgen Sie in der aktiven Zelle zunchst noch einen leeren Kommentar ein.ber die Eigenschaft UserName ermitteln Sie den Namen des Benutzers,welchen Excel brigens aus der Einstellung unter EXTRAS/OPTIONEN/ALLGEMEINholt. Danach setzen Sie das Verkettungszeichen & ein, um dem Kommentar-fenster weitere Informationen hinzuzufgen. Die Funktion Chr(10) bewirkteinen Zeilenvorschub, die Funktionen Date und Time sorgen fr die ge-wnschten Zusatzinformationen fr den Kommentar. Am Ende fgen Sieden eigentlichen Kommentartext ein, den Sie vorher in der String-Variablens zwischengespeichert haben. Wenn Sie mchten, knnen Sie anschlieendnoch die Schriftart bzw. die Schriftgre des Kommentars anpassen.

Genaue Informationen zur vorgenommen Kommentierung

Kommentare schnell findenSollte die Ansicht des roten Indikators im Men EXTRAS unter OPTIONEN aufdem Registerblatt ANSICHT deaktiviert sein, ist es recht schwer, Kommentarein Tabellen aufzufinden. Kommentare finden Sie am schnellsten, wenn Sie

Hinweis

Ganz wichtig ist die Eigenschaft AutoSize. Diese Eigenschaft sorgtdafr, dass sich die Gre des Kommentarfensters automatisch demeingegebenen Text anpasst.

76

den Dialog GEHE ZU aufrufen, die Schaltflche INHALTE klicken und die OptionKOMMENTARE anklicken.

oSelbstverstndlich knnen Sie hierfr auch noch schneller ein kleines Makroschreiben, welches alle Zellen markiert, die Kommentare enthalten. Zu-stzlich sorgt es dafr, dass der rote Indikator wieder angezeigt wird. SehenSie sich nun einmal die folgende Abbildung an:

Es sind mehrere Kommentare auf der Tabelle eingefgt worden

Markieren Sie jetzt alle Kommentarzellen, indem Sie das Makro aus Listing2.33 starten.


77

Sub KommentareFinden() Sheets("Tabelle15").Activate On Error Resume Next Selection.SpecialCells(xlCellTypeComments).Select If Application.DisplayCommentIndicator = 0 Then _ Application.DisplayCommentIndicator = 1End Sub

Listing 2.33: Kommentarzellen markieren

Auch in diesem Beispiel knnen Sie wieder die Methode SpecialCellseinsetzen, der Sie die Konstante xlCellTypComments bergeben. IndemSie die Eigenschaft DisplayCommentIndicator auf den Wert 1 setzen oderdie Konstante xlCommentIndicatorOnly verwenden, wird der Indikator inKommentarzellen wieder angezeigt.

Alle Kommentarzellen sind nun markiert

Weitere Mglichkeiten der Kommentaranzeige entnehmen Sie der folgen-den Tabelle.

78

Tabelle 2.7: Die Ansicht von Kommentaren regeln

Kommentare lschenFr das Lschen von Kommentaren setzen Sie die Methode ClearCommentsein. Dabei knnen Sie wiederum die Methode SpecialCells einsetzen, diealle Kommentare vorher markiert, um sie anschlieend zu lschen.

Im nchsten Beispiel in Listing 2.34 werden alle Kommentare ohne weitereRckfrage auf der TABELLE15 gelscht.

Sub KommentareLschen()Sheets("Tabelle15").Activate On Error Resume Next Selection.SpecialCells(xlCellTypeComments).Select Selection.ClearCommentsEnd Sub

Listing 2.34: Alle Kommentare einer Tabelle lschen

Die Kommentare wurden gelscht, die Markierung bleibt erhalten.

Konstante Index Beschreibung

XlNoIndicator 0 Es wird kein Indikator angezeigt.

xlCommentIndicatorOnly 1 Es wird lediglich der Indikator ange-zeigt.

xlCommentAndIndicator 2 Es werden sowohl der Indikator als auch der Kommentar dauerhaft ange-zeigt.


79

Kommentare formatierenStandardmig werden Kommentare in Excel in der Schriftart TAHOMA undder Schriftgre 8 formatiert. Wenn Ihnen diese Formatierung nicht geflltund Sie alle Ihre Kommentare in Ihrer Tabelle umformatieren mchten, dannstarten Sie das Makro aus Listing 2.35. Dabei werden alle Kommentare derTABELLE15 mit der Schriftart ARIAL und der Schriftgre 10 formatiert.

Sub KommentareFormatieren()Dim Kom As Comment

Sheets("Tabelle15").Activate For Each Kom In ActiveSheet.Comments With Kom.Shape.TextFrame.Characters.Font .Name = "Arial" .Size = 10 End With Next Kom Application.DisplayCommentIndicator = _ xlCommentIndicatorOnlyEnd Sub

Listing 2.35: Kommentaren andere Schriftart bzw. Schriftgre zuweisen

80

In einer For Each-Schleife werden alle Kommentare des Tabellenblattes er-mittelt. Danach sprechen Sie den Kommentar an, indem Sie von auen nachinnen gehen. Es wird also erst das Objekt Shape angesprochen, das zunchstdie uere Form des Kommentars darstellt. Danach wird das TextFrame-Objekt angesprochen, welches sich um die Ausrichtung des Kommentarskmmert. Jetzt wird das Objekt Characters angegangen, welches die Zei-chen innerhalb des Textrahmens eines Kommentars darstellt. Nun fehlt nurnoch das Objekt Font, ber das Sie die Schriftart sowie den Schriftgrad desKommentars festlegen knnen.

Alle Kommentare wurden formattechnisch angepasst

Bereiche und Zellen benennenSelbstverstndlich knnen Sie zur leichteren Navigation auf Tabellenblt-tern und zur besseren bersichtlichkeit Ihres Quellcodes einzelnen Zellenoder Bereichen auch einen Namen geben und diese dann ber diesen Na-men ansprechen.

Ob in einer Arbeitsmappe Namen verwendet werden, erfahren Sie, indemSie aus dem Men EINFGEN den Befehl NAMEN/DEFINIEREN auswhlen. In ei-nem Listenfeld sehen Sie alle in der Arbeitsmappe verwendeten Namen.

Bereiche und Zellen benennen

81

Zellen benennen

Bereich benennenDas folgende Beispiel in Listing 2.36 gibt dem Zellbereich A1:A10 auf derTABELLE16 den Namen NAMENSBEREICH und markiert diesen sofort im Anschlussdaran.

Sub BereichBenennen()Dim Bereich As Range

Sheets("Tabelle16").Activate Set Bereich = Sheets("Tabelle16").Range("A1:A10") ActiveWorkbook.Names.Add _ Name:="NamensBereich", _

Achtung

Bei der Vergabe eines Namens mssen Sie darauf achten, dass Sie dieallgemeinen Namenskonventionen einhalten. Dabei muss das ersteZeichen des Namens ein Buchstabe oder auch ein Unterstrich sein.Beachten Sie auch, dass Namen nicht wie Zellenbezge benannt wer-den drfen. Bei der Lnge eines Namens knnen Sie bis zu 255Zeichen verwenden, was in der Programmierung jedoch eherumstndlich ist. Hier sollten Sie sich auf kurze, aber sprechendeNamen verstndigen. Eine weitere Einschrnkung bei der Namenver-gebung ist, dass Sie Namen nicht aus mehreren Wrtern bilden dr-fen, d.h. konkret, dass Sie keine Leerzeichen verwenden drfen. DieGro- und Kleinschreibung spielt bei der Vergabe von Namen jedochkeine Rolle.

82

RefersTo:=Bereich, Visible:=True Bereich.SelectEnd Sub

Listing 2.36: Bereich benennen und markieren

Definieren Sie zuerst den gewnschten Bereich und speichern ihn in derVariablen Bereich. Danach wenden Sie die Methode Add an, um einenNamen festzulegen. Die Methode Add weist folgende Argumente auf:

Im ersten Argument Name geben Sie den gewnschten Namen an, unter demSie spter den Zellenbereich ansprechen mchten. Dabei knnen Sie denNamen frei whlen. Sie mssen allerdings darauf achten, dass Sie bei derNamensgebung die vorher beschriebenen Namenskonventionen einhalten.Den gewhlten Namen mssen Sie in Anfhrungszeichen setzen. Das Argu-ment RefersTo gibt an, auf welchen Zellbereich sich der Name beziehensoll. Das Argument Visible bestimmt, ob der Name fr den Anwendersichtbar sein soll oder nicht. Verwendete Namen knnen Sie in Excel anzei-gen lassen, indem Sie den Befehl NAME/DEFINIEREN aus dem Men EINFGENwhlen. Setzen Sie das Argument Visible auf den Wert False, wenn Sieverhindern mchten, dass der Anwender den von Ihnen gewhlten Nameneinsehen kann. Die Standardeinstellung fr dieses Argument ist jedochTrue, was bedeutet, dass sowohl die verwendeten Namen als auch derenZellenadressen vom Anwender eingesehen werden knnen.

Bereich benennen


83

Gehen Sie jetzt wie folgt vor, um die interne Namensvergabe von Excel zuberprfen:

1 Markieren Sie in der TABELLE16 den Zellenbereich A1:A10.2 Whlen Sie aus dem Men DATEI den Befehl DRUCKBEREICH/DRUCKBEREICH FEST-LEGEN.

3 Whlen Sie aus dem Men EINFGEN den Befehl NAMEN/DEFINIEREN.

Der Name Druckbereich wird von Excel intern verwendet

Tipp

Excel verwendet auch intern Namen. So wird der Name DRUCKBEREICHautomatisch vergeben, wenn Sie auf Ihrem Tabellenblatt einen Druck-bereich erstellen. Ebenso wird der Namen DRUCKTITEL von Excel internbenutzt, wenn Sie Wiederholungszeilen im Men DATEI unter demBefehl SEITE EINRICHTEN auf der Registerkarte TABELLE definieren. Zweiweitere reservierte Namen, die Sie nicht verwenden sollten, lautenZIELBEREICH bzw. SUCHKRITERIEN. Diese beiden Namen werden automa-tisch festgelegt, wenn Sie eine Tabelle mit dem Spezialfilter aus demMen DATEN auswerten.

84

Mehrere Zellen benennenEs ist ziemlich langwierig, wenn Sie fr mehrere Zellen auf Ihrem Tabellen-blatt Namen vergeben mchten. Die Namen mssen nmlich einer nachdem anderen manuell festgelegt werden. Diese Aufgabe knnen Sie durchein Makro automatisieren. Dabei werden im Listing 2.37 auf dem Tabellen-blatt TABELLE17 die Zellen A1:A12 jeweils mit dem jeweiligen Zellenwert alsNamen ausgestattet. Geben Sie fr diesen Zweck die zwlf MonatsnamenJanuar bis Dezember in die Zellen A1:A12 ein und starten danach das Makroaus Listing 2.37.

Sub MehrereZellenBenennen()Dim Bereich As RangeDim i As Integer

Sheets("Tabelle17").Activate Range("A1").Select For i = 1 To 12 Set Bereich = Range("A" & i) ActiveWorkbook.Names.Add _ Name:=ActiveCell.Value, _ RefersTo:=Bereich, Visible:=True ActiveCell.Offset(1, 0).Select Next IEnd Sub

Listing 2.37: Zelleninhalte fr Namensvergabe verwenden

Aktivieren Sie zu Beginn des Makros die Tabelle, auf der Sie die automati-sche Namensvergabe durchfhren mchten. Danach setzen Sie den Maus-zeiger auf die Startzelle A1. Durchlaufen Sie danach eine Schleife genau 12Mal. Bei jedem Schleifendurchlauf basteln Sie sich den Bereich zusammenund speichern ihn in einer Objektvariablen. Bilden Sie dann den Namen di-rekt aus der gerade aktiven Zelle. Setzen Sie hierfr die Methode Add ein undweisen im Argument Name den aktuellen Zellenwert zu.


85

Zellen fr die Namensvergabe heranziehen

Bezge von benannten Bereichen ermittelnWenn Sie ber das Men EINFGEN und den Befehl NAME/DEFINIEREN gehen,knnen Sie sehen, welcher Zellenbezug zum jeweiligen Namen gehrt.

In VBA knnen Sie die Zellenbezge zu den verwendeten Namen auslesen,indem Sie das Makro Listing 2.38 starten.

Sub BereichsadresseErmitteln()Dim Bereich As Range

Sheets("Tabelle16").Activate Set Bereich = Range(Range("NamensBereich").Address) MsgBox Bereich.Address(external:=True)End Sub

Listing 2.38: Die Zellenadresse von benannten Bereichen ermitteln

Mit Hilfe der Eigenschaft Address knnen Sie den Zellenbezug ermitteln,welcher hinter dem Namen NAMENSBEREICH steckt. Diesen Bezug geben Siedanach auf dem Bildschirm ber die Funktion Msgbox aus.

86

Die genaue Adresse des benannten Bereichs ermitteln

Zellen bearbeitenOft liegen Daten nicht ganz so vor, wie man sie eigentlich bruchte. Einemanuelle Umformatierung ist dann eine manchmal stundenfllendeBeschftigung. Diese Arbeit knnen Sie automatisch ber den Einsatz vonMakros durchfhren. Lernen Sie auf den nchsten Seiten ein paar typischeBeispiele aus der Praxis kennen, die den Einsatz von Makros geradezu for-dert.

Zellen auseinander nehmenStellen Sie sich vor, Sie haben eine Liste mit Vor- und Nachnamen vorliegen.Leider wurden beide Namen in einer Zelle erfasst, wie Sie in der nchstenAbbildung sehen knnen.

Zellen bearbeiten

87

Die Ausgangssituation

Ihre Aufgabe besteht nun darin, die Namen zu trennen. Dabei sollen in Spal-te B die Vornamen und in Spalte C die Nachnamen geschrieben werden.

Extrahieren Sie die Vornamen aus der Spalte A und bertragen diese in Spal-te B. Das Makro fr diese Aufgabe knnen Sie in Listing 2.39 sehen.

Sub ZellenAuseinderNehmen()Dim i As Integer

Sheets("Tabelle18").ActivateRange("A1").SelectDo Until ActiveCell.Value = ""i = InStr(ActiveCell.Value, " ")ActiveCell.Offset(0, 1).Value = _Left(ActiveCell.Value, i 1)ActiveCell.Offset(1, 0).SelectLoopEnd Sub

Listing 2.39: Den Vornamen extrahieren

Aktivieren Sie zuerst die Tabelle, auf der die Namen aufgelistet sind. Danachsetzen Sie den Mauszeiger auf die Startzelle der Tabelle und fgen eineSchleife ein, die so lange durchlaufen wird, bis die erste leere Zelle in der

88

Tabelle festgestellt wird. Innerhalb der Schleife ermitteln Sie ber die FunktionInstr, an welcher Position genau das Leerzeichen zwischen Vor- und Nach-namen vorkommt, und speichern Sie diese Information in der Variablen i.Im nchsten Schritt verweisen Sie auf die Nebenzelle, indem Sie die Eigen-schaft Offset einsetzen und als Spaltenargument den Wert 1 angeben. Nunkommt die Funktion Left ins Spiel. Mit Hilfe dieser Funktion bertragen Sieeine bestimmte Anzahl von Zeichen vom linken Zellenrand her gesehen indie Nebenzelle. Die genaue Anzahl der zu bertragenden Zeichen habenSie vorher ber die Funktion Instr ermittelt. Da bei der Zhlung das Leer-zeichen auch mitgezhlt wurde, subtrahieren Sie davon noch den Wert 1, da-mit dieses Leerzeichen nicht mitbertragen wird. Vergessen Sie nicht, denZellenzeiger nach der Bearbeitung eine Zelle weiter nach unten zu setzen.Weisen Sie dazu der Eigenschaft Offset als Zeilenargument den Wert 1 zu.

Die Vornamen wurden bertragen

Extrahieren Sie jetzt aus der Spalte A die Nachnamen.

Zellen bearbeiten

89

Sub ZellenAuseinderNehmenTeil2()Dim i As IntegerDim e As Integer

Sheets("Tabelle18").ActivateRange("A1").SelectDo Until ActiveCell.Value = ""i = InStr(ActiveCell.Value, " ")e = Len(ActiveCell.Value)ActiveCell.Offset(0, 2).Value = _ Mid(ActiveCell.Value, i + 1, e 1)ActiveCell.Offset(1, 0).SelectLoopEnd Sub

Listing 2.40: Den Nachnamen extrahieren

Aktivieren Sie zuerst die Tabelle, auf der die Namen aufgelistet sind. Danachsetzen Sie den Mauszeiger auf die Startzelle der Tabelle und fgen eineSchleife ein, die so lange durchlaufen wird, bis die erste leere Zelle in derTabelle festgestellt wird. Innerhalb der Schleife ermitteln Sie ber die Funk-tion Instr, an welcher Position genau das Leerzeichen zwischen Vor- undNachnamen vorkommt, und speichern diese Information in der Variablen i.Im nchsten Schritt verweisen Sie auf die Nebenzelle, indem Sie die Eigen-schaft Offset einsetzen und als Spaltenargument den Wert 1 angeben. MitHilfe der Funktion Mid knnen Sie einen Teil einer Zelle extrahieren. Dabeisetzen Sie bei der ermittelten Position des Leerzeichens auf und addierenden Wert 1. Sie si

Addison Wesley - VBA Mit Excel

Documents

Transcript of Addison Wesley - VBA Mit Excel