VBA Solver - Trin for trin-eksempel for at bruge Solver i Excel VBA

Indholdsfortegnelse

Excel VBA-løsning

Hvordan løser du komplicerede problemer? Hvis du ikke er sikker på, hvordan du skal løse disse problemer, så er der intet at bekymre dig om, vi har en løsning i vores excel. I vores tidligere artikel "Excel Solver" har vi lært, hvordan man løser ligninger i Excel. Hvis du ikke ved det, er "SOLVER" også tilgængelig med VBA. I denne artikel vil vi gennemgå, hvordan du bruger "Solver" i VBA.

Aktivér Solver i regneark

En løser er et skjult værktøj, der er tilgængeligt under datafanen i Excel (hvis allerede aktiveret).

For at bruge SOLVER i excel først skal vi aktivere denne mulighed. Følg nedenstående trin.

Trin 1: Gå til fanen FIL. Vælg "Indstillinger" under fanen FIL.

Trin 2: I vinduet Excel-indstillinger skal du vælge “Tilføjelsesprogrammer”.

Trin 3: I bunden vælger du "Excel-tilføjelsesprogrammer" og klikker på "Gå".

Trin 4: Marker nu afkrydsningsfeltet "Solver-tilføjelsesprogram", og klik på OK.

Nu skal du se "Løser" under fanen data.

Aktivér Solver i VBA

Også i VBA er Solver et eksternt værktøj; vi er nødt til at sætte det i stand til at bruge det. Følg nedenstående trin for at aktivere det.

Trin 1: Gå til Værktøjer >>> Reference i Visual Basic Editor-vinduet.

Trin 2: Fra referencelisten skal du vælge “Løser” og klikke på Ok for at bruge den.

Nu kan vi også bruge Solver i VBA.

Løsningsfunktioner i VBA

For at skrive en VBA-kode skal vi bruge tre “Solver-funktioner” i VBA, og disse funktioner er “SolverOk, SolverAdd og SolverSolve”.

SolverOk

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

SetCell: Dette vil være den cellehenvisning, der skal ændres, dvs. Profitcelle.

MaxMinVal: Dette er en valgfri parameter, nedenunder er tal og specifikationer.

  • 1 = Maksimer
  • 2 = Minimer
  • 3 = Match en bestemt værdi

ValueOf: Denne parameter skal leveres, hvis MaxMinVal- argumentet er 3.

ByChange: Ved at ændre hvilke celler skal denne ligning løses.

SolverTilføj

Lad os nu se parametrene for SolverAdd

CellRef: For at indstille kriterierne for at løse problemet, skal cellen ændres.

Forhold: I dette, hvis de logiske værdier er opfyldt, kan vi bruge nedenstående tal.

  • 1 er mindre end (<=)
  • 2 er lig med (=)
  • 3 er større end (> =)
  • 4 er skal have endelige værdier, der er heltal.
  • 5 er skal have værdier mellem 0 eller 1.
  • 6 er skal have endelige værdier, der alle er forskellige og heltal.

Eksempel på Solver i Excel VBA

For et eksempel se nedenstående scenario.

Ved hjælp af denne tabel er vi nødt til at identificere "Profit" -beløbet, som skal være mindst 10000. For at nå dette nummer har vi visse betingelser.

  • Enheder, der skal sælges, skal have en heltalsværdi.
  • Pris / enhed skal være mellem 7 og 15.

Baseret på disse betingelser er vi nødt til at identificere, hvor mange enheder der skal sælges til hvilken pris for at få fortjenstværdien på 10000.

Ok, lad os løse denne ligning nu.

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

Når du kører koden, ser du følgende vindue.

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

Så for at tjene et overskud på 10000 er vi nødt til at sælge 5000 enheder til 7 pr. Pris, hvor kostprisen er 5.

Ting at huske

  • For at arbejde med Solver i excel & VBA skal du først aktivere det til regneark og derefter aktivere for VBA-reference.
  • Når det er aktiveret på begge regneark og VBA, er det kun vi, der har adgang til alle Solver-funktionerne.

Interessante artikler...