Penyelesai VBA

Penyelesai Excel VBA

Bagaimana anda menyelesaikan masalah yang rumit? Sekiranya anda tidak pasti bagaimana menyelesaikan masalah ini, maka tidak perlu risau kami mempunyai penyelesaian dalam prestasi kami. Dalam artikel sebelumnya "Excel Solver" kami telah belajar bagaimana menyelesaikan persamaan dalam excel. Sekiranya anda tidak sedar "SOLVER" juga tersedia dengan VBA. Dalam artikel ini, kami akan membimbing anda bagaimana menggunakan "Solver" di VBA.

Dayakan Penyelesai dalam Lembaran Kerja

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

Untuk menggunakan SOLVER dalam excel terlebih dahulu kita perlu mengaktifkan pilihan ini. Ikuti langkah di bawah.

Langkah 1: Pergi ke tab FILE. Di bawah tab FILE pilih "Pilihan".

Langkah 2: Di tetingkap Pilihan Excel pilih "Tambah".

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

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

Sekarang anda mesti melihat "Penyelesai" di bawah tab data.

Dayakan Penyelesai dalam VBA

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

Langkah 1: Pergi ke Alat >>> Rujukan di Tetingkap Visual Basic Editor.

Langkah 2: Dari senarai rujukan, pilih "Penyelesai" dan klik Ok untuk menggunakannya.

Sekarang kita boleh menggunakan Solver di VBA juga.

Fungsi Penyelesai dalam VBA

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

SolverOk

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

SetCell: Ini akan menjadi rujukan sel yang perlu diubah iaitu Profit cell.

MaxMinVal: Ini adalah parameter pilihan, di bawah adalah nombor dan penentu.

  • 1 = Maksimumkan
  • 2 = Kurangkan
  • 3 = Padankan nilai tertentu

ValueOf: Parameter ini perlu dibekalkan sekiranya argumen MaxMinVal adalah 3.

ByChange: Dengan mengubah sel mana persamaan ini perlu diselesaikan.

PenyelesaianAdd

Sekarang mari kita lihat parameter SolverAdd

CellRef: Untuk menetapkan kriteria untuk menyelesaikan masalah apa sel perlu diubah.

Perkaitan: Dalam hal ini, jika nilai logik dipenuhi maka kita boleh menggunakan nombor di bawah.

  • 1 kurang daripada (<=)
  • 2 sama dengan (=)
  • 3 lebih besar daripada (> =)
  • 4 mesti mempunyai nilai akhir yang berupa bilangan bulat.
  • 5 mesti mempunyai nilai antara 0 atau 1.
  • 6 mesti mempunyai nilai akhir yang semuanya berbeza dan integer.

Contoh Penyelesai dalam Excel VBA

Anda boleh memuat turun Templat VBA Solver Excel ini di sini - Templat VBA Solver Excel

Sebagai contoh lihat senario di bawah.

Dengan menggunakan jadual ini, kita perlu mengenal pasti jumlah "Keuntungan" yang minimum 10000. Untuk mencapai nombor ini, kita mempunyai syarat tertentu.

  • Unit untuk Dijual mestilah nilai integer.
  • Harga / Unit hendaklah antara 7 dan 15.

Berdasarkan syarat-syarat ini, kita perlu mengenal pasti berapa unit yang hendak dijual dengan harga berapa untuk mendapatkan nilai keuntungan 10000.

Ok, mari selesaikan persamaan ini sekarang.

Langkah 1: Mulakan subprosedur VBA.

Kod:

 Sub Solver_Contoh () Sub Akhir

Langkah 2: Pertama kita perlu menetapkan rujukan sel Objektif dengan menggunakan fungsi SolverOk .

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

Kod:

 Sub Solver_Contoh () SolverOk SetCell: = Julat ("B8") Sub Akhir

Langkah 4: Sekarang kita perlu menetapkan nilai sel ini ke 10000, jadi untuk MaxMinVal menggunakan 3 sebagai nilai argumen.

Kod:

 Sub Solver_Contoh () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3 Sub Akhir

Langkah 5: Nilai ValueOf argumen seterusnya hendaklah 10000.

Kod:

 Sub Solver_Contoh () 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.