VBA Solver

Excel VBA Solver

Jak rozwiązujesz skomplikowane problemy? Jeśli nie jesteś pewien, jak rozwiązać te problemy, nie martw się, mamy rozwiązanie w naszym programie Excel. Z naszego wcześniejszego artykułu „Excel Solver” dowiedzieliśmy się, jak rozwiązywać równania w programie Excel. Jeśli nie wiesz, „SOLVER” jest również dostępny w VBA. W tym artykule pokażemy, jak używać „Solvera” w języku VBA.

Włącz dodatek Solver w arkuszu

Solver to ukryte narzędzie dostępne na karcie danych w programie Excel (jeśli jest już włączone).

Aby używać SOLVER w programie Excel, musimy najpierw włączyć tę opcję. Wykonaj poniższe kroki.

Krok 1: Przejdź do zakładki PLIK. W zakładce PLIK wybierz „Opcje”.

Krok 2: W oknie opcji programu Excel wybierz „Dodatki”.

Krok 3: Na dole wybiera „Dodatki programu Excel” i kliknij „Idź”.

Krok 4: Teraz zaznacz pole „Dodatek Solver” i kliknij OK.

Teraz w zakładce danych musisz zobaczyć „Solver”.

Włącz Solver w VBA

Również w VBA Solver jest narzędziem zewnętrznym, musimy umożliwić mu korzystanie z niego. Wykonaj poniższe kroki, aby go włączyć.

Krok 1: Przejdź do Narzędzia >>> Odniesienie w oknie Edytora Visual Basic.

Krok 2: Z listy odniesień wybierz „Solver” i kliknij OK, aby go użyć.

Teraz możemy używać Solvera również w VBA.

Funkcje solvera w języku VBA

Aby napisać kod VBA, musimy użyć trzech „Funkcji Solvera” w VBA, a te funkcje to „SolverOk, SolverAdd i SolverSolve”.

SolverOk

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

SetCell: będzie to odwołanie do komórki, które należy zmienić, np. Komórka zysku.

MaxMinVal: jest to parametr opcjonalny, poniżej znajdują się liczby i specyfikatory.

  • 1 = Maksymalizuj
  • 2 = Minimalizuj
  • 3 = Dopasuj określoną wartość

WartośćOf: ten parametr musi być podawany, jeśli argument MaxMinVal ma wartość 3.

JakZmień: Zmieniając komórki, które to równanie należy rozwiązać.

SolverAdd

Zobaczmy teraz parametry SolverAdd

CellRef: Aby ustawić kryteria rozwiązania problemu, należy zmienić komórkę.

Relacja: W tym przypadku, jeśli wartości logiczne są spełnione, możemy użyć poniższych liczb.

  • 1 to mniej niż (<=)
  • 2 jest równe (=)
  • 3 jest większe niż (> =)
  • 4 musi mieć końcowe wartości będące liczbami całkowitymi.
  • 5 musi mieć wartości z przedziału od 0 do 1.
  • 6 musi mieć końcowe wartości, które są różne i są liczbami całkowitymi.

Przykład dodatku Solver w Excel VBA

Możesz pobrać ten szablon VBA Solver Excel tutaj - szablon VBA Solver Excel

Na przykład spójrz na poniższy scenariusz.

Korzystając z tej tabeli, musimy określić kwotę „Zysk”, która musi wynosić co najmniej 10000. Aby otrzymać tę liczbę, musimy spełnić pewne warunki.

  • Jednostki do sprzedaży powinny być liczbą całkowitą.
  • Cena / jednostka powinna wynosić od 7 do 15.

Na podstawie tych warunków musimy określić, ile jednostek sprzedać za jaką cenę, aby uzyskać wartość zysku wynoszącą 10000.

Ok, rozwiążmy teraz to równanie.

Krok 1: Uruchom podprocedurę VBA.

Kod:

 Sub Solver_Example () End Sub

Krok 2: Najpierw musimy ustawić odwołanie do komórki celu za pomocą funkcji SolverOk .

Krok 3: Pierwszym argumentem tej funkcji jest „SetCell”, w tym przykładzie musimy zmienić wartość komórki Profit czyli komórki B8.

Kod:

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

Krok 4: Teraz musimy ustawić wartość tej komórki na 10000, więc dla MaxMinVal użyj 3 jako wartości argumentu.

Kod:

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

Krok 5: Następny argument o wartości ValueOf powinien wynosić 10000.

Kod:

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

Kolejnym argumentem jest ByChange, czyli poprzez zmianę komórek, w których to równanie ma zostać rozwiązane. W takim przypadku należy zmienić komórki Jednostki na Sprzedaj (B1) i Cena za jednostkę (B2).

Kod:

 Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") End Sub

Uwaga: pozostałe argumenty nie są tutaj wymagane.

Krok 6: Po ustawieniu komórki celu, teraz musimy skonstruować inne kryteria. W tym celu otwórz funkcję „SolverAdd”.

Krok 7: Pierwsza komórka, którą musimy zmienić, to Price Per Unit komórka, czyli komórka B2.

Kod:

 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.