VBA-oplosser

Excel VBA-oplosser

Hoe los je ingewikkelde problemen op? Als u niet zeker weet hoe u deze problemen moet aanpakken, hoeft u zich geen zorgen te maken, we hebben een oplosser in onze Excel. In ons eerdere artikel "Excel Solver" hebben we geleerd hoe we vergelijkingen in Excel kunnen oplossen. Als u het niet weet, is "SOLVER" ook beschikbaar met VBA. In dit artikel laten we u zien hoe u "Solver" in VBA gebruikt.

Schakel Oplosser in werkblad in

Een oplosser is een verborgen tool die beschikbaar is onder het gegevenstabblad in Excel (indien al ingeschakeld).

Om SOLVER in Excel te gebruiken, moeten we deze optie eerst inschakelen. Volg onderstaande stappen.

Stap 1: Ga naar het tabblad FILE. Kies onder het tabblad FILE voor "Options".

Stap 2: Kies in het Excel-optiesvenster “Add-Ins”.

Stap 3: Kies onderaan voor "Excel Add-Ins" en klik op "Go".

Stap 4: Vink nu het vakje "Solver Add-in" aan en klik op Ok.

Nu moet u "Oplosser" zien onder het gegevenstabblad.

Schakel Oplosser in VBA in

Ook in VBA is Solver een externe tool, we moeten het inschakelen om het te gebruiken. Volg de onderstaande stappen om het in te schakelen.

Stap 1: Ga naar Tools >>> Reference in Visual Basic Editor Window.

Stap 2: Kies "Oplosser" uit de lijst met referenties en klik op Ok om het te gebruiken.

Nu kunnen we Solver ook in VBA gebruiken.

Oplosserfuncties in VBA

Om een ​​VBA-code te schrijven, hebben we drie "Solver Functions" in VBA nodig en die functies zijn "SolverOk, SolverAdd en SolverSolve".

SolverOk

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

SetCell: dit is de celverwijzing die moet worden gewijzigd, dwz de winstcel .

MaxMinVal: Dit is een optionele parameter, hieronder staan ​​getallen en specificaties.

  • 1 = Maximaliseren
  • 2 = Minimaliseren
  • 3 = Komt overeen met een specifieke waarde

ValueOf: deze parameter moet opgeven als het MaxMinVal- argument 3 is.

ByChange: Door te veranderen in welke cellen deze vergelijking moet worden opgelost.

OplosserAdd

Laten we nu eens kijken naar de parameters van SolverAdd

CellRef: Om de criteria in te stellen om het probleem op te lossen, moet de cel worden gewijzigd.

Relatie: Hierin, als aan de logische waarden is voldaan, kunnen we de onderstaande nummers gebruiken.

  • 1 is kleiner dan (<=)
  • 2 is gelijk aan (=)
  • 3 is groter dan (> =)
  • 4 is moet definitieve waarden hebben die gehele getallen zijn.
  • 5 is moet waarden hebben tussen 0 of 1.
  • 6 is moet eindwaarden hebben die allemaal verschillend zijn en gehele getallen.

Voorbeeld van Oplosser in Excel VBA

U kunt deze VBA Solver Excel-sjabloon hier downloaden - VBA Solver Excel-sjabloon

Kijk voor een voorbeeld naar het onderstaande scenario.

Aan de hand van deze tabel moeten we het bedrag van de “Winst” identificeren dat minimaal 10000 moet zijn. Om op dit aantal te komen, hebben we bepaalde voorwaarden.

  • Eenheden om te verkopen moeten een geheel getal zijn.
  • Prijs / eenheid moet tussen 7 en 15 liggen.

Op basis van deze voorwaarden moeten we bepalen hoeveel eenheden we tegen welke prijs moeten verkopen om de winstwaarde van 10.000 te krijgen.

Oké, laten we deze vergelijking nu oplossen.

Stap 1: Start de VBA-subprocedure.

Code:

 Sub Solver_Example () End Sub

Stap 2: Eerst moeten we de Objective-celverwijzing instellen met behulp van de SolverOk- functie.

Stap 3: Het eerste argument van deze functie is "SetCell", in dit voorbeeld moeten we de waarde van de Profit-cel wijzigen, dwz de B8-cel.

Code:

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

Stap 4: Nu moeten we deze celwaarde instellen op 10000, dus gebruik voor MaxMinVal 3 als de argumentwaarde.

Code:

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

Stap 5: Het volgende argument ValueOf- waarde moet 10000 zijn.

Code:

 Sub Solver_Example () SolverOk SetCell: = Bereik ("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.