Solveur VBA

Solveur Excel VBA

Comment résolvez-vous des problèmes complexes? Si vous ne savez pas comment résoudre ces problèmes, rien d'inquiétant, nous avons un solveur dans notre excellent. Dans notre article précédent «Excel Solver», nous avons appris à résoudre des équations dans Excel. Si vous ne savez pas que «SOLVER» est également disponible avec VBA. Dans cet article, nous allons vous expliquer comment utiliser «Solver» dans VBA.

Activer le solveur dans la feuille de calcul

Un solveur est un outil caché disponible sous l'onglet données dans Excel (s'il est déjà activé).

Pour utiliser SOLVER dans Excel, nous devons d'abord activer cette option. Suivez les étapes ci-dessous.

Étape 1: Accédez à l'onglet FICHIER. Sous l'onglet FICHIER, choisissez «Options».

Étape 2: Dans la fenêtre Options Excel, choisissez «Compléments».

Étape 3: En bas, choisissez «Excel Add-Ins» et cliquez sur «Go».

Étape 4: Cochez maintenant la case «Solver Add-in» et cliquez sur Ok.

Vous devez maintenant voir «Solveur» sous l'onglet de données.

Activer le solveur dans VBA

Dans VBA aussi, Solver est un outil externe, nous devons lui permettre de l'utiliser. Suivez les étapes ci-dessous pour l'activer.

Étape 1: Accédez à Outils >>> Référence dans la fenêtre Visual Basic Editor.

Étape 2: Dans la liste des références, choisissez «Solveur» et cliquez sur OK pour l'utiliser.

Maintenant, nous pouvons également utiliser Solver dans VBA.

Fonctions du solveur dans VBA

Pour écrire un code VBA, nous devons utiliser trois «fonctions de solveur» dans VBA et ces fonctions sont «SolverOk, SolverAdd et SolverSolve».

SolverOk

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

SetCell: Ce sera la référence de la cellule qui doit être modifiée, c'est-à-dire la cellule Profit.

MaxMinVal: Ceci est un paramètre facultatif, ci-dessous sont des nombres et des spécificateurs.

  • 1 = Agrandir
  • 2 = minimiser
  • 3 = Correspond à une valeur spécifique

ValueOf: ce paramètre doit indiquer si l' argument MaxMinVal est 3.

ByChange: En modifiant les cellules, cette équation doit être résolue.

SolverAdd

Voyons maintenant les paramètres de SolverAdd

CellRef: Pour définir les critères de résolution du problème, la cellule doit être modifiée.

Relation: En cela, si les valeurs logiques sont satisfaites, nous pouvons utiliser les nombres ci-dessous.

  • 1 est inférieur à (<=)
  • 2 est égal à (=)
  • 3 est supérieur à (> =)
  • 4 doit avoir des valeurs finales qui sont des entiers.
  • 5 doit avoir des valeurs comprises entre 0 et 1.
  • 6 doit avoir des valeurs finales toutes différentes et des nombres entiers.

Exemple de solveur dans Excel VBA

Vous pouvez télécharger ce modèle Excel de solveur VBA ici - Modèle Excel de solveur VBA

Pour un exemple, regardez le scénario ci-dessous.

En utilisant ce tableau, nous devons identifier le montant «Profit» qui doit être au minimum de 10 000. Pour arriver à ce nombre, nous avons certaines conditions.

  • Les unités à vendre doivent être une valeur entière.
  • Le prix / unité doit être compris entre 7 et 15.

Sur la base de ces conditions, nous devons identifier le nombre d'unités à vendre à quel prix pour obtenir la valeur de profit de 10000.

Ok, résolvons cette équation maintenant.

Étape 1: Démarrez la sous-procédure VBA.

Code:

 Sub Solver_Example () End Sub

Étape 2: Nous devons d'abord définir la référence de cellule Objective à l'aide de la fonction SolverOk .

Étape 3: Le premier argument de cette fonction est «SetCell», dans cet exemple, nous devons changer la valeur de la cellule Profit, c'est-à-dire la cellule B8.

Code:

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

Étape 4: Nous devons maintenant définir cette valeur de cellule sur 10000, donc pour MaxMinVal, utilisez 3 comme valeur d'argument.

Code:

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

Étape 5: La valeur ValueOf de l' argument suivant doit être 10000.

Code:

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

L'argument suivant est ByChange, c'est-à-dire en changeant quelles cellules cette équation doit être résolue. Dans ce cas, en changeant les unités en vente (B1) et la cellule Prix par unité (B2) doit être modifiée.

Code:

 Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") End Sub

Remarque: les arguments restants ne sont pas requis ici.

Étape 6: Une fois la cellule d'objectif définie, nous devons maintenant construire d'autres critères. Pour cette fonction ouverte «SolverAdd».

Étape 7: La première référence de cellule que nous devons modifier est la cellule Prix par unité, c'est-à-dire la cellule B2.

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.