VBA Solver - Primjer korak po korak za korištenje Solvera u programu Excel VBA

Excel VBA Solver

Kako rješavate komplicirane probleme? Ako niste sigurni kako se pozabaviti tim problemima, onda ne brinite, imamo rješenje u našem excelu. U našem ranijem članku "Excel Solver" naučili smo kako rješavati jednadžbe u excelu. Ako niste svjesni, "SOLVER" je dostupan i s VBA-om. U ovom članku provest ćemo vas kako koristiti "Solver" u VBA.

Omogućite Solver u radnom listu

Riješivač je skriveni alat dostupan na kartici podataka u Excelu (ako je već omogućen).

Da bismo prvo koristili SOLVER u Excelu, moramo omogućiti ovu opciju. Slijedite korake u nastavku.

Korak 1: Idite na karticu FILE. Pod karticom DATOTEKA odaberite "Opcije".

Korak 2: U prozoru programa Excel odaberite "Dodaci".

Korak 3: Na dnu odabire "Excel Add-Ins" i kliknite "Go".

Korak 4: Sada označite okvir "Dodatak za rješavanje" i kliknite na, Ok.

Sada na kartici s podacima morate vidjeti "Solver".

Omogući Solver u VBA

I u VBA-u je Solver vanjski alat; moramo mu omogućiti da ga koristi. Slijedite korake u nastavku da biste ga omogućili.

Korak 1: Idite na Alati >>> Referenca u prozoru Visual Basic Editor.

Korak 2: Na popisu referenci odaberite "Solver" i kliknite U redu da biste ga koristili.

Sada Solver možemo koristiti i u VBA.

Funkcije rješavanja u VBA

Za pisanje VBA koda trebamo upotrijebiti tri "funkcije rješavanja" u VBA, a to su "SolverOk, SolverAdd i SolverSolve".

SolverOk

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

SetCell: Ovo će biti referenca ćelije koju treba promijeniti, tj. Profit ćelija.

MaxMinVal: Ovo je neobavezni parametar, u nastavku su brojevi i specifikatori.

  • 1 = Uvećaj
  • 2 = Smanji
  • 3 = Podudaranje određene vrijednosti

ValueOf: Ovaj parametar mora navesti ako je argument MaxMinVal 3.

ByChange: Promjenom kojih ćelija treba riješiti ovu jednadžbu.

RiješilacDodaj

Sada da vidimo parametre SolverAdd-a

CellRef: Da biste postavili kriterije za rješavanje problema, potrebno je promijeniti što je stanica.

Odnos: U ovom slučaju, ako su logičke vrijednosti zadovoljene, možemo koristiti donje brojeve.

  • 1 je manje od (<=)
  • 2 jednako je (=)
  • 3 je veće od (> =)
  • 4 mora imati konačne vrijednosti koje su cjelobrojne vrijednosti.
  • 5 mora imati vrijednosti između 0 ili 1.
  • 6 mora imati konačne vrijednosti koje se razlikuju i cijele brojeve.

Primjer rješavača u programu Excel VBA

Za primjer pogledajte scenarij u nastavku.

Pomoću ove tablice moramo identificirati iznos dobiti, koji mora biti najmanje 10000. Da bismo došli do ovog broja, imamo određene uvjete.

  • Jedinice za prodaju trebaju biti cjelobrojna vrijednost.
  • Cijena / jedinica treba biti između 7 i 15.

Na temelju tih uvjeta moramo identificirati koliko jedinica prodati po kojoj cijeni da bismo dobili vrijednost dobiti od 10000.

Ok, riješimo sada ovu jednadžbu.

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, pokrenite kôd pritiskom na tipku F5 da biste dobili rezultat.

Kada pokrenete kôd, vidjet ćete sljedeći prozor.

Pritisnite Ok i dobit ćete rezultat u excel listu.

Dakle, da bismo zaradili 10000, moramo prodati 5000 jedinica po 7 po cijeni, gdje je cijena koštanja 5.

Stvari koje treba zapamtiti

  • Da biste radili s Solverom u excelu i VBA, prvo ga omogućite za radni list, a zatim omogućite za VBA referencu.
  • Jednom kad je omogućen na oba radna lista i VBA, samo mi možemo pristupiti svim funkcijama Solvera.

Zanimljivi članci...