VBA Solver - Contoh Langkah Demi Langkah untuk menggunakan Solver di Excel VBA

Daftar Isi

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 sadar, "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 terlebih dahulu, 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

Sebagai contoh, lihat skenario di bawah ini.

Dengan menggunakan tabel ini, kita perlu mengidentifikasi jumlah "Keuntungan", yang minimal harus 10.000. 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.

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, jalankan kode dengan menekan tombol F5 untuk mendapatkan hasilnya.

Saat Anda menjalankan kode, Anda akan melihat jendela berikut.

Tekan Ok dan Anda akan mendapatkan hasilnya dalam lembar excel.

Jadi untuk mendapatkan keuntungan 10.000, kita perlu menjual 5.000 unit dengan harga 7 per harga di mana harga biaya adalah 5.

Hal-hal untuk diingat

  • Untuk bekerja dengan Solver di excel & VBA, pertama, aktifkan untuk lembar kerja, lalu aktifkan untuk referensi VBA.
  • Setelah diaktifkan pada lembar kerja dan VBA, maka hanya kami yang dapat mengakses semua fungsi Solver.

Artikel yang menarik...