VBA-oplosser - Stap voor stap Voorbeeld om Solver te gebruiken in Excel VBA

Inhoudsopgave

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 kunt gebruiken.

Schakel Oplosser in werkblad in

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

Om SOLVER eerst in Excel te gebruiken, moeten we deze optie 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, moeten we drie "Solver Functions" in VBA gebruiken 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 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 welke cellen te wijzigen, moet deze vergelijking 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

Kijk voor een voorbeeld naar het onderstaande scenario.

Aan de hand van deze tabel moeten we het "Winst" -bedrag 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.

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, voer de code uit door op de F5-toets te drukken om het resultaat te krijgen.

Wanneer u de code uitvoert, ziet u het volgende venster.

Druk op Ok en je krijgt het resultaat in een Excel-sheet.

Dus om een ​​winst van 10.000 te maken, moeten we 5000 eenheden verkopen tegen 7 per prijs, waarbij de kostprijs 5 is.

Dingen om te onthouden

  • Om met Oplosser in Excel en VBA te werken, schakelt u het eerst in voor het werkblad en vervolgens in voor VBA-referentie.
  • Als het eenmaal is ingeschakeld op zowel werkbladen als VBA, hebben alleen wij toegang tot alle Oplosser-functies.

Interessante artikelen...