VBA Solver - Exemple étape par étape pour utiliser le solveur dans Excel VBA

Table des matières

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 le savez pas, «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 en premier, nous devons 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 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 changeant 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, quelle est 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

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

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, exécutez le code en appuyant sur la touche F5 pour obtenir le résultat.

Lorsque vous exécutez le code, vous verrez la fenêtre suivante.

Appuyez sur Ok et vous obtiendrez le résultat dans une feuille Excel.

Donc, pour réaliser un bénéfice de 10000, nous devons vendre 5000 unités à 7 par prix où le prix de revient est de 5.

Choses dont il faut se rappeler

  • Pour travailler avec Solver dans Excel et VBA, activez-le d'abord pour la feuille de calcul, puis activez-le pour la référence VBA.
  • Une fois qu'il est activé sur les feuilles de calcul et VBA, nous pouvons seulement accéder à toutes les fonctions du solveur.

Articles intéressants...