Inhaltsverzeichnis
1 Funktionen für Excel und VBA
1.1 Erstellen einer VBA Funktion
1.2 Makros, aufnehmen und bearbeiten
1.3 Einführung in die VBA Programmierung
1.4 Eigenes Programm schreiben
1.5 Berechnungen in eigener Benutzeroberfläche ausführen
1.6 Menüs programmieren
1.7 Grafische Darstellungen in 3D
1.8 Dreiecksdiagramme
1.9 Datenaustausch mit Dateien
2 Mathematische Methoden
2.1 Funktionen und ihre grafische Darstellung
2.2 Berechnen von Reihen
2.3 Steigung und Minimum einer Funktion
2.4 Nullstellensuche
2.5 Lösen von kubischen Gleichungen, die Cardanische Formel
2.6 Lösen von Gleichungssystemen, die Gauß-Jordan Methode
2.7 Numerische Integration nach Simpson
2.8 Numerische Lösung von Differentialgleichungen, die Runge-Kutta-Methode
2.9 Partielle Differentialgleichungen
2.10 Lineare Regression
3 Anwendungen in Chemie und Verfahrenstechnik
3.1 Maßeinheiten und deren Umrechnung
3.2 Berechnung von Gemischen
3.3 Molgewicht eines Moleküls aus der Summenformel
3.4 Füllstandsberechnung von Behältern
3.5 Reale Gasgleichung nach van der Waals und Soave-Redlich-Kwong
3.6 Kompression und Expansion eines Gases
3.7 Kompression realer Gase
3.8 Die barometrische Höhenformel der Atmosphäre
3.9 Molekularpotentiale nach Coulomb
3.10 Chemisches Gleichgewicht nach van′t Hoff und Gibbs
3.11 Methanisierung-Shift nach van t’Hoff
3.12 Reaktion nach Gibbs
3.13 Chemische kinetische Reaktion nach Arrhenius
3.14 Verbrennungsrechnung
3.15 Polymerisation
3.16 Elektrochemische Reaktion, Brennstoffzelle
3.17 Wärme- und Stoffaustausch, stationär und instationär
3.18 Dampf-Flüssiggleichgewicht, McCabe-Thiele-Diagramm
3.19 Flüssig-Flüssiggleichgewicht
3.20 Fest-Flüssiggleichgewicht
3.21 Batchdestillation nach Rayleigh und Schlünder
3.22 Das Biot-Savart-Gesetz und dessen Anwendung
4 Anhang
4.1 Auswahlmenü in Excel
4.2 Kopieren von Excel Tabellen
4.3 Inhaltsverzeichnis
4.4 Formelnummerierung
4.5 Tastenkombination
5 Literaturverzeichnis
Index
Beachten Sie bitte auch weitere interessante Titel zu diesem Thema
Green, J., Bullen, S., Bovey, R.,
Alexander, M.
Excel 2007 VBA
Programmer’s Reference
2007
Print ISBN: 978-0-470-04643-2; also
available in electronic formats
Billo, E.
Excel for Chemists
A Comprehensive Guide, Third
Edition (with CD-ROM)
3. Auflage
2011
Print ISBN: 978-0-470-38123-6; also
available in electronic formats
Billo, E.
Excel for Scientists and
Engineers
Numerical Methods
2007
Print ISBN: 978-0-471-38734-3; also
available in electronic formats
Mansfield, R.
Mastering VBA for
Microsoft Office 2013
2013
ISBN: 978-1-118-69512-8
Autoren
Prof. Dr. Shichang Wang
Hochschule Niederrhein
SWK-Energiezentrum E2
Thermische Verfahrenstechnik
Reinarzstraße 49
47805 Krefeld
Germany
Dipl.-Ing. Wolfgang Schmidt
Südstr. 39
46562 Voerde
Germany
1. Auflage 2015
Alle Bücher von Wiley-VCH werden sorgfältig erarbeitet. Dennoch übernehmen Autoren, Herausgeber und Verlag in keinem Fall, einschließlich des vorliegenden Werkes, für die Richtigkeit von Angaben, Hinweisen und Ratschlägen sowie für eventuelle Druckfehler irgendeine Haftung
Bibliografische Information der Deutschen Nationalbibliothek
Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über <http://dnb.d-nb.de> abrufbar.
© 2015 Wiley-VCH Verlag & Co. KGaA, Boschstr. 12, 69469 Weinheim, Germany
Alle Rechte, insbesondere die der Übersetzung in andere Sprachen, vorbehalten. Kein Teil dieses Buches darf ohne schriftliche Genehmigung des Verlages in irgendeiner Form – durch Photokopie, Mikroverfilmung oder irgendein anderes Verfahren – reproduziert oder in eine von Maschinen, insbesondere von Datenverarbeitungsmaschinen, verwendbare Sprache übertragen oder übersetzt werden. Die Wiedergabe von Warenbezeichnungen, Handelsnamen oder sonstigen Kennzeichen in diesem Buch berechtigt nicht zu der Annahme, dass diese von jedermann frei benutzt werden dürfen. Vielmehr kann es sich auch dann um eingetragene Warenzeichen oder sonstige gesetzlich geschützte Kennzeichen handeln, wenn sie nicht eigens als solche markiert sind.
Print ISBN: 978-3-527-33716-3
ePDF ISBN: 978-3-527-68024-5
ePub ISBN: 978-3-527-68023-8
Mobi ISBN: 978-3-527-68025-2
oBook ISBN: 978-3-527-68022-1
Für meinen Vater Zhixun Wang und meine Mutter Duolan Zheng aus dem Dorf Tengyuan, China – Shichang Wang
Für meine Kinder Annette und Christoph – Wolfgang Schmidt
Berechnungen in der Chemie und Verfahrenstechnik mit Excel-VBA
Es gehört zu den täglichen Aufgaben von Chemikern und Ingenieuren, Berechnungen durchzuführen. Für einige spezielle Anwendungen stehen kommerzielle Softwareangebote zur Verfügung, weniger jedoch für die vielen kleineren, täglichen Aufgabenstellungen. In diesem Buch werden Lösungen in Excel und VBA genau dafür vorgestellt.
Schwerpunkt sind numerische Methoden und deren Anwendungen in der Chemie und Verfahrenstechnik. Dazu gehören numerische Integration und Differenzierung, Lösung von Differentialgleichungen, Lösungen linearer und nicht-linearer Gleichungen durch Matrixberechnung. Beispiele sind u.a. Volumenberechnungen von Behältern, Berechnung zwischenmolekularer Potentiale, Phasengleichgewichte, chemische Reaktionen, instationäre Wärmeleitung, Maßeinheiten, Zustandsgleichungen, Optimierung. Alle Berechnungen werden in Excel in Kombination mit der Programmierung VBA realisiert. In einer Schritt für Schritt Vorgehensweise erlernt der Leser nicht nur die Möglichkeiten der Numerik, sondern auch das Programmieren kennen und schätzen. Vorkenntnisse sind nicht erforderlich. Sowohl beim Studium als auch in der Praxis ist dieses Buch sehr hilfreich.
Berechnungen in der Chemie oder Verfahrenstechnik sind heute wichtiger denn je. Durch Berechnung und Simulation kann man Experimente im Voraus testen und später begleiten. Die Literatur ist voll von Berechnungsmethoden, deren Nutzung aber ohne entsprechende Hilfsmittel meist am Aufwand scheitern. Excel in Kombination mit VBA bietet sich geradezu an, die allgemein als schwierig empfundenen numerischen Methoden kennenzulernen.
Jedes Thema wird optimal in sich geschlossen behandelt, so dass sich der Leser mit jedem beliebigen Thema beschäftigen kann, ohne ein vorheriges Themen gelesen haben zu müssen.
Sowohl eine vollständige Einführung als auch eine Beschreibung der Möglichkeiten der Programmiersprache VBA würde den Rahmen dieses Buches sprengen. Diesbezüglich wird auf die allgemeine Literatur verweisen. Es seien hier die Verlage Addison-Wesley, Hanser, Mikrosoft als auch M&T genannt, um nur einige zu nennen. Anregungen zu diesem Buch wurden insbesondere den Büchern von Ebert, Ederer: Computerberechnungen in der Chemie und Müller-Erlwein: Computeranwendungen in der chemischen Reaktionstechnik sowie einigen Lehrbüchern entnommen.
Excel wie seine historischen Vorgänger Lotus Symphony waren ursprünglich für kaufmännische Anwendungen erstellt worden, die ersten Computer übrigens auch. Während Borland einen eigenen C-Compiler und eine Tabellenkalkulation im Quellcode lieferte, brachte Microsoft das erste Excel auf den Markt. Damit ließen sich tabellarische Daten leicht horizontal und vertikal summieren, was zur Kontrolle von Materialbilanzen z.B. einer Chemieproduktion außerordentlich hilfreich war. Für häufig wiederkehrende Vorgänge wurde eine Makrosprache eingeführt, die aber wenig intuitiv war. Bill Gates mochte Basic persönlich sehr. Deshalb befand sich stets ein Basic Interpreter auf jeder DOS-Diskette eines PCs.
Im Gegensatz zum langsamen Interpreter konnte man mit den Basic Compilern von Microsoft, Power Basic oder Turbo Basic Basic-Programme deutlich beschleunigen. Im Vergleich zum damals in der Technik häufig verwendeten FORTRAN war die Programmsprache Basic erheblich einfacher.
Als B. Gates Visual Basic 4 herausbrachte, begann der zweite Siegeszug von Basic. Sehr bekannt ist die Version 6, der VB6 Compiler. Damit werden industriell gefertigte Programme erstellt, die denen in C++ erstellten in nichts nachstehen.
Basic als VBA (Visual Basic for Application) ersetzte die alte Makrosprache in Excel. Die Stärke von VBA in Excel besteht heute darin, dass die Oberfläche von Excel mit VBA kombiniert werden kann. Dabei ist besonders erwähnenswert, dass aus einer Ausführung in Excel unmittelbar ein VBA-Programm, das sog. Makro erstellt werden kann, das sog. Makroaufzeichnen. Das ist auch zum Erlernen von VBA sehr hilfreich. Der Befehlsumfang von VBA ist im Vergleich zum alten Quick Basic enorm gewachsen. In Kombination mit DLL ist man fast so stark wie der VB6 Compiler. Programme in VB6 laufen allerdings ohne Excel.
Zu diesem Buch gehört selbstverständlich eine Excel-Datei: „Excel-VBA-Chemie.xlsm“ in der Version Office 2007. Diese Excel-Datei ist nicht geschützt und frei verwendbar. In den einzelnen Kapiteln wird die jeweils dazu gehörende Excel Tabelle angegeben, so dass diese leicht gefunden werden kann.
Einige Themen und Basic-Berechnungen wurden den Büchern von Müller-Erlwein und Ebert, Ederer entnommen.
Sie lernen hier, wie man VBA in Excel aufruft und damit arbeitet. Sie erstellen eine Funktion und ein Makro und verwenden diese in Excel.
Tabelle: Polynom
Modul: Polynom
VBA: Polynomf
Wir wollen hier eine Polynom-Funktion sowohl in Excel als auch in VBA erstellen und dann bewerten.
Starten Sie Excel 2007 und tragen Sie in Zelle B3 und B4 jeweils die Zahl 1 ein.
Nun schreiben Sie in die Zelle C3 1 + 2*B3+3*B3*B3:
Dies wird ebenfalls in die Bearbeitungsleiste (fx = …) übertragen. Das Ergebnis steht unmittelbar zur Verfügung.
Nun wenden wir uns der Alternative zu, nämlich die gleiche Berechnung in VBA durchzuführen.
Zum Start von VBA in Excel drücken Sie Alt + F11 oder wählen „Entwicklungstools“, „VisualBasic“. Damit öffnet sich die Oberfläche von VBA.
Anm.: Wir gehen hier von einer leeren Excel-Mappe aus.
Falls die rechte Fläche grau ist, klicken Sie doppelt auf „Tabelle1 (Tabelle1)“. Die rechte Fläche wird nun weiß. Wählen Sie nun „Einfügen“ im Hauptmenü und „Modul“.
Excel hat das Modul links oben in das VBA-Projekt Fenster hinzugefügt:
Im Eigenschaftsfenster tragen Sie den Namen „Polynom“ ein.
Klicken Sie doppelt auf „Polynom“ im Projektfenster. Der Cursor blinkt im rechten großen Fenster. Wählen Sie nun „Einfügen“ und „Prozedur“:
Es erscheint ein neues Fenster.
Wählen Sie „Function“ und geben Sie den Namen der Funktion (Polynom) ein, OK.
Die erste und letzte Zeile wird automatisch erstellt. Nun können Sie Ihr Programm schreiben.
Die Zeile „PolynomFunktion“ ist ein Kommentar, weil sie mit einem Apostroph (Shift #) beginnt. Die Zeile
ist die eigentliche mathematische Funktion. Rechts davon steht ein Kommentar, der ebenfalls mit einem Apostroph beginnt.
Die Programmzeile
überträgt das Ergebnis in die Funktion selbst. Unter diesem Namen finden Sie diese Funktion in Excel automatisch. Das werden wir jetzt in Excel ausführen.
Auf dem PC sind in der unteren Leiste 2 Fenster von Excel offen, zwischen denn Sie hin und her springen können. Dies ist einerseits das Fenster der Excel-Tabelle, andererseits das VBA-Fenster. Wählen Sie nun das Excel-Fenster, das VBA Fenster wird aber nicht geschlossen. Geben Sie in Zelle B4 die Zahl 1 ein. Klicken Sie auf die Zelle C4. und auf „fx“ links von der Bearbeitungssleiste.
Es erscheint das Menü „Funktion suchen“. Wählen Sie „Benutzerdefiniert“ und darin „Polynomf“.
Anm.: die weiteren hier angezeigten Funktionen befinden sich (noch) nicht auf Ihrem PC.
Es erscheint:
Der Mauscursor sollt im Menü „Funktionsargumente“ neben dem X stehen und blinken. Falls nicht dort anklicken. Nun klicken Sie die Zelle B4 an und dann OK.
Das Ergebnis wird bereits im Menü angezeigt: Formelergebnis = 6.
In der Exceltabelle erscheint:
Sowohl die Excel-Formel in C3 als auch die VBA-Funktion in C4 ergeben dasselbe Ergebnis. Klicken Sie zur Kontrolle zwischen den Zellen C3 und C4 hin und her.
Wenn Sie nun in B3 und B4 eine andere Zahl eingeben, z.B. 10, erscheint:
Dies finden Sie in der Excel Datei „Excel-VBA-Chemie.xlsm“ in der Tabelle „Polynom“.
Im Vergleich beider Methoden ergibt sich folgendes. Eine Formel in Excel ist zunächst weniger aufwändig als diese in VBA zu erstellen. Ist die Formel allerdings komplexer und muss sie z.B. iterativ berechnet werden, ist VBA eindeutig im Vorteil. Für die Excel-Formel steht nämlich nur 1 Zeile zur Verfügung, während in VBA die Zahl der Zeilen unbegrenzt ist. Auch viele Möglichkeiten in Excel wie z.B. „Wenn“ und „Verweis“ lassen sich auch in VBA verwenden. So lautet z.B. der VBA Befehl für „Wenn“ schlicht „if“, der für „Verweis“ lautet „Lookup“. Dazu bietet die VBA Literatur viele Beispiele. Durch Makroaufzeichnen kann man dies leicht nachvollziehen.
Die hier beschriebene Funktion kann standardmäßig nur einen einzigen Wert übertragen. Mit der nachstehend beschriebenen Methode lässt sich das aber erweitern. Öffnen Sie dazu die Tabelle „Funktionen“.
Es wird zunächst nur ein Ergebnis angezeigt, nämlich der erste Wert einer Mehrfachvariablen, dem Array. Mathematisch betrachtet ist dies eine eindimensionale Matrix, oder auch Vektor genannt. Sie hat die Bezeichnung arr(x). Um nun tatsächlich alle 6 Daten dieses Arrays zu erhalten, müssen Sie vor Aufruf dieser Funktion die Zellen K25:K30 auswählen, d.h. markieren. Dann steht in Zelle K25 dasselbe Ergebnis wie zuvor. Drücken Sie nun die F2 Taste und dann die Dreierkombination Strg+Shift+Enter.
Dadurch erhält die Funktion geschweifte Klammern und in allen markierten Zellen finden Sie nun die 6 Ergebnisse. Das Löschen erfordert einige Schritte. Markieren Sie die zu löschenden Zellen und wählen mit der rechten Maustaste „Inhalte löschen“. Dabei werden alle Zellen komplett gelöscht.
Die jetzt übertragenen Daten sind eindimensional. Sie können aber auch mehrdimensionale Daten übertragen. Dazu verwenden Sie die Funktion „arrTest“. Verfahren Sie wie zuvor, markieren Sie aber die Zellen R24:S26.
Tabelle: Makro
Modul: Modul1
Makro: Makro1
Ein Makro ist ein selbständig laufendes Programm. Es gibt zwei Möglichkeiten, ein Makro zu erstellen. Entweder man programmiert es selber oder man nimmt es auf. Sie lernen in den nächsten Schritten wie Sie ein Makro aufnehmen können. Dazu öffnen Sie eine Tabelle und benennen diese „Makro“. Tragen Sie in die Zellen B3 = 2 und in die Zelle C3 = 3 ein. Nun wollen wir ein Programm (Makro) erstellen, welches diese beiden Zellen multipliziert und das Ergebnis in die Zelle D3 schreibt.
Wählen Sie im Hauptmenü „Entwicklungstools“. Klicken Sie die Zelle D3 an und wählen Sie „Makro aufzeichnen“.
In Zelle D3 geben Sie die Formel ein =B3*C3..
Ergebnis:
Mit der Enter Taste abschließen. Dann das Makro schließen, indem Sie auf „Aufzeichnen beenden“ klicken.
Nun löschen Sie D3. Starten Sie das Makro. Dazu wählen Sie „Makro“.
Klicken Sie nun auf „Ausführen“. In Zelle D3 erscheint jetzt das Ergebnis.
Das Makro schreibt also in die Zelle D3 genau das hinein, was Sie zuvor auch geschrieben hatten. Nun schauen wir uns das Makro etwas näher an. Dazu klicken Sie wieder auf „Makros“.
Jetzt klicken Sie auf „Bearbeiten“. Das VBA-Fenster wird automatisch geöffnet. Das Makro sieht möglicherweise etwas anders. Ändern Sie es wie folgt:
Excel generiert aus der Aufzeichnung einen VBA-Code und speichert diesen als ein Makro. Auf diese Weise lernt man den entsprechenden VBA Befehle kennen.
Die einzige wichtige Programmzeile lautet: ActiveCell.FormulaR1C1 = “=RC[-2]*RC[-1]” und bedeutet, daß in der selektierten Zelle eine Formel eingegeben wurde.
Die Schreibweise RC ist erklärungsbedürftig. R bedeutet Row (Reihe), C Column (Spalte). R1C1 ist die zuletzt markierte Zelle. RC(-2) bedeutet 2 Columns links, (-2) steht neben C, nicht neben R. C(-1) bedeutet daher 1 Zelle links von der ursprünglichen. Damit sind die Zelle relativ zur Ausgangsposition D3 gekennzeichnet. Welchen Vorteil hat das?
Schreiben Sie die Zahl 5 in die Zelle B2, 4 in die Zelle C6. Klicken Sie die Zelle D6 an, sodass sie markiert ist und und starten Sie das Makro erneut mit „Ausführen“. Die Berechnung wird ausgeführt. Das Ergebnis steht in Zelle D6.
Wenn Sie wollen, dass das Makro eine Berechnung nur der Zellen B4:D4 ausführt, statt eine Berechungsformel zu schreiben, dann sollten Sie es wie folgt ändern:
und den Makronamen in Makro2 ändern.
Füllen Sie die Zellen wie folgt aus und starten Sie das Makro2. Ergebnis:
Das Makro funktioniert jetzt nur mit den 3 Zellen B4, C4 und D4. Damit haben Sie zwei Makros, die unterschiedlich funktionieren.
Das Aufzeichnen von Makros ist eine sehr sinnvolle Einrichtung in Excel-VBA und erleichtert in vielen Fällen das Programmieren. Man lernt dabei oft VBA-Befehle schneller und einfacher kennen als durch Suchen in Fachbüchern.
Etwas umständlich ist das Starten eines Makros, so wie bisher durchgeführt. Bequem ist das Starten mit einem Start-Knopf, d.h. Command Button.
Wählen Sie „Entwicklungstools“, „Entwurfsmodus“. Links davon befindet sich „Einfügen“. Dort finden Sie die Symbole der Formularsteuerelemente und der ActiceX-Steuerelemente:
Im Bereich Formularsteuerelemente finden Sie oben links ein kleines Symbol. Halten Sie die Maus darauf und es erscheint „Schaltfläche (Formularsteuerelement)“. Klicken Sie dieses Symbol an und ziehen Sie das Symbol auf die gewünschte Größe:
In dem angezeigten Menü wählen Sie Makro1 und dann OK. Klicken Sie den Knopf mit der rechten Maustaste an und ändern Sie den Text in „Makro1“. Klicken Sie danach erneut auf „Entwurfsmodus“. Das Symbol erhält nun wieder seine normale Ansicht.
Wählen Sie die Zelle D3 und klicken Sie auf den Knopf „Makro1“. Dadurch wird das Makro1 automatisch ausgeführt. In D3 erscheint das Rechenergebnis.
Wählen Sie wieder den Entwurfsmodus. Wenn Sie jetzt den Knopf „Makro1“ mit der Rechten Maustaste anklicken, erscheint:
Wählen Sie „Makro2“. Ändern Sie den Namen des Knopfes auf „Makro2“, dann die Zahlen in B4 bzw. D4 und klicken Sie auf den Knopf „Makro2“.
In D4 erscheint das Ergebnis. Damit haben Sie die wichtigsten Möglichkeiten eines Makros kennengelernt.
Tabelle: Daten
Modul: Daten
Funktion: Beispiel1
Nachdem Sie im vorherigen Kapitel bereits gelernt haben, eine Funktion in VBA zu erstellen, lernen Sie hier an 4 Beispielen, wie Sie die Zellen in Excel mit den Variablen in VBA verbinden können. Diese Technik wird sowohl bei der Dateneingabe als auch bei der Datenausgabe benötigt. Damit wiederholen wir einen Teil des vorherigen Kapitels. Zunächst behandeln wir die Datenübertrageung selbst an Hand von 3 Beispielen, anschließend eine Datengenerierung zur grafische Darstellung eines Kreises.
Im Beispiel 1 erstellen wir die folgende mathematische Polynomfunktion:
Die nachfolgenden Schritte wurden grundsätzlich z.T. bereits im vorangegangenen Kapitel besprochen. Deren Anwendung wird hier an dem vorliegenden Beispiel vertieft.
Öffnen Sie mit Alt F11 das VBA Fenster und erweiteren Sie „Module“ um „Daten“. Klicken Sie dazu im Projekt-Fenster links auf „Module“ und dann auf „Einfügen“ im Hauptmenü. Wählen Sie dort „Modul“. Im Eigenschaftenfenster links unter dem Projektfenster ändern Sie den Modulnamen in „Daten“.
Jetzt befindet sich das Modul „Daten in der Modulliste:
Klicken Sie doppelt auf „Daten“, dann doppelt auf „Einfügen“ im Hauptmenü und wählen Sie „Prozedur“ und dann „Function“. Geben Sie als Namen „Beispiel1“ ein. VBA trägt nun automatisch das Grundgerüst ein:
Das füllen Sie nun wie folgt aus. Zunächst tragen Sie in die Funktion die folgenden 3 Variablen in die Klammern ein:
Damit ist die Funktion in der Lage, diese 4 Variablen einzulesen. Nun schreiben Sie die Funktion in die leere Zeile darunter:
Statt x2 müsste man schreiben x^2. Schneller rechnet VBA aber, wenn man x*x schreibt. Nun muß die Variable noch mit der Funktion verbunden werden. Schreiben Sie daher: Beispiel1 = y.
Wenn Sie einen Kommentar einfügen wollen, schreiben Sie diesen in Apostroph. Die Variablen lassen sich auch in große Buchstaben ändern.
Damit ist die Funktion fertig. Nun wechseln Sie in die Excel Tabelle „Daten“ und geben folgendes ein:
Klicken Sie in die Zelle B9 und dann auf das Funktionszeichen fx
Wählen Sie „Benutzerdefiniert“
und dann „Beispiel1“:
Es öffnet das Fenster „Funktionsargumente“.
Klicken Sie in dieses Fenster in die obere Zeile „A“ und dann in Excel auf B5. Dann klicken Sie in die Zeile „B“ und auf B6, in die Zeile „C“ und auf B7 und schließlich in die Zeile „X“ und in die Zelle B8.
Ergebnis:
In dem Menü „Funktionsargumente“ finden Sie neben den Zeilen „A“ bis „X“ die eingegebenen Werte.
sowie unten das Ergebnis „Formelergebnis = 60,06“. Wenn Sie nun OK klicken wird das Menü geschlossen und es erscheint das Ergebnis in der Zelle B9.
In der Bearbeitungsleiste finden Sie:
Es ist zwar sehr komfortabel, die Zelle B9 wie oben beschrieben mit Hilfe des Menüs „Funktionsargumente“ auszufüllen, alternativ kann man auch die Zelle B9 anklicken und die Bearbeitungsleiste direkt Feld direkt ausfüllen. Korrekturen des Berechungsalgorithmus sind kein Problem, wohl aber Änderungen der Variablen. In dem Fall muß die Funktion neu gestartet und die Änderungen aktiviert werden.
Nun wollen wir uns den Datentransfer von den Excelzellen in die VBA-Variablen näher ansehen. Dazu wechseln wir wieder in das VBA Fenster (Alt F11). Klicken Sie die erste Funktionszeile an und drücken Sie F9.
Wechseln Sie jetzt zurück in die Tabelle „Daten“ und klicken Sie auf die Zelle B9, dann auf das Funktionszeichen und es erscheint:
Wenn Sie jetzt OK drücken, springt Excel automatisch in VBA.
Die erste Zeile ist jetzt gelb markiert. Das bedeutet, dass diese Zeile nun ausgeführt wird.
Drücken Sie nun die F8 Taste.
und halten Sie den Mauszeiger in der ersten Zeile auf A. Es erscheint A = 1,5. Wiederholen Sie das mit den anderen Variablen B, C und X. Sie finden dieselben Daten wie in der Excel Tabelle „Daten“. Drücken Sie nun F8. Der gelbe Balken wandert ein Zeile tiefer.
Halten Sie nun den Mauszeiger auf y in der vorherigen Zeile. Es wird y = 60,06 angezeigt. Sie können auch hier den Mauszeiger auf jede Variable der Formel halten, um dessen Inhalt anzuzeigen. Wenn Sie noch einmal F8 drücken, erscheint
Nun wird das Ergebnis von y in die Funktionsvariable „Beispiel1“ übertragen. Wenn Sie den Mauszeiger in der Zeile davor auf y oder Beispiel1 halten, wird wieder 60,06 angezeigt.
Drücken Sie erneut F8 und Sie werden beobachten, dass das Ergebnis in die Zelle B6 geschrieben wird. Um einen Wert innerhalb der Funktion anzuzeigen, können Sie auch wie folgt verfahren:
Wiederholen Sie die Prozedur bis zu diesem Bild:
Dann wählen Sie im Hauptmenü „Debuggen“ und „ Aktuellen Wert anzeigen“. Dort befindet sich ein Brillensymbol.
Es erscheint:
Hier sehen Sie, das x = 3,2 ist. Wenn Sie jetzt „Hinzufügen“ wählen, erscheint unten eine neue Zeile.
Dies ist eine Überwachungszeile. Immer wenn Sie X in Zelle B8 ändern, wird dies hier angezeigt.
Nun heben wir die Markierung mit F9 auf. Klicken Sie die 1. Programmzeile an und drücken Sie F9. Falls Sie das Programm abbrechen müssen, wählen Sie den kleinen rechteckigen Knopf „Zurücksetzen“ im VBA Fenster .
Sie können soviele Überwachungsausdrücke einfügen wie Sie wollen. Wenn Sie im Überwachungsfenster mit der rechten Maustaste auf das Brillensymbol klicken, erscheint:
Wenn Sie auf „Überwachen entfernen“ klicken wird die Überwachunsgzeile entfernt. Im nächsten Beispiel wollen wir die Datenübetragung in einem Makro betrachten.
Nun behandeln wir das Beispiel2. In der Tabelle „Daten“ finden Sie:
Im VBA Fenster öffnen Sie nun das Makro „Beispiel1“. Dazu wechseln Sie in das VBA Fenster mit Alt F11 und wählen das Modul „Daten“.
Dort finden Sie den VBA Code für Beispiel2. Es handelt sich nicht um eine Funktion, sondern um ein Makro. Dieses wird wie folgt erstellt:
VBA erstellt nun automatisch den Rahmen zum Makro. Fertig sieht das wie folgt aus:
Das Makro erkennt man an der 1. Zeile, dort steht Sub statt Function. Ein Makro funktioniuert nicht wie eine Funktion. Ein Makro arbeitet eigenständig wie ein Programm und wird nicht einer Zelle zugeordnet. Die Datenübertargeung funktioniert daher anders als bei eienr Funktion. Ein Makro, dessen Klammern leer sind, kann in Excel über „Entwicklertools“ „Beispiel2“ und „Ausführen“ gestartet werden. Wenn in der Klammer jedoch Variablen stehen, wie das bei der Funktion übkliche ist, kann dieses Makro nicht eigenständig gestartet werden, sondern muß von einem übergeordneten Makro gestartet, welches über die Variablen Daten übergibt werden.
In der Zeile
A = Cells (23, 2) :B = Cells (24, 2) : C = Cells (25, 2) : X = Cells (17, 2)
werden Mit den Klammern […] kennzeichen Sie die Zelle in der aktuellen Tabelle. Diese aktuelle Tabelle muß die Tabelle „Daten“ sein, d.h. die muß angewählt sein. Wenn Sie jetzt das Makro „Beispiel2“ erneut starten, funktioniert es.
Wir lernen eine weitere Eigenschaft in „Debuggen“ kennen. Klicken Sie irgend eine Zeile des Makros „Beispiel2“ an und wählen Sie dann „Debuggen“.
Die erste Programmzeile wird automatisch gelb.
Mit der Taste F8 geht das auch. Drücken Sie nun die Taste F8 und Sie sehen, dass die gelbe Zeile um eins tiefer wandert. Auch hier könenn Sie die Werte der Variablen durch einfaches Berühren mit dem Mauszeiger oder dem Debug Befehl „Aktuellen Wert anzeigen“ sehen.
Im Beispiel 3 lernen Sie eine andere, flexible Art des Datentransfers kennen. Dazu schreiben Sie zunächst in Excel.
Nun richten Sie sich wieder ein Makro „Beispiel3“ ein, wie oben beschrieben. Darin kopieren Sie zunächst den Inhalt von „Beispiel2“. Das sieht dann so aus:
Ändern Sie dies wie folgt:
Es ist leicht zu erkennen, dass aus [B14] cells(14,2) wird. Dabei steht die 14 für die Zeile (Row) und die 2 für die Soalte (Column). Sobald Sie Cells geschrieben haben, erscheint:
Das bedeutet, das in der Klammer zuerst die Reihe, dann die Spalte geschrieben wird. Damit wäre Beispiel3 identisch mit Beispiel2. Jetzt wollen wir die Adressen aber so setzen, wie es der Darstellung in Excel entspricht.
Starten Sie dieses Makro von VBA aus, indem Sie irgendwo in Beispiel3 reinklicken und F8 drücken. Wieder wird die 1. Zeile gelb markiert.
Drücken Sie nun mehrfach F8, bis die Gelbmarkierung verschwunden ist. Zwischendurch prüfen Sie die Variablen wie oben gezeigt. Wenn alles ok ist, müßte in Zelle B27 die Zahl 5373,12 stehen. Natürlich können Sie dieses Makro auch über den Makro Befehl aus dem Hauptmenü unter „Entwicklertools“ starten.
In nächsten Beispiel4 wollen wir die indizierte bzw. tabellarische Datenübertragung kennenlernen. Dazu legen wir keine neuen Daten in Excel an, sondern verwenden die von Beispiel3. Durch Kopieren erstellen wir uns wie zuvor erst einmal das Grundgerüst in VBA.
Markieren Sie die beiden ersten Programmzeilen mit einem Apostroph (Shift #), sodass daraus ein Text wird und schreiben Sie die neuen Programmzeilen darunter, so dass es wie folgt ausieht:
Starten Sie dieses Makro wie gewohnt in VBA mit F8 und testen Sie die Variable k(i). Sie werden die Daten der Zellen B23 bis B26 sehen. Neu ist der Befehl Dim k(10). Das bedeutet, dass eine indizierte Variable deklariert wird, die maximal 10 Indice haben kann. Hier werden nur 4 benötigt.
Neu ist auch der Befehl
for i = 1 to 4
Das bedeutet, daß die Variable i der Reihe nach die Zahlenwerte 1,2,3, 4 annimmt und jedesmal danach der Befehl
k(i) = Cells(22+i,2) ausgeführt wird. Dabei wird dasselbe erreicht wie zuvor mit a = Cells(23,2). Nur wird die Zahl 23 aus 22+1 bzw. 22+2 usw. ersetzt und a durch k(1) und b durch k(2) usw. Der Vorteil besteht darin, dass nur eine Zeile zum Datenlesen erforderlich ist.
Mit „next i“ wird diese Schleife erneut ausgeführt bis i = 4 ist. Dann erfolgt die eigentliche Formelberechnung.
Insgesamt erscheint diese Art Programm etwas umständlich und weniger leicht lesbar als Beispiel3. Das stimmt, jedoch ist sie bei größerer Datenmenge im Vorteil. Man denke nur daran, dass eine Tabelle aus 30 Zeilen und 20 Spalten in Variable gelesen werden muss. Allein dafür hätte man nicht genug Buchstaben zur Verfügung.
Es ist hier besonders empfehlenswert, die Programmschritte mit F8 zu prüfen.
Tabelle: Daten
Modul: Daten
Makro: Kreisgrafik
In diesem einfachen Makro wollen wir die Koordinaten eines Kreises berechnen, diese in die Tabelle „Daten“ schreiben und grafisch als Kreis darstellen.
Erstellen Sie ein Makro mit dem Namen „Kreisgrafik“. Das fertige Programm sieht wie folgt aus:
Mit der Variablen z berechen wir die Zeile, in die die Tabellendaten geschrieben werden. Nach jeder Berechnung wird der Wert von z um 1 erhöht und damit die Zeile um eins tiefer gesetzt. Damit ist die Zeilenzahl von i unabhängig.
Die Programmzeile
ist in dieser Form neu. Der Winkel i im Bogenmass beginnt mit 0, schreitet aber dann nicht in den Schritten 1, 2, 3 usw. fort, sondern in Teilen des Kreises und der ist insgesamt 2π. Der Kreis soll in 100 Teilen berechnet werden. Daher muß die Schrittweite 2π/100 sein. Das erfüllt der Befehl Step, was Schrittweite bedeutet.
Das Ergebnis in Excel mit der Wahl des Hauptgitters sieht wie folgt aus.
Hier sehen Sie den oberen, positiven Teil der 100 zeiligen Tabelle:
Gerade für den VBA-Anfänger ist es wichtig, an fertigen Programmen VBA zu lernen, indem das Programm Zeile für Zeile nachvollzogen und selbst geschrieben wird. Dabei wird man Fehler machen, die man suchen muß. Erst wenn man gelernt hat, eigene Fehler zu finden, wird man in der Lage sein, eigene Programme zu erstellen.
Tabelle: Einheiten
Modul: Einheiten
Makro: EinheitenMenu
Formulare: UserForm1
In dem Programm „Einheiten“ lernen Sie wie Sie ein VBA Programm mit einer eigenen Oberfläche versehen können. Die Aufgabenstellung besteht darin, ein Programm zu erstellen, mit dessen Hilfe eine beliebige Energieeinheit in eine andere beliebige Energieeinheit umgerechnet werden soll. Dabei soll die Bedienung optimal intuitiv sein.
Klicken Sie auf einen der beiden Menüknöpfe
Es erscheint die Benutzeroberfläche (UserForm):
Geben Sie in das Datenfenster „Eingabe“ den Wert 10 ein und rechts daneben wählen Sie die Maßeinheit J. Wählen Sie jetzt im darunter liegenden Fenster die Maßeinheit kcal.
Im Datenfenster „Ausgabe“ erscheint automatisch das Ergebnis 0,002388. Gleichzeitig werden alle Daten in A3:H4 angezeigt. D.h. 10 J sind in 0,002388 kcal umgerechnet worden.
Wir betrachten nun die UserForm. Wählen Sie das VBA Fenster mit Alt F11.
Klicken Sie links im Projektfenster auf „UserForm“.
Es erscheint die UserForm sowie eine Werkzeugsammlung.
Wenn Sie in der Werkzeugsammlung die Maus auf „A“ bewegen, erscheint der Text „Bezeichnungsfeld“. Klicken Sie A an und klicken Sie dann auf die UserForm. Damit übertragen Sie das Bezeichnungsfeld in die UserForm . Im Eigenschaftenfenster der VBA Oberfläche links finden Sie den Namen Label.
Ebenso verfahren Sie mit „ab“, dem Textfeld. Dieses wird im Eigenschaftenfenster „TextBox“ genannt. In der zweiten Reihe finden Sie xyz mit einem Rahmen, im Eigenschaftenfenster „Frame“ genannt. Das kleine Symbol in der ersten Reihe rechts hat den Namen „Listenfeld“. Im Eigenschaftenfenster wird es „Listbox“ genannt“.
Dass es jeweils zwei Begriffe für die Elemente der Werkzeugsammlung gibt, liegt daran, dass die Namen in der Werkzeugsammlung aus der Übersetzung aus dem englischen Original stammt, während die Namen im VBA Eiegnschaftenfenster im Englischen belassen wurden, wie alle VBA Begriffe.
Nun zur UserForm selbst.
Im Rahmen „Eingabe“ setzen Sie links das Datenfeld (Textbox1), rechts das Listenfeld ( Listbox1). Für den Rahmen „Ausgabe“ gilt das Gleiche, d.h. dort befinden sich Textbox2 und Listbox2. Die Größe der UserForm können Sie mit der Maus durch Zeihen eines Randes verändern.
Klicken Sie in das Datenfenster „Eingabe“.
Sie sehen den Rahmen und das Datenfeld markiert. Im Eigenschaftenfenster links finden Sie den Namen dieses Datenfeldes „TextBox1“.
Klicken Sie nun auf das Einheitenfenster rechts neben dem Eingabedatenfeld.
Im Eigenschaftenfenster finden Sie den Namen „ListBox1“.
Klicken Sie nun auf das Datenfeld „Ausgabe“.
Das Eigenschaftenfenster zeigt den Namen an.
Klicken Sie auf das Einheitenfenster rechts daneben.
Das Eigenschaftenfenster zeigt den Namen an.
Diese Namen können direkt in VBA verendet werden.
Das Einheitenfenster soll mit den Maßeinheiten gefüllt werden. Klicken Sie doppelt auf das Einheitenfenster der Eingabe.
Es erscheint der zugehörige VBA Code.
Dieses Programm wird ausgeführt, sobald bei der Anwendung eine der Zeilen angeklickt, d.h. ausgewählt wird. Wir besprechen das Programm weiter unten.
Im Eigenschaftenfenster finden Sie in der Zeile „RowSource“ „Einheiten!B23:C36“. Das bedeutet, dass die Listbox1 mit den Daten aus B23:C36 gefüllt wird.
Klicken Sie nun auf das Einheitenfenster im Rahmen „Ausgabe“.
Im Eigenschaftenfenster finden Sie in der Zeile „RowSource“ „Einheiten!B23:C36“. Das bedeutet, dass die Listbox2 ebenfalls mit den Daten aus B23:C36 gefüllt wird. Beide Einheitenfenster verwenden also dieselben Daten. Diese finden Sie in der Tabelle „Einheiten“ in den Zellen B23:C36.
Es werden die Spalten B und C gelesen, aber nur die Textdaten der Spalte B angezeigt. Wenn Sie wollen, dass auch die Daten der Spalte C, d.h. die Umrechnungsfaktor im Menü angezeigt werden, dann geben Sie im Eigenschaftenfenster in der Zeile „ColumnCount„ eine 2 ein.
Nun schauen wir uns die Datenfelder an. Klicken Sie doppelt auf das Datenfeld „Eingabe“. Der Cursor springt automatisch nach
In der ersten Zeile wird festgelegt, wann dieses Programm ausgeführt wird. In diesem Fall bedeutet „TextBox1_Change()“, dass jeweils bei einer Änderung das Programm ausgeführt wird. Dieses Datenfeld hat den Namen „Textbox1“, s.o. Dessen Inhalt wird in der zweiten Zeile in die Variable Eingabe1 übertragen und anschließend in die Zelle A4 geschrieben.
Die Textbox2 ist nicht für die Eingabe vorgesehen. Falls Sie dieses Datenfeld doppelt anklicken, werden Sie außer einem leeren Makro nichts finden. Das kann so bleiben.
Wie das Datenfeld funktioniert, finden Sie im Programm ListBox2_Click weiter unten.
Wir betrachten nun die VBA-Programme und beginnen dem Programm ListBox1_Click.
Die erste Zeile „Sub ListBox1_Click()“ besagt, dass das Programm ausgeführt wird, sobald eine Textzeile angeklickt wird.
Die 2. Zeile „Einheit1 = ListBox1.List(ListBox1.ListIndex, 0)“ überträgt aus der Listbox1, die angeklickte Zeile, gekennzeichnet durch die Nr. 0 und überträgt den Inhalt in die Variable Einheit1. Die Zeile „[B4] = Einheit1“ schreibt den Inhalt in die Zelle B4. In der nächsten Zeile „Faktor1 = ListBox1.List(ListBox1.ListIndex, 1)“ wird die 2. Spalte der angeklickten Zeile in die Variable „Faktor1“ übertragen. Durch die nächste Zeile „[C4] = Faktor1“ wird der Wert in die Zelle C4 geschrieben. Danach findet eine einfache Berechnung statt.
Zunächst wird in der Zeile „Eingabe = TextBox1“ der Inhalt des Datenfeldes „Eingabe = TextBox1 in die Variable „Eingabe“ übertragen. In der nächsten Zeile „EingabeJ = Eingabe / Faktor1“ wird der Wert der Eingabe durch den gewählten Faktor dividiert, normiert. Dadurch erhalten wir den Eingabewert in der Einheit [J]. Daher wird er EingabeJ genannt und in Zelle D4 ausgegeben.
Wir wenden uns nun der Ausgabe zu. Klicken Sie doppelt auf das Einheitenfenster (ListBox2) im Rahmen „Ausgabe“. Der Cursor blinkt in folgendem Programm:
Die erste Zeile „Sub ListBox2_Click()“ besagt, dass das Programm ausgeführt wird, sobald man eine Zeile in der Listbox2 anklickt. Wir nehmen folgende Auswahl an: