VBA Solver - Steg för steg-exempel för att använda Solver i Excel VBA

Innehållsförteckning

Excel VBA-lösare

Hur löser du komplicerade problem? Om du inte är säker på hur du ska gå tillväga för dessa problem så finns det inget att oroa oss för. I vår tidigare artikel "Excel Solver" har vi lärt oss hur man löser ekvationer i Excel. Om du inte är medveten om det finns "SOLVER" också med VBA. I den här artikeln tar vi dig igenom hur du använder “Solver” i VBA.

Aktivera lösare i kalkylblad

En lösare är ett dolt verktyg som är tillgängligt under Excel-fliken (om det redan är aktiverat).

För att använda SOLVER i Excel först måste vi aktivera det här alternativet. Följ stegen nedan.

Steg 1: Gå till fliken FIL. Välj "Alternativ" under fliken FIL.

Steg 2: Välj "Tillägg" i Excel-alternativfönstret.

Steg 3: Välj "Excel-tillägg" längst ned och klicka på "Gå".

Steg 4: Markera nu rutan "Solver Add-in" och klicka på OK.

Nu måste du se "Lösare" under datafliken.

Aktivera lösare i VBA

Även i VBA är Solver ett externt verktyg. vi måste göra det möjligt för den att använda den. Följ stegen nedan för att aktivera det.

Steg 1: Gå till Verktyg >>> Referens i Visual Basic Editor-fönstret.

Steg 2: Från referenslistan väljer du "Lösare" och klickar på OK för att använda den.

Nu kan vi också använda Solver i VBA.

Lösningsfunktioner i VBA

För att skriva en VBA-kod måste vi använda tre “Solver-funktioner” i VBA och dessa funktioner är “SolverOk, SolverAdd och SolverSolve”.

SolverOk

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

SetCell: Detta kommer att vara cellreferensen som behöver ändras, dvs Profit cell.

MaxMinVal: Detta är en valfri parameter, nedan visas siffror och specifikationer.

  • 1 = Maximera
  • 2 = Minimera
  • 3 = Matcha ett specifikt värde

ValueOf: Denna parameter måste levereras om MaxMinVal- argumentet är 3.

ByChange: Genom att ändra vilka celler måste denna ekvation lösas.

SolverAdd

Låt oss nu se parametrarna för SolverAdd

CellRef: För att ställa in kriterier för att lösa problemet måste vad som är cellen ändras.

Relation: Om detta är logiskt, kan vi använda nedanstående siffror.

  • 1 är mindre än (<=)
  • 2 är lika med (=)
  • 3 är större än (> =)
  • 4 är måste ha slutliga värden som är heltal.
  • 5 är måste ha värden mellan 0 eller 1.
  • 6 är måste ha slutliga värden som alla är olika och heltal.

Exempel på lösare i Excel VBA

För ett exempel, se nedanstående scenario.

Med hjälp av denna tabell måste vi identifiera beloppet "Vinst", som måste vara minst 10000. För att nå detta nummer har vi vissa villkor.

  • Enheter att sälja ska vara ett heltal.
  • Pris / enhet ska vara mellan 7 och 15.

Baserat på dessa villkor måste vi identifiera hur många enheter som ska säljas till vilket pris för att få vinstvärdet 10000.

Ok, låt oss lösa den här ekvationen 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 genom att trycka på F5-tangenten för att få resultatet.

När du kör koden ser du följande fönster.

Tryck på Ok så får du resultatet i ett excel-ark.

Så för att tjäna en vinst på 10000 måste vi sälja 5000 enheter till 7 per pris där kostnaden är 5.

Saker att komma ihåg

  • För att arbeta med Solver i excel & VBA, aktivera det först för kalkylblad, aktivera sedan för VBA-referens.
  • När det väl är aktiverat på både kalkylblad och VBA, har vi bara tillgång till alla lösningsfunktioner.

Intressanta artiklar...