VBA-Solver

Excel VBA Solver

Wie lösen Sie komplizierte Probleme? Wenn Sie sich nicht sicher sind, wie Sie diese Probleme lösen sollen, haben Sie 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 nicht wissen, dass "SOLVER" auch mit VBA verfügbar ist. 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 zu verwenden, 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 verwenden zu können. 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

Sie können diese VBA Solver Excel-Vorlage hier herunterladen - VBA Solver Excel-Vorlage

Ein Beispiel finden Sie im folgenden Szenario.

Anhand dieser Tabelle müssen wir den Gewinnbetrag identifizieren, der mindestens 10000 betragen muss. Um zu dieser Zahl zu gelangen, haben wir bestimmte Bedingungen.

  • 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: = Bereich ("B8"), MaxMinVal: = 3, ValueOf: = 10000 End Sub 

Das nächste Argument ist ByChange, dh durch Ändern, welche Zellen diese Gleichung gelöst werden muss. In diesem Fall muss durch Ändern der Einheiten in Verkauf (B1) und Preis pro Einheit (B2) die Zelle geändert werden.

Code:

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

Hinweis: Die verbleibenden Argumente sind hier nicht erforderlich.

Schritt 6: Sobald die Zielzelle festgelegt ist, müssen wir andere Kriterien erstellen. Öffnen Sie dazu die Funktion „SolverAdd“.

Schritt 7: Die erste Zellreferenz, die wir ändern müssen, ist der Preis pro Einheit, dh die B2-Zelle.

Code:

 Sub Solver_Example () SolverOk SetCell: = Bereich ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Bereich ("B1: B2") SolverAdd CellRef: = Bereich ("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: 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, run the code by pressing the F5 key to get the result.

When you run the code you will see the following window.

Press Ok and you will get the result in an excel sheet.

So in order to earn a profit of 10000, we need to sell 5000 units at 7 per price where the cost price is 5.

Things to Remember

  • To work with Solver in excel & VBA, first, enable it for worksheet, then enable for VBA reference.
  • Once it is enabled on both worksheets and VBA then only we can access all the Solver functions.