[VB6] Utiliser le solver d'Excel

**Peltchag** · 26/10/2005, 09h53

Bonjour

je fais une application sous vb6 qui me génère des systèmes d'équations. Je souhaite à présent les résoudre grâce au solver d'Excel par la méthode des moindres carrés.
j'ai donc placé la référence à Excel, et généré mon document comme il faut (je vérifie à chaque fois en enregistrant le document), il ne me reste plus qu'à exécuter le solver, mais je n'y arrive pas

j'ai essayé en copiant le code créé par les enregistreurs de macros pour ajouter la référence au solver, et ca marche. En revanche, quand je copie le code de la macro d'exécution du solver, j'obtiens un message d'erreur

code de déclaration des variables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim ExcelApp As Excel.Application
Dim Classeur As Excel.Workbook
Dim Feuille As Excel.Worksheet
Dim Plage As Excel.Range
 
'nouvelle instance d'application Excel
Set ExcelApp = New Excel.Application
'ne pas afficher d'alertes
ExcelApp.DisplayAlerts = False
 
'nouveau classeur
Set Classeur = ExcelApp.Workbooks.Add(xlWBATWorksheet)
'sélectionner la feuille
Set Feuille = ExcelApp.Worksheets(1)
Feuille.Name = "CMP"

ce code fonctionne bien

code pour ajouter la référence au solver :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ExcelApp.AddIns("Solver Add-in").Installed = True

ce code fonctionne bien aussi (à chaque fois j'enregistre le fichier grâce à la méthode "SaveAs" et je vérifie), j'ai bien "Solver Add-In" de cocher dans les options de macros complémentaires (menu outils -> macros complémentaires)

par contre, quand je copie le code de la macro d'exécution du solver :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverAdd CellRef:="$A$1", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$B$1", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverSolve

j'obtiens une erreur de compilation lors de l'exécution de la feuille qui contient ce code :

Procédure Sub ou Function non définie

l'erreur se trouve dès la première ligne, ce qui me fait bien penser que le solver n'est pas reconnu

Comment faire donc pour pouvoir "piloter" le solver d'Excel depuis VB6 ?

j'ai essayé de donner un maximum de détails, mais si quelque chose n'est pas clair, n'hésitez pas à me le faire savoir

merci pour votre aide !

**ouskel'n'or** · 26/10/2005, 10h01

Hello,

Pour cette ligne,

SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"

J'ai trouvé cette syntaxe dans l'aide VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SolverOK setCell:=Range("BénéficeTotal"), maxMinVal:=1, byChange:=Range("C4:E6")

Si ça peut te donner une indication...

A+

**ouskel'n'or** · 26/10/2005, 10h12

Tant qu'à faire, j'ai aussi ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SolverLoad loadArea:=Range("A33:A38")
SolverChange cellRef:=Range("F4:F6"), relation:=1, formulaText:=200
SolverAdd cellRef:=Range("F4:F6"),relation:=1, formulaText:=100
SolverDelete cellRef:=Range("C4:E6"), relation:=4
SolverSolve userFinish:=False

A toutes fins utiles

**Peltchag** · 26/10/2005, 10h19

merci de ton intervention

Mais le problème n'est pas un problème de syntaxe je pense, c'est plutôt que VB ne connait pas le solver d'excel, et n'arrives donc pas à le "piloter".
Je cherche donc à déclarer le solver comment étant une macro complémentaire d'Excel pour pouvoir l'utiliser depuis mon code sous VB6

**ouskel'n'or** · 26/10/2005, 10h35

Ok, je dois avoir ça quelque part...

A tout'

**ouskel'n'or** · 26/10/2005, 10h40

En attendant, j'ai ça dans l'aide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set a = AddIns("Macro complémentaire Solveur")

A+

**ouskel'n'or** · 26/10/2005, 10h44

Voilà, si ça peut te convenir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AddIns("Conversion_Francs_Euros.xla").Installed = True

A+

**Peltchag** · 26/10/2005, 10h53

je viens d'essayer ca :

j'ai rajouter dans ma partie déclarative :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Dim SolverExcel As Object

puis dans mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Set SolverExcel = AddIns("Solver Add-in")
Call SolverExcel.solverok("$G$3", 2, "0", "$A$1:$B$1")

résultat : erreur à la compilation sur la 2eme ligne -> l'objet ne gère pas cette méthode

mais ca doit s'approcher de ca

je me demande ici, s'il n'est pas possible de faire quelque chose avec la DLL ou le .XLA du solver (chez moi, c'est dans le dossier d'Office : Office10\Library\Solver). Peut être que la DLL fournit des API ? comment vérifier ca ?

en tout cas, merci pour l'aide

**ouskel'n'or** · 26/10/2005, 11h22

Oui, je me doute que ce que je fais en VBA doit être adapté au VB mais là, je ne peux pas t'aider.
Cependant, pourquoi déclares-tu "SolverExcel As Object" ?
Ne serait-ce pas à l'appli de s'en occuper ? "Un truc dans le genre" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
With appliExcel '???? tu vois pour la syntaxe (!)
      Set SolverExcel = AddIns("Solver Add-in") 
      Call SolverExcel.solverok("$G$3", 2, "0", "$A$1:$B$1")
end with

Vraiment à tout hasard...

A+

**Peltchag** · 26/10/2005, 11h34

je declare SolverExcel en tant qu'objet, parce qu'il n'y a pas de type Solver

mais je sais que ca sera un objet, donc je le declare en tant qu'objet

j'ai bien pense a ta methode, mais le soucis, c'est que la fonction AddIns renvoi un objet AddIn, et que, apres avoir regarde dans la doc, je ne vois pas ce que je vais pouvoir faire de cet objet, qui ne sert qu'a donner des infos sur le composant

j'ai pense a autre chose :
si j'enregistrais la macro de solver dans un module vba, est ce qu'apres, je pourrais rattacher, via du code, ce module a mon fichier excel pour executer les macros qui sont dedans ?

**ouskel'n'or** · 26/10/2005, 11h49

Oui, "il peut le faire !"

Tu places ton code dans

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub Workbook_Open()
'...Ton code
End sub

A l'ouverture du .xls, il exécutera tout ce que tu voudras...

A+

**Peltchag** · 26/10/2005, 13h26

oui, mais...

je répète ce que je compte faire pour y arriver, histoire qu'on soit sûr de se comprendre :
- sous VB, je génère mon système d'équations
- je créé une instance d'Excel
- je remplis la feuille avec le système d'équations

ce que je voudrais faire :
- toujours sous VB, rattaché un module VBA (enregistré sur le disque dur), à l'instance d'Excel
- lancer la macro de ce module pour qu'elle traite le système d'équations de la feuille Excel

si j'arrive à faire ça, je pourrais sans problème récupéré les solutions après

je ne pense donc pas que ce que tu m'as donné puisse marcher, on devait s'être mal compris

**ouskel'n'or** · 26/10/2005, 14h21

Ok,

Dans ce que tu veux faire, je n'ai pas bien compris ça :

- toujours sous VB, rattaché un module VBA (enregistré sur le disque dur), à l'instance d'Excel

ou si j'ai compris, je ne sais pas faire ça tel quel...

J'ai vu passé, il y a un mois ou deux, un post sur une demande d'un xla créant un objet application Excel (c'était du moins la volonté du "posteur") et je crois bien qu'il avait trouvé une solution (sinon détourné le pb) qui lui permettait de faire "ce qu'il souhaitait" depuis VB créant une instance Excel (!) La question est : Est-ce le même pb ?

Ce que je sais faire : ajouter une xla dans Excel et l'activer, placer la sub qui fait ça dans une procédure lancée à l'ouverture du fichier... Ce qui ne paraît pas du tout résoudre ton pb.

Je laisse donc la main... Si le "posteur" en question passe par là, il saura te répondre mieux que moi.

Bonne chance

A+

**Peltchag** · 26/10/2005, 14h56

En fait, je voulais créer un module VBA contenant la macro du solver, et pour chaque système à résoudre, créer une instance Excel, et ajouté ce module à l'instance. Mais bon, passons car j'ai fait plus simple.

J'ai reussi a faire ce que je voulais faire :

-j'ai créé un fichier excel vide, dans lequel j'ai créé la macro exécutant le solver
- sous VB, je génère mon système d'équations
- ensuite, j'ouvre mon fichier excel contenant la macro
- je remplis le fichier excel avec le système
- je lance la macro
- je récupère les résultats

MAIS (il y a toujours un mais...

), il y a un hic :
au moment de lancer la macro, j'obtiens un message d'erreur de l'instance Excel :

Solver : An unexpected internal error occured, or available memory was exhaused.

J'ai regarde le gestionnaire des taches, et Excel n'a pas mange toute la memoire (la memoire occupee max est faible), donc je ne pense pas que ce soit ca qui cloche. Mais je ne vois pas quoi d'autre non plus, car j'ai copié la macro qui marche !

petit à petit, je vais y arriver !!!!

**ouskel'n'or** · 26/10/2005, 15h25

Que fait ta macro ? Juste activer la macro complémentaire ?

A+

**Peltchag** · 26/10/2005, 15h45

ma macro est dans un module :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub SolverMoindresCarres()
        SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
        SolverAdd CellRef:="$A$1", Relation:=3, FormulaText:="0"
        SolverAdd CellRef:="$B$1", Relation:=3, FormulaText:="0"
        SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
        SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
        SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
        SolverSolve
End Sub

j'ai essayé, dans mon code VB de remplacer l'appel de la procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Call ExcelApp.Run("SolverMoindresCarres")

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Call ExcelApp.ExecuteExcel4Macro("SolverMoindresCarres")

ca ne fait plus d'erreur, mais ca ne semble pas exécuter la macro

**ouskel'n'or** · 26/10/2005, 16h06

Oui, je crois que tu dois désigner le nom du classeur...

mais avec VB...

:

A+

**Peltchag** · 27/10/2005, 09h00

bonjour

j'ai essayé pas mal de choses, et à chaque fois, j'obtiens l'erreur "impossible de trouver la macro"

donc, j'ai mon fichier excel, qui ne possede qu'une seuille feuille, nommée CMP. Sous l'éditeur VBA du fichier, j'ai aussi renommé la feuille de code en CMP.

j'ai essayé de lancer la macro depuis mon appli VB en faisant un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Run("CMP!SolverMoindresCarres")

j'obtiens le message :

Impossible de trouver la macro 'CMP!SolverMoindresCarres'

j'ai ensuite essayé de placer la macro dans un module nommé MdlSolver, je l'appelle depuis VB en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Run("Solver.xls!SolverMoindresCarres")

j'obtiens le message :

Solver : An unexpected internal error occured, or available memory was exhausted

je pense donc que ma deuxième méthode est la bonne pour l'appel, mais comme la macro est dans un module, elle ne sait pas sur quelle feuille elle doit agir (même s'il y en a qu'une !), donc elle plante, c'est "logique"...

j'essaye donc de combiner les 2 méthodes :
je place ma macro dans le code de la feuille CMP, et je l'appelle depuis VB en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Run("Solver.xls!CMP!SolverMoindresCarres")

j'obtiens le message :

Impossible de trouver la macro 'Solver.xls!CMP!SolverMoindresCarres'

bon, il est 8h50, je me dis qu'il faut rester calme, il fait beau, faut etre zen

bref je continue mes recherches, et j'ai la bonne idée de créer une nouvelle macro, à partir de l'enregistreur de macro, qui lance la macro, histoire de voir la syntaxe. J'obtiens le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Run("Solver.xls!CMP.SolverMoindresCarres")

je mets ca dans VB, je lance, j'obtiens :

Solver : An unexpected internal error occured, or available memory was exhausted

et là, je ne vois vraiment pas pourquoi

bref, je m'en remets à vous

PS : le code de la macro n'a pas changé

**Peltchag** · 27/10/2005, 15h31

Une fois la résolution effectuée, le solveur affichait une boîte de dialoge demandant si on veut garder les résultats.
Comme j'instancie l'application, mais je ne la rends pas visible, j'ai pensé que ca pouvait venir de là, j'ai donc essayé en rendant l'application visible, ça ne change rien, j'ai aussi modifié la macro, mais ça ne change pas plus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Public Sub SolverMoindresCarres()
    SolverReset
    SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
    SolverAdd CellRef:="$A$1", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$B$1", Relation:=3, FormulaText:="0"
    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
    SolverOk SetCell:="$G$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
End Sub

ça me dépasse vraiment là, même si mes compétences de développeurs sont encore relativement maigres, je n'arrive vraiment pas à saisir d'où vient le problème

toute aide est vraiment la bienvenue, car je commence à épuiser mon stock d'idées