VBA: Zielwertsuche (Goal Seek)

Die Zielwertsuche ist ein sehr nützliches Instrument in Excel, um die Argumente einer Formel so zu gestalten, dass am Ende ein gewünschtes Ergebnis erreicht wird.

In einem früheren Beitrag habe ich Dir die Zielwertsuche in Excel bereits vorgestellt. In diesem Beitrag geht es nun darum, die Zielwertsuche mit einem VBA-Makro umzusetzen. In VBA heißt die Zielwertsuche „Goal Seek“.

Weiterlesen „VBA: Zielwertsuche (Goal Seek)“

VBA-Methoden: Arbeitsblätter verschieben und umbenennen

Am Ende des letzten Beitrags enthielt die Arbeitsmappe mehrere Arbeitsblätter, die allerdings nicht in einer gewünschten /  logischen Reihenfolge angeordnet waren.

Eine gewünschte Reihenfolge kannst Du nun herstellen, indem Du die Arbeitsblätter verschiebst.

1. Arbeitsblätter verschieben

Fürs Verschieben verwendest Du in VBA die Methode „Move“ und die Argumente „Before“ und „After“.

Die Endmappe des letzten Beitrags enthielt Arbeitsblätter in dieser Reihenfolge:

Tabelle2, Tabelle1, Tabelle3, Tabelle5, Tabelle4

Mit folgendem Code bringst Du mit einem Makro alle Arbeitsblätter in die richtige Reihenfolge:

Sub ArbBlattVerschieben()

Worksheets(„Tabelle1“).Move Before:=Worksheets(„Tabelle2“)

Worksheets(„Tabelle5“).Move After:=Worksheets(„Tabelle4“)

End Sub

2. Arbeitsblätter umbenennen

Die Arbeitsblätter werden jetzt standardmäßig nur mit „Tabelle1“ bis „Tabelle5“ bezeichnet.

Du willst sie aber mit Monatsnamen benennen, Januar bis Mai.

Mit dem folgenden Code erhalten alle fünf Arbeitsblätter einen neuen Namen:

Sub ArbBlattUmbenennen()

Worksheets(„Tabelle1″).Name=“Januar“

Worksheets(„Tabelle2″).Name=“Februar“

Worksheets(„Tabelle3″).Name=“März“

Worksheets(„Tabelle4″).Name=“April“

Worksheets(„Tabelle5″).Name=“Mai“

End Sub

Die Arbeitsblätter haben jetzt alle den gewünschten Namen erhalten.

VBA-Methoden: Ein neues Arbeitsblatt erstellen und einfügen

Es kann innerhalb einer VBA-Prozedur erforderlich werden, in einer Arbeitsmappe ein weiteres Arbeitsblatt einzufügen und ggf. namentlich zu benennen.

Eine neues Arbeitsblatt zu erstellen, erfordert nur eine Codezeile.

Sub NeuesArbBlatt1()
Worksheets.Add
End Sub

Das ist schon alles. Vor dem aktuellen Arbeitsblatt wird das neue eingefügt.

Dir gefällt nicht, dass das neue Blatt an vorderster Stelle eingefügt wurde.

Mit dem Argument „After“ veranlasst Du, dass das neue Blatt nach einem vorhandenen eingefügt wird.

Sub NeuesArbBlatt2()
Worksheets.Add After:=Worksheets(„Tabelle1“)
End Sub

Um ein neues Arbeitsblatt vor einem vorhandenen Blatt einzufügen, verwendest Du das Argument „Before“.

Sub NeuesArbBlatt3()
Worksheets.Add Before:=Worksheets(„Tabelle3“)
End Sub

Hierzu erspare ich uns die Abbildung.

Du kannst ein neues Arbeitsblatt auch grundsätzlich an letzter Stelle einfügen.

Verwende dazu diesen Code:

Sub NeuesArbBlatt4()
Worksheets.Add After:=Worksheets(Worksheets.Count)
End Sub

Der Vollständigkeit halber, Du kannst auch gleich mehrere neue Arbeitsblätter einfügen. Dazu verwendest Du das Argument „Count“.

Sub NeuesArbBlatt5()
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
End Sub

Mit „Count:=2“ hast Du Dich für zwei weitere Blätter entschieden. Die Blätter „Tabelle5“ und „Tabelle4“ wurden hinter den vorhandenen neu eingefügt.

Du kannst den Blättern jetzt manuell andere Namen geben oder Du wartest auf den nächsten Beitrag zu den VBA-Methoden.

VBA: Das Steuerelement OptionButton

Der OptionButton (Optionsfeld) ist eine Steuerelement aus den ActiveX-Steuerelementen.

Verwende ein OptionButton, um anzuzeigen, ob ein einzelnes Element innerhalb einer Gruppe ausgewählt wurde.

Du willst beispielsweise mit ausgewählten Werten aus der folgenden Liste wahlweise in zwei Verfahren rechnen.

Einerseits sollen die Werte aus B26 und B28 subtrahiert werden, wenn ein OptionButton aktiv ist.

Andererseits sollen die Werte in B27 und B29 multipliziert werden, wenn ein zweiter OptionButton aktiv ist.

Füge zunächst zwei OptionButton in Dein Arbeitsblatt ein. Gehe dazu im Menü „Entwicklertools“ auf „Einfügen“ und wähle den OptionButton bei den ActiveX-Steuerelementen.

Aktiviere danach eine Zelle Deines Arbeitsblattes, um das Steuerelement einzufügen.

Um den Hintergrund farbig zu gestalten, schaltest Du unter den „Entwicklertools“ in den „Entwurfsmodus“ und rufst „Eigenschaften“ auf.

Wähle unter „BackColor“ eine Farbe aus.

Beide OptionButton stehen nun zur Verfügung.

Die Berechnungsergebnisse sollen rechts daneben, in den Zellen D2 bzw. D4 ausgegeben werden.

Eröffne das Makro mit z.B. Sub OptionRechnen() in einem Modul.

Deklariere nun zuerst die Variablen für Dein Makro.

Dim Subtraktion As Long

Dim Multiplikation As Long

Weise ihnen Werte zu.

Subtraktion = Range(„B7“).Value – Range(„B9“).Value

Multiplikation = Range(„B8“).Value * Range(„B10“).Value

Weise nun an, was geschehen soll, wenn der OptionButton1 aktiv ist. Das machst Du mit einer If-Anweisung. Die Anweisung lautet, die definierte Subtraktion auszuführen und gleichzeitig das Ergebnisfeld für OptionButton2 zu leeren.

If Worksheets(„Tabelle1“).OptionButton1.Value = True Then

Range(„D2“) = Subtraktion

Range(„D4“).ClearContents

Weise dann an, was geschehen soll, wenn der OptionButton2 aktiv ist. Hier lautet die Anweisung, die definierte Multiplikation auszuführen und das Ergebnisfeld für OptionButton1 zu leeren.

ElseIf Worksheets(„Tabelle1“).OptionButton2.Value = True Then

Range(„D4“) = Multiplikation

Range(„D2“).ClearContents

Beende das Makro mit End If und End Sub.

Komplett sieht das Makro so aus:

Sub OptionRechnen()

Dim Subtraktion As Long

Dim Multiplikation As Long

Subtraktion = Range(„B7“).Value – Range(„B9“).Value

Multiplikation = Range(„B8“).Value * Range(„B10“).Value

If Worksheets(„Tabelle1“).OptionButton1.Value = True Then

Range(„D2“) = Subtraktion

Range(„D4“).ClearContents

ElseIf Worksheets(„Tabelle1“).OptionButton2.Value = True Then

Range(„D4“) = Multiplikation

Range(„D2“).ClearContents

End If

End Sub.

Füge zuletzt auf Deinem Arbeitsblatt noch eine Schaltfläche aus den Formularsteuerelementen ein und weise diesem das Makro „OptionRechnen“ zu.

Aktiviere nun OptionButton1 und klicke auf die Schaltfläche. Es wird die Subtraktion ausgeführt.

Aktiviere jetzt OptionButton2 und klicke auf die Schaltfläche. Es wird die Multiplikation ausgeführt.

Dies war nur ein einfaches Beispiel der Anwendung von OptionButton. Vieles andere ist denkbar.

Z.B. könnte die Aktivierung von OptionButto1 bzw. von OptionButton2 jeweils ein weiteres Makro starten.

Du kannst auch Makros am Steuerelement als Private Sub hinterlegen. Gehe dazu in den Entwurfsmodus, aktiviere das Steuerelement und rufe mit der rechten Taste „Code anzeigen“ auf.

Du gelangst jetzt in den Editor. Die erste und letzte Zeile sind bereits vorgegeben. Füge die Codezeilen dazwischen ein. Ein Klick auf einen OptionButton teilt Dir nun über eine MsgBox z.B. mit, ob ein Gast eine Tasse Kaffee oder eine Tasse Tee wünscht.

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then

MsgBox („Der Gast wünscht eine Tasse Kaffee!“)

End If

End Sub

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then

MsgBox („Der Gast wünscht eine Tasse Tee!“)

End If

End Sub

VBA: Diagramm- und Zeichnungsfläche formatieren

In einem der letzten Blogbeiträge ging es um Diagrammtitel, Legende und Achsentitel

In diesem Beitrag soll gezeigt werden, wie die Diagramm- und die Zeichnungsfläche eines Säulendiagramms formatiert werden können.

Weiterlesen „VBA: Diagramm- und Zeichnungsfläche formatieren“

Diagrammelemente mit VBA bearbeiten

(Korrigiert am 06.05.2021)

In einem der letzten Blogbeiträge hast Du sehen können, wie mit VBA ein einfaches Diagramm erstellt werden kann.

Dem Diagramm fehlte ein Diagrammtitel, es fehlte die Legende und es fehlten Achsentitel.

In diesem Beitrag soll auf die fehlenden Aspekte eingegangen werden.

Weiterlesen „Diagrammelemente mit VBA bearbeiten“

Ein Diagramm per VBA erstellen

Diagramme ermöglichen es, Zahlen grafisch darzustellen, die sich dann oftmals besser verstehen und vergleichen lassen.

Die manuelle Erstellung von Diagrammen war schon mehrfach Thema im Blog.

In diesem Beitrag soll es darum gehen, ein Diagramm mit einem VBA-Makro aufzubauen.

Weiterlesen „Ein Diagramm per VBA erstellen“

VBA: Eine UserForm erstellen

In meinem Blogbeitrag „Integrierte Eingabemaske in Excel“ habe ich gezeigt, dass es in Excel bereits eine

Eine UserForm in der VBA-Programmierung ist im Prinzip nichts anderes. Auch sie dient zum Eingeben von Daten, aber in einer auf deine Bedürfnisse zugeschnittenen Datenmaske.

Wie eine UserForm erstellt und angewendet wird, zeigt dieser Beitrag.

Weiterlesen „VBA: Eine UserForm erstellen“

VBA-Steuerelemente: Eine ComboBox anlegen, füllen und verwenden

Von den Steuerelementen habe ich Dir bisher den SpinButton (Drehfeld) und die TextBox (Textfeld) erklären dürfen.

In diesem Beirag geht es um ein weiteres Steuerelement, die ComboB (Kombinationsfeld).

Die ComboBox kann sowohl auf einem Arbeitsblatt als auch in einer UserForm platziert werden.

Die UserForm wird Gegenstand eines späteren Beitrages sein, deshalb beschränke ich mich hier auf das Arbeitsblatt.

Weiterlesen „VBA-Steuerelemente: Eine ComboBox anlegen, füllen und verwenden“

VBA: Bereiche mit Range und Cells ansprechen

Wer sich schon einmal in der VBA-Programmierung versucht hat, weiß, dass zur Ansprache eines Bereiches die Anweisungen RANGE oder CELLS verwendet werden können. Beide Anweisungen liefern letztlich das gleiche Ergebnis.

Dennoch gibt es Unterschiede, mit denen sich der Beitrag beschäftigt.

Weiterlesen „VBA: Bereiche mit Range und Cells ansprechen“