Risolutore VBA

Risolutore VBA di Excel

Come risolvi problemi complicati? Se non sei sicuro di come affrontare questi problemi, non preoccuparti, abbiamo risolutore nel nostro Excel. Nel nostro precedente articolo "Risolutore Excel" abbiamo imparato come risolvere le equazioni in Excel. Se non si è consapevoli che "SOLVER" è disponibile anche con VBA. In questo articolo, ti spiegheremo come utilizzare "Risolutore" in VBA.

Abilita Risolutore nel foglio di lavoro

Un risolutore è uno strumento nascosto disponibile nella scheda dati in Excel (se già abilitato).

Per utilizzare SOLVER in Excel, dobbiamo prima abilitare questa opzione. Segui i passaggi seguenti.

Passaggio 1: vai alla scheda FILE. Nella scheda FILE scegli "Opzioni".

Passaggio 2: nella finestra Opzioni di Excel selezionare "Componenti aggiuntivi".

Passaggio 3: in basso, seleziona "Componenti aggiuntivi di Excel" e fai clic su "Vai".

Passaggio 4: Ora seleziona la casella "Componente aggiuntivo Risolutore" e fai clic su OK.

Ora devi vedere "Risolutore" nella scheda dati.

Abilita Risolutore in VBA

Anche in VBA, Risolutore è uno strumento esterno, dobbiamo abilitarlo per usarlo. Segui i passaggi seguenti per abilitarlo.

Passaggio 1: vai su Strumenti >>> Riferimento nella finestra di Visual Basic Editor.

Passaggio 2: dall'elenco dei riferimenti, scegliere "Risolutore" e fare clic su OK per utilizzarlo.

Ora possiamo usare Risolutore anche in VBA.

Funzioni del risolutore in VBA

Per scrivere un codice VBA abbiamo bisogno di utilizzare tre "Solver Functions" in VBA e queste funzioni sono "SolverOk, SolverAdd e SolverSolve".

SolverOk

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

SetCell: questo sarà il riferimento della cella che deve essere modificato, ad esempio la cella Profit.

MaxMinVal: questo è un parametro facoltativo, di seguito sono riportati i numeri e gli specificatori.

  • 1 = Massimizza
  • 2 = Riduci a icona
  • 3 = Corrisponde a un valore specifico

ValueOf: questo parametro deve essere fornito se l' argomento MaxMinVal è 3.

ByChange: cambiando le celle questa equazione deve essere risolta.

RisolutoreAdd

Vediamo ora i parametri di SolverAdd

CellRef: per impostare i criteri per risolvere il problema, è necessario modificare la cella.

Relazione: in questo caso, se i valori logici sono soddisfatti, possiamo utilizzare i numeri seguenti.

  • 1 è minore di (<=)
  • 2 è uguale a (=)
  • 3 è maggiore di (> =)
  • 4 deve avere valori finali interi.
  • 5 deve avere valori compresi tra 0 o 1.
  • 6 deve avere valori finali tutti diversi e numeri interi.

Esempio di Risolutore in Excel VBA

Puoi scaricare questo modello Excel Risolutore VBA qui - Modello Excel Risolutore VBA

Per un esempio, guarda lo scenario seguente.

Utilizzando questa tabella dobbiamo identificare l'importo "Profitto" che deve essere un minimo di 10000. Per arrivare a questo numero abbiamo determinate condizioni.

  • Le unità da vendere dovrebbero essere un valore intero.
  • Il prezzo / unità dovrebbe essere compreso tra 7 e 15.

Sulla base di queste condizioni dobbiamo identificare quante unità vendere a quale prezzo per ottenere il valore di profitto di 10000.

Ok, risolviamo ora questa equazione.

Passaggio 1: avviare la sottoprocedura VBA.

Codice:

 Sub Solver_Example () End Sub

Passaggio 2: per prima cosa dobbiamo impostare il riferimento di cella Obiettivo utilizzando la funzione SolverOk .

Passaggio 3: il primo argomento di questa funzione è "SetCell", in questo esempio dobbiamo modificare il valore della cella Profit, ovvero la cella B8.

Codice:

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

Passaggio 4: ora è necessario impostare il valore di questa cella su 10000, quindi per MaxMinVal utilizzare 3 come valore dell'argomento.

Codice:

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

Passaggio 5: il valore ValueOf dell'argomento successivo dovrebbe essere 10000.

Codice:

 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.