VBA Solver - Schritt für Schritt Beispiel für die Verwendung von Solver in Excel VBA

Inhaltsverzeichnis

Excel VBA Solver

Wie lösen Sie komplizierte Probleme? Wenn Sie sich nicht sicher sind, wie Sie diese Probleme lösen sollen, haben wir keinen Grund zur Sorge. Wir haben einen Löser in unserem Excel. In unserem früheren Artikel „Excel Solver“ haben wir gelernt, wie man Gleichungen in Excel löst. Wenn Sie sich nicht bewusst sind, ist „SOLVER“ auch mit VBA verfügbar. In diesem Artikel werden wir Sie durch die Verwendung von "Solver" in VBA führen.

Aktivieren Sie Solver im Arbeitsblatt

Ein Solver ist ein verstecktes Werkzeug, das in Excel auf der Registerkarte "Daten" verfügbar ist (sofern bereits aktiviert).

Um SOLVER zuerst in Excel verwenden zu können, müssen wir diese Option aktivieren. Befolgen Sie die folgenden Schritte.

Schritt 1: Gehen Sie zur Registerkarte DATEI. Wählen Sie auf der Registerkarte DATEI die Option "Optionen".

Schritt 2: Wählen Sie im Fenster "Excel-Optionen" die Option "Add-Ins".

Schritt 3: Wählen Sie unten "Excel Add-Ins" und klicken Sie auf "Los".

Schritt 4: Aktivieren Sie nun das Kontrollkästchen "Solver Add-In" und klicken Sie auf "OK".

Jetzt müssen Sie "Solver" auf der Registerkarte "Daten" sehen.

Aktivieren Sie Solver in VBA

Auch in VBA ist Solver ein externes Tool. wir müssen es aktivieren, um es zu benutzen. Führen Sie die folgenden Schritte aus, um es zu aktivieren.

Schritt 1: Gehen Sie zu Tools >>> Referenz im Visual Basic-Editorfenster.

Schritt 2: Wählen Sie in der Referenzliste "Solver" und klicken Sie auf "OK", um es zu verwenden.

Jetzt können wir Solver auch in VBA verwenden.

Solver-Funktionen in VBA

Um einen VBA-Code zu schreiben, müssen drei "Solver-Funktionen" in VBA verwendet werden. Diese Funktionen sind "SolverOk, SolverAdd und SolverSolve".

SolverOk

SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

SetCell: Dies ist die Zellreferenz , die geändert werden muss, dh die Profit-Zelle.

MaxMinVal: Dies ist ein optionaler Parameter. Nachfolgend finden Sie Zahlen und Bezeichner .

  • 1 = Maximieren
  • 2 = Minimieren
  • 3 = Einen bestimmten Wert abgleichen

ValueOf: Dieser Parameter muss angeben, wenn das MaxMinVal- Argument 3 ist.

ByChange: Durch Ändern der Zellen muss diese Gleichung gelöst werden.

SolverAdd

Nun sehen wir uns die Parameter von SolverAdd an

CellRef: Um die Kriterien zur Lösung des Problems festzulegen , muss die Zelle geändert werden.

Beziehung: Wenn die logischen Werte erfüllt sind, können wir die folgenden Zahlen verwenden.

  • 1 ist kleiner als (<=)
  • 2 ist gleich (=)
  • 3 ist größer als (> =)
  • 4 ist muss Endwerte haben, die ganze Zahlen sind.
  • 5 ist muss Werte zwischen 0 oder 1 haben.
  • 6 is muss Endwerte haben, die alle unterschiedlich und ganzzahlig sind.

Beispiel für Solver in Excel VBA

Ein Beispiel finden Sie im folgenden Szenario.

Anhand dieser Tabelle müssen wir den Gewinnbetrag ermitteln, der mindestens 10000 betragen muss. Um zu dieser Zahl zu gelangen, müssen bestimmte Bedingungen erfüllt sein.

  • Zu verkaufende Einheiten sollten ein ganzzahliger Wert sein.
  • Preis / Einheit sollte zwischen 7 und 15 liegen.

Basierend auf diesen Bedingungen müssen wir ermitteln, wie viele Einheiten zu welchem ​​Preis verkauft werden sollen, um den Gewinnwert von 10000 zu erhalten.

Ok, lassen Sie uns diese Gleichung jetzt lösen.

Schritt 1: Starten Sie die VBA-Unterprozedur.

Code:

Sub Solver_Example () End Sub

Schritt 2: Zuerst müssen wir die Zielzellenreferenz mithilfe der SolverOk- Funktion festlegen .

Schritt 3: Das erste Argument dieser Funktion ist "SetCell". In diesem Beispiel müssen wir den Wert der Profit-Zelle, dh der B8-Zelle, ändern.

Code:

Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub

Schritt 4: Jetzt müssen wir diesen Zellenwert auf 10000 setzen, also verwenden Sie für MaxMinVal 3 als Argumentwert.

Code:

Sub Solver_Example () SolverOk SetCell: = Bereich ("B8"), MaxMinVal: = 3 End Sub

Schritt 5: Das nächste Argument ValueOf sollte 10000 sein.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000 End Sub

The next argument is ByChange i.e. by changing which cells this equation needs to be solved. In this case by changing Units to Sell (B1) and Price Per Unit (B2) cell needs to be changed.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") End Sub

Note: remaining arguments are not required here.

Step 6: Once the objective cell is set, now we need to construct other criteria’s. For this open “SolverAdd” function.

Step 7: First Cell Ref we need to change is Price Per Unit cell i.e. B2 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2") End Sub

Step 8: This cell needs to be>= 7, so the Relation argument will be 3.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3 End Sub

Step 9: This cell value should be>=7 i.e. Formula Text = 7.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 End Sub

Step 10: Similarly the same cell needs to be less than 15, so for this relation is <= i.e. 1 as the argument value.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 End Sub

Step 11: First cell i.e. Units to Sell must be an Integer value for this also set up the criteria as below.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" End Sub

Step 12: In one final step, we need to add the SolverSolve function.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" SolverSolve End Sub

Ok, führen Sie den Code aus, indem Sie die Taste F5 drücken, um das Ergebnis zu erhalten.

Wenn Sie den Code ausführen, wird das folgende Fenster angezeigt.

Drücken Sie OK und Sie erhalten das Ergebnis in einer Excel-Tabelle.

Um einen Gewinn von 10000 zu erzielen, müssen wir 5000 Einheiten zu 7 pro Preis verkaufen, wobei der Selbstkostenpreis 5 beträgt.

Dinge, an die man sich erinnern sollte

  • Um mit Solver in Excel und VBA zu arbeiten, aktivieren Sie es zuerst für das Arbeitsblatt und dann für die VBA-Referenz.
  • Sobald es sowohl auf Arbeitsblättern als auch in VBA aktiviert ist, können nur wir auf alle Solver-Funktionen zugreifen.

Interessante Beiträge...