CLS: Tipps & Tricks: VBA allgemein



Tipps & Tricks: VBA allgemein

Auf dieser Seite finden Sie 15 Tipps & Tricks für VBA allgemein.

Wenn in den folgenden Erläuterungen Texte wie MsgBox formatiert sind, handelt es sich um konkret einzugebende Inhalte wie VBA-Code oder die Eingabe von Werten. Menüs wie Datei Speichern sind wie hier zu sehen formatiert. Schaltflächen oder Registerkarten auf Dialogen werden wie Menüs behandelt.

Alle Tipps sind nach bestem Wissen geprüft, aber selbstverständlich ohne Gewähr. Sollten Sie doch einen Fehler darin entdecken, würden wir uns freuen, wenn Sie uns per eMail Bescheid sagen.

Wenn Ihr VBA-Code beispielsweise von Word aus auf Excel-Daten zugreifen soll, müssen Sie Excel zur Laufzeit einbinden. Ebenso gilt dies für den Zugriff von Access etwa auf Outlook-Daten. Das ist eine sogenannte "späte Bindung" (Late Binding), weil nicht schon im Entwurf, sondern erst zur Laufzeit das jeweils fremde Objektmodell hinzugebunden wird. Das können Sie standardisiert in einer Funktion kapseln, ohne irgendwelche Verweise hinzufügen zu müssen:

Function AnderesProg(strProgName As String) As Object
On Error Resume Next
Set AnderesProg = GetObject(, strProgName & ".Application")
If AnderesProg Is Nothing Then
Set AnderesProg = CreateObject(strProgName & ".Application")
If AnderesProg Is Nothing Then
MsgBox "Leider konnte '" & strProgName & _
"' nicht eingebunden werden!", vbCritical
End If
End If
On Error GoTo 0
End Function

Dabei versucht der Code mit GetObject eine schon laufende Instanz des gesuchten Programms zu finden. Erst wenn das scheitert, wird eine neue Instanz geöffnet. Der Aufruf dieser Funktion erfolgt beispielsweise für Word so:

Sub SucheWord()
Dim appWord As Object
 
Set appWord = AnderesProg("Word")
If Not appWord Is Nothing Then
MsgBox appWord.Caption
End If
End Sub

Die Anzeige der Beschriftung mittels MsgBox soll lediglich demonstrieren, dass der Zugriff nun möglich ist.

Die vier Standard-Operatoren +, -, * und / kennen Sie sicherlich. Es gibt aber noch ein paar mehr, die das Rechnen sehr erleichtern. Mit dem Caret-Zeichen (das "Dach" auf dem accent d'aigue) können Sie potenzieren:

dblKreisflaeche = 3.1415 * dblRadius * dblRadius

lässt sich kürzer als

dblKreisflaeche = 3.1415 * dblRadius ^ 2

schreiben. Dem Caret-Zeichen (^) muss bei der Eingabe immer noch eine Leertaste folgen, bevor es auch wirklich auf dem Bildschirm erscheint. Für das Wurzelzeichen gibt es keinen Operator. Das braucht es auch nicht, denn die Quadratwurzel ist:

dblWurzelAusZwei = 2 ^ 0.5

oder wenn Sie es lieber als Bruch schreiben:

dblWurzelAusZwei = 2 ^ (1 / 2)

Achten Sie dabei vor allem auf die Klammern, sonst würden Sie zuerst 2 ^ 1 rechnen und das dann durch 2 teilen. Damit erhielten Sie fälschlicherweise 1 als Ergebnis.

Außerdem können Sie den ganzzahligen Teil einer Division ermitteln, indem Sie statt des üblichen rechtsgeneigten Schrägstrichs (/, "Slash") den linksgeneigten (, "Backslash") benutzen:

intZahl = 11 4 'ergibt 2, weil 2*4=8

Das Gegenstück dazu ist der modulo-Operator, der den verbleibenden Rest der obigen Division errechnet:

intRest = 11 mod 4 'ergibt 3, weil 11-8=3

Einen Einsatz dieses Operators sehen im Tipp "Gerade/ungerade Seiten".

Neben den mathematischen Operatoren gibt es noch logische Operatoren, die ebenfalls "Berechnungen" durchführen. Sicherlich werden sie meistens als Vergleichsoperatoren eingesetzt wie im folgenden Beispiel:

If Me.edtVorname.Value = "" Or Me.edtNachname.Value = "" Then
MsgBox "Bitte kompletten Namen eingeben!", vbCritical
End If

Mit Hilfe der logischen Operatoren können Sie aber auch rechnen und in sogenannter Binärcodierung bitweise Informationen abspeichern. Während das Argument des gesparten Speicherplatzes heutzutage kaum noch überzeugt, erlaubt diese Technik Ihnen, statt vieler Boole'scher Einzelwerte einfach eine (Long-)Zahl zu speichern.

Sobald solche Daten gespeichert werden müssen, ist das Sichern als Zahl viel einfacher als mehrere Boole'sche Werte. Diese werden nämlich je nach System

  • direkt als Text (Excel: -1 wird zu WAHR) angezeigt oder
  • verbrauchen viel Platz (Textdateien: 14 entspräche 0, 0, 0, 0, -1, -1, -1, 0) oder
  • brauchen je ein eigenes Datenfeld (Access: statt eines Long-Zahlenfelds 16 Ja/Nein-Felder).
Daher lohnt es sich, gelegentlich auch noch mal an die Grundzüge des Computers zu denken und mit einzelnen Bits zu arbeiten.

Dazu braucht es jedoch vorab einen kurzen Blick auf die Art, wie ein Computer Zahlen "versteht". Ein einzelnes Bit kann nur die Zustände Nein/Ja (bzw. 0/-1) annehmen. Wenn Sie acht davon zusammenpacken, nennt sich das "Byte" und kann 28 Kombinationen aus Nein/Ja annehmen. Jedes Bit darin steht für eine Zweierpotenz, wobei von rechts gelesen wird und die erste Zweierpotenz 20=1 ist:

27 26 25 24 23 22 21 20
0 0 0 0 | 0 0 |
0+ 0+ 0+ 0+ 8+ 0+ 0+ 1= 9
0 0 0 0 | | | |
0+ 0+ 0+ 0+ 8+ 4+ 2+ 1= 15

Die Summe der jeweiligen Zweierpotenzen, deren Bit auf Ja steht, ergibt den Zahlenwert. Soweit ist das natürlich für einen normalen Gebrauch viel zu kompliziert. Sie können aber ebenfalls jedes einzelne Bit setzen und abfragen, so dass sich damit in einer Byte-Zahl 8 Ja/Nein-Entscheidungen oder in einer Long-Zahl 16 Entscheidungen speichern lassen.

Dabei müssen die Zahlenwerte der jeweiligen Bit-Vergleichswerte Zweierpotenzen sein, denn dann ist genau nur ein Bit gesetzt. Einzelne Bits einer Zahl verändern Sie, indem Sie diese mit einer anderen Zahl (welche exakt das zu ändernde Bit enthält) über OR verknüpfen:

0 0 0 0 | 0 0 |
OR
0 0 0 0 0 0 | 0
=
0 0 0 0 | 0 | |

Der OR-Operator vergleicht jedes einzelne Bit mit dem jeweils passenden der anderen Zahl und setzt das jeweilige Ergebnis-Bit auf True bzw. -1, wenn wenigstens eines der beiden gesetzt war. Dadurch sind alle Ergebnis-Bits "angeschaltet", die bei einer der Zahlen auch "an" waren. Einzig die Bits, die wirklich bei beiden Zahlen "aus" waren, bleiben "aus". Daher können Sie bei einer Zahl genau ein Bit einschalten, indem Sie eine zweite Zahl wählen, die genau nur dieses eine Bit gesetzt hat. Alle "0"-Bits ändern nichts.

Das gezielte Ausschalten erfolgt etwas komplizierter mit dem "Kehrwert" des Bitschalters und dem AND-Operator. Das hat zur Folge, dass im Bitschalter jetzt alle außer dem zu ändernden Bit auf True stehen und über die AND-Verknüpfung nur dieses eine abgeschaltet wird:

0 0 0 0 | 0 | |
AND
| | | | | | 0 | (Kehrwert des vorigen Beispiels)
=
0 0 0 0 | 0 0 |

Im folgenden Beispiel sollen zu einem fiktiven Dokument Informationen gesichert werden, wie dessen Status ist, also ob es schon bearbeitet wurde, ob es eine Zweitunterschrift benötigt usw. Dazu bereiten Sie als Bit-Vergleichswerte am besten ein paar Konstanten vor, damit es besser lesbar wird:

Option Explicit
 
Dim lngStatus As Long
 
Const Unbearbeitet = 0
Const Erledigt = 1
Const Intern = 2
Const Zweitunterschrift = 4
Const AnfahrtSkizze = 8
Const MaxWert = 255

Dann ist eine Funktion Status hilfreich, welche das jeweilige Bit bequem ein und ausschaltet:

Function Status(lngTeil As Long, lngGesamt As Long, booAn As Boolean)
If booAn Then
Status = (lngGesamt Or lngTeil)
Else
Status = (lngGesamt And (MaxWert - lngTeil))
End If
End Function

Die Differenz zwischen MaxWert und lngTeil ist genau der gesuchte Kehrwert von lngTeil, so dass dieses Ergebnis dann mit dem And-Operator verknüpft werden kann.

Außerdem sollten Sie noch eine Funktion HatStatus bereitstellen, mit der Sie ermitteln können, ob das gesuchte Bit gesetzt ist:

Function HatStatus(lngTeil As Long, lngGesamt As Long) As Boolean
HatStatus = ((lngGesamt Or lngTeil) = lngGesamt)
End Function

Diese Funktion versucht das Bit mittels OR-Operator zu setzen und vergleicht das mit dem vorherigen Wert. Wenn das Ergebnis identisch ist, war das Bit bereits gesetzt.

Damit können Sie wie im folgenden Beispiel in einer einzigen Variablen lngStatus bis zu 16 verschiedene Informationen abspeichern:

Sub DokumentStatusTesten()
lngStatus = Unbearbeitet
Zeige
 
lngStatus = Status(Erledigt, lngStatus, True)
Zeige
 
lngStatus = Status(Intern, lngStatus, False)
Zeige
 
lngStatus = Status(Zweitunterschrift, lngStatus, True)
Zeige
 
lngStatus = Status(Intern, lngStatus, False)
Zeige
End Sub
 
Sub Zeige()
Debug.Print "erledigt: " & HatStatus(Erledigt, lngStatus) & ", " & _
"intern: " & HatStatus(Intern, lngStatus) & ", " & _
"Zweitunterschrift: " & HatStatus(Zweitunterschrift, lngStatus) & ", " & _
"Anfahrt: " & HatStatus(AnfahrtSkizze, lngStatus)
End Sub

Das hat in der Programmierung vor allem die Bequemlichkeit, dass eine neue Information einfach durch eine weitere Konstante wie Const perEmail = 16 berücksichtigt werden kann

Wenn Sie in einer Office-Datei Meta-Daten wie "Letzter Zugriff", "Änderung durch" o.ä. speichern wollen, bieten sich die benutzerdefinierten Eigenschaften an. Diese erreichen Sie per Menü Datei Eigenschaften auf der Registerkarte "Anpassen". Lästig ist bei diesen nur, dass noch nicht vorhandene Eigenschaften erst erzeugt werden müssen, ansonsten gibt es eine Fehlermeldung. Daher bietet es sich an, das Problem in eine eigene Prozedur auszulagern, welche das abfängt:

Property Let CustomProp(strName As String, strText As String)
Dim varTest As Variant
 
With ActiveDocument.CustomDocumentProperties
On Error Resume Next
varTest = .Item(strName).Value
If Err.Number = 0 Then
.Item(strName).Value = strText
Else
.Add Name:=strName, LinkToContent:=False, _
Value:=strText, Type:=msoPropertyTypeString
End If
On Error GoTo 0
End With
End Property

Die Erläuterungen zum Einsatz einer Property finden Sie im Tipp "Properties statt Sub/Function". Bitte beachten Sie, dass ActiveDocument ein spezifisches Word-Objekt ist, bei Excel heißt es beispielsweise ActiveWorkbook. Dies müssen Sie für das gewünschte Office-Programm anpassen.

Hier geht es vor allem darum, dass bei ausgeschalteter Fehlerbehandlung per Zugriff auf den Inhalt der benutzerdefinierten Eigenschaft zuerst geprüft wird, ob diese schon existiert. lässt sich fehlerfrei ein Inhalt auslesen, kann direkt ein neuer Wert (gleichen Typs!) hineingeschrieben werden. Im Fehlerfall muss diese Eigenschaft erst neu erzeugt werden.

Entsprechend soll auch beim Auslesen des Wertes eine gar nicht vorhandene Eigenschaft keinen Fehler auslösen, sondern einfach einen Leerstring zurückgeben. Dies erreichen Sie mit dem folgenden Code:

Property Get CustomProp(strName As String) As String
Dim varTest As Variant
 
With ActiveDocument.CustomDocumentProperties
On Error Resume Next
CustomProp = .Item(strName).Value
If Err.Number <> 0 Then
CustomProp = ""
End If
On Error GoTo 0
End With
End Property

Zum Testen brauchen Sie noch eine Prozedur, welche verschiedene Werte speichert und diese anschließend wieder ausliest:

Sub TesteCustomProps()
CustomProp("Merke dies") = "was denn?"
CustomProp("Letzter Benutzer") = Application.UserName
 
MsgBox "Inhalt von 'Letzter Benutzer': " & _
CustomProp("Letzter Benutzer") & vbCrLf & _
"Inhalt von 'Gibt es nicht': " & _
CustomProp("Gibt es nicht")
End Sub

Da die benutzerdefinierte Eigenschaft Gibt es nicht nie erzeugt wurde, liefert die Property wie gewünscht einen Leerstring zurück.

Achtung! Word betrachtet die Änderungen an benutzerdefinierten Eigenschaften nicht als Änderung am Dokument. Sie würden also vor dem Schließen nicht gefragt, ob dieses Dokument gespeichert werden soll. Um das zu erzwingen, setzen Sie einfach das Dokument auf ungeändert mit ActiveDocument.Saved = False.

Vor allem, wenn Sie mit kompexen Daten aus Tabellen arbeitet, ist es oft praktisch, unterschiedliche Datentypen in einem Paket zusammenzufassen. Das klassische Beispiel sind die Informationen rund um eine Person: Vor- und Nachname als Datentyp String, der Geburtstag als Datentyp Date und evtl. ein Status wie "ledig" als Datentyp Boolean.

Deklarieren Sie dazu auf Modulebene einen neuen Datentyp Person (dieser Name ist frei wählbar, darf aber kein VBA-Schlüsselwort sein) wie folgt:

Type Person
Vorname As String
Nachname As String
Geburtstag As Date
Ledig As Boolean
End Type

Jetzt können Sie in Ihren Prozeduren zum Beispiel so darauf zugreifen:

Sub SpeicherePerson()
Dim Kunde As Person
Dim Lieferanten(3) As Person
 
Kunde.Vorname = "Willi"
Kunde.Nachname = "Wichtig"
Kunde.Geburtstag = #12/24/1960#
Kunde.Ledig = True
 
Lieferanten(0).Nachname = "Meier"
Lieferanten(1).Nachname = "Müller"
Lieferanten(2).Nachname = "Schmidt"
Lieferanten(2).Vorname = "Pauline"
Lieferanten(3).Nachname = "Schmitt"
Lieferanten(3).Vorname = "Michael"
End Sub

Wie Sie sehen, lässt sich dieser Datentyp auch als Array benutzen und muss nicht alle Datenwerte gefüllt haben.

Obwohl die Fehlerbehandlung in Office-VBA noch aus dem tiefsten Mittelalter der Programmierung (vielleicht würde "frühe Kreidezeit" das Alter besser beschreiben?) stammt, nämlich mit dem berüchtigten Go To und kaum verkappten Basic-Zeilennummern, die hier als Labels/Text auftauchen, führt manchmal doch kein Weg dran vorbei. Also mutig eine Prozedur erfunden, welche auch einen Laufzeitfehler erzeugt, der dann VBA-gerecht behandelt werden soll:

Option Explicit
 
Dim intA As Integer
Dim intB As Integer
 
Sub FehlerUnbehandelt()
intA = 123456
intB = 0
 
MsgBox "Ergebnis: " & intA / intB
End Sub

Die beiden Integer-Variablen sind auf Modul-Ebene deklariert, damit sie nun nicht in jeder Beispiel-Prozedur erneut deklariert werden müssen. Alle folgenden Prozeduren müssen sich also im gleichen Modul befinden.

Die Prozedur lässt sich zwar mit F5 starten, scheitert aber schon in der intA = 123456-Zeile, weil der höchste zulässige Wert für eine Integer-Variable eben 32.767 und damit hier überschritten ist. Natürlich wäre es einfach, daraus eine Variant-Variable zu machen, um den Laufzeitfehler zu beseitigen. Das ist aber nicht Sinn der Sache, denn wir wollen ja möglichst einfach zu einem Laufzeitfehler kommen, um den Umgang damit zu zeigen.

Das zweite Beispiel aktiviert die Fehlerbehandlung mit On Error GoTo Mist ab dieser Zeile. Tritt danach ein Fehler auf, springt die Code-Ausführung sofort zu der mit dem Label Mist: (Achtung, diesmal mit Doppelpunkt dahinter!) gekennzeichneten Zeile. Damit der Code nicht immer dorthin gerät, muss vorher ein Exit Sub stehen:

Sub FehlerEinfach()
On Error GoTo Mist
intA = 123456
intB = 0
 
MsgBox "Ergebnis: " & intA / intB
Exit Sub
 
Mist:
MsgBox "Fehler Nr. " & Err.Number & ": " & Err.Description
End Sub

Der Fehler ist behebbar, indem einfach der Maximalwert stattdessen genommen und dann das Programm mit Resume Next in der Folgezeile weitergeführt wird:

Sub FehlerEinfach()
' ...Code wie bisher
Mist:
MsgBox "Fehler Nr. " & Err.Number & ": " & Err.Description
intA = 32767
Resume Next
End Sub

Jetzt bleibt allerdings noch ein zweiter Fehler übrig: die Division durch 0 ist nicht erlaubt. Das testen Sie am besten mit F8 zeilenweise, denn ansonsten hängen Sie in einer Endlosschleife: der Divisionsfehler tritt auf, die Variable intA wird korrigiert, es wird unverändert durch intB = 0 dividiert und der Fehler tritt wieder auf. Sie müssen also die Fehler unterschiedlich behandeln, was letztlich zu folgendem Code führt:

Sub FehlerKomplex()
On Error GoTo FehlerA
KorrekturA:
intA = 123456
 
On Error GoTo FehlerB
KorrekturB:
intB = 0
 
On Error GoTo FehlerC
KorrekturC:
MsgBox "Ergebnis: " & intA / intB
Exit Sub
 
FehlerA:
MsgBox "Fehler (A) Nr. " & Err.Number & ": " & Err.Description
intA = 32767
Resume Next
 
FehlerB:
MsgBox "Fehler (B) Nr. " & Err.Number & ": " & Err.Description
Resume KorrekturB
 
FehlerC:
MsgBox "Fehler (C) Nr. " & Err.Number & ": " & Err.Description
intB = 1
Resume KorrekturC
 
End Sub

Nur zur Erinnerung sind die eigentlichen Programmzeilen fett markiert. Aus bescheidenen 3 Zeilen Code sind wegen der Fehlerbehandlung nun 27 Zeilen geworden. Das kann es ja nun wirklich nicht sein! Stellen Sie sich vor, nach diesem Konzept wollen Sie größere Prozeduren behandeln...

Da es leider nicht (wie in modernen Programmiersprachen und in zukünftigen VBA-Versionen übrigens auch) eine try...catch...fail-Anweisung gibt, welche die Fehlerbehandlung blockweise ohne das furchtbare Hin- und Herspringen im Code erledigt, simulieren wir das einfach ein bißchen:

Sub FehlerWiederEinfacher()
On Error Resume Next
 
intA = 123456
If Err.Number <> 0 Then
MsgBox "Fehler (A) Nr. " & Err.Number & ": " & Err.Description
intA = 32767
Err.Clear
End If
 
intB = 0
If Err.Number <> 0 Then
MsgBox "Fehler (B) Nr. " & Err.Number & ": " & Err.Description
Err.Clear
End If
 
Nochmal:
MsgBox "Ergebnis: " & intA / intB
If Err.Number <> 0 Then
MsgBox "Fehler (C) Nr. " & Err.Number & ": " & Err.Description
intB = 1
Err.Clear
GoTo Nochmal
End If
End Sub

Auch hier sind die ursprünglichen Code-Zeilen wieder fett. Auf den ersten Blick sind es natürlich fast ebenso viele Zeilen, aber der Code ist nun gradlinig und springt nicht zwischen Zeilen hin- und her. Meistens können Sie auf diese Fehlerbehandlung sogar noch weitgehender verzichten, wenn es nämlich egal ist, ob es geklappt hat, wie im folgenden Beispiel:

Sub FehlerIstEgal()
'...beliebiger Code...
 
On Error Resume Next
Kill "c:meinPfadmeineDatei.txt"
On Error GoTo 0
 
'...beliebiger Code...
End Sub

Mit On Error Resume Next wird die Fehlerbehandlung ausgeschaltet. Falls die Kill-Zeile scheitert, weil die genannte Datei gar nicht existiert oder aus anderen Gründen nicht gelöscht werden kann, wird das ignoriert. Mit On Error GoTo 0 ist die Original-Fehlerbehandlung von VBA wieder aktiv (und die Zeilennummer 0 aus frühesten Basic-Tagen feiert fröhliche Urständ!).

Vor allem beim Import von Daten aus Textdateien oder dem Internet enthalten die Texte oft überflüssige Leerzeichen. Während es noch recht leicht ist, die Leerzeichen vor oder nach dem Text mit der Trim()-Funktion zu entfernen, berücksichtigt diese keine mehrfachen Leerzeichen mitten im Text. Da müssen Sie selbst etwas programmieren, beispielsweise so:

Function LeerRaus1(strText As String) As String
LeerRaus1 = Replace(Trim(strText), " ", "")
End Function

Da die Trim()-Funktion enorm schnell ist, lohnt es sich, mit dieser erst die äußeren Leerzeichen zu entfernen, bevor die eher langsame Replace()-Funktion eingesetzt wird. Testen Sie das beispielsweise mit folgender Prozedur:

Sub TesteLeerRaus()
Call MsgBox(LeerRaus1( _
"     Hier     sind viele       Lücken     im        Text!        "))
End Sub

Das ist sehr erfolgreich, vielleicht sogar zu erfolgreich, denn es sind nun alle Leerzeichen entfernt: "HiersindvieleLückenimText!". Also brauchen Sie eine Funktion, welche nur mehrfache Leerzeichen entfernt und zwischen den Worten jeweils eines übrig lässt. Die gibt es leider nicht fertig, also folgt der zweite Versuch:

Function LeerRaus2a(strText As String) As String
Dim strAlt As String
Dim intPos As Integer
 
strAlt = Trim(strText)
For intPos = 1 To Len(strAlt)
If Mid(strAlt, intPos, 1) = " " Then
If Right(LeerRaus2a, 1) = " " Then
'war schon Leerzeichen, also nichts tun
Else
LeerRaus2a = LeerRaus2a & Mid(strAlt, intPos, 1)
End If
Else
LeerRaus2a = LeerRaus2a & Mid(strAlt, intPos, 1)
End If
Next
LeerRaus2a = LeerRaus2a
End Function

Dabei wird jeweils ein Buchstabe von der Zeichenkette strAlt in die Zeichenkette LeerRaus2a umkopiert, es sei denn, deren letztes Zeichen ist schon ein Leerzeichen. So kommt maximal ein Leerzeichen dort an. Das funktioniert zwar, liest sich aber doch eher umständlich.

Als Kompromiß lässt sich in der nächsten Funktion auf die Replace()-Funktion zurückgreifen, welches diesmal zwei Leerzeichen gegen eines austauscht. Bei drei Leerzeichen sind dann allerdings noch zwei übrig, so dass dies also so lange wiederholt werden muss, bis keine doppelten Leerzeichen mehr enthalten sind:

Function LeerRaus2b(strText As String) As String
Dim str1 As String
Dim intPos As Integer
 
str1 = Trim(strText)
intPos = InStr(str1, " ")
Do Until intPos = 0
str1 = Replace(str1, " ", " ")
intPos = InStr(str1, " ")
Loop
LeerRaus2b = str1
End Function

Das liefert ebenso ein brauchbares Ergebnis wie LeerRaus2a, ist aber deutlich kompakter geschrieben.

Sobald Ihr VBA-Projekt ein wenig größer wird, braucht es eine vernünftige Bedienoberfläche und damit früher oder später Menüs. Diese lassen sich zwar auch manuell in der jeweiligen Office-Oberfläche mit Extras Anpassen erstellen, aber das ist zum einen eher umständlich und hat zum anderen weitaus weniger Möglichkeiten als die programmgesteuerte Erstellung von Menüs. Dabei sind Symbolleisten und Menüs technisch gesehen identisch, die einen eben mit Symbolen und die anderen textorientiert.

Da mit Office 2007 die "Ribbons" (Registerkarten-Menüs/Symbolleisten) als komplett neues Konzept eingeführt wurden und diese über XML-Dateien gesteuert werden, gilt dieser Tipp nur für alle vorherigen Versionen. Obwohl der Code erfreulicherweise einheitlich in allen Office-Programmen funktioniert, gibt es doch ein paar wenige Unterschiede:

  • Visio ist kleinlich und verlangt Application.Commandbars, während Word, Excel oder Access auch schon mit Commandbars zufrieden wären. Das ist aber im Sinne einer exakten Angabe des Elternobjekts auch sauberer programmiert.
  • Outlook hat die Symbolleisten nicht als Objekt der Application (also der Anwendung im Hintergrund, welche die Daten verwaltet!) eingerichtet, sondern hat diese sinnvollerweise an die sichtbare Oberfläche gebunden, so dass es hier ActiveExplorer.Commandbars heißen muss.
Beginnen wir zuerst damit, überhaupt eine neue Symbolleiste zu erzeugen. Natürlich ist das manuell vergleichsweise wenig Aufwand, soll hier aber der Vollständigkeit halber auch per VBA gemacht werden:

Sub NeueSymbolleiste()
With Application.CommandBars
With .Add("Test")
.Visible = True
End With
End With
End Sub

Damit existiert eine neue, leere Symbolleiste. Wenn Sie diesen Code aber ein zweites Mal aufrufen, wird noch eine gleichnamige Symbolleiste erstellt, daher sollten Sie auch schon vorbereiten, diese Symbolleiste automatisch löschen zu können:

Sub SymbolleisteWeg()
On Error Resume Next
Application.CommandBars("Test").Delete
On Error GoTo 0
End Sub

Die "Fehlerbehandlung" ist notwendig, falls diese Symbolleiste noch gar nicht existiert. Jetzt können wir der Symbolleiste zwei Menütitel hinzufügen, die deshalb vom Typ msoControlPopUp sein müssen. Normale Symbole oder Menüeinträge wären vom Typ msoControlButton, wie Sie weiter unten noch sehen werden. Wenn der Debugger eine undefinierte Variable (nämlich die msoControl...-Konstante) meldet, müssen Sie unter Extras Verweise noch "Microsoft Office nn.0 Object Library" (mit nn als passender Versionsnummer) hinzufügen.

Sub NeueMenuetitel()
With Application.CommandBars("Test")
With .Controls.Add(msoControlPopup)
.Caption = "&Einfach"
End With
 
With .Controls.Add(msoControlPopup)
.Caption = "&Kompliziert"
End With
End With
End Sub

Zu den Menütiteln braucht es anschließend noch die ausklappenden Menüs, die wiederum selber Untermenüs enthalten können. Daher ist die Menüstruktur rekursiv, das heißt an einer Symbolleiste hängen Symbole, an diesen (wenn Sie vom Typ msoControlPopUp sind) wiederum Symbolleisten mit Symbolen (nämlich die Menüeintragsliste). Sollte sich unter den Menüeinträgen ein Untermenü befinden, wiederholt sich diese Konstruktion. Fangen wir daher zuerst mit einem einfachen Menü ohne Schnickschnack an:

Sub NeueMenueeintraegeEinfach()
With Application.CommandBars("Test")
With .Controls("&Einfach")
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&Erster Eintrag"
.OnAction = "SagHallo"
End With  
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&Zweiter Eintrag"
.OnAction = "SagHallo"
End With
End With
End With
End Sub

Die OnAction-Eigenschaft nennt den Namen der Prozedur, die beim Klick auf den Menüeintrag aufgerufen wird. Die muss natürlich vorhanden sein, und zwar schon beim Erstellen, also braucht es diese Prozedur wie folgt:

Sub SagHallo()
With Application.CommandBars.ActionControl
MsgBox "Ich wurde angeklickt: '" & .Caption & "'!"
End With
End Sub

Damit die Prozedur weiß, von wem sie ausgelöst wurde, können Sie auf ActionControl zurückgreifen, welches automatisch das entsprechende Objekt enthält. Hier zeigt es nur die Beschriftung des jeweiligen Menüeintrags an, damit Sie sehen, dass es korrekt arbeitet.

Damit ist die Grundstruktur eines einfachen Menüs fertig und wir können uns auf etwas Komplizierteres stürzen wie ein Menü mit Untermenü oder auf Menüeinträge, die ihren Status ändern:

Sub NeueMenueeintraegeKompliziert()
With Application.CommandBars("Test")
With .Controls("&Kompliziert")
With .CommandBar.Controls.Add(msoControlPopup)
.Caption = "&Auswahl"
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&rot"
.OnAction = "MeldeFarben"
.Tag = "Kirschen und Erdbeeren"
End With
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&gelb"
.OnAction = "MeldeFarben"
.Tag = "Bananen"
End With
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&blau"
.OnAction = "MeldeFarben"
.Tag = "Himmel und Wasser"
End With
End With
 
With .CommandBar.Controls.Add(msoControlButton)
.Caption = "&Umschaltmenue"
.OnAction = "Umschalten"
End With
End With
End With
End Sub

Mit obigem Code wird im Menü "Kompliziert" ein Untermenü namens "Auswahl" eingefügt und an dieses die drei Menüeinträge "rot", "gelb" und "blau" angehängt. Damit diese auch mal etwas anderes machen, als später per MsgBox ihre Beschriftung zu nennen, lassen sich beispielsweise in der jeweiligen Tag-(Marke-)Eigenschaft beliebige Hilfsinformationen speichern, auf welche die Prozedur MeldeFarben später zugreifen kann:

Sub MeldeFarben()
With Application.CommandBars.ActionControl
' das Zeichen _ ist ein Pseudoumbruch,
' eigentlich gehört das folgende in eine Zeile!

MsgBox "Die Farbe '" & Replace(.Caption, "&", "") & _
"' gibt es bei " & .Tag & "."
End With
End Sub

Die Replace-Funktion dient hier nur dazu, das &-Zeichen aus der Menüeintrag-Beschriftung zu entfernen, damit es hübscher aussieht.

Sicherlich kennen Sie in vielen Office-Programmen auch die Menüeinträge, die durch ein Häkchen davor anzeigen, dass irgendetwas aktiviert ist (wie in Word beispielsweise Ansicht Normal). Diese Fähigkeit zum Umschalten bringen Menüeinträge schon mit, allerdings ist die zugehörige StateEigenschaft ärgerlicherweise nicht in der automatisch ausklappenden Methoden- und Eigenschaften-Liste enthalten. Schreiben Sie dazu folgende Prozedur:

Sub Umschalten()
With Application.CommandBars.ActionControl
.State = Not .State
End With
End Sub

Das ist die einfachste Variante, welche mit/ohne Häkchen umschaltet, was Sie erst beim nächsten Ausklappen des Menüs sehen. Offiziell gibt es zur State-Eigenschaft die passenden msoButton...-Konstanten, wie das nächste Beispiel zeigt:

Sub UmschaltenUmstaendlich()
With Application.CommandBars.ActionControl
If .State = msoButtonDown Then
.State = msoButtonUp
MsgBox "Ohne Häkchen!"
Else
.State = msoButtonDown
MsgBox "Mit Häkchen!"
End If
End With
End Sub

Im Echtbetrieb werden Sie natürlich anstatt solcher Meldungen eine passende Aktion auslösen. Diese Beispiele hier zeigen ja auch nur die grundsätzliche Funktionalität. Damit sollten Sie nun in der Lage sein, beliebige Menüs oder Symbolleisten per VBA zu erstellen.

Wenn in einem Formular die ausgewählte Option ermittelt werden soll, ist oft ein Code wie dieser zu finden:

Private Sub CommandButton1_Click()
If Me.OptionButton1.Value = True Then
'mache dieses
ElseIf Me.OptionButton2.Value = True Then
'mache etwas anderes
ElseIf Me.OptionButton3.Value = True Then
'oder das hier
End If
End Sub

Die viel übersichtlichere Select Case-Struktur scheint nicht möglich zu sein, weil hier ja nicht eine Variable, sondern eben viele unterschiedliche Objekt-Eigenschaften geprüft werden müssen. Wenn Sie das Pferd aber von hinten aufzäumen, klappt es dennoch. Prüfen Sie also statt der Objekt-Eigenschaft Value die Konstante True:

Private Sub CommandButton1_Click()
Select Case True
Case Me.OptionButton1.Value
'mache dieses
Case Me.OptionButton2.Value
'mache etwas anderes
Case Me.OptionButton3.Value
'oder das hier
End Select
End Sub

Damit lassen sich die Ergebnisse von Optionsschaltflächen viel übersichtlicher verarbeiten.

Oft ist es hilfreich, für die Argumente in selbstgeschriebenen Prozeduren eine eingeschränkte Auswahlliste vorzugeben, wie das beim Aufruf der vorhandenen VBA-Prozeduren ja auch der Fall ist. Im Beispiel eines Fehlerprotokolls etwa sollen die Fehler in leichte, schwere und sehr schwere Typen unterschieden werden. Normalerweise würden Sie eine Prozedur wie die folgende schreiben:

Sub Fehlerprotokoll(strText As String, bytTyp As Byte)
' ...hier der eigentliche Code...
End Sub

Bei jedem späteren Aufruf im Code schreiben Sie als zweites Argument die Zahlen 0, 1 oder 2 hin. Oder war es doch 1, 2 oder 3? Eventuell -1, 0 oder +1? Oder gar 10, 50, 100? Jetzt hilft Ihnen eine Enumeration: wann immer zukünftig das zweite Argument bytTyp auszufüllen ist, bietet der VBA-Editor eine begrenzte und vor allem sinnvolle Liste ausgesuchter Konstanten nur für diesen Fall an. Schreiben Sie am Anfang des Moduls:

Enum Fehlertyp
Leicht
Schwer
SehrSchwer
End Enum

Dabei ist Enum die Abkürzung für Enumeration, also Aufzählung. Die Definition der obigen Prozedur ändert sich bei:

Sub Fehlerprotokoll(strText As String, meinTyp As Fehlertyp)

Sobald Sie ab jetzt diese Prozedur im Code aufrufen, bietet der Editor automatisch eine Auflistung von eigenen Konstanten an. Damit kennen Sie jederzeit alle jeweils zulässigen Konstanten und erhalten mit wenig Aufwand viel übersichtlicheren Code.

Um interne Variablen mit neuen Inhalten zu füllen oder diese auszulesen, werden Sie typischerweise auf ein Sub/Function-Paar wie im folgenden Code zurückgreifen:

Option Explicit
 
Dim mstrWert As String
 
Sub SchreibeWert(strInhalt As String)
mstrWert = strInhalt
End Sub
 
Function LiesWert() As String
LiesWert = mstrWert
End Function
 
Sub TesteSubFunction()
SchreibeWert "Das ist neu um " & Now()
MsgBox "Inhalt: " & LiesWert()
End Sub

Das mag auf den ersten Blick ja ganz nett aussehen, aber erstens brauchen Sie zwei Namen (nämlich SchreibeWert und LiesWert) und zweitens wäre es gut, wenn der Zugriff auf eine (hier übrigens Modul-öffentliche) Variable auch wie ein Zugriff aussähe. Schreiben Sie das doch einfach als Properties, denn diese können gleiche Namen in Schreiben und Lesen unterscheiden, wie dieser Code zeigt:

Option Explicit
 
Dim mstrWert As String
 
Property Let Wert(strInhalt As String)
mstrWert = strInhalt
End Property
 
Property Get Wert() As String
Wert = mstrWert
End Property
 
Sub TesteProperties()
Wert = "Das ist neu um " & Now() MsgBox "Inhalt: " & Wert
End Sub

Diesmal reicht ein einziger Name Wert zum Lesen und Schreiben und er wird außerdem wie eine Variable behandelt, da ein neuer Inhalt einfach per Gleichheitszeichen zugewiesen wird.

Im Tipp "CustomDocumentProperties schreiben und lesen" finden Sie ein Beispiel, wie Sie einer solchen Property einen (oder bei Bedarf auch mehrere) Parameter mitgeben, in diesem Fall den Namen der benutzerdefinierten Eigenschaft.

Textdateien lesen ID 450 Top-Tipp!

Auch wenn in der jeweiligen Office-Anwendung ein spezialisiertes Dateiformat zur Verfügung steht, gibt es oft genug Anlässe für den direkten Zugriff auf Textdateien. Beispielsweise können Sie damit vor dem eigentlichen Import noch störende Datei-Header aus den Daten entfernen oder einfach Protokolldateien erzeugen, die auch beim Absturz des Programms noch Informationen über die letzten Aktionen enthalten.

Um auf eine Datei zugreifen zu können, müssen Sie zuerst eine Nummer für einen freien Lese-/Schreib-"Kanal" erhalten. Hier wird oftmals einfach eine 1 benutzt, dabei liefert FreeFile sehr einfach eine gültige Nummer. Dann müssen Sie nur noch festlegen, wie Sie auf die Datei zugreifen wollen.

Open "C:DatenTest.txt" For Input As #lngKanal

Ab jetzt können Sie die Datei-Inhalte zeilenweise in eine Variable (hier strDatei) einlesen, etwa bis ein bestimmter Text gefunden wurde:

Sub LiesDatei()
Dim strZeile As String
Dim lngKanal As Long
 
lngKanal = FreeFile()
Open "C:DatenTest.txt" For Input As #lngKanal
 
Do Until EOF(lngKanal)
Input #lngKanal, strZeile
If InStr(strZeile, "Summe") > 0 Then
MsgBox "Gefunden: " & strZeile
Exit Do
End If
Loop
Close lngKanal
End Sub

Textdateien schreiben ID 451 Top-Tipp!

Das Schreiben von Daten in eine Textdatei funktioniert ähnlich wie im Tipp "Textdateien lesen", indem Sie zuerst eine freie Kanal-Nummer zum Schreiben von der Funktion FreeFile erhalten. Damit können Sie einen neuen Kanal zum Schreiben in einen Dateinamen öffnen.

Falls die Datei bereits existierte, wird sie komplett überschrieben. Das können Sie verhindern, indem Sie statt Output die Einstellung Append wählen. Dann erfolgt das Hinzufügen der Zeilen am Ende der bereits vorhandenen Datei:

Sub SchreibDatei()
Dim lngKanal As Long
Dim intZeile As Integer
 
lngKanal = FreeFile()
Open "C:DatenTestSchreiben.txt" For Output As #lngKanal
 
Print #lngKanal, "Hier geht es los"
Print #lngKanal, "" 'für eine Leerzeile
For intZeile = 1 To 5
Print #lngKanal, "Dies ist Zeile " & intZeile
Next
Print #lngKanal, ""
Print #lngKanal, "Das ist die letzte Zeile"
Close lngKanal
End Sub

Bevor Sie, wie beispielsweise im Tipp "Textdateien schreiben" eine vorhandene Datei versehentlich löschen, sollten Sie vorher prüfen, ob diese bereits existiert. Das lässt sich mit einer kleinen Funktion schnell und einfach bewerkstelligen:

Function IstDateiDa(strPfadDatei As String) As Boolean
IstDateiDa = (Len(Dir(strPfadDatei)) > 0)
End Function

Wenn die Datei (inklusive Pfadangabe natürlich) existiert, liefert die Dir-Funktion deren Namen zurück, so dass also die Länge des gelieferten Ergebnisses größer als 0 ist.

Ebenso können Sie mit einer solchen Funktion prüfen, ob ein Verzeichnis vorhanden ist, indem Sie den zweiten Parameter der Dir-Funktion passend einstellen:

Function IstPfadDa(strPfad As String) As Boolean
IstPfadDa = (Len(Dir(strPfad, vbDirectory)) > 0)
End Function

Dabei wird der komplette Pfad gleichzeitig geprüft, es reicht also nicht, dass das Hauptverzeichnis vorhanden ist oder nur das Laufwerk stimmt.

Hinter dem Datum/Zeit-Datentyp verbirgt sich ein so genanntes Serielles Datum, also vor dem Komma die Anzahl der Tage seit einem Startdatum (und nach dem Komma die Anteile an den heutigen 24 Stunden). Das Startdatum ist der Jahreswechsel 1900, aber der erste Tag ist nicht Neujahr 1900!

Mit der folgenden Prozedur können Sie sich anzeigen lassen, an welchem Tag für VBA diese Zeitrechnung beginnt:

Sub TesteDatum()
Debug.Print Format(CDate(0), "dd.mm.yyyy")
End Sub

Sie werden es kaum glauben: die Zeitrechnung startet am 30.12.1899 und nicht Silvester 1899 oder Neujahr 1900! Das bedeutet, daß der 1.1.1900 schon der Tag 2 ist. Daher wird zum Beispiel auch im Tipp "Mehr als 24 Stunden anzeigen" für den Wert 3,99 als Ergebnis der 2.1.1900 angezeigt.

Während man sich hier schon fragt, wer sich diesen merkwürdigen Datumsstart am vorletzten Tag des Jahres ausgedacht hat (trinken die bei Microsoft schon vor Silvester?), ist bei Excel noch eine besonders gefährliche Besonderheit zu beachten: in Tabellen ist das Startdatum einen Tag später!

Geben Sie dort in eine beliebige Tabellenzelle =TEXT(1;"TT.MM.jjjj") als Formel ein und finden als Ergebnis 01.01.1900. Hier ist also tatsächlich Neujahr 1900 auch der erwartete Tag 1. Das funktioniert ebenso, wenn Sie sich statt der Zahl 1 mit =TEXT(A1;"TT.MM.jjjj") auf eine Zelladresse beziehen, welche ein Datum enthält.

Lesen Sie dieselbe Zelle per (Excel-)VBA aus, bedarf es nur einer geringfügigen Änderung am Code:

Sub TesteDatumAusTabelle()
Debug.Print Format(CDate(ActiveSheet.Range("$A$1").Value), "dd.mm.yyyy")
End Sub

Obwohl die Zelle offensichtlich den Wert 01.01.1900 anzeigt, ermittelt VBA nun den 31.12.1899 als Inhalt! Das ist genauso falsch wie Ihre Vermutung, daß der Tag 2 der 2.1.1900 sei, denn dabei handelt es sich schon um den dritten Tag dieser Zeitrechnung.