VBA Solver - Primer po korakih za uporabo rešitve v Excelu VBA

Excel VBA Solver

Kako rešujete zapletene probleme? Če niste prepričani, kako se lotiti teh težav, potem ne skrbite, imamo rešitev v našem Excelu. V prejšnjem članku »Excel Solver« smo se naučili reševanja enačb v Excelu. Če se ne zavedate, je možnost »SOLVER« na voljo tudi z VBA. V tem članku vam bomo predstavili, kako uporabljati »Solver« v VBA.

Omogoči Solver v delovnem listu

Reševalnik je skrito orodje, ki je na voljo na zavihku s podatki v Excelu (če je že omogočeno).

Če želite najprej uporabiti SOLVER v Excelu, moramo to možnost omogočiti. Sledite spodnjim korakom.

1. korak: Pojdite na zavihek DATOTEKA. Na zavihku DATOTEKA izberite »Možnosti«.

2. korak: V oknu Excelove možnosti izberite »Dodatki«.

3. korak: Na dnu izbere »Excel Add-Ins« in kliknite »Go«.

4. korak: Zdaj potrdite polje »Dodatek za reševalce« in kliknite V redu.

Zdaj morate na kartici s podatki videti »Solver«.

Omogoči Solver v VBA

Tudi v VBA je Solver zunanje orodje; omogočiti mu moramo uporabo. Sledite spodnjim korakom, da jo omogočite.

1. korak: Pojdite na Orodja >>> Referenca v oknu urejevalnika Visual Basic.

2. korak: Na seznamu referenc izberite “Solver” in kliknite V redu, da ga uporabite.

Zdaj lahko Solver uporabljamo tudi v VBA.

Funkcije reševalca v VBA

Za pisanje kode VBA moramo v VBA uporabiti tri »funkcije reševalca«, ki so »SolverOk, SolverAdd in SolverSolve«.

SolverOk

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

SetCell: To bo referenca celice, ki jo je treba spremeniti, tj. Celica dobička.

MaxMinVal: To je neobvezen parameter, spodaj so številke in specifikatorji.

  • 1 = Povečaj
  • 2 = Zmanjšaj
  • 3 = Ujemanje določene vrednosti

ValueOf: Ta parameter mora navesti, če je argument MaxMinVal 3.

ByChange: S spreminjanjem celic je treba to enačbo rešiti.

SolverAdd

Zdaj pa poglejmo parametre SolverAdd

CellRef: Če želite določiti merila za rešitev težave, morate spremeniti, kaj je celica.

Razmerje: V tem primeru lahko uporabimo spodnje številke, če so logične vrednosti izpolnjene.

  • 1 je manj kot (<=)
  • 2 je enako (=)
  • 3 je večje od (> =)
  • 4 mora imeti končne vrednosti, ki so cela števila.
  • 5 mora imeti vrednosti med 0 ali 1.
  • 6 mora imeti končne vrednosti, ki se razlikujejo in števila.

Primer rešitve v Excelu VBA

Za primer si oglejte spodnji scenarij.

V tej tabeli moramo določiti znesek dobička, ki mora biti najmanj 10000. Da bi prišli do te številke, imamo določene pogoje.

  • Enote za prodajo naj bodo celoštevilska vrednost.
  • Cena / enota naj bo med 7 in 15.

Na podlagi teh pogojev moramo ugotoviti, koliko enot prodati po kakšni ceni, da dobimo vrednost dobička 10000.

Ok, zdaj rešimo to enačbo.

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, zaženite kodo s pritiskom na tipko F5, da dobite rezultat.

Ko zaženete kodo, se prikaže naslednje okno.

Pritisnite Ok in rezultat boste dobili v Excelovem listu.

Torej, da bi zaslužili 10000 dobička, moramo prodati 5000 enot po 7 na ceno, kjer je stroškovna cena 5.

Stvari, ki si jih je treba zapomniti

  • Če želite delati s Solverjem v excelu in VBA, ga najprej omogočite za delovni list, nato omogočite za referenco VBA.
  • Ko je omogočena na obeh delovnih listih in VBA, lahko samo mi dostopamo do vseh funkcij Solverja.

Zanimive Članki...