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-mallFö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.