Excel VBA Programmierung -...

Excel VBA Programmierung

hw :: lemitec.de

Dieses Skript zur VBA-Programmierung von MS Office erhebt nicht den Anspruch ein vollstndiges Lehrbuch zu Visual Basic und VBA darzustellen. Die angesprochenen Grundlagen und Objektstrukturen sollen eine Lesekompetenz fr VBA-Code vermitteln. Also eine Einfhrung und Hilfe zur Selbsthilfe sein. Erwarten sie nicht, dass das Lesen eines, dieses, Buches sie zu einem Programmierer macht das werden sie nur durch Programmieren, durch Debuggen, durch Studieren und Anwenden von Code-Beispielen.

javascript:AlterAllDivs('none');

Inhalt

BASIC GRUNDLEGENDE KONZEPTE .............................................................................................................................. 3

VBA-ENTWICKLUNGSUMGEBUNG .................................................................................................................................... 3 VBA-EDITOR .............................................................................................................................................................. 4 MAKRO-SICHERHEIT ..................................................................................................................................................... 8 DEKLARATION.............................................................................................................................................................. 9 FUNCTION .................................................................................................................................................................. 9 FUNKTIONSBEISPIELE ................................................................................................................................................... 12 VERZWEIGUNGEN ....................................................................................................................................................... 18 ARRAYS ................................................................................................................................................................... 19 VB-BIBLIOTHEKEN ...................................................................................................................................................... 21

Split-Funktion .................................................................................................................................................... 21

FORMULARSTEUERELEMENTE (FORMULARCONTROLS) ............................................................................................ 22

EXCEL UND SEINE OBJEKTWELT ................................................................................................................................. 23

BUNG MIT DEM VBA-DEBUGGER ................................................................................................................................. 25 EIN AUSZUG DER OBJEKTBESCHREIBUNG VON RANGE ........................................................................................................ 26 AUFLISTUNGEN - LISTEN ............................................................................................................................................... 29 BUNG PRODUKTION-LAGERBUCHHALTUNG ..................................................................................................................... 31 MAKRO BUNG UND CODE-OPTIMIERUNG ...................................................................................................................... 33 VERFOLGUNG DES PROGRAMMABLAUFS MIT DEM DEBUGGER ............................................................................................... 35 VBA TABELLENZUGRIFFE .............................................................................................................................................. 37 EREIGNIS-KLASSEN ..................................................................................................................................................... 38

BUNG LAGERBUCHUNG .......................................................................................................................................... 40

RIBBON UI MEN-REGISTERKARTEN SELBST BAUEN .............................................................................................. 44

SO ERSTELLEN SIE EIN BENUTZERDEFINIERTES MEN-REGISTER .............................................................................................. 45 ALLGEMEINE STRUKTUR DER RIBBON-XML-BESCHREIBUNGSDATEI ......................................................................................... 47 XML - PROGRAMMIERUNG .......................................................................................................................................... 49 RIBBON UI EIN BEISPIEL MIT DEM VISUAL RIBBON DESIGNER .............................................................................................. 51

ANHANG CODE BEISPIELE .......................................................................................................................................... 54

ANHANG VIDEO-BEISPIELE ........................................................................................................................................ 59

RibbonX Visual Designer 2010

http://www.andypope.info/vba/ribboneditor_2010.htm

Einfhrung Excel VBA I. BI.3

3 Version 1.2016 hw::lemitec.de

BASIC Grundlegende Konzepte VBA steht fr Visual Basic for Application und ist die Programmiersprache fr alle MS-Office-Anwendungen. Fr eine Programmiersprache ist die Unterscheidung von Code und Daten von Bedeutung. Code besteht aus einem vorgegebenen Vorrat an Befehlen, die aus einem festgelegten Satz an Vokabeln besteht. Daten werden vom Programmierer im Speicher abgelegt, dazu definiert er Speicherplatz (Speichervokabeln/-Namen). Die Namen der Code-Vokabeln knnen nicht als Daten-Speicher-Vokabeln vorkommen und umgekehrt. Fr Code besteht eine vorgegebene Rechtschreibung und Satzstellungen (sagen wir Reihenfolge der Befehle) zusammengefasst als Syntax bezeichnet. Als allgemeine Syntax-Vorschrift mssen alle Vokabeln mit einem Buchstaben beginnen und drfen keine Leerzeichen enthalten, wobei nicht zwischen Gro-/Kleinschreibung unterschieden wird.

VBA-Entwicklungsumgebung

BASIC

ProgrammierspracheStellt Mechanismen zur Verwaltung von Speicherplatz

Dim i As Integer

und Kontrollstrukturen fr den Programmablauf

Sub End SubIf i=0 thenFor i=1 to n :: next

zur Verfgung.

Application

Objektmodell der zugrunde-liegenden Applikation bildet den Funktionsumfang der entsprechenden Office-Komponenten ab.

Ein Objekt wird durch eine Klasse beschrieben und stellt dem Programmierer- Eigenschaften- Methoden- Ereignissezur Manipulation der Appliaktion zur Verfgung

Editor

SchreibenAusfhrenundDebuggen

---Sub AnzahlDerFolien()

Msgbox ActivePresentation.Slides.Count

End Sub

Interpreter/Compiler

Umwandeln des lesbaren Textes aus dem Editor in binre Daten und ausfhrbare Anweisungen .

Sub AnzahlTabellen() Msgbox _ ActiveWorkbook.Worksheets.Count



VBA-Editor

VBA-Code wird mit dem VBA-Editor erfasst. Sie rufen ihn aus allen Office-Programmen mit Alt+F11 oder ber Extras | Makro | VBA-Editor auf. Es ffnet sich eine Programmieroberflche mit einem Schreibbereich, einem Projektexplorer fr bereits vorhandene Module, einer Eigenschaften-Liste mit eigenen Men- und Symbolleisten. Sie knnen zur PowerPoint-Oberflche zurckkehren, indem sie ber die Taskleiste zwischen den beiden Programmteilen wechseln oder indem sie den Editor wieder schlieen. Begonnener Code wird automatisch gespeichert. Makroprogramme werden in ein Modul geschrieben. Anfangs sind keine Module vorhanden und sie mssen zuerst ber Einfgen | Modul eines anlegen. Es ist erstmal ganz normale Schreibarbeit einen VBA-Code in ein Modul hineinzubringen der VBA Editor dient dabei als Textprozessor (einfache Textverarbeitung). Der eingegebene Text wird automatisch (vom Compiler) kompiliert, d.h. in einen Maschinencode umgewandelt. Einen Compilerlauf fr das Gesamtprojekt stt man ber Debuggen | Compilien von im VBA-Editor an. Im VBA-Editor stellen sie sicher, dass ber Ansicht

der Projektexplorer 1) das Eigenschaftsfenster (2) das Direktfenster (3) das berwachungsfenster (4)

zugeschaltet sind. ber Symbolleisten sind alle dort zu findenden Controls zuzuschalten. Insbesondere der Debugger

ist ein unverzichtbares Werkzeug beim Programmieren! Zum Erstellen von Code erzeugen sie zumindest ein Modul: Einfgen | Modul

Der Editor erzeugt eine Moduldatei Modul 1, in die Code geschrieben werden kann:

1 2

3 4



Der Projekt-Explorer (1) zeigt die vorhandenen Objektklassen (spezielle Moduldateien fr VBA-Code der die entsprechenden Objekte steuern kann): DieseArbeitsmappe Ereignisklasse fr die Datei Tabelle1..3 Ereignisklassen fr je eine der angelegten Tabellen Ereignisklassen dienen der Reaktion auf in der jeweiligen Klasse auftretende Ereignisse, z.B. :

Function Wrfel(Augen As Integer) As Integer Wrfel = Int(Rnd() * Augen) + 1

End Function



Oben im Bild will ich VBA-Code programmieren, wenn sich durch eine Eingabe der Wert einer Zelle (Worksheet_Change) in Tabelle1 ndert oder wenn der Benutzer eine Markierung in Tabelle1 anbringt oder ndert (Worksheet_SelectionChange). Die Programmierung von ereignisgesteuertem Code soll im Rahmen der Einfhrung erst spter angesprochen werden dies Beispiel erwhnen ich nur um das Vorhandensein dieser Standardmodulklassen und ihre Bedeutung zu erklren. Modul Codeklasse Im Rahmen der Einfhrung schreiben sie VBA-Code ausschlielich in ein Modul, um Makros zu speichern. Ein Modul ist ein Codecontainer fr Benutzermakros. Sie knnen beliebig viele Modulklassen anlegen. Achten sie darauf, in welches Modul sie hineinschreiben Titelzeile des Fensters beachten. Modul durch Doppelklick im Projekt-Fenster ffnen!



Debugger Programmieren sie eine MessageBox eine Nachricht ausgeben mit der Anweisung MsgBox: Die wichtigste Taste ist Strg+Leertaste, immer dann, wenn sie Hilfe vom Editor bentigen! Scheibe Ms Strg+Leertaste Liste der mglichen Eingaben wird angezeigt, mit Pfeiltasten Eingabe auswhlen und mit Tab-Taste bernehmen sie die entsprechende Codesequenz:

Wie bei den Tabellenfunktionen blendet VBA eine Eingabehilfe ein,

die die mglichen Parameter-Angaben des Codes beschreibt: Der Prompt, also die anzuzeigende Textmeldung wre die nchste zwingend anzugebende Information:

Angaben in eckigen Klammern sind optional. Beim Schreiben von Objektpfaden oder Anweisungslisten blendet der VBA-Editor die mglichen Optionen ein. Nachdem ich das den Prompt-Text abschlieende Komma geschrieben habe, stellt VBA eine Liste der mglichen Eingaben fr die Parameterliste [Buttons..] dar. Hier kann der Meldungsdialog mit verschiedenen Optionen ausgestattet werden, z.B. mit einem OK-Button und einem Abbrechen-Button:

Mehrere Optionen werden einfach per Addition angefordert, z.B. der Typ der Meldung. Die Auswahl erfolgt mit der Maus per Klick. Jede weitere Eingabe (vbc) schrnkt die Listenoptionen weiter ein, bis die Auswahl mit Tab-Taste bernommen werden kann! Im Direktbereich (rechts) kann jederzeit eine Codeeingabe erfolgen; abschlieen mit Eingabe-Taste Hier mit dem fertiggestellten Hallo Welt Code und das Ergebnis:

Kontrollieren sie, wie die einzelnen Parameter-Angaben der MsgBox-Anweisung im Dialog-Fenster umgesetzt worden sind!



Debuggen von Variablen Debugger Einzelschritt F8-Taste , wenn Schreibmarke in der auszufhrenden Sub/Function steht! In Frage kommenden Codeschritte durch-laufen: Marker zeigt nchsten auszu-fhrenden Schritt an

1. Variable markieren: OK 2. Debug:

Aktuellen Wert anzeigen und folgenden Dialog mit Hinzufgen abschlieen.

3. Im berwachungsfenster die Inhalte der Variablen nachlesen

Mit F8 im Einzelschritt durch den Code gehen und die MsgBox mit OK abschlieen -> die Variable OK hat dann den Wert 1 ! D.h. MsgBox liefert frs Klicken des OK-Button eine 1 sonst eine 0. Meist kann auch durch Anhalten des Mauszeigers an eine Variable deren Inhalt angezeigt werden. Der Debugger, Dreh- und Angelpunkt beim Analysieren und Verstehen von Code! ben Sie das Handling des Debuggers durch Einzelschritt-Verfolgung des hier zur Verfgung gestellten Beispielcodes.

Unter Bearbeiten Einfgen finden sie weitere Schreibhilfen: Strg+J Strg+I_ Strg+Umschalt+I Strg+Umschalt+J_

Makro-Sicherheit

Einstellungen fr Makros und Add-ins in Excel 2003 und frher Einmalige Makroeinstellung

Extras | Makro | Makros | Sicherheit | Mittel

Einstellungen fr Makros und Add-ins in Excel 2007 Office | Excel Optionen | Vertrauensstellungscenter | Einstellungen fr das Vertrauensstellungscenter | Generelle Makrofreigabe: Einstellungen fr Makros | Alle Makros aktivieren

Add-Ins mssen vom einem als vertrauenswrdig deklariertem Speicherort geladen werden Vertrauenswrdige Speicherorte

Makrodateien bentigen eine spezielle Dateierweiterung .xlam - .xlsm - .xltm

Temporre Add-In Nutzung Add-In per Doppelklick (oder per Datei | ffnen) laden Permanent beim Start von Excel laden: Extras | Add-Ins | Neu hinzufgen

xla von beliebigem Ordner, xlam von einem als vertrauenswrdig festgelegtem Ordner, oder vom Microsoft Add-Ins Ordner laden



Deklaration

von Speicherplatz kann, muss nicht erfolgen, ist aber ratsam, mit folgender Zeile wird die Variablendeklaration erzwungen 'Option Explicit 'Compileranweisung als Kommentar, also nicht aktiv, eingegeben. 'Zur Freischaltung wre das Hochkomma ( ' ) am Zeilenanfang zu lschen. Extras | Optionen | Editor | Variablendeklaration erforderlich Schalter fr die automatische Vereinbarung der Deklarationspflicht Dim a As Single, b As Double ' reserviere einen Speicherplatz auf den Namen a als einfach genaue Zahl (7 Stellen) ' reserviere einen Speicherplatz auf den Namen b als doppelt genaue Zahl (15 Stellen) Dim Zeile As String ' reserviere einen Speicherplatz auf den Namen Zeile als Zeichenfolge, ' Text immer in Anfhrungszeichen einpacken Dim Zahl As Integer ' reserviere einen Speicherplatz auf den Namen Zahl als eine ganze Zahl, ohne Kommastellen Dim Richtig As Boolean ' reserviere einen Speicherplatz auf den Namen Richtig als Wahr/Falsch-Wert, logische Entscheidung

Function

berechnen ein Ergebnis, richten einen Speicherplatz unter einem freiwhlbaren Namen ein, der als Zuweisung (=) mit dem Ergebnis der Berechnung belegt wird.

sind direkt in einem Tabellenblatt anwendbar, wie die eingebauten Tabellenfunktionen SUMME usw. auch - werden in der Kategorie Benutzerdefiniert gefhrt

Betrag:Eingang fr Werte aus der Tabelle

FixRabatt:Ausgang Wert fr Rckgabe in Tabellenzelle



Function FixRabatt(Betrag) As Single 'Deklaration (As Single) optional ' Rckgabewert der Funktion ist eine einfach genaue Zahl FixRabatt = Betrag * 0.19 End Function Function Rabatt(Betrag, Wert) As Single Rabatt = Betrag * Wert End Function 'Test der Funktion Rabatt im Tabellenblatt z.B. =RABATT(A1;5%) ' Wrfel fr VBA ' Rnd berechnet eine Zufallszahl zwischen 01 und wird mit 6 multipliziert ' nach Addition von 1 ergibt sich eine Zahl zwischen gerundet 1 und 6. Function Wrfel(Augen As Integer) As Integer

Wrfel = Int(Rnd() * Augen) + 1 End Function

Eine Funktion erhlt ber den Funktionskopf einen oder mehrere Werte bergeben - verarbeitet die Eingangswerte und gibt ein Ergebnis zurck. Der Rckgabewert wird in einer Zuweisung an den Namen der Funktion deklariert.

Benutzerdefinierte Tabellen-Funktionen Neben der Subroutine, die eine Automatisierung von Ablufen in der Tabellenkalkulation ermglicht gib es auch die Functions, ber die Tabellenfunktionen erstellt werden knnen. Die Anwendung einer Function unterscheidet sich nicht von der Anwendung der eingebauten Funktionen wie SUMME o.! Function BenutzerFunktion(Argumentliste) BenutzerFunktion = Rckgabe End Function Tabellen-Funktionen haben eine Argumentliste mit der die zu verarbeitenden Daten bergeben werden. Das Ergebnis der Funktion wird als Zuweisung an den Funktionsnamen zurckgegeben. Die Argumente sind in der Regel Bezge auf Tabellenzellen. nderungen an den Argumentzellen lsen die Neuberechnung der Funktion aus. Achten sie also darauf, dass alle Daten, die die Funktion verarbeiten soll auch ber die Argumentliste bergeben werden. Erstellen sie eine Funktion zur Berechnung des Salzgehaltes im Endprodukt Kochschinken. Function Salzgehalt_Endprodukt(Lakezugabe, Lakekonzentration, Kochverlust) Salzgehalt_Endprodukt = Lakezugabe*Lakekonzentration/((1+Lakezugabe)*(1-Kochverlust)) End Function Legen sie fr diese Funktion ein Modul an und legen sie den Code in diesem Modul ab. Sobald eine Function in einem Modul vorliegt, kann die Funktion als Tabellenfunktion eingesetzt werden. In Excel wird die Funktion in der Funktionskategorie "Benutzerdefinierte Funktion" eingetragen und wird wie alle anderen Tabellenfunktionen durch den Funktionsassistenten untersttzt. In OpenOffice werden Functions nicht im Funktionsassistenten eingebaut, knnen aber vom Anwender durch direkte Eingabe des Funktionsnamens genutzt werden. Programmieren sie die im Folgenden angegebene Funktion zur Berechnung des Osterdatums. Sie erweitern damit die Funktionalitt von Excel/Calc um eine allgemein Verwendbare bisher nicht vorhandene Formel.

Code 1:FixRabatt

Code 2:Wrfel



Vom Osterdatum aus berechnen sie alle beweglichen Feiertage befragen sie das Netz welche Feiertage wie vom Osterdatum abhngen: 'vereinfachte Osterformel 1900 bis 2078 (Norbert Hetterich) Function Ostern(Jahr as Integer) Ostern = Val(DateValue(Day(Minute(Jahr / 38) / 2 + 55) _ & ".4." & Jahr) / 7 + 0.5) * 7 - IIf(Year(1) = 1904, 5, 6) End Function Oder die BMI-Berechnung Function BodyMassIndex(Gewicht As Single, Grsse As Single) If Gewicht > 15 And Gewicht < 160 And Grsse > 0.6 And Grsse < 2.21 Then BodyMassIndex = Gewicht / Grsse ^ 2 Else BodyMassIndex = "#Angaben?" End If End Function Debuging von Function mit Argumenten Die Funktion BodyMassIndex bernimmt die Angaben zu Gewicht und Gre aus dem Tabellenblatt gewhnlich aus dafr vorgesehenen Zellen, d.h. der Debugger kann nicht einfach im VBA-Editor angestoen werden, weil dann keine Werte fr Gewicht und Gre vorhanden sind. Setzen sie einen Breakpoint (Haltepunkt) im Function-Code an dem die Codeausfhrung anhalten soll, wenn eine Berechnung der Funktion bei der Anwendung im Tabellenblatt ausgefhrt wird:

Klicken sie mit der Maus im Editor auf die Randleiste. Der Haltepunkt markiert die Stopp-Zeile mit einem roten Hintergrund. Wird nun im Tabellenblatt einer der Eingangswerte B2/C2 gendert, so wird die Funktion berechnet und der Code ausgefhrt. Im Bild sehen sie den Mauszeiger, der den Breakpoint auf die Zeile mit der IF-Anweisung gelegt hat. Ich habe die Funktion in B4 eingetragen und der Debugger hat an der Stopp-Zeile die Codeausfhrung angehalten. Mit F8 habe ich einen Code-Einzelschritt angestoen, der die IF-Anweisung ausgewertet hat und zur Else-Klausel verzweigt, weil die Gre in cm und nicht wie vorgesehen in Meter angegeben ist. Funktionen, die Argumente enthalten werden im Debugger mittels eines Haltepunktes untersucht! Sie stoen die Ausfhrung des Codes an indem sie einen Eingangswert aus der Argumentliste der Funktion ndern. Der Debugger stoppt die Codeausfhrung an dem Breakpoint ffnet den VBA-Editor und sie knnen die Funktion debuggen.



Funktionsbeispiele

Funktionenbeispiel Statistik

Berechnen sie Mittelwert und Standardabweichung der Strichlisten. Die Datenwerte der Klassen werden gemittelt. Fr besonders flexiblen Einsatz wre die Erstellung von Mako-Funktionen denkbar, etwa

Extras | Makro | Visual Basic-Editor | Einfgen | Modul

'------------------------------------------------------------------------------- Function GewichteterMittelwert(Werte, Anzahl) Dim i as Integer, s, n As Single For i = 1 To Anzahl.Count

s = s + Werte(i) * Anzahl(i) n = n + Anzahl(i)

Next GewichteterMittelwert = s / n End Function '-------------------------------------- Function GewichteteStabw(Werte, Anzahl) Dim i as Integer, s, q, n As Single For i = 1 To Anzahl.Count

s = s + Werte(i) * Anzahl(i) q = q + Werte(i) * Werte(i) * Anzahl(i) n = n + Anzahl(i)

Next GewichteteStabw = Sqr((n * q - s * s) / n / n) End Function

Extras | Makros | Makro verwalten | Makro ...Makro aus | aktive Datei | Standard | Neu

'-------------------------------------------------------------------------------- Function GewichteterMittelwert(Werte, Anzahl) Dim i as Integer,s, n As Single For i = 1 To Ubound(Anzahl)

s = s + Werte(i,1) * Anzahl(i,1) n = n + Anzahl(i,1)

Next GewichteterMittelwert = s / n End Function '-------------------------------------- Function GewichteteStabw(Werte, Anzahl) Dim i as Integer, s, q, n As Single For i = 1 To Ubound(Anzahl)

s = s + Werte(i,1) * Anzahl(i,1) q = q + Werte(i,1) * Werte(i,1) * Anzahl(i,1) n = n + Anzahl(i,1)

Next GewichteteStabw = Sqr((n * q - s * s) / n / n) End Function '--------------------------------------------------------------------------------

und die Anwendung der Funktionen

=GewichteterMittelwert(C2:C8;D2:D8) =GewichteteStabw(C2:C8;D2:D8)



xls A B C D E F

1 Gewichtsklasse g Gewicht Anzahl Mittelwert Standardabweichung 2 490 - 495 492,5 1 492,50 192,20 3 495 - 500 497,5 4 1990,00 78,56 4 500 - 505 502,5 5 2512,50 14,93 5 505 - 510 507,5 5 2537,50 1,29 6 510 - 515 512,5 4 2050,00 37,65 7 515 - 520 517,5 2 1035,00 124,02 8 520 - 525 522,5 1 522,50 260,38 9 506,36 7,53

Funktionenbeispiel Extrakt Verwenden sie das Datenblatt Produktion2.xls und setzen sie das folgende VBA-Makroprogramm fr Aufgabe 4 ein!

ALT+F11 MSOffice VBA-Editor ffnen und Einfgen Modul:

Function Extrakt(Bereich As Range, Index As Integer) Dim a As New Collection, b On Error Resume Next For Each b In Bereich a.Add b.Value, "!" & b.Value If a.Count = Index Then Extrakt = a(Index): Exit For Next End Function

oder ALT+F11 Openoffice Basic-Editor ffnen und Einfgen Modul

Function Extrakt(Bereich, Index) Dim i As Long, puffer As String puffer = "#" For i = 1 To UBound(Bereich,1)

If InStr(puffer, Bereich(i, 1)) = 0 Then puffer = puffer & Bereich(i, 1) & "#"

Else Index = Index + 1

End If If i = Index Then Exit For Next If Index



Aufgabe Beschreiben sie, wie sie folgenden Code als Tabellen-Funktion in Excel verfgbar machen! Gelingt das fehlerfrei oder mssen sie nachbessern. Welche Nachbesserung mssen sie ggf. durchfhren? Geben sie ein Beispiel fr die Anwendung der Funktion mit den im Kommentar gegebenen Daten. Function Ladekapazitt(l As Double, b As Double, d As Double) As Integer ' Die Ladeflche eines LKW l=13,6 x b =2,48 soll mit Rollen d=1 beladen werden ' Ladekapazitt in Rollen, wenn die Rollen Rolle an Rolle versetzt geladen werden oo Dim reihe As Integer, anzahl As Integer reihe = Int(b / d) anzahl = Int((l - d) / (Sqrt(d ^ 2 - ((b - d) / (reihe - 1) / 2) ^ 2))) If anzahl Mod 2 = 1 Then Ladekapazitt = reihe + (reihe-1) * ((anzahl-1) / 2 + 1) + reihe * (anzahl-1) / 2 Else Ladekapazitt = reihe + (reihe-1) * anzahl / 2 + reihe * anzahl / 2 End Function xls A B

8 l= 13,6 9 b= 2,48

10 d= 1,00 11 =



Sub, Subroutine (Prozedure) dienen als Blockanweisung, zusammenfassen von Makroanweisungen. Z.B. Anzeige einer Berechnung der Rabattfunktion Sub Tuwas() MsgBox Rabatt(20, 15 / 100) End Sub 'Aufruf ber Debugger (Schreibmarke muss in auszufhrender Sub stehen) oder 'im Tabellenblatt ber Extras | Makro Sub Wrfeln() Dim Wurf As Integer

Wurf = Wrfel(6) MsgBox Wurf

End Sub Die Prozedur Wrfeln richtet zuerst einen Speicherplatz Wurf ein; eine frisch initialisierte numerische Variable wird auf Null gesetzt (wichtig zu wissen)! Die Funktion Wrfeln wird mit dem Argument 6 (Augen) aufgerufen. Die Zuweisung an die Funktion Wrfel ist eine kleine Unsauberkeit, weil Rnd eine Zahl von Typ Single liefert, die in einer Integer-Variable gespeichert werden soll! Was tatschlich abluft sei hier ausfhrlich dargestellt: Wrfel = CInt(Rnd() * CSng(Augen)) + 1 Zuerst wird die Integer Variable zum Typ Single gewandelt (CSng Cast to Single), dann kann sie mit dem Single Wert Rnd() Zufallszahl multipliziert werden, das Ergebnis Single wird gewandelt nach Integer (CInt Cast to Integer) und dann erfolgt die Inkrementierung, eine Integer-Addition von 1. Andere Programmiersprachen wie C++, z.B. wrden die ausfhrliche Schreibweise verlangen, d.h. der Programmierer muss wesentlich mehr Detailwissen mitbringen BASIC ist da vergleichsweise bequem und wandelt (casted) automatisch die beteiligten Datentypen. Sub

fhren eine Aktion aus: eine Ttigkeit, wie sie von einem realen Benutzer von Excel veranlasst wird Bedienungsschritt in Excel oder Nachricht an Benutzer.

Werden oft als Makroaufzeichnung erstellt, z.B. die Nullanzeige ausschalten Sub NullAnzeigeAus() ' ' Makro1 Makro ' Makro am 28.01.2011 von Hans W. Hofmann aufgezeichnet ' ' ActiveWindow.DisplayZeros = False End Sub Aufzeichnungen des Makrorecorders bedrfen fast immer der Nacharbeit, um die Handlungslogik programmtechnisch abzubilden! Kippschalterlogik: Bei Nullanzeige ein (true) ausschalten (false) und umgekehrt Wir nehmen in diesem Beispiel bereits Bezug auf die XL-Objektstruktur, die die Excel Anwendung abbildet und dem Programmierer verfgbar macht. Sub NullAnzeigeEinAus()

ActiveWindow.DisplayZeros = Not ActiveWindow.DisplayZeros End Sub Einbinden in eine Schaltflche zur direkten Anwendung im Tabellenblatt

Code 3:NullAnzeige



2003 Extras | Anpassen | Kategorien | Makros 2003 Benutzerdefinierte Schaltflche in Symbolleiste ziehen 2003 per Rechtsklick: Makro zuweisen und ggf. Schaltflchensymbol berarbeiten 2007 Symbolleiste fr den Schnellzugriff | Weitere Befehle | Makros 2007 Schaltflche hinzufgen und | ndern

Bild 1: Schalter fr Symbolleiste/Schnellzugriffsleiste

' Das ganze in OO Starbasic Code Sub NullAnzeigeEinAus thisComponent.CurrentController.ShowZeroValues = _

NOT thisComponent.CurrentController.ShowZeroValues End Sub Sub FormulasEinAus thisComponent.CurrentController.ShowFormulas = _

NOT thisComponent.CurrentController.ShowFormulas End Sub

Code 4: Starbasic NullAnzeige



Schleifen, Wiederholungen For Next For Each Do Loop While 'Wiederholen bei bekannter Anzahl - geschlossene Schleife: ' Summe von 3 Wrfel-Versuchen Sub AugenBei3Wrfen() Dim Augen As Integer Dim i As Integer For i = 1 To 3 Augen = Augen + Wrfel(6) Next MsgBox "Mit 3 Wrfen wurden " & Augen & " Augen erzielt." End Sub 'Wiederholen bei unbekannter Anzahl - offene Schleife: ' Wrfle so lange bis eine 6 erzielt wurde Sub SechserWrfeln() Dim Versuche As Integer Do

Versuche = Versuche + 1 Loop Until Wrfel(6) = 6 MsgBox "Mit " & Versuche & " Versuch(en) eine 6 erzielt." End Sub 'Ersetzen sie until durch while, wie muss die Prfbedingung dann lauten? 'Welches Ergebnis erhalten sie, wenn die while Bedingung unmittelbar nach Do steht? Die in Schleifen bentigten Index-Variablen sollten aus Geschwindigkeitsgrnden immer passend als Integer oder Long Datentyp gewhlt werden. Die For Next-Schleife ist eine geschlossene Schleife, d.h. man wei genau wie viele Durchlufe zu einem Endergebnis bentigt werden. Im Gegensatz zu den offenen Schleifen wie Do Loop oder Do While, wo eine Bedingung die Wiederholung der Schleife steuert. Bei Do While wird die Wiederholungsbedingung am Schleifenbeginn getestet in Gegensatz zu Do Loop, was dazu fhren kann, dass die Schleife berhaupt nicht durchlaufen wird wie bei der For Next-Schleife, wenn der Startwert grer ist als der Endwert. Eine Do Loop-Schleife dagegen wird immer mind. einmal durchlaufen.

Code 5: Schleifen



Verzweigungen

' Entscheidungen If Then Else End If Select Case End Select IIF(), Choose(), Switch() Die drei letzt genannten Methoden stellen einzeilige Auswahlentscheidungen dar. Es werden immer alle Bedingungen und Ausdrcke berechnet. Sie eignen sich also nicht um ungltige Operationen abzufangen (z.B. IIF( n=0 , a , a/n ) erzeugt einen Laufzeitfehler 'Division by Zero' Division durch Null, weil auch der Ausdruck a/n berechnet werden muss. Sub GeradeUngerade() Dim Wurf As Integer Wurf = Wrfel(6) If Wurf Mod 2 = 1 Then MsgBox "Ungerade Augenzahl " & Wurf & " gewrfelt." Else MsgBox "Gerade Augenzahl " & Wurf & " gewrfelt." End If End Sub Sub bersetze() Dim Wurf As Integer Wurf = Wrfel(6) Select Case Wurf Case 1 MsgBox "Eins" Case 2 MsgBox "Zwei" Case Else MsgBox "mehr als 2 Augen gewrfelt" End Select End Sub

Code 6: If Then Else



Arrays

' Tabellen in Speichervariablen ablegen, indexierte Variablen sind indizierte Datenfelder mit einer oder mehreren Dimensionen. Statische Felder werden bei der Definition mit ihren Unter, bzw. Obergrenzen festgelegt: Dim Liste(1 to 10) As Integer erzeugt 10 ganzzahlige Speicherpltze, die durch einen Index in Klammern Liste(1)Liste(10) adressiert und unterschieden werden. Eine zweidimensionale Matrix aus Zahlenwerten (hier 10*4 Variable, z.B. als Tabelle(5,1) angesprochen, wrde vereinbart werden als Dim Tabelle(1 to 10, 1 to 4) As Integer. Variable Felder, die ihre Dimension erst zur Laufzeit erhalten werden ohne Indizes definiert und mit ReDim zur Laufzeit auf die bentige Dimension gebracht: Dim IsAList() as Integer, n As Integer n=5: ReDim IsAList(1 to n) As Integer Ein Variant kann auch als Feld auftreten. Dim Zeile As Variant Zeile = Array(1,"Zwei","Drei",4.6,True,14) Die Array-Funktion erzeugt Felder mit dem Start-Index 0, d.h. Zeile(0) = 1! Debugging eines Arrays:

Sub WrfelText() Dim Liste Liste = Array("Null", "Eins", "Zwei", "Drei", "Vier", "Fnf", "Sechs") Do Loop While MsgBox(Liste(Wrfel(6)), vbExclamation + vbRetryCancel, "Wrfel") = vbRetry End Sub 'Varainte Was ist der Unterschied? 'Do While MsgBox(Liste(Wrfel(6)), vbExclamation + vbRetryCancel, "Wrfel") = vbRetry 'Loop Beispiel Eine Prozedur soll auf einem markierten Zellenbereich die Formatierung um eine sogn. Nullschablone ergnzen, die die Anzeige der Null verhindert also ausblendet. Beginnen sie mit einer Aufzeichnung, die auf einer Markierung die Null-Schablone anbringt:

Code 7:Array



ber die Aufzeichnung erfahren Sie welcher Objektpfad fr die Formatierung zustndig ist. Wir haben 2 Unwgbarkeiten zu bercksichtigen: 1. Der Aufbau der Formatschablone kann

unterschiedlich sein: 0,1,2 oder 3 Strichpunkte mit den entsprechenden Schablonen fr positiv; negativ; null; text

2. Eine Markierung kann unterschiedlich viele Zellen umfassen und jede Zelle kann eine eigene Formatschablone haben.

D.h. wir sollten die Selection zellenweise durchgehen und die Formatschablone fr jede einzelne Zelle ndern.

Sub FormatMitNullAusSchablone() ' Formatbeschreibung um eine Schablone fr die Darstellung der Null ergnzen ' Lesen sie nach, welche Aufgabe die Funktion Split hat Dim cell As Range, fmt As Variant For Each cell In Selection.Cells 'NumberFormat ist eine Strichpunkt getrennte Liste Postiv;Negativ;Null;Text 'Split splittet die Liste am ; auf und stellt die Teile in einen Array fmt = Split(cell.NumberFormat, ";") 'Ubound bestimmt die obere Indexgrenze von fmt Select Case UBound(fmt) Case 0 ' nur positiv-Schablone vorhanden

cell.NumberFormat = fmt(0) & "; -" & fmt(0) & ";" Case 1, 2 ' positiv- & negativ- oder Null-Schablone vorhanden

cell.NumberFormat = fmt(0) & ";" & fmt(1) & ";" Case 3 ' alle Schablonen vorhanden

cell.NumberFormat = fmt(0) & ";" & fmt(1) & "; ;" & fmt(3) End Select Next End Sub Aufgabe/bung Erstellen sie eine Funktion, die aufgrund einer Punktezahl von 0100 eine Schulnote 16 berechnet. Function Note(Punkte As Single, Schlssel As Variant, Komma As Integer) As Single ' Schlssel ist ein Array mit Schlssel(1)=maxPunkte ' Schlssel(2)=Punkte Notengrenze 1.5, Schlssel(3)=Punkte Notengrenze 2.5 ... ' Schlssel(7)=Punkte Notengrenze 6.0 [Anwendung: =Note(B2;{100.92.80.67.50.25.0};0)] Select Case Punkte Case Is >= Schlssel(2) Note = 1# + (Schlssel(1) - Punkte) / (Schlssel(1) - Schlssel(2)) / 2 Case Is >= Schlssel(3) Note = 1.5 + (Schlssel(2) - Punkte) / (Schlssel(2) - Schlssel(3)) Case Is >= Schlssel(4) Note = 2.5 + (Schlssel(3) - Punkte) / (Schlssel(3) - Schlssel(4)) Case Is >= Schlssel(5) Note = 3.5 + (Schlssel(4) - Punkte) / (Schlssel(4) - Schlssel(5)) Case Is >= Schlssel(6) Note = 4.5 + (Schlssel(5) - Punkte) / (Schlssel(5) - Schlssel(6)) Case Is > Schlssel(7) Note = 5.5 + (Schlssel(6) - Punkte) / (Schlssel(6) - Schlssel(7)) / 2 Case Else Note = 6 End Select Note = Round(Note - 5 * 10 ^ (-Komma - 3), Komma) End Function

Code 8: FormatMitNullAusSchablone



VB-Bibliotheken

' Der Werkzezugkasten von Visual Basic

Blicken sie mit dem Objektkatalog in den Werkzeugkasten fr VB VBA Bibliothek. Links unter Klassen finden sie die Werkzeuge fr Strings, Funktionen zur Bearbeitung von Zeichenfolgen. Elemente von Strings listet die Funktionen auf, die zur Bearbeitung von Textfolgen zur Verfgung stehen. Fr die Arbeit mit den Funktionen verschaffen sie sich erstmal einen berblick. Die Anwendung lesen sie in der OH nach (das ist eine Grundsatzbung beim Programmieren) vor allem die Beispiele geben einen ganz guten Einblick. Leistungen der VB-Bibliothek:

Split-Funktion Beschreibung Gibt ein nullbasiertes, eindimensionales Datenfeld zurck, das eine festgelegte Anzahl an untergeordneten Zeichenfolgen enthlt. Syntax Split(expression[, delimiter[, limit[, compare]]]) Die Syntax der Split-Funktion besteht aus folgenden benannten Argumenten:

Teil Beschreibung

expression Erforderlich. Zeichenfolgenausdruck, der untergeordnete Zeichenfolgen und Trennzeichen enthlt. Wenn expression eine Zeichenfolge der Lnge Null ("") ist, gibt Split ein leeres Datenfeld zurck, d. h. ein Datenfeld ohne Elemente und ohne Daten.

delimiter Optional. Zeichen einer Zeichenfolge, mit der die Grenzen von unter-geordneten Zeichenfolgen identifiziert werden. Wird es ausge-lassen, wird das Leerstellenzeichen (" ") als Trennzeichen verwendet. Wenn delimiter eine Zeichenfolge der Lnge Null ist, wird ein aus einem Element bestehendes Datenfeld, das die gesamte Zeichenfolge von expression enthlt, zurckgegeben.

Limit Optional. Anzahl der zurckzugebenden untergeordneten Zeichenfolgen; 1 gibt an, dass alle untergeordneten Zeichenfolgen zurckgegeben werden.

compare Optional. Numerischer Wert, der die Art des Vergleichs angibt, der beim Beurteilen von untergeordneten Zeichenketten verwendet werden soll. Werte finden sie im Abschnitt Einstellungen.



Formularsteuerelemente (Formularcontrols) ' Entwicklertools Einfgen Sind interaktive Schalter zum Aufruf von Makros oder zur Manipulation von Tabellenzellen. Einfacher aufgebaut sind Formularsteuerelemente, whrend ActiveX-Controls komplexer zu programmieren, aber auch leistungsfhiger sind. Bleiben wir bei den Formularelementen und widmen den ActiveX-Control ein spteres Kapitel. Im Vorgriff auf das Objektmodell eine kurze Objektbeschreibung der Controls: Alles was auf einer Tabelle sonst noch so rum liegt ist ein Shape und wie alle mehrfach vorkommenden

Objekte werden diese in einer Shapes-Liste (Aufzhlung, vgl. Array) zusammengefasst:

ActiveSheet.Shapes(1) ' Erstes Object der Liste Shapes ActiveSheet.Shapes(2).OLEFormat.Object ' Objectmodell des 2.ten Controls Schaltflche

Werden ggf. beim Ein-zeichnen mit einem Makro verbunden und im Kontextmen finden sie spezifischen Ein-richtungsfunktionen. (die zwei am Ende der Liste) Einmal markiert, knnen sie das Control beschriften und formatieren.

Das Objektmodell der Controls liegt unter .OLEFormat.Object : Hier finden sie alle Eigenschaften der Controls, wie sie auszugsweise durch die Kontextmens angeboten werden fr den Zugriff via VBA. .Caption enthlt die Beschriftung .Height die Objekthhe .Width die Objektbreite .Left den Abstand vom linken Rand .Top den Abstand vom oberen Rand .Value den Wert .LinkedCell die verknpfte Zelle .ZOrder die Ebene der Lage 0=unten Alle Eigenschaften und ggf. Methoden finden sie im Objektkatalog die Funktion von einigen lsst sind schon allein durch ihre Wortbedeutung erahnen ersetzen aber nicht das Nachlesen!

Unter .OnAction finden sie z.B. das verknpfte Makro, das der Schalter beim Klick ausfhrt.

Bild 2: Beispiele fr Formularcontrols



Excel und seine Objektwelt VBA ist zwar eine prozedurale Sprache, hat aber einige objektbezogene Anstze. Und weil die ganze Applikationswelt spter aus Objekten aufgebaut ist, wagen sie vielleicht einen Blick in die objektbezogene Programmierung mit VBA. Objekte sind hierarchisch aufgebaut, von einer zentralen Wurzel aus fhren Pfade zu dem Objekt, das angesprochen werden soll. Zwischen den Objektebenen schreiben sie einen Punkt: z.B. die aktive Zelle Application.ActiveCell, oder die zweite Tabelle der ersten Excel-Mappe Application.Workbooks(1).Sheets(2). Vereinfachenden geschrieben knnen sie die Wurzel Application auch einsparen und weglassen:

Bild 3: Objekte des Excel-Fensters

Alle mehrfach vorkommenden Objekte werden in Auflistungen (hnlich den Feldern, Arrays, von VB) zusammengefasst, die ein Mehrzahl s am Objektsnamen angehngt bekommen. Z.B.: Sheet Sheets Workbook Workbooks



Die Tabellen (Tabelle1, Tabelle2, Tabelle3) sind mehrfach vorhandene Objekte einer XL-Mappe (Workbook) damit muss eine Listenstruktur, eine Aufzhlungsliste existieren, die alle Tabellen-Objekte enthlt ein Container der alle Tabellen zusammenfasst. Die einzelnen Elemente werden durchnummeriert und knnen ber ihren Index angesprochen werden: Tabelle1 = Sheets(1), Tabelle2 = Sheets(2), usw.. Die Elemente einer Auflistung lassen sich ber ihren Index oder ber ihren Namen ansprechen. Es gibt also evtl. ein Workbooks(1), das enthlt ein Sheets(1) gleichbedeutend Sheets(Tabelle1), wenn Sheets(1).Name = Tabelle1 ist. Tabelle1 ist Bestandteil von Workbooks(1) mit Workbooks(1).Name = Mappe1.xlsx, was dann auch als Workbooks(Mappe.xlsx) angesprochen werden kann. Eine Tabelle besteht aus Zeilen, Spalten, Zellen. Das erzwingt die Exisitenz von entsprechenden Listen zur Speicherung der Objekte: Tabelle1.Rows(1) reprsentiert in Tabelle1 die erste Zeile und Tabelle1.Columns(1) steht dann fr die erste Spalte. Eine einzelne Zelle wrde ich also mit dem Objektpfad Tabelle1.Cells(3,1) erreichen. Die Liste Cells erwartet zuerst die Zeilennummer und dann die Spaltennummer. Fr Zellenbereiche, wie sie z.B. in XL-Formeln =SUMME(D1:D3), eingesetzt werden gibt es das Objekt RANGE Range impliziert sprachlich bereits die Mehrzahl ist eine Liste, die Zellenbereiche adressieren kann: RANGE(D1:D3) wre die Entsprechnung des in der Summen-Funktion angegebenen Zellenbereiches. Haben sie eine Zelle, eine Tabelle, eine XL-Mappe ausgewhlt, dann wird sie zur aktiven Zelle, zur aktiven Tabelle usw.: Active (ActiveCell, ActiveWindow, ActiveSheet, ActiveWorkbook) bezieht sich immer auf ein ausgewhltes Objekt und ist damit eine Formulierung bei der auf Listen und Indizes verzichtet werden kann! In der folgenden bung stelle ich eine kleine Rechenaufgabe durch VBA-Anweisungen dar:

Beschriften von 3 Spalten mit den Texten Menge, Preis, Gesamt In die Zeilen darunter gebe ich in zwei Zeilen Werte fr Menge und Preis ein Es folgen die Formeln zur Berechnung Menge*Preis Und der Berechnung der Gesamtsumme Anwenden von Zahlenformatierungen Und ndern der Textfarbe fr das Ergebnis

Die VBA-Anweisungen erfolgen ber das Direktbereich-Fenster im VBA-Editor: Ansicht | Direktfenster (Strg+G) Schieben sie das VBA-Editor-Fenster auf die Seite und stellen sie das Direktfenster, wie im Screenshot zu sehen, so auf, dass sie die Ergebnisse im Tabellenfenster beobachten knnen! Die Kommentare sind zu Ihrer Information, sie mssen natrlich nicht eingegeben werden und haben auch keine Auswirkung auf die Ergebnisse der bung!



bung mit dem VBA-Debugger

Bild 4: TabellenZugriffe.xlsm - Codebeispiele

ActiveCell.Offset(-2,2) Zum Ausfhrungszeitpunkt ist A9 die aktive Zelle Offset verweist auf die Zelle -2 Zeilen nach oben und 2 Spalten nach rechts, also C7 und nimmt Bezug auf die Eigenschaft FormulaLocal um eine Formel in der lokalen Spache (Deutsch) zu setzen. Tabelle1.Cells(1,3).Value Den Wert von Zelle Zeile 1 Spalte 3, C1, Range("A11").Formula = "=Average(A8:A10)" Aktive Tabelle Zelle A11 eine Formel fr den MITTELWERT setzen Range("A5:C5").Formula = Array( 100, 14.5, "=A5+B5") In Tabelle1 in dem Zellenbereich A5 bis C5 die via Array gegebenen Werte in die Formula-Eigenschaft schreiben Range("A12").FormulaLocal = "=Summe(A8:A11)" ForumlaLocal schreibt eine Formel in der nationalen Schreibweise in die Zelle A12 Range("A12").Font.Color = RGB(90, 200, 100) In der Zelle 12 die Zeichenfarbe auf die Farbe ndern. RGB(rot-wert,grn-wert,blau-wert), Farbwerte zwischen 0255. Workbooks(1).Sheets(1).Range("A1:A10").NumberFormat = "0.00 " In Tabelle1 in dem Zellenbereich A1 bis A10 die Formatschablone (Benutzerdefiniertes Format: Achtung englische Notation fr Schablone) setzen Test = ActiveWorkBook.Sheets(1).Name In der aktiven Datei den Namen der ersten Tabelle anzeigen.



Test = WorkBooks(Mappe1.xlsx).Sheets(Tabelle1).Cells(4,4).Formula In der Datei Mappe1.xlsx (die msste dann auch geffnet werden) in der Tabelle mit dem Namen Tabelle1 in der Zelle D4 die Formel in der intenationalen Schreibweise in dem Speicher Test schreiben. SUMME() wird international geschrieben als SUM().

VBA Help bzw. Entwicklerreferenz online http://msdn.microsoft.com/en-us/library/office/ff846392.aspx

Ein Auszug der Objektbeschreibung von RANGE

Excel 2010-Entwicklerreferenz > Excel-Objektmodellreferenz > Range-Objekt

Elemente des Range-Objekts Alle anzeigen

Stellt eine Zelle, eine Zeile, eine Spalte, eine Auswahl von Zellen aus einem oder mehreren zusammenhngenden Zellblcken oder einen 3D-Bereich dar.

Methoden

Name Beschreibung

Activate Aktiviert eine einzelne Zelle, die sich innerhalb der aktuellen Markierung befinden muss. Verwenden Sie die Select-Methode, um einen Zellbereich zu markieren.

Calculate Diese Methode berechnet alle geffneten Arbeitsmappen, ein bestimmtes Arbeitsblatt einer Arbeitsmappe oder einen bestimmten Zellbereich in einem Arbeitsblatt (siehe Tabelle unten).

Clear Lscht das gesamte Objekt.

ClearContents Lscht die Formeln aus dem Bereich.

ClearFormats Lscht die Formatierung des Objekts.

Copy Kopiert den Bereich in den angegebenen Bereich oder in die Zwischenablage.

Cut Schneidet das Objekt aus, legt es in die Zwischenablage oder fgt es an einer angegebenen Stelle ein.

Delete Lscht das Objekt.

Group Wenn das Range-Objekt eine einzelne Zelle im Datenbereich des PivotTable-Felds darstellt, fhrt die Group-Methode eine Gruppierung nach Zahlen oder Datum in diesem Feld durch.

PasteSpecial Fgt ein Range-Objekt aus der Zwischenablage in den angegebenen Bereich ein.

PrintOut Druckt das Objekt.

Replace Gibt einen BooleanBoolean-Wert zurck, der Zeichen in Zellen des angegebenen Bereichs kennzeichnet. Bei Verwendung dieser Methode wird weder die Markierung noch die aktive Zelle gendert.

Run Fhrt das Microsoft Excel-Makro an der aktuellen Position aus. Der Bereich muss sich in einer Makrovorlage befinden.

Select Markiert das Objekt.

Ungroup

Hebt die Gruppierung eines Bereichs in einer Gliederung auf (stuft den Bereich um eine Gliederungsebene herunter). Bei dem angegebenen Bereich muss es sich um eine Zeile oder Spalte bzw. um einen Bereich von Zeilen oder Spalten handeln. Wenn der Bereich in einem PivotTable-Bericht liegt, wird die Gruppierung fr die im Bereich enthaltenen Elemente aufgehoben.

UnMerge Lst einen zusammengefhrten Bereich in einzelne Zellen auf.

Excel 2010 Entwickler-Referenz

javascript:AlterAllDivs('block');javascript:AlterAllDivs('block');https://msdn.microsoft.com/de-de/library/office/ee861528.aspx



Eigenschaften

Name Beschreibung

Address Gibt einen String-Wert zurck, der den Bereichsbezug in der Sprache des Makros darstellt.

AddressLocal Gibt den Bereichsbezug fr den angegebenen Bereich in der Sprache des Benutzers zurck. Schreibgeschtzter String-Wert.

AllowEdit Gibt einen Boolean-Wert zurck, der angibt, ob der Bereich in einem geschtzten Arbeitsblatt bearbeitet werden kann.

Areas Gibt eine Areas-Auflistung zurck, die alle Bereiche in einer Mehrfachmarkierung darstellt. Schreibgeschtzt.

Borders Gibt eine Borders-Auflistung zurck, die den Rahmen einer Formatvorlage oder eines Zellbereichs darstellt (einschlielich eines Bereichs, der als Teil eines bedingten Formats definiert ist).

Cells Gibt ein Range-Objekt zurck, das die Zellen im angegebenen Bereich darstellt.

Characters Gibt ein Characters-Objekt zurck, das einen Bereich von Zeichen innerhalb des Objekttexts darstellt. Mit dem Characters-Objekt knnen Sie Zeichen innerhalb einer Zeichenfolge formatieren.

Column Gibt die erste Spalte am Anfang des angegebenen Bereichs als Zahl zurck. Schreibgeschtzter Long-Wert.

Columns Gibt ein Range-Objekt zurck, das die Spalten im angegebenen Bereich darstellt.

ColumnWidth Gibt die Breite aller Spalten im angegebenen Bereich zurck oder legt sie fest. Variant-Wert mit Lese-/Schreibzugriff.

Comment Gibt ein Comment-Objekt zurck, das den Kommentar darstellt, der mit der Zelle in der linken oberen Ecke des Bereichs verknpft ist.

Count Gibt einen Long-Wert zurck, der die Anzahl der Objekte in der Auflistung darstellt.

End Gibt ein Range-Objekt zurck, das die Zelle am Ende der Region darstellt, die den Quellbereich enthlt. Diese Methode entspricht dem Drcken von ENDE + NACH-OBEN, ENDE + NACH-UNTEN, ENDE + NACH-LINKS oder ENDE + NACH-RECHTS. Schreibgeschtztes Range-Objekt.

EntireColumn Gibt ein Range-Objekt zurck, das eine oder mehrere ganze Spalten darstellt, die den angegebenen Bereich enthalten. Schreibgeschtzt.

EntireRow Gibt ein Range-Objekt zurck, das eine oder mehrere ganze Zeilen darstellt, die den angegebenen Bereich enthalten. Schreibgeschtzt.

Errors Ermglicht dem Benutzer, auf die Optionen fr die Fehlerprfung zuzugreifen.

Font Gibt ein Font-Objekt zurck, das die Schriftart des angegebenen Objekts darstellt.

Formula Gibt einen Variant-Wert zurck, der die Formel des Objekts in der A1-Schreibweise und in der Sprache des Makros darstellt, oder legt diesen fest.

FormulaArray Gibt die Arrayformel eines Bereichs zurck oder legt diese fest. Gibt eine einzelne Formel oder ein Visual Basic-Array zurck bzw. kann auf ein solches festgelegt werden. Wenn der angegebene Bereich keine Arrayformel enthlt, gibt diese Eigenschaft null zurck. Variant-Wert mit Lese-/Schreibzugriff.

FormulaHidden Gibt einen Variant-Wert zurck, der angibt, ob die Formel ausgeblendet ist, wenn das Arbeitsblatt geschtzt ist, oder legt diesen Wert fest.

FormulaLocal Gibt die Formel fr das Objekt unter Verwendung von A1-Bezgen in der Sprache des Benutzers zurck oder legt diese fest. Variant-Wert mit Lese-/Schreibzugriff.

FormulaR1C1 Gibt die Formel fr das Objekt in der Z1S1-Schreibweise in der Sprache des Makros zurck oder legt diese fest. Variant-Wert mit Lese-/Schreibzugriff.

FormulaR1C1Local Gibt die Formel fr das Objekt in der Z1S1-Schreibweise in der Sprache des Benutzers zurck oder legt diese fest. Variant-Wert mit Lese-/Schreibzugriff.

HasArray True, wenn die angegebene Zelle Teil einer Arrayformel ist. Schreibgeschtzter Variant-Wert.

HasFormula True, alle Zellen im Bereich Formeln enthalten. False, wenn keine Zelle im Bereich eine Formel enthlt. Andernfalls null. Schreibgeschtzter Variant-Wert.

Height Gibt einen Variant-Wert zurck, der die Hhe des Bereichs in Punkten darstellt, oder legt diesen fest.

Hidden Gibt einen Variant-Wert zurck, der angibt, ob die Zeilen oder Spalten ausgeblendet sind, oder legt



diesen fest.

HorizontalAlignment Gibt einen Variant-Wert zurck, der die horizontale Ausrichtung fr das angegebene Objekt darstellt, oder legt diesen Wert fest.

Hyperlinks Gibt eine Hyperlinks-Auflistung zurck, die die Hyperlinks fr den Bereich darstellt.

Left Gibt einen Variant-Wert zurck, der den Abstand (in Punkt) vom linken Rand der Spalte A zum linken Rand des Bereichs darstellt.

Name Gibt einen Variant-Wert zurck, der den Namen des Objekt darstellt, oder legt diesen Wert fest.

Next Gibt ein Range-Objekt zurck, das die nchste Zelle darstellt.

NumberFormat Gibt einen Variant-Wert zurck, der den Formatierungscode fr das Objekt darstellt, oder legt diesen Wert fest.

NumberFormatLocal Gibt einen Variant-Wert zurck, der den Formatierungscode des Objekts als Zeichenfolge in der Sprache des Benutzers darstellt, oder legt diesen Wert fest.

Offset Gibt ein Range-Objekt zurck, das einen Bereich darstellt, der gegenber dem angegebenen Bereich versetzt ist.

Parent Gibt das bergeordnete Objekt fr das angegebene Objekt zurck. Schreibgeschtzt.

Previous Gibt ein Range-Objekt zurck, das die nchste Zelle darstellt.

Row Gibt die Nummer der ersten Zeile im ersten Bereich zurck. Schreibgeschtzter Long-Wert.

RowHeight Gibt die Hhe aller Zeilen im angegebenen Bereich in Punkt zurck. Gibt null zurck, wenn die Zeilen im angegebenen Bereich nicht alle dieselbe Hhe haben. Variant-Wert mit Lese-/Schreibzugriff.

Rows Gibt ein Range-Objekt zurck, das die Zeilen im angegebenen Bereich darstellt. Schreibgeschtztes Range-Objekt.

Text Gibt den Text des angegebenen Objekts zurck oder legt ihn fest. Schreibgeschtzter String-Wert.

Top Gibt einen Variant-Wert zurck, der den Abstand (in Punkt) vom oberen Rand der Zeile 1 zum oberen Rand des Bereichs darstellt.

Value Gibt einen Variant-Wert zurck, der den Wert des angegebenen Bereichs darstellt, oder legt diesen fest.

Value2 Gibt den Zellwert zurck oder legt ihn fest. Variant-Wert mit Lese-/Schreibzugriff.

Width Gibt einen Variant-Wert zurck, der die Breite des Bereichs in Einheiten darstellt.

Worksheet Gibt ein Worksheet-Objekt zurck, das das Arbeitsblatt mit dem angegebenen Bereich darstellt. Schreibgeschtzt.

WrapText Gibt einen Variant-Wert zurck, der angibt, ob der Text im Objekt in Microsoft Excel umbrochen wird, oder legt diesen Wert fest.

2010 Microsoft Corporation. Alle Rechte vorbehalten.



Auflistungen - Listen

Fast alle Objekte haben eine Eigenschaft Parent (Eltern, Vorgnger), die auf den Vorgnger in der Objekthierarchie verweist.

Bild 5: bersicht Objektstrukturen

.Count, Eigenschaft die die Anzahl der Objekte in der Aufzhlung angibt, .Add, Methode der Auflistung ein Objekt zu zufgen. .Name, Eigenschaft eine Zeichenfolge, die das Objekt eindeutig benennt und als Index in der

bergeordneten Listenstruktur verwendet werden kann Eine Eigenschaft wie .Count steht fr einen schreibgeschtzten Zahlenwert, genauer ActiveSheets.Count steht fr die Anzahl der vorhandenen Tabellen eines Workbook. Schreibgeschtzt deshalb, weil mittels der Methode .Add eine weitere Tabelle erzeugt und in die Auflistung aufgenommen wird, was .Count um einen Zhler aufstockt. Die Delete-Methode dagegen zhlt .Count runter, wenn ein Tabellenblatt entfernt wird. Whrend die Eigenschaft .Name mit Schreib-Lese-Zugriff ausgestattet und damit programmtechnisch per Zuweisung nderbar ist (.Name="NeuerName"). Wie oben erwhnt kann Name auch hier als Index der Liste Verwendung finden, so bezeichnen z.B. Sheets(3) und Sheets(Tabelle1) die gleiche Tabelle, wenn Sheets(3).Name = Tabelle1 lautet. Der Name leitet sich z.B. bei Workbooks vom Dateinamen ab. Eine Methode ist ein Programm, eine Funktion, die etwas mit einem Objekt anstellt. Die Add-Methode veranlasst alle Schritte, um eine neue Tabelle zu erzeugen (ein Tabellen-Objekt zu instanzieren) und geeignet zu speichern - genau so, als wenn sie auf Einfgen | neue Tabelle klicken. Wenn sie nun die Tabelle wieder entfernen wollen, wo wre dann die entsprechende Methode zu suchen? Gehen wir doch einmal den Objektpfad entlang und arbeiten alle Informationen ein, die eine diesbezgliche Methode bentigt: Es muss eine Datei benannt werden: ActiveWorkbook Welche Tabelle .zB.: .Sheets(5) Die Tabelle muss "wissen", eine Methode haben, wie sie entfernt werden kann: ActiveWorkbook.Sheets(5).Delete



Oft setzt man einen Objektverweis ein, wie z.B.: Set sl = ActiveWorkbook.Sheets(1) Das Schlsselwort ist Set und speichert in sl einen Verweis, einen Zeiger, auf ein Objekt. Insbesondere ist sl nicht das Objekt selber! In diesem Fall wird, wie sie wissen, die Tabelle1 der aktuelle ExcelDatei angesprochen. Wird eine andere Datei/Workbook ausgewhlt Active , so verweist sl nach wie vor auf die alte Datei und nicht auf das jetzt aktuelle Workbook! Lschen sie die Tabelle ActiveWorkbook.Sheets(5).Delete so verweist der Zeiger sl nun auf einen Speicherbereich, der ein nicht mehr vorhandenes Tabellenobjekt adressiert. Sl zeigt noch alle Eigenschaften einer Tabelle ist aber ein Phantom, da ja die Tabelle nicht mehr existiert sie als Programmierer mssen dafr Sorge tragen, das der Zeiger sl zurckgesetzt wird: Set sl = Nothing



bung Produktion-Lagerbuchhaltung

In Zeile 3 erfassen sie einen einfachen Produktionsvorgang mit Datum, Menge und Artikel(Bezeichnung) der in einem Lager verbucht werden soll. Const Datum = 1 Const Artikel = 2 Const Menge = 3

Sub Lagerbuchung()

Set Eingabe = Range("A3:C3") Set Lager = Range("A11:C19")

Lager(Eingabe(Artikel), Datum) = Eingabe(Datum) Lager(Eingabe(Artikel), Menge) = _ Lager(Eingabe(Artikel), Menge) + Eingabe(Menge)

End Sub

Sub Lager1einrichten()

Lagerartikel = Array("Einserl", "Zweierl", "Dreierl", _ "Viererl", "Fnferl", "Sexserl", _

"Siebenerl", "Achterl", "Neunerl") SollMenge = Array(150,100,200,200,250,25,75,75,100)

Range("B11:B19").Value = WorksheetFunction.Transpose(Lagerartikel)

For Zlager = 0 To UBound(SollMenge) Cells(11, 4).Offset(Zlager) = SollMenge(Zlager) Next

End Sub

Sub Zufallsverkauf()

With WorksheetFunction z = .RandBetween(11, 19) Cells(z, 3) = .RandBetween(5, Cells(z, 3))

End With End Sub

Sub suchLetzteLagerbewegung()

Set Lager = Range("A11:A19") letzte = CVDate(WorksheetFunction.Max(Lager))

Set letzte = Lager.Find( _ What:=letzte, _

LookAt:=xlWhole) letzte.Select MsgBox "Letzte Lagerbuchung " & letzte.Text, _

vbInformation, "Lagerbuchhaltung"

End Sub

Sub ZufallsProduktion() With WorksheetFunction

[A3].Value = .RandBetween(Now()-7,Now()+7) [B3].Value = .RandBetween(1, 9)

[C3].Value = .RandBetween(20, 75) End With Call Lagerbuchung

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Art As Range If Target.Row = 3 And Target.Column = 2 Then

Set Art = Range("C10").Offset(Target.Value) Target.Offset(0,1).Value = Art.Offset(0, 1) - Art.Value End If

End Sub

HWNotizSub ZufallsProduktion()With WorksheetFunction[B3].Value = .RandBetween(1, 9)[A3].Value = .RandBetween(Now() - 7, Now() + 7)mge = [D10].Offset([B3].Value) - [C10].Offset([B3].Value)If mge > 0 Then [C3].Value = .RandBetween(1, mge)Else Exit SubEnd IfEnd WithCall LagerbuchungEnd Sub



Sub

Eing

abeL

sch

en

Im Z

elle

nber

eich

A2:

E30

solle

n al

le Z

elle

n le

erge

schr

iebe

n w

erde

n un

d di

e Ze

lle A

2 so

ll zu

r wei

tere

n Da

tene

inga

be a

ktiv

iert

wer

den!

2.

Wel

che

Opt

imie

rung

des

auf

geze

ichn

eten

Cod

es k

nne

n Si

e vo

rsch

lage

n?

3. W

as b

ewirk

t der

Mak

roco

de K

opie

Eint

rag,

wen

n er

dur

ch d

en S

chal

ter

Abla

ge zu

r Aus

fhr

ung

gebr

acht

wird

? Le

sen

sie d

ie F

unkt

ion

von

evtl.

un

beka

nnte

n M

akro

code

s nac

h (s

iehe

Sei

te 2

6 ?

4. E

rste

llen

sie d

iese

n M

akro

und

den

daz

ugeh

rig

en S

chal

ter z

ur

Ausf

hru

ng d

es M

akro

code

s!

5. W

elch

e Co

deop

timie

rung

schl

agen

sie

vor?

Eine

XL-

Date

i ent

hlt

die

zwei

Tab

elle

n

"Tag

espr

oduk

tion"

und

"Pro

dukt

ions

log"

. In

der T

abel

le "T

ages

prod

uktio

n"

wer

den

die

Prod

uktio

nsda

ten

fr e

inen

Tag

(24.

07.1

2) e

inge

trag

en u

nd in

de

r Tab

elle

"Pro

dukt

ions

log"

solle

n al

le P

rodu

ktio

nsda

ten

zur A

usw

ertu

ng

aufg

esam

mel

t wer

den.

Ein

Mak

ro so

ll di

e Da

ten

von

der T

abel

le

Tage

spro

dukt

ion

in d

ie T

abel

le P

rodu

ktio

nslo

g ko

pier

en u

nd a

n di

e vo

rhan

dene

n Da

ten

ansc

hlie

en.

0.

Bea

chte

n sie

die

For

mel

in E

1! E

ntne

hmen

sie

dem

Bild

: Ac

tiveC

ell=

?,

Activ

eShe

et=?

, Ac

tiveW

orkb

ook=

?

1. E

rste

llen

sie fo

lg. M

akro

dur

ch A

ufze

ichn

en m

it de

m M

akro

-Rec

orde

r und

le

gen

sie e

inen

Sch

alte

r zur

Aus

fhr

ung

an:



Makro bung und Code-Optimierung

Erstellen sie die auf letzter Seite vorgestellten Tabellen Tagesproduktion und ProduktionsLog. Geben sie Daten entsprechend den Vorgaben in Tabelle Tagesproduktion ein. Legen sie eine Modulklasse im VBA-Editor an und erstellen sie folg. VBA-Code: Sub KopieEintrag() 'Feststellen wie viele Zeilen in ProduktionsLog schon vorhanden sind Z = ActiveSheet.Range("E1") + 1 'Datenkopieren mit ' ActiveWorkbook und ActiveSheet Sheets(2).Cells(Z, 1).Value = Cells(ActiveCell.Row, 1).Value 'Kopieren sie diese Zeile 4 mal um neben der Spalte 1 auch Spalte 2,3,4,5 'nach ProduktionsLog zu schreiben Sheets(2).Cells(Z, 2).Value = Cells(ActiveCell.Row, 2).Value Sheets(2).Cells(Z, 3).Value = Cells(ActiveCell.Row, 3).Value Sheets(2).Cells(Z, 4).Value = Cells(ActiveCell.Row, 4).Value Sheets(2).Cells(Z, 5).Value = Cells(ActiveCell.Row, 5).Value End Sub Erstellen sie einen Schalter aus den Formularsteuerelementen und weisen sie den Makro zur Ausfhrung an den Schalter zu. Verwenden sie aus Grnden der einfacheren Anwendung unbedingt Formularsteuerelemente und vermeiden sie ActiveX-Steuerelemente die wesentlich aufwndiger in der Programmierung sind. Testen sie den Makro und tragen sie einige Datenzeilen in Tabelle Tagesproduktion ein und schreiben sie die Daten mit dem Makro in die Tabelle ProduktionsLog. 1. Spielen sie ein paar Anwendungsflle des Makros

durch. berlegen sie ob eine Optimierung des Datenflusses erfolgen sollte?

2. Welche Routineaufgaben knnte der Makro gleich mit erledigen?

Bild 6: Formularschalter

Optimieren der Befehlsfolge fr die Spalten 15

Sheets(2).Cells(Z, 1).Value = Cells(ActiveCell.Row, 1).Value Sheets(2).Cells(Z, 2).Value = Cells(ActiveCell.Row, 2).Value Sheets(2).Cells(Z, 3).Value = Cells(ActiveCell.Row, 3).Value Sheets(2).Cells(Z, 4).Value = Cells(ActiveCell.Row, 4).Value Sheets(2).Cells(Z, 5).Value = Cells(ActiveCell.Row, 5).Value durch eine geschlossene For-Schleife:

Z = WorksheetFunction.Count(Sheets(2).Columns(1)) - 1 Zeile = 3 ' (Zeile = 3,4,5...) For Spalte = 1 To 5 ' ActiveWorkbook und ActiveSheet von Spalte 1 ...5 Sheets(2).Cells(Z + Zeile, Spalte).Value = Cells(Zeile, Spalte).Value Next



Nehmen sie die oben beschriebenen nderungen an dem Code vor: In der Variablen Z berechnen sie die Anzahl der in ProduktionsLog geschriebenen Zeilen in dem sie die Funktion ANZAHL der Bibliothek WorksheetFunction entnehmen und damit ohne Rckgriff auf eine Tabellenformel auskommen ein Beispiel, wie sie Tabellenfunktionen direkt im VBA-Code verwenden knnen. Fr den Arbeitsfluss wre es auch interessant die gesamte Tagesproduktion in einem Durchgang zu schreiben und nicht fr jede Zeile einzeln einen Schreibvorgang anzustoen. Der Makro wrde immer in A3 (Zeile = 3) beginnen und solange die Datenspalten 15 nach ProduktionsLog scheiben, wie Daten eingegeben wurden. Dazu liest der Makro der Reihe nach die Zellen A3, A4, A5, Findet er einen Eintrag (Datum) in der ersten Spalte, so wird die For-Schleife abgearbeitet. Dazu konstruieren sie eine offene Schleife, die beim Erreichen einer leeren Zelle in Spalte 1 abbricht: ' Anzahl beschriebene Zeilen in ProduktionsLog (Sheets(2)) ' verwenden von Tabellenfunktionen im VBA-Code Z = WorksheetFunction.Count(Sheets(2).Columns(1)) - 1 Zeile = 3 ' Datenkopieren von Zeile ab A3 bis Leerzeile ' solange wie ab Zeile 3 Spalte 1 Werte > 0 stehen While Cells(Zeile, 1).Value > 0 ' Solange in Spalte A, Zeile ein Datum > 0 gelesen wird For Spalte = 1 To 5 ' ActiveWorkbook und ActiveSheet von Spalte 1 ...5 Sheets(2).Cells(Z + Zeile, Spalte).Value = Cells(Zeile, Spalte).Value Next Zeile = Zeile + 1 ' ActiveSheet nchste Zeile (Zeile = 3,4,5...) Wend

Verfolgung des Programmablaufs mit dem Debugger

Bild 7: Video Debuggerlauf des Makrocodes KopieEintrag

Codeoptimierung ChargenNr Check

Vor dem Kopieren der Datenstze wird die ChargenNr berprft, damit keine doppelt vergebenen Chargennummern vorkommen knnen

Lschen der nach ProduktionsLog bertragenen Daten in TagesProduktion Vorgaben fr neuen Datensatz Beim Auswhlen einer Zelle in Spalte A, E werden Datum und Chargennummer vorgegeben.

Deklarieren von Speicherplatz-Variablen



Sub KopieEintrag3() Dim Z as Integer, Zeile as Integer ' ? Check Chargennummer in Tabelle ProduktionsLog ? While Range("A3").Offset(Z, 0).Value > 0 ' Statt Cells kommt das Range-Objekt zum Einsatz wobei Offset die Zeilen adressiert If checkChargenNr(Cells(Z + 3, 5).Value) Then Cells(Z + 3, 5).Select ' Nachricht an den Anwender absetzen - MessageBox MsgBox "Chargennummer " & Cells(Z + 3, 5).Value & " bereits verarbeitet!", _ vbOKOnly + vbExclamation, "Chargenfehler" Exit Sub End If Z = Z + 1 Wend ' die End-Methode liefert die letzte Zelle am Ende der angebebenen Richtung Z = Sheets(2).Columns(1).End(xlDown).Row + 1 'Datenkopieren von Zeile ab A3 bis Leerzeile While Range("A3").Offset(Zeile, 0).Value > 0 ' ActiveWorkbook.Sheets(2).Range() = ActiveSheet.Range() With Sheets(2) .Range(.Cells(Z + Zeile, 1), .Cells(Z + Zeile, 5)).Value = _ Range(Cells(Zeile + 3, 1), Cells(Zeile + 3, 5)).Value End With Zeile = Zeile + 1 Wend EingabeLschen3 End Sub Function checkChargenNr(chnr) ' durchsuche in Tabelle ProduktionsLog Spalte 5 nach chnr (Chargennummer) With Sheets(2) ' die Find-Methode liefert Nothing zurck, wenn keine chnr gefunden wurde checkChargenNr = Not (.Range(.Cells(2, 5), .Cells(2, 5).End(xlDown)).Find(chnr) _ Is Nothing) End With End Function Sub EingabeLschen3() ' EingabeLschen Makro ' Makroaufzeichnung Range("A3:E30").ClearContents Range("A3").Value = Date Range("E3").FormulaLocal = "=MAX(ProduktionsLog!E:E)" Range("E3").Formula = Range("E3").Value Range("B3").Select End Sub ' Datenkopieren von Zeile ab A3 bis Leerzeile ohne Schleife ' Berechnen des zu schreibenden Datenblockes Z = Sheets(2).Columns(1).End(xlDown).Row + 1 dZ = Cells(3, 5).End(xlDown).Row - 3 With Sheets(2) .Range(.Cells(Z, 1), .Cells(Z + dZ, 5)).Value = _ Range(Cells(3, 1), Cells(3, 5).End(xlDown)).Value End With ' ergibt Sheets(2).Range("A5:A7").Value= Range("A3:A5").Value fr Bild Seite 31

Code 9:ProduktionsLog



VBA Tabellenzugriffe

Zellen und Zellenbereiche Einzelne Zelle: B5

Range("B5") oder Cells(5,2)

Gesamter Zellenblock B1:B10 Range("B1:B10") Range(Cells(1,2),Cells(10,2))

Einzelne Zellen in einem Bereich Einzelne Zelle aus einem Zeilenbereich B1:B10

For z=1 To 10: Cells(z,2): Next For z=0 To 9: Range("B1").Offset(z,0): Next For Each Zelle in Range("B1:B10"): Zelle: Next

Einzelne Zelle aus einem Spaltenbereich B4:H4

For s=2 To 8: Cells(4,s): Next For s=0 To 6: Range("B4").Offset(0,s): Next For Each Zelle in Range("B4:H4"): Zelle: Next

Einzelne Zelle aus einem Tabellenbereich B2:H9

For Each Zelle in Range("B2:H9"): Zelle: Next For z=0 To 7 For s=0 To 6: Range("B2").Offset(z,s): Next Next For z=2 To 9 For s=2 To 8: Cells(z,s): Next Next

Objektzeiger Set Bereich = Range("B2:H10") Set Bereich = Range(Cells(2,2),Cells(10,8) For Each Zelle in Bereich: Zelle: Next For i=1 To Bereich.Count: Bereich(i): Next

End-Zelle einer Region (Strg+Pfeil) zusammenhngender Bereich B1:C10 aus leeren Zellen bzw beschriebenen Zellen bestimmt durch 1.te Zelle

.End(xlDown) 'nach unten

.End(xlToLeft) 'nach Links

.End(xlToRight) 'nach rechts

.End(xlUp) 'nach oben Zelle bei Strg + Pfeil in B1 Zelle bei Strg + Pfeil und Strg + Pfeil in B1 Zelle in Spalte 1 bei A1+Strg+ Zelle in Zeile 1 bei A1+Strg+

Range("B1:B10").End(xlDown) Range("B1:B10").End(xlDown).End(xlToRight) Columns(1).End(xlDown) Rows(1).End(xlToRight)

Formel einer Zelle (international) Formel einer Zelle (Landessprache) Wert einer Zelle Formatschablone (international) Zelle oder Zellenbereich auswhlen Zelle oder Zellenbereich leeren Zellenbereich durchsuchen

Range("B1").Formula Range("B1").FormulaLocal Cells(1,1).Value Cells(5,1).NumberFormat Range("B1:B10").Select Range("A3:E30").ClearContents Range("A3:E30").Find("Text")

Selektierte Zelle/Zellen

Selection Selection.Font Selection.Address



Ereignis-Klassen

Die VBA-Untersttzung ist nun in den wesentlichen Punkten programmiert. Bei der wiederholten Anwendung stren die immer wieder kehrenden Routinen der Datumseingabe und der korrekten Wahl der Chargennummer hier wre noch Optimierungsbedarf. Die Idee: Wenn ich eine Zelle der Spalte A Columns(1) auswhle, dann knnte VBA eigentlich reagieren und gleich das Datum und die Chargen-nummer vorgeben, oder? Das ist jetzt was Neues reagieren auf Ereignisse, (ein Event: hier das Auswhlen einer Zelle). Es wird Zeit einen Blick in die Ereignis-Klassen zu werfen.

Bild 8: Ereignis-Prozedure Tagesproduktion

Jede Excel Datei enthlt bereits vorstrukturierte Module, die Ereignis-Klassen: Diese Arbeitsmappe fr Ereignisse auf Dateiebene (Workbook) und Tabelle1 ff. fr jede Tabelle (Worksheet) je eine. Objekte in VBA haben Eigenschaften, Methoden und Ereignisse. Fr die Objekte Workbook und Worksheet werden Ereignis-Prozeduren zur Verfgung gestellt, die beim Auftreten eines Ereignisses aufgerufen werden: Die Ereignisprozedur Sub Worksheet_SelectionChange reagiert auf jede nderung der Zellenauswahl in Tabelle1. Richten sie in der Ereignisklasse Tabelle1 (TagesProduktion) eine Testprozedur ein: Whlen sie in der Objekt-Liste (1) Worksheet aus und ggf. in der Prozedur-Liste (2) SelectionChange Sub Worksheet_SelectionChange(ByVal Target As Range)

Msgbox ("nderung Aktive-Zelle") End Sub und untersuchen sie die Wirkung der Prozedur. ndern sie die Nachricht zu Msgbox("nderung Aktive-Zelle: " & Target.Address) Weitere Ereignisse finden sie in der Prozedur-Liste (2). Lesen sie ggf. im Objektkatalog auf welches Ereignis, auf welche Benutzeraktion, welche Ereignis-Prozedur reagiert. Manches Ereignis erklrt sich aus der Procedure-Bezeichnung wie z.B. Workbook_Open Ereignis beim ffnen einer Exceldatei WorkSheet_Calculate Ereignis wenn die Tabelle neu berechnet wird usw.

'Ereignis-Prozedure Tagesproduktion 'Bei jedem Wechsel der ActiveCell, Wechsel der Zellen-Auswahl, 'in der Tabelle1 wird die Prozedure _SelectionChange aufgerufen Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 And Target.Row > 2 _ And Target.Cells.Count = 1 And Target.Cells(1).Value = 0 Then Target.Value = Date Cells(Target.Row, 2).Select End If If Target.Column = 5 And Target.Row > 2 _ And Target.Cells.Count = 1 Then Target.FormulaLocal = "=MAX(MAX(ProduktionsLog!E:E)+1;" & _ WorksheetFunction.Max(Range("E:E")) & "+1)" Target.Formula = Target.Value Cells(Target.Row + 1, 1).Select End If End Sub

1 2



Setzen den im Bild 8: Ereignis-Prozedure Tagesproduktion Seite 36 (vorangehende Seite) angegebenen Code ein (Codezeile Msgbox entfllt): Erst die erste IF-Abfrage (If Target.Column = 1 And Target.Row > 2), dann testen und dann die 2. If-Abfrage (If Target.Column = 5 And Target.Row > 2) ergnzen, testen! Untersuchen sie die Wirkung der Ereignis-Prozedur: Setzen sie Breakpoints in den Abfrageblcken und erkunden sie wann welcher Code abgearbeitet wird. ffnen sie die Ereignisklasse Diese Arbeitsmappe, stellen sie das Objekt Workbook ein, untersuchen sie welche Ereignisse in einem Workbook verarbeitet werden knnen. Testen und kommentieren sie folg. Code: Private Sub Workbook_Open() ' Make2Windows Makro-Aufzeichnung angepasst If Windows.Count = 1 Then ActiveWindow.NewWindow End If If Windows.Count = 2 Then Windows.Arrange ArrangeStyle:=xlVertical Windows(Me.Name & ":2").Activate Sheets(2).Activate Windows(Me.Name & ":1").Activate Sheets(1).Activate End If End Sub Excel-Entwicklerreferenz

Workbook.Open-Ereignis

Tritt auf, wenn die Arbeitsmappe geffnet wird.

Syntax Ausdruck.Open

Ausdruck Ein Ausdruck, der ein Workbook-Objekt zurckgibt.

Beispiel In diesem Beispiel wird Microsoft Excel bei jedem ffnen der Arbeitsmappe maximiert.

Visual Basic fr Applikationen

Private Sub Workbook_Open() Application.WindowState = xlMaximized End Sub



bung Lagerbuchung Betrachten wir eine Situation in der in einer Tabelle ein Rezept und in einer anderen Tabelle eine Rohstoff(Datenbank) mit einem Lagerbestand gefhrt wird. Die in der Rezeptur fr die Chargierung eingesetzten Rohstoffmengen sollen in der Datenbank vom Bestand abgezogen werden. Das diese Aufgabe nicht von Formeln abgedeckt werden kann ist klar:

Rez A B C

1 Rezept Es ist ein einmaliger Vorgang, der erst nach Fertigstellung der Chargenmengen ausgefhrt werden soll und dabei die Menge der Rezeptur von den Bestandsmengen der eingesetzten Rohstoffe abgezogen werden soll.

8 9 ArtNr Menge Bezeichnung

10 17 30 R1 11 32 40 S6 12 0 13 0

------------------ Roh A B C D E F G H

1 ======================================================= 2 Tabelle der Materialien:

3 Artikelnummer ber INDEX-Kodierung: 01..9 Zutaten / 10..19 Rindfleisch

4 20..29 Schweinefleisch / 30..39 Kalbfleisch 5 ======================================================= 6 Bezeichg H2O % Fett % FE % BEFFE Preis/kg FW-Punkte Lagerbestand

17 R1 75 4 21 19,5 11,2 150 380 18 R2 72 8 20 17 10,15 118 180 19 R3 63 20 17 12,7 8,45 92 400 31 S5 55 28 17 11,9 5,8 55 500 32 S6 50 35 15 10,5 5,2 45 100

bernehmen sie den Code in die Tabellenkalkulation und kommentieren sie die einzelnen Programmschritte indem sie die Funktionalitten fr sich verstndlich aufschreiben: VBA:--------------------------------------------------------------------------------------------------- Option Explicit Sub Lagerbuchung2() Dim Menge As Single Dim i As Integer, ArtNr As Integer For i = 10 To 24 Menge = Tabelle1.Cells(i, 2).Value ArtNr = Tabelle1.Cells(i, 1).Value If ArtNr > 0 Then Tabelle2.Cells(ArtNr, 8).Value = Tabelle2.Cells(ArtNr, 8).Value Menge End If Next MsgBox "Mengen vom Rohstoffbestand abgebucht...", vbInformation, "Lager" End Sub



ooBasic:--------------------------------------------------------------------------------------------- ' ActiveSheet as Object ' ActiveSheet = ThisComponent.CurrentController.ActiveSheet Sub Lagerbuchung2() Dim Tabelle1 as Object, Tabelle2 as Object Dim Menge as Single Dim i As Integer, ArtNr as Integer Tabelle1 = ThisComponent.getSheets.getByName("Rezept") Tabelle2 = ThisComponent.getSheets.getByName("Rohstoffe") 'xRay ThisComponent For i = 9 To 23 Menge = Tabelle1.getCellByPosition(1,i).value ArtNr = Tabelle1.getCellByPosition(0,i).value If ArtNr > 0 Then Tabelle2.getCellByPosition(7,ArtNr-1).value = _ Tabelle2.getCellByPosition(7,ArtNr-1).value - menge Endif Next MsgBox "Mengen vom Rohstoffbestand abgebucht...", 0+64, "Lager" End Sub Grundstzlich lsst sich feststellen, dass die Objekthierarchie von VBA fr Programmieranfnger verstndlicher kommentiert, flacher (weniger umfangreich aber auch unvollstndig) und stringenter als die API von Openoffice ist. Der VBA Debugger ist auch wesentlich Leistungsfhiger als der von ooBasic. Dim Festlegen der zu verwendenden Speichervariablen Tabelle1, Tabelle2 Objekte, die ein Tabellenblatt beschreiben. Sind in VBA vordefinierte Objekte. Mssen in ooBasic erst ber einen Objektpfad abgeleitet werden. For Next Schleife Beginnt in Zeile 10 (ooBasic nummeriert die Zeilen/Spalten beginnend mit 0) und verarbeitet die Daten bis Zeile 24. Auslesen der Menge aus den Zellen B10:B24 und der Artikelnummer aus A10:A24. Mit den in Menge und ArtNr gespeicherten Angaben konstruiere ich einen Zugriff auf die Rohstoffdatenbank , wenn eine Artikelnummer > 0 vorgefunden wurde. Um den Code mglichst einfach zu halten, verwende ich eine Artnr die der Zeilennummer im Tabellenblatt entspricht.

Lagerbuchung.xls Lagerbuchung.ods

Beispielmakros fr Excel bzw. OpenOffoce Calc Und noch eine Calc/Excel kompatible Version. OpenOffice verfgt seit Version 3.0 auch ber einen eingeschrnkten VBA Support, der als Option gewhlt wird:



option VBASupport 1 Sub LagerbuchungVBA() Dim Menge As Single Dim i As Integer, ArtNr As Integer Dim Tabelle1 as Object, Tabelle2 as Object Set Tabelle1 = ActiveWorkbook.Sheets("Rezept") Set Tabelle2 = ActiveWorkbook.Sheets("Rohstoffe") For i = 10 To 24 Menge = Tabelle1.Cells(i, 2).Value ArtNr = Tabelle1.Cells(i, 1).Value If ArtNr > 0 Then Tabelle2.Cells(ArtNr, 8).Value = Tabelle2.Cells(ArtNr, 8).Value - Menge End If Next MsgBox "Mengen vom Rohstoffbestand abgebucht...", vbInformation, "Lager" End Sub

Soweit so gut... Unser Makro bucht die Rezeptmengen vom Lager ab. Den Fall, dass der Lagerbestand nicht ausreicht haben wir jedoch noch nicht behandelt. Beschreiben sie das strategische Vorgehen im Fall einer nicht ausreichenden Bestandsmenge! Ergnzen sie das Beispiel fr obigen Makro:

Alg A B C E

1 Fr Zeile 10 bis 24 2 Lese Rezept.ArtNr 3 Lese Rezept.Menge 4 Wenn Rezept.ArtNr vorhanden 5 Lese Rohstoffe.ArtNr.Bestand 6 ziehe Menge ab 7 Speichere Neu.Rohstoffe.ArtNr.Bestand 8 Nachricht habe Fertig

Idee: In der (1.) Schleife alle Bestandswerte zwischenspeichern und einen BestandUnterschrittenMerker setzen wenn ein Artikel Bestand negativ wird. Schleifendurchlauf beendet. Wenn BestandUnterschrittenMerker nicht gesetzt wurde in einer zweiten Schleife die zwischengespeicherten Bestandswerte in die Rohstofftabelle schreiben. Wurde der BestandUnterschrittenMerker gesetzt ohne Aktualisierung des Bestandes nur eine Nachricht absetzen.



Alg A B C D

1 Fr Zeile 10 bis 24 2 Lese Rezept.ArtNr 3 Lese Rezept.Menge 4 Wenn Rezept.ArtNr vorhanden 5 Lese Rohstoffe.ArtNr.Bestand 6 ziehe Menge ab 7 Speichere in Bestand(Zeile) 8 Speichere ArtNr in Rohstoff(Zeile) 9 Wenn Bestand(Zeile)0 16 Speichere Bestand(Zeile) in Rohstoffe.ArtNr.Bestand 17 Nachricht habe Fertig

Sub Lagerbuchung3() Dim Menge As Single Dim i As Integer, ArtNr As Integer Dim Bestand(10 To 24, 1 To 2) Dim BestandUnterschritten As Boolean Dim FehlBestand As String For i = 10 To 24 Menge = Tabelle1.Cells(i, 2).Value ArtNr = Tabelle1.Cells(i, 1).Value If ArtNr > 0 Then Bestand(i, 1) = Tabelle2.Cells(ArtNr, 8).Value - Menge Bestand(i, 2) = ArtNr If Bestand(i, 1) < 0 Then FehlBestand = FehlBestand & ArtNr & " - " BestandUnterschritten = True End If End If Next If BestandUnterschritten Then MsgBox "Rohstoffmenge im Lager nicht ausreichend..." & vbCrLf & _ "- Fehlbestand bei Artikel " & FehlBestand, vbCritical, "Lager" Else For i = 10 To 24 If Bestand(i, 2) > 0 Then Tabelle2.Cells(i, 8).Value = Bestand(i, 1) End If Next MsgBox "Mengen vom Rohstoffbestand abgebucht...", vbInformation, "Lager" End If End Sub

Code 10:Lagerbuchung



Ribbon UI Men-Registerkarten selbst bauen Die Multifunktionsleiste, das Ribbon User Interface, hat Einzug in Office 2007 gehalten. Kaum hat man damit gearbeitet stellt sich die Frage, wie kann man das Ribbonkonzept anpassen. VBA ist zur Programmierung der Benutzeroberflche, der Steuerelemente und Symbol-Schaltflchen nicht notwendig, nicht vorgesehen sondern es kommt XML, eXtensible Markup Language zum Einsatz. Die Multifunktionsleiste, die ganze Benutzeroberflche von Office wird durch eine Textdatei beschrieben. Ohne auf die Details der Implementierung ein zu gehen, verweise ich auf die zahlreichen Fundstellen auf dem MSDN-Server von Microsoft. Aus diesem Angebot ldt man zumindest den Office 2007 Custom UI Editor (dieser Suchbegriff fr Google liefert eine Vielzahl von interessanten Fundstellen) und die Excel-Dateien, Office2007IconsGallery.EXE und 2007OfficeControlIDsExcel2007.EXE.

Aus dem MSDN-Artikel Erweitern Sie Office 2007 mit eigenen Multifunktionsleisten-Registerkarten und Steuerelementen (customizing ribbon user interface) stammt die folg. Aufstellung:

1. Multifunktionsleiste (Ribbon): Der groe rechteckige Bereich ber dem Dokument wird als Multi-funktionsleiste bezeichnet. Sie enthlt die Titelleiste, die Office-Schaltflche, die Schnellzugriffs-Symbolleiste und die Registerkarten. RibbonX bezieht sich hauptschlich auf die Multifunktionsleisteund alles, was sich darin befindet.

2. Office-Schaltflche (officeMenu oder Backstage ab 2010 ): Diese Schaltflche ruft das Office-Menauf, das etwa dem Dateimen in frheren Office-Versionen entspricht. Das Office-Men enthltBefehle, die sich auf Dokumente auswirken und nicht so sehr auf den Inhalt von Dokumenten.RibbonX-Add-Ins knnen den Inhalt des Office-Mens frei verndern (obwohl sie die Office-Schaltflche selbst nicht anpassen knnen).

3. Schnellzugriffs-Symbolleiste (qat): Diese Symbolleiste enthlt hufig verwendete Befehle und ist derHauptbereich, in dem Endbenutzer Anpassungen vornehmen knnen. Benutzer knnen mit derrechten Maustaste auf ein beliebiges Steuerelement der Multifunktionsleiste klicken und es derSchnellzugriffs-Symbolleiste hinzufgen (einschlielich benutzerdefinierter RibbonX-Steuerelemente).Da dies der Bereich ist, der dem Endbenutzer gehrt, drfen RibbonX-Add-Ins normalerweise nichtdie Schnellzugriffs-Symbolleiste verndern, es sei denn, dass der StartFromScratch-Modus aktiviertwurde.

4. Registerkarten (tabs) unterteilt in (tab): Die Registerkarten bilden den Hauptinhalt der Multifunk-tionsleiste und enthalten Benutzeroberflchen-Steuerelemente, die sich auf den Inhalt des jeweiligenDokuments auswirken. RibbonX-Add-Ins knnen eigene benutzerdefinierte Registerkarten er-stellenund die Ansicht und Beschriftungen der integrierten Registerkarten ndern.

5. Kontextbezogene Registerkartenstze (contextualTabs) unterteilt in (tabSet): Bei Auswahl vonObjekten wie z. B. Bildern oder Tabellen innerhalb des Dokuments werden kontextbezogeneRegisterkartenstze angezeigt, die alle Benutzeroberflchen-Elemente zur Handhabung dieser Objekteenthalten. Durch RibbonX-Add-Ins knnen die Ansicht von integrierten Registerkarten gendert undbenutzerdefinierte Registerkarten hinzugefgt werden. Eine Funktion, die in Office 2007-Anwendungen nicht untersttzt wird, ist die Erstellung von benutzerdefinierten, kontextbezogenenRegisterkartenstzen. Registerkartenstze enthalten kontextbezogene Registerkarten, die sich an-sonsten genau wie normale Registerkarten verhalten.

6. Gruppen (group): Registerkarten enthalten Stze von Gruppen, die wiederum einzelne Benut-zeroberflchen-Steuerelemente enthalten. RibbonX-Add-Ins knnen die Ansicht von integriertenGruppen verndern und eigene benutzerdefinierte Gruppen erstellen. Allerdings knnen sie nicht denInhalt integrierter Gruppen ndern. Diese Einschrnkung schtzt das Benutzeroberflchen-Layout undAdd-Ins vor Konflikten miteinander und mit zuknftigen Versionen von Office. Optional habenGruppen in der Ecke Dialogfelder-Startprogramme, die fr die Gruppe relevante Dialoge an-zeigen (z.B.die Schriftart- oder Absatzdialoge).

7. Aufgabenbereiche (taskPane): Es gibt auch bei Office 2007 noch mehrere Aufgabenbereiche, und jetztist es mglich, mehrere auf einmal zu ffnen. COM-Add-Ins knnen nun CustomTaskPanes erstellen,die Inhalt wie beispielsweise ActiveX-Steuerelemente oder Windows Forms-Steuerelemente hosten.

http://msdn2.microsoft.com/en-us/office/aa905530.aspxhttp://openxmldeveloper.org/articles/customuieditor.aspxhttp://openxmldeveloper.org/articles/customuieditor.aspxhttp://www.google.de/search?q=Office+2007+Custom+UI+Editorhttp://www.microsoft.com/downloads/details.aspx?familyid=12b99325-93e8-4ed4-8385-74d0f7661318http://www.microsoft.com/downloads/details.aspx?FamilyID=4329D9E9-4D11-46A5-898D-23E4F331E9AE&displaylang=en



(Die CustomTaskPane-Funktion unterscheidet sich von RibbonX und wird in diesem Artikel nicht behandelt).

8. MiniToolbar: Die MiniToolbar ist eine Ansammlung von hufig verwendeten Formatierungsbefehlen, die ber ausgewhlten Texten und ber Kontextmens fr die rechte Maustaste angezeigt wird. RibbonX-Add-Ins knnen den Inhalt der MiniToolbar nicht ndern, aber sie knnen die integrierten Befehle deaktivieren oder fr andere Zwecke verwenden.

9. Kontextmens: Dies sind dieselben, mit rechtem Mausklick aufrufbaren Kontextmens, die wir alle aus frheren Office-Versionen kennen und zu schtzen wissen. In Office 2007 wird RibbonX nicht fr Kontextmens angewendet, doch sie knnen wie in der Vergangenheit mit dem CommandBars-Objektmodell erweitert und angepasst werden.

10. Statusleiste: Die Statusleiste enthlt mehrere ntzliche, neue Steuerelemente, wie z. B. Wrter zhlen und Schieberegler anzeigen. Die Statusleiste kann in Office 2007 nicht mit Add-Ins ange-passt werden, aber sie kann ausgeblendet werden.

So erstellen sie ein benutzerdefiniertes Men-Register

Fr die Anpassung des Ribbon interessieren mich vor allem das

Hinzufgen neuer Registerkarten (tab) Platzieren vorhandener Steuerelemente (controls wie Schaltflchen.button,

Wahlschalter.togglebutton, Auswahllisten.listbox usw) auf neuen und vorhandenen Registerkarten Hinzufgen von Steuerelementen zur Bedienung von Makros Definieren von eigenen Tastenkombinationen

Gehen Sie ab MSO 2010 wie in folgendem Screenshot beschrieben vor. Anwender von MSO 2007 bleiben erst mal auen vor sie mssen sich gedulden, bis wir den Aufbau der erzeugten Steuerdatei analysiert und fr die Verwendung in Excel-Dateien verfgbar gemacht haben. Datei | Optionen | Menband anpassen

Neue Registerkarte anlegen

Neue Registerkarte (Benutzerdefiniert) Umbenennen in Prfungsaufgaben Neue Gruppe(Benutzerdefiniert) Umbenennen in Konstruieren Neue Gruppe einfgen Umbenennen in Quiz Befehle Auswhlen Makros Einfgen in Gruppe Quiz Makroeintrag Umbenennen in Antworten ab/aus und ein Symbol auswhlen

alle gewnschten Makroschalter in die ents

Excel VBA Programmierung -...

Documents

Transcript of Excel VBA Programmierung -...