Risolutore VBA - Esempio passo dopo passo per utilizzare Risolutore in Excel VBA

Risolutore VBA di Excel

Come risolvi problemi complicati? Se non sei sicuro di come affrontare questi problemi, niente di cui preoccuparti, abbiamo risolutore nel nostro Excel. Nel nostro precedente articolo "Risolutore Excel" abbiamo imparato come risolvere le equazioni in Excel. Se non sei a conoscenza, "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 prima SOLVER in Excel, è necessario 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 di cella che deve essere modificato, ad esempio, 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, qual è la cella deve essere modificata.

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

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.

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, esegui il codice premendo il tasto F5 per ottenere il risultato.

Quando esegui il codice, vedrai la seguente finestra.

Premi Ok e otterrai il risultato in un foglio Excel.

Quindi, per ottenere un profitto di 10000, dobbiamo vendere 5000 unità a 7 per prezzo, dove il prezzo di costo è 5.

Cose da ricordare

  • Per lavorare con Risolutore in Excel e VBA, prima abilitalo per il foglio di lavoro, quindi abilita per riferimento VBA.
  • Una volta abilitato su entrambi i fogli di lavoro e VBA, solo noi possiamo accedere a tutte le funzioni del Risolutore.

Articoli interessanti...