VBA-lösare

Excel VBA-lösare

Hur löser du komplicerade problem? Om du inte är säker på hur du ska hantera dessa problem så har du inget att bekymra dig om. I vår tidigare artikel "Excel Solver" har vi lärt oss hur man löser ekvationer i Excel. Om du inte är medveten finns "SOLVER" också tillgängligt 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 finns tillgängligt under datafliken i Excel (om det redan är aktiverat).

För att använda SOLVER i excel 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: Välj "Lösare" i referenslistan och klicka 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 denna ekvation behöver lösas.

SolverAdd

Låt oss nu se parametrarna för SolverAdd

CellRef: För att ställa in kriterierna för att lösa problemet behöver cellen ändras.

Relation: I detta, om de logiska värdena är uppfyllda 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

Du kan ladda ner denna VBA Solver Excel-mall här - VBA Solver Excel-mall

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.

Okej, låt oss lösa den här ekvationen nu.

Steg 1: Starta VBA-delproceduren.

Koda:

 Sub Solver_Example () Avsluta Sub 

Steg 2: Först måste vi ställa in objektivcellreferensen med hjälp av SolverOk- funktionen.

Steg 3: Första argumentet för denna funktion är "SetCell", i det här exemplet behöver vi ändra värdet på vinstcellen, dvs B8-cellen.

Koda:

 Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub 

Steg 4: Nu måste vi ställa in detta cellvärde till 10000, så för MaxMinVal använder du 3 som argumentvärde.

Koda:

 Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3 End Sub 

Steg 5: Nästa argument ValueOf- värde ska vara 10000.

Koda:

 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: 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, run the code by pressing the F5 key to get the result.

When you run the code you will see the following window.

Press Ok and you will get the result in an excel sheet.

So in order to earn a profit of 10000, we need to sell 5000 units at 7 per price where the cost price is 5.

Things to Remember

  • To work with Solver in excel & VBA, first, enable it for worksheet, then enable for VBA reference.
  • Once it is enabled on both worksheets and VBA then only we can access all the Solver functions.