200
Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA V isual Makroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt- flächen, etc. B asic die zugrundeliegende Programmiersprache BASIC = Beginners All Symbolic Instruction Code (entwickelt Anfang der 60er Jahre aus der Sprache FORTRAN) A pplications Variante der Programmiersprache Visual Basic speziell für Anwendungen (hier: EXCEL 97)

Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Embed Size (px)

Citation preview

Page 1: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 2: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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:

Page 3: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Modulblatt

Das Modulblatt nimmt den Quellcode zur Steuerung von Programmen auf:

Page 4: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Optionen Modul-Optionen können über <Extras/Optionen...> gewählt werden.

Page 5: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Modulblätter einfügen

Module werden über <Einfügen/Modul...> von der VBA-Ebene eingefügt:

Page 6: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Symbolleisten

Allgemein werden in EXCEL Symbolleisten über den Befehl <Ansicht/Symbolleisten...> aufgerufen:

Page 7: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Visual-Basic Symbolleisten

Page 8: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 9: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 10: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Makros aufzeichnenMakros werden über <Extras/Makro/Aufzeichnen...> aufgezeichnet:

Page 11: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Makro: Aufgabe G1 (1)

Legen Sie in einer neuen Datei zwei Tabellen nach folgendem Muster an:

Page 12: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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“.

Page 13: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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):

Page 14: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 15: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 16: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 17: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 18: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 19: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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:

Page 20: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Erstellung von Add-InsErstellt werden Add-Ins über <Datei/Speichern unter / .... / Microsoft Excel-Add-In>.

Page 21: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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:

Page 22: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 23: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 24: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 25: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Sequenz

Anweisung 1

Anweisung 2

Anweisung 3

Page 26: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Verzweigung

Bedingung

Ja oder TRUE Nein oder FALSE

Anweisung bei erfüllter Bedingung

Anweisung bei nicht erfüllter Bedingung

Page 27: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Schleifen

abweisende Schleife

nicht abweisende

Schleife

Standardtyp:

Abbruchbedingung

Abbruchbedingung

Schleifenrumpf

Schleifenrumpf

Page 28: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 29: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Verfeinerung

Page 30: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 31: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 32: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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) ^ ...

Page 33: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 34: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 35: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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”

Page 36: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 37: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 38: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 39: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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:

Page 40: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Sub-Prozedur

Syntax:

Sub Prozedurname (Variablenliste)

Anweisung 1

Anweisung 2

...

Anweisung n

End Sub

Page 41: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Funktions-Prozedur

Syntax:

Function Funktionsname (Argumentliste)Anweisung 1Anweisung 2 ...Anweisung n

Funktionsname = RückgabewertEnd Function

Page 42: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Prozedurarten

ModulblattModulblatt

ModulblattTabellenblätter

Arbeitsmappe

Funktions-Prozeduren

Sub-Prozeduren

Page 43: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 44: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Prozedurarten

Funktion: "Addieren"

Function Addieren(x as Single, y as Single)

Addieren = x + y

End Function

Page 45: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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>

Page 46: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 47: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 48: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Beispiel Direktfenster

Page 49: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 50: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Ausdrucküberwachung im Überwachungsfenster (2)

Vorgehensweise:

3. Fügen Sie nacheinander im Feld <Ausdruck> die Namen der Variablen ein:

Page 51: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 52: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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“

Page 53: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Aufgabe G3 (3)

Page 54: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 55: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 56: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 57: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 58: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 59: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!)

Page 60: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 61: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 62: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 63: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 64: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 65: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 66: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 67: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 68: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 69: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 70: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 71: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 72: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 73: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 74: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 75: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Verzweigung: Select-Case

Select Case Variable

Case möglicheVariablenWerte1

Anweisung1

Case möglicheVariablenWerte2

Anweisung2

...

Case Else

AnweisungN

End Select

Page 76: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 77: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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"!

Page 78: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 79: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 80: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 81: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

For To Next

For Variable = AnfangsWert To Endwert Step Schrittweite

Anweisung1

Anweisung2

...

Next Variable

Page 82: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!)

Page 83: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

For Each Next

For Each Objektvariable In Objektliste

Anweisung 1

...

Anweisung n

Next Objektvariable

Page 84: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 85: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Do While

Do While Bedingung

Anweisung1

...

AnweisungN

Loop

Page 86: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 87: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

While Wend

While Bedingung

Anweisung1

...

AnweisungN

Wend

Page 88: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 89: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Nichtabweisende Schleifen

Do-Loop-While (Durchlaufe solange)

Do-Loop-Until (Durchlaufe bis wahr)

Page 90: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Do-Loop-While

Der Abbruch erfolgt, wenn die Bedingung nicht mehr erfüllt ist:

Do

Anweisung1

...

AnweisungN

Loop While Bedingung

Page 91: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 92: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Do-Loop-Until

Der Abbruch erfolgt, wenn die Bedingung erfüllt ist:

Do

Anweisung1

...

AnweisungN

Loop Until Bedingung

Page 93: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 94: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Endlosschleife (Do-Loop)

Endlosschleifen sind Schleifen, deren Abbruchbedingung niemals erfüllt ist.

Do

Anweisung

Loop

Page 95: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 96: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 97: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 98: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 99: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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).

Page 100: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 101: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 102: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 103: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 104: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 105: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 106: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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"

Page 107: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 108: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 109: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Objekte (1)

Application.ActiveWorkbook.Sheets.Add Type:=xlCharts

Methode

Unterobjekt =Eigenschaft

Unterobjekt = Methode

Basisobjekt

Argument

Page 110: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Objekte (2)

Page 111: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 112: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

wichtige ObjekttypenWichtige Objekttypen und Objektlistensind:

AnwendungFensterListeFensterBlattListeBlattArbeitsmappenlisteArbeitsmappeTabellenBlattlisteModulListeModulDiagrammListeBereich

ApplicationWindowsWindowSheetsSheetWorkbooksWorkbookWorkSheetsModulesModuleChartsRange

Page 113: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 114: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Objektkatalog - Online-Hilfe

Page 115: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 116: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 117: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 118: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 119: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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!

Page 120: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 121: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 122: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 123: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 124: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Zellreferenzen - Mehrfachauswahl

Mehrfachauswahl mit dem Schlüsselwort Union:

Page 125: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 126: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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"

Page 127: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 128: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 129: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 130: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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).

Page 131: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 132: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 133: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 134: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 135: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 136: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 137: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 138: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 139: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 140: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 141: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 142: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 143: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 144: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 145: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 146: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 147: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 148: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 149: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 150: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 151: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 152: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 153: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 154: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 155: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 156: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 157: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Symbole ändern oder bearbeiten (3)

Page 158: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 159: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Neue Menüs oder Symbolleisten erstellen (2)

Neues Menü:

Page 160: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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“)

Page 161: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Neue Menüs oder Symbolleisten erstellen (4)

Neue Symbolleiste:

Page 162: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Schaltflächen (1)

Schaltflächen stellen die einfachste Möglichkeit dar, VBA-Anwendungen benutzerfreundlich zu gestalten:

Page 163: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Schaltflächen (2)

Page 164: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 165: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 166: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 167: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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... !

Page 168: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 169: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Lösung S1

Page 170: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 171: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

selbstdefinierte Dialoge (Userform)

Page 172: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

selbstdefinierte Dialoge - Elemente plazieren

Page 173: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Aktivierungsreihenfolge festlegen

Die aktivierungsreihenfolge der Elemente des Dialogs kann durch <Ansicht/Aktivierungsreihenfolge> verändert werden.

Page 174: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 175: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Prozedurprogrammierung Userform (2)

Page 176: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 177: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Aufgabe D1 (1)

Legen Sie zunächst eine Tabelle mit folgendem Aussehen an und nennen Sie sie „Fahrzeugliste“.

Page 178: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 179: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Lösung D1 (1)

Page 180: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Lösung D1 (2)

Page 181: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)

Page 182: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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:

Page 183: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Beispiel Listenbezug (2):

Page 184: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Beispiel Listenbezug (3):

Page 185: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Beispiel Direktbefehl:

Page 186: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 187: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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)"

Page 188: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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.

Page 189: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Aufgabe S2 - Ergebnis (1)

Page 190: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

Dipl. oec. Andreas LautenbacherGrundlagen

Aufgabe S2 - Ergebnis (2)

Page 191: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 192: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 193: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 194: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 195: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 196: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 197: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 198: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 199: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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

Page 200: Dipl. oec. Andreas Lautenbacher Grundlagen Definition VBA VisualMakroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schalt-

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