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 z.B. 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. Um sich dazu zu zwingen, sollte man in der Entwicklungsumgebung im Menü Extras / Optionen die CheckBox Variablendeklaration erforderlich aktivieren.
Wo sind Variablen zu deklarieren?
Variablen, die nur für die Prozedur gelten sollen, sind innerhalb der Prozedur, in der Regel am Prozuduranfang zu deklarieren. Variablen, die außerhalb einer Prozedur deklariert werden, gelten für das ganze Modul, werden sie als Public dekariert, 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?
Variablen sind immer zu dimensionieren. Hiervon gibt es keine Ausnahmen.
Sind Objekttyp-Variablen bestimmten Objekten zuzuweisen?
Jeder Objekttyp-Variablen ist das Objekt korrekt zuzuweisen. Diese Vorgehensweise beschleunigt den Programmablauf, ausserdem werden in der Entwicklungsumgebung während der Programmierphase - wenn im obigen Dialog die CheckBox Elemente automatisch auflisten aktiviert ist - beim Ansprechen des Objekts alle Objektelemente automatisch aufgelistet, was Fehler vermeidet und Schreibarbeit erspart.
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 berechnet 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
Die gebräuchlichen Variablentypen:
| Variablentyp | Namenskonvention | Res.Speicherplatz | Kurzbezeichnung | Beschreibung |
| Boolean | bln | 16 Bit, 2 Bytes | WAHR (1) oder FALSCH (0) | |
| Integer | int | 16 Bit, 2 Bytes | % | -32.768 bis 32.767 |
| Long | lng | 32 Bit, 4 Bytes | & | -2.147.483.648 bis 2.147.483.647 |
| Currency | cur | @ | -922.337.203.685.477,5808 bis 922.337.203.685.477,5807 | |
| Single | sng | 32 Bit, 4 Bytes | ! | -3,402823E38 bis -1,401298E-45 für negative Werte und von 1,401298E-45 bis 3,402823E38 für positive Werte |
| Double | dbl | 64 Bit, 8 Bytes | # | -1.79769313486231E308 bis -4,94065645841247E-324 für negative Werte und von 4,94065645841247E-324 bis 1,79769313486232E308 für positive Werte |
| Date | dat | 64 Bit, 8 Bytes | Datum und Zeit | |
| String | str | $ | Zeichenfolgen | |
| Object | obj | 32 Bit, 4 Bytes | Objekte | |
| Variant | var | 128 Bit, 16 Bytes | Alle Typen, Voreinstellung | |
| benutzerdefinierter Typ | typ | ein oder mehrere Elemente jeden Datentyps | ||
| Objekttyp | Objekte wie Workbook, Range |
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 die Programmausführung erschwert. Außerdem benötigen Variant-Variablen erheblich längere Berechnungszeiten als andere.
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 werden mit dem &-, nicht dem +-Zeichen verknüpft. Der Einsatz des Pluszeichens kann zu überraschenden, ungewollten Ergebnissen führen.
Es gibt Fälle, in denen es wünschenswert ist, dass eine Variable ihren Typ ändert. In diesem Fall können Variant-Variablen eingesetzt werden. Ein typisches Beispiel:
Sub Oeffnen()
Dim varFile As Variant
varFile = Application.GetOpenFilename("Excel-Dateien (*.xls), *.xls")
If varFile = False Then Exit Sub
Workbooks.Open varFile
End Sub
Es wir ein Dialog zur Auswahl einer Datei aufgerufen. Wird dieser über die Schaltfläche Abbrechen beendet, nimmt die Variable varFile den Typ Boolean und den Wert False an. Wird der Dialog nicht abgebrochen, sondern normal beendet, wird varFile zu einer String-Variablen mit dem Dateinamen als Wert.
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.
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
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
Ü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
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
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.