© Martin Korneffel, Stuttgart 2003 +++ email: Martin.Korneffel@t-online.de +++ web: trac.biz

EXCEL – VBA

Inhalt

1. Objektmanipulationen in Macros aufzeichnen und wiederholen

   1. Macros mit Macrorecorder aufzeichnen

   2. Aufbau eines Makros

   3. Eigenschaften von Objekten in Macros verändern

2. Objektmodell von Excel

   1. Objekt

   2. Objektdiagramm

   3. Objektmodell einer Arbeitmappe (Excel- Datei)

   4. Objektmodell einer Excel- Tabelle

   5. Objektmodell eines Diagrammes

      1. Zugriff auf das aktivierte Diagramm

      2. Deaktivieren eines Diagramms

3. Entwicklungsumgebung

   1. Direktfenster

   2. Projektexplorer

   3. Eigenschaften

   4. Debugger

   5. Objektkatalog

4. Grundlegende Sprachelemente

   1. Literale

   2. Operatoren

   3. Variablen

      1. Deklaration in VBA

   4. VBA- Datentypen

      1. Option Explicit

      2. Option Explicit global einschalten

      3. Typ bestimmen

      4. Zeichenkettenliterale auf Typ prüfen

      5. Variantvariablen und der Wert Empty

      6. Null- Werte

      7. Werte in Formularelementen vom Typ INPUT

      8. Umwandlungsfunktionen/Konvertierungen

   5. Zahlen in Zeichenketten mit Format umwandeln

   6. Verarbeiten von Zeichenketten (Strings)

   7. Datum und Uhrzeit

   8. Arrays

      1. Bereichsgrenzen ermitteln

      2. Bereichsgrenzen mit To

      3. Dynamische Arrays

   9. Assoziative Arrays (Dictionary)

      1. Erzeugen

      2. Elemente hinzufügen

      3. Durchlaufen einer Dictionary

   10. Konstanten

       1. Vordefinierte Konstanten

   11. Gültigkeitsbereiche von Deklarationen

   12. Steuerung der Programmausführung

       1. Bedingte Ausdrücke

       2. Bedingt ausführbarer Programmabschnitt

       3. Bedingt wiederholbarer Programmabschnitt

   13. Fehlerbehandlung

   14. Funktionen und Prozeduren

       1. Sub vs Function

       2. Call by Referenc vs Call by Value

5. Selbstdefinierte Excelfunktionen

6. Makros mit GUI

7. Steuerelemente

   1. TextBox

   2. Listbox

8. Weitere Steuerelemente

9. Formulare

   1. Ereignissteuerung von Formularen

   2. Lebenszyklus eines Formulars

   3. Formulare Anzeigen und Verdecken

   4. Objektmodell der Klasse UserForm

   5. Controls- Collection

   6. Vordefinierte Dialoge

   7. Diagramme

10. Datei IO

    1. Prozeduraler Datei IO

       1. Ablauf des Dateizugriffs

       2. Dateitypen

    2. Objektorientierter Datei IO

       1. Beispiel: Rekursiver Verzeichnisdurchlauf in der Klasse DirTree

       2. Bsp: Anwenden der Klasse DirTree

11. Verarbeiten von XML- Dokumenten mittels VBA und MSXML

Objektmanipulationen in Macros aufzeichnen und wiederholen

Ein Macro (engl. "Zusammenfassung von Befehlen") ist eine Liste von Instruktioen, die Excel- Objekte wie Zellen und Grafiken manipulieren. Macros dienen der Rationalisierung durch Automation sich häufig wiederholender Arbeitsschrittfolgen.

Sub ZelleFormatierenMakro()
' Tastenkombination: Strg+Umschalt+R
'
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Fett"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub

Sub ErgebnisFormatierenMakro()
' Tastenkombination: Strg+Umschalt+R
'
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Fett"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.NumberFormat = "#,##0.00 $"
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Standard"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleDouble
        .ColorIndex = xlAutomatic
    End With    
End Sub

Sub TorteMakro()
' Tastenkombination: Strg+a
'
    Charts.Add
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Sheets("Kopie (1) von FileTypeStatistic"). _
        Range("A2:B33"), PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:= _
        "PieFileTypeStatistic"
End Sub

Macros mit Macrorecorder aufzeichnen

Um Excel- VBA zu erforschen, kann der Macrorecorder genutzt werden. Er erzeugt aus einer Reihe von Benutzerinteraktionen reinrassigen VBA- Code.Der Macrorecorder ist erreichbar unter Extras/Macros/Macrorecorder.

Aufbau eines Makros

In Excel wird ein Makro durch eine Prozedur in VBA realisiert. Eine Prozedur ist ein Block, der eine Liste von VBA- Anweisungen enthält. Eine Prozedur hat einen Namen und kann über diesen Namen zur Ausführung gebracht werden.

Sub Macrobname                                   <- Hier startet der Prozedurblock
  ' Ein Kommentar                                <- Kommentare dienen dem Entwickler als Speichern von Hilfeinformationen für das Verständnis
  <Eine Liste von VBA- Anweisungen>              <- Durch die Anweisungen werden Excel- Objekte gesteuert
End Sub                                          <- Hier endet der Prozedurblock

Eigenschaften von Objekten in Macros verändern

In VBA können die Objekte über spezielle Bezeichner (Namen) angesprochen werden. Die aktuell selektierte Zelle ist z.B. über Selection erreichbar. Eine Zelle hat viele Eigenschaften wie z.B. den eingegebenen Wert oder die Hintergrundfarbe. Diese Eigenschaften werden über den Objektnamen adressiert, indem der Eigenschaftsname, separiert durch einen Punkt an den Objektnamen angehängt wird.

Objektname.Eigenschaftsname

Beispiel:

' Zugriff auf Wert der aktuell selektierten Zelle
Selection.Value

Verändert werden die Eigenschaften indem entweder ihnen neue Werte zugewiesen-, oder auf ihnen bestimme Methoden aufgerufen werden:

' Setzen eines Neuen Werts in einer Zelle
Selection.Value = 99

' Hinzufügen eines neuen Diagramms zur Arbeitsmappe
Charts.Add

Objektmodell von Excel

Objekt

Durch die Einführung des Objektbegriffes in VB können Systeme mit komplexer innerer Struktur direkt beschrieben werden. Jedes Objekt wird mit einem Namen gekennzeichnet, jeder Prozess in einem Objekt durch eine sogenannte Methode ausgedrückt, und jeder Zustand durch eine Eigenschaft dokumentiert. Beziehungen und Prozesse, an denen mehrere Objekte beteiligt sind, können durch Methoden, die Objekte als Parameter erwarten, und Eigenschaften, die wiederum Objekte sind, ausgedrückt werden.

Objektdiagramm

Objektmodell der Excel- Anwendung

Application                              <=> Excel- Anwendung
   |
   +-> OrganizationName                  <=> Name der Firma, für das das Produkt lizensiert wurde
   |
   +-> UserName                          <=> Name des Benutzers, der Excel ausführt
   |
   +-> OperatingSystem                   <=> Betriebssystemversion, unter dem die Anwendung läuft
   |
   +-> ActivePrinter                     <=> Name des aktuell eingestellten Standarddruckers 
   |
   +-> Selection                         <=> Referenz auf selektierten Bereich
   |
   +-> ActiveCell                        <=> Referenz auf aktive Zelle
   |
   +-> ActiveChart                       <=> Referenz auf aktives Diagramm
   | 
   +-> ActiveSheet                       <=> Referenz auf aktives Tabellenblatt
   |
   +-> ActiveWindow                      <=> Referenz auf aktives Fenster
   |
   +-> ActiveWorkbook                    <=> Datei, die IO- aktiv ist
   | 
   +-> ThisWorkbook                      <=> Datei, die momentan
   |                                         ausgeführtes Macro enthält
   |
   +-> ScreenUpdating                    <=> TRUE, dann werden alle Änderungen an Zellen
   |                                               sofort angezeigt
   |                                         FALSE, die Änderungen werden nicht angezeigt
   +-> M: GetOpenFilename([Filter], ...) <=> Zeigt einen "Datei öffnen" Dialog an, 
   |                                         und gibt den Name der zu öffnenden Datei zurück
   |
   +-> M: GetSaveAsFilename(...)         <=> Zeigt einen "Datei speichern unter..." Dialog an,
   |                                         und gibt den Namen der Datei zurück, in die gesichert werden soll.
   |
   +-> Workbooks                         <=> Aufzählung aller geladenener
          |                                  Excel- Dateien. 
          |                                  Zugriff über: Workbooks(Nr) oder
          |                                                Workbooks(„Dateiname“)
          |                                  liefert die Referenz auf ein 
          |                                  Workbook- Objekt.
          |
          +-> M: Add                     <=> neue, leere Arbeitsmappe anlegen
          |
          +-> count                      <=> Anzahl der aktuell geladenen Arbeitsmappen
          |
          +-> M: open „Dateiname“        <=> Vorhandene Excel-Datei laden
          |
          +-> M: Close                   <=> schließen einer Excel- Datei

Objektmodell einer Arbeitmappe (Excel- Datei)

Workbook                                 <=> Arbeitsmappe, entspricht einer 
   |                                         Excel- Datei
   |
   +-> M: Activate                       <=> Datei zur IO- aktiven machen           
   |   
   +-> M: Save                           <=> Arbeitsmappe sichern
   |
   +-> Path                              <=> Verzeichnis, in dem die Arbeitsmappe gepeichert ist
   |
   +-> Sheets                            <=> Aufzählung aller Arbeitsblätter der 
          |                                  Arbeitsmappe
          |                                  Zugriff über: Sheets(Nr) oder
          |                                                Sheets(„Name“)
          |                                  Ein Sheet kann ein Tabellenblatt, ein 
          |                                  Diagramm oder ein Dialog sein.
          |
          +-> M: Add                     <=> neue, leere Arbeitsmappe anlegen
          |
          +-> count                      <=> Anzahl der aktuell geladenen Arbeitsmappen
          |       
          +-> M: Close                   <=> schließen einer Excel- Datei   

Objektmodell einer Excel- Tabelle

Worksheet                                <=> Excel- Tabelle
   |
   +-> M: Activate                       <=> Tabelle zur IO- aktiven machen 
   |
   +-> ChartObjects                      <=> Aufzählung aller Diagrammobjekte in einem Arbeitsblatt
   |     |
   |     +->>ChartObject          
   |
   +-> Cells                             <=> Aufzählung aller Tabellenzellen.
   |                                         Zugriff erfolgt über Zeilen und 
   |                                         Spaltenindizes z.B. Cells(1, 2)
   |                                         liefert Zugriff auf Zelle B2
   |
   +-> Range                             <=> Afzählung aller Tabellenzellen.
        |                                    Zugriff erfolgt über A1- Adressen
        |                                    z.B. Range(„B2:C4“) liefert Aufzählung
        |                                    aller Zellen aus dem Bereich B2:C4,
        |                                    Range("A1") liefert die Zelle A1
        |
        +-> Interior                     <=> Hintergrundfarbe und -Muster einer Zelle
        |
        +-> Borders                      <=> Aufzählung aller Umrandungslinien eines Zellbereiches
        |      |
        |      +-> Line
        |           |
        |           +-> LineStyle        <=> Linienstil einer Umrandungslinie
        |
        +-> Value                        <=> Wert der Zelle
        | 
        +-> Formula                      <=> In Zelle gespeicherte Formel
        |
        +-> HasFormula                   <=> True, wenn Zelle eine Formel enthält
        |
        +-> Font                         <=> Von der Zelle verwendete Schriftart
        |     |
        |     +-> Name                   <=> Name des Schriftfonts
        |     |
        |     +-> FontStyle              <=> Schriftschnitt (z.B. Bold )
        |
        +-> NumberFormat                 <=> Nummerische Formatierung des Zelleninhaltes. Zulässige 
        |                                    Werte sind Zeichenketten wie im Dialog Zellen Formatieren

Objektmodell eines Diagramms

Zugriff auf das aktivierte Diagramm

Das aktivierte Diagramm ist über die Eigenschaft Applicatio.ActiveSheet erreichbar.

Deaktivieren eines Diagramms

Die Deaktivierung eines Diagramms erfolgt über die Eigenschaft Application.ActiveWindow.Visble = False. Die Ursache dafür besteht darin, dass ein Diagramm als OLE- Objekt behandelt wird.

Charts                                  <=> Aufzählung aller Diagramme
   |
   +-> Chart                            <=> Diagramm
        |
        +-> ChartArea                   <=> Hintergrund des gesamten Diagrammes
        |
        +-> PlotArea                    <=> Hintergrund des Zeichnungsbereiches
        |
        +-> Foor                        <=> Boden eines 3D Diagramms
        |
        +-> Walls                       <=> Seitenwände eines 3D Diagramms
        |
        +-> Corners                     <=> Ecken eines 3D Diagramms
        |
        +-> ChartTitle                  <=> Titel
        |
        +-> Legend                      <=> Beschriftung der Datenreihe
        |
        +-> Axes                        <=> Diagrammachsen
        |    |
        |    +->> Axis
        |          |
        |          +-> TickLabels       <=> Markierungen von Achspunkten
        |          |
        |          +-> Gridlines        <=> Orientierungslinien
        |
        +-> SeriesCollection            <=> Datenreihen
             |
             +-> Series
                  |
                  +-> Points            <=> Datenpunkte
                  |     |
                  |     +->> Point
                  |
                  +-> Trendlines        <=> Trend und Ausgleichslinien
                        |
                        +->> Trendlinie  

Entwicklungsumgebung

Direktfenster

Menü: Ansicht/Direktfenster Strg+G

Im Direktfenster können VBA- Anweisungen ausgetestet werden oder Funktionen und Prozeduren ausgeführt werden. So kann eine Messagebox wie folgt gestartet werden:

MsgBox "Hallo, heute ist der " & Now

Projektexplorer

Eigenschaften

Debugger

Objektkatalog

Der Objektkatalog listet alle von Excel aus manipulierbaren Objekte samt ihrer Eigenschaften und Methoden auf.

Grundlegende Sprachelemente

Literale

Operatoren

Variablen

Unsere Programme realisieren Algorithmen, mit denen wir Daten verarbeiten. Um Zugriffe auf die Daten im Computerspeicher zu formulieren, muss uns die Programmiersprache entsprechende Ausdrucksmittel zur Verfügung stellen.

Ein elementares Ausdrucksmittel für Speicherzugriffe sind Variabeln.

Unter Variablen können wir uns benannte Speicherplätze vorstellen. Variabeln sind immer vor ihrer Verwendung im Programmtext mittels einer Deklaration zu vereinbaren. In der Deklaration wird der zu verwendende Name für den Speicherplatz und der Datentyp festgelegt.

Deklaration in VBA

Dim Variablenname As Datentyp

Der Datentyp steht für eine Klasse von Daten. Beispielsweise verbirgt sich hinter dem Datentyp Integer die Menge aller ganzen Zahlen im Intervall [-32768, 32767]. Der Datentyp bestimmt, wie die Informationen als Dualzahlen im Speicher zu verschlüsseln sind, und wieviel Speicherplatz für die Variabel benötigt wird. So werden Variabeln vom Typ Integer vom Dezimal- ins Dualsystem konvertiert, und für sie ein Speicherplatz von 16bit = 2Byte reserviert.

VBA- Datentypen

Im der folgenden Tabelle werden alle elementaren Datentypen der Programmiersprache VBA aufgelistet:

Option Explicit

VB ermöglicht aus Gründen der Abwärtskompatibilität, auf die explizite Deklaration von Variablen zu verzichten. Die Deklaration erfolgt dann implizit durch Zuweisen eines Wertes an einen Variablenamen. Damit werden allerlei exotische Fehler, die sich oft erst zur Laufzeit outen, ermöglicht. Um diesen offensichtlichen Unsinn abzustellen, muss an den Beginn eines jeden Moduls die Compileranweisung

Option Explicit

gesetzt werden.

Option Explicit global einschalten

In der VB- Entwicklungsumgebung kann unter Extras\Optionen\Editor eingestellt werden, dass jedes neu erzeugte Modul am Anfang ein solches Option Explicit gestempelt bekommt. Dazu bei Variablendeklarationen erforderlich einen Haken setzen.

Typ bestimmen

Der aktuelle Typ einer Variable kann mittels der Type- Name Funktion bestimmt werden. Dies ist besonders bei Variantvariablen hilfreich:

x = 123
typ_x = TypeName(x)     ' typ_x = "Integer"

Zeichenkettenliterale auf Typ prüfen

Mit den Prädikaten IsNumeric und IsDate kann getestet werden, ob eine Zeichenkette in einen nummerischen- oder einen Datumswert gewandelt werden kann.

Dim eingabe As Variant, test As Boolean
eingabe = "einundzwanzig"
    
test = IsNumeric(eingabe)
eingabe = "21a"
    
test = IsNumeric(eingabe)
eingabe = "21,1"
    
test = IsNumeric(eingabe)
eingabe = "21#1"
    
test = IsNumeric(eingabe)

'---------------------------------------------------------------- 
eingabe = "2003-01-31"  ' Datumsangabe im ODBC- Format
test = IsDate(eingabe)
    
eingabe = "2003-31-01"  ' Datumsformat im SQL- Server Format
test = IsDate(eingabe)
    
eingabe = "2003-14-07 15:12:00"
test = IsDate(eingabe)
    
eingabe = #7/14/2003 3:12:00 PM#
test = IsDate(eingabe)

Variantvariablen und der Wert Empty

Wurde einer Variantvariable noch kein Wert zugewiesen, dann befindet sich das Typkodefeld in einem Startzustand EMPTY. Dieser Besondere Zustand kann mittels des Prädikates IsEmpty geprüft werden.

Null- Werte

Bei der Datenbank-Programmierung trifft man sehr häufig auf Null-Felder, also Felder, die absolut leer sind. VB kennt für diesen Zustand die Konstante Null, welche nur selten für sinnvolle Operationen benutzt werden kann, im Gegensatz zu Empty, das sich wie ein neutrales Element (bzgl. Addition) verhält.

So ergibt z.B. Null + 5 einfach nur Null. Eine Zuweisung von Null an einen nicht-Variant erzeugt sogar einen Fehler. Mittels des Prädikates IsNull kann auf einen Null- Wert geprüft werden.

Werte in Formularelementen vom Typ INPUT

Input- Formularelemente speichern Werte immer als Zeichenketten.

Umwandlungsfunktionen/Konvertierungen

Übung

Implementieren Sie das Prädikat IsInteger(test as Variant) welches true zurückgibt, wenn test ein Integer- Literal ist, und sonst false.

Zahlen in Zeichenketten mit Format umwandeln

Mittels der Format(Zahl, "Formatstring") Funktion kann ein Zahlenwert in eine Formatierte Zeichenkette umgewandelt werden.

dim ztxt as string

ztxt = Format(1234.5678, "000000,##") 
'ztxt hat jetzt den Wert 001234,57

Verarbeiten von Zeichenketten (Strings)

Zeichenketten können mit den Operatoren + und & zu einer neuen Verbunden werden.

Ü: Romzahl

Datum und Uhrzeit

Datumsangaben werden als Variant- Subtyp Date gespeichert.

Dim dat As Date

Etwas problematisch sind die Literale für Datumsangaben. Sie müssen stets im amerikanischen Format vorliegen (Monat/Tag/Uhrzeit) wobei sie in #-Symbolen geklammert werden

weihnachten = #12/24/2003#

Möchte man lieber das europäische Datumsformat verwenden, dann kann die Cdate- Funktion verwendet werden:

silvester = CDate(31.12.2003 00:00)

Aus einzelnen Partikeln wie Jahr, Monat und Tag kann ein Datumswert mittels der Funktion DateSerial gewonnen werden:

silvester = DateSerial(2003, 12, 31)

Das aktuelle Datum wird mittels der eingebauten Funktion Now bestimmt

dat = Now   ' Bestimmt aktuelles Datum und Uhrzeit

Au einem Datum sind Partikel wie Tag, Monat und Jahr herauslösbar

Dim tag As Integer, monat As Integer, jahr As Integer
    
tag = Day(dat)
monat = Month(dat)
jahr = Year(dat)

Zur Bestimmung von Wochentagnummer und Name gibt es wiederum spezielle Funktionen

' Wochentagnummer und Wochentagname
Dim wtagnr As Integer, wtagname As String
    
wtagnr = Weekday(dat, vbMonday)
wtagname = WeekdayName(wtagnr, , vbMonday)    

Der Zeitraum zwischen zwei Zeitpunkten kann mittels der Funktion DateDiff berechnet werden:

' Seit wieviel Tagen gibt es Unix
Dim unixzeit_in_tagen As Integer
unixzeit_in_tagen = DateDiff("d", DateSerial(1970, 1, 1), Now)

Zeitpunkte in der Zukunftund in der Vergangenheit können mittels DateAdd berechnet werden:

' Berechnen des Fälligkeitsdatums mittels DateAdd
    
Dim faelligkeit As Date
faelligkeit = DateAdd("m", 1, Now)    

Ü: Gbt-Wochentag, Wieviel Tage lebe ich

Arrays

Zur Verarbeitung tabellarischer Daten dienen in VB Arrays. Durch die Deklaration eines Arrays werden im RAM n Speicherplätze zur Aufnahme von Werten des Typs x reserviert.

Deklaration eines Arrays hat folgenden Aufbau:

Dim a(3) as Integer
    | |       +----- Typ der Einträge
    | +------------- größter Index eines Eintrages
    +--------------- Name des Arrays

Nach einer Array- Deklaration ergibt sich folgendes Format im Arbeitsspeicher:

Der Zugriff auf die Elemente eines Arrays erfolgt über den Namen und den Indizes:

Dim x as Integer

' x hat nach Ausführung dieser Anweisung den Wert 2
x = a(2)
    | +--- Indize (Platznummer)
    +----- Arrayname

Die Indizes können dynamisch zur Laufzeit festgelegt werden. Damit ist eine komfortable Verarbeitung der Array- Daten in Schleifen möglich:

Dim i as Integer, s as Long

' s enthält nach der Schleife die Summe aller Einträge
for i = 0 to 3
   s = s + a(i)
next

Ü: Min/Max- Suche, Sortieren

Bereichsgrenzen ermitteln

Der größte und der kleinste Index pro Dimension kann mittels der Funktionen UBound und LBound wie folgt ermittelt werden:

Dim translator(1 To 4, 1 To 2) As String
translator(1, 1) = "unten": translator(1, 2) = "down"
translator(2, 1) = "oben": translator(2, 2) = "up"
translator(3, 1) = "links": translator(3, 2) = "left"
translator(4, 1) = "rechts": translator(4, 2) = "right"
    
Dim zeile As Integer
For zeile = LBound(translator, 1) To UBound(translator, 1)
   For spalte = LBound(translator, 2) To UBound(translator, 2)
      Range("A3").Cells(zeile, spalte).Value = translator(zeile, spalte)
   Next
Next

Bereichsgrenzen mit To

Der Indexbereich eines Arrays kann auch völlig Flexibel mittels To Direktive definiert werden:

Dim Umsatzprognose(5 To 10) as Currency
print LBound(Umsatzprognose) ' =5
print UBound(Umsatzprognose) ' =10

Dynamische Arrays

Die Anzahl der Plätze eines gewöhnlichen Arrays wird bei der Deklaration fest definiert. Wird ein Array mit Daten kontinuierlich gefüllt, dann läuft es irgend wann einmal voll. Sollen weitere Daten im Array abgelegt werden, dann muß es vergrößert werden.

Gewöhnliche Arrays sind nicht vergrößerbar. VB bietet einen zweiten Typ von Arrays, die sog. dynamischen Arrays. Sie werden mit einem unbestimmten max. Index wie folgt deklariert:

dim a() as Integer

Zur Laufzeit können die Dimensionen eines dynamischen Arrays mit der Prozedur ReDim <arrayName>( <neuerMaxIndex>) vergrößert und verkleinert werden wie folgt:

:
ReDim Preserve a(6) ' Das Array wird um 3 Elemente vergrößert, wobei der alte Inhalt erhalten bleibt
:
ReDim a(9) ' Das Array wird wiederum um 3 Elemente vergrößert. Diesmal wird der alte Inhalt jedoch nicht
           ' erhalten (Preserve fehlt)

Ü: Prog. Messwerterfassung (akt. Wert, dyn. Mittelwert)

Assoziative Arrays (Dictionary)

S.124

Erzeugen

Dim telbuch as New Dictionary

Elemente hinzufügen

telbuch.add "Schlüssel-1", wert1
:
telbuch.add "Schlüssel-N", wertN

Durchlaufen einer Dictionary

dim key as String
for each key in telbuch
  MsgBox telbuch(key)
next key

Konstanten

Eine spezielle Form von Variabeln sind Konstanten. Eine Konstante erhält bei der Deklarartion einen Wert zugewiesen. Im weiteren Programmablauf kann der Konstanten kein neuer Wert zugewiesen werden.

Const Name_der_Konstante = Wert 

Beispiel

Const Pi = 3.142

Der Typ der Konstante wird automatisch bei der Zuweisung des Wertes aus diesem bestimmt.

Vordefinierte Konstanten

In VBA gibt es eine Menge vordefinierte Konstanten, z.B. für Farbwerte (z.B. vbRed) oder für Zeilenumbruchszeichen (z.B. vbCrLf). Diese können im Objektexplorer unter der VBA- Bibliothek studiert werden.

Gültigkeitsbereiche von Deklarationen

Public eingabezeile as String  ' Variable in der gesamten Anwendung sichtbar

Private akku as Double         ' Variable nur im Modul1 sichtbar

Private Sub addiere_click()

  Dim wert as Double           ' Variable nur in der Prozedur sichtbar

  wert = Val(eingabezeile)

  akku = akku + wert

End Sub

Private arabzahl as Integer    ' Variable nur in Modul2 sichtbar

Private Sub M_Click()

  arabzahl = arabzahl + 1000   

  eingabezeile = Str$(arabzahl)

End Sub

Steuerung der Programmausführung

In seiner Grundbetriebsart lädt der Computer eine Anweisung nach der anderen aus dem Arbeitsspeicher und führt sie aus. Viele Algorithmen erfordern jedoch, dass einige Abschnitte aus den Anweisungslisten nur unter bestimmten Bedingungen auszuführen sind (z.B. „Wenn bis zum Tag X kein Zahlungseingang erfolgte, dann Mahnung abschicken“) oder wiederholt werden müssen. Um dies zu ermöglichen, verfügt der Computer über spezielle Anweisungen, die den Befehlszählerstand manipulieren. Sie bewirken, dass nach ihrer Ausführung das Programm an einer beliebigen neuen Adresse fortgesetzt wird. Aufgrund dieser Wirkung werden sie Sprunganweisungen genannt.

In VBA sind die Sprunganweisungen in Form von bedingt ausführbaren, oder bedingt wiederholbaren Programmabschnitten implementiert.

Bedingte Ausdrücke

Bedingte Ausdrücke haben die Form

IIF(Bedingung, ExprA, ExprB)

Zuerst wird die Bedingung evaluiert. Ergibt sie den Wert true, dann wird ExprA evaluiert und deren Wert ersetzt den kompletten bedingten Ausdruck. Sonst wird ExprB evaluiert, und deren Wert ersetzt den Ausdruck.

Bedingt ausführbarer Programmabschnitt

Der bedingt ausführbare Programmabschnitt entspricht einer Wenn ... dann Regel.

IF Bedingung Then
   Anweisung 1
   Anweisung 2
       :
   Anweisung n
End IF

Trifft die Bedingung nicht zu, dann kann anschließend mit dem Schlüsselwort Else ein Programmabschnitt als Alternative formuliert werden:

IF Bedingung Then
   Anweisung 1
       :
   Anweisung n
Else
   Anweisung n+1
       :
   Anweisung n+m
End IF

Gibt es mehrere Alternativen, die von Zusatzbedingungen abhängig sind, dann können diese in ElseIf- Blöcken formuliert werden:

IF Bedingung Then
   Anweisung 1
       :
   Anweisung n
ElseIF Bedingung2
   Anweisung n+1
       :
   Anweisung n+m
End IF

Ist die Ausführung mehrerer Programmabschnitte vom Wert eines einzigen Ausdrucks abhängig, dann kann alternativ zu ElseIf eine vereinfachte Schreibweise mittels Select Case Block gewählt werden:

Select Case Ausdruck
Case Wert1
     Anweisung 1
         :
     Anweisung n
Case Wert2
     Anweisung n+1
         :
     Anweisung n+m
Case Wert3
  :
Case Else
     Anweisung 
End Select 

Bedingt wiederholbarer Programmabschnitt

Es handelt sich hierbei um Programmabschnitte, die sooft wiederholt werden, solange eine Bedingung zutrifft (wahr ist). Sie werden gewöhnlich als Schleifen bezeichnet. Zwei elementare Formen der Schleifen werden unterschieden: die abweisende, und die nicht abweisende Schleife.

Bei der abweisenden Schleife wird stets am Anfang des Programmabschnittes geprüft, ob die Bedingung für eine Ausführung des Abschnittes erfüllt ist. Wenn ja, dann wird der Programmabschnitt ausgeführt, und am Ende der Befehlszähler wieder auf die Startadresse des Programmabschnittes gesetzt. Gilt die Bedingung weiterhin, dann erfolgt eine wiederholte Ausführung des Programmabschnittes. Trifft die Bedingung nicht zu, dann wird der Programmabschnitt übersprungen.

DO Bedingung
   Anweisung 1
   Anweisung 2
       :
   Anweisung n
Loop

Bei der nicht abweisenden Schleife wird die Bedingung erst am Ende des Programmabschnittes überprüft. Trifft sie zu, dann wird der Befehlszähler auf den Abschnittsanfang gesetzt, und dieser wiederholt. Sonst wird mit der nachfolgenden Anweisung fortgesetzt.

Da die Bedingung erst am Ende des Abschnittes überprüft wird, wird der Abschnitt immer mindestens einmal ausgeführt.

DO
   Anweisung 1
   Anweisung 2
       :
   Anweisung n
Loop Bedingung

Sind Datenfelder fester Länge zu durchlaufen, oder Berechnung mit einer vorgegebenen Zahl wiederholt auszuführen, dann bietet sich die For...to ... next Schleife an:

FOR i = <Startwert> TO <Endwert> [STEP <Schrittweite>]
   Anweisung 1
   Anweisung 2
       :
   Anweisung n
Next

Zum Durchlaufen aller Felder eines Arrays kann die For Each - Schleife benutzt werden.

Fehlerbehandlung

Fehler werden ignoriert:

On Error Resume Next

Nach Auftreten eines Fehlers wird zu einer Sprungmarke gesprungen, und das Programm dort fortgesetzt:

On Error Goto Sprungmarke

Informationen zum Fehler sind über das Fehlerobjekt Err abrufbar:

Err.Number         ' Fehlernummer
Err.Description    ' Fehlerbeschreibung

Funktionen und Prozeduren

Sub vs Function

In VBA werden Unterprogramme unterschieden nach Prozeduren und Funktionen. Für jeden Typ gibt es eine spezielle Syntax.

Mit Unterprogrammen werden verallgemeinerte Routinen wie z.B. HintergrundAllerSelektierterZellenRotFärben verstanden. Sie kapseln sich häufig wiederholende Programmabschnitte und reduzieren damit die Anzahl an Codezeilen. Bsp:

' Deklaration einer Prozedur
Sub SetBackgroundColorRed(select as Range)
  :
End Sub

' Aufruf einer Prozedur
SetBackgroundColorRed Selection

Funktionen ordnen einer Menge von Argumenten einen Funktionswert zu. In VB kann diese Zuordnung durch einen Spzeialform eines Unterprogrammes, der Function beschrieben werden. Beispiel: Umrechnen einer Wegeinheit in die Basiseinheit Meter:

' Deklaration der Funktion
Function ToMeter(value as Double, unit as String) as Double ' Rückgabewert deklarieren
   :
   ToMeter = wertInMetern
End Function

' Aufruf der Funktion
summeWege = ToMeter(12, "km") + ToMeter(20000, "dm")

Call by Referenc vs Call by Value

Selbstdefinierte Excelfunktionen

Mittels VBA kann die Menge der Excel – Funktionen wie Summe, Mittelwert usw. um selbsdefinierte erweitert werden. Jedoch sind folgende Einschränkungen zu beachten:

1. Die Funktion muss in einem Modul definiert werden. Wird die Funktion in einem Excel- Tabellenobjekt oder einem Formular definiert, dann kann sie nicht verwendet werden

2. Rückgabewert und Parameter müssen vom Typ Variant sein !

Treten in der Funktion Fehler auf, ist Excel nicht sehr kooperativ. Wird die Funktion das erste Mal verwendet, dann sollte der Funktionsassistent aufgerufen werden.

Beispiel:

Function meineMwSt(pNetto, pWg)
    Dim Netto As Currency, Wg As String
    
    If TypeName(pNetto) = "Range" Then
        Netto = pNetto.Cells(1, 1).Value
    Else
        Netto = pNetto
    End If
    
    If TypeName(pWg) = "Range" Then
        Wg = pWg.Cells(1, 1).Value
    Else
        Wg = pWg
    End If
    
        
    If Wg = "A" Or Wg = "a" Then
        meineMwSt = Netto * 1.07
    Else
        meineMwSt = Netto * 1.16
    End If
    
End Function

Makros mit GUI

Mittels der MSForms- Bibliothek bietet Excel einen Satz grafischer Steuerelemente an, mit denen Excel – Makros graphische Benutzeroberflächen verpasst werden können. Die Steuerelemente können direkt in auf einem Tabellenblatt oder auf einem separaten Formular platziert werden. Wer jedoch vermutet, dass es sich in beiden Fällen um dieselben Steuerelemente handelt, irrt: Steuerelemente auf Tabellenblättern haben teilweise andere Eigenschaften als Steuerelemente auf Dialogen !

Steuerelemente

Alle Steuerelemente verfügen über einen gemeinsamen Satz an Methoden und Eigenschaften. Das folgende Objektdiagramm gibt eine Überblick über die wichtigsten:

TextBox

Listbox

Private Sub UserForm_Initialize()

    With ListBox1
        .AddItem ("Gagarin")
        .AddItem ("Amstrong")
        .AddItem ("Taikonaut")
        .MultiSelect = fmMultiSelectSingle
    End With
    
End Sub

Private Sub btnLBoxAuswerten_Click()
    Dim txt As String, i As Integer
    If ListBox1.MultiSelect = fmMultiSelectMulti Then
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                txt = txt + ListBox1.List(i) + vbCrLf
            End If
        Next
    Else
        txt = ListBox1.List(ListBox1.ListIndex)
    End If
    
    TextBox1.Text = txt
    
End Sub

Weitere Steuerelemente

Unter Weitere Steuerelemente kann aus dem großen Angebot von ActiveX- Controls ausgewählt werden. Bsp.: MSCAL- Kalender Steuerelement.

Formulare

Über Formulare kann dem Benutzer eine komfortable Steuerung von Makros ermöglicht werden

Ereignissteuerung von Formularen

Ereignisgesteuerte Programmierung

Lebenszyklus eines Formulars

Formulare Anzeigen und Verdecken

Durch die Methoden show und hide können Formulare angezeigt und wieder verdeckt werden:

Private Sub btnFormularVerdecken_Click()
    UserForm1.Hide
End Sub

Private Sub btnStarteFormular_Click()
    UserForm1.Show (False)
End Sub

Objektmodell der Klasse UserForm

Jedes selbstdefinierte Formular in Excel folgt dem Bauplan der Klasse UserForm. Im Folgenden ein Ausschnitt aus dem Objektmodell der Klasse UserForm.

Controls- Collection

Alle Steuerelemente eines Formulars sind in der Controls- Collection aufgelistet

  Dim elem As Control, i As Integer
    For Each elem In UserForm1.Controls
        elem.ControlTipText = tbxTipText & i
        i = i + 1
    Next

Vordefinierte Dialoge

Dialoge zum Laden und Sichern von Dateien

Mittels komfortabler Dialoge können Dateipfade zum lesen und schreiben geöffnet werden:

Application.GetOpenFilename ("Messdaten (*.csv), *.csv")
Application.GetSaveAsFilename()

Diagramme

' Diagramm löschen
 ChartObjects(1).Delete
        
' neues Diagramm aufbauen
ChartObjects.Add(135, 150, 400, 150).Name = "Plot"
ChartObjects("Plot").Activate
Dim bereich As String
bereich = "B12:B" & (akt_anz_werte + 12)
ActiveChart.ChartWizard Range(bereich), xlLine, , , , , 0

Datei IO

Es gibt zwei Varianten des Zugriffs auf Dateien: IO über die VBA- eigenen Funktionen (prozedural) und über eine COM- Klasse des Betriebssystems (objektorientiert)

Prozeduraler Datei IO

Ablauf des Dateizugriffs

Dateitypen

'nur lesen
Open <Dateiname> For Input As #<DatNr>

'nur schreiben
Open <Dateiname> For Output As #<DatNr>

'Anhängen von Text
Open <Dateiname> For Append As #<DatNr>

'lesen und schreiben
Open <Dateiname> For Random As #<DatNr>

'Zeilenweise einlesen
Dim s as String
Line Input #1, s

'Anz Zeichen lesen (z.B. 3)
s = Input(3, 1)

' N-ten Datensatz lesen
Get #1, N, <Datensatzvariabel>

Print #1, "Auszugebender Text"

'N-ten Datensatz schreiben
Put #1, N, <Datensatzvariabel>

Dim laenge as Integer

laenge = LOF(1)

Dim datenstzende as Boolean

datensatzende = EOF(1)

Dim anz_datensaetze as Integer

anz_datensaetze = LOF(1)/LEN(<datensatz>)

Seek #1, <neue_position>

Dim akt_pos as Integer
akt_pos = Seek(#1)

Objektorientierter Datei IO

Die Com- Komponente, welche eine objektorientierte Schnittstelle für das Dateisystem anbietet, befindet sich in Microsoft Scripting Runtime. Auf diese muss vor Benutzung der Com- Klassen über Extras/Verweise im VBA- Editor verwiesen werden.

Beispiel: Rekursiver Verzeichnisdurchlauf in der Klasse DirTree

Option Explicit
' (c) Martin Korneffel 15.12.2008
' Implementierung eines Dateibaumscanners unter VBA
'---------------------------------------------------------------------------------
' Fehlernummern
Const ErrDirectoryDontExist = 1

'-------------------------------------------------------------------------------------------
' Implementierungsdetails
' Zugreifen auf den Ordner über die ActivX- Komponente Scripting.FileSystemObject
' Voraussetzungen: In Extras/Verweise auf "Microsoft Scripting Runtime" verweisen

Dim mFso As Scripting.FileSystemObject

Dim mCountFiles As Long
Dim mCountDirs As Long
Dim mStopScan As Boolean

'-------------------------------------------------------------------------------------------
' Ereignisse

' Wird zu Begin des Verzeichnisdurchlaufes aufgerufen
Event BeginScanDir(rootPath As String)

' Wird am Ende des Verzeichnisdurchlaufes aufgerufen
Event EndScanDir(rootPath As String)

' Wird beim Betreten eines Unterverzeichnisses aufgerufen
Event EnterDir(path As String, name As String)

' Wird beim Verlassen eines Unterverzeichnisses aufgerufen
Event ExitDir(path As String, name As String)

' Wird beim besuchen einer Datei aufgerufen
Event TouchFile(path As String, name As String)


Private Sub Class_Initialize()
    Set mFso = New Scripting.FileSystemObject
End Sub

' nur Leseeigenschaften für die Statistikdaten eines Dateidurchlaufes
Property Get CountFiles() As Long
    CountFiles = mCountFiles
End Property

Property Get CountDirs() As Long
    CountDirs = mCountDirs
End Property

Property Let StopScan(value As Boolean)
    mStopScan = value
End Property

' Fehlerereignis aus dem Klassenmodul heraus auslösen
Sub Raise(errId As Integer, descr As String)
    With Err
        .Clear
        .Description = descr
        .Number = vbObjectError + 513 + errId
    End With
    Err.Raise Err.Number
End Sub

'--------------------------------------------------------------------------------------

Public Sub scan(rootPath As String)

    ' Reset der Statistik
    mCountDirs = 0
    mCountFiles = 0
    mStopScan = False
    
    ' Start des Durchlaufes
    If Not mFso.FolderExists(rootPath) Then
        Raise ErrDirectoryDontExist, "Das Verzeichnis " & rootPath & " existiert nicht"
        Exit Sub
    End If
    
    RaiseEvent BeginScanDir(rootPath)
    
    Dim folder As Object
    Set folder = mFso.GetFolder(rootPath)
    
    Dim endedWithFailure As Boolean
    endedWithFailure = traverse(rootPath, folder.name)
    
    RaiseEvent EndScanDir(rootPath)
    
End Sub

Function traverse(path As String, name As String) As Boolean
    
    If mStopScan Then
        traverse = False
        Exit Function
    End If
    
    If mFso.FolderExists(path) Then
        
        RaiseEvent EnterDir(path, name)
    
        ' Alle Unterverzeichnisse durchlaufen
        Dim subFolder As Object
        For Each subFolder In mFso.GetFolder(path).SubFolders
            mCountDirs = mCountDirs + 1
            If (Not traverse(subFolder.path, subFolder.name)) Then
                traverse = False
                Exit Function
            End If
        Next
    
        ' Alle Dateien im Unterverzeichnis durchlaufen
        Dim file As Object
        For Each file In mFso.GetFolder(path).files
            mCountFiles = mCountFiles + 1
            RaiseEvent TouchFile(file.path, file.name)
        Next
        
        RaiseEvent ExitDir(path, name)
    
    Else
        
        Raise ErrDirectoryDontExist, "Das Verzeichnis " & path & " existiert nicht"
        traverse = False
        Exit Function
    End If
    
    traverse = True

End Function

Bsp: Anwenden der Klasse DirTree

Option Explicit
' © Martin Korneffel 15.12.2008
' Einsatz der Verzeichnisscannerklasse, die auf FileSystemObjects basiert
'-------------------------------------------------------------------------------------------------

' DirTree- Objektvariable mit Events einführen
Dim WithEvents scanner As DirTree

' Dictionary, mit der die Dateitypen auf den summierten Speicherplatzverbrauch abgebildet werden
Dim dictTypeSumSize As Scripting.Dictionary

Private Sub btnQuit_Click()
    Me.Hide
End Sub

'------------------------------------------------------------------------------------------------
' Eventhandler des Dateiscanners

Private Sub scanner_BeginScanDir(rootPath As String)
    With lbxSizePerTyp
        .Clear
        .AddItem
        ' Spalten des 1. Lisboxeintrages setzen
        .List(0, 0) = "Dateityp"
        .List(0, 1) = "Größe in Bytes"
    End With
    
    ' Dictionary, die den Typen die Summe des Speicherplatzes zuordnet
    Set dictTypeSumSize = New Scripting.Dictionary
    dictTypeSumSize.CompareMode = 0
End Sub

Private Sub scanner_EndScanDir(rootPath As String)
    Dim key
    For Each key In dictTypeSumSize
        With lbxSizePerTyp
            ' Neue Zeile hinzufügen
            .AddItem
            .List(.ListCount - 1, 0) = key
            .List(.ListCount - 1, 1) = dictTypeSumSize.Item(key)
        End With
    Next
End Sub

Private Sub scanner_TouchFile(filename As String, name As String)
    Dim fso As Object
    Set fso = New Scripting.FileSystemObject
    
    Dim file As Object
    Set file = fso.GetFile(filename)
    
    If dictTypeSumSize.Exists(file.Type) Then
        dictTypeSumSize.Item(file.Type) = dictTypeSumSize.Item(file.Type) + file.Size
    Else
        dictTypeSumSize.Add file.Type, file.Size
    End If
    
End Sub

' Konstruktor des UserForms, über welches der Dateiscan gesteuert wird
Private Sub UserForm_Initialize()
    ' Anlegen des Verzeichnisscanner- Objektes (frühe Bindung mittels New ...
    ' Späte Bindung wäre gegeben mittels CreateObject("Scripting.FileSystemObjects")
    Set scanner = New DirTree
    
End Sub

' Eventhandler vom UserForm für den Startbutton des Verzeichnisscans
Private Sub btnStartScan_Click()
    Dim rootPath As String
    
    If IsEmpty(tbxRootPath.value) Then
        MsgBox "Sie müssen eine Dateipfad eingeben"
        Exit Sub
    End If
    
    rootPath = tbxRootPath.value
    
    On Error Resume Next
    scanner.scan (rootPath)
    
    If Err.Number <> 0 Then
        MsgBox "Beim Dateiscann ist ein Fehler aufgetreten: " & Err.Description
        Exit Sub
    End If
    
    tbxCountDirs.value = CStr(scanner.CountDirs)
    tbxCountFiles.value = CStr(scanner.CountFiles)
End Sub

Verarbeiten von XML- Dokumenten mittels VBA und MSXML

Option Explicit
' (C) Martin Korneffel 2008
' Einlesen und analysieren von XML- Dokumenten mittels MSXML
' Verweis auf "Microsoft XML 6.0" muss gesetzt werden

Private Sub btnXmlDocEinlesen_Click()
    
    Dim filename
    filename = Application.GetOpenFilename() ' = Planeten.xml
    If IsEmpty(filename) Then
        MsgBox "Es muss eine xml- Datei ausgewählt werden"
        Exit Sub
    End If
    
    ' Xml- Datei in den Arbeitsspeicher als DOM- Baumstruktur laden
    Dim doc As New MSXML2.DOMDocument60
    
    ' Synchrones laden einschalten
    doc.async = False
    doc.Load ("file://" & filename)
    
    ' Den im Dokument verwendeten Defaultnamespace einem Präfix (my) zuweisen, damit
    ' die XPath- Ausdrücke gebildet werden können
    doc.setProperty "SelectionNamespaces", "xmlns:my=""http://www.tracs.de/Planeten.xsd"""
    
    ' Abfragen der Knoten, deren Elementname = 'Planet' ist
    Dim nodelist As MSXML2.IXMLDOMNodeList
    Set nodelist = doc.selectNodes("//my:Planet")
    
    ' Durchlaufen der gefundenen Knotenmenge
    Dim node As MSXML2.IXMLDOMNode
    Dim zeile As Integer
    zeile = 1
    For Each node In nodelist
        
        With Range("B10")
            ' Elementname ausgeben (= Planet)
            .Cells(zeile, 1).Value = node.nodeName
            
            ' Auslesen der Attribute im Element 'Planet'
            .Cells(zeile, 2).Value = node.Attributes.getNamedItem("name").nodeValue
            .Cells(zeile, 3).Value = "Durchmesser = "
            Dim ndDia As IXMLDOMNode
            Set ndDia = node.selectSingleNode("./my:Durchmesser")
            .Cells(zeile, 4).Value = ndDia.Attributes.getNamedItem("Wert").nodeValue
        End With
        zeile = zeile + 1
    Next

End Sub