VBA in Excel/ Variablen und Arrays

| Hoch zu „Inhaltsverzeichnis“ |

Grundlegendes

Was sind Variablen?

Variablen sind eine Art von Platzhalter für Zeichenfolgen, Werte und Objekte. So können beispielsweise mehrfach anzuzeigende Meldungen, bei Berechnungen mehrfach einzusetzende Werte oder in einer Schleife anzusprechende Objekte in Variablen gespeichert werden.

Wann sind Variablen einzusetzen?

Der Einsatz von Variablen ist immer dann sinnvoll, wenn das Element mehrfach angesprochen wird. Sinnvoll eingesetzt, beschleunigen Variablen die Ausführung eines VBA-Programms erheblich. Wird das Element im Code nur einmal angesprochen – wie zum Beispiel eine Msg-Meldung – ist das Speichern dieser Zeichenfolge in eine String-Variable überflüssig und verwirrend. Ausnahmen bilden Fälle, in denen auch bei einmaligem Vorkommen die Übersichtlichkeit des Codes verbessert wird. Dies kann beispielsweise bei langen Objektnamen der Fall sein.

Sind Variablen zu deklarieren?

Eine Deklaration der Variablen sollte immer erfolgen (siehe auch Variablendeklaration). Dazu sollte in der Entwicklungsumgebung im Menü Extras / Optionen die CheckBox Variablendeklaration erforderlich aktiviert sein. VBA-Anweisungen zur Deklarierung sind:

• Dim
  • In einer Function oder Sub Anweisung. Die Deklaration sollte am Anfang stehen
  • Zu Beginn eines (Standard-)Moduls oder Klassenmoduls, ist gleichwertig mit Public Dim
• Private: Am Anfang eines (Standard-)Moduls oder Klassenmoduls, bedeutet Private Dim (nicht zulässig)
• Global entspricht Public, aus Gründen der Abwärtskompatibilität unterstützt

Empfehlenswert ist ein Kommentar in der Zeile vor der Variablendeklaration oder in der Zeile der Deklaration am Ende, um den Zweck der Variablen zu erklären. Beispiel:

  Private i As Integer ' Schleifenzähler

Wo sind Variablen zu deklarieren?

Variablen, die nur für die Prozedur gelten sollen, sind innerhalb der Prozedur, in der Regel am Prozeduranfang zu deklarieren. Variablen, die außerhalb einer Prozedur deklariert werden, gelten für das ganze Modul, werden sie als Public deklariert, für das gesamte Projekt. Zu einem sauberen Programmierstil gehört es, Variablen soweit irgend möglich nur auf Prozedurebene zu deklarieren und an Unterprogramme als Parameter zu übergeben.

Sind Variablen zu dimensionieren?

Wenn Variablen als Array deklariert wurden, z.B. Dim MitgliedsNr() As Long können sie entweder mit der Deklaration dimensioniert werden (Dim MitgliedsNr(1001) As Long oder Dim MitgliedsNr(1 To 1000) As Long oder nachträglich mit der ReDim-Anweisung

Sind Objekttyp-Variablen bestimmten Objekten zuzuweisen?

Zur Referenzierung von Objekten durch Variable kann stets der allgemeine Typ Variant (nicht empfehlenswert), als auch der allgemeine Objekttyp Object verwendet werden. Wenn die Bibliothek des Objekts über das Menü 'Extras' 'Verweise' eingebunden ist, kann auch der spezielle Objekttyp deklariert werden. Zu bevorzugen ist immer eine möglichst genaue Deklaration, die Deklaration des spezifischen Objekttyps bietet vor allem diese Vorteile:

• Schnellerer Programmablauf
• Weniger Speicherbedarf als bei Variant
• In der Entwicklungsumgebung werden während der Programmierphase - wenn im obigen Dialog die CheckBox Elemente automatisch auflisten aktiviert ist - beim Eintippen des Punktes nach einem Objektnamen alle Methoden und Eigenschaften automatisch aufgelistet, was Fehler vermeidet und Schreibarbeit erspart.
• Fehlermeldungen schon beim Kompilieren (falls beispielsweise Argumente fehlerhaft sind), genauere Fehlerbeschreibungen

Konstanten

Konstanten werden hier der Vollständigkeit halber erwähnt. Weisen Sie immer dann, wenn ein Wert vom Programmstart bis zum Programmende unverändert bleibt, diesen einer Konstanten, keiner Variablen zu. Konstanten werden in VBA-Programmen schneller verarbeitet als Variablen. Konstanten werden generell im Allgemein-Abschnitt von Modulen deklariert, Private-Konstanten in Klassen- und Standard-, Public-Konstanten nur in Standardmodulen. Beispiel für eine Konstanten-Deklaration:

Private Const cintStart As Integer = 5

Variablentypen

Die gebräuchlichen Variablentypen:

Variablentyp	Namenskonvention	Res.Speicherplatz	Kurzbezeichnung	Dezimalstellen
Boolean	bln	16 Bit, 2 Bytes		-
Byte		8 Bit, 1 Byte		-
Integer	int	16 Bit, 2 Bytes	%	-
Long	lng	32 Bit, 4 Bytes	&	-
Currency	cur		@	32
Single	sng	32 Bit, 4 Bytes	!	8
Double	dbl	64 Bit, 8 Bytes	#	16
Date	dat	64 Bit, 8 Bytes
String	str		$
Object	obj	32 Bit, 4 Bytes
Variant	var	128 Bit, 16 Bytes
benutzerdefinierter Typ	typ
Objekttyp

Variablentyp	Beschreibung
Boolean	WAHR (-1) oder FALSCH  (0)
Byte	0 ... +255
Integer	-32.768 ... +32.767
Long	-2.147.483.648 ... +2.147.483.647
Currency	-922.337.203.685.477,5808 ... +922.337.203.685.477,5807
Single	$\pm$3,402823E38 ... $\pm$1,401298E-45 und 0
Double	-1.79769313486231E308 bis -4,94065645841247E-324 für negative Werte und von 4,94065645841247E-324 bis 1,79769313486232E308 für positive Werte und 0
Date	Datum und Zeit
String	Zeichenfolgen (Text)
Object	Objekte
Variant	Alle Typen, Voreinstellung
benutzerdefinierter Typ	ein oder mehrere Elemente jeden Datentyps. Der Aufbau wird mit einer Type-Anweisung deklariert
Objekttyp	Objekte wie Workbook, Range

Anmerkungen zu den Variablentypen

Boolean

Dieser Datentyp speichert eigentlich nur ein Bit, aus Gründen der Speicherorganisation wird jedoch stets ein Byte belegt. Die Werte von Boolean werden als 8-Bit Zahl dargestellt, wobei nur -1 (= alle Bits gesetzt bei Darstellung der -1 als Zweierkomplement) als WAHR gilt, jeder andere Wert aber als FALSCH. Speziell bei Vergleichen wird das Ergebnis FALSCH als 0 (= kein Bit gesetzt) zurückgegeben.

In Kenntnis dieser Interpretation kann der Programmierer Vergleiche auch direkt auf Zahlenwerte in Long-, Integer- und Byte-Datentypen (bei letzteren setzt der Wert 255 alle Bits) anwenden. Aus Gründen der Lesbarkeit des Codes sollte das aber vermieden werden.

Byte

Bei diesem Variablentyp ist in speziellen Fällen Vorsicht geboten, beispielsweise kann bei

For i = 10 To 0 Step -1

dieser Schleifenkonstruktion ein Unterlauf-Fehler auftreten, wenn i als Byte dimensioniert wird, weil in der internen Berechnung auch noch -1 berechnet wird. Wird als Endwert der Schleife 1 statt 0 angegeben oder wird beispielsweise der Datentyp Integer für i verwendet, gibt es kein Problem.

Date

Der Typ speichert das Datum in zwei Teilen:

• Vor dem Komma steht die fortlaufende Tagesnummer. Tag 0 dieser Zählung ist der 31.12.1899; Bei der Anzeige wird es in die vom System eingestellte Darstellung von Tag, Monat und Jahr umgerechnet.
• Nach dem Komma stehen die Anteile des Tages. 0,25 steht für 6 Stunden, 0,5 für 12 h usw.

Vom Wert her ist der Inhalt dieses Datentyps nicht von einem Fließkommawert zu unterscheiden. Entsprechend einfach können Tage und Stunden addiert werden, hier einige Beispiele:

• Um zu einem Datum h Stunden zu addieren, rechnet man Datum + h/24
• Um zu einem Datum h Stunden und m Minuten zu addieren, rechnet man Datum + h/24 + m/(24*60) oder Datum + (h + m/60)/24
• Um zu einem Datum h Stunden und m Minuten und s Sekunden zu addieren, rechnet man Datum + (h + (m + s/60)/60)/24

Currency

Der Datentyp ist ein Festkommaformat mit vier Nachkommastellen. Daher wird er intern wie eine Ganzzahl berechnet. Wenn die Genauigkeit ausreicht, kann mit der Wahl dieses Datentyps gegenüber Single und Double die Berechnung schneller erfolgen. Bei Kettenrechnungen mit langen oder periodischen Dezimalteilen ist allerdings mit einem Genauigkeitsverlust zu rechnen.

String

Der Datentyp speichert Zeichen mit variabler Länge von maximal 2³¹ Zeichen.

Für bestimmte Zwecke können auch Strings mit fester Länge sinnvoll sein. Sie können mit einem * definiert werden, Beispiel String mit der festen Länge 3:

    Public Sub Demo_StringMitFesterLänge()
        Dim ZeichenKette As String * 3
        ZeichenKette = "A"
        MsgBox ">" & ZeichenKette & "<"
    End Sub

Bei der Zuweisung von "A" wird der String von links belegt, die übrigen Zeichen werden mit einem Leerzeichen aufgefüllt. Die Strings mit fester Länge unterliegen gewissen Einschränkungen, so können sie max. 2¹⁶ Zeichen speichern und nicht mit dem Attribut Attribut Public in Klassenmodulen verwendet werden.

Benutzerdefinierte Typen

Diese Typen werden aus den Grundtypen mit Hilfe der Type-Anweisung zusammengesetzt. Das folgende Beispiel zeigt, wie die Typdeklaration für komplexe Zahlen aussehen könnte. Neben dem Real- und Imaginärteil wird in dem benutzerdefinierten Typ auch gespeichert, ob die komplexe Zahl in kartesischen Koordinaten (FALSE) oder in Polarkoordinaten (TRUE) abgelegt wurde.

Das Beispiel des komplexen Multiplikationsprogramms cMult wurde nur für den Fall ausgeführt, in dem beide Variablen in kartesischen Koordinaten vorliegen.

    Type Komplex            ' Komplexe Zahl
       Re      As Double    ' Realteil
       Im      As Double    ' Imaginärteil
       Winkel  As Boolean   ' FALSE = Kartesisch, TRUE = Polar
    End Type
    

    ' ** Funktion zur Multiplikation zweier komplexer Zahlen
    Public Function cMult(a As Komplex, b As Komplex) As Komplex
        If (a.Winkel = b.Winkel) Then
            ' Beide Zahlen liegen im gleichen Koordinatensystem vor
            If Not a.Winkel Then
                ' Beide Zahlen liegen in kartesischen Koordinaten vor
                ' Multiplikation in kartesischen Koordinaten
                cMult.Re = a.Re * b.Re - a.Im * b.Im
                cMult.Im = a.Im * b.Re + a.Re * b.Im
                cMult.Winkel = a.Winkel
           End If
        End If
    End Function

Das folgende Beispiel zeigt zwei Möglichkeiten, um die Variablen Faktor1 und Faktor2 mit Werten zu belegen und wie man das Ergebnis der Funktion cMult im weiteren Programmlauf verwerten kann:

    Public Sub Demo_KomplexeMultiplikation()
        Dim Faktor1  As Komplex  ' Erster Faktor
        Dim Faktor2  As Komplex  ' Zweiter Faktor
        Dim Ergebnis As Komplex  ' Komplexes Produkt
        
        ' Möglichkeit 1.1: Variable mit Hilfe der With-Anweisung belegen
        With Faktor1
            .Re = 2
            .Im = 3
            .Winkel = False
        End With
    
        ' Möglichkeit 1.2: Direkt belegen
        Faktor2.Re = 5
        Faktor2.Im = 7
        Faktor2.Winkel = False
        
        ' Möglichkeit 2.1: Ergebnis einer Variablen vom Typ Komplex zuweisen
        Ergebnis = cMult(Faktor1, Faktor2)
        
        ' Ausgabe ins Direktfenster
        Debug.Print Ergebnis.Re, Ergebnis.Im, Ergebnis.Winkel
        
        ' Möglichkeit 2.2: Alle Werte einzeln aus dem Rückgabewert der Funktion holen
        With cMult(Ergebnis, Faktor2)
            MsgBox Iif(.Winkel, "R: ", "x-Koordinate: ") & .Re
            MsgBox Iif(.Winkel, "Winkel: ", "y-Koordinate: ") & .Im
        End With
    End Sub

Der Einfachheit halber wurden die Rückgabewerte mit Debug.Print in das Direktfenster geschrieben.

Variablendeklaration

Wie schon erwähnt, sind Variablen generell zu deklarieren und zu dimensionieren. Werden sie nicht deklariert oder nicht dimensioniert, handelt es sich beim Programmstart in jedem Fall um den Variablentyp Variant, der zum einen mit 16 Bytes den größten Speicherplatz für sich beansprucht, zum anderen während des Programmablaufes seinen Typ mehrmals wechseln kann, was möglicherweise zu unerwarteten Verhalten und damit Fehlern führen kann. Außerdem benötigen Variant-Variablen erheblich längere Berechnungszeiten als andere.

Einsatz von String-Variablen

Im nachfolgenden Beispiel wird eine String-Variable deklariert und zum Finden und Ersetzen einer Zeichenfolge eingesetzt:

Sub Ersetzen()
   Dim rngCell As Range
   Dim strText As String
   strText = "Kasse "
   strYear = CStr(Year(Date))
   For Each rngCell In Range("A1:F15")
      If rngCell.Value = strText & Year(Date) - 1 Then
         rngCell.Value = strText & Year(Date)
      End If
   Next rngCell
End Sub

Im vorgegebenen Bereich werden alle Zellen darauf überprüft, ob ihr Text aus der Zeichenfolge Kasse und der Jahreszahl des Vorjahres besteht. Wenn ja, wird die Vorjahreszahl durch die aktuelle Jahreszahl ersetzt. String-Variablen sollten mit dem &-Zeichen verknüpft werden. Strings können auch mit + verknüpft werden. Dies funktioniert aber nur zuverlässig, wenn beide Variablen oder Ausdrücke strings sind. Falls ein Ausdruck numerisch ist und der andere ein String, der als Zahl interpretierbar ist, nimmt Excel eine Typumwandlung vor und liefert als Ergebnis die algebraische Summe der beiden Ausdrucke. Wenn in einem Ausdruck & mit + gemischt wird, berechnet VBA zuerst + (und alle anderen algebraischen Operationen wie -*/) dann erst &;

Beispiele:

• Aus "2" + "3" wird "23"
• Aus "2" + 3 wird 5
• Aus "2" & 3 wird "23"
• Aus "2" & 3 + 4 & "5" wird 275
• Aus "2" & 3 & 4 & "5" wird 2345
• Aus "2" + 3 & 4 + "5" wird 59

Einsatz von Variant-Variablen

Es gibt Fälle, in denen eine Variable ihren Typ ändert oder unterschiedliche Typen entgegennehmen muss. In diesem Fall können Variant-Variablen eingesetzt werden. Dies ist besonders dann notwendig, wenn eine Funktion unterschiedliche Datentypen zurückgeben kann, wie z.B. GetOpenFilename. Diese liefert entweder einen String als Pfadangabe oder den booleschen Wert FALSE, wenn in dem von ihr geöffneten Dialog die Schaltfläche 'Abbrechen' betätigt wurde:

Sub Oeffnen()
   Dim varFile As Variant
   varFile = Application.GetOpenFilename("Excel-Dateien (*.xls), *.xls")
   If varFile = False Then Exit Sub
   Workbooks.Open varFile
End Sub

Ein anderes Beispiel ist die Funktion IsMissing, mit der geprüft werden kann, ob einer Funktion ein optionales Argument übergeben wurde:

Public Sub EingabeMöglich(Optional Wert As Variant)
    If IsMissing(Wert) Then
        MsgBox "Kein Argument übergeben"
    Else
        MsgBox Wert
    End If
End Sub

Falls das übergebene Argument in (Optional Wert As String) geändert wird, funktioniert IsMissing() nicht mehr und das Programm durchläuft immer den Else-Zweig.

Einsatz von Public-Variablen

Im nachfolgenden Beispiel wird in einem Standardmodul eine Public-String-Variable deklariert. Diese wird in der Prozedur AufrufenMeldung mit einem Wert belegt; danach wird das Unterprogramm Meldung aufgerufen. Da die Variable außerhalb der Prozeduren deklariert wurde, ist der Wert nicht verlorengegangen und kann weiterverwertet werden.

Public strMsg As String

Sub AufrufenMeldung()
    strMsg = "Hallo!"
    Call Meldung
End Sub

Sub Meldung()
    MsgBox strMsg
End Sub

Auch wenn sich die Prozedur Meldung in einem anderen Modul befindet, funktioniert der Aufruf. Erfolgt jedoch die Deklaration mit Dim oder als Private, gilt sie nur für das jeweilige Modul.

Übergabe von String-Variablen

Eine Vorgehensweise wie im vorhergehenden Beispiel ist zu meiden und eine Übergabe der Variablen als Parameter ist vorzuziehen:

 Sub AufrufenMeldung()
     Dim strMsg As String
     strMsg = "Hallo!"
     Call Meldung(strMsg)
 End Sub
 
 Sub Meldung(strMsg As String)
     MsgBox strMsg
 End Sub

Variablen in Funktionen

Funktionen werden eingesetzt, wenn Werte zurückgeliefert werden müssen. Eine Alternative wäre (neben einer ByRef-Variablenübergabe) der Einsatz von Public-Variablen, die wir ja meiden wollen. Bei den Parametern einer Funktion handelt es sich ebenfalls um Variablen. Der Deklarationsbereich liegt innerhalb der Klammern der Funktion. Diese Parameter müssen beim Aufruf der Funktion - aus einem Tabellenblatt oder aus einer anderen Prozedur - übergeben werden. In der nachfolgenden Funkion wird die Kubatur errechnet:

Function Kubatur( _
   dblLaenge As Double, _
   dblBreite As Double, _
   dblHoehe As Double) As Double
   Kubatur = dblLaenge * dblBreite * dblHoehe
End Function

Die Eingabesyntax einer solchen Prozedur in einem Tabellenblatt ist, wenn die Werte in den Zellen A1:C1 stehen:

=kubatur(A1;B1;C1)

Wird die Funktion aus einer anderen Prozedur zur Weiterverarbeitung aufgerufen, sieht das wie folgt aus:

Sub ErrechneGewicht()
   Dim dblSpezGewicht As Double, dblKubatur As Double
   dblSpezGewicht = 0.48832
   dblKubatur = Kubatur(Range("A1"), Range("B1"), Range("C1"))
   Range("E1").Value = dblKubatur * dblSpezGewicht
End Sub

Hierarchische Anordnung der Objekttyp-Variablen

Über die Objekttypvariablen kann ein Typengerüst aufgebaut werden, indem die jeweils aktuelle Ebene referenziert wird:

Sub NeueSymbolleiste()
   Dim objCmdBar As CommandBar
   Dim objPopUp As CommandBarPopup
   Dim objButton As CommandBarButton
   Dim intMonth As Integer, intDay As Integer
   On Error Resume Next
   Application.CommandBars("Jahr " & Year(Date)).Delete
   On Error GoTo 0
   Set objCmdBar = Application.CommandBars.Add("Jahr " & Year(Date), msoBarTop)
   For intMonth = 1 To 12
      Set objPopUp = objCmdBar.Controls.Add(msoControlPopup)
      objPopUp.Caption = Format(DateSerial(1, intMonth, 1), "mmmm")
      For intDay = 1 To Day(DateSerial(Year(Date), intMonth + 1, 0))
         Set objButton = objPopUp.Controls.Add
         With objButton
            .Caption = Format(DateSerial(Year(Date), intMonth, intDay), _
               "dd.mm.yy - dddd")
            .OnAction = "MeldenTag"
            .Style = msoButtonCaption
         End With
      Next intDay
   Next intMonth
   objCmdBar.Visible = True
End Sub

Mit vorstehendem Code wird eine neue Symbolleiste mit dem Namen des aktuellen Jahres angelegt und im Symbolleistenbereich als nächstuntere platziert. Der Leiste wird für jeden Monat ein Menü und diesem Menü wird für jeden Tag eine Schaltfläche hinzugefügt.

Das Auslesen der betätigten Schaltfläche und die Datumsberechnungen erfolgen anhand einer Datumsvariablen:

Private Sub MeldenTag()
    Dim datAC As Date
    datAC = DateSerial(Year(Date), Application.Caller(2), Application.Caller(1))
    Select Case datAC
        Case Is < Date
            MsgBox Date - datAC & " Tage vergangen"
        Case Is = Date
            MsgBox "Heute"
        Case Is > Date
            MsgBox "Noch " & datAC - Date & " Tage"
    End Select
End Sub

Collections von Objekttyp-Variablen

Das Objekt UserForm1.Controls stellt alle Steuerelemente dar, die in der UserForm1 enthalten sind. Nicht ganz so einfach ist es, auf alle CheckBoxes dieser UserForm zuzugreifen, um sie über eine Schleife zu bearbeiten, denn die CheckBox ist kein gültiges Objekt, das heißt Controls. Liest man die CheckBoxes in ein Collection-Objekt ein, lassen Sie sich später problemlos ansprechen und in Schleifen einbinden:

Public colChBox As New Collection 

Private Sub UserForm_Initialize()
   Dim cnt As Control, intMonth As Integer
   For Each cnt In Controls
      If TypeName(cnt) = "CheckBox" Then
         intMonth = intMonth + 1
         colChBox.Add cnt
         cnt.Caption = Format(DateSerial(1, intMonth, 1), "mmmm")
      End If
   Next cnt
End Sub

Das Collection-Objekt wird - damit es seinen Wert nicht verliert - als Public außerhalb einer Prozedur deklariert und im Initialisierungscode der UserForm mit den Einzelobjekten - den 12 CheckBoxes der UserForm - belegt. Beim Klick auf die Schaltfläche Meldung werden alle aktivieren CheckBoxes in einer MsgBox ausgegeben:

Private Sub cmdMeldung_Click()
   Dim intCounter As Integer
   Dim strMsg As String
   strMsg = "Aktiviert:" & vbLf
   For intCounter = 1 To 12
      If colChBox(intCounter).Value Then
         strMsg = strMsg & colChBox(intCounter).Caption & vbLf
      End If
   Next intCounter
   MsgBox strMsg
End Sub

Arrays und Feldvariablen

Es gibt grundsätzlich zwei Möglichkeiten, Variablen für Matrizen zu schaffen. Entweder man deklariert die Variable als Variant und weist ihr ein Array zu oder man deklariert sie als Datenfeld. Variant-Variablen können Datenfeldvariablen aufnehmen.

Beispiel

Dim Array(1 to 200) as integer
'Zuweisung von Werten
Array(1) = 1

Arrays im Code

Eine einfache Methode, um im Code ein Array von Konstanten zu verwenden, benötigt eine Variable vom Typ Variant, in die ein Array gespeichert wird. Beim folgenden Beispiel sollen ein paar Tabellen explizit ausgeblendet werden. Das macht z.B. beim Öffnen einer Datei Sinn, wenn man sicher sein will, dass bestimmte Informationen nicht sichtbar sind.

Public Sub BlätterAusblenden()
    Dim MeineTabellen As Variant, Tabelle As Variant
    MeineTabellen = Array("Tabelle1", "Tabelle3")

    On Error Resume Next
    
    For Each Tabelle In MeineTabellen
        Worksheets(Tabelle).Visible = False
    Next Tabelle
End Sub

Der Vorteil des Arrays liegt hier in der Übersichtlichkeit, denn wenn man den Code anpassen muss, hat man die Namen der Tabellen schön zusammengefasst. Das Array kann aber auch Objekte speichern, und das macht bei dieser Aufgabe einen besonderen Sinn. Die Namen der Tabellen können geändert werden, dann läuft das Programm ins Leere. In VBA haben alle Tabellen einen zweiten Namen, der über die Eigenschaft .CodeName ausgelesen werden kann und der im VBA-Code auch gleichzeitig das Tabellenobjekt benennt. Die Eigenschaft .CodeName kann man lesen, wenn man im VBA-Editor den Projekt-Explorer öffnet. Dort steht neben dem Codenamen der Tabelle in Klammern der Name des Tabellenblattes in Excel. Nur im VBA-Editor oder mit VBA lässt sich der Codename ändern, einfaches Umbenennen des Blattes in Excel wirkt sich nicht auf den CodeNamen aus.

Wenn eine Mappe erstellt wird oder ein leeres Blatt eingefügt wird, erhält sind Name und Codename erst mal gleich. Das setzen wir auch beim folgenden Beispiel voraus. Leider funktioniert die For-Each-Schleife nicht mehr so gut, daher muss die Schleifenstruktur mit einem Zähler aufgebaut werden. LBound() und UBound() ermitteln den kleinsten und den größten Index des Arrays.

Public Sub BlätterAusblenden()
    Dim MeineTabellen As Variant, i As Integer
    MeineTabellen = Array(Tabelle1, Tabelle3)

    On Error Resume Next
    
    For i = LBound(MeineTabellen) To UBound(MeineTabellen)
        MeineTabellen(i).Visible = False
    Next i
End Sub

Wie bereits geschrieben, Tabelle1 und Tabelle2 sind in diesem Beispiel keine Texte, Namen oder Variablen, sondern echte Objekte (nämlich zwei Tabellen), die ihren CodeNamen tragen.

Arrays mit mehreren Dimensionen

Den folgenden Beispielen liegt folgende Aufgabe zu Grunde: Markiere einen Bereich in einer Tabelle und starte ein Makro, welches von jeder Zelle in der Markierung drei Werte speichert:

1. Die absolute Adresse
2. Die Zellformel bzw. den Zellwert, wenn keine Formel in der Zelle steht
3. Wahrheitswert (True/False), ob sich in der Zelle eine Formel oder ein Wert befand.

Das Array wird von dem Programm nicht weiter genutzt, aber das ist für diese Beispiele egal. Alle Lösungen erzeugen ein zweidimensionales Array.

• Mit der ersten Dimension steuert man beim Lesen oder Schreiben, ob die Zelle im Array gerade die Adresse, den Inhalt oder den booleschen Wert enthält. Die erste Dimension hat daher nur die Werte 1, 2, 3
• Die zweite Dimension ist die Zelle, sie läuft von 1 bis zur Anzahl der Zellen.

Public Sub DimensionsDemo1()
    Dim ZellInhalt() As String, Markierung As Range
    Dim ZelleNr As Long
    
    ' Bereich verkleinern, nur den benutzten Bereich bearbeiten
    Set Markierung = Intersect(Selection.Cells, Selection.Parent.UsedRange)
    
    ' Array auf die Größe des Bereiches setzen
    ReDim ZellInhalt(1 To 3, 1 To Markierung.Cells.Count)
    
    For ZelleNr = 1 To Markierung.Cells.Count
        With Markierung.Cells(ZelleNr)
            ' Adresse der Zelle speichern
            ZellInhalt(1, ZelleNr) = .Address(True, True, xlA1, True)
            
            ' Inhalt der Zelle speichern
            ZellInhalt(2, ZelleNr) = .Formula

            ' Speichern, ob die Zelle eine Formel / Wert enthielt
            ZellInhalt(3, ZelleNr) = .HasFormula
        End With
    Next ZelleNr
End Sub

Die nächste Lösung ist nur unwesentlich besser. Da man sich normalerweise die erste Dimension schlecht merken kann ("Was ist nun wieder 1, 2, oder 3?") kann man hier mit einer Aufzählung (Private Enum) nachhelfen. Die Aufzählung steht im Deklarationsbereich des Moduls.

' Definition einer Aufzählung
Private Enum InhaltsArt
    ddAdresse = 1
    ddInhalt = 2
    ddFormel = 3
End Enum

Public Sub DimensionsDemo2()
    Dim ZellInhalt() As String, Markierung As Range
    Dim ZelleNr As Long
    
    ' Bereich verkleinern, nur den benutzten Bereich bearbeiten
    Set Markierung = Intersect(Selection.Cells, Selection.Parent.UsedRange)
    
    ' Array auf die Größe des Bereiches setzen
    ReDim ZellInhalt(1 To 3, 1 To Markierung.Cells.Count)
    
    For ZelleNr = 1 To Markierung.Cells.Count
        With Markierung.Cells(ZelleNr)
            ' Adresse der Zelle speichern
            ZellInhalt(ddAdresse, ZelleNr) = .Address(True, True, xlA1, True)
            
            ' Inhalt der Zelle speichern
            ZellInhalt(ddInhalt, ZelleNr) = .Formula

            ' Speichern, ob die Zelle eine Formel / Wert enthielt
            ZellInhalt(ddFormel, ZelleNr) = .HasFormula
        End With
    Next ZelleNr
End Sub

Dies war nur eine kleine Verbesserung des Codes, um in lesbarer zu machen. Wenn man auf so ein Array mehrfach im Code zugreifen muss, dann reduziert das sicher die Fehler beim Schreiben des Programms.

Reduktion auf eine Dimension

Die nächste Überarbeitung hat eine ganz andere Qualität: Das Array wird nun eindimensional. Die unterschiedlichen Daten werden jetzt mit einer Typdefinition (Private Type) zusammengefasst. Mit ein paar zusätzlichen Maßnahmen spart dies viel Speicherplatz, denn durch die Typdefinition wird die Information kompakt gespeichert:

• Adresse wird nun als Range angelegt, da eine Objektreferenz nur 4 Bytes braucht. In den anderen Versionen wurde ein String mit der Adresse belegt, wo jedes Zeichen ein Byte braucht. Nachteil der Objektreferenz: Wird die Tabelle geschlossen, verliert die Objektreferenz die Verbindung zur Zelle.
• Der Inhalt, also ein Wert oder eine Formel bleibt weiterhin ein Text
• IstFormel ist vom Typ Bool, also nur noch ein Byte groß.

Dazu kann man mit der Array-Variablen Intellisense benutzen, denn Adresse, Inhalt und IstFormel werden automatisch angeboten, was Programmierfehler erheblich minimiert. Die Typdefinition steht wieder im Deklarationsteil des Moduls, also außerhalb der Prozedur.

' Typdefinition
Public Type InhaltsArt
    Adresse As Range
    Inhalt As String
    IstFormel As Boolean
End Type

Public Sub DimensionsDemo3()
    Dim ZellInhalt() As InhaltsArt, Markierung As Range
    Dim ZelleNr As Long
    
    ' Bereich verkleinern, nur den benutzten Bereich bearbeiten
    Set Markierung = Intersect(Selection.Cells, Selection.Parent.UsedRange).Cells
    
    ' Array auf die Größe des Bereiches setzen
    ReDim ZellInhalt(1 To Markierung.Count)
    
    For ZelleNr = 1 To Markierung.Count
        ' Adresse der Zelle speichern
        Set ZellInhalt(ZelleNr).Adresse = Markierung(ZelleNr)
        
        ' Inhalt der Zelle speichern
        ZellInhalt(ZelleNr).Inhalt = Markierung(ZelleNr).Formula

        ' Speichern, ob die Zelle eine Formel / Wert enthielt
        ZellInhalt(ZelleNr).IstFormel = Markierung(ZelleNr).HasFormula
    Next ZelleNr
End Sub

Man beachte, dass die Eigenschaft .Cells nun in die Anweisung Set Markierung = ... gewandert ist, was den Zugriff auf die Variable Markierung vereinfacht.!

Die letzte Variante soll besonders speicherplatzeffizient sein, da nur noch Zellen gespeichert werden, die tatsächlich gefüllt sind. Dazu wird jede Zelle geprüft, ob sie leer ist. Wenn sie voll ist, wird sie gespeichert und ein der Zähler LetzteBelegteZelle erhöht. Ganz zum Schluss wird das Array auf die Größe der tatsächlich belegten Zellen verkleinert.

' Typdefinition
Private Type InhaltsArt
    Adresse As Range
    Inhalt As String
    IstFormel As Boolean
End Type

Public Sub DimensionsDemo4()
    Dim ZellInhalt() As InhaltsArt, Markierung As Range
    Dim ZelleNr As Long, LetzteBelegteZelle As Long
    
    ' Bereich verkleinern, nur den benutzten Bereich bearbeiten
    Set Markierung = Intersect(Selection.Cells, Selection.Parent.UsedRange).Cells
    
    ' Array auf die Größe des Bereiches setzen
    ReDim ZellInhalt(1 To Markierung.Count)
    
    For ZelleNr = 1 To Markierung.Count
        If Not IsEmpty(Markierung(ZelleNr)) Then
            
            ' Zähler: Nur belegte Zellen zählen
            LetzteBelegteZelle = LetzteBelegteZelle + 1
            
            ' Adresse der Zelle speichern
            Set ZellInhalt(LetzteBelegteZelle).Adresse = Markierung(ZelleNr)
        
            ' Inhalt der Zelle speichern
            ZellInhalt(LetzteBelegteZelle).Inhalt = Markierung(ZelleNr).Formula
    
            ' Speichern, ob die Zelle eine Formel / Wert enthielt
            ZellInhalt(LetzteBelegteZelle).IstFormel = Markierung(ZelleNr).HasFormula
        End If
    Next ZelleNr
    
    ReDim Preserve ZellInhalt(1 To LetzteBelegteZelle)
End Sub

Beim Verkleinern ist das Preserve nach dem Redim wichtig, sonst werden die Inhalte des Arrays gelöscht!

| Hoch zu „Inhaltsverzeichnis“ |