HOME     www.Junoland.de         (c) Copyright 2003 °Andreas Klotz, Cologne, Germany [upd.Oct.2008]     AKL@Junoland.de

Excel Exkursion in VBA
Programmieren lernen mit Excel VBA
ca.116 Seiten / 1,3 MB: 1 HTML-Textfile 0,2 MB + 60 Abbildungen 0,9 MB +
Zip-File 0,2 MB mit 9 Demos

Ablaufplan

 ↑  Intro

i1.   ↑    Willkommen im VBA-Grundkurs "Excel Exkursion" !

Dieser Kurs richtet sich an absolute Beginner - damit meine ich Menschen, die noch nie oder sehr wenig programmiert haben. Erwarten Sie von diesem schmalen Script kein Wunder. Es ist nur als knapper Einstieg gedacht, ohne Anspruch auf Vollständigkeit. Sie werden an vielleicht vier halben Tagen (oder Nächten) gerade einmal soviel erlernen, um entscheiden zu können, ob Sie tiefer in die VBA-Welt eindringen wollen. Ich hoffe aber, Ihnen über die ersten Einstiegshürden hinweghelfen und Ihren Appetit anzuregen zu können.

i2.   ↑    Was ist VBA?

VBA steht für Visual Basic for Applications.

Was bitte heißt das? - Visual Basic (VB) ist die populärste PC-Programmiersprache für den interessierten Laien und für kleine bis mittlere Unternehmensanwendungen. (C++ und Java sind die wichtigsten im Profi-Bereich). VBA ist ähnlich aufgebaut wie VB, mit dem Unterschied, dass man mit VBA zusätzlich unmittelbar auf das Verhalten von bestimmten Microsoft-Büroprogrammen (Applications) einwirken kann; es gibt VBA nicht nur für Excel, sondern auch für Word, Access und Powerpoint. Wenn man sich für Programmierung zu interessieren beginnt, kann man mit Excel-VBA auf schnellstem Weg zu Resultaten kommen, denn das Spielfeld für das Sichtbarmachen von Zahlen, Wörtern, Bildern und Tönen ist schon durch das Schachbrett-artige Excel-Raster-Blatt schon vorbereitet.

Excel selbst wird treffend als "Schweizer Offizierstaschenmesser" bezeichnet - sehr universal einsetzbar, aber für größere Datenmengen und viele gleichzeitige Zugriffe ungeeignet.

Alles in allem gibt es zwischen VBA und VB keinen nennenswerten Unterschied. VB wird wegen seinem freundlichen Einstiegsansatz von vielen selbsternannten Fachleuten als Spielzeug abgetan. In der Tat, VB (und VBA) kann einfach sein - kann aber genauso so komplex sein wie 'ausgewachsene' Profisprachen, wenn man z.B. Objekte anlegt, um auf fremde Bibliotheken zuzugreifen.

Zu ähnlich schnellen Ergebnissen kommt man allenfalls noch in der Homepage-Programmierung (HTML mit Javascript und serverseitigen Scriptsprachen, wobei dort aber engere Grenzen gesetzt werden durch die Rollen-Verteilung zwischen Browser und Server und den sich daraus ergebenden Sicherheitskonzepten).

i3.   ↑    Was wird Sie in den nächsten Stunden erwarten?

• A. IDE & Cell - Entwicklungsumgebung und Zellzugriff

• B. Var & Loop - Variablen, Schleifen, Wenn/Dann-Verzweigungen

• C. Button & Form - Buttons, Listen, Formularbau

• D. Error & System - Fehlerbehandlung und Systemfunktionen

 ↑  A. IDE & Cell - Entwicklungsumgebung und Zellzugriff

Jetzt kommen einige lästige Einstellungsübungen auf Sie zu, die wir gleich zu Anfang einschleifen sollten, um dann anschließend mit der Programmierung zu beginnen . Ohne Anleitung würden Sie wahrscheinlich deutlich mehr als eine Stunde brauchen, um all diese Fenster und Menüpunkte zu finden.

Wenn Sie nun zu der Meinung kommen sollten, dass dies nicht für die Benutzerfreundlichkeit dieser Entwicklungsumgebung (IDE) spricht, schauen Sie sich einmal vergleichbare Produkte an. Ich denke, dass alles in allem die hier vorliegende VBA-IDE eine der besten Entwicklungsumgebungen überhaupt ist - auch wenn ich mich hin und wieder über einige Ungereimtheiten ärgere :-(

A1.   ↑    VBA-Programmierbildschirm starten

Excel starten und das gewohnte Bild mit dem leeren Arbeitsblatt "Mappe1.xls" erscheint:

mit [Alt] + [F11] kann man den Programmierbildschirm (VBA-Modus) zum ersten Mal hinzuschalten:
(gut versteckt, oder?)

A2.   ↑    und hier kann man zwischen Excel- und VBA-Bildschirm wechseln ...

A3.   ↑    Steuerelemente-Toolbox einblenden

Nun legen wir einen ersten Schalter an, von dem aus unser künftiges VBA-Programm ablaufen soll.

Hierzu müssen wir die Werkzeugkiste zum Erzeugen und Bearbeiten von Schaltern, Listenfeldern, Formularen und anderen Steuerelementen anwählen - ExcelMenü: Ansicht / Symbolleisten / SteuerelementToolbox:

A4.   ↑    Schalter anlegen (Command-Button)

Aus all den angebotenen Objekttypen wählen wir "Befehlsschaltfläche", indem wir einfach einmal kurz mit der linken Maustaste auf das entsprechende Symbol klicken; dabei können wir feststellen, dass das erste Symbol "Entwurfmodus" automatisch mit-aktiviert wird. Wir halten die Maustaste also nicht fest, sondern fahren in einem zweiten Schritt mit unserem Mauszeiger an irgendeine beliebige Stelle unseres Excel-Arbeitsblatts. Dort drücken wir die linke Maustaste so lange nieder und verschieben dabei gleichzeitig den Mauszeiger, bis wir ein Rechteck in gewünschter Größe markiert haben. Wenn wir nun die Maustaste loslassen, haben wir eine "Befehlsschaltfläche" im typischen Windows-Grau erzeugt, die ich aber künftig kurz und englisch "Button" nennen werde.

A5.   ↑    'mal zwischendurch bemerkt: VBA-Ausdrücke werden englisch geschrieben

Da wir seit den letzten VBA-Versionen Befehlszeilen grundsätzlich in englischer Sprache schreiben müssen (eine spezielle deutsche VBA-Version wird seit Excel 5 nicht mehr angeboten), werde ich auch die Elemente englisch bezeichnen, die auf Excel-Arbeitsblatt-Ebene eine deutsche Übersetzung haben. Denn wenn wir auf diese Elemente auf VBA-Ebene Bezug nehmen, müssen wir sie wieder - zum Leidwesen vieler Anwender - in der englischen Entsprechung bezeichnen. Es nützt aber nichts, Objektbezeichnungen ständig von deutsch auf englisch hin und her zu übersetzen, denn je länger man sich mit VBA-Programmierung befasst, um so mehr wird man von der Excel-Arbeitsblatt-Oberfläche ("visual") wegtreten und manuell Programmzeilen schreiben im VBA-Englisch. Doch keine Angst, es handelt sich um vielleicht 2-3 Dutzend kurze Basic-Grundbegriffe zur Schleifenbildung und Verzweigung wie Loop und Exit, die man so oft anwenden wird, dass sie einem schnell vertraut werden. Dazu kommen dann umfangreichere Objektbezeichnungen, die aber grundsätzlich aus Auswahllisten übernommen werden können, so dass man sein Gedächtnis nicht all zu sehr bemühen muss, vor allem, was die korrekte Schreibweise anbelangt. Und eingedeutscht wären diese Begriffe nicht unbedingt klarer als im Original.

Das VBA-Englisch hat ganz entscheidende Vorteile:
Ein Ausdruck wie "Toolbox" ist kürzer als "Werkzeugkiste", "Befehlsschaltfläche" ist wesentlich umständlicher als "Button", "worksheets" entspricht "Arbeitsblätter" und "Eingabeaufforderungstaste" ist natürlich um einiges länger übersetzt als das knappe "Enter-Key". Wir werden sehr bald mit komplexen Ausdrücken zur Bezeichnung der Excel- und Windows-Objekte arbeiten, die schon in englischer Sprache lang genug ausfallen, z.B.:
Application.ActiveWorkbook.Sheets.Add um ein neues Arbeitsblatt 'sheet' anzulegen. Ins Deutsche übersetzt würde das den Rahmen des Erträglichen endgültig sprengen!

Ein weiterer Vorteil an dem Englischen ist, dass man seinen VBA-Code weltweit austauschen kann.

A6.   ↑    Toolbox dauerhaft in Excel-Menü einbauen

Bevor wir uns nun unserem neuen Schalter zuwenden, haben wir noch etwas einzustellen. Da wir die Toolbox häufiger brauchen werden, wäre es gut, wenn wir diese dauerhaft in das Excel-Menü einbauten. Dazu gehen wir mit dem Mauszeiger auf die blaue Titelzeile "Steuerelemente-Toolbox" und drücken die linke Maustaste nieder und halten sie solange fest, bis wir die Toolbox erfolgreich an eine freie Stelle des Excel-Hauptmenüs verschoben haben:

So ungefähr sollte die Werkzeugkiste nun im Excel-Hauptmenü eingebaut sein
(Um diese Sektion wieder woanders anzuordnen, Mauszeiger an linken Rand positionieren und linke Maustaste gedrückt halten zum Verschieben) :

A7.   ↑    Button-Eigenschaften einstellen

Jetzt erschrecken Sie bitte nicht, wenn daraufhin ein üppiges Auswahl-Menü aufspringt! Wir werden davon nur die allerwenigsten Eigenschaften einstellen. Fast alle Default-Werte (Vorgabewerte) kann man erst einmal so hinnehmen wie vorgefunden.

Wir verändern den Defaultwert "CommandButton1" der Name-Eingenschaft zu "cmdFangAn" und die Aufschrift (merkwürdigerweise "Caption" genannt) unseres Buttons von "CommandButton1" zu "Fang an !".

A8.   ↑    Hello World! Unsere ersten VBA-Programmzeilen schreiben

Mit einem beherzten Doppeklick auf unseren Button (Entwurfmodus sollte immer noch eingeschaltet bleiben) stoßen wir nun zum ersten Mal das Tor zur VBA-Programmierung auf!

Wir sollten das mickrige Fester mit dem VBA-Sourcecode (Quelltext, Programmbefehlstext) zu einer seiner künftigen Bedeutung angemesseneren Größe bringen:

nun schreiben wir unsere erste VBA-Befehlszeile ein, und zwar zwischen
Private Sub cmdFangan_Click()
und
End Sub

Private Sub cmdFangan_Click()
  MsgBox "Hello World!"
End Sub	

Schnell wechseln wir vom VBA-Bildschirm zum Excel-Sheet, deaktivieren dort den Entwurfmodus und klicken auf unseren Button "auf-auf !"- und erhalten als Antwort diese klassische Meldung:

Ich hoffe doch, dass ich den Wechsel zwischen VBA-Bildschirm und Excel-Arbeitsblatt hinreichend erklärt habe - wenn nicht, schauen Sie bitte noch einmal hier hinein.

Und um unsere Freude an diesem beispielhaften Programm zu steigern, bauen wir noch einen Rücksprung ein, der unseren Menschheitsgruß wiederholen soll:

Private Sub cmdFangan_Click()
[A5] = 0

hierhin: 
  [A5] = [A5] + 1
  MsgBox "Hello World! "
Goto hierhin

MsgBox "Fertig, Mama!"
End Sub

(das können Sie übrigen auch ganz einfach aus dem VBA-Programm-Code aus, indem Sie den Sub/UserForm-Ausführen-Pfeil hier anklicken oder die F5-Taste drücken)

3 typische Fehler beim Umgang mit Excel-VBA

A9.   ↑    VBA-Editor-Einstellungen: Tabulator-Schrittweite und Fehlerbehandlung

Leider muss ich Sie jetzt noch einmal für 1-2 Minuten stören, damit Sie einige Einstellungen checken, um Ihre Entwicklungsumgebung zu optimieren; danach wird nicht mehr allzuviel "Gefummel" auf Sie zu kommen. Bitte speichern Sie das Excel-File mit dem Hello-World-Beispiel und alles andere irgendwohin ab, wo Sie es später wiederfinden werden.

Um Optionen einstellen zu können, muss irgendein Excel-Arbeitsblatt vorliegen, und wenn es einfach nur eine neue leere Arbeitsmappe ist.

Extras | Optionen | Editor:
   [x] Variablendeklaration erforderlich
(dies ist wichtig, um uns von Anfang an zu einer übersichtlichen Variablendeklaration aufzuraffen)
Tab-Schrittweite [ 2 ]
(sonst entstehen bei verschachtelten Ausdrücken mit sprechenden Variablenbezeichnungen Zeilen, die sehr schnell über den rchten Bildschirmrand gehen)

Extras | Optionen | Allgemein:
   Fehlerbehandlung :
   ( o ) Bei nicht verarbeiteten Fehlern unterbrechen
(sonst stoppen unsere VBA-Programme z.B. bei jeder nicht-aufgefunden Datei, auch wenn wir dafür eigens eine Fehlerbehandlungsroutine geschrieben haben)

Und nun sollten Sie Excel bitte komplett beenden (PC nicht 'runterfahren) und wieder neu starten, damit die Einstellungen wirksam werden. Starten Sie Excel erneut, schalten Sie wieder mit [Alt] + [F11] Taste zu VBA und dort machen Sie bitte im Project-Fenter (VBA-screen oben links) einen Doppelklick auf "Tabelle1" - Sie sollten dort in der ersten Zeile lesen können:
Option Explicit
also die Anweisung, die besagt, dass Sie sich dazu entschlossen haben, Variablen künftig vor Gebrauch anzumelden.

A10a.   ↑    VBA-Bildschirm in 4 Fenster unterteilen

• Ansicht | Sourcecode
• Ansicht | Projektexplorer
• Ansicht | Projektexplorer
• Ansicht | Eigenschaftenfenster

So ähnlich wie in folgender Abbildung, sollte der VBA-Bildschirm dann aussehen:

Oben links wird das Project angezeigt in all seinen Untergliederungen von Sheet-Modulen, Form-Modulen und zusätzlichen Modulen, auf die wir bei Bedarf noch zurückkommen werden. Wechsel zu anderen Modulen per Doppeklick (einfach-Klick zeigt immer noch den VBA-Code des vorherigen Moduls).

Vorsicht ist dann geboten, wenn Sie gleich mehrere Excel-Files auf einmal geöffnet haben! Sie sollten aufpassen, dass Sie nicht versehentlich Änderungen für das falsche Project (File) vornehmen. Ich rate Ihnen also, nur dann mit mehreren Files zugleich umzugehen, wenn es sich nicht vermeiden lässt.

Es kann sein, dass Sie Ihre Module ähnlich wie der Windows-Explorer nicht in aufgeklappter Baum-Darstellung vorfinden. Machen Sie also gegebenenfalls einen Doppelklick auf das Project (also auf Ihr Excel-File) , auf "Microsoft-Excel-Objekte", worunter Arbeitsmappen- und Sheets-Module vorzufinden sind, und auf Formulare und Module.

Unten links werden Eigenschaften (properties) des jeweils angewählten Projektteils angezeigt. Hier kann man z.B die interne Bezeichnung von Sheets ändern, Eigenschaften von hinzugefügten Formularen, etc.

Oben rechts ist der eigentliche VBA-SourceCode. Der Quelltext ist entweder dem ganzen File zugeordnet (z.B. für die Dinge, die beim Öffnen eines Files geschehen sollen), oder einem einzelnen Button eines Sheets oder einem separat hinzugefügten Formular (das ist eine Art Sammelbehälter für verschiedene Objekte wie Buttons, Eingabefelder, Auswahl-Listen) oder einem beliebig hinzugefügten allgemeinen Programm-Moduls.
(Über all diese Punkte wird noch gesprochen werden)

Unten rechts das Debug-Fenster - auch Direct-Fenster genannt - zum schrittweisen Ablauf des Programms; zwischen den einzelnen Schritten können aktuelle Variablen- oder Zell-Inhalte zur Kontrolle angezeigt und sogar verändert werden.

A10b.   ↑    +Form-Design-Fenster

An derselben Stelle, an der das Code-Fenster für unsere jeweiligen Module steht (Arbeitsblatt-Modul, freies Modul), erscheint für Forms das FormDesign-Fenster. Ein Doppelklick auf eine Userform im Projektfenster (im VBA-screen links oben) bringt also zunächst das Form-Objekt in Grundriss-Darstellung, an dem man mit der Maus Objekte verschieben, vergößern, etc. kann:

Um dies zu erfahren, sollten Sie jetzt gleich einmal ein Formular anlegen, falls Sie noch keines vorliegen haben, in dem Sie mit rechter Maustaste irgendwo ins Project-Fenster klicken und "Einfügen Userform" ausführen. Userforms und andere Module können übrigens exportiert und importiert werden. Man könnte sich also die wichtigsten Befehle, die man immer weieder verwendet in einem separates Modul führen, das man dann in neue Projekte importiert.

Man kommt im Laufe der Entwicklungsarbeit viel öfter zum Form-Design-Fenster, als einem vielleicht recht ist! Man wird z.B. aus jeder abgebrochenen Debugger-Session dorthin zurückgeworfen statt zum VBA-Code, den man doch wahrscheinlich bearbeiten möchte!

Um nun an den Sourcecode eines Form-Objekts zu gelangen, klickt man per rechter Maustaste auf das entsprechende Form-Modul im Project-Fenster und wählt "Code anzeigen" aus dem Kontext-Menü; und dann kann man weiter per Ctrl+Shift+F2 (siehe Hotkeys) zu seinen letzten Text-Eingaben zurückkehren ... - ärgerlich ? - In meinen Augen schon!

A11.   ↑    Kommentier-Symbol in Menüleiste einbauen

Man kann bequem ganze markierte Blöcke von Befehlszeilen deaktivieren, indem man das Hochkomma als Kommentar-Zeichen voranstellt, z.B so hier:

Sub test()
Dim a, b
a = 123
b = 456 
'If a = 1 then 
'  b = 7 * a
'End If	
End Sub

Rechts-Click auf die Pulldown-Menü-Leiste, Anpassen, Befehle, Bearbeiten, "BlockAuskommentieren" wählen, mit niedergedrückter linker Maustaste in die Menüleiste ziehen (ganz links anfassen); das gleiche auch mit BlockKommentierenAufheben" - fertig.

A12a.   ↑    Funktionstasten
F1 / Strg+F / Strg+H / Shift+F2 / Ctrl+Shift+F2 / (Shift)+Tab

• F1=Hilfe zu dem VBA.Ausdruck, auf dem der Cursor gerade steht
• Strg+F=suchen
• Strg+H=ersetzen
• Shift+F2=go def
  (Geh zur Definition einer Variablen oder zum Beginn der Sub, auf deren namen der Cursoer gerade steht)
• Ctrl+Shift+F2=go last input
• (Shift)+Tab=Tab-Step nach rechts (links),
  auch ganzer Block aus mehreren Zeilen verschiebbar)

12b.   ↑    'mal zwischendurch bemerkt: Ohne Maus kann man einfach schneller Programmzeilen schreiben

• [Strg]+a = alles im Fenster markieren
• [Strg]+y = Ganze Zeile löschen
• [Strg]+z = letzte Änderung rückgängig machen
• [Strg]+Einfg = In die Zwischenablage kopieren
• [Shift]+[Einfg] = Einfügen, was in der Zwischenablage steht
• [Shift]+[Einfg] auf markiertem Text = Ersetzen durch das, was in der Zwischenablage steht

VBA macht übrigens glücklicherwise keinen Unterschied zw. Groß- und Klein-Schreibung. Tippen Sie alles bequem in kleinen Buchstaben ein und lassen Sie dann vom VBA-Editor alles, was an Befehlen und Variablen großgeschrieben wird, entsprechend in Großbuchstaben umwandeln. Bleibt alles klein, sehen Sie auf den ersten Blick, dass hier wohl ein Tipp-Fehler vorliegen muss.

A13.   ↑    Zellzugriff: Range / Cells / Offset

Es gibt eine verwirrende Vielfalt von Zugriffsmöglichkeiten auf die Zellbereiche der Excel-Arbeitsblätter, die uns aber schnell geläufig werden sollten:

A13a.    ↑    Range

Range bedeutet in Excel Zellbereich, mit zB: Range("C8")=123 wird der Zahlenwert 123 in die Zelle C8 eingetragen, mit a = Range("C8") wird der Zellinhalt von C8 in die Variable a eingelesen (kopiert).

Ein Zellbereich kann aus einem rechteckig angeordneten Verbund von benachbarten Zellen bestehen. Ebenfall mit Range , Range("C8:F15") , kann man auf diesen zugreifen.

Noch viel kürzer ist folgende Schreibweise:
[C8], was dem Range("C8") entspricht und auch fast immer zu empfehlen ist, um sich lästige Tipparbeit zu sparen. Für den Anfänger hat diese Schreibweise allerdings den Nachteil, dass hierzu keine Autocompletion angeboten wird; damit meine ich die Liste, die eingeblendet wird, wenn ich zB schreibe Range("C8"). ; sobald ich den Punkt eingeben habe, springt eine Liste der verfügbaren Erweiterungen für den betreffenden Ausdruck auf, die in grau-unterlegte Ausdrücke für Eigenschaften und gelb-unterlegte Ausdrücke für Aktionen unterschieden wird; tippe ich den Anfangsbuchstaben an (zB "C"), werde ich schnell an den gewünschten Ausdruck geführt ("ClearContents"), den ich dann nur noch mit der Tabulator-Taste (das ist die Taste links neben der [Q]-Taste) bestätigen muss. So kann man sich den Luxus von lesbaren unabgekürzten Ausdrücken leisten, ohne sich die Finger wund zu tippen.

Man kann Range noch um den Blattnamen erweitern, vor allem wenn man von einem bestimmten Sheet aus ein Makro gestartet hat, das auf ein anderes sheet zugreifen soll: Range("Plan!C8:F15").Clearcontents löscht zB den Bereich C8 bis F15 auf dem Arbeitsblatt 'Plan'.

Auch Bereichsnamen, die man im Arbeitsblatt vergibt, kann man ganz normal mit Range aufrufen: Range("Einkaufsliste").Copy kopiert zB den gleichnamigen Bereich, usw. ....

A13b.    ↑    Cells

Was ich im "normalen" Excel (also nur Zellformeln ohne VBA-Programmierung) schmerzlich vermisse, sind relative Zellbereiche; ich möchte z.B. etwas summieren, für das sich die Bereichsgrenze dynamisch einstellen lassen müsste - wenn es nur ginge! z.B. soll der Bereich(A1:A30) im April eben diese 30 Zellen umfassen, weil man diesen ausschneiden möchte oder eine Summe bilden will oder sonstiges. Im Mai mit seinen 31 Tagen wäre dann ja schon eine andere Formel nötig, es sei denn man könnte die Zellformel so schreiben, zB: Summe(A1:Tage), wobei Tage ein Zellbereich wäre, in dem die Monatstage eingetragen oder ausgerechnet wären... - das geht aber nicht!

Mit VBA ist es hingegen ein Leichtes, eine Adresse flexibel aus anderen Variablen zu bilden:
Range(Cells(1, 1), Cells(x, 1)) wäre hier die Lösung.
Cells(1,1) ist eine einzelne Adresse A1 (auch wenn dieses mit einem Wort im Plural benannt wird) und Cells(x, 1) ist ebenfalls eine einzelne Adresse, wobei diese je nach dem Wert von x variiert. Wenn zB x=30 ist, dann ist dies A30; wenn es 31 ist, dann ergibt der Ausdruck eben A31 usw. Ein Range aus beiden Zellen ist nichts weiteres als eben der gewünschte Ausdruck: A1 bis A-irgendwas, was bei einem x-Wert von 28 eben A1:A28 (also A1 bis A28) ergäbe und für x=31 eben A1:A31.

verkehrte Welt: Range("A5") = Cells(5,1)

Dem aufmerksamen Leser ist wahrscheinlich nicht entgangen, dass dem VBA-Ausdruck Cells nicht eine Bezeichnungsreihenfolge wie bei einem Schachbrett oder dem Excel-sheet zugrunde liegt, bei dem ja zuerst der Buchstabe um die Spalte (Column) zu bezeichnen angegeben wird und dann die Zahl um die Zeile (Row) zu benennen; sondern hier wird - leider - die gewohnte Reihenfolge vertauscht: Erst die Zeile, dann die Spalte, also zB D7 wird durch Cells(7,4) bezeichnet und nicht etwa durch Cells(4,7)!

A13c.    ↑    Offset

While Range("sorte1").Offset(y, 0) <> was And y < maxProds
  y = y + 1
Wend

A14.   ↑    14.Stufengliederung: Range / Sheet / Workbook / Application

Das Arbeitsblatt kann so angesprochen werden:

Worksheets(1) oder mit Namensnennung Worksheets("Plan")
oder kürzer so:
Sheets(1) oder mit Namensnennung Sheets("Plan").

Warum verwendet man mühsam den Namen statt der kurzen Ordnungszahl? - Man kann ein Blatt vor ein anders verschieben, oder ein neues einfügen, so dass die hinteren um 1 Nummer höher "rutschen"; ein Blatt umbenennen tut man dagegen zwar auch hin und wieder einmal, aber der Interpreter "schreit" dann laut auf, wenn er dann dieses Objekt nicht mehr findet, während er bei der Nummerbezeichnung häufig etwas auf dem benachbarten Blatt duchführt, ohne dass man es sofort bemerkt. Und dann ist es oft schon zu spät, weil Eingaben und ZellFormeln brutal überschrieben wurden....

Man kann den zusammengesetzten Ausduck aus Sheet und Zelle u.a. auch so bilden:
Sheets("plan").Cells(3, 5).

A15a.   ↑    Probleme bei nicht-aktiviertem Arbeitsblatt / Arbeitsmappe

Den folgenden Abschnitt sollten Sie als Anfänger nur einmal kurz überfliegen und erst dann gründlicher zu Gemüte führen, wenn Sie mit mehreren Sheets und mehreren Excel-Files zugleich arbeiten und auf entsprechende Schwierigkeiten stoßen sollten.

Folgender elend-lange Ausdruck ist die vollständige Bezeichnung für eine bestimmte Zelle:
Application.Workbooks("Umsatz.Xls").Worksheets("Plan").Cells(8, 3)
Puh...! - Was bedeutet das im Einzelnen? Mit Application ist Excel als die Wurzel gemeint (im Gegensatz zu zB "Word" oder "Access"); mit Workbooks(2) ist das 2. geöffnete Excel-File gemeint (im Hauptmenü unter "Fenster" werden ja zueilen mehrere aufgelistet). Den Rest kennen wir schon: mit Worksheets ist die Gruppe von Arbeitsblättern eines Files gemeint, von denen hier das 1. gemeint ist; und Cells bezichnet davon eine bestimmte Zelle (C8).

Heißt das nun, dass man immer solche monströsen Ausdrücke schreiben muss, nur um eine winzige Zelle anzusprechen? - Natürlich nicht! Man verwendet Ausdrücke stufenweise in dem Grad vollständiger, in dem man von der jetzt aktiven Umgebung auf eine benachbarte Umgebung greifen möchte. Im Normal-Fall sagt man schlicht Cells(8, 3) und nichts weiter, wenn man eben den Programmcode auf demselben Workbook gestartet hat und sich immer nur auf einem bestimmten Sheet befindet...

Doch Vorsicht! Oft kommt es dann doch dazu, dass man Bereichsgrenzen überschreitet ohne daran zu denken! Wenn man zB ein Excel-Diagramm anklickt, dann steht der Zellzeiger nicht mehr auf dem Arbeitsblatt, das man immer noch glaubt vor sich zu haben - Buttons funktionieren dann nicht mehr! Man muss also dann wieder das Arbeitsblatt aktivieren: Sheets("Plan").Activate und irgendeine Zelle anwählen, damit der VBA-Interpreter endlich wieder "Boden unter den Füßen hat" : Range("K7").Select.

A15b.   ↑    Loosing Control

 ↑  B. Var & Loop - Variablen, Schleifen, Wenn/Dann-Verzweigungen

B1.   ↑    Visual vs. Manual

Sie werden die Maus künftig öfter aus der Hand legen als Ihnen vielleicht lieb ist!

Lassen Sie sich nicht von dem werbeträchtigen Zauberwort "Visual" in VBA zu der Annahme verleiten, dass Sie mit ein paar Mausklicks schon den fertigen Programmablauf erzeugen können; als wenn Sie nur in eine Kiste mit Bauklötzchen greifen müssten um einige vorgefertigte Elemente auszuwählen, einige geschmackliche Änderungen mit Drag and Drop einzustellen um dann in Windeseile ein fertiges Programm abzuliefern ... - Weit gefehlt!

Dass wir das Sourcecode-Fenster schon einmal größer eingestellt haben, hat durchaus seine Berechtigung. Denn wenn Sie tatsächlich "Blut lecken", also Gefallen an der VBA-Programmierung finden sollten, werden Sie im Laufe der Zeit feststellen, dass Sie ca. 60 Prozent Ihrer Aufmerksamkeit dem manuellen Eintippen von nüchternen und den Anfänger zunächt eher bizarr anmutenden Befehlswörtern wie "if err.number>0 then..." verbringen werden. Mehrfach ineinander-geschachtelte Klammerausdrücke, die die Zeilenbreite überschreiten, die verwirrende Vielfalt von Orten, an denen Programmtext und Objekteinstellungen vorgenommen werden können, die Tücken von Ereignisbehandlung und Formulareinstellungen, die Mühe, Eingabefehler abzufangen und den User mit entsprechenden Meldungen zu versorgen - all das wird den/die eine(n) oder andere(n) abschrecken!

Jetzt holen Sie einmal tief Luft und schauen Sie sich einmal ein typisches Beispiel für VBA-Programmierung im "fortgeschrittenen Stadium" an:

Function CheckInput() As Boolean
Dim RoCheck As Long, Co As Long, Cel As String
Dim par1 As String, par2 As String, a As Long
Dim LRefZeile As Long, okay As Boolean
Dim icel, yy As Long, oldlen As Long, maxlen As Long
Dim Ro1 As Long, Co1 As Long, LinEnd As Long, LinMax As Long, OrientCol As Long

Application.Calculation = xlCalculationAutomatic 
' ...damit zB dfix-wert richtig ausgerechnet wird
Application.Calculate
Call CheckDef(False)
okay = True

RoCheck = SearchDBTCommand("check") ': MsgBox RoCheck
If RoCheck = 0 Then Exit Function
Ro1 = Range([A2].Value).Row: Co1 = Range([A2].Value).Column
If IsNumeric([a3]) And [a3] <> "" Then LinMax = [a3] Else LinMax = 65536
For Co = 2 To 256
  Cel = UCase(Trim(ActiveSheet.Cells(RoCheck, Co)))
  If Cel <> "" Then
    If Left(Cel, 1) = "§" Then
      Cel = Right(Cel, Len(Cel) - 1) ':      MsgBox cel
      '----------------------------------
      If IsNumeric(Cel) Then  ' ....max. Länge eines Textfeldes testen....
        maxlen = CLng(Cel)
        LinEnd = Ro1 - 1 + Application.WorksheetFunction.CountA( _ 
          Range([A2].Value, Cells(LinMax, Range([A2].Value).Column)))
        For yy = Ro1 To LinEnd
          icel = ActiveSheet.Cells(yy, Co): oldlen = Len(icel)
          While Left(icel, 1) = "?": icel = Right(icel, Len(icel) - 1): Wend
          If Len(icel) <> oldlen Then ActiveSheet.Cells(yy, Co) = icel
          If Len(icel) > maxlen Then 
            okay = False: ActiveSheet.Cells(yy, Co).Select 
            Cells(yy, Co) = "?" & icel '': Stop
          End If
        Next yy
      End If
      '-------------------------------------
      If InStr(Cel, ",") = 0 And Not IsNumeric(Cel) Then _ 
        Call FindAliDef(Co, Cel, okay)  
      ' ... also der normale Fall, 
      '   die offizielle Bezeichnung eines populären Namens zu finden
      If Left(Cel, 2) = "R," Then
        Cel = Right(Cel, Len(Cel) - 2)
        If Cel = "PIG_BM" Then Call FindPigBM(Co, okay)
        If Cel = "ROH_ID" Then Call FindRohID(Co, okay)
      End If
      If Left(Cel, 2) = "V," Then 'Copy Value,Spalte mit Vorgabewerte füllen
        Cel = Right(Cel, Len(Cel) - 2)
        LinEnd = Ro1 - 1 + Application.WorksheetFunction.CountA( _ 
          Range([A2].Value, Cells(LinMax, Range([A2].Value).Column)))
        ''Cells(Ro1, Co1).Select: Selection.End(xlDown).Select
				'' LinEnd = Selection.Row
        Range(Cells(Ro1, Co), Cells(LinEnd, Co)).Value = Cel
      End If
      If Left(Cel, 5) = "DFIX," Then ' ....copy festes datum
        par1 = Mid(Cel, 6, Len(Cel) - 6 - 5) ' feldnamen auslesen zur orient.
        par2 = Right(Cel, 5)
        Range(Cells(Ro1, Co1), Cells(LinMax, Co1)).ClearContents
        OrientCol = WorksheetFunction.Match(par1, [6:6], 0)
        LinEnd = Ro1 - 1 +         Application.WorksheetFunction.CountA( _ 
          Range(Cells(Ro1, OrientCol), Cells(LinMax, OrientCol)))
        ''Cells(Ro1, OrientCol).Select: Selection.End(xlDown).Select
				''LinEnd = Selection.Row
        Range(Cells(Ro1, Co), Cells(LinEnd, Co)).Value = CLng(par2)
      End If
    End If
  End If
Next Co
CheckInput = okay
Range([A2].Value).Select
End Function

Doch halt !
Bevor Sie nun die Flucht ergreifen, lassen sie sich gesagt sein, dass Sie mächtige Verbündete haben, um den Überblick zu behalten:

• Sie haben eine sehr ausgereifte Hilfefunktion (F1-Taste) mit vielen Beispielen und Querverweisen.
• Sie haben die Automatische Vervollständigung all dieser sperrigen Objekt-Ausdrücke.
• Sie haben eine exzellente Such-und Ersetz-Funktion und zahlreiche weitere Navigationsmöglichkeiten (, die keinesfalls bei allen anderen Programmierumgebungen so üblich sind)
• Sie haben eine große Zahl von Mitmenschen, die ebenfalls vor solchen Herausforderungen stehen, wenn Sie einmal im Internet nach Demos suchen (z.B. www.Google.de - Suchbegriffe: Excel while wend).
• Sie können sich in beliebig großen Teilabschnitten fortbewegen und alles gründlich kommentieren, um sich eigene Beispiele aufzubauen.
• Auf alles, was Sie entwickeln, bekommen Sie sofort Schritt-für-Schritt eine Antwort, wenn Sie den "Debugger" benutzen.
• Der Makro-Rekorder verrät Ihnen den Namen der Funktionen und Objekte, auf die Sie im Excel-Arbeitsblatt zugreifen....

Lassen Sie sich also nicht entmutigen und verfolgen Sie noch einige Augenblicke diesen Kursus, um einmal zu sehen, was man mit VBA "so alles anstellen" kann! Sie können Datenbanken abfragen und Werte berechnen, in aufwendigen Reports ausgeben und auch zurückschreiben. Sie können andere Windows-Prozesse abfragen und auch anstoßen. Ihrer schöpferischen Ader, ihrem Erfindungsgeist sind fast keine Grenzen gesetzt, sofern Sie die nötige Muße finden! Wenn Sie wirklich wollen, werden Sie sich die Zeit nehmen - wenn nicht, werden Sie hier zumindest einmal einen ersten Einblick gewinnen können, wie weit VBA nützlich sein kann, um typische Büro-Aufgaben zu lösen.

B2.   ↑    Variablen - Namenskonventionen

[sorte1].Offset(PNum - 1, 2) = [sorte1].Offset(PNum - 1, 2) + pquant

[ErsteSorteInProduktliste].Offset(Produktnummer - 1, 2) = [ErsteSorteInProduktliste].Offset(Produktnummer - 1, 2) + Tagesproduktionsmenge

B3.   ↑    Speedtest

Zugriffe auf Zellen kosten ein Vielfaches an Prozessor-Zeit und Platzverbrauch als der Umgang mit 'inneren' Speicherstellen für Werte, als die man Variablen bezeichnen könnte. Dies macht sich allerdings erst ab vielen zehntausend Zugriffen bemerkbar, was wir mit diesen beiden kleinen Subs einmal vergleichen können, die Sie in ähnlicher Form in cellspeedtest.xls vorfinden werden.

Sub TestCellSpeed() 
[A1] = 0
Do
  [a1] = [a1] + 1
  if [a1] >= 32000 Then Exit Do 
Loop
MsgBox "Fertig!"
End Sub

Sub TestVarSpeed()
Dim a
Do
  a = a + 1
  if a >= 32000 Then Exit Do 
Loop
[B1]=a
MsgBox "Fertig!"
End Sub

Variablen sind außerdem einfacher zu benennen als Zellen: "a=1" ist kürzer als "Range("A1")=1", sogar kürzer noch als die von mir favorisierte kürzeste Form "[A1]=1". Bei zusammengesetzen Ausdrücken aus mehrfachverschachtelten Elementen würde das noch mehr den Programmierfluss bremsen.

B4.   ↑    warum Option Explicit ? (Variablendeklaration erzwingen)

Ich möchte Ihnen hier einmal demonstrieren, was passiert, wenn wir es uns bequem machen und uns selber keine explizite Variablendeklaration abverlangen:

Sub TestNoOptionExplicit()
Do
  Anzahl = Anzahl + 1
  if Anzal >= 32000 Then Exit Do 
Loop

End Sub

B5.   ↑    Variablentypen

Muss man Variablen deklarieren (anmelden)?

Man muss nicht, aber es wird einem dringend angeraten, um selber die Übersicht zu behalten über die Komponenten, die in den Programmabschnitten eine Rolle spielen.

Schreibt man "Option Explicit" zu Beginn eines Moduls, bedeutet dies, das man sich dazu verpflichtet alle Variabeln, die man verwenden will, explizit (ausdrücklich) anzumelden - und dazu rate ich Ihnen hiermit auch explizit, siehe Option Explicit

Wie deklariert (dimensioniert) man Variablen?

Man schreibt zu Beginn des entsprechenden Programmabschnitts zB folgendes:
Dim a as Long, Runden as Long, Zinssatz as Double, Anrede as String

Variant

Achtung, wenn man zB mehre Long deklarieren will, genügt es nicht, zB so etwas hier zu schreiben: Dim a, b, c as Long - denn nur "c" wäre hier in diesem Fall als Long deklariert, die anderen ("a" und "b") werden zwar 'angemeldet', nicht aber näher spezifiziert. Man weiß also nicht, ob man damit einen Long, einen String oder sonst etwas meint! Ja, auch das ist möglich: unbestimmter Typ, "Variant" genannt, oder eben einfach ohne weitere Bezeichnung.

Wozu ist ein Variant gut?

Zunächst scheint er ja äußerst bequem, aber die Bequemlichkeit wird teuer erkauft: Man wird viel häufiger von der Laufzeitumgebung der Entwicklungsplattform gewarnt, wenn man etwas unsinniges miteinander in Bezug setzt, weil man sich einfach nur im Namen geirrt hat: if Anrede + 3 > 100 ergibt ja wohl keinen Sinn, wenn man mit "Anrede" tatsächlich so ein Wort wie "Herr" oder "Frau Königin" meint; wenn die Variable aber einfach nur "a" hieße statt "Anrede", könnte man den Fehler nicht so schnell erkennen - wohl aber der VBA-Interpreter: Er lässt einen gar nicht erst starten, sondern springt dann vorwurfsvoll und diensteifrig zugleich zur Fehlerstelle, damit man diese korrigieren soll. Variant statt bestimmter Variablentyp lässt oft auch keine Auto-Vervollständigung zu, wenn man seinen Sourcetext schreibt.

Nur wenn ich mich auf sehr unwirtlichem Terrain befinde und mich irgendwie durch den Dschungel der wenig bekannten Objekte durchschlagen muss, erlaube ich mir einmal, die Typ-Frage offenzuhalten, zB hier:

For Each errLoop In Cn.Errors
If errLoop.Number = -2147217843 Then....

Dim Kunden(100,5) As String

B6a.   ↑    Public für das ganze Modul

B6b. Über das Modul hinaus

B7.   ↑    Bedingungen

If a > 31 then 
  MsgBox "Auf nächsten Monat ausweichen!"
End If

If a > 31 then 
  MsgBox "Auf nächsten Monat ausweichen!"
  A =123
  Fertig = True
End If

If a > 31 Then MsgBox "Auf nächsten Monat ausweichen!"

If a > 31 then  MsgBox "Auf nächsten Monat ausweichen!": A=123: Exit Sub

If a > 31 Or b = 0 then 
  MsgBox "Auf nächsten Monat ausweichen!"
  Exit Sub
Else
  MsgBox "Alles Paletti!"	
End If

a = Range("C8")
If isnumeric(a) and a > 31 Then ....

If isnumeric(a) Then
  If a > 31 Then 
    ..
  End If
End If		

B8.   ↑    Schleifen

Loop.xls

• Do..Loop
• Do..Loop Until..
• Do While..Loop
• While..Wend
• For..Next
• For Each .. In .. Next

B8a.    ↑    Do..Loop (~ wiederhole a bis auf weiteres)

Sub test_loop()
Dim i As Long, b As Long
i = 0: [c8] = 3
Do
  If i = [c8] Then Exit Do
  i = i + 1
  MsgBox i
Loop
End Sub

der Ausdruck links vom Gleichhtiszeichen soll den Wert des Ausdrucks rechts vom Gleichheitszeichen annehmen (hier also: i soll jetzt den alten Wert von i , aber zusätzlich um 1 erhöht, annehmen).

B8b.    ↑    Do .. Loop Until ..(~wiederhole a bis b ist)

Sub test_while_loop()
Dim i As Long
i = 0: [c8] = 3
Do
  i = i + 1
  MsgBox i
Loop Until i >= [C8] Or i = 10 
End Sub

B8c.    ↑    Do While .. Loop (~wiederhole a solange b ist)

Sub test_loop_until()
Dim i As Long
i = 0: [c8] = 3
Do While i < [c8]
  i = i + 1
  MsgBox i
Loop
End Sub

B8d.    ↑    While..Wend (~wiederhole a solange b ist)

Sub test_while_wend()
Dim i As Long
i = 0: [c8]=3
While i < [c8]
  i = i + 1
  MsgBox i
Wend
End Sub

B8e.    ↑    For..Next (wiederhole a genau x mal)

Sub test_for_next()
Dim x As Long
For x = 1 To 3
  MsgBox x
Next x	
End Sub

Sub test_for_next_exit()
Dim x As Long
[c8] =2
For x = 1 To 3
  MsgBox x
  If x >= [c8] Then Exit For
Next x	
End Sub

Sub test_for_next_exit()
Dim x As Long
[c8] =7
For x = 1 To 3
  MsgBox x
  If x >= [c8] Then Exit For
Next x	
MsgBox x, , "Wert von x ,nachdem die Schleife beendet worden ist"
If x = 4 Then MsgBox "Zelle kann also nicht 1, nicht 2 und auch nicht 3 sein!" 
End Sub

Sub test_StepBack()
For x = 3 To 1 Step -1
  MsgBox x
Next x	
End Sub

Sub test_Step()
For x = 1 To 6 Step 2
  MsgBox x
Next x	
End Sub

B8f.    ↑    For Each ..In .. Next (~Mach etwas für jedes Objekt aus einer Gruppe)

Sub test_foreach()
Dim x
For Each x In ActiveWorkbook.Names
  Debug.Print x.Name, x.Value ' ggf. [Strg+G]=Direct-Fenter einschalten
Next ' oder auch: Next x; aber dies ist nicht zwingend notwendig
End Sub

B9.   ↑    Sub & Function

B9a.   ↑    Sub

Eine Sub hat folgenden Aufbau:

Sub abc('ggf. für die übergabe parameter-variablen mit typ deklarieren)
'Dim a, b ... ggf. Variablendeklarationen, automatisch und zwingend privat ..
..
End Sub

Sub ClearPizzaPlan()
If MsgBox("wirklich ALLES löschen?", vbYesNoCancel, "myprog") = vbYes Then
  Range("d4:k35").ClearContents
End If
End Sub

B9b.   ↑    Function

Eine Function hat - wie gesagt - immer genau einen Rückgabewert. Der Aufbau sieht so aus:

Function abc('ggf. für die übergabe parameter-variablen mit typ deklarieren) As Typ (z.B Long) 'Dim a, b ... ggf. Variablendeklarationen, automatisch und zwingend privat
..
..
abc=123 ' <--- Rückgabewert!
End Function

Eine sehr nützliche Funktion ist zB die Osterformel nach Gauss [das ist der Mann mit der komischen Mütze auf den früheren 10-DM-Scheinen. Wenn man bedenkt, dass er dies vor rund 200 Jahren noch mit Tintenfeder und Papier ausgetüftelt hat, hat er diesen Platz in der ehrwürdigen Reihe unserer früheren Banknoten allemal verdient !] Übergibt man eine Jahreszahl als Parameter, bekommt man eine Tagesnummer als Antwort auf die Frage, auf welches Datum Ostersonntag von einem bestimmten Jahr fällt (Datumsangaben werden in allen Programmiersprachen intern als Tagnummer seit einem bestimmten Zeitpunkt, zB in VBA seit dem 31.12.1899 angesehen).

Public Function Gauss_Ostern(A As Long) As Long
Dim D As Long
D = (((255 - 11 * (A Mod 19)) - 21) Mod 30) + 21
Gauss_Ostern = DateSerial(A, 3, 1) + D + (D > 48) + 6 - ((A + A \ 4 + _
D + (D > 48) + 1) Mod 7)
End Function

Sub test_Gauss_Ostern()
MsgBox Format(Gauss_Ostern(2003), "dd.mm.yyyy")
End Sub

B9c.   ↑    Private

Private Sub CommandButton1_Click()

B9d.   ↑    Liste meiner Subs und Functions

B10.   ↑    Übergabeparameter ByRef oder ByVal

VB und VBA gehen von folgender Regelung aus:

Alle Werte, die in Form von Variablen - statt blanker Eingaben wie Zahlen und Buchstaben - einer Sub oder Function übergeben werden, verändern den Wert dieser Variablen dauerhaft, also so, dass beim Rücksprung in die aufrufende Sub für diese Variablen der nun veränderte Wert gilt, auch wenn die aufgerufene Funktion ganz andere Variablennamen zur Bezeichnung dieser Übergangswerte hat.

Um eine solche Änderung zu vermeiden, muss die aufgerufene Sub oder Function die Übergabeparameter als ByVal deklariert haben - statt ByRef; was aber nicht explizit angegeben werden muss, weil dies die Default (Vorgabe)-Einstellung ist.

Ist dies klar genug ausgedrückt? Bevor Sie jetzt schon aufgeben, versuchen Sie durch langsames Lesen dieser meiner Zeilen den Sinn zu erfassen... ganz ruhig... ;-#

Es folgt nun ein Beispiel aus 3 kleinen Routinen; eine aufrufende (start, unten stehend) und 2 aufgerufene (test2 und test3). Ich kann nun nicht seitenlang beschreiben, was Sie durch schrittweises Verfolgen der Werte in den Messageboxen erkennen können, wenn Sie sich einmal die Mühe machen, mit Copy & Paste den Sourcecode in ein VBA-Modul zu übertragen und dort von Sub Start() aus zu starten. Bitte auch die Titelzeilen der Boxen zu beachten ....

Sub test3(ByVal i As Long, ByVal s As String)
'.. ByVal statt  dem default-mäßigen ByRef!
i = i + 1: s = s + "/wertzuiop"
MsgBox "in test3: " & i, , s
End Sub

Sub test2(i As Long, s As String)
'... ohne Angabe = ByRef statt ByVal
i = i + 1: s = s + "/wertzuiop"
MsgBox "in test2: " & i, , s
End Sub

Sub Start() ' <------- aufrufende Routine
Dim ii As Long, ss As String

ii = 123: ss = "abc"

Call test2(7, "Hallo")
MsgBox "in test1: " & ii, , ss
' test2 verändert nichts,
' weil ja nur blanke werte statt vars übergeben wurden

Call test2(ii, ss)
MsgBox "in test1: " & ii, , ss
' test2 verändert ii und ss, weil vars übergeben wurden
' und defaultmäßig ByRef empfangen wurden

ii = 123: ss = "abc" 'Werte wieder auf alten Wert zurücksetzen

Call test3(ii, ss)
MsgBox "in test1: " & ii, , ss
' test3 verändert ii und ss NICHT,
' denn test3 empfängt übergabewerte explizit als ByVal statt ByRef!

End Sub

B11a.    ↑    Inputbox - User-Eingabe

Sub test_Input()
Dim A 
A = InputBox("Wie alt bist Du?", "Blöde Frage")
MsgBox "Du hast also schon " & Fix(A * 365.25) & " Tage gelebt."
End Sub

If Not Isnumeric(a) Then Exit Sub

Sub test_input()
Dim A
A = InputBox("Wie alt bist Du?", "Blöde Frage")
If Not IsNumeric(A) Then
  MsgBox "Falsche Angabe, ich arbeite nicht mehr weiter!"
  Exit Sub
End If
MsgBox "Du hast also schon " & Fix(A * 365.25) & " Tage gelebt."
End Sub

B11b.    ↑    If MsgBox - User-Entscheidung

Option Explicit

Sub test()
If MsgBox("Willst Du wirklich dieses tolle Programm beenden, ohne deine Daten abzuspeichern?", _
  vbYesNo, "Eine ernste Frage an Dich") = vbYes Then
  MsgBox "Na gut, du hast es so gewollt!": Exit Sub
Else
  MsgBox "Wir machen also weiter..."
End If
End Sub

B12.   ↑    Diverse VBA-Innereien
(Goto / Stop / Exit Sub / End / Call / _ / : )

B12a.    ↑    Goto

Sub Test()
Dim a, b, c
a = [C8]: b=[D8]
If a = b Then Goto hierhin
'..
'..
hierhin:
MsgBox "Mama, fertig! "
End Sub

B12b.    ↑    Stop

Sub test()
Dim a As Long
For a = 1 To 5
  If a = 3 Then Stop
  MsgBox a * a
Next a
End Sub

B12c.    ↑    Exit Sub (Exit Function)

Sub Verteil_Aepfel(Aepfel As Long, Personen As Long)
If Personen = 0 Then Exit Sub
MsgBox "Jeder erhält also " & Aepfel / Personen & " Äpfel.", , Personen
End Sub

Sub test_Verteil_Aepfel() ' die aufrufende Routine, mit F5 starten
Dim i As Long
For i = 3 To 0 Step -1
  Call Verteil_Aepfel(8, i)
Next i
MsgBox "So, alles erledigt"
End Sub

B12d.    ↑    End

Sub Verteil_Aepfel_End(Aepfel As Long, Personen As Long)
If Personen = 0 Then
  MsgBox "Alarm! " & vbCr & "Habe alles abgebrochen, weil sonst durch Null geteilt worden wäre!!!"
  End
End If
MsgBox "Jeder erhält also " & Aepfel / Personen & " Äpfel.", , Personen
End Sub

Sub test_Verteil_Aepfel_End() ' die aufrufende Routine, mit F5 starten
Dim i As Long
For i = 3 To 0 Step -1
  Call Verteil_Aepfel_End(8, i)
Next i
MsgBox "So, alles erledigt" 'wird hier nie ausgeführt werden wegen End
End Sub

B12e.    ↑    Call

B12f.    ↑    _ (Unterstrich um die Zeile bis zur nachfolgenden zu verlängern)

Sub test()
If MsgBox("Willst Du wirklich dieses tolle Programm beenden, ohne deine Daten abzuspeichern?", _
  vbYesNo, "Eine ernste Frage an Dich") = vbYes Then
    MsgBox "Na gut, du hast es so gewollt!": Exit Sub
End If
End Sub

B12g.    ↑    : (Doppelpunkt um in selber Zeile eine weitere Anweisung einzuleiten)

Sub test(i As Long)
If i = 0 Then MsgBox "Ich darf nicht durch Null teilen!" :  Exit Sub
MsgBox "100 geteilt durch " & i & " = " & 100 / i
End Sub

Sub test_test() ' mit F5 hier starten
Call test(4)
Call test(0)
End Sub

X1a.   ↑    zum Beispiel: Pizza-Fabrik Produktionsplan

Es ist nun an der Zeit, unsere frisch erworbenen Kennnisse über den Umgang mit der Entwicklungsumgebung, über Zellzugriffe und Schleifen an einem größeren Beispiel zu erproben: Für eine Pizza-Fabrik mit 3 Produktionsstraßen soll ein Monatsplan erstellt werden. Für jede Straße wird Pizza-Typ und Tagesmenge abzüglich Umstellverluste eingetragen.

X1b.    ↑    Dateien zum Pizza-Demo in Entwicklungsstufen

pizza01_purecells.xls

Links wird der Bereichsname und rechts daneben die jeweilige Adresse angegeben. Beachten Sie, dass nicht nur einzelne Zellen, sondern auch Zellverbände ("Sortiment" und "sums") hier verwendet werden. Als einziger Bereichsname für das sheet "Change" ist "Nullpunkt" in Zelle "C3" definiert. (Wie man Bereichsnamen definiert, wird hier erklärt.)

X1c.    ↑    Funktionen ("Features", Ausstattungsmerkmale):

• a1) Summierung nach Sorten
• a2) Monat vor/zurück-schalten, Monatslänge ermitteln
• b1) ProduktKampagne einfügen: Listbox für Sortenauswahl
• b2) Mengen-Eintrag laut Ofenleistung und produktspezifisch
• b3) Umstellungsverlust je nach Produkt/Vorprodukt-Kombination
• c1/2) Verschiebung ab Zellzeiger um x Tage nach unten/oben
• d1) ganzen Plan löschen
• d2) Kampagne löschen und vorhergehende Kampgane verlängern
• d3) Umstellungsverluste der nachfolgenden Kampagne neu berechnen
• e1) Kampagne_einfügen erweitern mit Option für Mengen/Zeit-Vorgabe
• e2) manuelle Änderung von Menge/Zeit : was ist möglich?
• e3) Verlängerung durch Verschiebung der nachfolgenden Kampagnen
• e4) Umstellungsverlust für nachfolgende Kampagne neu berechnen
• f1) verschiedene Druckbereiche einrichten
• f2) Plan-Bereich als Extrakt auf separates File exportieren

X1d.     ↑    Warum gerade dieses Beispiel?

X2.    ↑    function findProdNum - Welche Produktnr. hat dieser Pizzaname

Bevor wir die Mengen der Pizza-Kampagnen nach Pizzasorten getrennt aufsummieren können, müssen wir eine Funktion schreiben, die herausfindet, in der wievielten Zeile von oben eine bestimmte Sorte eingetragen ist.

Diese Aufgabe könnte man auch direkt in der Summier-Sub einbauen; da wir dieselbe Teilfunktion aber auch nützen können zum Ermitteln der Produktintensität je nach Pizzatyp laut Produktliste in Spalte O bis P des Haupt-sheets und zur Ermittlung des Umstellzeitverlusts in der Change-Tabelle, machen wir eben eine separate Funktion daraus.

Wir wollen von Anfang an alles das, was nicht einem bestimmten Button zuzuordnen ist, auf einem separaten Modul "main" eintragen, das wir per rechter Maustaste im Project-Fenster des VBA-Screens erstellen: einfügen Modul; Eigenschaften-Fenster (name)=main statt Modul1 als Vorgabewert.

In main geben wir auch später einige wenige Variablen und Konstanten an, die wir von verschiedenen anderen Modulen aus brauchen werden.

Konstanten sind einfach Bezeichner ähnlich wie Variablen, die eine bestimmte Zahl oder ein bestimmtes Wort ersetzen, aber ohne dass diese sich noch einmal ändern sollen. Bekannteste Konstante in der Mathematik ist Pi (3,14...).

Wir tragen jetzt schon einmal in der ersten freien Zeile des main-Moduls nach Option Explicit die Konstante maxProds an, in der wir die max. Anzahl an verschiedenen Pizzasorten eintragen:

Public Const maxProds = 50

Function findProdNum(ByVal was As String) As Long

While Range("sorte1").Offset(Y, 0) <> was And Y < maxProds
  Y = Y + 1
Wend


If Y >= maxProds Then Y = 0 Else Y = Y + 1 findProdNum = Y

Function findProdNum(ByVal was As String) As Long
Dim Y As Long, c
If was = "" Then findProdNum = 0: Exit Function
Y = 0: c = ""
While Range("sorte1").Offset(Y, 0) <> was And Y < maxProds 
  Y = Y + 1 
Wend 
''MsgBox y
If Y >= maxProds Then Y = 0 Else Y = Y + 1
findProdNum = Y
End Function

Sub test_findProdNum()
MsgBox findProdNum("Milano")
End Sub

X3.    ↑    Calc - berechne die Mengen nach Pizzatyp getrennt

Private Sub cmdCalc_Click()
..
End Sub

Public Const maxTage = 31, maxProds = 50

Private Sub cmdCalc_Click()
Dim y as Long, c, pquant
  For Y = 1 To main.maxTage
    c = Range("line1").Offset(Y - 1, 0)
    pquant = Range("line1").Offset(Y - 1, (Line - 1) * 3 + 1)
  Next Y
End Sub

Die Sub teste ich ohne Button-Click, in dem ich mich mit dem Cursor irgendwo in den Quelltext stelle und auf die F5-Taste drücke.

Und wenn man nun vor der "Next Y"-Zeile einen Haltepunkt setzt, in dem man einfach mit der linken Maustaste auf den grauen Rand klickt (noch einmal klicken zum wieder-entfernen), kann man Zeile für Zeile verfolgen, welche Werte in y, in c und in pquant gesetzt sind. Hierzu fahren wir vorsichtig mit dem Mauszeiger über die gewüschte Variable und lassen diesen hier stehen, während wir mit der F5-Taste weiterschalten zur nächsten Runde...

    If c <> "" Then
      ''aha, neue Kampagne....
      ptype = c
      pnum = findProdNum(ptype)
    End If

    [sorte1].Offset(pnum - 1, 2) = [sorte1].Offset(pnum - 1, 2) + pquant

    MsgBox pquant, , ptype

Private Sub cmdCalc1_Click()
Dim y As Long, c, pquant, ptype, pnum As Long
  For y = 1 To main.maxTage
    c = Range("line1").Offset(y - 1, 0)
    If c <> "" Then
      ''aha, neue Kampagne....
      ptype = c
      pnum = findProdNum(ptype)
    End If
    pquant = Range("line1").Offset(y - 1, 1)
    [sorte1].Offset(pnum - 1, 2) = [sorte1].Offset(pnum - 1, 2) + pquant
    MsgBox pquant, , ptype
  Next y
End Sub

Range("sums").ClearContents

Private Sub cmdCalc_Click()
Dim Y As Long, Line As Long, PNum As Long
Dim c, ptype, pquant
Range("sums").ClearContents
For Line = 1 To 3
  For Y = 1 To main.maxTage
    c = Range("line1").Offset(Y - 1, (Line - 1) * 3)
    If c <> "" Then
      ''aha, neue Kampagne....
      ptype = c
      ''MsgBox y, , "neue Kampagne"
      PNum = findProdNum(ptype)
    End If
    pquant = Range("line1").Offset(Y - 1, (Line - 1) * 3 + 1)
    ''MsgBox ptype, , pquant
    [sorte1].Offset(PNum - 1, 2) = [sorte1].Offset(PNum - 1, 2) + pquant
  Next Y
Next Line
End Sub

pizza02_calcsum.xls

X4a.    ↑    Function findVorProd - Finde das Vorgänger-Produkt

Geh mit dem Zellzeiger so lange einen Schritt nach oben, bis du auf einen anderen Produktnamen stößt (oder über die erste Zeile hinaus geraten bist).

Function findVorProd() As String

Dim x As Long, Y As Long
x = ActiveCell.Column: Y = ActiveCell.Row

Do
  Y = Y - 1
Loop Until Cells(Y, x) <> "" Or < 4

Do While Cells(Y, x) <> "" And Y >= 4
  Y = Y + 1
Loop

If Y < 4 Then findVorProd = "" Else findVorProd = Cells(Y, x)
End Function

Function findVorProd() As String
Dim x As Long, Y As Long
x = ActiveCell.Column: Y = ActiveCell.Row
Do
  Y = Y - 1
Loop Until Cells(Y, x) <> "" Or Y < 4
If Y < 4 Then findVorProd = "" Else findVorProd = Cells(Y, x)
End Function

Sub testfindVorProd()
MsgBox findVorProd
End Sub

X4b.    ↑    Function findNextProdRow - Nachfolgerzeile finden

Die Zelle [M1] benennen wir "maxro" - wir werden später noch erfahren, wie die letzte Zeile eines Monats errechnet wird, vorerst tragen wir einfach manuell den Wert "35" für einen 31-tägigen Monat ein. In Excel kann man Bereichsnamen bekanntlich angeben, in dem man einen bestimmten Zellbereich markiert (oder einfach nur für 1 einzige Zelle den Zellzeiger irgendwohin stellt) und dann aus dem Excel-Menü Einfügen / Namen / Festlegen wählt und dort den Namen eingibt. Wenn Ihnen das immer noch zu 'hoch' ist, schauen Sie sich bitte in C3c an, wie man das auch noch machen kann.

Function findNextProdRow() As Long
Dim x As Long, Y As Long
x = ActiveCell.Column: Y = ActiveCell.Row + 1
While Cells(Y, x) = "" And Y <= [maxro]
  Y = Y + 1
Wend
findNextProdRow = Y
End Function

Sub test_findNextProdRow()
MsgBox findNextProdRow
End Sub

Ab 1 Zelle unterhalb des Zellzeigers (ActiveCell.Row + 1) solange weiterrücken (While .. Y = Y + 1 .. Wend ), wie nichts in der Zelle zu finden ist und man sich noch innerhalb des Berichs bis incl. Zeile 35 [maxro] befindet. Dann den Wert der jetzigen Zeilennummer (y) wieder zurückgeben an den Funktionsaufrufer (findNextProdRow = Y). - Fertig!

X4c.    ↑    Function findProdBeginRow - Beginn eines Produktionsabschnitts finden

Function findProdBeginRow() As Long
Dim x As Long, Y As Long
x = ActiveCell.Column: Y = ActiveCell.Row
While Cells(Y, x) = "" And Y > 5
  Y = Y - 1
Wend
findProdBeginRow = Y
End Function

Sub test_findProdBeginRow()
MsgBox findProdBeginRow
End Sub

Solange du keine nicht-leere Zelle vorfindest und die Zeile des ersten Monatstags (5) nicht unterschreitest, schau eine Zelle darüber nach.

X4d.    ↑    Produktionsabschnitt löschen

Ermittle den Produktionsabschnittsbeginn ab Zellzeigerposition und die Zeile über dem nächsten Produktionsabschnitt darunter und markiere diesen Bereich 2 Spalten breit. Stell noch einmal die Frage, ob der User wirklich löschen will; wenn ja, dann lösch!

Private Sub cmdClear1_Click()
Dim Ro1 As Long, Ro2 As Long, Co As Long
Co = ActiveCell.Column
Ro1 = main.findProdBeginRow
Ro2 = main.findNextProdRow - 1
End Sub

Range(Cells(Ro1, Co), Cells(Ro2, Co + 1)).Select

If MsgBox("wirklich diese Kampagne löschen?", vbYesNoCancel, "myprog") = vbYes Then
  Selection.ClearContents
End If

Private Sub cmdClear1_Click()
Dim Ro1 As Long, Ro2 As Long, Co As Long
Co = ActiveCell.Column
Ro1 = main.findProdBeginRow
Ro2 = main.findNextProdRow - 1
Range(Cells(Ro1, Co), Cells(Ro2, Co + 1)).Select
If MsgBox("wirklich diese Kampagne löschen?", vbYesNoCancel, "myprog") = vbYes Then
  Selection.ClearContents
End If
Cells(Ro1, Co).Select
End Sub

X5a.    ↑    Function calcQuantperday - Tagesmenge ermitteln

Function calcQuantperday(Co As Long, PNum) As Double
'Tagesleistung=Ofenkapazität * produktspezifische Intensität
calcQuantperday = Cells(2, Co + 1) * [sorte1].Offset(PNum - 1, 1)
End Function

Sub test_calcQuantperday()
MsgBox calcQuantperday(4, 5)
End Sub

X5b.    ↑    Function calcDaysforchange - Umstellungsdauer ermitteln

Geh in der Umstelltabelle vom [Nullpunkt] aus neu-Produktnummer Zellen nach rechts und alt-Produktnummer Zellen nach unten und gib diesen Wert zurück.

Function calcDaysforchange(PNum As Long, PreNum As Long) As Double
calcDaysforchange = [change!Nullpunkt].Offset(PreNum, PNum)
End Function

Function calcDaysforchange(PNum As Long, PreNum As Long) As Double
'Umstelltage aus Tabelle holen, falls wirklich P-Wechsel vorliegt...
If PNum <> PreNum And PreNum > 0 Then
  calcDaysforchange = [change!Nullpunkt].Offset(PreNum, PNum)
Else
  calcDaysforchange = 0
End If
End Function

Sub test_calcDaysforchange()
MsgBox calcDaysforchange(4, 2)
End Sub

X6.    ↑    Sub WriteQuants - Produktionsabschnittsmengen eintragen

• Der Sub wird die Zellzeile und die Zellposition des Startpunkts übergeben:
• Im Startpunkt ist schon der Produktname eingetragen.

Sub writeQuants(Ro As Long, Co As Long)
Dim P As String, Pre As String, PNum As Long, PreNum As Long

'Produktnummern von Produkt und Vorgänger ermitteln...
Cells(Ro, Co).Select
P = Cells(Ro, Co): PNum = findProdNum(P)

Cells(Ro, Co).Select
Pre = findVorProd: PreNum = findProdNum(Pre)

End Sub

Sub test_writeQuants()
Call writeQuants(12, 7)
End Sub

'wieviel tage lang ...
Cells(Ro, Co).Select
nextRo = findNextProdRow '.. also nachfolger suchen um kampagnenende zu kriegen
Days = nextRo - Ro

Dim nextRo As Long, Days As Long

Dim Daysforchange As Double, Quantperday As Double, D As Long

'wieviel produzieren wir pro normalen tag (also ohne umstellverlust) ...
Quantperday = main.calcQuantperday(Co, PNum)

'wieviel umstelltage...
Daysforchange = main.calcDaysforchange(PNum, PreNum)

Sub writeQuants(Ro As Long, Co As Long)
Dim P As String, Pre As String, PNum As Long, PreNum As Long
Dim nextRo As Long, Days As Long
Dim Daysforchange As Double, Quantperday As Double, D As Long

'Produktnummern von Produkt und Vorgänger ermitteln...
Cells(Ro, Co).Select
P = Cells(Ro, Co): PNum = findProdNum(P)

Cells(Ro, Co).Select
Pre = findVorProd: PreNum = findProdNum(Pre)

'wieviel tage lang ...
Cells(Ro, Co).Select
nextRo = findNextProdRow '.. also nachfolger suchen um kampagnenende zu kriegen
Days = nextRo - Ro

'wieviel produzieren wir pro normalen tag (also ohne umstellverlust) ...
Quantperday = main.calcQuantperday(Co, PNum)

'wieviel umstelltage...
Daysforchange = main.calcDaysforchange(PNum, PreNum)

End Sub

For D = 0 To Days - 1
  Cells(Ro + D, Co + 1) = Quantperday
Next D

'Tagesmengen eintragen...
For D = 0 To Days - 1
  If Daysforchange > 0 Then
    Cells(Ro + D, Co + 1) = 0
    Daysforchange = Daysforchange - 1 '  .. 1 tag weniger zu berücks.
  Else
    Cells(Ro + D, Co + 1) = Quantperday
  End If
Next D

If Daysforchange >= 1 Then
  Cells(Ro + D, Co + 1) = 0
  Daysforchange = Daysforchange - 1 '  .. 1 tag weniger zu berücks.
Else
  Cells(Ro + D, Co + 1) = (1 - Daysforchange) * Quantperday
  Daysforchange = 0 ' nichts mehr zu berücks.; ab nächstem tag volle leistung
End If

Sub writeQuants(Ro As Long, Co As Long)
'Tagesmengen eintragen - dabei umstellzeit berücksichtigen
Dim P As String, Pre As String, PNum As Long, PreNum As Long
Dim Days As Long, nextRo As Long
Dim Daysforchange As Double, Quantperday As Double, D As Long

'Produktnummern von Produkt und Vorgänger ermitteln...
Cells(Ro, Co).Select
P = Cells(Ro, Co): PNum = findProdNum(P)

Cells(Ro, Co).Select
Pre = findVorProd: PreNum = findProdNum(Pre)

'wieviel tage lang ...
Cells(Ro, Co).Select
nextRo = findNextProdRow '.. also nachfolger suchen um kampagnenende zu kriegen
Days = nextRo - Ro

'wieviel produzieren wir pro normalen tag (also ohne umstellverlust) ...
Quantperday = main.calcQuantperday(Co, PNum)

'wieviel umstelltage...
Daysforchange = main.calcDaysforchange(PNum, PreNum)

'Tagesmengen eintragen...
For D = 0 To Days - 1
  If Daysforchange > 0 Then
    If Daysforchange >= 1 Then
      Cells(Ro + D, Co + 1) = 0
      Daysforchange = Daysforchange - 1 '  .. 1 tag weniger zu berücks.
    Else
      Cells(Ro + D, Co + 1) = (1 - Daysforchange) * Quantperday
      Daysforchange = 0 ' nichts mehr zu berücks.  - ab nächstem tag volle leistung
    End If
  Else
    Cells(Ro + D, Co + 1) = Quantperday
  End If
Next D
End Sub

pizza03_find.xls

 ↑  C. Button & Form - Buttons, Listen, Formularbau

C1.    ↑    Events - Ereignisse wie Button_click und Before_Close)

Private Sub CommandButton2_MouseMove(ByVal Button As Integer, _
   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox "Halt!"
End Sub

Private Sub Workbook_Open()
MsgBox "Welcome to that file, my friend!"
End Sub

othereventtypes.xls

C2a.   ↑    Form anlegen

pizza03_find.xls

C2b.   ↑    Form benennen und betiteln

C3a.    ↑    Listbox anlegen und mit Daten in Excel-cells verknüpfen

C3b.     ↑    Rowsource

C3c.    ↑    Relative Adressierung per Bereichsname ist besser als die nackte Adresse

C3d.    ↑    Listbox mitsamt der UserForm starten - per Button

Private Sub cmdNewP_Click()
frmJob.Show
End Sub

C3e.    ↑     AutoCompletion

C3f.    ↑    List-Click auswerten

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox1_Click()
MsgBox ListBox1.ListIndex
End Sub

C3g.    ↑    Test

C3h.    ↑    ggf. Liste noch mal checken

• Button anlegen (cmdNewp)
• Form anlegen (frmJob)
• Listbox in Form anlegen (ListBox1)
• Im Excel-sheet Bereich für Liste benennen (Sortiment)
• In Listbox1 Rowsource eintragen (Sortiment)
• Button_Click anlegen und frmJob.Show eintragen
• ListBox1_Click anlegen und dort "MsgBox ListBox1.ListIndex" eingeben
• Entwurfmodus ausschalten und Button auf Excel-Sheet starten

C4.    ↑    andere Toolbox-Objekte : Label, Textbox, Options

Labels - sind für Textausgaben (nicht für Texteingaben) vorgesehen. Es kann sich hier um feste Überschriften o.ä. handeln, aber auch um Werte, die sich während des Programmlaufs ändern und als Label ausgegeben werden. TextBoxes - Ich finde das dt. "Textfeld" unterscheidet sich nicht eindeutig genug von Label/Bezeichnungsfeld. Jedenfalls ist der Inhalt der Texteingabe in der Eigenschaft Value zu finden. Könnte also auch als Textausgabe-Box missbraucht werden, indem man Value besetzt. Ereignis zur Abfrage des so eben Eingegebenen ist Exit ! OptionButton - Es handelt sich vorgabemäßig um entweder-oder-Schalter; es sollten also mindestens 2 davon angelegt werden, die sich dann wechselseitig automatisch ausschließen, ohne dass man da noch extra programmieren muss. Man kann also für genau einen davon den Value vorgabemäßig = True eintragen.

C4b.   ↑    Objekte in Form anlegen

Wir werden nun neben der Listbox in unserer Form zusätzliche Toolbox-Elemente hinzufügen, bis wir diese Form hier ungefähr aufgebaut haben werden:

pizza04_listbox.xls

C5.    ↑    Objekt-Logik - Ablauf steuern durch ausblenden

• Man kann zB die Form-Elemente zunächst teilweise ausblenden und diese erst dann einblenden, wenn eine bestimmte Operation in der User-Form durchgeführt worden ist.
• Man kann auch Vorgabewerte für Eingabefelder je nach Lage berechnen und dort anbieten.
• Man kann Optionsschalter je nach Situation auf einen bestimmten Vorgabewert setzen und davon wieder abhängig machen, ob dazugehörende Eingabefelder aktiviert sind oder nicht.
• Man kann die ganze Maske per OK-Button beenden und per Cancel so beenden, dass die Information "abgebrochen" irgendwo abgespeichert wird, damit dann auch die weitere Abfolge abbricht
• etc. ...

X7.    ↑    Ablaufsteuerung Pizza-Plan

X8.    ↑    Pizza-Plan: Eingabefelder erst nach Listbox-Wahl zeigen

pizza03_listbox.xls

Private Sub cmdNewP_Click()
frmJob.tbQuantperjob.Visible = False
frmJob.tbDays.Visible = False
frmJob.OptionButton1.Visible = False
frmJob.OptionButton2.Visible = False
frmJob.Show
End Sub

Private Sub ListBox1_Click()
If frmJob.ListBox1.ListIndex > 0 Then
  'kann ich die nachfolgenden optionen sichtbar machen....
  frmJob.tbQuantperjob.Visible = True
  frmJob.tbDays.Visible = True
  frmJob.OptionButton1.Visible = True
  frmJob.OptionButton2.Visible = True
Else
  'wenn nur titelzeile gewählt wurde, lieber (wieder) nachfolgende
  'optionen unsichtbar machen....
  frmJob.tbQuantperjob.Visible = False
  frmJob.tbDays.Visible = False
  frmJob.OptionButton1.Visible = False
  frmJob.OptionButton2.Visible = False
End If
End Sub

X9.    ↑    Pizza-Plan: Kampagnenmenge bei Auswahl einer Sorte einblenden

Private Sub ListBox1_Click()
Dim Co As Long
Co = ActiveCell.Column
..
..

If frmJob.ListBox1.ListIndex > 0 Then 
  .. (PNum ermitteln)
  ..
  .. (Objekte sichtbar schalten)
  ..
  'menge für ganze kampagne berechnen....
  [Quantperday] = main.calcQuantperday(Co, [PNum])
  [Daysforchange] = main.calcDaysforchange([PNum], [PreNum])
  [quantperjob] = ([Days] - [Daysforchange]) * [Quantperday]
  frmJob.tbQuantperjob = CInt([quantperjob])
Else
  ..
End If

Private Sub ListBox1_Click()
Dim Co As Long
Co = ActiveCell.Column

If frmJob.ListBox1.ListIndex > 0 Then
  [PNum] = frmJob.ListBox1.ListIndex
  [PNam] = frmJob.ListBox1.Text
  'kann ich die nachfolgenden optionen sichtbar machen....
  frmJob.tbQuantperjob.Visible = True
  frmJob.tbDays.Visible = True
  frmJob.OptionButton1.Visible = True
  frmJob.OptionButton2.Visible = True
  
  'menge für ganze kampagne berechnen....
  [Quantperday] = main.calcQuantperday(Co, [PNum])
  [Daysforchange] = main.calcDaysforchange([PNum], [PreNum])
  [quantperjob] = ([Days] - [Daysforchange]) * [Quantperday]
  frmJob.tbQuantperjob = CInt([quantperjob])
Else
  'wenn nur titelzeile gewählt wurde, lieber (wieder) nachfolgende
  'optionen unsichtbar machen....
  frmJob.tbQuantperjob.Visible = False
  frmJob.tbDays.Visible = False
  frmJob.OptionButton1.Visible = False
  frmJob.OptionButton2.Visible = False
End If

End Sub

X10.    ↑    Pizza-Plan: im newP-Schalter Form vorbereiten (Kampagnenlänge...)

Dim Ro As Long, oldRo As Long, Co As Long, D As Long, v As Long, R2 As Long
Ro = ActiveCell.Row: Co = ActiveCell.Column: oldRo = Ro

'korrekter eingabe-bereich? .................
If Ro < 5 Or Ro > [maxro] Then GoTo mySubEnd
If Co <> 4 And Co <> 7 And Co <> 10 Then GoTo mySubEnd

'vorgabewerte für userform berechnen.............
[Prenam] = main.findVorProd '...vorprod ermitteln
[PreNum] = main.findProdNum([Prenam]) '... produktnr. des Vorprods laut Liste

[daystonext] = main.findNextProdRow - Ro
  frmJob.lbDaystoNext.Caption = [daystonext]
  frmJob.tbDays.Value = [daystonext]
  [Days] = [daystonext]

[daysToMonEnd] = [maxro] - Ro + 1 
  frmJob.lbDaystomonend.Caption = [daysToMonEnd]

[Nexnam] = Cells(Ro + [daystonext], Co)
[nexNum] = main.findProdNum([Nexnam])

[daystoprolong] = 0
  frmJob.lbDaystoprolong.Caption = ""

Private Sub cmdNewP_Click()
Dim Ro As Long, oldRo As Long, Co As Long, D As Long, v As Long, R2 As Long
Ro = ActiveCell.Row: Co = ActiveCell.Column: oldRo = Ro

'korrekter eingabe-bereich? .................
If Ro < 5 Or Ro > [maxro] Then GoTo mySubend
If Co <> 4 And Co <> 7 And Co <> 10 Then GoTo mySubend

'vorgabewerte für userform berechnen.............
[Prenam] = main.findVorProd '...vorprod ermitteln
[PreNum] = main.findProdNum([Prenam]) '... produktnr. des Vorprods laut Liste
[daystonext] = main.findNextProdRow - Ro
  frmJob.lbDaystoNext.Caption = [daystonext]
  frmJob.tbDays.Value = [daystonext]
  [Days] = [daystonext]
[daysToMonEnd] = [maxro] - Ro + 1 '' später für variable Monatslänge erweitern!
  frmJob.lbDaystomonend.Caption = [daysToMonEnd]
[Nexnam] = Cells(Ro + [daystonext], Co)
[nexNum] = main.findProdNum([Nexnam])
[daystoprolong] = 0
  frmJob.lbDaystoprolong.Caption = ""

frmJob.tbQuantperjob.Visible = False
frmJob.tbDays.Visible = False
frmJob.OptionButton1.Visible = False
frmJob.OptionButton2.Visible = False
frmJob.Show
mySubend:
End Sub

X11.    ↑    Pizza-Plan: Tagesmengen einer Kampagne eintragen

If frmJob.ListBox1.ListIndex > 0 Then
  [PNum] = frmJob.ListBox1.ListIndex
  [PNam] = frmJob.ListBox1.Text
  ..
  ..

Public myCancelled As Boolean

Private Sub cmdCancel_Click()
frmJob.Hide
'myCancelled bleibt also auf startwert (true)
End Sub

Private Sub cmdOK_Click()
frmJob.Hide ' form auf jeden fall schon mal ausknipsen
If [Days] < [Daysforchange] Then
  MsgBox "Geht nicht - Umstellzeit wäre länger als diese Kampagne! "
  Exit Sub ' also so dass mycancelled immer noch auf true bleibt!
End If
If frmJob.ListBox1.ListIndex <= 0 Then Exit Sub
If [PNum] = [PreNum] Then Exit Sub
' erst wenn alle prüfungen überstanden, mycancelled auf false setzen....
myCancelled = False
End Sub

'userform aufrufen
myCancelled = True '.. form könnte ja auch durch window-close ohne cancel-button geschlossen werden
frmJob.Show
If myCancelled Then GoTo mySubEnd ''Exit Sub
'Kampagne eintragen.....
' ..

'ggf Namen eintragen.......
If [PNam] <> [Prenam] Then
  Cells(Ro, Co) = [PNam] 'name nur wenn ungleich vorprod
Else
  Cells(Ro, Co) = "" ''sicherheitshalber löschen falls hier etwas übeschrieben wurde
End If

Call writeQuants(Ro, Co)

mySubEnd:
End Sub

Private Sub cmdNewP_Click()
Dim Ro As Long, oldRo As Long, Co As Long, D As Long, v As Long, R2 As Long
Ro = ActiveCell.Row: Co = ActiveCell.Column: oldRo = Ro

'korrekter eingabe-bereich? .................
If Ro < 5 Or Ro > [maxro] Then GoTo mySubEnd
If Co <> 4 And Co <> 7 And Co <> 10 Then GoTo mySubEnd

'vorgabewerte für userform berechnen.............
[Prenam] = main.findVorProd '...vorprod ermitteln
[PreNum] = main.findProdNum([Prenam]) '... produktnr. des Vorprods laut Liste
[daystonext] = main.findNextProdRow - Ro
  frmJob.lbDaystoNext.Caption = [daystonext]
  frmJob.tbDays.Value = [daystonext]
  [Days] = [daystonext]
[daysToMonEnd] = [maxro] - Ro + 1 '' später für variable Monatslänge erweitern!
  frmJob.lbDaystomonend.Caption = [daysToMonEnd]
[Nexnam] = Cells(Ro + [daystonext], Co)
[nexNum] = main.findProdNum([Nexnam])
[daystoprolong] = 0
  frmJob.lbDaystoprolong.Caption = ""

frmJob.tbQuantperjob.Visible = False
frmJob.tbDays.Visible = False
frmJob.OptionButton1.Visible = False
frmJob.OptionButton2.Visible = False

 
'userform aufrufen
myCancelled = True '.. form könnte ja auch durch window-close ohne cancel-button geschlossen werden
frmJob.Show
If myCancelled Then GoTo mySubEnd ''Exit Sub

'Kampagne eintragen.....
'  ggf Namen eintragen.......
If [PNam] <> [Prenam] Then
  Cells(Ro, Co) = [PNam] 'name nur wenn ungleich vorprod
Else
  Cells(Ro, Co) = "" ''sicherheitshalber löschen falls hier etwas übeschrieben wurde
End If

'Mengen eintragen.....
Call writeQuants(Ro, Co)

mySubEnd:
End Sub

pizza05_logic.xls

X12.    ↑    Hinweise zu weiteren Funktionen

pizza06_move.xls

If [plan!autocalc] = "ein" Then Call cmdCalc_Click

 ↑  D. File & Error - Fehlerbehandlung und Systemfunktionen

D1a.   ↑    Error!

D1b.   ↑    On Error Resume Next .. On Error Goto 0

On Error Resume Next
ActiveWorkbook.SaveAs FileName:=fnam, FileFormat:= _
  xlWorkbookNormal, Password:="", CreateBackup:=False
If Err.Number <> 0 Then MsgBox ("kann Datei nicht schreiben: " + fnam)
On Error GoTo 0

D2a.   ↑    Debug (zur Kontrolle schrittweise ablaufen lassen)

D2b.   ↑    Kompilieren

D3.   ↑    Debugger -später- nicht vergessen auszuschalten!

Achtung! Wundern Sie sich nicht, wenn Sie keine Buttons mehr ausführen können, solange der Debug-Mode noch aktiv ist ...

Debug gestoppt - jetzt können Sie wieder klicken ....

D4.   ↑    Der Macrorecorder und seine Grenzen

Der Makrorecorder ist ein sehr nützliches Element zum Aufzeichnen aller Bearbeitungsschritte und Eingaben (Excel-Menü: Extras / Makros / Aufzeichnen).

Viele Dinge, die wir gerne automatisieren wollen, lassen sich zwar aufzeichnen, funktionieren aber dann auch nur in genau demselben Zusammenhang. Wenn ich zB die Aufsummierung der Pizza-Tagesmengen aus den 3 Ofenstraßen automatisieren möchte, würde der Makro-Recorder zwar getreulich jeden Tastenschlag von mir aufzeichen - aber ich könnte den Code nur genau dann noch einmal verwenden, wenn sich exakt die gleiche Aufteilung des Produktionsplans ergeben würde; Wenn auch nur 1 Kampagne für einen Tag später eingegeben würde, würde mein aufgezeichneter Code ja schon daneben greifen und falsch rechnen. Wenn der Makro-Recorder uns jegliche Programmier-Arbeit abnehmen würde, würden wir uns ja auch erst gar nicht die Mühe machen müssen, in die VBA-Programmierung einzusteigen....

Es gibt aber immer noch viele wiederkehrende Aufgaben, die exakt in der gleichen Weise ablaufen - hier bietet sich also das Aufzeichnung der Bearbeitungsschritte per Makro-Recorder an. Wie ZB hier beim Vorbereiten eines Ausdrucks.

Schaut man sich dann den aufgezeichneten Code an (meistens in einem separaten Project-Modul abgelegt), ist man zunächst verblüfft über die Menge an Quelltext, die herbeigezaubert worden ist. Man erfährt auf diese Weise ganz einfach, wie die Funktionen und Objekte mit all ihren Einstellungen in VBA genannt werden. So weit so gut.

Wenn man aber genauer hinschaut, fällt einem auf, wie wortreich - um nicht zu sagen geschwätzig - diese Aufzeichnungen sind. Viel zu viel 'Zeug', das wir gar nicht eingestellt haben, wird in seiner Default-Einstellung mitaufgezählt. So haben wir nun das Problem, die interessanten Stellen nicht mehr davon unterscheiden zu können, wenn wir den vom Recorder gelieferten Code manuell überarbeiten müssen. Und überarbeiten müssen wir den Code häufiger, als uns lieb ist.

Wir sollten zB bei einer Makro-Aufzeichnung, mit der wir unsere Seite zum Drucken einrichten eine 600- dpi-Drucker-Einstellung, die mitaufgezeicnet wurde, wieder entfernen, da sich dieser Wert nur auf unseren eigenen Standard-Drucker bezieht. Andere User haben vielleicht nur einen 300-dpi-Drucker und bekommen nur 'Hieroglyphen' ausgedruckt, wenn diese Anweisung trotzdem bestehen bleibt!

Sehen Sie sich einmal die Makro-Aufzeichnung einer typischen Druck-Session an - man beachte den aufgeblähten Code:

Option Explicit

Sub DruckMakro_RecorderAufzeichnung()
'
' Makro3 Makro
' Makro am 18.05.2003 von a aufgezeichnet
'

'
    Application.Goto Reference:="druckplan"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.787401575)
        .RightMargin = Application.InchesToPoints(0.787401575)
        .TopMargin = Application.InchesToPoints(0.984251969)
        .BottomMargin = Application.InchesToPoints(0.984251969)
        .HeaderMargin = Application.InchesToPoints(0.4921259845)
        .FooterMargin = Application.InchesToPoints(0.4921259845)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveSheet.PageSetup.PrintArea = "$B$1:$M$35"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.787401575)
        .RightMargin = Application.InchesToPoints(0.787401575)
        .TopMargin = Application.InchesToPoints(0.984251969)
        .BottomMargin = Application.InchesToPoints(0.984251969)
        .HeaderMargin = Application.InchesToPoints(0.4921259845)
        .FooterMargin = Application.InchesToPoints(0.4921259845)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

D5.   ↑    Druck einrichten

Sub DruckPlan()
Sheets("Plan").Activate: [Plan!A1].Select
ActiveSheet.PageSetup.PrintArea = "Plan!B$2:$M$35"
ActiveSheet.PageSetup.Orientation = xlPortrait ' /xlLandscape (Hoch/Querformat)
ActiveSheet.PageSetup.FitToPagesWide = 1 ' alles an Seitenbreite anpassen
ActiveSheet.PageSetup.FitToPagesTall = 1 ' alles an Seitenhöhe anpassen
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

D6.   ↑    Zelllbereich in separate Datei exportieren

Sub iExportPart(mySheetName As String, rangeName As String, fnam As String, FormelnAlsWerte As Variant)
' Aufgabe: save ein Range aus she=arbBlatt unter dem Namen fnam
' optional FormelNotVal: true=zellformeln als zellformeln - sonst als werte
Sheets(mySheetName).Activate
ThisWorkbook.Activate
Range(rangeName).Select
Selection.Copy
Workbooks.Add
[A1].Select
Sheets("tabelle1").Activate
If FormelnAlsWerte Then
  Selection.PasteSpecial Paste:=xlPasteValues
Else
   ActiveSheet.Paste
End If

On Error Resume Next
Kill fnam
On Error GoTo 0

On Error Resume Next
ActiveWorkbook.SaveAs FileName:=fnam, FileFormat:= _
  xlWorkbookNormal, Password:="", CreateBackup:=False
If Err.Number <> 0 Then MsgBox ("kann nicht schreiben: " + fnam)
On Error GoTo 0

ActiveWorkbook.Close savechanges:=False
[A1].Select 'um meldung zu vermeiden "es befinden sich große Datenmengen in Zwischenablage"
End Sub

Private Sub test_exp()
Call Util.iExportPart("change", "druckumstell", "test2.xls", True)
End Sub

Diverses

D7.   ↑    Quelltext (Sourcecode) verstecken - wenn es denn unbedingt sein muss...

D8.   ↑    Get Network User Name (angemeldeten Benutzernamen ermitteln)

Declare Function GetNetworkUserName Lib "advapi32.dll" Alias "GetUserNameA" _ 
    (ByVal lpBuffer As String, nSize As Long) As Long
Function iGetNetUserName() As String
Dim MaxLen As Long, Result As Long, netUser As String
netUser = String$(254, 0)
MaxLen = 255
Result = GetNetworkUserName(netUser, MaxLen)
iGetNetUserName = Left$(netUser, MaxLen - 1)
'MsgBox iGetNetUserName
End Function

Sub test_netuser()
MsgBox iGetNetUserName
End Sub

D9.   ↑    weitere Funktionen kurz dargestellt (Copy & Paste, Screenupdating etc.)

# Excel-Funktionen benutzen: zB WorksheetFunction.Round(17.3456, 3)

# ActiveCell.Row (ActiveCell.Column) : Zeilennummer (Spalten) der 
  aktuellen Zellzeigerposition

# Set myRange=[A1:D7] - Range einer Variable zuweisen

# With als Abkürzung für ein vollständigiges Objekt bei mehrfachem Zugriff,zB:
  With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = 1
  End With

# Die letzte Spalte finden, für die in Zeile 1 Inhalt vorliegt:
  Range("IV1").Select
  Cols = Selection.End(xlToLeft).Column

# Copy & Paste - Beispiel:
  Sheets("Plan").Activate

  'a) copy.......
  Range(Cells(Ro, Co), Cells([maxro] - Dist, Co + 1)).Select
  Selection.Copy

  'b) zellzeiger woanders positionieren ...
  Cells(Ro + Dist, Co).Select

  'c) einfügen ''ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlValues 'nur werte, nicht aber formate

# Application.Wait = Now + TimeSerial(0, 0, 2) = 2 sec warten

# Unload me  me=object, indem prog gerade steht, zB Userform

# formXY.Repaint : Form refreshen (statt .hide und danach wieder .show)

# Application.DisplayAlerts = False : um Warnhinweise nicht zu zeigen

# Application.Calculation = xlCalculationManual 
  auf manuelle Zellberechnung stellen um Berechnungsablauf zu kontrollieren
  !!! Achtung unbedingt vorher folgenden perversen Term durchführen:
  ActiveSheet.Activate: Range(ActiveCell.Address).Select
  Achtung! Später wieder auf xlCalculationAutomatic setzen, sonst werden auch 
  andere Excel-Files mit abgeschalteter Zell-Neuberechnung gestartet!

# Application.ScreenUpdating = False   um Bild "einzufrieren"
  und somit enorme Beschleunigung zu erreichen
  und somit User-Verwirrung zu dämpfen

# Application.Visible = false um Excel zu verbergen - Vorsicht, Verwirrung vorprogrammiert!

# einem Makro einen ShortCut zuweisen, zB:
  Application.MacroOptions Macro:="pack", Description:= "",ShortcutKey:="c"

# Statusbar schreiben, zB: Application.StatusBar ="Berechnung läuft...." 
  Statusbar löschen : Application.StatusBar = False 

# Now : Datum und Uhrzeit von jetzt als Tagnummer seit 01.01.1900 mit Dezimalstellen für angebrochenen Tag 


	
Konvertierungen:

# S = Format(Now, "dd.mm.yyyy hh:mm:ss") Jetzt-Zeit formatieren 
  -  auch andere Formatangaben mögl. zB "dd.mm"

# m = 7 : A = DateSerial(2003, m, 1)  : Tagnummer zusammensetzen	

# Mid(S, 4, 2)	 : ab 4. Buchstaben 2 Buchstaben aus String S
	
# Len(S)	 : Länge eines Strings S

# S = "Hallo " & 123  : String plus anderen Ausdruck zusammenfügen

# A = CLng(" 123,6") : Ausdruck zu Long konvertieren (ergibt 124)

# A = Fix(123.6) :  zu Ganzzahl abschneiden (ergibt 123)

# A = CDbl(" 123,6") * 10 : Ausdruck zu Long (ergibt 1236)

# S = "abc" & cStr(123.456*2)  : Zahl zu String (ergibt "abc246.912")

 ↑  p. End & Links Zukunft von VBA, Links zu Userforen

p1.   ↑    VBA sinnvoll einsetzen

• ein komplexer Aufbau kann viel übersichtlicher in Programmierzeilen angeordnet und kommentiert werden, als dies mit einer Kombination von verschiedenen Zellformeln möglich wäre
• Das Zerreißen von Zellverbänden durch Verschieben von Bereichsteilen führt zu ungültigen Formel-Ausdrücken wie zB [Summe(A1:#BEZUG!)], die mühsam rekonstruiert werden müssen - mit VBA lässt sich das sicher umgehen
• es können mit VBA sogar eigene Zellfunktionen entwickelt werden, die genau so wie zB die Summenfunktion von Excel angewandt werden können
• der Zugiff auf Windows-Funktionen wie SendMail und Getnetuser ist möglich
• die Grundbefehle von VBA sind universal und somit leicht von anderen / auf andere Sprachen übertragbar

p2.   ↑    Ausblick

p3.   ↑    Demo-Download und Sicherheit

p4.   ↑    Das komplette Listing von pizza05_logic.xls

pizza05_plan.txt

pizza05_main.txt

pizza05_frmjob.txt

p5.   ↑    Das komplette Listing von pizza06_move.xls

pizza06_plan.txt

pizza06_main.txt

pizza06_frmjob.txt

pizza06_util.txt

p6.   ↑    Links