Excel VBA – Arbeiten mit Excel Elementen
Excel VBA Programmierung – Teil 2
HARUN KAPLAN
EXCEL VBA – ARBEITEN MIT EXCEL ELEMENTEN
EXCEL VBA PROGRAMMIERUNG –
TEIL 2
ISBN 978-87-403-2225-5
INHALT
Über den Autor: Harun Kaplan 5
Vorwort 7
1 Programmierung 8
1.1 Ereignisprozeduren 8
1.2 Arbeitsmappen – Excel Dateien - Workbooks 13
1.3 Tabellenblätter „Worksheets“ 22
1.4 Umgang mit Zellen und Bereichen „Range“ / „Cells“ 33
1.5 Zellen befüllen 47
1.6 Umgang mit der Methode „SpecialCells“ 59
1.7 Umgang mit Kommentaren 62
1.8 Zellen formatieren 65
1.9 Versetzen des Cursors 72
1.10 Zelle verlinken 73
1.11 Optimale Spaltenbreite und Zeilenhöhe einstellen 74 1.12 Druckbereich und Kopf-, Fußzeile definieren 76
1.13 Autofilter setzen 80
Literaturverzeichnis 83
aok.de/nordost Gesundheit in besten Händen
AOK-StarterBonus
Das Vorteilspaket für gesundheitsbewusste und aktive Berufsstarter, Azubis und Stu- dierende bringt bis zu 930 Euro Extra-Geld im Jahr. Gesund leben zahlt sich aus.
Ihre AOK Nordost informiert Sie gern.
ÜBER DEN AUTOR: HARUN KAPLAN
Harun Kaplan wurde am 25. März 1964 in Ankara – Türkei geboren. Dort verbrachte er seine Kindheit und besuchte die Haupt- und Mittelschule. Er lebte mit seiner Mutter, vier Geschwistern und seinem Großvater zusammen. Sein Taschengeld verdiente er sich an Bahnhöfen und Haltestellen durch Wasser verkaufen beziehungsweise Schuhe polieren.
Er wollte Offizier werden, scheiterte aber an der zweiten Aufnahmeprüfung in Istanbul.
Im Zuge der Familienzusammenführung kam er dann zu seinem Vater nach Deutschland.
Da seine Schulbildung in Deutschland nicht anerkannt wurde, holte er den Hauptschulabschluss nach und lernte parallel die deutsche Sprache. Während dessen fing er bei Daimler AG in Stuttgart in der Küche als Spüler zu arbeiten an. Zugleich half er seinen Freund als Verkäufer in der Markthalle.
Ein Jahr später bestand er die Aufnahmeprüfung für eine Ausbildungsstelle als Betriebsschlosser, heute Industriemechaniker, bei Daimler AG.
Schon während Ausbildungszeit entschloss er sich, weiter zu studieren. Dazu machte er parallel die Fachhochschulreife, um in die Hochschule gehen zu können. Nach der Ausbildung fing er als Transferstraßen-Bediener in der Fertigung an zu arbeiten. Dies war auch die notwendige Berufserfahrung für die Hochschule.
Er kaufte damals von einem älteren Kollegen sein ersten Rechner, ZX81 Sinclair mit 1KB Memory angeschlossen am Fernseher als Monitor, dann einen Commodore 64, dann Commodore 128 und dann endlich einen superschnellen PC mit 128 MB Arbeitsspeicher mit eigenem Monitor. So fing er an zu programmieren. Erst mit GW-Basic und später mit Turbo Pascal in der Technikerschule.
Nach seiner drei jährige Berufserfahrung, meldete er sich in der Robert-Bosch-Abend- Technikerschule in Zuffenhausen an. Sie dauerte vier lange Jahre. Er schloss sie im Jahr 1987 erfolgreich ab. Drei Jahre später, fing er in der Entwicklung im Messraum als Prüfstellenleiter an zu arbeiten. Das war für ihn eine Umstellung vom Arbeiter in Angestellte.
Im Jahr 1992 wechselte er in die Diesel-Motorenentwicklung. Dort entwickelte er unterschiedliche Bauteile. Dabei war eine Datenbank für seine Bauteile mit vielen Daten zu pflegen. Er lernte „learning by doing“ die Excel VBA-Programmierung. Hinzu kamen viele Excel-VBA-Fragen von seinen Kollegen, was seine VBA-Kenntnis weiter vertiefte.
Sein Traum war ein Buch in Bibliotheken von sich zu haben. Ein Buch, welches möglichst viele VBA-Fragen beantwortet, die sehr häufig gestellt werden.
Er ist Vater von drei Kindern.
Er war zwischen 1992-2000 EDV-Lehrer in der Volkhochschule in Stuttgart und an unterschiedlichen Kulturzentren.
Er trainierte bis vor zehn Jahren aktiv regelmäßig Kampfsport. Heute trainiert er nur um fit zu bleiben. Er hat den 1. Dan in Shotokan-Karate sowie in Ken-Jitsu-Karate und wurde im Jahr 1982 Süddeutscher-Vize-Meister, ein Jahr später Deutscher Meister im Kickboxen.
Heute arbeitet er bei Daimler AG als Entwicklungs-Ingenieur in Stuttgart.
VBA-Programmierung erleichtert sein tägliches Arbeiten sehr.
VORWORT
Im ersten Buch „Excel VBA - Einführung“ haben wir viele Allgemeininformationen, wie Editor-Umgebung und Sprachkonzept der VBA-Programmierung kennengelernt. Ab diesem Buch fangen wir mit dem Programmieren an. Die meiste Beispiele sind aus der Praxis.
Sie fangen mit einfachsten Elementen der VBA - Sprache an und werden mit steigendem Niveau aufgebaut.
Im zweiten Buch „Excel VBA - Arbeiten mit Excel Elementen“ geht es hauptsächlich um:
• Arbeitsmappen „Workbooks“,
• Tabellenblätter „Worksheets“,
• Umgang mit Zellen und Bereichen „Range“ / „Cells“,
• Umgang mit der Methode „SpecialCells“,
• Funktion / Formel einfügen,
• Umgang mit Kommentaren
Auch hier als Zielgruppe für das Buch wurde der Einsteiger bis hin zum fortgeschrittenen Anwender vorgesehen.
Harun Kaplan
1 PROGRAMMIERUNG
Wenn wir die VBA-Programmierung als ein Körper betrachten, die Themen bis jetzt waren dessen Hände und Füße. Ab jetzt fangen die Innereien des VBA-Körpers. Hier ist auch umfangreich, aber angenehm spannend. Wir werden von einem Thema zum Nächsten begleitet.
1.1 EREIGNISPROZEDUREN
Ein Ereignis ist ein Ist-Zustand. Das heißt, eine Prozedur wird nach bestimmten Zustand ausgeführt wird. Anders beschrieben, um eine Prozedur ausführen zu können, muss ein bestimmter Ist-Zustand zutreffen. Jede Datei, jede Tabelle wird in ereignisorientierte Programmiersprache als Objekt behandelt.
Die wichtigsten Objektereignissen sind:
• Workbook ruft alle Ereignisse einer Datei auf. Durch einem Doppelklick auf die
“Diese Arbeitsmappe” im Projektfenster, gelangen wir zu dessen Bereich. Dann rufen wir dazu passende Ereignisprozedur auf. Beispielsweise „Open“; „BeforClose“;
„BeforSave“; „Activate“.
• Worksheet ruft alle Ereignis einer Tabelle auf. Durch einem Doppelklick auf die gewünschte Tabelle im Projektfenster gelangen wir zu dessen Bereich. Beispielsweise
„SelectionChange“, „Activate“; “Change“.
Die Namen der Ereignisprozeduren sind fest bzw. starr definiert.
Diese setzt sich zusammen aus:
¾ Objektnamen
¾ Unterstrich „_“ und
¾ Ereignisnamen.
Beispielsweise: „Workbook_open“ oder „Worksheet_Change“.
1.1.1 EREIGNISSE DER ARBEITSMAPPE „WORKBOOK_“
Alle „Workbook“-Ereignisse sind in der Tabelle unten aufgelistet. Wir werden hier „Open“
und „BeforeClose“ besprechen.
Nun. Zum ersten Beispiel mit „open“: Wenn wir unsere Excel-Datei öffnen, soll sie mich mit „Hallo Kollege“ begrüßen. Dazu sieht unsere Prozedur mit passendem Ereignis, „open“
so aus:
Private Sub Workbook_Open() Worksheets(“VBA”).Select Range(“A1”).Select
MsgBox “Hallo Kollege”
End Sub
Um open-Ereignis einzufügen, gehen Sie wie folgt vor:
• Projekt-Explorer der VBA-Entwicklung aufrufen,
• Auf die „DieseArbeitsmappe“ per Doppelklick gelangen wir in die VBA-Editor,
• Aus dem linken Listenfeld den Eintrag „Workbook“ und im rechten Listenfeld ein passendes Ereignis, hier „open“ selektieren,
• Dann schreiben wir unsere Prozedur.
Abbildung 1: Arbeitsmappen-Open-Ereignis erstellen
Nächstes Beispiel mit „open“: Wenn wir die Exceldatei geöffnet haben, werden folgende Aktionen durchgeführt:
• Die Tabelle „Datenbank“ vorselektiert,
• Mit dem Befahl „Call“ wird „Menue_Aufrufen“ ausgeführt,
• Mit .OnKey wird zu Funktionstaste „F9“ „Diagramm_erstellen“ zugeteilt,
• Mit .OnTime TimeValue ab angegebene Uhrzeit „Datenbak_aktualisieren“ ausgeführt.
Private Sub Workbook_Open() Worksheets(“Datenbank”).Select Call Menue_Aufrufen
Application.OnKey „{F9}“, „Diagramm_erstellen“
Application.OnTime TimeValue(“08:01:00”), “Datenbank_aktualisieren”
End Sub
Mit „BeforeClose“ führt VBA welche Aktionen vor dem Beenden der Exceldatei aus.
Ein Beispiel mit „BeforClose“: Wir haben im vorherigen Beispiel „Menue_Aufrufen“
aufgeführt. Den Inhalt setzen wir beim Beenden der Exceldatei zurück. Detailinformationen dazu werden wir im Kapitel „Eigene Menüerstellung“ später genauer anschauen.
Private Sub Workbook_BeforClose()
Application.CommandBars(“Worksheet Menu Bar”).Controls(“Datenbank öffnen”).Delete Application.CommandBars(“Worksheet Menu Bar”).Controls(“Aktivitäten”).Delete
End Sub
In unterer Tabelle sind alle Ereignisse des Objektes „Workbook“ aufgelistet.
Ereignisse des „Workbook“-Objekts
Activate Nach dem Wechsel der Excel-Datei ausgeführt AddInstall Ausgeführt, wenn Excel-Datei als AddIn installiert AddUninstall Ausgeführt, wenn Excel-Datei als AddIn deinstalliert
BeforeClose Vor dem Beenden der Excel-Datei ausgeführt BeforePrint Vor dem Drucken der Excel-Datei ausgeführt BeforeSave Vor dem Speichern der Excel-Datei ausgeführt
Deactivate Nach dem Wechsel der Excel-Datei ausgeführt
NewSheet Beim Einfügen eines neuen Blattes ausgeführt Open Beim Öffnen der Excel-Datei ausgeführt
SheetActivate Beim Wechsel eines Blattes (Tabelle wird aktiviert) ausgeführt SheetBeforeDobleClick Beim einem Doppelklick auf die jeweilige Tabelle ausgeführt SheetBeforeRightClick Ausgeführt, wenn rechte Maustaste in die jeweilige Tabelle betätigt SheetCalculate Ausgeführt, wenn Bezüge oder Formeln neu berechnet werden SheetChange Ausgeführt, wenn mindesten in eine Zelle geändert
SheetDeactivate Beim Wechsel eines Blattes ausgeführt
SheetSelectionChage Bei der Markierungsänderung in einem Blatt ausgeführt WindowActivate Bei einem Fensterwechsel ausgeführt
WindowDeactivate Bei einem Fensterwechsel ausgeführt WindowResize Bei Fenstergrößenänderung ausgeführt Tabelle 1: Ereignisse des Workbooks_Objektes
1.1.2 EREIGNISSE DER TABELLENBLÄTTER „WORKSHEET_“
Ein Tabellenblatt-Ereignis wird mit Worksheet-Objekt definiert. Dieses Ereignis wird bei einer Aktivierung / Deaktivierung eines Tabellenblattes, bei einer Änderung einer Zelle oder auch Pivot-Tabelle aktiviert. Hier gehen wir genauso wie in vorherigem Kapitel vor.
Um ein Tabellenblatt-Ereignis, hier „Activate“, einzufügen, gehen Sie wie folgt vor:
• Projekt-Explorer der VBA-Entwicklung aufrufen,
• die betreffende Tabelle markieren, hier „Tabelle1(VBA)“,
• aus dem linken Listenfeld „Worksheet“ selektieren,
• aus dem rechten das gewünschte Ereignis selektieren, hier „Activate“.
Abbildung 2: Tabellenblätter-Ereignis erstellen
Erstes Beispiel mit „Activate“. Sobald die jeweilige Tabelle gewählt „Aktiviert“, soll eine Meldung erscheinen:
Private Sub Worksheet_Activate() MsgBox „ Ich wurde gewählt!“
End Sub
Zweites Beispiel soll die aktive beziehungsweise selektierte Zelle mit der Farbe “gelb”
befüllen. Diese passiert aber nur in der Tabelle „Tabelle1(VBA)“. In anderen Tabellen hat dieses Ereignis keine Funktion.
Nun zu unserem Beispiel. Im Rumpf dieses Ereignisses sind die Angaben (ByVal Target As Range), das heißt sinngemäß „Aktivitäten im Bereich der Zelle“.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Selection.Interior
.ColorIndex = 6 .Pattern = xlSolid
.PatternColorIndex = xlAutomatic End With
End Sub
In unterer Tabelle sind alle Ereignisse des Objektes „Worksheet“ aufgelistet.
Ereignisse des „Worksheet“-Objektes
Activate Nach dem Wechsel einer Tabelle ausgeführt
BeforDobbleClick Bei einem Doppelklick innerhalb einer Tabelle ausgeführt
BeforRightClick Ausgeführt, wenn mit der rechten Maustaste innerhalb einer Tabelle geklickt Calculate Ausgeführt, wenn Bezüge oder Formeln neu berechnet werden
Chage Ausgeführt, wenn mindesten in eine Zelle geändert
Deactivate Ausgeführt, wenn aus dem Blattes zu einem anderem wechselt FollowHyperlink
PivotTableUpdate
SelectionChange Ausgeführt, wenn sich die Markierung in der Tabelle ändert Tabelle 2: Ereignisse des Worksheet-Objektes
1.2 ARBEITSMAPPEN – EXCEL DATEIEN - WORKBOOKS
Wir erkennen eine Excel-Datei an ihrer xls*-Endung. . Solche Exceldateien werden auch als Arbeitsmappe genannt.
Beim Deklarieren werden sie als Workbook-Objekt definiert.
Beispielsweise: Dim <Variabelname> As Workbook.
Wir werden das Thema „Arbeitsmappen / Workbooks“ in folgenden Abschnitten behandeln:
• Erzeugen .Add
• Öffnen .Open
• Speichern .Save / .SaveAs / .Saved
• Schließen .Closed
• Auswählen .Activate
Jede Methode hat teilweise eine lange Argumentenliste. In folgenden Beispielen werden wir zu jede Methode nur die wichtigsten Argumenten anschauen. Ausführlichere Informationen können im Helptext gelesen werden. Und zwar Cursor auf die Methode positionieren, dann
„F1“-Taste drücken.
1.2.1 ARBEITSMAPPE ERZEUGEN
Eine leere Arbeitsmappe wird mit „Workbooks.Add“ temporär erzeugt. Vor dem Beenden des Excels sollten wir sie noch speichern. Wenn wir keinen festen Speicherort angegeben haben, wird sie im „Standard“-Verzeichnis, also in das Verzeichnis „Eigene Dateien“ gespeichert.
Dieses Verzeichnis kann unter Menü „Extras/Optionen“ im Register „Allgemein“ kontrolliert beziehungsweise neues Verzeichnis angeben werden.
Die Endung .xls* muss nicht explizit angegeben werden. Denn sie wird beim Speichern automatisch angehängt.
Nun sehen wir ein paar Beispiele, wie per VBA so eine Arbeitsmappe erzeugt wird:
Sub Arbeitsmappe_anlegen_ohne_Speicherung() Workbooks.Add
End Sub
Sub Arbeitsmappe_anlegen_1_mit_Speicherung()
Workbooks.Add.SaveAs („Harun_Test2“) ‚gespeichert unter Eigene Dateien End Sub
Sub Arbeitsmappe_anlegen_2_mit_Speicherung()
Workbooks.Add.SaveAs (“C:\Excel\Harun_Test”) ‘gespeichert unter “C:\Excel”
End Sub
Sub Arbeitsmappe_anlegen_2_mit_SET() Dim NeueMappe As Workbook
Set NeueMappe = Workbooks.Add
‚NeueMappe.SaveAs („Harun_Test“) ‚gespeichert unter Eigene Dateien NeueMappe.SaveAs („C:\Excel\Harun_Test“) ‚gespeichert unter „C:\Excel“
End Sub
Mit der SET-Anweisung können wir unsere Arbeit etwas vereinfachen. Damit wird eine Variable zu der Anweisung zugewiesen. Diese Variable, hier „wbNeueMappe“ sollte davor mit der DIM-Anweisung als Workbook dimensioniert werden.
Das untere Beispiel gibt es in der Hilfe des SaveAs (Speichern unter) in VBA. Diese Prozedur erstellt eine neue Arbeitsmappe und fordert dem Benutzer einen Dateiname einzugeben.
Dann wird sie unter neuen Namen gespeichert.
Sub Arbeitsmappe_anlegen_mit_Anforderung_speichern() Dim NeueMappe As Workbook
Dim Neuer_Name As String Set NeueMappe = Workbooks.Add Do
Neuer_Name = Application.GetSaveAsFilename Loop Until Neuer_Name <> False
newbook.SaveAs Filename:=Neuer_Name End Sub
1.2.2 ARBEITSMAPPE ERMITTELN
Der Dateiname oder der Dateiname mit dem Pfad kann mit diesen beiden Eigenschaften ermittelt:
¾ ThisWorkbook => die Datei, aus der die Prozedur ausgeführt wurde,
¾ ActiveWorkbook => die Datei, die im aktiven Fenster (das Fenster im Vordergrund).
NUR Dateiname: <Eigenschaft>.Name
Dateiname mit Pfad: <Eigenschaft>.FullName beziehungsweise .FullNameURLEncoded Unsere Datei heißt Arbeitsmappe.xls. Unser Beispiel „Arbeitsmappe“ befindet sich auch hier. Nun rufen wir eine zweite Datei „Datei_ThisWorkbook.xls“ auf. Dann führen wir unser Beispiel aus:
Sub Arbeitsmappe()
Dim Name, Vollername, VollernameURL As String Dim Name2, Vollername2, VollernameURL2 As String Name = ThisWorkbook.Name
Vollername = ThisWorkbook.FullName
VollernameURL = ThisWorkbook.FullNameURLEncoded Name2 = ActiveWorkbook.Name
Vollername2 = ActiveWorkbook.FullName
VollernameURL2 = ActiveWorkbook.FullNameURLEncoded MsgBox „Makro wurde ausgeführt aus der Datei : „ & _
vbLf & Name & vbLf & Fullname & vbLf & strFullnameURL & vbLf & _
“Aktive Datei ist “ & vbLf & strName2 & vbLf & strFullname2 & vbLf &
strFullnameURL2 End Sub
Unser Ergebnis sieht so aus:
Abbildung 3: Arbeitsmappen Informationen in MsgBox
Ein Ergebnis mit einer Server-Angabe kann so aussehen:
Abbildung 4: Beispiel Fullname einer Datei
1.2.3 ARBEITSMAPPE ÖFFNEN
Zum Öffnen einer Arbeitsmappe ist „Workbooks.Open“ verantwortlich.
Wenn wir kein bestimmtes Verzeichnis angegeben haben, wird sie in dem Standardspeicherort gesucht.
Workbooks.Open („Harun_Test“) ‚geöffnet aus dem Standardspeicherort Beim nicht Finden wird eine Fehlermeldung ausgegeben.
Mit komplettem Verzeichnispfad sieht beispielsweise so aus:
Workbooks.Open („C:\Excel\Harun_Test“) ‚geöffnet aus dem Verzeichnis „C:\Excel“
Wenn welche Zellen aus der Tabelle Verknüpfungen haben, können sie zur Aktualisierung ein- oder ausgeschaltet werden. Diese wird mit „UpdateLinks:=1“ eingeschaltet oder mit
„UpdateLinks:=0“ ausgeschaltet.
Workbooks.Open („C:\Excel\Harun_Test“, UpdateLinks:=0, ReadOnly:=True) ‚geöffnet aus dem Verzeichnis „C:\Excel“, ohne Aktualisierung der Verknüpfungen, schreibgeschützt geöffnet Mit Passwort sieht wie folgt aus:
Workbooks.Open („C:\Excel\Harun_Test“, Password:=“Harun“) ‚Die Passwort geschützte Mappe aus dem Verzeichnis „C:\Excel“ zum Schreiben freigegeben.
Wir können auch eine Arbeitsmappe per Öffnen-Dialog selber auswählen. Dann könnte unser Beispiel so aussehen:
Sub Arbeitsmappe_Öffnen_Fenster_1() ‚aus dem Standardspeicherort Dim strDatei As String
‚Öffnen-Dialog aufrufen
strDatei = Application.GetOpenFilename(„Meine Excel-Dateien (*.xls*), *.xls*“)
‚Selektierte Arbeitsmappe öffnen
Workbooks.Open Filename:=strDatei End Sub
Sub Arbeitsmappe_Öffnen_Fenster() ‚aus dem vorgegebenem Verzeichnis Dim strZiel, strPfad As String
‚Pfad definieren
strPfad = „C:\KAPLAN“
ChDrive „C“
ChDir strPfad
‚Öffnen-Dialog aufrufen
strDatei = Application.GetOpenFilename(„Meine Excel-Dateien (*.xls*), *.xls*“)
‚Selektierte Arbeitsmappe öffnen
Workbooks.Open Filename:=strDatei End Sub
1.2.4 ARBEITSMAPPE SPEICHERN
Zum Schließen einer Arbeitsmappe ist Workbooks.Save verantwortlich. Zum Speichern der Arbeitsmappen gibt es folgende Eigenschaften und Methoden:
• SaveAs diese Methode speichert die Arbeitsmappe unter einem neuen Namen
• Save diese Methode speichert Änderungen in der angegebenen Arbeitsmappe
• Saved diese Eigenschaft prüft, ob eine Arbeitsmappe seit der letzten Änderung gespeichert wurde. Wenn ja Dann True.
Sub speichern_mit_saveas_1() ‘speichert die aktive Arbeitsmappe unter neuen Namen ab Workbook.SaveAs (“VBA.xlsx”)
End Sub
Sub speichern_mit_save_1() ‚speichert nur die aktive Arbeitsmappe ActiveWorkbook.Save
End Sub
Sub speichern_mit_save_2() ‚speichert alle offene Arbeitsmappe Dim AMappen As Workbook
For Each AMappen In Application.Workbooks AMappen.Save
Next AMappen End Sub
Mit dem nächsten Beispiel wird die Datei unter neuen Namen gespeichert, wenn sie seit letzter Änderung nicht gespeichert ist.
Grau ist alle Theorie.
Zeit für etwas Farbe.
kfw.de/karriere
Sub speichern_mit_saved() If ActiveWorkbook.Saved Then
MsgBox “Bereits gespeichert.”
Else
ActiveWorkbook.SaveAs (“Harun_TTest.xlsx”) End If
End Sub
1.2.5 ARBEITSMAPPE SCHLIESSEN
Zum Schließen einer Arbeitsmappe ist „Workbooks.Close“ verantwortlich. Alles was erstellt beziehungsweise geöffnet wurde, muss wieder geschlossen werden. Mit der Methode .Close wird eine Arbeitsmappe geschlossen. Wenn wir ohne Attributen, also nur mit der Close- Methode beenden und haben davor eine Änderung, dann bekommen wir eine „Speichern“- Meldung angezeigt.
Workbooks(„Harun_Test.xlsx“).Close ‚Die Arbeitsmappe wird geschlossen ActiveWorkbook.Close ‚Die aktive Arbeitsmappe wird geschlossen
ThisWorkbook.Close SaveChage:=True ‚Die Arbeitsmappe aus der Prozedur gestartet wurde, wird beendet mit Speichern.
Workbooks(„Harun_Test.xlsx“).Close SaveChanges:=True ‚Beenden mit Speichern Workbooks(„Harun_Test.xlsx“).Close SaveChanges:=False ‚Beenden ohne Speichern
Das folgende Beispiel beendet sämtliche Arbeitsmappen und speichert die darin vorgenommenen Änderungen, außer der Arbeitsmappe, aus der die Prozedur ausgeführt wird.
Sub beenden_alle_Dateien_Arbeitsmappen () For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then wb.Close SaveChange:=True
End If Next wb End Sub
1.2.6 DATEN IN DIE GESCHLOSSENE DATEI EINTRAGEN
Mit den beiden vorherigen Beispielen werden wir eine Prozedur so schreiben, dass damit einige Einträge in eine „geschlossene“ Datei eingetragen werden. Praktisch wird die Zieldatei im Hintergrund geöffnet, einzutragende Werte eingetragen und speichernd geschlossen.
Genauer Umgang mit Cells beziehungsweise mit Range werden wir später ausführlicher behandeln.
Sub SchreibeInEineExcelDatei() Dim strName, strVorname As String Dim intZelle As Integer
‚Bildschirmaktualliesierung deaktivieren Application.ScreenUpdating = False
‚Zieldatei wird im Hintergrund geöffnet
Workbooks.Open Filename:=”C:\Excel\Test_Harun.xls”
‚Letzte beschreibbare Zelle in der Zieldatei finden intZelle = Cells(Rows.Count, 1).End(xlUp).Row + 1
‚einzutragende Einträge strName = „Kaplan“
strVorname = „Harun“
‚Einträge
Cells(intZelle, 1).Value = strName Cells(intZelle, 2).Value = strVorname
‚Zieldatei speichernd schließen
ActiveWorkbook.Close SaveChanges:=True
‚Bildschirmaktualliesierung aktivieren Application.ScreenUpdating = True End Sub
Ich denke, unser Beispiel ist selbsterklärend. Deshalb machen wir weiter.
1.2.7 ANZAHL DER GEÖFFNETEN ARBEITSMAPPEN ERMITTELN
Mit der Funktion „Workbooks.Count“ können wir der Anzahl der geöffneten Arbeitsmappen ermitteln. Unser Beispiel dazu:
Sub AnzahlMappenErmitteln() Dim intAnzahl As Integer
intAnzahl = „Anzahl der geöffneten Arbeitsmappen: „ & Workbooks.Count MsgBox intAnzahl
End Sub
1.2.8 ARBEITSMAPPE / TABELLE SCHÜTZEN & SCHUTZ BEHEBEN
Selbstverständlich kann eine Arbeitsmappe beziehungsweise eine Tabelle geschützt und auch den Schutz behoben werden. Mit der Methode „Protect“ kann ein Arbeitsmappe / Arbeitsblatt vor Änderung geschützt beziehungsweise mit der Methode „Unprotect“ den Schutz behoben werden.
Das Beispiel dazu sieht wie folgt aus:
Sub Schützen_und_beheben() Dim strDateiname As String
strDateiname = ActiveWorkbook.Name
Workbooks(strDateiname).Protect ‚Arbeitsmappe Schutz aktiviert Workbooks(strDateiname).Unprotect ‚Arbeitsmappe Schutz deaktiviert End Sub
1.2.9 ZELLEN SCHÜTZEN / BEHEBEN
Bestimmte Zellen bei Bedarf mit der Methode „.Loced“ geschützt werden. Im ersten Beispiel wird der Bereich „C2:D5“ einer Tabelle mit der Methode „.Loced=True“ geschützt. Davor heben wir vom aktuellen Tabellenblatt mit der Methode „.Cells.Loced = False“ den Schutz auf.
Sub Bestimmter_Bereich_schützen() ActiveSheet.Unprotect „Harun“
ActiveSheet.Cells.Locked = False
ActiveSheet.Range(“C2:D5”).Locked = True ActiveSheet.Protect „Harun“
End Sub
Im nächsten Beispiel sehen wir gerade die Umkehrung des vorherigen Beispiels. Das heißt, das ganze Tabellenblatt wird mit der Methode „.Cells.Loced = True“ geschützt und bestimmter Bereich mit der Methode „.Loced = False“ Schutz aufgehoben.
Sub Bestimmter_Bereich_Schutz_aufheben() ActiveSheet.Unprotect “Harun”
ActiveSheet.Cells.Locked = True
ActiveSheet.Range(“C2:D5”).Locked = False ActiveSheet.Protect “Harun”
End Sub
1.3 TABELLENBLÄTTER „WORKSHEETS“
Im folgenden Kapitel behandeln wir die Möglichkeiten um Tabellenblätter per VBA – Code anzusprechen und nach unseren Wünschen anzupassen.
1.3.1 ZUGRIFF AUF TABELLENBLÄTTER
Für die Auswahl eines Tabellenblattes gibt es im VBA zwei Alternativen:
• Auswahl über den Index des Worksheets – Objekts Tabellenblatt
• Auswahl über den Namen des Worksheets – Objekts
Es gibt drei Arten, um den Namen der Tabellenblätter anzusprechen:
1. Direktes Ansprachen
Worksheets(„Tabelle1“).Activate
2. Direkte Angabe mit genauer Zellenbezeichnung.
Diese kann unter Umständen lange Anweisungen erzeugen:
Worksheets(„Tabelle1“).Cells(3,5).Value = 233 Worksheets(„Tabelle1“).Range(“E3“).Value = 233
3. Über SET-Definition deklariertem Objektvariable : Sub Ansprache()
Dim wsTabellenName as Worksheet
Set wsTabellenName = Worksheets(„Tabelle1“) wsTabellenName.Cells(3,5).Value = 123 End Sub
Nun, in einer Arbeitsmappe, also in der Excel-Datei können mehrere Tabellenblätter geben.
Deshalb werden sie mit der VBA-Methode „Worksheets“, also Mehrzahl eingegeben und zum Deklarieren als Worksheet-Objekt definiert.
Wir möchten das Thema „Tabellenblätter / Worksheets“ in folgenden Abschnitten behandeln.
• Erzeugen .Add
• Auswählen .Activate / .Select
• Kopieren / Verschieben .Copy / .Move
• Drucken .Print / .PrintPreview
• Löschen .Delete
• Einblenden / Ausblenden .Visible
1.3.2 TABELLENBLATT ERZEUGEN
Mit der „Worksheets.Add“-Methode kann ein neues Tabellenblatt eingefügt werden. Die Syntax der Add-Methode sieht mit ihren Attributen wie folgt aus:
Abbildung 5: Attribute der Add-Methode
In unserem nächsten Beispiel sind die Zellen der „Tabelle 1“ mit folgenden Informationen befüllt:
A15=Apfel, A16=Melone und A18=Birne.
Nun sollen drei Tabellenblätter mit den Namen „Apfel, Melone und Birne“ vor der Tabelle
„Bücher“ erstellt werden. Die erstellten Tabellenblätternamen bekommen jeweils eine Hintergrundfarbe, hier rot, zugeteilt. Nun sieht unsere Listing wie folgt aus:
Sub Tabellenblätter_erzeugen() Dim x As Integer
Dim Name As Range For x = 1 To 3
Set Name = Worksheets(“Tabelle1”).Range(“A” & 14 + x)
Worksheets.Add Before:=Sheets(„Bücher“) ‚3 Tabellenblätter vor der Tabelle („Bücher“) eingefügt
ActiveSheet.Name = Name.Value ‚Als Name werden Zelleninhalte ab A15 umbenannt ActiveSheet.Tab.ColorIndex = 3 ‚Die Hintergrundfarbe des Tabellenblattes definiert. 3 = rot Next x
End Sub
Auflisten diese Tabellenblätter kann wie folgt funktionieren:
Sub Welche_Tabellenblätter_gibt_es() Dim Anzahl, x As Integer
Anzahl = Worksheets.Count For x = 1 To Anzahl
Debug.Print Sheets(x).Name Next x
End Sub
Am nächsten Beispiel kontrollieren wir, ob eine bestimmte Tabelle existiert. Wenn nicht, dann soll sie angelegt werden:
Sub Kontrolle_ob_bestimmte_Tabelle_gibt() Dim wsName As Worksheet
Dim stgesucht As String Stgesucht=“Neue Name“
For Each wsName In Worksheets
If wsName.Name = stgesucht Then ‚Aktuelle Tabelle wird mit der gesuchte Tabelle verglichen MsgBox wsName.Name & „Es gibt“
Exit Sub Else End If Next wsName
Worksheets.Add Before:=Sheets(“Bücher”) ActiveSheet.Name = stgesucht
End Sub
1.3.3 TABELLENBLATT AUSWÄHLEN
Mit den Methoden „Worksheets.Activate“ oder „Worksheets.Select“ können wir eine Tabelle von den anderen Tabellen hervorheben. Am jetzigen Beispiel wird die gesuchte Tabelle nach dem Finden hervorgehoben. Wenn nicht, wird sie angelegt.
Sub Kontrolle_ob_bestimmte_Tabelle_gibt() Dim wsName As Worksheet
Dim stgesucht As String stgesucht=“Neue Name“
For Each wsName In Worksheets
If wsName.Name = stgesucht Then ‚Aktuelle Tabelle wird mit der gesuchte Tabelle verglichen MsgBox wsName.Name & „Es gibt“
Worksheets(stgesucht).Select Exit Sub
Else End If Next wsName
Worksheets.Add Before:=Sheets(“Bücher”) ActiveSheet.Name = stgesucht
End Sub
1.3.4 TABELLENBLATT KOPIEREN
Alle erstellten Tabellenblätter können mit der Methode „Worksheets.Copy“ oder „Sheets.
Copy“ kopiert werden. Beim Kopieren handelt es um das Klonen eines Tabellenblattes.
Dieses Blatt kann mit den Attributen „Before = vor angegebener Tabelle“ oder „After = nach angegebener Tabelle“ platziert werden.
Nach diesem Vorgang gibt es zwei exakt gleiche Tabellenblätter. Wenn das neue Tabellenblatt nicht umbenannt wird, bekommt die gleiche Benennung mit einem Index als Zahl in Klammer. Beispielsweise „Tabelle2“ wird als „Tabelle2 (2)“ geklont. Deshalb geben Sie immer gleich einen neuen Name an.
Nun sieht unser Beispiel dazu wie folgt aus:
Wir werden das Tabellenblatt „Tabelle2“ vor dem Tabellenblatt „Melone“ kopieren und dann auf „Neuer Name“ umbenennen.
Sub Tabellenblatt_kopieren()
Sheets(“Tabelle2”).Copy Before:=Sheets(“Melone”) ActiveSheet.Name = “Neuer Name”
End Sub
Wenn das geklonte Tabellenblatt am Ende der Tabellenblätter eingeordnet wird, fragen wir vorher Anzahl der Tabellenblätter ab.
Sub ans_Ende_stellen() Dim Anzahl As Integer
Anzahl = ThisWorkbook.Sheets.Count ‚Anzahl der Tabellenblätter abgefragt
Sheets(„Melone“).Copy After:=Sheets(Anzahl) ‚Die Tabelle „Melone“ klonen und ans Ende stellen
ActiveSheet.Name = „Neuer Name“ ‚Es bekommt einen neuen Namen End Sub
Es kommt sicherlich vor, dass das geklonte Tabellenblatt nicht in die gleiche Arbeitsmappe sondern in eine neue Arbeitsmappe eingefügt wird. Dann wie folgt:
Sub Tabellenblatt_kopieren()
Sheets(„Tabelle2“).Copy ‚Das geklonte Tabellenblatt wird in eine neue Arbeitsmappe eingefügt ActiveSheet.Name = „Neuer Name“ ‚Wenn notwendig, kann auch umbenannt werden End Sub
Wir können auch mehrere Tabellenblätter gleichzeitig klonen. In unserem Beispiel werden drei Tabellenblätter geklont und vor dem ersten Tabellenblatt eingefügt. Somit werden sie automatisch Sheets(1), Sheets(2), Sheets(3). Dann kann ich sie auch sehr einfach umbenennen.
Sub mehrfach_kopieren_Tabellen_am_Anfang()
Sheets(Array(“Neuer Name”, “Bücher”, “Melone”)).Select
Sheets(Array(“Neuer Name”, “Bücher”, “Melone”)).Copy Before:=Sheets(1) Sheets(1).Name = “Muster_1”
Sheets(2).Name = “Muster_2”
Sheets(3).Name = „Muster_3“
End Sub
Was passiert, wenn sie an Ende eingefügt werden? Ändert sich nicht viel. Hier arbeiten wir mit der ermittelten Anzahl der Tabellenblätter.
Sub mehrfach_kopieren_Tabellen_am_Ende() Dim Anzahl As Integer
Anzahl = ThisWorkbook.Sheets.Count
Sheets(Array(“Neuer Name”, “Bücher”, “Melone”)).Select
Sheets(Array(“Neuer Name”, “Bücher”, “Melone”)).Copy After:=Sheets(Anzahl) Sheets(Anzahl + 1).Name = „Muster_1“
Sheets(Anzahl + 2).Name = „Muster_2“
Sheets(Anzahl + 3).Name = „Muster_3“
End Sub
1.3.5 TABELLENBLATT VERSCHIEBEN
Zum Verschieben ist die Methode „Worksheets.Move“ oder „Sheets.Move“ zuständig. Das Verschieben ist ähnlich wie das Klonen eines Tabellenblattes. Der Unterschied besteht darin, dass es von der alten Stelle „ausgeschnitten“ und an der neuen Stelle „eingepflanzt“ wird.
Wir nehmen das gleiche Beispiel von Copy und ersetzten einfach die Copy-Methode in Move-Methode.
Sub Tabellenblatt_verschieben()
Sheets(„Tabelle2“).Move Before:=Sheets(„Melone“) ‚“Tabelle2“ wird vor “Melone“ verschoben ActiveSheet.Name = „Neuer Name“ ‚wenn notwendig, kann es umbenannt werden End Sub
Es kann auch an das Ende verschoben werden:
Sub ans_Ende_verschieben() Dim Anzahl As Integer
Anzahl = ThisWorkbook.Sheets.Count ‚Anzahl der Tabellenblätter abgefragt
Sheets(„Melone“).Move After:=Sheets(Anzahl) ‚Die Tabelle „Melone“ wird ans Ende verschoben End Sub
1.3.6 TABELLENBLATT LÖSCHEN
Das Tabellenblatt wird mit der Methode „Worksheets.Delete“ oder „Sheets.Delete“
unwiderruflich gelöscht.
Mit dem ersten Beispiel löschen wir das Blatt „Muster_1“.
Sub Tabellenblatt_löschen() Sheets(„Muster_1“).Delete End Sub
Es ist auch mehrfache Löschung von Tabellenblättern möglich.
Sub mehrfach_löschen ()
Sheets(Array(“Muster_1”, “Muster_2”, “Muster_3”)).Select ActiveWindow.SelectedSheets.Delete
End Sub
1.3.7 TABELLENBLATT ALS DATEI SPEICHERN
Ein gewünschtes oder aktuelles Tabellenblatt kann auch als eigenständige Excel-Datei gespeichert werden. Unser Beispiel dazu:
Sub Tabelle_abspeichern()
Dim strDateiName, strPfad As String strDateiName = ActiveSheet.Name strPfad = „C:\Kaplan\Kapitel_10\“
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=strPfad & strDateiName MsgBox „Die Datei befindet sich im Verzeichnis „ & vbLf & _
strPfad & vbLf & _
„wurde gespeichert als“ & vbLf & _ strDateiName & „.xlsx“
End Sub
Abbildung 6: Tabellenblatt als Datei abgespeichert
1.3.8 TABELLENBLATT EIN- /AUSBLENDEN
Die Tabellenblätter können mit den Methoden „Worksheets.Hidden“ und „Worksheets.
Visible“ ein- und ausgeblendet werden. Das Tabellenblatt wird aus der Excel-Oberfläche unsichtbar. Aber der Zugriff auf die vorhandenen Daten in ausgeblendetem Tabellenblatt weiterhin möglich.
Am unteren Beispiel wird die Tabelle „Harun“ erst ausgeblendet, die Zelle „A1“ ausgelesen, als MsgBox ausgegeben und sie wird wieder eingeblendet.
Sub aus_einblenden() Dim strInhalt As String
Worksheets(„Harun“).Visible = False ‚Die Tabelle Harun wird ausgeblendet
strInhalt = Worksheets(„Harun“).Range(„A1“).Value ‚Der Wert aus der Zelle A1 als String ausgelesen
MsgBox stInhalt ‚Ausgelesener Wert in MsgBox ausgegeben
Worksheets(„Harun“).Visible = True ‚Die Tabelle Harun wird wieder eingeblendet End Sub
Im folgenden Beispiel werden sämtliche Tabellenblätter der aktiven Arbeitsmappe erst aus- dann eingeblendet. Normalerweise soll ein Tabellenblatt bestehen bleiben. Wenn ich versuche alle Tabellenblätter auszublenden, bekomme ich beim letzten Tabellenblatt eine Fehlermeldung. Um diese Fehlermeldung zu umgehen, füge ich die On Error Resume Next – Anweisung ein. Nun unser Beispiel :
Sub AlleTabellenblätter_aus_einblenden() Dim wsName As Variant
On Error Resume Next ‚Fehlermeldung wird umgegangen
‚Alle Tabellenblätter ausblenden For Each wsName In Sheets
wsName.Visible = False Next wsName
‚Alle Tabellenblätter einblenden For Each wsName In Sheets
wsName.Visible = True Next wsName
End Sub
Nun machen wir etwas Einfaches. Und zwar Zeile „5“ und Spalte „F“ ausgeblendet und anschließend wieder eingeblendet werden.
Sub Zeile_Spalte_aus_einblenden () Rows(“5:5”).Select
With Selection
.EntireRow.Hidden = True .EntireRow.Hidden = False End With
Columns(“F:F”).Select With Selection
.EntireColumn.Hidden = True .EntireColumn.Hidden = False End With
End Sub
1.3.9 LISTEN ALLE TABELLENBLÄTTERNAMEN
Wenn wir die Tabellenblätternamen listen möchten, benötigen wir die Methode „Worksheets(x).
Name“.
Das nächste Beispiel listet uns alle vorhandene Sheets, auch ausgeblendete Sheets. Wir lassen erst Anzahl der vorhandenen Sheets mit der Eigenschaft Worksheets.Count feststellen und das Ergebnis zu „Anzahl“ zuweisen. Dann lassen wir „Anzahl“-mal durchlaufen.
Sub Welche_Tabellenblätter_gibt_es() Dim intAnzahl, x As Integer
intAnzahl = Worksheets.Count For x = 1 To intAnzahl
Debug.Print x & “.Tabelle : “ & Worksheets(x).Name Next x
End Sub
1.3.10 HINTERGRUNDFARBE TABELLENBLATTNAME ÄNDERN
Auch die Hintergrundfarbe kann, je nach Wunsch mit der Methode „.Tab.Colorindex“
geändert werden. Wenn wir die Tabellenblätter um 180° gedreht anschauen, sehen sie wie Register aus. Deshalb ist „.Tab“-Bezeichnung. Um die Hintergrundfarben dieser Tab’s zu ändern, benötigen wir „.Colorindex“.
Nun ein Beispiel dazu:
Sub Hintergrundfarbe()
Sheets(„Neuer Name“).Tab.ColorIndex = 3 End Sub
1.3.11 SORTIEREN DER TABELLENBLÄTTER
Wenn wir mal mehrere Tabellenblätter eingefügt haben, kann auch vorkommen, dass wir sie sortieren sollten. Dazu könnte unsere Listing wie folgt aussehen:
Die For-Next Schleife eignet sich sehr gut dazu um etwas zu sortieren. Auch die Tabellenblätter.
Wir können nach Wunsch aufsteigend oder absteigend sortieren.
Sub Blatter_Sortieren()
Dim Anzahl_Blätter, x, y As Integer
Anzahl_Blätter = ActiveWorkbook.Worksheets.Count For x = 1 To Anzahl_Blätter
For y = x To Anzahl_Blätter
If Worksheets(y).Name < Worksheets(x).Name Then Worksheets(y).Move Before:=Worksheets(x) End If
Next y Next x End Sub
1.3.12 LÖSCHBESTÄTIGUNG AUS- / EINSCHALTEN
Vor jedem Löschen eines Objektes (Tabelle, Zeile, Spalte, Zelle) werden wir sicherheitshalber befragt. Wenn diese Abfrage zu lästig sein sollte, kann sie mit dem Funktion .DisplayAlerts aus- dann wieder eingeschaltet werden.
Unser Beispiel dazu wie folgt aus:
Sub Tabellenblatt_löschen_ohne_Meldung()
Application.DisplayAlerts = False ‚Löschbestätigung ausschalten Sheets(„Muster_3“).Delete
Application.DisplayAlerts = True ‚Löschbestätigung wieder einschalten End Sub
Wenn wir sie ausgeschaltet haben, bitte am Ende der Listing wieder einschalten. Sonst werden alle Meldungen, die normalerweise am Bildschirm zu sehen sind, werden nicht mehr erscheinen. Diese kann unter Umständen gefährlich sein.
1.3.13 UNTERDRÜCKEN DER MELDUNG “VERKNÜPFUNG AKTUALISIEREN”
Wenn die zu öffnende Datei mindesten eine Verknüpfung beinhaltet, werden wir bei jedem Öffnen darauf hingewiesen, ob sie aktualisiert werden soll. Diese Meldung kann auch unterdrückt werden.
Abbildung 7: Meldung der Aktualisierungsabfrage
Wenn wir solche Tabellen per VBA öffnen und bearbeiten möchten, bleibt der Ablauf des Makros an dieser Stelle stehen. An solchen Fällen arbeiten wir entweder mit einer AskToUpdateLinks-Eigenschaft oder mit dem Parameter der Open-Methode UpdateLinks=0.
Der Unterschied besteht darin, dass AskToUpdateLinks-Eigenschaft die Aktualisierung ohne Abfrage durchführt und der Parameter UpdateLinks=0 nicht durchführt.
Sub Verknüpfungsmeldung_unterdrücken_Application() Application.AstToUpdateLinks=False
Workbooks.Open „C:\Excel\Beispiel_Verknüpfung.xls“
End Sub
Falls eine Verknüpfung ins Leere geht oder eine fehlerhafte Verknüpfung gibt, erscheint trotz obengenanntem Vorgang doch eine Abfrage am Monitor.
Abbildung 8: Meldung bei z.B. fehlerhafter Verknüpfung
Unser zweiter Weg ist ohne Aktualisierung der Verknüpfungen. Dazu ändern wir den UpdateLinks-Parameter auf null in der Open-Methode:
Sub Venknüpfungsmeldung_unterdrücken_Open()
Workbooks.Open „C:\Excel\Beispiel_Verknüpfung.xls“, UpdateLinks := 0 End Sub
1.4 UMGANG MIT ZELLEN UND BEREICHEN „RANGE“ / „CELLS“
Eine Zelle und ein Zellenbereich in VBA-Programmierung erfasst am größten Teil der Programmierung gegenüber Arbeitsmappen „Workbooks“ oder Tabellenblätter „Worksheets“.
Denn, wenn eine Zelle oder ein Bereich markiert ist, gibt es viele Möglichkeit sie zu formatieren. Das Markieren ist ein Selektion beziehungsweise das Ansprechen einer Zelle oder eines Bereiches.
Der Zugriff auf Zellen oder Zellbereiche ist nicht trivial, weil VBA mit zahlreichen, inhaltlich ähnlichen Begriffen und Objekten arbeitet. Wie man so schön sagt: „Viele Wege führen nach Rom.“ Viele Beispiele, die wir in diesem Buch gesehen und sehen werden, sind auch unterschiedlichen Wegen realisierbar.
Eine Zelle / ein Bereich wird selektiert zum:
• Bearbeiten
• Kopieren
• Ausschneiden
• Einfügen
• Umbenennen
• Formatieren nach
○ Ausrichtung
○ Schriftgestaltung
○ Rahmengestaltung
○ Farbfüllung
○ Schutz
○ Ein- und Ausblenden der Spalten / Zeile
○ Automatische Spaltenbreite und Zeilenhöhe
• Sortieren
• Auf null setzen
• Formel einfügen
• Druckbereich setzen
Die Zellen werden mit folgenden Möglichkeiten angesprochen:
• mit dem Range-Objekt
• mit dem Cells-Objekt
• ein Mix aus dem Beiden.
Das Range-Objekt wird mit der üblichen Zellenbezeichnung und das Cells-Objekt mit der Adressierung der Zellen- und Spaltennummern, angesprochen.
Beim Range-Objekt wird eine Zelle innerhalb zwei Klammern und Hochkommas geschrieben.
In der Reihenfolge werden erst die Spalte dann die Zeile geschrieben.
Beispielsweise („A1“) => Die Buchstabe „A“ gibt die Spalte an. Der Ziffer „1“ gibt die Zeile an.
Beim Cells-Objekt wird eine Zelle innerhalb offenem und geschlossenem Klammern geschrieben und mit einem Komma getrennt. In der Reihenfolge werden erst die Zeile dann die Spalte geschrieben.
Beispielsweise (3,4) => Die erste Ziffer (3) gibt die Zeile und die zweite Ziffer (4) gibt die Spalte-D an. Diese Schreibweise eignet sich besonders mit den Variablen zu arbeiten.
Range Cells
(“A1”) (1,1)
(“D3”) (3,4)
(“Z13”) (13,26)
Tabelle 3: Range / Cells
Bevor wir mit den Zellen und Bereichen anfangen, schauen wir kurz wie der Cursor in die Zelle positionieren werden kann.
Die Positionierung wird entweder mit den Methoden „Select“ oder „Activate“ gemacht.
Select-Methode markiert eine Zelle oder einen Zellenbereich.
Activate-Methode aktiviert eine einzelne Zelle, die sich innerhalb der aktuellen Markierung befindet.
Im folgendem Beispiel in der Tabelle „VBA“ ist der Bereich mit Range(„A1:C3“) markiert und mit Cells(1,1) die Zelle „A1“ aktiviert. Also der Cursor befindet sich in der Zelle „A1“.
Sub BspActivate_Select() Worksheets(„VBA“).Activate Range(“A1:C3”).Select Cells(1,1).Activate End Sub
Die folgenden Beispiele stellt die Schriftart in der Zelle A1 der Tabelle „VBA“ die blaue Farbe ein.
Nun Beispiele mit und ohne Select:
Sub MitActivate_Select() Worksheets(„VBA“).Activate Range(“A1”).Select
Selection.Font.Color = vbBlue End Sub
Jetzt etwas schneller in der Ausführung und weniger Tipparbeit:
Sub OhneActivate_Select()
Worksheets(“VBA”).Range(“A1”).Font.Color = vbBlue End Sub
Wenn wir in die Zelle etwas eintragen möchten, dann können wir OHNE Select bzw.
Activate-Methode arbeiten. Dadurch wird unsere Prozedur um einiges kürzer und schneller.
Hierzu ein Beispiel:
Sub Eintrag() Text = „Harun“
‚
Range(„C1“).Activate Range(„C1“).Value = Text ActiveCell.Value = Text Cells(4, 3).Activate Cells(4, 3).Value = Text ActiveCell.Value = Text
‚
Range(„B1“).Select ActiveCell.Value = Text Cells(3, 3).Select ActiveCell.Value = Text
‚
Range(„A1“) = Text Cells(2, 3) = Text End Sub
1.4.1 KOMBINATIONEN VON CELLS UND RANGE
Es ist aber auch beide Schreibvarianten miteinander zu kombinieren.
Die Kombination mit den Cells und Range wird meistens in den Schleifen verwendet. Denn die Zellbereiche mittels berechneter Indizes besser adressierbar sind.
Das untere Beispiel füllt folgende Zellen mit errechneten Werten:
Anstelle jede Zeile zu erklären, bitte eintippen und mit „F8“ step-to-step laufenlassen. Sub Kombination_Cells_Range()
Dim i As Integer For i = 2 To 4
Worksheets(“Tabelle1”).Range(Cells(2, 2), Cells(i + 2, i)).Value = 20 * i Next
End Sub
Oder wir markieren einen bestimmten Bereich:
Sub Bereich_markieren()
Worksheets(“Tabelle1”).Range(Cells(1, 2), Cells(1, 4)).Select ‚markiert B1:D1 End Sub
Oder mit Variablen:
Sub Kombination_Range_Cells() Dim Zeile, Spalte As Integer
Worksheets(„Tabelle1“).Activate Zeile=InputBox(„Anzahl Zeilen“) Spalte=InputBox(„Anzahl Spalte“)
Range(Cells(3, 3), Cells(2+Zeilen, 2+Spalten)).Activate End Sub
1.4.2 LETZTE BESCHRIEBENE ZELLE ERMITTELN
Die letzte beschriebene Zelle in der Tabelle mit folgenden Eigenschaften ermittelt:
Rows.Count letzte beschriebene Zelle in der Spalte und Columns.Count letzte beschriebene Zelle in der Zeile.
Abbildung 9: Letzte Zeile
Sub Letzte_beschriebene_Zelle_Spalte() ‚von oben nach unten gezählt Dim strLetzteZeile As String
strLetzteZeile = Cells(Rows.Count, 1).End(xlUp).Row End Sub
ÖDas Ergebnis ist 4. “A4” ist zuletzt beschrieben!
Unser Beispiel dazu sieht wie folgt aus:
Sub Letzte_beschriebene_Zelle_Zeile() ‚ von links nach rechts gezählt Dim strLetzteSpalte As String
strLetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column End Sub
ÖDas Ergebnis ist 2. “B1” ist zuletzt beschrieben! Das Ergebnis hierzu ist keine Buchstabe, sondern ein Zahl!
1.4.3 IN DIE LETZTE ZU BESCHREIBENDE ZELLE IN DER SPALTE EINTRAGEN Das kommt in der Praxis auch oft vor. Unterste beschreibbare Zelle wird wie folgt eingetragen:
Sub Letzte_zu_beschreibende_Zelle_befüllen_2() Dim strLetzteZeile As String
strLetzteZeile = Cells(Rows.Count, 3).End(xlUp).Row
Range(„C“ & strLetzteZeile).Offset(1, 0) = „Ich werde eingetragen“
End Sub
Abbildung 10: Letzte Spalte
1.4.4 AKTIVE ZEILE / SPALTE / ADRESSE ERMITTELN
Nun, ermitteln wir die aktive Zeile, aktive Spalte und die aktive Position des Cursors.
ActiveCell.Row gibt uns die Zeile als String an ActiveCell.Column gibt uns die Spalte als String an
ActiveCell.Address gibt uns die Adresse in Range-Format mit $-Zeichen davor.
Sub Aktive_Infos()
Zeile = ActiveCell.Row Spalte = ActiveCell.Column Adress = ActiveCell.Address
Adress_2 = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) MsgBox „Der Cursor befindet sich in „ & vbLf & _
„Zeile „ & Zeile & vbLf & _
„Spalte „ & Spalte & vbLf & _
„Adresse_1 „ & Adress & vbLf & _
„Adresse_2 „ & Adress_2 End Sub
Abbildung 11: Zelleninformationen
Um das Ergebnis ohne $-Zeichen zu bekommen, setzen wir die Attributen RowAbsolte:=False bzw.
ColumnAbsolute:=False ein.
In unserem Beispiel oben sah so aus:
ÖAdress_2 = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) Genug mit Zellen. Jetzt schauen wir die Tabellen an.
1.4.5 AKTIVE TABELLE ERMITTELN
Folgende Eigenschaften mit der Anweisung „.Name“ ermitteln die Namen des aktiven Tabellenblattes:
• ActiveSheet.Name
• ActiveCell.Parent.Name Sub Aktiven_Tabellenname_ermitteln()
Dim strTabName_1, strTabName_2 As String strTabName_1 = ActiveSheet.Name ‚ Variante 1 strTabName_2 = ActiveCell.Parent.Name ‚ Variante 2 MsgBox strTabName_1 & vbLf & strTabName_2 End Sub
1.4.6 AKTIVE DATEI ERMITTELN
Jetzt kommen wir zur Ermittlung der aktiven Datei. Um der Name der aktiven Datei zu ermitteln, verwenden wir folgende Eigenschaft mit der „.Name“-Anweisung:
• ActiveWorkbook.Name
• ActiveSheet.Parent.Name Sub Aktive_Dateiname_ermitteln()
Dim strDateiName_1, strDateiName_2 As String strDateiName_1 = ActiveWorkbook.Name ‚ Variante 1 strDateiName_2 = ActiveSheet.Parent.Name ‚ Variante 2 MsgBox strDateiName_1 & vbLf & strDateiName_2 End Sub
1.4.7 AKTIVES FENSTER ERMITTELN
Wenn wir mehrere Excel-Dateien gleichzeitig geöffnet haben, diese Dateien sind dann praktisch „digital“ gestapelt. Wir können mit der „ActiveWindow“-Eigenschaft ermitteln, welches Fenster beziehungsweise welche Excel-Datei ganz oben ist. Das erreichen wir mit der „ActiveWindow“-Eigenschaft angehängt die „.Caption“-Eigenschaft.
Sub Aktives_Fenster_ermitteln() Dim strFenster As String
strFenster = ActiveWindow.Caption MsgBox strFenster
End Sub
Hier haben wir einige Befehle auf einem Haufen verwendet. Diese haben Funktionen wie folgt:
Befehl Aktivität
ActiveCell.Address Ermittelt die Adresse der aktiven Zelle ActiveCell.Row Ermittelt den Zeilennummer der aktiven Zelle ActiveCell.Column Ermittelt den Spaltennummer der aktiven Zelle ActiveCell.Parent.Name Ermittelt den Namen der Tabelle mit der aktiven Zelle ActiveSheet.Parent.Name Ermittelt den Namen der Arbeitsmappe
ActiveSheet.Name Gibt die Name des aktiven Tabellenblattes
ActiveWindow Gibt ein Window-Objekt zurück, das für das aktive Fenster steht ActiveWorkbook.Name Gibt die Name der aktiven Arbeitsmappe
Tabelle 4: Wichtige Active-Eigenschaften
1.4.8 SUCHEN UND ERSETZEN DER ZELLENINHALTEN
Das Suchen und Ersetzen nach bestimmten Inhalten in der Tabelle werden mit den folgenden Methoden realisiert:
• .Find Duchsucht den Bereich nach bestimmten Begriff.
• .Replace Duchsucht den Bereich und ersetzt mit anderem Begriff.
Als erstes schauen wir nach dem „Range.Find“ „Suchen“!
Wir lassen in bestimmten Bereichen nach einem Wert, hier „Harun“ suchen und davon die Adresse ausgeben.
Abbildung 12: Unser Suchbegriff
Die Listing dazu:
Sub Suchen_Adresse_ausgeben() Dim strAdresse As String
strAdresse = Range(“A1:C5”).Find(“Harun”).Address MsgBox strAdresse
End Sub
Und das Ergebnis:
Abbildung 13: Suchergebnis
Jetzt lassen wir mehrere gleiche Begriffe suchen und die Adresse dazu ausgeben:
Abbildung 14: Unsere Suchbegriffe
Die Listing dazu:
Sub Suchen_Adresse_ausgeben_2() Dim rgBereich As Range
Dim strAdresse, strBereiche As String Set rgBereich = Cells.Find(“Auto”) If Not rgBereich Is Nothing Then strAdresse = rgBereich.Address Do
Set rgBereich = Cells.FindNext(rgBereich)
strBereiche = rgBereich.Address & vbLf & strBereiche Loop Until (rgBereich.Address = strAdresse)
MsgBox strBereiche End If
End Sub
Abbildung 15: Suchergebnis
Nun lassen wir nach den Werten suchen, die dann dessen Zellen mit blauer Farbe befüllen.
Unsere Tabelle dazu sieht wie folgt aus:
Abbildung 16: Werte Suchmaske
Gesucht werden alle Zellen im bestimmten Bereich mit For Each nach nummerischen Werten die größer 20 sind. Beim Finden wird die jeweilige Zelle mit blauer Farbe befüllt.
Das Suchen nach Zellinhalten, die in mehreren Zellen auftreten können, ist am einfachsten über For Each-Schleife am einfachsten. Dazu setzen wir die „>“ Größer- / „<“ Kleiner- oder
„=“ das Gleichheitszeichen-Operator ein.
Sub Suchen_mit_Farbe_befuellen() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If IsNumeric(rngZelle.Value) Then
If rngZelle.Value > 20 Then rngZelle.Interior.ColorIndex = 5 End If
End If Next End Sub
Abbildung 17: Farbbefüllte Zellen
1.4.9 ERSETZEN DER ZELLENINHALTEN
Aber jetzt sollten wir ein paar Beispiele mit dem Ersetzen machen. Das folgende Beispiel soll unsere Zellen, die „aut“ beinhalten mit dem „Mercedes Benz“ ersetzen. Auch hier setzen wir For Each-Schleife ein. Zusätzlich den „Like“-Operator ein, um Inhalte zu vergleichen.
Für die Suche nach Zelleninhalten benutzen wir die „Value“-Eigenschaft. Die Inhalte der Zellen können unterschiedlich sein. Also, sie können aus numerisch, alphanumerisch oder aus Formeln bestehen.
Sub Suchen_Ersetzen_1() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If LCase(rngZelle.Value) Like “aut*” Then
rngZelle.Value = “Mercedes Benz”
End If Next End Sub
Oder auch mit Range.Replace-Methode:
Sub Suchen_Ersetzen_1()
Worksheets(“Tabelle1”).Range(“A1:C10”).Replace _ What:=”*aut*”, Replacement:=”Mercedes Benz”, _ SearchOrder:=xlByColumns, MatchCase:=True End Sub
Abbildung 18: Ergebnis Suchen_Ersetzen_1
Nun, alle Zellen, die *ben* beinhalten, sollen mit dem Doppelrahmen, mit der Farbe „27“
befüllt werden. Auch hier setzen wir For Each-Schleife ein. Zusätzlich den „Like“-Operator ein, um Inhalte zu vergleichen.
Sub Suche_Ersetzen_2() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If LCase(rngZelle.Value) Like “*ben*” Then
rngZelle.Borders.LineStyle = xlDouble End If
Next End Sub
Abbildung 19: Ergebnis Suchen_Ersetzen_2
Alle Zellen, die mit blauer Farbe befüllt sind, werden mit punktiertem Rahmen, mit gelber Farbe und als Wert „Daimler“ ersetzt.
Sub Suche_Ersetzen_3() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If Zelle.Interior.ColorIndex = 5 Then
With Zelle
.Borders.LineStyle = xlDot .Interior.ColorIndex = 27 .Value = “Daimler”
End With End If Next End Sub
Abbildung 20: Ergebnis Suchen_Ersetzen_3
1.4.10 LÖSCHEN VON ZELLINHALTEN UND ZEILEN
Zellen können Texte, Zahlen, Formeln oder auch Kommentare beinhalten. Wir können sie mit einer Rahmenart, mit bestimmter Füllfarbe oder auch mit einem Muster formatieren.
Alles, was zu einer Zelle eingegeben wurden, können einzeln oder auf einem Mal mit deren Formatierungen gelöscht werden.
Dazu sind folgende Methoden zuständig:
Löschfunktion Bedeutung
Clear Alle Einträge und Formatierungen eines Bereiches löschen ClearContents Nur Einträge eines Bereiches löschen
ClearComments Nur Kommentare eines Bereiches löschen ClearFormats Nur Format eines Bereiches löschen
Delete Zellen löschen
Tabelle 5: Löschmethoden
Sub Formatierung_löschen()
Worksheets(“Tabelle1”).Range(“A1”).Clear
Worksheets(“Tabelle1”).Range(“A2:C2”).ClearContents Worksheets(“Tabelle1”).Range(“A3”).ClearFormats
Worksheets(“Tabelle1”).Range(Cells(4, 1), Cells(4, 2)).ClearComments Worksheets(“Tabelle1”).Range(“A5”).Delete
ActiveCell.EntireRow.Clear ‘Spalte löschen
ActiveCell.EntireColumn.ClearContents ‘Zeile Löschen End Sub
Wenn wir gesamten Einträge der Tabelle löschen möchten, dann wie folgt:
Sub Zellinhalte_des_gesamten_Tabellenblattes_löschen()
Cells.ClearContents ‘ClearContents löscht die Inhalte und Formeln aus einem Bereich End Sub
Nun, Löschung mit allem, wie folgt:
Sub Gesamtes_Tabellenblatt_löschen() Cells.Delete
End Sub
1.5 ZELLEN BEFÜLLEN
Das Befüllen der Zellen oder Bereichen werden per VBA normalerweise mit der .Value- Eigenschaft gemacht. Das kann auch ohne.
¾ Diese Einträge sind in die jeweilige Zelle der aktiven Tabelle:
Range(“A20”).Value = “12345”
Range(“A20”) = “12345”
ActiveCell=”Harun Kaplan”
ActiveCell.Value=”Harun Kaplan”
¾ Dieser Eintrag ist explizit in die Zelle (A21) der Tabelle “VBA” eingetragen:
Worksheets(„VBA“).Cells(21, 1).Value = „12345“
¾ Dieser Eintrag ist in die umbenannte Zelle einer Tabelle eingetragen. Solche Beispiele sehen wir etwas später:
Range(„Umbenannt“) = „Auto“
Range(„Umbenannt“).Value = „Auto“
¾ Summe aus den Zellen der unterschiedlichen Tabellen:
WorksheetFunction.Sum(Worksheets(“Daten”).Range(“A12:A14”), _ Worksheets(“VBA”).Range(“A15”))
Sub Zellen_Befüllen() Dim Name, Obst As String Dim Wert1, Summe As Integer
‚Zuweisungen in Zellen schreiben ActiveCell = “Harun Kaplan”
Range(“A20”) = “12345”
Worksheets(„VBA“).Cells(21, 1).Value = „12345“
Range(„Umbenannt“) = „Auto“ ‚“B13“ in Tabelle „Auswertung“
‘Zellinhalte als Variable definieren Name = Range(“A1”)
Wert1 = Worksheets(“VBA”).Cells(21, 1).Value = “12345”
Obst = Cells(2, 1).Value
Summe = WorksheetFunction.Sum(Worksheets(“Daten”).Range(“A12:A14”), _ Worksheets(“VBA”).Range(“A15”))
MsgBox (“Name : “ & Name & Chr(10) & _
„Wert1 : „ & Wert1 & Chr(10) & _
„Obst : „ & Obst & Chr(10) & _
„Summe : „ & Summe) End Sub
Beim nächsten Beispiel werden die Zellen A10 bis A1 von 10 bis 1 rückwärts befüllt.
Sub Werte_einfügen_schleife() Dim i As Integer
For i = 10 To 1 Step -1
Range(Cells(1, i), Cells(1, i)).Value = i Next i
End Sub
1.5.1 FUNKTION / FORMEL EINFÜGEN
Als erstes tragen wir ein Ergebnis aus einer Berechnung, hier Summe von zwei Werten, in die bestimmte Zelle. Im ersten Beispiel wird jeder Wert zu einer Variablen zugeordnet.
Zweites Beispiel ohne Variablen, also direkt.
Sub Berechnung_VBA_Code_Variable()
Dim intZahl1, intZahl2, intErgebnis As Integer intZahl1= Range(“B1”).Value ‘erster Wert als Variable intZahl2= Range(“C1”).Value ‘ zweiter Wert als Variable
intErgebnis = intZahl1 + intZahl2 ‘Das Ergebnis von erstem und zweitem Wert als Variable Range(„A1“).Value = intErgebnis ‘Eintrag in die Zelle A1
End Sub
Hier ohne Variablen:
Sub Berechnung_VBA_Code_direkt()
Range(„A1“).Value = Range(„B1“).Value + Range(„C1“).Value End Sub
Es kann aber doch vorkommen, dass wir eine Formel direkt in die Zelle schreiben müssen.
Diese können wir mit der Formula-Eigenschaft im A1-Bezugssystem für die Tabellen- Makrosprache eintragen.
Am unteren Beispiel wird in die Zelle „A1“ der Tabelle „Tabelle1“ eine Formel eingefügt.
Der wiederum die Zellen „B1“ und „C1“ zusammenzählt.
Sub Formel_einfügen_1()
Worksheets(“Tabelle1”).Range(“A1”).Formula = “=$B$1+$C$1”
End Sub
Abbildung 21: Eingefügter Formel
Nächstes Beispiel fügt in die Zelle „B2“ an allen Tabellenblättern den Formel „=A1*0,5“
Sub Formel_einfügen_2() Dim intAnzahl As Integer
For intAnzahl = 1 To Worksheets.Count
Worksheets(intAnzahl ).Cells(2, 2).Formula = “=A1*0.5”
Next End Sub
INNOVATIVES COACHING FÜR SIE
Mit dem liveonline-Angebot unterstützt die AOK Sie im Schulalltag und bei Ihrer Karriereplanung durch erstklassige Vorträge und Kurse.
aok-on.de/liveonline
Das folgende Beispiel fügt eine Excel-Funktion in die Tabelle1 der aktuellen Datei in die Zelle „B16“ den Formel „=SVERWEIS(A16;‘[Beispieldatei.xlsx]Tabelle1“.
Sub Formel_einfügen_3()
ThisWorkbook.Worksheets(“Tabelle1”).Range(“b16”).FormulaLocal = _
„=SVERWEIS(A16;‘[Beispieldatei.xlsx]Tabelle1‘“
End Sub
1.5.2 KOPIEREN / AUSSCHNEIDEN / EINFÜGEN DER ZELLEN ODER BEREICHEN VBA-Syntax vom Kopieren und Ausschneiden von Zellen oder Bereichen fast gleich geschrieben. Zum Ausschneiden wird „.Cut“ und zum Kopieren „.Copy“ verwenden. Rest bleibt gleich.
In die Tabelle „Daten“ sind die Bereiche A1:B3 befüllt.
Abbildung 22: Beispiel Copy
Dieses Beispiel soll den Bereich „A1:B3“ kopieren und ab „D1“ einfügen:
Sub Bereiche_Copy_Paste_1()
Range(“A1:B3”).Copy Range(“D1”) End Sub
Gleiches Beispiel mit „.Cut“. Der Bereich wird ausgeschnitten und und ab „D1“ einfügen:
Sub Bereiche_Cut_Paste_1()
Range(“A1:B3”).Cut Range(“D1”) End Sub
Gleichen Bereich fügen wir in die Tabelle „Auswertung“ ab Zelle A1 ein:
Sub Bereiche_Copy_Paste_2()
Range(“A1:B3”).Copy Worksheets(“Auswertung”).Range(“A1”) End Sub
Der Bereich („A1:B3“) aus der Tabelle „Daten“ fügen wir in die Tabelle „Auswertung“ ab Zelle A1 ein:
Sub Bereiche_Copy_Paste_3()
Worksheets(“Daten”).Range(“A1:B3”).Copy _ Worksheets(“Auswertung”).Range(“A1”) End Sub
Der Bereich („A1:B3“) aus der Tabelle „Daten“ der Datei „Daten.xlxs“ fügen wir in die Tabelle „Auswertung“ der Datei „Auswertung.xlsx“ ab Zelle A1 ein:
Sub Bereiche_Copy_Paste_4()
Workbooks(“Daten.xlsx”).Worksheets(“Daten”).Range(“A1:B3”).Copy _ Workbooks(“Auswertung.xlsx”).Worksheets(“Auswertung”).Range(“A1”) End Sub
1.5.3 BEREICHE MARKIEREN MIT STRG+SHIFT+*
In Excel bzw. in VBA gibt es zwei Wege einen Bereich zu markieren:
Erster Weg:
Excel: Den Cursor am oberen Kante positionieren, dann ziehen wir zur diagonal gegenüber liegende Kante.
VBA: Range(“A11:D14”).Select Zweiter Weg:
Excel: Den Cursor irgendwo in jeweiligem Bereich positionieren, dann mit der Tastenkombination Strg+Shift+* markieren. Dieser elegante Weg kann auch per VBA wie folgt realisiert werden.
VBA: Selection.CurrentRegion.Copy Sub Bereiche_Copy_Paste_5()
Range(“A1”).Select
Selection.CurrentRegion.Copy Range(“D1”) End Sub
Bei so einem Feld, wie unten dargestellt, genügt die Maus irgendwo im Feld „A11:D14“ zu positionieren. Alle Felder, die mit der Kante oder mit den Ecken berühren, werden markiert.
Abbildung 23: Markierung mit „Strg+Shift+*“ in VBA -Code
Sub Bereiche_Copy_Paste_6() Range(“C1”).Select
Selection.CurrentRegion.Copy Worksheets(“Auswertung”).Range(“A11”) End Sub
1.5.4 BEREICHE MARKIEREN DURCH REFERIERUNG
Die Bereiche können auch mit der „Set“-Anweisung referiert werden:
Sub Bereiche_Copy_Paste_6() Dim rngQuelle As Range Dim rngZiel As Range
Set rngQuelle = Workbooks(“Daten.xls”).Worksheets(“Daten”).Range(“A1:B3”).Copy Set rngZiel = Workbooks(“Auswertung.xls”).Worksheets(“Auswertung”).Range(“A1”)
rngQuelle.Copy rngZiel Set rngQuelle = Nothing Set rngZiel = Nothing End Sub
1.5.5 EINE ZELLE / EINEN BEREICH UMBENENNEN
Ich möchte Ihnen eine sehr nützliche Möglichkeit zeigen. Wir haben hier ein paar Regeln zu beachten:
ÖEr darf pro Datei NUR einmal geben.
ÖEr darf auch nicht für Makro, Steuerelement vergeben werden.
Sonst springt der Cursor direkt in die vorhandene Zelle bzw. an das Makro oder zu jenem Steuerelement hinzu.
Abbildung 24: Zellenname
In der oberen Abbildung befindet sich der Cursor in Zelle „C4“.
In Excel haben alle Spalten, Zeilen und Zellen eigene Namen. Die Zeilen sind mit den Ziffern zwischen 1 – 1.048.576 und die Spalten sind mit den Buchstaben von A – XFD, benannt. Die Namen der Zellen einer Tabelle sind von A1 bis XFD1048576 bezeichnet.
Deshalb bei der Umbenennung werden alte und neue Namen angegeben. Wenn wir neuer Name vergeben haben, erscheint dann der neue Zellenname. Dieser neue Name kann dann später mit Range(“Neuer_Name“) angesprochen werden.
Script dazu sieht wie folgt aus:
ActiveWorkbook.Names.Add Name:=“Neuer_Name“ , RefersTo:=“zu der Zelle bzw. Bereich“
Sub Zelle_umbenennen_1() ActiveWorkbook.Names.Add _ Name:=”Obst”, _
RefersTo:=Worksheets(“Tabelle1”).Range(“A1”) Range(„Obst“).Select
End Sub
Abbildung 25: Umbenannte Zelle
Nun zu meinem ersten praxisnahem Beispiel:
Es kommt sehr oft vor, dass ein Teil aus irgendeine Quelle, kann auch Datenbank sein, in eine Zieldatei eingelesen werden müssen. So etwas könnte wie unten dargestellt aussehen.
Die Daten aus der aktiven Zeile aus der Datei „Quelle.xlsx“ soll in die „Ziel.xlsm“-Datei übertragen werden.
Zu unserem Beispiel lassen wir die Daten aus den Spalten „Name“; „Vorname“ und „Alter“
übertragen und wird dann weiter erweitert mit den Spalten „Beruf“ und „Einsatzbereich“.
In unserem Beispiel unten nach dem Ausführen der Prozedur o.g. Daten in richtiger Reihenfolge übertragen. Es funktioniert so lange bis wir keine neuen Spalten zwischen „A“
und „B“ oder zwischen „B“ und „C“ eingefügt haben. Sonst werden sie in faschen Spalten eingetragen.
Nun, in der Datei „Ziel.xlsx“ die Zellen sind wie folgt von Hand geändert:
„A1“ in „Ziel_Name“;
„B1“ in „Ziel_Vorname“ und
„C1“ in „Ziel_Alter“.
Wenn wir später nach Bedarf neuen Spalten einfügen, werden diese Informationen in die richtige Spalte eingetragen.
Wie stellen wir die richtige Spalte fest?
Mit der Range(„Ziel_Name“).Column wird die Spalte der Zelle von „Ziel_Name“ ermittelt.
Wir schrieben wir die Cells-Anweisung? Cells(Zeile, Spalte).
Uns fehlt noch die richtige Zeile:
letzteZeile = Cells(Rows.Count, 1).End(xlUp).Row + 1 Nun, setzten wir sie wie folgt zusammen:
Cells(letzteZeile, Range(„Ziel_Name“).Column).Value
Die Übertragung funktioniert jetzt für immer mit folgender VBA-Syntax.
Cells(letzteZeile, Range(„Ziel_Name“).Column).Value = Ziel_Name
