VBA Solver

Pemecah VBA Excel

Bagaimana Anda mengatasi masalah yang rumit? Jika Anda tidak yakin bagaimana mengatasi masalah ini maka tidak perlu khawatir, kami memiliki pemecah masalah di excel kami. Dalam artikel kami sebelumnya "Pemecah Excel", kami telah mempelajari cara menyelesaikan persamaan di excel. Jika Anda tidak tahu "SOLVER" juga tersedia dengan VBA. Pada artikel ini, kami akan memandu Anda tentang cara menggunakan "Solver" di VBA.

Aktifkan Solver di Lembar Kerja

Solver adalah alat tersembunyi yang tersedia di bawah tab data di excel (jika sudah diaktifkan).

Untuk menggunakan SOLVER di excel, pertama kita perlu mengaktifkan opsi ini. Ikuti langkah-langkah di bawah ini.

Langkah 1: Buka tab FILE. Di bawah tab FILE pilih "Options".

Langkah 2: Di jendela Opsi Excel pilih "Add-Ins".

Langkah 3: Di bagian bawah pilih "Excel Add-Ins" dan klik "Go".

Langkah 4: Sekarang centang kotak "Solver Add-in" dan klik Ok.

Sekarang Anda harus melihat "Solver" di bawah tab data.

Aktifkan Solver di VBA

Di VBA juga, Solver adalah alat eksternal, kita perlu mengaktifkannya untuk menggunakannya. Ikuti langkah-langkah di bawah ini untuk mengaktifkannya.

Langkah 1: Buka Alat >>> Referensi di Jendela Editor Visual Basic.

Langkah 2: Dari daftar referensi, pilih "Solver" dan klik Ok untuk menggunakannya.

Sekarang kita dapat menggunakan Solver di VBA juga.

Fungsi Solver di VBA

Untuk menulis kode VBA kita perlu menggunakan tiga "Fungsi Solver" di VBA dan fungsi tersebut adalah "SolverOk, SolverAdd, dan SolverSolve".

SolverOk

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

SetCell: Ini akan menjadi referensi sel yang perlu diubah yaitu sel Profit.

MaxMinVal: Ini adalah parameter opsional, di bawah ini adalah angka dan penentu .

  • 1 = Maksimalkan
  • 2 = Minimalkan
  • 3 = Cocokkan nilai tertentu

ValueOf: Parameter ini perlu disediakan jika argumen MaxMinVal adalah 3.

ByChange: Dengan mengubah sel mana persamaan ini perlu diselesaikan.

SolverAdd

Sekarang mari kita lihat parameter SolverAdd

CellRef: Untuk mengatur kriteria untuk memecahkan masalah sel apa yang perlu diubah.

Relasi: Dalam hal ini, jika nilai logika terpenuhi maka kita dapat menggunakan angka di bawah ini.

  • 1 lebih kecil dari (<=)
  • 2 sama dengan (=)
  • 3 lebih besar dari (> =)
  • 4 harus memiliki nilai akhir yaitu bilangan bulat.
  • 5 harus memiliki nilai antara 0 atau 1.
  • 6 harus memiliki nilai akhir yang semuanya berbeda dan bilangan bulat.

Contoh Solver di Excel VBA

Anda dapat mengunduh Template Excel Solver VBA ini di sini - Template Excel Solver VBA

Untuk contoh lihat skenario di bawah ini.

Dengan menggunakan tabel ini kita perlu mengidentifikasi jumlah "Keuntungan" yang harus minimal 10000. Untuk sampai pada angka ini, kita memiliki persyaratan tertentu.

  • Unit untuk Dijual harus berupa nilai integer.
  • Harga / Unit harus antara 7 dan 15.

Berdasarkan kondisi tersebut kita perlu mengidentifikasi berapa unit yang akan dijual dengan harga berapa untuk mendapatkan nilai keuntungan 10000.

Oke, ayo selesaikan persamaan ini sekarang.

Langkah 1: Mulai subprocedure VBA.

Kode:

 Sub Solver_Example () End Sub

Langkah 2: Pertama kita perlu mengatur referensi sel Objective dengan menggunakan fungsi SolverOk .

Langkah 3: Argumen pertama dari fungsi ini adalah "SetCell", dalam contoh ini kita perlu mengubah nilai sel Profit yaitu sel B8.

Kode:

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

Langkah 4: Sekarang kita perlu mengatur nilai sel ini menjadi 10000, jadi untuk MaxMinVal gunakan 3 sebagai nilai argumen.

Kode:

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

Langkah 5: Nilai ValueOf argumen berikutnya harus 10000.

Kode:

 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.