VBA Solver - Steg for trinn-eksempel for å bruke Solver i Excel VBA

Innholdsfortegnelse

Excel VBA Solver

Hvordan løser du kompliserte problemer? Hvis du ikke er sikker på hvordan du skal gjøre noe med disse problemene, har du ikke noe å bekymre deg for. I vår tidligere artikkel "Excel Solver" har vi lært hvordan vi kan løse ligninger i Excel. Hvis du ikke er klar over det, er "SOLVER" også tilgjengelig med VBA. I denne artikkelen vil vi ta deg gjennom hvordan du bruker “Solver” i VBA.

Aktiver løsning i regneark

En løser er et skjult verktøy tilgjengelig under datafanen i Excel (hvis allerede aktivert).

For å bruke SOLVER i excel først, må vi aktivere dette alternativet. Følg trinnene nedenfor.

Trinn 1: Gå til FIL-fanen. Velg "Alternativer" under FIL-fanen.

Trinn 2: Velg "Add-Ins" i Excel Options-vinduet.

Trinn 3: Velg "Excel-tillegg" nederst og klikk på "Gå".

Trinn 4: Merk av i boksen “Solver Add-in” og klikk på, Ok.

Nå må du se "Løser" under datafanen.

Aktiver Solver i VBA

Også i VBA er Solver et eksternt verktøy; vi må gjøre det mulig å bruke den. Følg trinnene nedenfor for å aktivere det.

Trinn 1: Gå til Verktøy >>> Referanse i Visual Basic Editor-vinduet.

Trinn 2: Fra referanselisten, velg “Løser” og klikk på OK for å bruke den.

Nå kan vi også bruke Solver i VBA.

Løsningsfunksjoner i VBA

For å skrive en VBA-kode må vi bruke tre "Solver Functions" i VBA, og disse funksjonene er "SolverOk, SolverAdd og SolverSolve".

SolverOk

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

SetCell: Dette vil være cellereferansen som må endres, dvs. Profitcelle .

MaxMinVal: Dette er en valgfri parameter, nedenfor er tall og spesifikasjoner.

  • 1 = Maksimer
  • 2 = Minimer
  • 3 = Match en spesifikk verdi

ValueOf: Denne parameteren må leveres hvis MaxMinVal- argumentet er 3.

ByChange: Ved å endre hvilke celler, må denne ligningen løses.

Løs til

La oss nå se parametrene til SolverAdd

CellRef: For å sette kriteriene for å løse problemet, må cellen endres.

Forhold: I dette, hvis de logiske verdiene er oppfylt, kan vi bruke tallene nedenfor.

  • 1 er mindre enn (<=)
  • 2 er lik (=)
  • 3 er større enn (> =)
  • 4 er må ha endelige verdier som er heltall.
  • 5 er må ha verdier mellom 0 eller 1.
  • 6 er må ha endelige verdier som er forskjellige og heltall.

Eksempel på Solver i Excel VBA

For et eksempel se på scenariet nedenfor.

Ved å bruke denne tabellen må vi identifisere "Profit" -beløpet, som må være minimum 10000. For å komme til dette tallet har vi visse betingelser.

  • Enheter å selge skal være et helt tall.
  • Pris / enhet skal være mellom 7 og 15.

Basert på disse forholdene må vi identifisere hvor mange enheter som skal selges til hvilken pris for å få fortjenesteverdien på 10000.

Ok, la oss løse denne ligningen nå.

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, kjør koden ved å trykke på F5-tasten for å få resultatet.

Når du kjører koden, ser du følgende vindu.

Trykk på Ok, så får du resultatet i et excel-ark.

Så for å tjene et overskudd på 10000, må vi selge 5000 enheter til 7 per pris der kostprisen er 5.

Ting å huske

  • For å jobbe med Solver i excel & VBA, må du først aktivere det for regneark, og deretter aktivere for VBA-referanse.
  • Når det er aktivert på begge regneark og VBA, er det bare vi som får tilgang til alle Solver-funksjonene.

Interessante artikler...