VBA-Sub

Allzweck-Programme

Sie können mit VBA Excel-Hilfsprogramme erstellen. Innerhalb von Excel haben sie Zugriff auf alle Objekte, außerhalb zumindest auf das Dateisystem.

Diese Programme werden in VBA mit "Sub" bezeichnet und in Excel als "Makro".

VBA	Visual basic for Applications
Test-Sub	Ein eigenes Macro-(Sub)-Programm erstellen und anwenden
Excel-Buttons	Programm auf Knopfdruck
Excel-Menü	Eigene Menüs und Menübefehle
Parameter	Call, benannte Bereiche

Ein eigenes Macro-(Sub)-Programm erstellen und anwenden
Erste Schritte zu einem "Benutzerdefinierten" Makro-Programm
Wechseln sie aus Excel in das VBA-Fenster Erstellen sie ein VBA-Modul oder öffnen sie ein solches Programmieren sie ein eigenes Sub-Programm nach dem Muster rechts Testen sie nach der Eingabe die Syntax mit Menübefehl Debuggen \| Kompilieren von VBAProject. Ihr Quellcode sollte keine Fehlermeldung ergeben.	Sub inc_A1() v = Range("A1").Value Range("A1").Value = v + 1 End Sub
Wechseln sie in das Excel-Fenster Geben sie einen beliebigen Zahlenwert in Zelle A1 ein. Menübefehl Extras \| Makro \| Makros Das Makro-Fenster öffnet sich und zeigt eine Liste aller verwendbaren Programme. Darunter sollte sich ihr Programm "inc_A1" befinden.	Wählen sie aus der Liste inc_A1 und klicken sie "Ausführen" Der Zahlenwert in Zelle A1 wird um 1 erhöht. Wiederholen sie das Programm: Jedesmal wird der Wert erhöht.
Sie haben nun: eine eigenes VBA-Programm erzeugt ihr Programm angewendet	Ein Programm wird zwar mit den gleichen Mitteln erstellt wie eine Function, arbeitet jedoch anders: Gibt keinen Wert zurück, kann daher nicht in den Zellen (z.B. als Funktion) des Kalkulationsblattes verwendet werden.
Macro: Dieser Begriff ist in der Informatik seit langer Zeit eindeutig belegt und wird von M$ falsch verwendet. Macro bezeichnet einen benannten Text. Bei jeder Verwendung des Namens im Programm wird dieser durch den Text ersetzt. Das ist bei VBA nicht der Fall, daher wird in diesem Web weiterhin das Wort "Programm" an Stelle von "Macro" verwendet.

Excel-Buttons
Die Bedienung von VBA-Programmen über das Menü "Extras" ist mühsam. Besser gehts mit Buttons:
Wechseln sie zum Excel-Fenster. Stellen sie sicher, dass ihre Kalkulation mindestens 2 Blätter enthält (unterer Rand, Standard ist "Tabelle1", "Tabelle2", ...) und wechseln sie zum ersten Blatt. Wechseln sie zum VBA-Fenster und erstellen sie die beiden nebenstehenden Programme. Falls die Blätter ihrer Kalkulation andere Namen tragen, setzen sie diese ein.	Sub goto_tabelle1() Sheets("Tabelle1").Select End Sub Sub goto_tabelle2() Sheets("Tabelle2").Select End Sub
Wechseln sie zum Excel-Fenster. Menübefehl Ansicht \| Symbolleisten \| Formular Diese Symbolleiste enthält ein Button-Werkzeug (rechts) Klicken sie das Symbol und ziehen sie damit einen Excel-Button auf dem Arbeitsblatt. Das Fenster "Makro zuweisen" öffnet sich automatisch. Wählen sie aus der Liste das Programm "goto_tabelle2". Ab sofort ist ihr Button in Funktion: ein Mausklick sollte sie zum Blatt "Tabelle2" bringen.	Zuletzt wird der Button formatiert: Mit Rechtsklick steht ihnen eine Reihe von Möglichkeiten zur Verfügung: + Ändern sie den Text (Text bearbeiten) sinnvoll, z.B. auf "Gehe zu Tabelle 2" + Ändern sie Position und Größe: Nach Auswahl durch Rechtsklick nochmals in den schraffierten Rand des Buttons klicken: nun lässt sich der Button ziehen und ändern. + Sie können auch Schrift, Farbe usw. ändern: Nach dem Rechtsklick "Steuerelement formatieren".
Kopieren sie den fertigen Button auf ihr Arbeitsblatt "Tabelle2" Ändern sie den Button auf Tabelle2 für einen Spring zum Blatt "Tabelle1": nach Rechtsklick ein anderes Makro zuweisen, sowie die Beschriftung sinngemäß ändern.	Nun können sie zwischen ihren Blättern "Tabelle1" und "Tabelle2" mit Hilfe von Buttons navigieren.
Autoformen als Buttons: Auch andere Objekte können als Buttons verwendet werden. Menübefehl Ansicht \| Symbolleisten \| Zeichnen Hier finden sie "Autoformen" wie Rechteck, Ellipse, Pfeile, Linien usw, die sie einzeln oder zusammengesetzt verwenden können.	Diese Objekte zählen zur VBA-Klasse "shapes", ebenso wie die oben erwähnten Buttons. Rechtsklicken sie ein Objekt: Mit dem Kontextbefehl "Makro zuweisen" können sie jedes Objekt zum Klickbutton machen, der ein beliebiges Programm auslöst.
Unsichtbare Buttons: Mit Autoformen können sie auch unsichtbare (transparente) Buttons herstellen. Das macht Sinn, wenn der Hintergrund als Hinweis auf den Button dient. Schreiben sie in eine Zelle ihres Blattes "Tabelle1" den Text "Tabelle2" Verwenden sie Menübefehl Format \| Zellen, um diese Zelle auffällig zu formatieren: große, fette Schrift, gelber Hinergrund, dicker roter Rand, ... Menübefehl Ansicht \| Symbolleisten \| Zeichnen Wählen sie das Rechteck-Werkzeug und ziehen sie ein Rechteck um die gelbe Button-Zelle. Rechtsklicken sie das Rechteck und weisen sie ihm das Programm goto_tabelle2 zu.	Rechtsklicken sie das Rechteck zum "Autoform formatieren" Stellen sie Eigenschaften = Von Zellposition und -Größe abhängig ein. Damit wird ihr Button zusammen mit seiner Hintergrund-Zelle vergrößert, verkleinert oder verschoben. Rechtsklicken sie das Rechteck zum "Autoform formatieren" und stellen sie Farben und Linien ein: Linie=Keine Linie und Farbe = Keine Füllung Der Transparent-Button ist fertig und funktionsfähig. Durch Excel-Programmierung können sie den Text der Button-Zelle ändern: Der Button ändert dann seine Bezeichnung je nach ihrem Programm.
Bilder als Buttons: Sie können auch Bilder als Buttons verwenden. Solche "ClipArt" können sie in großer Zahl aus dem Internet erhalten. Speichern sie die Grafiken auf ihrer Festplatte. Excel-Menübefehl Einfügen \| Grafik \| Aus Datei Das Datei-Suchfenster ihres Betreibssystems erscheint. Wählen sie eine Grafik aus.	Die Grafik wird (im Gegensatz zu HTML) komplett in ihre Anwendung kopiert. Sie bleibt dort auch erhalten, wenn die Originaldatei umbenannt oder gelöscht wurde. Rechtsklicken sie die Grafik: Mit dem Kontextbefehl "Makro zuweisen" können sie jede Grafik zum Klickbutton machen, und ein beliebiges Programm damit auslösen.

Excel-Menüs und -Menübefehle
VBA-Programme können sie nicht nur mit Buttons starten sondern auch mit eigenen Menübefehlen.
Eine besonders einfache und professionelle Methode, eigene Programme in Excel einzubauen, sind eigene Menüs: Menübefehl Ansicht \| Symbolleisten \| Anpassen \| Befehle \| Kategorien \| Neues Menü:. Ziehen sie den Befehl "Neues Menü" aus der rechten Seite des Fensters an jene Stelle des Menübalkens, wo das neue Menü stehen soll. Mit der rechten Maustaste können sie den Titel (hier: myMenü) ändern.	Nun können sie in dieses Menü (oder in andere) eigene Befehle (=VBA-Programme) einsetzen: Menübefehl Ansicht \| Symbolleisten \| Anpassen \| Befehle \| Makros: Ziehen sie aus diesem oder anderen Menüs Symbole aus der rechten Seite des Fensters an die gewünschte Stelle des neuen Menüs. Klick mit der rechten Maustaste auf einen Menüpunkt zeigt dessen Eigenschaften. Ändern sie "Makro zuweisen" nach Wunsch.
Hinweis: Wenn sie die Eigenschaften von Menüs oder Menübefehlen ändern wollen, dann müssen sie stets das Fenster "Symbolleisten anpassen" öffnen ! Nur dann sind auch die Kontext-Menüs der Menüs und Menübefehle auf Rechtsklick zugänglich.	Automatische Ausführung: VBA-Programme können bei bestimmten Ereignissen automatisch ausgeführt werden. Mehr Information dazu im Kapitel über Ereignisse.
Tastenkombination: Besonders bequem ist die Ausführung von Programmen mit einer bestimmten Tasten-Kombination: Das ist allerdings nur für Routine-AnwenderInnen interessant:	Menübefehl Extras \| Makro \| Makros. Programm aus der Liste auswählen, Taste "Optionen" klicken: Tastenkombination eingeben.

Argumente (Parameter)
Sub-Programme können ebenso wie Functions beliebig viele Argumente (Parameter) vom aufrufenden (Parent)-Programm erhalten. Das gilt jedoch nur für VBA: Excel kann an ein Sub keine Argumente übergeben !
Bei Aufruf aus Excel können sie (im Gegensatz zu Functions) keine Argumente zur Steuerung von Sub-Programmen übergeben. Die Weitergabe von Daten erfolgt meistens mit Hilfe von Excel-Zellen: VBA liest den Inhalt bestimmter Zellen und verwendet die Daten als Argumente. Ein Problem dabei ist, dass Zellen häufig verschoben werden. + Für Excel stellt das kein Problem dar: Falls verschobene Zellen mit anderen Excel-Zellen verknüpft sind, dann werden diese Bezüge beim Verschieben von Excel automatisch verändert. - VBA kann das nicht, ein Bezug auf eine verschobene Zelle geht daher 'ins Leere' oder - noch schlimmer - verwendet falsche Daten aus anderen Zellen. Die Lösung: Definition eindeutiger Namen für bestimmte Zellen. Wenn sie Zellen mit Namen versehen, dann bleiben diese auch dann gültig und richtig, wenn die Zellen verschoben werden: Solche Daten werden sowohl von Excel als auch von VBA gefunden.	Beispiel: Erstellen sie im Excel-Fenster 4 Zellen nach diesem Muster: Die Zellen B1 und B2 sollen dazu dienen, die Parameter x und y an das VBA-Programm zu übergeben. Definieren sie nun die Namen: Markieren sie Zelle B1, Menübefehl Einfügen \| Namen \| Definieren. Verwenden sie Name=x für Bezieht sich auf==Tabelle1!$B$1 Kontrolle: Wenn sie in Zelle B1 klicken, sehen sie links oben den Namen der Zelle (x) - bei jeder anderen Zelle wird hier die Adresse angezeigt (B1). Vergeben sie analog den Namen "y" für Zelle B2 Erstellen sie einen Button mit der Aufschrift "Test" und weisen sie ihm vorläufig noch keinen Makro-Befehl zu.
Wechseln sie ins VBA-Fenster und erstellen sie das rechts angezeigte Testprogramm. Betrachten sie zunächst das öffentliche Programm shape_make: Es liest die Excel-Zellen (Range) mit den Namen 'x' und 'y' und weist sie gleichnamigen Variablen zu: So werden Parameter aus Excel-Zellen an VBA-Programme übergeben. Anschließend wird das private Programm shape_delete aufgerufen. Es dient dazu, eine vorhandene Grafik mit dem Namen "Test" zu löschen. Wichtig: Sub-Programme können Parameter ebenso wie Functions übernehmen, jedoch nur bei Aufruf (Call) durch andere VBA-Programme. Nachdem die (alte) Grafik gelöscht wurde, wird mit AddShape ein Rechteck erzeugt. Die Position wird aus den Excel-Zellen 'x' und 'y' gelesen, die Größe (hier: 100x100 Pixel) ist in VBA eingestellt. Wechseln sie ins Excel-Fenster und weisen sie ihrem Button 'Test' das Programm shape_make zur Ausführung zu. Testen sie das Programm mit unterschiedlichen Werten der Parameter x und y, die sie in Excel ändern können. Einen Zufallstest erhalten sie, wenn sie in die Zellen B1 und B2 jeweils diese Formeln eingeben: =Zufallszahl()*250	Private Sub shape_delete(shapename As String) Dim sh As Shape For Each sh In ActiveSheet.Shapes If sh.Name = shapename Then ActiveSheet.Shapes(shapename).Select Selection.Cut End If Next End Sub Sub shape_make() Dim x, y, dx, dy As Integer Dim rechteck As Object Calculate x = Range("x").Value y = Range("y").Value dx = 100 dy = 100 Call shape_delete("Test") Set rechteck = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, dx, dy) With rechteck .Name = "Test" .Placement = xlFreeFloating .Fill.ForeColor.SchemeColor = 42 .Line.Visible = msoTrue End With End Sub

Zum Inhaltsverzeichnis

> Entwicklung > VBA