dipl. oec. andreas lautenbacher grundlagen definition vba visualmakroprogrammierung mit hilfe von...
TRANSCRIPT
Dipl. oec. Andreas LautenbacherGrundlagen
Definition VBA
Visual Makroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-flächen, etc.
Basic die zugrundeliegende ProgrammierspracheBASIC = Beginners All Symbolic Instruction Code (entwickelt Anfang der 60er Jahre aus der Sprache FORTRAN)
Applications Variante der Programmiersprache Visual Basicspeziell für Anwendungen (hier: EXCEL 97)
Dipl. oec. Andreas LautenbacherGrundlagen
Programmierumgebung VBA
Modulblatt Editor
Direktfenster Debugger (Fehlersuche)
Macrorecorder Aufzeichnen von Quellcode
Objektkatalog Liste aller Objekte, Eigenschaften, Methoden
Makroverwaltung Verwaltung aller geladenen Makros
Add-In Kompilieren und portieren vonProgrammen
Die Programmierumgebung stellt das Entwicklungswerkzeug von VBA- Programmen in EXCEL dar und besteht unter anderem aus folgenden Teilen:
Dipl. oec. Andreas LautenbacherGrundlagen
Modulblatt
Das Modulblatt nimmt den Quellcode zur Steuerung von Programmen auf:
Dipl. oec. Andreas LautenbacherGrundlagen
Optionen Modul-Optionen können über <Extras/Optionen...> gewählt werden.
Dipl. oec. Andreas LautenbacherGrundlagen
Modulblätter einfügen
Module werden über <Einfügen/Modul...> von der VBA-Ebene eingefügt:
Dipl. oec. Andreas LautenbacherGrundlagen
Symbolleisten
Allgemein werden in EXCEL Symbolleisten über den Befehl <Ansicht/Symbolleisten...> aufgerufen:
Dipl. oec. Andreas LautenbacherGrundlagen
Visual-Basic Symbolleisten
Dipl. oec. Andreas LautenbacherGrundlagen
Makrorekorder
Der Makrorecorder ist ein integriertes Werkzeug, um durchgeführte Aktionen und gewählte Befehle aufzuzeichnen.
Von Beginn der Aufzeichnung an werden alle Aktionen des Benutzers registriert und als Visual Basic-Code in einem Modul festgehalten.
Sub Tabellen_Formatierung() Range("A4:C7,A11:C12").Select Range("A11").Activate With Selection.Borders(xlLeft) .Weight = xlThin .ColorIndex = xlAutomatic End With...End Sub
Excel-Aktionen . . . werden aufgezeichnet
Dipl. oec. Andreas LautenbacherGrundlagen
Aufzeichnungsarten
Die Programmierumgebung stellt das Entwicklungswerkzeug von VBA- Programmen in EXCEL dar und besteht unter anderem aus folgenden Teilen:
absolute Aufzeichnung: (Standard)Positionen des Zellzeigers beziehen sich auf absolute Zelladressen,d.h. in der Aufzeichnung werden die genauen Zellbereiche angege-ben, die bei den aufgezeichneten Aktionen gewählt wurden. Bsp.: Range("A4:C7,A11:C12").Select
relative Aufzeichnung: (<Extras/Makro aufzeichnen/Relative Aufzeichnung>)
Positionen des Zellzeigers werden abhängig von der Ursprungszelle aufgezeichnet.Bsp.: ActiveCell.Offset(3, 0).Range("A1:C4,A8:C9").Select
Dipl. oec. Andreas LautenbacherGrundlagen
Makros aufzeichnenMakros werden über <Extras/Makro/Aufzeichnen...> aufgezeichnet:
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (1)
Legen Sie in einer neuen Datei zwei Tabellen nach folgendem Muster an:
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (2)
Teilaufgabe 1:
Zeichnen Sie ein Makro auf, das die erste Tabelle (Makrobeispiel-1) entsprechend Ihren Vorstellungen formatiert. Das Makro soll absolut aufgezeichnet und „Tabellen_Formatierung_1“ genannt werden.
Teilaufgabe 2:
Formatieren Sie nun Ihre Tabelle um und zeichnen Sie die dafür benötigten Schritte relativ auf. Achten Sie darauf, daß der Cursor in Zelle A1 steht und starten Sie erst dann die Aufzeichnung. Ihr Makro heißt „Tabellen_ Formatierung_2“.
Dipl. oec. Andreas LautenbacherGrundlagen
Makros ausführen und verwaltenÜber den Menüpunkt <Extras/Makro/Makros> erhält man eine Liste der momentan geöffneten Makros (abhängig von der geladenen Datei):
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (3)
Teilaufgabe 3:
Wenden Sie die zuvor aufgezeichneten Makros auf die zweite Tabelle (Makrobeispiel-2) an. Achten Sie darauf, daß bei Anwendung der relativen Aufzeichnung der Cursor nicht in Zelle A1 steht, bevor Sie das ent-sprechende Makro starten. Beachten Sie die Unterschiede zwischen beiden Aufzeichnungsarten!
Alternativ zur Startart über <Extras/Makro> kann Ihr Makro auch über den entsprechenden Befehl im Menü <Extras> oder über den angegebenen Shortcut aufgerufen werden.
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (4)
Teilaufgabe 4:
Zeichnen Sie ein Makro auf, das die Kostenarten des Produktes X und die entsprechenden Beträge graphisch darstellt.
Wenden Sie sodann die Aufzeichnung auf die Tabelle „Makrobeispiel-2“, also auf die Preiskalkulation des Produktes Y an!
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (5)
aufgezeichnetes Makro: ausgeführtes Makro:
Kosten Produkt X
Herstellungskosten
Vertriebskosten
sonstige Kosten
außerordentl.Kosten
Kosten Produkt X
Herstellungskosten
Vertriebskosten
sonstige Kosten
außerordentl.Kosten
beachte: Beide Diagramme haben als Titel „Kosten Produkt X“ (=falsch)! Der Makrorecorder hat also nur die veränderten Werte, die durch die zugrundeliegenden Zellbereiche repräsentiert werden übernommen!
Dipl. oec. Andreas LautenbacherGrundlagen
Makro: Aufgabe G1 (6)
Quellcode zur Diagrammgestaltung (abhängig von Diagrammtyp, etc.):
Sub Diagramm_Formatierung_Produkt_X()
Range("A4:A7,C4:C7").Select
Range("C4").Activate
ActiveSheet.ChartObjects.Add(195, 43.2, 240.6, 169.8).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard Source:=Range("A4:A7,C4:C7"), Gallery:=xl3DPie, _
Format:=4, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=0, _
HasLegend:=1, Title:="Kosten Produkt X", CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Add-In (1)
Um Visual Basic-Programme für andere Benutzer zu portieren, bietet sich die komfortable Möglichkeit der Add-Ins an. Ein Add-In ist ein spezielles EXCEL-Format mit der Endung .XLA, das Arbeitsmappen in ein komprimiertes Format umwandelt.
Der in der Ursprungsmappe enthaltene Quellcode wird dabei kompiliert, was die Ausführungsgeschwindigkeit der darin enthaltenen Programme erhöht.
Wichtig: Der Inhalt der Add-Ins kann nicht gelesen und in eine Arbeitsmappe umgewandelt werden. Einmal programmierter Quellcode bleibt also für den Benutzer des Add-Ins unveränderbar!
Dipl. oec. Andreas LautenbacherGrundlagen
Verwaltung von Add-InsVerwaltet werden Add-Ins über den Add-In-Manager (<Extras/Add-In-Manager...>). Alle markierten Add-Ins werden beim EXCEL-Start automatisch geladen und stehen allen Arbeitsmappen zur Verfügung:
Dipl. oec. Andreas LautenbacherGrundlagen
Erstellung von Add-InsErstellt werden Add-Ins über <Datei/Speichern unter / .... / Microsoft Excel-Add-In>.
Dipl. oec. Andreas LautenbacherGrundlagen
Add-In ManagerWurde ein Add-In erstellt, kann es über <Durchsuchen> im Add-In-Manager (im Menü Extras) und Angabe der entsprechenden Datei geladen werden:
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe G2
Aufgabe:
Programmieren Sie ein Makro, das den selektierten Bereich von
Zellen mit einem bestimmten Format versieht (z.B. Hintergrund gelb,
ganzer Rahmen, Währungsdarstellung, etc.) und erstellen Sie dieses
Makro dann als Add-In.
Dipl. oec. Andreas LautenbacherGrundlagen
Nassi-Shneiderman-Diagramme
Erläuterung
Anstelle von Programmablaufplänen nach DIN 66001 werden bei der strukturierten Programmierung sogenannte Struktogramme verwendet. Die bekannteste Darstellungsform ist die von Nassi und Shneiderman erwickelte.
Statt eines linearen Programmierstils fördert eine solche Darstellungsweise eine Top-Down-Entwicklung des gesamten Programms. Die Zerlegung des Programmablaufs erfolgt in sogenannten Strukturblöcken, die weitgehend voneinander unabhängig sind und möglichst auch keine Spezifika der Programmiersprache enthalten sollten. Diese Struktogramme dienen gleichzeitig der Dokumentation des Programms.
Dipl. oec. Andreas LautenbacherGrundlagen
Eigenschaften von Strukturblöcken
Programmbaustein mit eindeutiger Funktion Zusammensetzung aus einem Befehl, mehreren
sukzessiven Befehlen oder aus mehreren nach-geordneten Strukturblöcken
keine Überlappung; d.h. ein anderer Strukturblock ist entweder vollständig in dem betreffenden Strukturblock enthalten oder befindet sich vollständig außerhalb
genau ein Eingang (Eingangskante) genau ein Ausgang (Ausgangskante)
Dipl. oec. Andreas LautenbacherGrundlagen
Sequenz
Anweisung 1
Anweisung 2
Anweisung 3
Dipl. oec. Andreas LautenbacherGrundlagen
Verzweigung
Bedingung
Ja oder TRUE Nein oder FALSE
Anweisung bei erfüllter Bedingung
Anweisung bei nicht erfüllter Bedingung
Dipl. oec. Andreas LautenbacherGrundlagen
Schleifen
abweisende Schleife
nicht abweisende
Schleife
Standardtyp:
Abbruchbedingung
Abbruchbedingung
Schleifenrumpf
Schleifenrumpf
Dipl. oec. Andreas LautenbacherGrundlagen
Case-Struktur
Fall 1
Fall 2
Fall 3
Fall 4
Bedingung
Anweisungenfür Fall 1
Anweisungenfür Fall 2
Anweisungenfür Fall 3
Anweisungenfür Fall 4
Dipl. oec. Andreas LautenbacherGrundlagen
Verfeinerung
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel
Entwicklung eines Programm, das in Abhängigkeit des eingesetzten
Kapitals und der Laufzeit das Endkapital ermittelt.
Die Regeln für die Verzinsung seien wie folgt: 4% bis zu einer Laufzeit von 4 Jahren einschließlich 6% bis zu einer Laufzeit von 9 Jahren einschließlich 8% bei einer Laufzeit von über 9 Jahren
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel-Struktogramm
Eingabe des Kapitals
Eingabe der Laufzeit
Laufzeit <= 4 Jahre
Laufzeit <= 9 JahreJa
Ja
Nein
Nein
Ausgabe des Endkapitals
Berechnung des Endkapitalsaus Kapital und Laufzeit mit ...
Zinssatz = 4% Zinssatz = 6% Zinssatz = 8%
mit ... mit ...
E
V
A
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel-Struktogramm mit VBA-Elementen
Kapital = InputBox("Geben Sie das Kapital ein")
Laufzeit = InputBox("Geben Sie die Laufzeit in Jahren ein")
If Laufzeit <= 4
If Laufzeit <= 9
Then
Then
Else
Else
MsgBox "Das Endkapital beträgt " + Str$(Endkapital) + " DM"
Endkapital = ...Kapital * (1.04) ^ Laufzeit ... * (1.06) ^ ...
= ... = ...
E
V
A... * (1.08) ^ ...
Dipl. oec. Andreas LautenbacherGrundlagen
Nullstellenberechnung mit Regula falsi
a
bc
x
f(x)f(x) besitzt Nullstelle im Intervall[a, b]. Es wird der Nullpunkt cbzgl. der Geraden durch diePunkte (a, f(a)) und (b, f(b)) be-rechnet und das Intervall[a, c] oder [c, b] gewählt, daßdie Nullstelle enthält.
Dieses Intervall ist das neueIntervall [a, b] und der Prozeßwiederholt sich.
Dipl. oec. Andreas LautenbacherGrundlagen
Struktogramm Nullstellenberechnung
Gegeben: stetige Funktion f: und Intervall [a, b] mit f(a)*f(b) < 0
Berechne Nullstelle der Geraden durch (a, f(a)) und (b, f(b))
Diese Nullstelle sei c (mit a < c < b)
f(a) * f(c) < 0 ?
J N
b = c a = c
Wiederhole bis Abbruchkriterium erfüllt (z.B.: b-a klein, Iterations-anzahl groß, f(a) oder f(b) nahe 0, b-c oder c-a klein)
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe Struktogramm
Schreiben Sie ein Makro, das Zellen in einer Tabelle mit einer anzugebenen Zahl multipliziert.
Für die Zellauswahl soll eine Mehrfachauswahl zugelassen werden.
Der Umrechnungsfaktor soll durch eine InputBox abgefragt werden.
Legen sie das Makro als einen Menüpunkt in das EXCEL-Menü Extras.
Schreiben Sie für die Aufgabe zuerst ein Struktogramm.
““Währungsumrechnung”Währungsumrechnung”
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung Aufgabe Struktogramm
Frage Umrechnungsfaktor c ab (InputBox)
Für alle Gebiete (Area) der Auswahl (Selection)
Für alle Zellen (Cells) eines Gebietes
Ist die Zelle eine Zahl? (IsNumeric)
J N
Zellenwert (Value) = Zellenwert * c
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe Durchlaufzeitberechnung
Gegeben seien eine Reihe von Aufträgen, diedie Maschinen M1,... Mn der Reihe nach durch-laufen müssen. Die Gesamtdurchlaufzeit allerAufträge soll berechnet werden.
Diese Durchlaufzeit hängt von der Reihenfolgeab, da zu einer Zeit auf einer Maschine nur einAuftrag ausgeführt werden kann.
Die notwendigen Daten sollen aus der Selek-tion eines rechteckigen Schemas übernommenwerden.
Au
ftra
g
Reihen-folge M1 M2 M3 M4
Durchlaufzeiten
2
3
5
6
1
4
Markierung
Dipl. oec. Andreas LautenbacherGrundlagen
Programmprozeduren
Kontrollstrukturen wie z.B. Schleifen etc. lassen sich mit dem Makro-recorder nicht aufzeichnen. Diese müssen „von Hand“ in sogenannten Prozeduren programmiert werden, in denen auch der Quellcode einer Makro-Aufzeichnung abgelegt wird. Prozeduren sind kleine Programm-einheiten, die in Modulen geschrieben werden.
VBA-Programme unter EXCEL haben im Gegensatz zu Programmier-sprachen wie C keine Hauptroutine, die beim Programmstart aufgerufen wird, sondern bestehen aus einer Vielzahl von Prozeduren, die auf bestimmte Ereignisse (Programmstart, Aktivieren einer Tabelle...) reagieren. VBA ist somit eine ereignisorientierte Sprache.
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurarten
Sub-Prozedur Führt Aktionen aus, liefert aberkeinen Rückgabewert
Funktions-Prozeduren wie Sub-Prozeduren, liefert aber einen Rückgabewert
Eigenschaft-Prozedur Prozedur, die den Wert einer Eigenschaft festlegt/zurückgibt oder einen Bezug auf ein Objekt festlegt
EXCEL unterscheidet grundsätzlich drei Arten von Prozeduren:
Dipl. oec. Andreas LautenbacherGrundlagen
Sub-Prozedur
Syntax:
Sub Prozedurname (Variablenliste)
Anweisung 1
Anweisung 2
...
Anweisung n
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Funktions-Prozedur
Syntax:
Function Funktionsname (Argumentliste)Anweisung 1Anweisung 2 ...Anweisung n
Funktionsname = RückgabewertEnd Function
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurarten
ModulblattModulblatt
ModulblattTabellenblätter
Arbeitsmappe
Funktions-Prozeduren
Sub-Prozeduren
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurarten
Programm: "Hello World"
Sub Hello_World()
Dim Inhalt As StringInhalt = "Hello World"ActiveCell.Formula = Inhalt
End Sub
Das Programm gibt die Zeichenkette „Hello World“ in der aktiven Zelle aus.
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurarten
Funktion: "Addieren"
Function Addieren(x as Single, y as Single)
Addieren = x + y
End Function
Dipl. oec. Andreas LautenbacherGrundlagen
Direktfenster (1)
Das Direktfenster dient der Überprüfung von Prozeduren auf Fehler.
Aufgerufen wird es aus der VBA-Ebene über <Ansicht / Direktfenster>
Dipl. oec. Andreas LautenbacherGrundlagen
Direktfenster
Test von Funktionen:
debug.printdebug.print Funktionsname (Argumentliste) statt debug.print kann auch einfach ein „?“ eingegeben werden
Test von Prozeduren:
callcall Prozedurname (Variablenliste)
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe G3 (1)
Teilaufgabe 1:
Programmieren Sie eine Funktion, die in einem ersten Schritt der
Variablen „Inhalt_1“ die Summe der Argumente x und y zuweist. Im
zweiten Schritt soll die Summe quadriert und der Variablen „Inhalt_2“
zugewiesen werden.
Der dritte Schritt ordnet „Inhalt_3“ das Ergebnis aus „Inhalt_2“ geteilt
durch Argument z zu. Letztlich soll der Wert von „Inhalt_3“ ausgegeben
werden. Nennen Sie Ihre Funktion „Funktionstest“ und überprüfen Sie Ihr
Ergebnis im Testfenster!
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel Direktfenster
Dipl. oec. Andreas LautenbacherGrundlagen
Überwachungsfenster (1)Im Überwachungsfenster lassen sich die Werte von Variablen bei einer schrittweisen Ausführung von Prozeduren überprüfen.
Vorgehensweise:
1. Setzen Sie den Cursor im Codefenster auf die erste Zeile der Funktionsprozedur und aktivieren Sie das Überwachungsfenster <Ansicht/Überwachungsfenster>.
2. Rufen Sie <Überwachung hinzufügen...> im Menü <Testen> auf
Dipl. oec. Andreas LautenbacherGrundlagen
Ausdrucküberwachung im Überwachungsfenster (2)
Vorgehensweise:
3. Fügen Sie nacheinander im Feld <Ausdruck> die Namen der Variablen ein:
Dipl. oec. Andreas LautenbacherGrundlagen
Ausdrucküberwachung im Überwachungsfenster (3)
Vorgehensweise:
4. Fügen Sie vor der ersten Befehlszeile der Prozedur einen Haltepunkt ein. <Testen/Haltepunkt ein>
5. Rufen Sie die Prozedur mit „? Funktionstest (4,6,5)“ im Direktfenster auf. Die Zeile wird durch einen dünnen Rahmen hervorgerufen.
6. Wählen Sie nun <Testen/Einzelschritt>. Alternativ: Taste F8 Die nächste Zeile wird umrahmt, Auswirkungen sind nicht zu sehen
7. Drücken Sie nochmals F8. Die umrahmte Zeile wird ausgeführt und der Wert der Variable „Inhalt_1“ angezeigt. Anschließend wird die nächste Zeile umrahmt.
8. Drücken Sie ein weiteres Mal F8 wird der Inhalt der nächsten Variable angezeigt, etc.
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe G3 (2)
Teilaufgabe 2:
Programmieren Sie eine Sub-Prozedur, die den Namen des in der
Arbeitsmappe momentan aktivierten Blattes im Direktfenster ausgibt.
Sie benötigen dazu:
Debug.PrintDebug.Print
ActiveWorkbook.ActiveSheet.NameActiveWorkbook.ActiveSheet.Name
Weisen Sie den Namen des Blattes der Variablen „Blattname“ zu und
nennen Sie die Prozedur „Blatt_Drucken“
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe G3 (3)
Dipl. oec. Andreas LautenbacherGrundlagen
Online-Hilfe (1)
Eine umfangreiche Online-Hilfe ist für das Erstellen von Programmen
unerläßlich. EXCEL verfügt über eine ausführliche Online-Hilfe zur
Programmiersprache Visual Basic und den unterstützten Objekten,
Methoden und Eigenschaften.
Für eine erfolgreiche Programmierung ist das Erlernen des Umgangs mit
der Online-Hilfe und deren Inanspruchnahme unbedingt zu empfehlen!
Dipl. oec. Andreas LautenbacherGrundlagen
Online-Hilfe (2)
Die vier wichtigsten Arten des Zugriffs auf Schlüsselwörter (über 2000!) in der Visual Basic-Hilfe werden im folgenden erläutert:
Vorgehensweise:
1. Klicken Sie in der Menüleiste auf <? / Inhalt und Index> und danach auf
<Visual Basic mit Excel/Referenz zu Visual Basic> oder aus dem VBA-Editor über <? / Inhalt/Index>
2. Vorgehensweise wie bei 1. und anschließend mit <Suchen> das betreffende Schlüsselwort direkt angeben
3. Cursor im Schlüsselwort auf der Modulebene positionieren und Taste F1 drücken
4. Im VBA-Editor <? / Microsoft Visual Basic Hilfe> eine Frage eingeben
Dipl. oec. Andreas LautenbacherGrundlagen
Variablen und Felder
Definition
Variablen sind Platzhalter für verschiedene Daten und werden gebraucht um während des Programmablaufs Werte vorübergehend zu speichern.
Felder (Array) sind Gruppen von Variablen gleichen Datentyps. Sie werden über Indexnummern angesprochen. Man kann sie mit Matrizen und Vektoren in der Mathematik vergleichen. Ein Vektor ist ein ein-dimensionales Feld, dessen Elemente über Indizes angesprochen werden. Eine Matrix ist entsprechend ein mehrdimensionales Feld.
Dipl. oec. Andreas LautenbacherGrundlagen
Variablen- und Feldnamen
müssen mit einem Buchstaben beginnen
dürfen max. 255 Zeichen lang sein
dürfen keine Leerzeichen, Punkte u. Sonderzeichen außer "_" enthalten.
dürfen Umlaute enthalten
dürfen nicht mit einem Schlüsselwort beginnen
werden nicht nach Groß- u. Kleinschreibung unterschieden
Dipl. oec. Andreas LautenbacherGrundlagen
Datentypen (1)
Datentypen legen die Art der in einer Variablen zu speichernden Daten fest. Typ Symbol Speicherbedarf Wertebereich
Boolean 2 Bytes True oder False
Integer % 2 Bytes -32768 bis 32767
Long & 4 Bytes -2147483648 bis 2147483647
Single ! 4 Bytes -3,402823E38 bis 1,401298E-45 fürnegative Werte und 1,401298E-45 bis3,402823E38 für positive Werte
Double # 8 Bytes -1,79769313486232E308 bis -4,94065645841247E-324 für negativeWerte und 4,94065645841247E-324 bis1,79769313486232E308 für pos. Werte
Dipl. oec. Andreas LautenbacherGrundlagen
Datentypen (2)
Typ Symbol Speicherbedarf Wertebereich
Currency 8 Bytes -922337203685477,5808 bis922337203685477,5807
Date 8 Bytes January 1, 100 bis December 31, 9999
Object 4 Bytes jeder Objektverweis
String $ 1 Bytepro Zeichen
Jedes Zeichen liegt im Bereich von0 bis 255
Variant 16 Bytes und1 Byte proZeichen
Jeder Wert (Default-Datentyp!)
Dipl. oec. Andreas LautenbacherGrundlagen
Datentypen (3)
Deklaration von Variablen
Variablen werden mit der Anweisung DIM Variablenname As Datentyp oder alternativ mit dem Variablennamen, gefolgt von dem Datentypsymbol deklariert (Das Symbol muß bei jedem Variablenaufruf angegeben werden).
Bsp.: Sub Deklaration ()
Dim Länge As Single, Breite As Single
Länge = 3,75
Breite = 4,86
Fläche# = Länge*Breite
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Eigene Datentypen: Verbunde
Verbunde sind Deklarationen von eigenen Datentypen, die aus Kombinationen mehrerer bekannter Datentypen bestehen:
Type Namename1 As Datentypname2 As Datentyp
...End Type Bsp.:
Type artikel artikelname As String preis As CurrencyEnd Type
Dipl. oec. Andreas LautenbacherGrundlagen
Felder mit 0 als unterer Grenze
Deklaration von eindimensionalen Feldern (1)
Dim Feldname (MaximalerIndex) As Datentyp
beachte: Der erste Index ist in VBA immer 0!Der erste Index ist in VBA immer 0!
Bsp.:
Dim x(10) As Integer (es werden 11 ganzzahlige Variablen x(0) ... x(10) deklariert)
Dipl. oec. Andreas LautenbacherGrundlagen
Felder mit definierter unterer Grenze
Deklaration von eindimensionalen Feldern (2)
Dim Feldname (MinimalerIndex To MaximalerIndex) As Datentyp
Bsp.:
Dim x(1 to 10) As Integer (es werden 10 ganzzahlige Variablen x(1) ... x(10) deklariert)
Soll beispielsweise der 5. Variable des Feldes der Wert 100 zugewiesen werden:
x(5) = 100
Dipl. oec. Andreas LautenbacherGrundlagen
mehrdimensionale Felder
Deklaration von mehrdimensionalen Feldern
Dim Feldname (MaxIndexDim1, MaxIndexDim2) As Datentyp
oder
Dim Feldname (MinimalerIndexDim1 To MaximalerIndexDim1; MinimalerIndexDim2 To MaximalerIndexDim2) As Datentyp
Bsp.: Dim x (1 to 10, 1 to 3) As Integer (zweidimensionales
x(1, 1) ... x(1, 3) Datenfeld mit
x(2, 1) ... x(2, 3) 30 Variablen)
... ...
x(10, 1) ... x(10, 3) (spaltenweise linearisiert)
Dipl. oec. Andreas LautenbacherGrundlagen
Deklaration von dynamischen Felder
Deklaration von dynamischen Feldern
Dim Feldname () as Datentyp
(am Anfang des Programms wird ein Feld deklariert)
Redim Feldname (Index)
(steht die Größe des Feldes fest, wird dem bereits deklarierten Feld der entsprechende Index zugewiesen)
Dipl. oec. Andreas LautenbacherGrundlagen
Konstanten
Konstanten sind sinnvolle Namen für Werte, die sich während des Programmablaufs nicht ändern und denen auch kein neuer Wert zugewiesen werden kann:
Const Name = Wert
Bsp.:
Const Pi = 3,1415927
Dipl. oec. Andreas LautenbacherGrundlagen
integrierte Konstanten
VBA enthält eine Reihe von integrierten Konstanten, die als Schlüsselwörter verwendet werden:
Bsp.:
Toolbars(xlClassic1).Visible = True
MsgBox("BlaBlaBla", vbOkOnly)
Dipl. oec. Andreas LautenbacherGrundlagen
Gültigkeitsbereich von Variablen
Variablen können
nur in einer Prozedur
für ein ganzes Modul
für eine ganze Arbeitsmappe
definiert werden
Dipl. oec. Andreas LautenbacherGrundlagen
Gültigkeitsbereich von Variablen
Deklaration innerhalb einer Prozedurinnerhalb einer Prozedur:Sub Deklaration () (innerhalb der Prozedur) Dim Länge as Integer
Deklaration auf Modulebeneauf Modulebene: (am Anfang eines Moduls)Dim Länge as Integer
oder Privat Länge as Integer
Deklaration auf Arbeitsmappenebeneauf Arbeitsmappenebene:Public Länge as Integer (am Anfang eines Moduls)
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel zu Variablen und Konstanten
Sub Symbolleistenrückgabe1() Dim Meldung As String, Stil As Variant If Toolbars(xlClassic1).Visible = True Then Meldung = "Die Symbolleiste Standard ist aktiviert." Stil = vbInformation + vbOKOnly Else Meldung = "Die Symbolleiste Standard ist nicht aktiviert" Stil = vbExclamation + vbOKOnly End If MsgBox Meldung, StilEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
numerische Operatoren
Mit Hilfe von Operatoren können Variable berechnet, verglichen, verknüpft und ausgewertet werden:
numerische Operatoren
+ Addition
- Subtraktion
/ Division
* Multiplikation
^ Potenzierung
\ ganzahlige Division (Rundet vor Division und gibt
einen ganzzahligen Wert zurück: 100,4 \ 26,3 = 3)
MOD (Modulo) ermittelt den Divisionsrest einer
ganzzahligen Division: 100,4 MOD 26,3 = 22
Dipl. oec. Andreas LautenbacherGrundlagen
logische Operatoren
Vergleichsoperatoren
=, <, > >=, <=, und <>(ungleich)
Logische Operatoren (Ausdruck1 Operator Ausdruck2)
And Und (beide Ausdrücke wahr)
Or Oder (mindestens ein Ausdruck wahr)
XOr entweder oder (wahr, wenn beide Ausdrücke un-
gleich sind, sonst falsch)
Äqv Äquivalent (beide wahr oder beide falsch)
Not Nicht (wahr, wenn Ausdruck falsch und umgekehrt)
Imp Immer (immer wahr, außer Ausdruck1 wahr und
Ausdruck2 falsch)
Dipl. oec. Andreas LautenbacherGrundlagen
Verzweigungen: If-Then- Else
If Bedingung Then Anweisung1 Else Anweisung2
oder
If Bedingung Then
Anweisung1
ElseIf Bedingung2 Then Anweisung2
ElseIf Bedingung3 Then Anweisung3
...
Else AnweisungN
End If
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: If
Sub Verzweigungen1()
Dim Eingabe As Variant, Ausgabe As String
Eingabe = InputBox("Bitte ein Zeichen eingeben")
If IsNumeric(Eingabe) Then
Ausgabe = "Das Zeichen ist eine Zahl"
Else
Ausgabe = "Das Zeichen ist keine Zahl"
End If
MsgBox Ausgabe, vbInformation
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Verzweigung: Select-Case
Select Case Variable
Case möglicheVariablenWerte1
Anweisung1
Case möglicheVariablenWerte2
Anweisung2
...
Case Else
AnweisungN
End Select
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Select
Sub Verzweigungen2()
Dim Eingabe As Variant, Ausgabe As String
Eingabe = InputBox("Bitte ein Zeichen eingeben")
Select Case IsNumeric(Eingabe)
Case True
Ausgabe = "Das Zeichen ist eine Zahl"
Case False
Ausgabe = "Das Zeichen ist keine Zahl"
End Select
MsgBox Ausgabe, vbInformation
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K1
Programmieren Sie das Beispiel zu Variablen und Konstanten mit Hilfe der Select-Case-Verzweigung!
Der Name der Sub-Prozedur ist "Symbolleistenrückgabe2"!
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung K1
Sub Symbolleistenrückgabe2()
Dim Meldung As String, Stil As Variant
Select Case Toolbars(xlClassic1).Visible
Case True
Meldung = "Die Symbolleiste Standard ist aktiviert."
Stil = vbInformation + vbOKOnly
Case False
Meldung = "Die Symbolleiste Standard ist nicht aktiviert"
Stil = vbExclamation + vbOKOnly
End Select
MsgBox Meldung, Stil
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Schleifen
Abweisende SchleifenAbweisende Schleifen (Ende-Bedingung wird vor dem
Beginn der Schleife überprüft)
Nichtabweisende SchleifenNichtabweisende Schleifen (Ende-Bedingung wird erst
nach dem Durchlauf der Schleife Überprüft)
EndlosschleifenEndlosschleifen (Haben kein Ende)
Dipl. oec. Andreas LautenbacherGrundlagen
Abweisende Schleifen
For To NextFor To Next (Für Nächste)
For Each NextFor Each Next (Für Alle Nächste)
Do WhileDo While (Durchlaufe Solange)
While WendWhile Wend (Solange Durchlaufe)
Dipl. oec. Andreas LautenbacherGrundlagen
For To Next
For Variable = AnfangsWert To Endwert Step Schrittweite
Anweisung1
Anweisung2
...
Next Variable
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: For - Next
Sub Schleife1()
Dim Fakultät As Variant, Ergebnis As Long
Fakultät = InputBox("Bitte geben Sie eine Zahl ein!")
Ergebnis = 1
For Index = 1 To Fakultät Step 1
Ergebnis = Ergebnis * Index
Next
MsgBox "Die Fakultät der eingegebenen Zahl lautet: " _
+ CStr(Ergebnis), vbInformation
End Sub
‘‘(CStr wandelt einen Ausdruck in den Typ String um!)(CStr wandelt einen Ausdruck in den Typ String um!)
Dipl. oec. Andreas LautenbacherGrundlagen
For Each Next
For Each Objektvariable In Objektliste
Anweisung 1
...
Anweisung n
Next Objektvariable
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: For Each - Next
Sub Schleife2 ()
Dim Index As Object
For Each Index In Toolbars
Index.Visible = False
Next Index
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Do While
Do While Bedingung
Anweisung1
...
AnweisungN
Loop
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Do While
Sub Schleife3()
Antwort = vbRetry
Do While Antwort = vbRetry
Antwort = MsgBox("Was soll das eigentlich?",vbRetryCancel)
Loop
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
While Wend
While Bedingung
Anweisung1
...
AnweisungN
Wend
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K2
Programmieren Sie das Beispiel der Do-While-Schleife in Form der While-Wend-Schleife!
Sub Schleife4()
Antwort = vbRetry
While Antwort = vbRetry
Antwort = MsgBox("Was soll das eigentlich?", vbRetryCancel)
Wend
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Nichtabweisende Schleifen
Do-Loop-While (Durchlaufe solange)
Do-Loop-Until (Durchlaufe bis wahr)
Dipl. oec. Andreas LautenbacherGrundlagen
Do-Loop-While
Der Abbruch erfolgt, wenn die Bedingung nicht mehr erfüllt ist:
Do
Anweisung1
...
AnweisungN
Loop While Bedingung
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K3
Programmieren Sie das Beispiel der Do-While-Schleife in Form der Do-Loop-While-Schleife!
Sub Schleife5()
Antwort = vbRetry
Do
Antwort = MsgBox("Was soll das eigentlich?", vbRetryCancel)
Loop While Antwort = vbRetry
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Do-Loop-Until
Der Abbruch erfolgt, wenn die Bedingung erfüllt ist:
Do
Anweisung1
...
AnweisungN
Loop Until Bedingung
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K4
Programmieren Sie das Beispiel der Do-While-Schleife in Form der Do-Loop-Until-Schleife!
Sub Schleife6()
Antwort = vbRetry
Do
Antwort = MsgBox("Was soll das eigentlich?", vbRetryCancel)
Loop Until Antwort = vbCancel
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Endlosschleife (Do-Loop)
Endlosschleifen sind Schleifen, deren Abbruchbedingung niemals erfüllt ist.
Do
Anweisung
Loop
Dipl. oec. Andreas LautenbacherGrundlagen
Ein- und Ausgabe
Ein und Ausgaben erfogen bei VBA meist durch die bereits oft benutzten Input- bzw. Messageboxen:
Variable = Inputbox (Text, Titel)
Variable = MsgBox (Text, Schaltflächen, Titel)
oder
MsgBox Text, Schaltfläche, Titel
Dipl. oec. Andreas LautenbacherGrundlagen
Formatierung der MsgBox
Konstante Wert BeschreibungvbOkOnly 0 nur OK-ButtonvbOkCancel 1 Ok- und Abbrechen- ButtonvbAbortRetryIgnore 2 Abbrechen/Wiederholen/IgnorierenvbYesNoCancel 3 Ja/Nein/AbbrechenvbYesNo 4 Ja/NeinvbRetryCancel 5 Wiederholen/AbbrechenvbCritical 16 Stop-SymbolvbQuestion 32 FragezeichenvbExclamation 48 AusrufezeichenvbInformation 64 Info-SymbolvbDefaultButton1 0 1.Schaltfläche ist aktiviertvbDefaultButton2 256 2.Schaltfläche ist aktiviertvbDefaultButton3 512 3.Schaltfläche ist aktiviert
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K5 (1)
Programmieren Sie ein Funktionsmakro, das die Einkommens-steuer eines zu versteuernden Betrages ermittelt. Unterschieden werden soll dabei zwischen Einzel- und Zusammenveranlagung.
Hilfestellung:
Es muß die Funktions-Prozedur verwendet werden
Als Argumente wird das Einkommen und die Einkommensart angegeben
Es wird die If-Then-Else- oder alternativ die Select-Case-Ver-zweigung benötigt
Grundlage ist der EStG §32e (Einkommenssteuerrichtliniengesetz)
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K5 (2)
Nach §32a Einkommenssteuertarif (1992) beträgt die Einkommenssteuer für zu versteuernde Einkommen:
bis 5.616 DM (Nullzone):
0 DM
von 5.617 DM bis 8.153 DM (untere Proportionalzone):
0,19 * x - 1.067 DM
von 8.154 DM bis 120.041 DM (Progressionszone):
(151,94 * y + 1.900)* y + 472 DM
ab 120.042 DM (obere Proportionalzone):
0,53 * x - 22.842 DM
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe K5 (3)
wobei x das abgerundete zu versteuernde Einkommen und y ein Zehntausenstel des 8.100 DM übersteigenden Teils des abgerundeten zu versteuernden Einkommens ist. Das zu versteuernde Einkommen ist auf den nächsten, durch 54 ohne Rest teilbaren vollen DM-Betrag abzurunden, wenn es nicht bereits ohne Rest teilbar ist. Der sich ergebende Steuerbetrag ist auf den nächsten vollen Deutsche-Mark-Betrag abzurunden.
Bei Zusammenveranlagung werden die Einkünfte der Ehegatten zusammengerechnet. Die Einkommenssteuer beträgt dann das Zweifache der Einkommenssteuer auf die Hälfte des zusammengerechneten Betrages (Splitting-Verfahren).
Dipl. oec. Andreas LautenbacherGrundlagen
Programmaufbau
Funktionsname und Argumente definieren
Einkommensart = 1 (ledig)
Einkommensart = 2
Ja Nein
Ja Nein
x = RundeAb(EK/54)+54
y = (x-8100)/10000
x = RundeAb(EK/2/54)+54
y = (x-8100)/10000
StruktogrammSteuerfunktion
StruktogrammSteuerfunktion(2xSteuer)
Fehler-meldung
Dipl. oec. Andreas LautenbacherGrundlagen
Struktogramm Steuerfunktion
x <= 5616
x <= 8153
x <= 120041
Ja Nein
Ja
Ja
Nein
NeinEinkommens-steuer = 0 Einkommens-
steuer = 0,19*x - 1067
EKsteuer = (151,94*y + 1900)*y + 472
EKsteuer = 0,53*x - 22842
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung K5 (1)Dim x As Single; y As Single 'Variablendeklaration'Variablendeklaration Function Einkommenssteuer(Einkommen; Einkommensart) 'Anfang der Funktionsprozedur'Anfang der Funktionsprozedur If Einkommensart = 1 Then 'Fall 1: Keine Zusammenveranlagung'Fall 1: Keine Zusammenveranlagung x = Int(Einkommen / 54) * 54 'Zu versteuerndes Einkommen'Zu versteuerndes Einkommen y = (x - 8100) / 10000 'Variable y'Variable y If x <= 5616 Then 'Nullzone'Nullzone Einkommenssteuer = 0 ElseIf x >= 5617 And x <= 8153 Then 'untere Proportionalzone'untere Proportionalzone Einkommenssteuer = Int(0,19 * x - 1067) ElseIf x >= 8154 And x <= 120041 Then 'Progressionszone'Progressionszone Einkommenssteuer = Int((151,94 * y + 1900) * y + 472) Else 'obere Proportionalzone'obere Proportionalzone Einkommenssteuer = Int(0,53 * x - 22842) End If
Die Funktion kann in beliebigen Tabellen aufgerufen werden. Sie wird genauso wie die bereits in EXCEL integrierten Funktionen behandelt.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung K5 (2)
ElseIf Einkommensart = 2 Then '' Fall 2: Zusammenveranlagung(Splitting)Fall 2: Zusammenveranlagung(Splitting) x = Int(Einkommen / 2 / 54) * 54 'Zu versteuerndes Einkommen'Zu versteuerndes Einkommen y = (x - 8100) / 10000 'Variable y'Variable y If x <= 5616 Then 'Nullzone'Nullzone Einkommenssteuer = 0 ElseIf x >= 5617 And x <= 8153 Then 'untere Proportionalzone'untere Proportionalzone Einkommenssteuer = 2 * Int(0,19 * x - 1067) ElseIf x >= 8154 And x <= 120041 Then 'Progressionszone'Progressionszone Einkommenssteuer = 2 * Int((151,94 * y + 1900) * y + 472) Else 'obere Proportionalzone'obere Proportionalzone Einkommenssteuer = 2 * Int(0,53 * x - 22842) End If Else 'Fall 3: Falsche Eingabe des'Fall 3: Falsche Eingabe des
letzten Argumentsletzten Arguments Msg = "Gültige Werte für Einkommensart sind" _ + Chr(10) + "1 (Normal) oder 2 (Splitting)!" MsgBox Msg; 64; "falsches Argument" End IfEnd Function 'Ende der Funktionsprozedur'Ende der Funktionsprozedur
Dipl. oec. Andreas LautenbacherGrundlagen
Definition
Objekte sind Bestandteile von EXCEL auf die mit Hilfe von VBA zugegriffen werden kann. Objekte sind z.B. Worksheet, Chart, Buttons, etc.
Eigenschaften sind Merkmale von Objekten und steuern dessen Aussehen und Verhalten. Eigenschaften sind z.B. RowHeight, ColorIndex, etc.
Methoden sind Aktionen, mit denen Objekte manipuliert werden können. Methoden sind z.B. Copy, Add, etc.
Dipl. oec. Andreas LautenbacherGrundlagen
Objekte
Objekte, Methoden und Eigenschaften sind nicht immer eindeutig voneinander zu unterscheiden. So ist z.B. Range ein Objekt, gleichzeitig aber auch eine Methode.
Faustformel:Faustformel:
Methoden stellen Aktionen dar, die ein Objekt ausführen soll
Eigenschaften haben Werte, die festgelegt oder zurückgegeben werden
Dipl. oec. Andreas LautenbacherGrundlagen
Eigenschaften setzen
Eigenschaften werden wie Variablen benutzt. Sie stellen die Attribute von Objekten dar. Der Wert einer Eigenschaft wird festgelegt durch:
Objekt.Eigenschaft = Ausdruck
Bsp.:
ActiveSheet.Name = "Tabelle1"
Dipl. oec. Andreas LautenbacherGrundlagen
Eigenschaften abfragen
Eigenschaftswerte von Objekten können aber auch zurück-gegeben werden, um den Zustand eines Objekts auszuwerten, bevor es manipuliert wird. Die Rückgabe von Eigenschaftswerten erfolgt durch:
Variable = Objekt.Eigenschaft
Bsp.:
NameDesAktivenBlattes = AvtiveSheet.Name
Dipl. oec. Andreas LautenbacherGrundlagen
Methoden
Methoden können Funktionen oder Kommandos sein, lösen also Aktionen aus oder liefern ein Ergebnis zurück.
Syntax:
Objekt.Methode Argumente
Bsp.:
Application.ActiveWorkbook.Sheets.Add Type:=xlChart
Dipl. oec. Andreas LautenbacherGrundlagen
Objekte (1)
Application.ActiveWorkbook.Sheets.Add Type:=xlCharts
Methode
Unterobjekt =Eigenschaft
Unterobjekt = Methode
Basisobjekt
Argument
Dipl. oec. Andreas LautenbacherGrundlagen
Objekte (2)
Dipl. oec. Andreas LautenbacherGrundlagen
Objekte (Listen)
VBA fast Gruppen von gleichen Objekten in einer Auflistung zusammen. Innerhalb dieser Auflistung kann über den Namen oder Index des Objekts zugegriffen werden.
Auflistung:
Workbooks (ArbeitsmappeListe)
Objekt in der Auflistung:
Workbook (Arbeitsmappe)
Dipl. oec. Andreas LautenbacherGrundlagen
wichtige ObjekttypenWichtige Objekttypen und Objektlistensind:
AnwendungFensterListeFensterBlattListeBlattArbeitsmappenlisteArbeitsmappeTabellenBlattlisteModulListeModulDiagrammListeBereich
ApplicationWindowsWindowSheetsSheetWorkbooksWorkbookWorkSheetsModulesModuleChartsRange
Dipl. oec. Andreas LautenbacherGrundlagen
Objektkatalog - Aufruf
Der Objektkatalog enthält eine Auflistung aller Objekte, Methoden und Eigenschaften. Erreichbar ist er bei aktiviertem Visual Basic-Modul über Ansicht/Objektkatalog oder die Taste F2 oder das Symbol in der Symbolleiste.
Inhalt des Objektkatalogs:
Objekte, Eigenschaften und Methoden von EXCEL
Sprachmerkmale von VBA
Alle Prozeduren der geöffneten Arbeitsmappe
Dipl. oec. Andreas LautenbacherGrundlagen
Objektkatalog - Online-Hilfe
Dipl. oec. Andreas LautenbacherGrundlagen
Zugriff auf Objekte
Zugriff auf Objekte durch Angabe des Namens oder Index:
Sub Zugriff1()
Application.Workbooks("Name des Blattes").Sheets(Index).Activate
End Sub
Bsp.:
Sub Zugriff1()
Application.Workbooks("VBA-Beispiel-1.xls").Sheets(4).Activate
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aktive Objekte
Eine wichtige Zugriffsart ist der Zugriff auf aktive Objekte. Solche Zugriffe werden über das Schlüsselwort Active, gefolgt von der Objektbezeichnung erreicht (ActiveWorkbook, ActiveCell,...)
Bsp.:
Sub Zugriff2()
Application.ActiveWorkbook.Sheets(1).Activate
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Objekte und das Schlüsselwort With
Um die Schreibweise bei Zugriffen auf ein Objekt zu verkürzen, bietet sich die Verwendung von With an. So lassen sich mehrere Eigenschaften und Methoden eines Objektes festlegen, ohne jedesmal die Objektreferenz anzugeben:
With Objekt
.Eigenschaft = Wert
.Methode 1
.Methode 2
End With
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: With
Sub Zugriff3() With Application.ActiveWorkbook.Sheets("Tabelle4") .Activate .Range("A1:C20").Select With .Range("A1:C20") .Formula = "BlaBla" End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Zuweisung von Objekten zu Variablen
Eine weitere Möglichkeit besteht in der Zuweisung von Objektvariablen mit Set, die eine Referenz auf Objekte beinhalten.
Set Referenz = Objekt
Referenz.Eigenschaft = Wert
Referenz.Methode 1
Referenz.Methode 2
...
Beachte: Variablen vom Typ Object müssen immer mit Set zugewiesen werden!
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe O1
Programmieren Sie das vorangegangene Beispiel (Zugriff3) unter Verwendung von Objektvariablen (Set).
Benennen Sie Ihre Prozedur "Zugriff4"
Benennen Sie die Objektvariablen fortlaufend mit Referenz1, Referenz2, etc.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O1
Sub Zugriff4()
Dim Referenz1 as Object, Referenz2 as Object, Referenz3 as Object
Set Referenz1 = Application.ActiveWorkbook.Sheets("Tabelle5")
Referenz1.Activate
Referenz1.Range("A1:C20").Select
Set Referenz2 = Referenz1.Range("A1:C20")
Referenz2.Formula = "BlaBla"
Set Referenz3 = Selection.Interior
Referenz3.ColorIndex = 3
Referenz3.Pattern = xlSolid
Referenz3.PatternColorIndex = xlAutomatic
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Zellreferenzen
Zellen werden über die Methode Range oder Cells angesprochen:
Range ("A1").Value = "Test"
Cells (1, 1).Value = "Test"
Range ("A1:B2").Value = "Test"
Range (Cells (1, 1), Cells(2, 2)).Value = "Test"
allgemein: Cells (Zeilenindex, Spaltenindex)
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Zellreferenzen
Sub Zellreferenz1() Zeilenindex = InputBox("Geben Sie den Wert der Zeile ein:") Spaltenindex = InputBox("Geben Sie den Wert der Spalte ein:") Zeile = CSng(Zeilenindex) Spalte = CSng(Spaltenindex) With Application.ActiveWorkbook.Sheets("Tabelle6") .Activate .Range(Cells(1, 1), Cells(Zeile, Spalte)).Value = "Test" .Range("A1").Select End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Zellreferenzen - Mehrfachauswahl
Mehrfachauswahl mit dem Schlüsselwort Union:
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Mehrfachauswahl mit Zellreferenzen
Sub Zellreferenz2()
Dim Referenz1 As Object, Referenz2 As Object, ReferenzBeide _
As Object
Application.ActiveWorkbook.Sheets("Tabelle7").Activate
Set Referenz1 = Range(Cells(1, 1), Cells(2, 2))
Set Referenz2 = Range(Cells(3, 3), Cells(4, 4))
Set ReferenzBeide = Union(Referenz1, Referenz2)
ReferenzBeide.Value = "Test"
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe O2
Programmieren Sie eine Prozedur, die in einer Zweifach-auswahl das Wort "Test" in der Tabelle 8 ausgibt.
Die Grenzen der beiden Bereiche sollen dabei über Inputboxen abgefragt werden.
Als Eingabedaten sollen jeweils die linke obere Ecke eines Bereichs, sowie die Anzahl der Zeilen und Spalten eines jeden Bereichs abgefragt werden.
Nennen Sie die Subprozedur "Zellreferenz3"
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O2 (1)
Sub Zellreferenz3() Dim Zeile11 As Single, Spalte11 As Single, Zeile12 As Single, _ Spalte12 As Single Dim Zeile21 As Single, Spalte21 As Single, Zeile22 As Single, _ Spalte22 As Single Dim Referenz1 As Object, Referenz2 As Object, _ ReferenzBeide As Object Zeilenindex = _ InputBox("Geben Sie den Wert der linken oberen Zeile des 1. Bereichs ein:") Spaltenindex = _ InputBox("Geben Sie den Wert der linken oberen Spalte des 1. Bereichs ein:") Zeile11 = CSng(Zeilenindex) Spalte11 = CSng(Spaltenindex)
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O2 (2)AnzahlZeilen = InputBox("Anzahl der Zeilen des 1. Bereichs?:") AnzahlSpalten = InputBox("Anzahl der Spalten des 1. Bereichs?:") Zeile12 = CSng(AnzahlZeilen) Spalte12 = CSng(AnzahlSpalten)Zeilenindex = _ InputBox("Geben Sie den Wert der linken oberen Zeile des 2. Bereichs ein:") Spaltenindex = _ InputBox("Geben Sie den Wert der linken oberen Spalte des 2. Bereichs ein:")Zeile21 = CSng(Zeilenindex)Spalte21 = CSng(Spaltenindex)AnzahlZeilen = InputBox("Anzahl der Zeilen des 2. Bereichs?:")AnzahlSpalten = InputBox("Anzahl der Spalten des 2. Bereichs?:")Zeile22 = CSng(AnzahlZeilen)Spalte22 = CSng(AnzahlSpalten)
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O2 (3)
Application.ActiveWorkbook.Sheets("Tabelle8").Activate Set Referenz1 = Range(Cells(Zeile11, Spalte11), _ Cells(Zeile11 + Zeile12 - 1, Spalte11 + Spalte12 - 1)) Set Referenz2 = Range(Cells(Zeile21, Spalte21), _ Cells(Zeile21 + Zeile22 - 1, Spalte21 + Spalte22 - 1)) Set ReferenzBeide = Union(Referenz1, Referenz2) ReferenzBeide.Value = "Test"End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe O3
Programmieren Sie eine Prozedur, die jeder zweiten Zeile und Spalte der Tabelle 9 die Zeilenhöhe bzw. Spaltenbreite 5 zuweist.
Die erste auf die angegebene Zeilenhöhe zu formatierende Zeile soll Zeile 2 sein, die letzte Zeile 200. Entsprechendes gilt für die Spalten.
Sie benötigen zur Referenzierung der Zeilen/Spalten die Objektlisten Rows (ZeileListe) und Columns (SpalteListe).
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O3
Sub Zellreferenz4() Dim Index As Single Application.ActiveWorkbook.Sheets("Tabelle9").Activate For Index = 2 To 200 Step 2 Rows(Index).Select Selection.RowHeight = 5 Next Index For Index = 2 To 200 Step 2 Columns(Index).Select Selection.ColumnWidth = 5 Next Index Range("A1").ActivateEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Autoprozeduren
Eine Auto-Prozedur ist eine Prozedur, die beim Öffnen oder Schließen der Arbeitsmappe, in der sie gespeichert ist aufgerufen wird:
Sub Auto_Open() MsgBox "Willkommen zur Programmierübung!", _ vbExclamation, "Info" Modules(1).ActivateEnd Sub Sub Auto_Close() MsgBox "Bis dann!", vbExclamation, "Info"End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe O4
Programmieren Sie eine Auto-Prozedur, die beim Schließen der Arbeitsmappe alle Symbolleisten ausblendet.
Programmieren Sie eine Auto-Prozedur, die beim Öffnen der Arbeitsmappe die Symbolleiste "Standard" sichtbar macht und in der linken oberen Ecke des Bildschirms plaziert. Sie benötigen dazu die Methode Position und die Konstante xlTop. Anschließend soll das Modulblatt der Arbeitsmappe aktiviert werden.
Speichern Sie dann ihre Arbeitsmappe und Testen Sie Ihr Makro durch Schließen bzw. Öffnen der Arbeitsmappe.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O4
Sub Auto_Open() With Toolbars(xlClassic1) .Visible = True .Position = xlTop End With Modules(1).ActivateEnd Sub Sub Auto_Close() For Each Index In Toolbars Index.Visible = False Next IndexEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Bei-Ereignis Prozeduren
Das Ausführen von Prozeduren kann von dem Eintritt von Ereignissen abhängig gemacht werden. Bei diesen Ereignissen handelt es sich um Aktionen, die durch die EXCEL-Umgebung ausgelöst werden.
Bsp.: Bei der Aktivierung eines Fensters
Nach Ablauf einer Zeitspanne
Bei einer bestimmten Tastenkombination
Dipl. oec. Andreas LautenbacherGrundlagen
Bei-Ereignis Prozeduren - deutsch
OnAction
OnCalculate
OnData
OnDoubleClick
OnEntry
OnKey
OnRepeat
OnSheetActivate
OnSheetDeactivate
OnTime
BeiAktion
BeiBerechnung
BeiDatenerhalt
BeiDoppelklick
BeiEingabe
BeiTaste
BeiWiederholung
BeiBlattaktivierung
BeiBlattdeaktivierung
BeiZeit
Dipl. oec. Andreas LautenbacherGrundlagen
Erstellen von Bei-Ereignis Prozeduren
Erstellen von Bei-Ereignis Prozeduren:
Erstellen Sie eine Prozedur, die die Ereignisbehandlungs-routine, die beim Eintritt des Ereignisses ausgeführt werden soll enthält.
Verwenden Sie in einer anderen Prozedur eine Bei-Ereignis-Anweisung, die dem Ereignis die Ereignisbehandlungsroutine zuweist.
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Bei-Ereignis Prozeduren
Beispiel:
Sub FensterEreignis()
Windows("VBALISTE.XLS").OnWindow = "EreignisAktion"
End Sub
Sub EreignisAktion()
MsgBox "Sie haben das aktive Fenster gewechselt"
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Ausführung von Bei-Ereignis Prozeduren
Ausführung:
Die Bei-Ereignis-Aktion wird aktiviert, indem die Prozedur (FensterEreignis) mit der Taste F5 ausgeführt wird! Erst danach wird bei einem Fensterwechsel die Meldungsbox ausgegeben. Deaktiviert wird die Bei-Ereignis-Aktion durch Umschreiben der Prozedur ("") und Taste F5:
Sub FensterEreignis()
Windows("VBALISTE.XLS").OnWindow = ""
End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Übergabe von Argumenten
Häufig benutzte Anweisungen und Aktionen müssen nicht wiederholt programmiert werden. Eine elegante Möglichkeit besteht darin, Prozeduren oder Funktionen, die den häufiger benutzten Quellcode enthalten bei Bedarf aufzurufen.
Der aufgerufenen Prozedur/Funktion werden dabei Argumente übergeben, mit denen dann Aktionen innerhalb der aufgerufenen Prozedur/Funktion durchgeführt werden.
Im Falle einer Funktion wird das Ergebnis der Aktionen der aufrufenden Prozedur zurückgegeben.
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Übergabe von Argumenten (1)Beispiel-1: Beispiel-1: Aufruf einer FunktionAufruf einer Funktion
'aufrufende Prozedur Sub Eingabe1() Parameter1 = CSng(InputBox("Geben Sie eine Zahl ein:")) Parameter2 = CSng(InputBox("Geben Sie eine weitere Zahl ein:")) Ergebnis = Addition(Parameter1, Parameter2) MsgBox "Das Ergebnis der Addition lautet: " + CStr(Ergebnis)End Sub 'aufgerufene Funktion Function Addition(Parameter1, Parameter2) Addition = Parameter1 + Parameter2End Function
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel: Übergabe von Argumenten (2)Beispiel-2: Aufruf einer Sub-Prozedur (Call) und Funktion
'aufrufende Prozedur Sub Eingabe2() Parameter1 = CSng(InputBox("Geben Sie eine Zahl ein:")) Parameter2 = CSng(InputBox("Geben Sie eine weitere Zahl ein:")) Ergebnis = Addition(Parameter1, Parameter2) Call Ausgabe(Ergebnis)End Sub 'aufgerufene Prozedur Sub Ausgabe(Ergebnis) MsgBox "Das Ergebnis der Addition lautet: " + CStr(Ergebnis)End Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe O5
Versuchen Sie, die Aufgabe der Zweifachauswahl ("Zellreferenz3") mit Hilfe von Funktionsaufrufen und Parameterübergaben soweit wie möglich zu vereinfachen.
Tip:
Die Inputboxen haben fast immer das gleiche Aussehen. Durch geschickte Parameterübergaben an Funktionsprozeduren, die diese Boxen aufrufen, läßt sich der Quellcode erheblich reduzieren. Es müssen in der Inputbox lediglich die Wörter Zeile/Spalte und 1/2 variiert werden.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O5 (1)
Sub Zellreferenz5() ......................Deklarationen............................... Zeile11 = Eingabe("Zeile", 1)
Spalte11 = Eingabe("Spalte", 1)
Zeile12 = EingabeAnzahl("Zeile", 1)
Spalte12 = EingabeAnzahl("Spalte", 1)
Zeile21 = Eingabe("Zeile", 2)
Spalte21 = Eingabe("Spalte", 2)
Zeile22 = EingabeAnzahl("Zeile", 2)
Spalte22 = EingabeAnzahl("Spalte", 2)Fortsetzung
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O5 (2)
Application.ActiveWorkbook.Sheets("Tabelle8").Activate
Set Referenz1 = Range(Cells(Zeile11, Spalte11), Cells(Zeile11 + _
Zeile12 - 1, Spalte11 + Spalte12 - 1))
Set Referenz2 = Range(Cells(Zeile21, Spalte21), Cells(Zeile21 + _
Zeile22 - 1, Spalte21 + Spalte22 - 1))
Set ReferenzBeide = Union(Referenz1, Referenz2)
ReferenzBeide.Value = "Test"
End Sub
Fortsetzung
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung O5 (3)
Function Eingabe(ZeSp As String, Bereich)
Eingabe = CSng(InputBox("Geben Sie den Wert der linken
oberen " + ZeSp + " des " + Bereich + ". Bereichs ein:"))
End Function
Function EingabeAnzahl(Anzahl As String, Bereich)
EingabeAnzahl = CSng(InputBox("Anzahl der " + Anzahl + _
" des " + Bereich + ". Bereichs?:"))
End Function
Dipl. oec. Andreas LautenbacherGrundlagen
Ereignisorientierung
Ein wesentliches Charakteristikum von VBA und EXCEL ist die Ereignisorientierung.
Programmierte Prozeduren können durch Bei-Ereignis-Aktionen ausgeführt werden.
Ereignisse sind aber auch die Betätigung einer Schaltfläche, die Aktivierung eines Menüpunktes, etc.
Dipl. oec. Andreas LautenbacherGrundlagen
Dialogführung
EXCEL bzw. VBA bieten zur individuellen Gestaltung der Benutzeroberfläche und Dialogführung zahlreiche Möglich-keiten an:
Menüleisteneditor
Symbolleisteneditor
Schaltflächen
Dialogelemente
vordefinierte Dialogboxen
selbstdefinierte Dialogboxen
Eigene Online-Hilfe
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Menübefehle (1)
Es ist möglich beliebige Makros durch selbstdefinierte Menübefehle aufzurufen. Hierzu muß zunächst das Menü <Extras/Anpassen> gewählt werden. Dort unter in der Mappe Befehle in den Kategorien der Punkt Makros.
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Menübefehle (2)
Das Menü anklicken, in das der Menübefehl eingefügt werden soll.
Nun im Anpassen-Dialog den Befehl „Menüelement anpassen“ mit der Maus fassen (linke Maustaste gedrückt halten) und das aufgeklappte Menü schieben.
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Menübefehle (3)
Mit der rechten Maustaste den neuen Menübefehl anklicken um das Kontextmenü zu öffnen und unter „Name“ den gewünschten Befehlsnamen eingeben.
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Menübefehle (4)
Dann über „Makro zuweisen“ das gewünschte Makro mit dem Menübefehl verknüpfen. Jetzt können Sie das Menü nutzen.
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Symbolbefehle (1)
Es ist auch möglich Makros durch selbstdefinierte Symbole in vorhandenen Symbolleisten aufzurufen.
Verfahren Sie zunächst wie auf der Folie „Benutzerdefinierte Menübefehle (1)“.
Fassen Sie den Befehl „Schaltfläche anpassen“ und schieben Sie ihn in die Standardsymbolleiste.
Dipl. oec. Andreas LautenbacherGrundlagen
Benutzerdefinierte Symbolbefehle (2)
Rufen Sie das Kontextmenü auf, indem Sie die neue Schaltfläche mit der rechten Maustaste anklicken und verfahren Sie weiter wie bei den Menübefehlen.
Dipl. oec. Andreas LautenbacherGrundlagen
Symbole ändern oder bearbeiten (1)
Es ist möglich das Standardsymbols „Smily“ das zuvor eingefügt wurde durch ein anderes Symbol zu ersetzen oder es zu verändern.
Hierzu wird zunächst der Dialog Anpassen geöffnet und dann das Schaltflächenkontextmenü aufgerufen.
Dipl. oec. Andreas LautenbacherGrundlagen
Symbole ändern oder bearbeiten (2)
In diesem Kontextmenü sind die Befehle „Schaltflächensymbol ändern“ und „Schaltflächensymbol bearbeiten“ enthalten.
• Durch den Befehl „Schaltflächensymbol ändern“ erscheint eine Auswahl an Schaltflächensymbolen, aus der eines durch anklicken gewählt werden kann (siehe vorherige Folie). Dieses Symbol wird dann anstatt des „Smily“ verwendet.
• Durch den Befehl „Schaltflächensymbol bearbeiten“ öffnet sich der Schaltflächen Editor. In ihm kann das Schaltflächensymbol bearbeitet werden. (siehe nächste Folie)
Dipl. oec. Andreas LautenbacherGrundlagen
Symbole ändern oder bearbeiten (3)
Dipl. oec. Andreas LautenbacherGrundlagen
Neue Menüs oder Symbolleisten erstellen (1)
Sie können auch eigene Menüs oder Symbolleisten erstellen.
1. Menüs:
Öffnen Sie den Dialog „Anpassen“ und wählen Sie im Ordner „Befehle“ die Kategorie „Neues Menü“.
Fassen Sie den Befehl „Neues Menü“ und schieben Sie ihn an die gewünschte Stelle in der Menüleiste.
Öffnen sie das Kontextmenü und gehen Sie vor wie bei den bisherigen Kontextmenüs.
Dipl. oec. Andreas LautenbacherGrundlagen
Neue Menüs oder Symbolleisten erstellen (2)
Neues Menü:
Dipl. oec. Andreas LautenbacherGrundlagen
Neue Menüs oder Symbolleisten erstellen (3)
2. Symbolleisten:
Öffnen Sie den Dialog „Anpassen“ und klicken Sie im Ordner „Symbolleisten“ die Schaltfläche „Neu“.
Geben Sie im erscheinenden Dialog der neuen Symbolleiste einen Namen und klicken Sie auf OK.
Nun können Sie durch einfügen von Symbolen Ihre Symbolleiste nutzbar machen.
(siehe Folien „Benutzerdefinierte Symbolbefehle“)
Dipl. oec. Andreas LautenbacherGrundlagen
Neue Menüs oder Symbolleisten erstellen (4)
Neue Symbolleiste:
Dipl. oec. Andreas LautenbacherGrundlagen
Schaltflächen (1)
Schaltflächen stellen die einfachste Möglichkeit dar, VBA-Anwendungen benutzerfreundlich zu gestalten:
Dipl. oec. Andreas LautenbacherGrundlagen
Schaltflächen (2)
Dipl. oec. Andreas LautenbacherGrundlagen
Schaltfläche - Makro zuweisen
Durch Doppelklick auf die Schaltfläche erscheint ein neues Codeblatt in das das gewünschte Makro eingesetzt wird.
Dipl. oec. Andreas LautenbacherGrundlagen
Schaltfläche - Eigenschaften festlegen
beachte:beachte: Um ein Objekt von Zellpositionen abhängig zu machen, muß während des Zeichnens die ALT-Taste gedrückt werden. Die Schältflächengröße richtet sich dann anhand der Gitternetzlinien aus.
Dipl. oec. Andreas LautenbacherGrundlagen
Dialogelemente
Neben Schaltflächen existieren noch zahlreiche andere Dialogelemente (Steuerelemente):
Entwurfsmodus / Eigenschaften
Code anzeigen
Kontrollfeld / Textfeld
Befehlsschaltfläche / Optionsfeld
Listenfeld / Kombinationsfeld
Umschaltfläche / Drehfeld
Bildlaufleiste / Bezeichnungsfeld
Bild / Weitere Steuerelemente
Dipl. oec. Andreas LautenbacherGrundlagen
vordefinierte Dialoge
Vordefinierte Dialoge werden über Konstanten aufgerufen. EXCEL enthält annähernd 200 Dialoge, die über VBA folgendermaßen angezeigt werden:
Sub Dialog1()
Application.Dialogs(xlDialogOpen).Show
End Sub
beachte: Dialogkonstanten beginnen immer mit xlDialog... !
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S1
Programmieren Sie eine Prozedur, die die aktive Arbeits-mappe unter einem anzugebenden Namen speichert.
Die Abfrage des Namens soll über einen Dialog abgefragt werden, der von einer Schaltfläche innerhalb einer Tabelle aufgerufen wird.
Verwenden sie integrierte Dialoge.
Weisen Sie der Schaltfläche die Subprozedur zu.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S1
Dipl. oec. Andreas LautenbacherGrundlagen
selbstdefinierte Dialoge (Userform)
Eigendefinierte Dialoge werden über <Einfügen/Userform> in der VBA-Ebene erstellt. Alternativ kann das Symbol in der Symbolleiste Voreinstellungen verwendet werden.
Dipl. oec. Andreas LautenbacherGrundlagen
selbstdefinierte Dialoge (Userform)
Dipl. oec. Andreas LautenbacherGrundlagen
selbstdefinierte Dialoge - Elemente plazieren
Dipl. oec. Andreas LautenbacherGrundlagen
Aktivierungsreihenfolge festlegen
Die aktivierungsreihenfolge der Elemente des Dialogs kann durch <Ansicht/Aktivierungsreihenfolge> verändert werden.
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurprogrammierung Userform (1)
Um den Dialog nutzen zu können muß natürlich zunächst ein Programm geschrieben werden, das den Dialog öffnet und anschließend je nach Eintrag Befehle ausführt.
Hierzu sollten zunächst alle im Dialog enthaltenen Elemente definiert, d.h. als Objektvariable festgelegt werden. Die Syntax hierfür lautet: Set ObjektVariableName = Objektname
Nun kann mit der Show Methode der Dialog geöffnet werden. Syntax: Objekt.Show (z.B. Userform1.Show)
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurprogrammierung Userform (2)
Dipl. oec. Andreas LautenbacherGrundlagen
Prozedurprogrammierung Userform (3)
Um den Schaltflächen Makros zuzuweisen doppelklicken Sie in der VBA-Ebene auf die entsprechende Schaltfläche und schreiben Sie das gewünschte Programm. (z.B. Fabrikat, Typ und Leistung im Direktfenster ausgeben)
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe D1 (1)
Legen Sie zunächst eine Tabelle mit folgendem Aussehen an und nennen Sie sie „Fahrzeugliste“.
Dipl. oec. Andreas LautenbacherGrundlagen
Audgabe D1 (2)
Schreiben Sie nun das vollständige Programm zum ausfüllen der Tabelle, das die Daten mit dem vorhergehenden Dialog einließt.
Hilfe: Das Programm muß zunächst das Fahrzeuglistenblatt auswählen, dann auf die Zelle in der das Wort Fabrikat steht gehen und schließlich die erste leere Zeile unter dieser Zelle finden. In diese werden dann die Daten eingetragen.
Dazu nötige Befehle: Select, Seleciton.End(xlDown)
Für das Eintragen in die Tabelle kann man teilweise den Makrorekorder nutzen, muß am aufgezeichneten Programm aber kleine Änderungen vornehmen.
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung D1 (1)
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung D1 (2)
Dipl. oec. Andreas LautenbacherGrundlagen
Dialog mit Kombinationsfeld (ComboBox) (1)
Bei der Verwendung von Kombinationsfeldern kann der Inhalt der Liste entweder über einen Bezug auf eine Liste in einem Tabellenblatt oder direkt durch einen Makrobefehl zugewiesen werden.
1. Bezug auf eine Liste im Tabellenblatt:
• Zunächst muß die Liste im Tabellenblatt erstellt werden
• Wechsel auf die VBA-Ebene
• Tragen Sie den Listenbereich unter den Eigenschaften der ComboBox unter Row Source ein
• Jetzt ist der zugrif wie auf Textfelder möglich
2. Benutzung des Befehls Objekt.List (z.B. Fabrikat.List)
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel Listenbezug (1):
Statt des Textfeldes für das Fabrikat verwende man ein Kombinationsfeld. Im Programm muß statt TextBox1 jetzt ComboBox1 stehen.
Herstellerliste:
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel Listenbezug (2):
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel Listenbezug (3):
Dipl. oec. Andreas LautenbacherGrundlagen
Beispiel Direktbefehl:
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S2 (1)
Es soll ein Makro programmiert werden, das einen vollständigen Abschreibungsplan erstellt.
Erläuterungen:
Der Abschreibungsplan soll auf einem neu einzufügenden Tabellenblatt erstellt werden.
Als Abschreibungsmethoden sollen entweder die lineare, die geometrisch-degressive, die arithmetisch-degressive Abschreibungs-methode, oder aber alle Methoden nebeneinander verwendet werden.
Alle notwendigen Daten (Anschaffungswert, Restwert, etc.) sollen über einen selbsterstellten Dialog abgerufen werden.
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S2 (2)
Die Funktionen für die einzelnen Abschreibungsmethoden lauten:
lineare AfA: LIA (SLN)
geom. degressive AfA: GDA (DDB)
arithm. degressive AfA: DIA (SYD)
Funktionen werden folgendermaßen in eine Zelle eingefügt (Bsp.):
Range("A1").Formula = " = LIA(10000, 1000, 5)"
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S2 (3)Das Makro soll über das Menü <Extras> in der Menüleiste oder alternativ über ein selbst zu definierendes Icon aufgerufen werden können.
HinweisHinweis:
Alle sonstigen Informationen über Objekte, auf diese anzuwendende Methoden, etc. sind der Online-Hilfe zu entnehmen.
Die Schlüsselwörter sind anschließend mit Hilfe der Datei "VBALISTE.XLS" zu übersetzen.
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S2 - Ergebnis (1)
Dipl. oec. Andreas LautenbacherGrundlagen
Aufgabe S2 - Ergebnis (2)
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (1)
'Anzeige des Dialogblattes'Anzeige des Dialogblattes
Sub DialogZeigen() Application.DialogSheets(1).ShowEnd Sub
'Initialisierung des Dialogs'Initialisierung des Dialogs
Sub Dialogfeldrahmen1_OnSheetActivate() With Application.DialogSheets(1) For Each Index In .EditBoxes Index.Text = "" Next Index For Each Index In .OptionButtons Index.Value = xlOff Next Index For Each Index In .Spinners Index.Value = 0 Next Index End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (2)'Verknüpfung von Drehfeldern und Bearbeitungsfeldern'Verknüpfung von Drehfeldern und Bearbeitungsfeldern Sub Drehfeld1_OnClick() With Application.DialogSheets(1) .EditBoxes(3).Text = .Spinners(1).Value End WithEnd Sub Sub Drehfeld2_OnClick() With Application.DialogSheets(1) .EditBoxes(4).Text = .Spinners(2).Value End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (3)'Verknüpfung von Drehfeldern und Bearbeitungsfeldern'Verknüpfung von Drehfeldern und Bearbeitungsfeldern Sub Nutzung_OnAction() With Application.DialogSheets(1) .Spinners(1).Value = .EditBoxes(3).Text End WithEnd Sub
Sub Satz_OnAction() With Application.DialogSheets(1) .Spinners(2).Value = .EditBoxes(4).Text End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (4)'Eigentliche Prozedur zu Erstellung des Abschreibungsplans _'Eigentliche Prozedur zu Erstellung des Abschreibungsplans _ auf einem neuen Tabellenblattauf einem neuen Tabellenblatt Sub Abschreibungsplan_aufstellen() ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Sheets(1) With ActiveSheet .Name = Application.DialogSheets(1).EditBoxes(5).Text 'Beschriftung des Tabellenblattes For Index = 1 To 3 .Cells(Index, 1).Formula = Application.DialogSheets(1). _ Labels(Index).Text Next Index .Range("A6").Formula = "Jahr:" Columns("A:A").EntireColumn.AutoFit For Index = 1 To 3 .Cells(Index, 2).Formula = DialogSheets(1) _ .EditBoxes(Index).Text Next Index
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (5)
'Abfrage der Abschreibungsart nach Einstellung der _'Abfrage der Abschreibungsart nach Einstellung der _ Optionsfelder und Aufruf der entsprechenden ProzedurenOptionsfelder und Aufruf der entsprechenden Prozeduren If Application.DialogSheets(1).OptionButtons(1) _ .Value = xlOn Then Call LineareAfA ElseIf Application.DialogSheets(1).OptionButtons(2) _ .Value = xlOn Then Call Degressiv1 ElseIf Application.DialogSheets(1).OptionButtons(3) _ .Value = xlOn Then Call Degressiv2 Else Call AlleAfA End If End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (6)
'linearer AfA-Plan'linearer AfA-Plan
Sub LineareAfA() With ActiveSheet .Range("B6").Formula = "Lineare AfA" Ende = CSng(Application.DialogSheets(1).EditBoxes(3).Text) For i = 1 To Ende .Cells(6 + i, 1).Formula = i .Cells(6 + i, 2).FormulaR1C1 = "=SLN(R1C2,R2C2,R3C2)" Next i Columns("B:B").EntireColumn.AutoFit End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (7)
'geometrisch degressiver AfA-Plan'geometrisch degressiver AfA-Plan
Sub Degressiv1() With ActiveSheet .Range("B6").Formula = "Degressive AfA" .Range("A4").Formula = "Satz (%)" .Range("B4").Formula = CSng(Application.DialogSheets(1) _ .EditBoxes(4).Text) 'Faktor(Prozent*n/100) .Range("C4").Formula = "=B4*B3/100" Ende = CSng(Application.DialogSheets(1).EditBoxes(3).Text) For i = 1 To Ende .Cells(6 + i, 1).Formula = i .Cells(6 + i, 2).FormulaR1C1 = _ "=DDB(R1C2,R2C2,R3C2,RC[-1],R4C3)" Next i Columns("B:B").EntireColumn.AutoFit End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (8)
'arithmetisch degressiver AfA-Plan'arithmetisch degressiver AfA-Plan
Sub Degressiv2() With ActiveSheet .Range("B6").Formula = "Digitale AfA" Ende = CSng(Application.DialogSheets(1).EditBoxes(3).Text) For i = 1 To Ende .Cells(6 + i, 1).Formula = i .Cells(6 + i, 2).FormulaR1C1 = _ "=SYD(R1C2,R2C2,R3C2,RC[-1])" Next i Columns("B:B").EntireColumn.AutoFit End WithEnd Sub
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (9)
'AfA-Plan mit allen angebotenen AfA-Möglichkeiten'AfA-Plan mit allen angebotenen AfA-Möglichkeiten
Sub AlleAfA() With ActiveSheet .Range("A4").Formula = "Satz (%)" .Range("B4").Formula = CSng(Application.DialogSheets(1) _ .EditBoxes(4).Text) 'Faktor(Prozent*n/100) .Range("C4").Formula = "=B4*B3/100" .Range("B6").Formula = "Lineare AfA" .Range("C6").Formula = "Degressive AfA" .Range("D6").Formula = "Digitale AfA" Ende = CSng(Application.DialogSheets(1).EditBoxes(3).Text) For i = 1 To Ende .Cells(6 + i, 1).Formula = i .Cells(6 + i, 2).FormulaR1C1 = "=SLN(R1C2,R2C2,R3C2)" Next i
Dipl. oec. Andreas LautenbacherGrundlagen
Lösung S2 (10)
For i = 1 To Ende .Cells(6 + i, 3).FormulaR1C1 = _ "=DDB(R1C2,R2C2,R3C2,RC[-2],R4C3)" Next i For i = 1 To Ende .Cells(6 + i, 4).FormulaR1C1 = _ "=SYD(R1C2,R2C2,R3C2,RC[-3])" Next i Columns("B:D").EntireColumn.AutoFit End WithEnd Sub