Die globale Cells-Eigenschaft in VBA 

Die Excel -Bibliothek

Damit auf Excel  zugegriffen werden kann, muß die Excel -Bibliothek in VBA  eingebunden sein. Dies ist normalweise der Fall, wenn der VBE  von Excel  aus geöffnet wird. Ansonsten kann die Excel -Bibliothek auch über den Eintrag „Verweise“ im Extras-Menü des VBE  eingebunden werden („Microsoft Excel … Object Library“).

Das globale Objekt

Die Cells-Eigenschaft ist eine Eigenschaft eines globalen Verzeichnisses  der Excel -Bibliothek. Der Name einer Eigenschaft eines globalen Verzeichnisses kann direkt als Ausdruck verwendet werden, ohne daß sein Objekt oder Modul (mit einem Punkt ».« getrennt) davorgeschrieben werden muß. Das globale Verzeichnis erscheint als Klasse „<Global>“ im Objektkatalog. Um die Anzeige übersichtlich auf die Excel -Bibliothek einzuschränken, kann diese links oben im Objektkatalog angewählt werden („Excel“ statt „<Alle Bibliotheken>“).

Objektkatalogeintrag zu »Excel.Cells« in „<Global>“

Property Cells As Range
    schreibgeschützt
    Element von Excel.Global

Die Cells-Eigenschaft

Die Cells-Eigenschaft hat den Typ »Range«.

Direktbereich

? Typename( Excel.Application.Cells )

Range

Direktbereich

? Typename( Cells )

Range

Der Typ »Range« ist der wichtigste Typ in Excel. Er repräsentiert eine Gruppe von Zellen.

Microsoft -Dokumentation für die Cells-Eigenschaft

docs.microsoft.com/en-us/office/vba/api/excel.worksheet.cells

Microsoft -Dokumentation für den Typ »Range«

docs.microsoft.com/en-us/office/vba/api/excel.range%28object%29

Adressen

Range-Objekte haben eine Excel-Adresse, die beschreibt, welche Zellen zu dem Bereich (englisch: “range ”) gehören.

Durch Ausgabe der Excel-Adresse können wir erkennen, daß der Fehlwert der Cells-Eigenschaft einfach die gesamte Tabelle  umfaßt.

Direktbereich

? Cells.Address

$1:$65536

Bei der oben verwendeten Textdarstellung eines Zellbereichs steht »$1« für die erste Zeile  des aktuellen Tabellenblattes und »$65536« für die letzte  (der Zahlenwert kann bei neueren Excel -Versionen auch größer sein als 65536, beispielsweise 1048576). Der Zellbereich »$1:$65536« umfaßt also alle  Zellen der Tabelle.

Tabellenblatt

                 Spalte    Spalte
           .---.---------.---------.-
           |   |    A    |    B    |
           '---'---------'---------'-
 Zeile $1  | 1 |        1|        2|
 Zeile $2  | 2 |        3|        4|

Die Verwendung von Range-Objekten

Ein Range-Objekt ergibt eine einfache Zelle, nachdem ihm noch Zeile und Spalte  in runden Klammern nachgestellt werden.

Tabellenblatt

 | --> 2.)  Spalte    Spalte
 V    .---.---------.---------.-
      |   |    1    |    2    |
 1.)  '---'---------'---------'-
Zeile | 1 |        1|        2|
Zeile | 2 |        3|        4|

Direktbereich

? Cells( 1, 2 )

 2 

Die Zahl »2« wird hier aber nur dann ausgegeben, wenn man diesen Wert zuvor in die Zelle B1 geschrieben hat! Dies kann bei Anzeige der Tabelle von Excel  aus (ohne VBA ) geschehen.

Direktbereich

? Cells( RowIndex := 1, ColumnIndex := 2 )

 2 

Wir werden erst etwas später sehen, wo man die Parameternamen (wie »RowIndex«) nachlesen kann.

Fachenglisch

row  [roʊ] Zeile

column  [ˈkɑləm] Spalte

index  [ˈɪnˌdɛks] (hier) Kennzahl, Versatz

Die Cells-Eigenschaft gehört zum Objektmodell  von Excel -VBA. Das heißt: Das Programm Excel, samt aller Werte in den Zellen der Tabellenblätter, wird in VBA  durch Objekte  „modelliert“ (dargestellt, nachgebildet).

Da diese Eigenschaft – zumindest der Schreibweise nach – Argumente  akzeptiert, ähnelt sie stark einer Methode  (einer Funktion ). Wie der Objektkatalog anzeigt, ist »Cells« aber eine Eigenschaft.

Die Zuweisung an Zellen

Zellen können wie Variablen des Typs »Variant« verwendet werden und damit auch Zuweisungen  akzeptieren.

Zuweisung einer Zahl an eine Zelle

Let Cells( 3, 4 ) = 17

(»Let « kann immer auch weggelassen  werden.)

Hierbei erfolgt tatsächlich eine Zuweisung an die Fehleigenschaft  »Value«.

Zuweisung einer Zahl an eine Zelle

Let Cells( 3, 4 ).Value = 17

Der neue Wert der Zelle erscheint dann in der von Excel  angezeigten Tabelle.

Tabellenblatt

         1         2         3         4         5
.---.---------.---------.---------.---------.---------.-
|   |    A    |    B    |    C    |    D    |    E    |
'---'---------'---------'---------'---------'---------'-
| 1 |        1|        2|         |         |         |
| 2 |        3|        4|         |         |         |
| 3 |         |         |         |       17|         |
| 4 |         |         |         |         |         |

Zuweisung einer Zeichenfolge an eine Zelle

Let Cells( 4, 4 ) = "abc"

Tabellenblatt

         1         2         3         4         5
.---.---------.---------.---------.---------.---------.-
|   |    A    |    B    |    C    |    D    |    E    |
'---'---------'---------'---------'---------'---------'-
| 1 |        1|        2|         |         |         |
| 2 |        3|        4|         |         |         |
| 3 |         |         |         |       17|         |
| 4 |         |         |         |abc      |         |
| 5 |         |         |         |         |         |

Zuweisung eines Zeitstempels an eine Zelle

Let Cells( 5, 4 ) = #2056-03-29 17:03:46#

Tabellenblatt

         1         2         3         4         5
.---.---------.---------.---------.---------.---------.-
|   |    A    |    B    |    C    |    D    |    E    |
'---'---------'---------'---------'---------'---------'-
| 1 |        1|        2|         |         |         |
| 2 |        3|        4|         |         |         |
| 3 |         |         |         |       17|         |
| 4 |         |         |         |abc      |         |
| 5 |         |         |         |#########|         |
| 6 |         |         |         |         |         |

Tabellenblatt

         1         2         3             4            5
.---.---------.---------.---------.----------------.---------.-
|   |    A    |    B    |    C    |        D       |    E    |
'---'---------'---------'---------'----------------'---------'-
| 1 |        1|        2|         |                |         |
| 2 |        3|        4|         |                |         |
| 3 |         |         |         |              17|         |
| 4 |         |         |         |abc             |         |
| 5 |         |         |         |2056-03-29 17:03|         |
| 6 |         |         |         |                |         |

Zuweisung einer Formel an eine Zelle

Let Cells( 6, 4 ) = "=D3+1"

Tabellenblatt

         1         2         3             4            5
.---.---------.---------.---------.----------------.---------.-
|   |    A    |    B    |    C    |        D       |    E    |
'---'---------'---------'---------'----------------'---------'-
| 1 |        1|        2|         |                |         |
| 2 |        3|        4|         |                |         |
| 3 |         |         |         |              17|         |
| 4 |         |         |         |abc             |         |
| 5 |         |         |         |2056-03-29 17:03|         |
| 6 |         |         |         |              18|         |
| 7 |         |         |         |                |         |

Kopieren des Wertes einer Zelle (6,4) in eine andere Zelle (7,4)

Let Cells( 7, 4 )= Cells( 6, 4 )

Tabellenblatt

         1         2         3             4        
.---.---------.---------.---------.----------------.-
|   |    A    |    B    |    C    |        D       |
'---'---------'---------'---------'----------------'-
| 1 |        1|        2|         |                |
| 2 |        3|        4|         |                |
| 3 |         |         |         |              17|
| 4 |         |         |         |abc             |
| 5 |         |         |         |2056-03-29 17:03|
| 6 |         |         |         |              18 <-- Formel "=D3+1"
| 7 |         |         |         |              18 <-- Zahlenwert "18"
| 8 |         |         |         |                |

Die Eigenschaften von Range-Objekten

Range-Objekte enthalten verschiedene Eigenschaften, wie »Address« (Adresse), »Interior« (Innenbereich) oder »Formula« (Excel -Ausdruck).

Ausgabe der Adresse  einer Zelle

? Cells( 6, 4 ).Address

$D$6

Ausgabe des Wertes  in einer Zelle

? Cells( 6, 4 ).Value

18

Ausgabe des Excel -Ausdrucks einer Zelle

? Cells( 6, 4 ).Formula

=D3+1

Kopieren  der Formel einer Zelle in eine andere Zelle

Let Cells( 7, 4 ).Formula = Cells( 6, 4 ).Formula

Festlegen der Farbkennzahl  des Innneren einer Zelle (6=Gelb)

Let Cells( 7, 4 ).Interior.ColorIndex = 6

Microsoft -Dokumentation für die Eigenschaft »ColorIndex«

docs.microsoft.com/en-us/office/vba/api/excel.colorindex

Microsoft -Dokumentation für die Eigenschaft »Interior«

docs.microsoft.com/en-us/office/vba/api/excel.range.interior

Namen für Farben für die Eigenschaft »ColorIndex«

xlColorIndexNone        keine Farbe
xlColorIndexAutomatic   automatisch

Kennzahlen für Farben für die Eigenschaft »ColorIndex«

 1   Schwarz
 2   Weiß
 3   Rot
 4   Hellgrün
 5   Blau
 6   Gelb
 7   Rosa
 8   Türkis
 9   Dunkelrot
10   Grün
11   Dunkelblau
12   Dunkelgelb
13   Violett
14   Blaugrün
15   25%iges Grau
16   50%iges Grau
33   Himmelblau
34   Türkis
35   Hellgrün
36   Hellgelb
37   Hellblau
38   Rose
39   Lavendel
40   gebraeunte Haut
41   Hellblau
42   Aqua
43   Kalk
44   Gold
45   Hellorange
46   Orange
47   Blaugrau
48   40%iges Grau
49   Dunkelblaugrün
50   Seegrün
51   Dunkelgrün
52   Olivgrün
53   Braun
54   Pflaume
55   Indigo
56   80%iges Grau

Fehlermeldung „1004: Die ColorIndex-Eigenschaft des Interior-Objekts kann nicht festgelegt werden“

1004 - unable to set ColorIndex property of Interior Class 

Bei dieser Fehlermeldung sollte geprüft werden, ob der Wert der rechten Seite wirklich im zulässigen Wertebereich und die Tabelle nicht geschützt ist.

Das Arbeiten mit Zwischenvariablen

Eine Variable für einen bestimmten Zwischenwert eines Ausdrucks nennen wir hier eine Zwischenvariable.

Durch Zwischenvariablen können wir die Bedeutung und den Typ bestimmter Objekte und Werte verdeutlichen.

Modul1

Option Explicit
Sub Main()
  Dim R As Excel.Range ' Fruehe Bindung
  Set R = Cells( 6, 4 )
  Debug.Print R.Value
End Sub

Ausgabe des Wertes einer Zelle

18

Modul1

Sub Kopieren
  Dim r As Object ' Spaete Bindung
  Set r = Cells( 1, 1 )
  Let r = 1
End Sub

Direktbereich

? Typename( Cells(1,1).Interior )

Interior

? Typename( Cells(1,1).Interior.ColorIndex )

Long

Modul1

Option Explicit
Sub Main()
  Dim R As Excel.Range
  Set R = Cells( 6, 4 )
  Dim Interior As Excel.Interior
  Set Interior = R.Interior
  Const Hellblau = 37
  Let Interior.colorIndex = Hellblau
End Sub

Der folgende Quelltext könnte jedoch nicht  am Ende des obigen Subs verwendet werden, da der Typ »Long« kein Typ eines Objekts ist.

Quelltext

Dim ColorIndex As Long
Set ColorIndex = interior.ColorIndex

Die Value-Eigenschaft

Falls ein Bereich nur eine Zelle  umfaßt (oder nicht-rechteckig ist), so ist die Eigenschaft »Value« die Fehleigenschaft des Bereiches.

Modul1

Option Explicit
Sub Main()
  Dim R As Excel.Range
  Set R = Cells( 6, 4 )
  Debug.Print R.Value
  Debug.Print R
End Sub

Ausgabe des Wertes einer Zelle

18
18

Falls eine andere Eigenschaft verwendet werden soll, so muß diese ausdrücklich angegeben werden.

Modul1

Option Explicit
Sub Main()
  Dim R As Excel.Range
  Set R = Cells( 6, 4 )
  Debug.Print R.Formula
End Sub

Ausgabe des Wertes einer Zelle

=D3+1

Auch ein Bereich mit mehreren Zellen  hat eine Value-Eigenschaft, die bei einem rechteckigen Bereich dann eine Reihung ergibt und sonst die erste Zelle des Bereichs. Wenn die Zelle leer ist, ergibt »Value« den Wert »Empty«. Der Typ des Ergebnisses kann mit »IsArray« beziehungsweise »IsEmpty« ermittelt werden.

Die Item-Eigenschaft

Falls ein Bereich rechteckig ist und mehr als eine Zelle umfaßt, so ist die Eigenschaft »Item« die Fehleigenschaft des Bereiches.

Sie erlaubt eine Auswahl einer Zeile, Spalte oder Zelle durch Angabe von Positionszahlen.

Modul1

Option Explicit
Sub Main()
  Dim R As Excel.Range
  Set R = Cells
  Debug.Print R.Item( 6, 4 )
  Debug.Print R( 6, 4 )
End Sub

Ausgabe des Wertes einer Zelle

18
18

In der Zeile »Set range = Cells« steht »Cells« rechts vom Gleichheitszeichen nicht  für eine Fehleigenschaft von »Cells«, sondern für das Objekt »Cells« selber. (Dies ist immer so rechts vom Gleichheitszeichen einer Set-Anweisung, falls die rechte Seite nicht eingeklammert ist.)

Microsoft -Dokumentation

docs.microsoft.com/en-us/office/vba/api/excel.range.item

In einigen Quellen wird zur Vereinfachung (oder weil die Autoren es selber nicht besser wissen) gesagt, daß »Item« die Fehleigenschaft von Range-Objekten ist. Tatsächlich haben wir aber schon erfahren, daß manchmal auch »Value« als Fehleigenschaft herangezogen wird. Genaugenommen kann ein Objekt aber immer nur eine  Fehleigenschaft haben und die tatsächlich Fehleigenschaft von Range-Objekten ist weder »Item« noch »Value«, sondern »_Default«. Dieses Detail muß man aber nicht wissen, und daher wird es weiter unten nur für neugierige Leser noch etwas vertieft werden.

Nützliche Subs

Das Sub »Drucke« erlaubt es ein Tabellenblatt als Ausgabeziel für VBA  zu verwenden.

Modul1

Option Explicit
Sub Drucke( Auszugebendes As Variant )
    Static aktuelleZeile As Integer
    Let aktuelleZeile = aktuelleZeile + 1
    Let Cells( aktuelleZeile, 1 )= Auszugebendes
End Sub
Sub Main()
    Drucke "alpha"
    Drucke 12
End Sub

Tabelle

         1         2      
.---.---------.---------.-
|   |    A    |    B    | 
'---'---------'---------'-
| 1 |alpha    |         |
| 2 |       12|         |
| 3 |         |         |

Übungsaufgaben

/   Übungsaufgabe

Schreiben Sie ein Sub, das den Inhalt der Zellen ›A1‹ und ›A2‹ summiert und das Ergebnis in die Zelle ›A3‹ schreibt.

Sie können für diese Aufgabe davon ausgehen, daß zwei Zahlen  in ›A1‹ und ›A2‹ stehen und sollten daher vor dem Schreiben des Subs (ohne VBA ) jeweils eine Zahl in ›A1‹ beziehungsweise ›A2‹ schreiben.

Reserveaufgaben

/   Reserveaufgabe

Schreiben Sie ein Sub, das die Texte aus den Zellen ›A1‹ und ›B1‹ mit einem Komma getrennt hintereinanderschreibt und das Ergebnis in die Zelle ›C1‹ schreibt.

Sie können für diese Aufgabe davon ausgehen, daß zwei Zeichenfolgen  (Texte) in ›A1‹ und ›B1‹ stehen und sollten daher vor dem Schreiben des Subs (ohne VBA ) jeweils ein Wort in ›A1‹ beziehungsweise ›B1‹ schreiben.

Form mit einem numerischen Argument ⃗

Bei der Verwendung nur eines numerischen Arguments stehen die Zahlen von 1 bis 256 für Zellen in der ersten Zeile, Zahlen von 257 bis 512 für Zellen in der zweiten Zeile und so weiter.

Direktbereich

? Cells( 1 ).Address
$A$1
? Cells( 2 ).Address
$B$1
? Cells( 256 ).Address
$IV$1
? Cells( 257 ).Address
$A$2

Beobachtung ⃗

Modul1

Function O(Ob As Object) As Object
  Debug.Print VBA.[_HiddenModule].ObjPtr(Ob)
  Set O = Ob
End Function
Sub Main()
  Debug.Print O(Cells) Is O(Cells)
End Sub

Direktbereich

 651380
 644276
Falsch

Der Fehlwert eines Range-Objektes *

Dieser Abschnitt erklärt noch, wie es intern realisiert wird, daß die Fehleigenschaft eines Range-Objekts manchmal »Value« und manchmal »Item« sein kann.

Wenn man im Kontextmenü des Objektkataloges anwählt „Verborgene Elemente anzeigen“ und sich dann den Inhalt der Klasse »Range« anschaut (links, unter „Klassen“) findet man rechts unter „Elemente von 'Range'“ in der alphabetisch sortierten Auflistung hinter „DataSeries“ den normalerweise versteckten Eintrag »_Default«. Dies ist die Fehlvariable eines Range-Objektes.

Objektkatalog zu „Range._Default“

Property _Default([RowIndex], [ColumnIndex])
    Standardelement von Excel.Range

„Standardelement“ bedeutet im Objektkatalog das gleiche wie hier in diesem Kurs „Fehlvariable“.

Sie muß mit »Cells.[_Default]« angesprochen werden, da der Grundstrich ohne die eckigen Klammern nicht erlaubt ist.

Direktbereich

? Typename( Cells.[_Default] )

Variant()

Der Typ »Variant()« ist der Typ einer Variant-Reihung. Eine Variant-Reihung akzeptiert einige Zahlen und ergibt daraufhin einen anderen Wert (vom Typ »Variant«). Hier werden normalerweise zwei Zahlen verwendet.

Auch beim Eintippen von »? Cells(« im Direktbereich erscheint unter einigen VBA -Implementation das gelbliche Info-Kästchen „_Default([RowIndex], [ColumnIndex])“.

Die _Default-Eigenschaft erwartet zwei Zahlen und ergibt daraufhin den Wert des aktuellen Tabellenblatts in der entsprechenden Zeile und Spalte.

Direktbereich

? Cells.[_Default]( 1, 2 )

 2 

Die Zahl »2« wird hier aber nur dann ausgegeben, wenn man diesen Wert zuvor in die Zelle B1 geschrieben hat!

An sich gibt es bereits die Eigenschaft »Item« für denselben Zweck.

Direktbereich

? Cells.Item( 1, 2 )

 2 

Im Gegensatz zu »Item« weist »_Default« nur die eine Besonderheit auf, daß es keine Reihung, sondern (per ».Value«) einen einfachen Wert ergibt, wenn der Bereich (vor dem Punkt ».«) nur eine Zelle umfaßt, sonst verhält es sich wie ».Item« und erwartet noch eingeklammerte Zahlen. (Ein Beispiel dazu, daß keine Zahlen angegeben werden müssen folgt später.)

Da »_Default« die Fehleigenschaft von Range-Objekten ist, kann man es auch weglassen. Damit kann »Cells.[_Default]( 1, 2 )« zu »Cells( 1, 2 )« verkürzt werden.

Direktbereich

? Cells( 1, 2 )

 2 

Falls man sein Gedächtnis derzeit nicht mit den Hintergrundinformationen zu »_Default« und »Item« belasten will, reicht es vollkommen aus, sich zu merken, daß »Cells( 1, 2 )« der Wert der Zelle in Zeile 1 und Spalte 2 ist.