An einem Beispiel möchte ich zeigen, wie man ein aufgezeichnetes Makro so bearbeiten kann, daß es unabhägig von der Tabellengröße eingesetzt werden kann.
Ich habe ein Makro aufgezeichnet, das die Beispieltabelle so formatiert, wie das Beispiel oben zeigt. Leider kann das Makro, so wie es aufgezeichnet ist, nur Tabellen mit exakt dieser Größe formatieren. Das kann natürlich nicht zufriedenstellen. Um zu ergründen was gemacht werden kann, sehen wir uns zunächst den Quellkode des aufgezeichneten Makros an:
Option Explicit
Sub Tabelle_formatieren()
'
' Tabelle_formatieren Makro
' Makro am 22.07.01 von Klaus-Dieter Oppermann aufgezeichnet
'
'
Range("A1:F1").Select ' In Zeile 1 Spalten A bis F selektieren
Selection.Font.Bold = True ' Schrift auf Fett umschalten
Columns("A:F").Select ' Spalten A bis F selektieren
Columns("A:F").EntireColumn.AutoFit ' Spalten A bis F auf optimale Breite stellen
Range("A1:F1").Select ' In Zeile 1 Spalten A bis F selektieren
With Selection.Interior ' Im selektierten Bereich Zellhintergrund
.ColorIndex = 15 ' auf graue Farbe schalten
.Pattern = xlSolid '
End With '
Range("A3:F3").Select ' In Zeile 3 Spalten A bis F selektieren
With Selection.Interior ' Im selektierten Bereich Zellhintergrund
.ColorIndex = 35 ' auf grüne Farbe schalten
.Pattern = xlSolid '
End With '
Range("A5:F5").Select ' In Zeile 3 Spalten A bis F selektieren
With Selection.Interior ' Im selektierten Bereich Zellhintergrund
.ColorIndex = 35 ' auf grüne Farbe schalten
.Pattern = xlSolid '
End With '
Range("A1").Select ' Zelle A1 selektieren
End Sub |
Die einzelnen Zeilen des Quellkodes habe ich komentiert, damit man nachvollziehen kann, was dort eigentlich gesteuert wird. Nun ist es natürlich möglich, die Zellbezüge im Quelltext zu verändern, um das Makro an größere Tabellen anzupassen. Für eine andere Tabellebreite geht das noch verhältnismäßig einfach: man muß nur in den entsprechenden Zeilen den den Buchstaben F durch den richtigen Buchstaben für die neue Breite ersetzen.
Schwieriger wird das schon bei der Tabellenlänge: dort müßte der Bereich von Range("A5:F5").Select bis End With kopiert werden und für jede grün zu färbende Zeile eingefügt werden. Dabei darf nicht vergessen werden, die Zellbezüge jeweils um den Faktor zwei zu erhöhen. Angenommen es liegt eine Tabelle mit mehreren hundert Zeilen vor, kann man leicht ermessen, was das bedeutet!
Option Explicit
Sub Tabelle_formatieren_komfort()
Dim z As Integer
Dim sp As Integer
Dim s As Integer
' Tabelle_formatieren Makro
' aufgezeichnetes Makro von Klaus-Dieter Oppermann
' für variable Tabellengrößen umgeschrieben
' # Tabellengröße ermitteln #
sp = Range("IV1").End(xlToLeft).Column + 1
' Tabellenlänge
z = Range("A65536").End(xlUp).Row + 1
' # Tabelle formatieren #
With Range("A1", Chr(63 + sp) & 1) ' erste Zeile definieren
.Font.Bold = True ' Schrift auf Fett umschalten
.EntireColumn.AutoFit ' Spalten A bis ? auf optimale Breite stellen
.Interior.ColorIndex = 15 ' grau hinterlegen
.Interior.Pattern = xlSolid ' Hintergrund vollflächig färben
End With ' Ende der Anweisung
' Schleife zum Einfärben der grünen Zellen
For s = 3 To z - 1 Step 2 ' For To Next Schleife - Beginn
With Range("A" & s, Chr(63 + sp) & s) ' Bereich zum Einfärben definieren
.Interior.ColorIndex = 35 ' Bereich grün färben
.Interior.Pattern = xlSolid ' Hintergrund vollflächig färben '
End With ' Ende der Anweisung
Next s ' For To Next Schleife - Wendepunkt
End Sub
|
So wie das Makro jetzt gestaltet ist, funtioniert es unabhängig von der Tabellengröße. Speziell im Bereich der grün gefärbten Zellen ist der Vorteil dieser Vorgehensweise deutlich zu erkennen. Es reicht eine einzige For To Next Schleife, um eine beliebige Anzahl von Zeilen einzufärben.
Weiterhin habe ich überflüssige Befehle entfernt.
Noch einige Bemerkungen zu den Zielwerten für die Tabellenbreite: was besagt die Formel Chr(63 + sp) & 1)? Im Gegensatz zum 'Cells-Befehl', in dem die Zellpositionen über Zahlen definiert werden, muß man im Range-Befehl Zellbezüge nach dem Muster 'A1' eintragen. Da unsere kleine Routine zum Ermitteln der Tabellenbreite als Ergebnis aber eine Zahl liefert, muß ein Weg gefunden werden, diesen Wert in einer lesbaren Form zu übergeben.
Es bietet sich das Vorgehen über einen "Chr-String" an. In grauer Computervorzeit wurden alle möglichen druckbaren und nicht druckbaren Zeichen mit einer Kodeziffer versehen. So hat das große F zum Beispiel den Wert 70. Da unsere Do While Loop-Schleife als Ergebnis immer eine Zahl liefert, die um 1 höher ist, als die tatsächliche Länge oder Breite unserer Tabelle, würde bei einer sechs Spalten breiten Tabelle als Ergebnis in der Variable sp der Wert von 7 stehen. 63 + 7 ergibt 70, das ist genau der Wert für den Buchstaben F. Über das "Kaufmanns-und" (&) wird eine 1 hinzugefügt, ergibt als Text den Wert F1. Mit dieser Formel lassen sich Tabellen bis zur Spalte Z bearbeiten. Bei breiteren Tabellen muß die Formel erweitert werden.
Noch ein wichtiger Hinweis: normalerweise hat der Range-Befehl die Syntax Range("A1":"F1"), das heißt, die Zellbezüge sind duch Doppelpunkt getrennt. Wird in den Befehl aber eine Variable oder eine Formel eingebaut, müssen die Zellbezüge durch ein Komma getrennt werden. Sicher weiß "Billy", warum das so ist. ;-)
Ich habe eine Beipieltabelle mit den Quellkodes in den Downloadbereich gestellt.