VBA megoldó - Lépésről lépésre példa a Solver használatára az Excel VBA-ban

Tartalomjegyzék

Excel VBA Solver

Hogyan oldja meg a bonyolult problémákat? Ha nem vagy biztos abban, hogyan kezelheted ezeket a problémákat, akkor semmi aggodalomra okot adó megoldás nincs az excelben. Korábbi „Excel Solver” cikkünkben megtanultuk, hogyan lehet megoldani az egyenleteket az excelben. Ha nincs tudatában, akkor a „SOLVER” elérhető a VBA-val is. Ebben a cikkben bemutatjuk, hogyan kell használni a „Solver” -t a VBA-ban.

Engedélyezze a Megoldót a munkalapon

A megoldó egy rejtett eszköz, amely az Excel adatlapján érhető el (ha már engedélyezve van).

Először a SOLVER használatát az excelben engedélyeznünk kell ezt a lehetőséget. Kövesse az alábbi lépéseket.

1. lépés: Menjen a FÁJL fülre. A FÁJL fül alatt válassza az „Opciók” lehetőséget.

2. lépés: Az Excel beállításai ablakban válassza a „Bővítmények” lehetőséget.

3. lépés: Alul az „Excel bővítmények” lehetőséget választja, majd kattintson a „Tovább” gombra.

4. lépés: Most jelölje be a „Solver Add-in” négyzetet, és kattintson az OK gombra.

Most az adatok fül alatt látnia kell a „Megoldó” lehetőséget.

Engedélyezze a Solver szolgáltatást a VBA-ban

A VBA-ban is a Solver külső eszköz; lehetővé kell tennünk a használatát. Kövesse az alábbi lépéseket annak engedélyezéséhez.

1. lépés: Nyissa meg az Eszközök >>> Referencia elemet a Visual Basic Editor ablakban.

2. lépés: A hivatkozási listában válassza a „Megoldó” lehetőséget, és kattintson az OK gombra a használatához.

Most már használhatjuk a Solver-t a VBA-ban is.

Megoldó funkciók a VBA-ban

A VBA kód megírásához három „Solver Functions” -ot kell használnunk a VBA-ban, ezek a függvények a „SolverOk, SolverAdd és SolverSolve”.

SolverOk

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

SetCell: Ez lesz a cellahivatkozás, amelyet meg kell változtatni, azaz a Profit cellát.

MaxMinVal: Ez egy opcionális paraméter, az alábbiakban számok és specifikációk szerepelnek.

  • 1 = maximalizálás
  • 2 = Minimalizálás
  • 3 = Egyezik meg egy adott értékkel

ValueOf: Ezt a paramétert meg kell adni, ha a MaxMinVal argumentum 3.

ByChange: Az egyes cellák megváltoztatásával ezt az egyenletet meg kell oldani.

SolverAdd

Most nézzük meg a SolverAdd paramétereit

CellRef: A probléma megoldásához szükséges kritériumok beállításához meg kell változtatni a cellát.

Relation: In this, if the logical values are satisfied then we can use the below numbers.

  • 1 is less than (<=)
  • 2 is equal to (=)
  • 3 is greater than (>=)
  • 4 is must have final values that are integers.
  • 5 is must have values between 0 or 1.
  • 6 is must have final values that are all different and integers.

Example of Solver in Excel VBA

For an example look at the below scenario.

Using this table, we need to identify the “Profit” amount, which needs to be a minimum of 10000. To arrive at this number we have certain conditions.

  • Units to Sell should be an integer value.
  • Price / Unit should be between 7 and 15.

Based on these conditions we need to identify how many units to sell at what price to get the profit value of 10000.

Ok, let’s solve this equation now.

Step 1: Start the VBA subprocedure.

Code:

Sub Solver_Example() End Sub

Step 2: First we need to set the Objective cell reference by using the SolverOk function.

Step 3: First argument of this function is “SetCell”, in this example we need to change the value of Profit cell i.e. B8 cell.

Code:

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

Step 4: Now we need to set this cell value to 10000, so for MaxMinVal use 3 as the argument value.

Code:

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

Step 5: The next argument ValueOf value should be 10000.

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, futtassa a kódot az F5 billentyű lenyomásával az eredmény eléréséhez.

A kód futtatásakor a következő ablak jelenik meg.

Nyomja meg az OK gombot, és az eredményt egy excel lapban kapja meg.

Tehát 10000 nyereség megszerzéséhez 5000 egységet kell eladni 7 áronként, ahol az önköltségi ár 5.

Dolgok, amikre emlékezni kell

  • Az Excel és a VBA megoldóival való együttműködéshez először engedélyezze a munkalapra, majd engedélyezze a VBA referenciát.
  • Miután engedélyezte mind a munkalapokon, mind a VBA-n, akkor csak mi férhetünk hozzá az összes Solver funkcióhoz.

érdekes cikkek...