addison wesley - vba mit excel
TRANSCRIPT
-
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.
-
Wie gehe ich von Anfang an richtig vor?
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).
-
Wie gehe ich von Anfang an richtig vor?
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:
-
Die Symbolleiste Bearbeiten
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.
-
Die Symbolleiste Bearbeiten
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.
-
Der Einsatz von Farben, Rahmen und Schriftschnitten
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:
-
Der Einsatz von Farben, Rahmen und Schriftschnitten
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
-
Der Einsatz von Farben, Rahmen und Schriftschnitten
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
-
Rechenoperationen mit Excel
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-
-
Rechenoperationen mit Excel
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.
-
Zahlenformate festlegen und ermitteln
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
-
Zahlenformate festlegen und ermitteln
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.
-
Zahlenformate festlegen und ermitteln
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
-
Arbeiten mit Kommentaren
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.
-
Arbeiten mit Kommentaren
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.
-
Arbeiten mit Kommentaren
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
-
Bereiche und Zellen 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.
-
Bereiche und Zellen benennen
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