Dipl. oec. Andreas Lautenbacher Grundlagen
Werbung
Definition VBA Visual Makroprogrammierung mit Hilfe von visuellen Programmiertools, wie z.b. Dialogboxen, Schaltflächen, etc. Basic die zugrundeliegende Programmiersprache BASIC = Beginners All Symbolic Instruction Code (entwickelt Anfang der 60er Jahre aus der Sprache FORTRAN) Applications Variante der Programmiersprache Visual Basic speziell für Anwendungen (hier: EXCEL 97) Grundlagen Dipl. oec. Andreas Lautenbacher Programmierumgebung VBA Die Programmierumgebung stellt das Entwicklungswerkzeug von VBAProgrammen in EXCEL dar und besteht unter anderem aus folgenden Teilen: 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 von Programmen Grundlagen Dipl. oec. Andreas Lautenbacher Modulblatt Das Modulblatt nimmt den Quellcode zur Steuerung von Programmen auf: Grundlagen Dipl. oec. Andreas Lautenbacher Optionen Modul-Optionen können über <Extras/Optionen...> gewählt werden. Grundlagen Dipl. oec. Andreas Lautenbacher Modulblätter einfügen Module werden über <Einfügen/Modul...> von der VBA-Ebene eingefügt: Grundlagen Dipl. oec. Andreas Lautenbacher Symbolleisten Allgemein werden in EXCEL Symbolleisten über den Befehl <Ansicht/Symbolleisten...> aufgerufen: Grundlagen Dipl. oec. Andreas Lautenbacher Visual-Basic Symbolleisten Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen ... werden aufgezeichnet Dipl. oec. Andreas Lautenbacher Aufzeichnungsarten Die Programmierumgebung stellt das Entwicklungswerkzeug von VBAProgrammen 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 angegeben, 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 Grundlagen Dipl. oec. Andreas Lautenbacher Makros aufzeichnen Makros werden über <Extras/Makro/Aufzeichnen...> aufgezeichnet: Grundlagen Dipl. oec. Andreas Lautenbacher Makro: Aufgabe G1 (1) Legen Sie in einer neuen Datei zwei Tabellen nach folgendem Muster an: Grundlagen Dipl. oec. Andreas Lautenbacher 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“. Grundlagen Dipl. oec. Andreas Lautenbacher 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): Grundlagen Dipl. oec. Andreas Lautenbacher 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 entsprechende 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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! Grundlagen Dipl. oec. Andreas Lautenbacher Makro: Aufgabe G1 (5) aufgezeichnetes Makro: ausgeführtes Makro: Kosten Produkt X Kosten Produkt X Herstellungskosten Herstellungskosten Vertriebskosten Vertriebskosten sonstige Kosten sonstige Kosten außerordentl. 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! Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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! Grundlagen Dipl. oec. Andreas Lautenbacher Verwaltung von Add-Ins Verwaltet werden Add-Ins über den Add-In-Manager (<Extras/Add-InManager...>). Alle markierten Add-Ins werden beim EXCEL-Start automatisch geladen und stehen allen Arbeitsmappen zur Verfügung: Grundlagen Dipl. oec. Andreas Lautenbacher Erstellung von Add-Ins Erstellt werden Add-Ins über <Datei/Speichern unter / .... / Microsoft Excel-Add-In>. Grundlagen Dipl. oec. Andreas Lautenbacher Add-In Manager Wurde ein Add-In erstellt, kann es über <Durchsuchen> im Add-InManager (im Menü Extras) und Angabe der entsprechenden Datei geladen werden: Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher Eigenschaften von Strukturblöcken Programmbaustein mit eindeutiger Funktion Zusammensetzung aus einem Befehl, mehreren sukzessiven Befehlen oder aus mehreren nachgeordneten 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) Grundlagen Dipl. oec. Andreas Lautenbacher Sequenz Anweisung 1 Anweisung 2 Anweisung 3 Grundlagen Dipl. oec. Andreas Lautenbacher Verzweigung Bedingung Ja oder TRUE Anweisung bei erfüllter Bedingung Grundlagen Nein oder FALSE Anweisung bei nicht erfüllter Bedingung Dipl. oec. Andreas Lautenbacher Schleifen Standardtyp: Abbruchbedingung Schleifenrumpf abweisende Schleife Schleifenrumpf nicht abweisende Schleife Abbruchbedingung Grundlagen Dipl. oec. Andreas Lautenbacher Case-Struktur Fall 1 Bedingung Fall 2 Fall 3 Fall 4 Anweisungen Anweisungen Anweisungen Anweisungen für Fall 1 für Fall 2 für Fall 3 für Fall 4 Grundlagen Dipl. oec. Andreas Lautenbacher Verfeinerung Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel-Struktogramm E Eingabe des Kapitals Eingabe der Laufzeit Laufzeit <= 4 Jahre Ja Nein Laufzeit <= 9 Jahre V Ja Berechnung des Endkapitals aus Kapital und Laufzeit mit ... Zinssatz = 4% A Nein mit ... Zinssatz = 6% mit ... Zinssatz = 8% Ausgabe des Endkapitals Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel-Struktogramm mit VBA-Elementen E Kapital = InputBox("Geben Sie das Kapital ein") Laufzeit = InputBox("Geben Sie die Laufzeit in Jahren ein") If Laufzeit <= 4 Then If Laufzeit <= 9 V Then Endkapital = ... Kapital * (1.04) ^ Laufzeit A Else Else = ... ... * (1.06) ^ ... = ... ... * (1.08) ^ ... MsgBox "Das Endkapital beträgt " + Str$(Endkapital) + " DM" Grundlagen Dipl. oec. Andreas Lautenbacher Nullstellenberechnung mit Regula falsi f(x) c b f(x) besitzt Nullstelle im Intervall [a, b]. Es wird der Nullpunkt c bzgl. der Geraden durch die Punkte (a, f(a)) und (b, f(b)) berechnet und das Intervall [a, c] oder [c, b] gewählt, daß die Nullstelle enthält. x a Dieses Intervall ist das neue Intervall [a, b] und der Prozeß wiederholt sich. Grundlagen Dipl. oec. Andreas Lautenbacher 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, Iterationsanzahl groß, f(a) oder f(b) nahe 0, b-c oder c-a klein) Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe Struktogramm “Währungsumrechnung” 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe Durchlaufzeitberechnung Auftrag Reihenfolge M1 M2 M3 M4 2 3 5 6 1 4 Gegeben seien eine Reihe von Aufträgen, die die Maschinen M1,... Mn der Reihe nach durchlaufen müssen. Die Gesamtdurchlaufzeit aller Aufträge soll berechnet werden. Diese Durchlaufzeit hängt von der Reihenfolge ab, da zu einer Zeit auf einer Maschine nur ein Auftrag ausgeführt werden kann. Durchlaufzeiten Die notwendigen Daten sollen aus der Selektion eines rechteckigen Schemas übernommen werden. Markierung Grundlagen Dipl. oec. Andreas Lautenbacher Programmprozeduren Kontrollstrukturen wie z.B. Schleifen etc. lassen sich mit dem Makrorecorder 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 Programmeinheiten, die in Modulen geschrieben werden. VBA-Programme unter EXCEL haben im Gegensatz zu Programmiersprachen 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. Grundlagen Dipl. oec. Andreas Lautenbacher Prozedurarten EXCEL unterscheidet grundsätzlich drei Arten von Prozeduren: Sub-Prozedur Führt Aktionen aus, liefert aber keinen 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 Grundlagen Dipl. oec. Andreas Lautenbacher Sub-Prozedur Syntax: Sub Prozedurname (Variablenliste) Anweisung 1 Anweisung 2 ... Anweisung n End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Funktions-Prozedur Syntax: Function Funktionsname (Argumentliste) Anweisung 1 Anweisung 2 ... Anweisung n Funktionsname = Rückgabewert End Function Grundlagen Dipl. oec. Andreas Lautenbacher Prozedurarten Arbeitsmappe Tabellenblätter Modulblatt Modulblatt Sub-Prozeduren Modulblatt Funktions-Prozeduren Grundlagen Dipl. oec. Andreas Lautenbacher Prozedurarten Programm: "Hello World" Sub Hello_World() Dim Inhalt As String Inhalt = "Hello World" ActiveCell.Formula = Inhalt End Sub Das Programm gibt die Zeichenkette „Hello World“ in der aktiven Zelle aus. Grundlagen Dipl. oec. Andreas Lautenbacher Prozedurarten Funktion: "Addieren" Function Addieren(x as Single, y as Single) Addieren = x + y End Function Grundlagen Dipl. oec. Andreas Lautenbacher Direktfenster (1) Das Direktfenster dient der Überprüfung von Prozeduren auf Fehler. Aufgerufen wird es aus der VBA-Ebene über <Ansicht / Direktfenster> Grundlagen Dipl. oec. Andreas Lautenbacher Direktfenster Test von Funktionen: debug.print Funktionsname (Argumentliste) statt debug.print kann auch einfach ein „?“ eingegeben werden Test von Prozeduren: call Prozedurname (Variablenliste) Grundlagen Dipl. oec. Andreas Lautenbacher 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! Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel Direktfenster Grundlagen Dipl. oec. Andreas Lautenbacher Ü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 Grundlagen Dipl. oec. Andreas Lautenbacher Ausdrucküberwachung im Überwachungsfenster (2) Vorgehensweise: 3. Fügen Sie nacheinander im Feld <Ausdruck> die Namen der Variablen ein: Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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.Print ActiveWorkbook.ActiveSheet.Name Weisen Sie den Namen des Blattes der Variablen „Blattname“ zu und nennen Sie die Prozedur „Blatt_Drucken“ Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe G3 (3) Grundlagen Dipl. oec. Andreas Lautenbacher 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! Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 eindimensionales Feld, dessen Elemente über Indizes angesprochen werden. Eine Matrix ist entsprechend ein mehrdimensionales Feld. Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Double # 8 Bytes -3,402823E38 bis 1,401298E-45 für negative Werte und 1,401298E-45 bis 3,402823E38 für positive Werte -1,79769313486232E308 bis 4,94065645841247E-324 für negative Werte und 4,94065645841247E-324 bis 1,79769313486232E308 für pos. Werte Grundlagen Dipl. oec. Andreas Lautenbacher Datentypen (2) Typ Symbol Speicherbedarf Wertebereich Currency 8 Bytes Date 8 Bytes -922337203685477,5808 bis 922337203685477,5807 January 1, 100 bis December 31, 9999 Object 4 Bytes jeder Objektverweis String $ Variant Grundlagen 1 Byte pro Zeichen 16 Bytes und 1 Byte pro Zeichen Jedes Zeichen liegt im Bereich von 0 bis 255 Jeder Wert (Default-Datentyp!) Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Eigene Datentypen: Verbunde Verbunde sind Deklarationen von eigenen Datentypen, die aus Kombinationen mehrerer bekannter Datentypen bestehen: Type Name name1 As Datentyp name2 As Datentyp ... End Type Bsp.: Type artikel artikelname As String preis As Currency End Type Grundlagen Dipl. oec. Andreas Lautenbacher 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! Bsp.: Dim x(10) As Integer Grundlagen (es werden 11 ganzzahlige Variablen x(0) ... x(10) deklariert) Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen (spaltenweise linearisiert) Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher Gültigkeitsbereich von Variablen Variablen können nur in einer Prozedur für ein ganzes Modul für eine ganze Arbeitsmappe definiert werden Grundlagen Dipl. oec. Andreas Lautenbacher Gültigkeitsbereich von Variablen Deklaration innerhalb einer Prozedur: Sub Deklaration () (innerhalb der Prozedur) Dim Länge as Integer Deklaration auf Modulebene: Dim Länge as Integer oder Privat Länge as Integer (am Anfang eines Moduls) Deklaration auf Arbeitsmappenebene: Public Länge as Integer (am Anfang eines Moduls) Grundlagen Dipl. oec. Andreas Lautenbacher 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, Stil End Sub Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 ungleich 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) Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Verzweigung: Select-Case Select Case Variable Case möglicheVariablenWerte1 Anweisung1 Case möglicheVariablenWerte2 Anweisung2 ... Case Else AnweisungN End Select Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe K1 Programmieren Sie das Beispiel zu Variablen und Konstanten mit Hilfe der Select-Case-Verzweigung! Der Name der Sub-Prozedur ist "Symbolleistenrückgabe2"! Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Schleifen Abweisende Schleifen (Ende-Bedingung wird vor dem Beginn der Schleife überprüft) Nichtabweisende Schleifen (Ende-Bedingung wird erst nach dem Durchlauf der Schleife Überprüft) Endlosschleifen (Haben kein Ende) Grundlagen Dipl. oec. Andreas Lautenbacher Abweisende Schleifen For To Next (Für Nächste) For Each Next (Für Alle Nächste) Do While (Durchlaufe Solange) While Wend (Solange Durchlaufe) Grundlagen Dipl. oec. Andreas Lautenbacher For To Next For Variable = AnfangsWert To Endwert Step Schrittweite Anweisung1 Anweisung2 ... Next Variable Grundlagen Dipl. oec. Andreas Lautenbacher 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!) Grundlagen Dipl. oec. Andreas Lautenbacher For Each Next For Each Objektvariable In Objektliste Anweisung 1 ... Anweisung n Next Objektvariable Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel: For Each - Next Sub Schleife2 () Dim Index As Object For Each Index In Toolbars Index.Visible = False Next Index End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Do While Do While Bedingung Anweisung1 ... AnweisungN Loop Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel: Do While Sub Schleife3() Antwort = vbRetry Do While Antwort = vbRetry Antwort = MsgBox("Was soll das eigentlich?",vbRetryCancel) Loop End Sub Grundlagen Dipl. oec. Andreas Lautenbacher While Wend While Bedingung Anweisung1 ... AnweisungN Wend Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Nichtabweisende Schleifen Do-Loop-While (Durchlaufe solange) Do-Loop-Until (Durchlaufe bis wahr) Grundlagen Dipl. oec. Andreas Lautenbacher Do-Loop-While Der Abbruch erfolgt, wenn die Bedingung nicht mehr erfüllt ist: Do Anweisung1 ... AnweisungN Loop While Bedingung Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Do-Loop-Until Der Abbruch erfolgt, wenn die Bedingung erfüllt ist: Do Anweisung1 ... AnweisungN Loop Until Bedingung Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Endlosschleife (Do-Loop) Endlosschleifen sind Schleifen, deren Abbruchbedingung niemals erfüllt ist. Do Anweisung Loop Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Formatierung der MsgBox Konstante vbOkOnly vbOkCancel vbAbortRetryIgnore vbYesNoCancel vbYesNo vbRetryCancel vbCritical vbQuestion vbExclamation vbInformation vbDefaultButton1 vbDefaultButton2 vbDefaultButton3 Grundlagen Wert 0 1 2 3 4 5 16 32 48 64 0 256 512 Beschreibung nur OK-Button Ok- und Abbrechen- Button Abbrechen/Wiederholen/Ignorieren Ja/Nein/Abbrechen Ja/Nein Wiederholen/Abbrechen Stop-Symbol Fragezeichen Ausrufezeichen Info-Symbol 1.Schaltfläche ist aktiviert 2.Schaltfläche ist aktiviert 3.Schaltfläche ist aktiviert Dipl. oec. Andreas Lautenbacher Aufgabe K5 (1) Programmieren Sie ein Funktionsmakro, das die Einkommenssteuer 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-Verzweigung benötigt Grundlage ist der EStG §32e (Einkommenssteuerrichtliniengesetz) Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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-MarkBetrag 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 (SplittingVerfahren). Grundlagen Dipl. oec. Andreas Lautenbacher Programmaufbau Funktionsname und Argumente definieren Einkommensart = 1 (ledig) Nein Ja Einkommensart = 2 x = RundeAb(EK/54)+54 y = (x-8100)/10000 Ja Nein x = RundeAb(EK/2/54)+54 y = (x-8100)/10000 Struktogramm Steuerfunktion Grundlagen Struktogramm Steuerfunktion (2xSteuer) Fehlermeldung Dipl. oec. Andreas Lautenbacher Struktogramm Steuerfunktion Ja x <= 5616 Ja Nein x <= 8153 Nein x <= 120041 Ja Einkommenssteuer = 0 Grundlagen Nein EinkommensEKsteuer = steuer = 0,19*x - 1067 (151,94*y + 1900)*y + 472 EKsteuer = 0,53*x 22842 Dipl. oec. Andreas Lautenbacher Lösung K5 (1) Dim x As Single; y As Single 'Variablendeklaration Function Einkommenssteuer(Einkommen; Einkommensart) 'Anfang der Funktionsprozedur If Einkommensart = 1 Then 'Fall 1: Keine Zusammenveranlagung x = Int(Einkommen / 54) * 54 'Zu versteuerndes Einkommen y = (x - 8100) / 10000 'Variable y If x <= 5616 Then 'Nullzone Einkommenssteuer = 0 ElseIf x >= 5617 And x <= 8153 Then 'untere Proportionalzone Einkommenssteuer = Int(0,19 * x - 1067) ElseIf x >= 8154 And x <= 120041 Then 'Progressionszone Einkommenssteuer = Int((151,94 * y + 1900) * y + 472) Else '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. Grundlagen Dipl. oec. Andreas Lautenbacher Lösung K5 (2) ElseIf Einkommensart = 2 Then ' Fall 2: Zusammenveranlagung(Splitting) x = Int(Einkommen / 2 / 54) * 54 'Zu versteuerndes Einkommen y = (x - 8100) / 10000 'Variable y If x <= 5616 Then 'Nullzone Einkommenssteuer = 0 ElseIf x >= 5617 And x <= 8153 Then 'untere Proportionalzone Einkommenssteuer = 2 * Int(0,19 * x - 1067) ElseIf x >= 8154 And x <= 120041 Then 'Progressionszone Einkommenssteuer = 2 * Int((151,94 * y + 1900) * y + 472) Else 'obere Proportionalzone Einkommenssteuer = 2 * Int(0,53 * x - 22842) End If Else 'Fall 3: Falsche Eingabe des letzten Arguments Msg = "Gültige Werte für Einkommensart sind" _ + Chr(10) + "1 (Normal) oder 2 (Splitting)!" MsgBox Msg; 64; "falsches Argument" End If End Function 'Ende der Funktionsprozedur Grundlagen Dipl. oec. Andreas Lautenbacher Definition Objekte sind Bestandteile von EXCEL auf die mit Hilfe von VBA zugegriffen werden kann. Objekte Buttons, etc. sind z.B. Worksheet, Chart, 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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: Methoden stellen Aktionen dar, die ein Objekt ausführen soll Eigenschaften haben Werte, die festgelegt oder zurückgegeben werden Grundlagen Dipl. oec. Andreas Lautenbacher 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" Grundlagen Dipl. oec. Andreas Lautenbacher Eigenschaften abfragen Eigenschaftswerte von Objekten können aber auch zurückgegeben 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Objekte (1) Application.ActiveWorkbook.Sheets.Add Type:=xlCharts Basisobjekt Unterobjekt = Eigenschaft Unterobjekt = Methode Methode Argument Grundlagen Dipl. oec. Andreas Lautenbacher Objekte (2) Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen (Arbeitsmappe) Dipl. oec. Andreas Lautenbacher wichtige Objekttypen Wichtige Objekttypen und Objektlistensind: Application Windows Window Sheets Sheet Workbooks Workbook WorkSheets Modules Module Charts Range Grundlagen Anwendung FensterListe Fenster BlattListe Blatt Arbeitsmappenliste Arbeitsmappe TabellenBlattliste ModulListe Modul DiagrammListe Bereich Dipl. oec. Andreas Lautenbacher Objektkatalog - Aufruf Der Objektkatalog enthält eine Auflistung aller Objekte, Methoden und Eigenschaften. Erreichbar ist er bei aktiviertem Visual BasicModul ü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 Grundlagen Dipl. oec. Andreas Lautenbacher Objektkatalog - Online-Hilfe Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher 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! Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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: Grundlagen Cells (Zeilenindex, Spaltenindex) Dipl. oec. Andreas Lautenbacher 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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Zellreferenzen - Mehrfachauswahl Mehrfachauswahl mit dem Schlüsselwort Union: Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe O2 Programmieren Sie eine Prozedur, die in einer Zweifachauswahl 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" Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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). Grundlagen Dipl. oec. Andreas Lautenbacher 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").Activate End Sub Grundlagen Dipl. oec. Andreas Lautenbacher 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).Activate End Sub Sub Auto_Close() MsgBox "Bis dann!", vbExclamation, "Info" End Sub Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher Lösung O4 Sub Auto_Open() With Toolbars(xlClassic1) .Visible = True .Position = xlTop End With Modules(1).Activate End Sub Sub Auto_Close() For Each Index In Toolbars Index.Visible = False Next Index End Sub Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Bei-Ereignis Prozeduren - deutsch OnAction BeiAktion OnCalculate BeiBerechnung OnData BeiDatenerhalt OnDoubleClick BeiDoppelklick OnEntry BeiEingabe OnKey BeiTaste OnRepeat BeiWiederholung OnSheetActivate BeiBlattaktivierung OnSheetDeactivate BeiBlattdeaktivierung OnTime BeiZeit Grundlagen Dipl. oec. Andreas Lautenbacher Erstellen von Bei-Ereignis Prozeduren Erstellen von Bei-Ereignis Prozeduren: Erstellen Sie eine Prozedur, die die Ereignisbehandlungsroutine, die beim Eintritt des Ereignisses ausgeführt werden soll enthält. Verwenden Sie in einer anderen Prozedur eine Bei-EreignisAnweisung, die dem Ereignis die Ereignisbehandlungsroutine zuweist. Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher Ü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. Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel: Übergabe von Argumenten (1) Beispiel-1: Aufruf 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 + Parameter2 End Function Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Fortsetzung Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher Dialogführung EXCEL bzw. VBA bieten zur individuellen Gestaltung der Benutzeroberfläche und Dialogführung zahlreiche Möglichkeiten an: Menüleisteneditor Symbolleisteneditor Schaltflächen Dialogelemente vordefinierte Dialogboxen selbstdefinierte Dialogboxen Eigene Online-Hilfe Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher Symbole ändern oder bearbeiten (3) Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher Neue Menüs oder Symbolleisten erstellen (2) Neues Menü: Grundlagen Dipl. oec. Andreas Lautenbacher 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“) Grundlagen Dipl. oec. Andreas Lautenbacher Neue Menüs oder Symbolleisten erstellen (4) Neue Symbolleiste: Grundlagen Dipl. oec. Andreas Lautenbacher Schaltflächen (1) Schaltflächen stellen die einfachste Möglichkeit dar, VBAAnwendungen benutzerfreundlich zu gestalten: Grundlagen Dipl. oec. Andreas Lautenbacher Schaltflächen (2) Grundlagen Dipl. oec. Andreas Lautenbacher Schaltfläche - Makro zuweisen Durch Doppelklick auf die Schaltfläche erscheint ein neues Codeblatt in das das gewünschte Makro eingesetzt wird. Grundlagen Dipl. oec. Andreas Lautenbacher Schaltfläche - Eigenschaften festlegen 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. Grundlagen Dipl. oec. Andreas Lautenbacher Dialogelemente Neben Schaltflächen existieren Dialogelemente (Steuerelemente): noch zahlreiche andere Entwurfsmodus / Eigenschaften Code anzeigen Kontrollfeld / Textfeld Befehlsschaltfläche / Optionsfeld Listenfeld / Kombinationsfeld Umschaltfläche / Drehfeld Bildlaufleiste / Bezeichnungsfeld Bild / Weitere Steuerelemente Grundlagen Dipl. oec. Andreas Lautenbacher 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... ! Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe S1 Programmieren Sie eine Prozedur, die die aktive Arbeitsmappe 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. Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S1 Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher selbstdefinierte Dialoge (Userform) Grundlagen Dipl. oec. Andreas Lautenbacher selbstdefinierte Dialoge - Elemente plazieren Grundlagen Dipl. oec. Andreas Lautenbacher Aktivierungsreihenfolge festlegen Die aktivierungsreihenfolge der Elemente des Dialogs kann durch <Ansicht/Aktivierungsreihenfolge> verändert werden. Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher Prozedurprogrammierung Userform (2) Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe D1 (1) Legen Sie zunächst eine Tabelle mit folgendem Aussehen an und nennen Sie sie „Fahrzeugliste“. Grundlagen Dipl. oec. Andreas Lautenbacher 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. Grundlagen Dipl. oec. Andreas Lautenbacher Lösung D1 (1) Grundlagen Dipl. oec. Andreas Lautenbacher Lösung D1 (2) Grundlagen Dipl. oec. Andreas Lautenbacher 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) Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel Listenbezug (1): Statt des Textfeldes für das Fabrikat verwende man ein Kombinationsfeld. Im Programm muß statt TextBox1 jetzt ComboBox1 stehen. Herstellerliste: Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel Listenbezug (2): Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel Listenbezug (3): Grundlagen Dipl. oec. Andreas Lautenbacher Beispiel Direktbefehl: Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe S2 (1) Es soll ein Makro programmiert werden, das einen vollständigen Abschreibungsplan erstellt. Erläuterungen: Der Abschreibungsplan soll Tabellenblatt erstellt werden. auf einem neu einzufügenden Als Abschreibungsmethoden sollen entweder die lineare, die geometrisch-degressive, die arithmetisch-degressive Abschreibungsmethode, oder aber alle Methoden nebeneinander verwendet werden. Alle notwendigen Daten (Anschaffungswert, Restwert, etc.) sollen über einen selbsterstellten Dialog abgerufen werden. Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe S2 (2) Die Funktionen für die einzelnen Abschreibungsmethoden lauten: lineare AfA: LIA geom. degressive AfA:GDA (DDB) arithm. degressive AfA: DIA (SLN) (SYD) Funktionen werden folgendermaßen in eine Zelle eingefügt (Bsp.): Range("A1").Formula = " = LIA(10000, 1000, 5)" Grundlagen Dipl. oec. Andreas Lautenbacher 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. Hinweis: Alle sonstigen Informationen über anzuwendende Methoden, etc. sind entnehmen. Objekte, auf diese der Online-Hilfe zu Die Schlüsselwörter sind anschließend mit Hilfe der Datei "VBALISTE.XLS" zu übersetzen. Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe S2 - Ergebnis (1) Grundlagen Dipl. oec. Andreas Lautenbacher Aufgabe S2 - Ergebnis (2) Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (1) 'Anzeige des Dialogblattes Sub DialogZeigen() Application.DialogSheets(1).Show End Sub '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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (2) 'Verknüpfung von Drehfeldern und Bearbeitungsfeldern Sub Drehfeld1_OnClick() With Application.DialogSheets(1) .EditBoxes(3).Text = .Spinners(1).Value End With End Sub Sub Drehfeld2_OnClick() With Application.DialogSheets(1) .EditBoxes(4).Text = .Spinners(2).Value End With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (3) 'Verknüpfung von Drehfeldern und Bearbeitungsfeldern Sub Nutzung_OnAction() With Application.DialogSheets(1) .Spinners(1).Value = .EditBoxes(3).Text End With End Sub Sub Satz_OnAction() With Application.DialogSheets(1) .Spinners(2).Value = .EditBoxes(4).Text End With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (4) 'Eigentliche Prozedur zu Erstellung des Abschreibungsplans _ auf 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 Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (5) 'Abfrage der Abschreibungsart nach Einstellung der _ Optionsfelder 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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (6) '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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (7) '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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (8) '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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher Lösung S2 (9) '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 Grundlagen Dipl. oec. Andreas Lautenbacher 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 With End Sub Grundlagen Dipl. oec. Andreas Lautenbacher
