Soucis de compilation (macro excel)

**Quentin78** · 10/04/2017, 20h50

Bonjour à tous.
Je suis nouveau sur votre forum, mais on m'en a dit beaucoup de bien.

Je code en ce moment une macro sur excel, et je rencontre des difficultés.
J'avais déjà fait ce code en C++ sur visual studio, il fonctionne bien, mais j'ai besoin d'excel aujourd'hui pour continuer.
Mon programme aujourd'hui est le suivant :

J'ai 18 farines, chacune a un certain pourcentage de protéines, de lipides et de glucides.
Je veux aussi un valeur maxi pour la farine. En gros, dans mon mélange, je ne veux pas qu'il y ai plus de 200 grammes de farine A (par exemple).

On obtient alors un tableau de ce style :
Farine A : 0.8 | 0.1 | 0.1 | 2000.8 étant 80% de protéines, 0.1 étant 10% de lipides puis de glucides, 200 la quantité max de cette farine.

J'ai donc un tableau de 18 farines définies de cette façon.

Je rentre ensuite dans un autre tableau les valeurs que je recherche, à savoir la quantité mini et maxi de protéines, de lipides et de glucides. Par exemple :

Prot : 200 a 300

Lipides : 400 a 500

Glucides : 400 a 450

jusqu'à là, tout va bien.
Maintenant je demande au programme de tester des mélanges de farine, et de me dire si un mélange peut fonctionner. Pour cela je fais :

1) Je fais 3 boucles for, comme ceci :

Code :

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

1
2
3
for prot = prot_mini (200 dans notre cas) to prot_maxi (300 ici)
   for lipides = lipides_mini (400 dans notre cas) to lipides maxi (500)
     for glucides = glucides_mini (400) to glucides_maxi (450)

Ce que je cherche, c'est obtenir 1 kilo de farine, je fais donc un if :

Code :

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

 if prot+lipides+glucides = 1000 then

Jusqu'ici, ça va encore !

2) C'est maintenant que les problèmes arrivent.
Pour effectuer tous les mélanges possibles de farine, j'ai pensé à la solution suivante :
J'ai 18 farines, j'ai donc dans mon tableau :
Etc....

Farine de blé

Farine de mais

Farine de soja

Farine de machin

Farine de truc

Farine de bidule

Etc....

J'ai alors fait 5 boucles for, comme je cherche des mélanges de 5 farines maxi.
J'obtiens :

Code :

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

1
2
3
4
5
For A = 1 to 14
  For B = 2 to 15
    For C = 3 to 16
      For D = 4 to 17
         For E = 5 to 18

De cette façon, si on fait du pas à pas, on obtient dans notre exemple :

Farine de blé

Farine de mais

Farine de soja

Farine de machin

Et la 5eme farine prendra la valeur de la ligne 5 à 18.
Quand E vaudra 5, on aura le mélange avec farine de truc, quand E vaudra 6 on aura farine de bidule, etc jusqu'à la 18eme farine.
Puis une fois à la 18eme farine, alors on gardera les 3 premières farine, blé mais soja, et la farine de machin deviendra la farine suivante, puis ont recommencera avec le E.

Je pense qu'ainsi je testerais tous les mélanges possibles, du moins c'est la meilleure solution que je vois.

3) Si je ne vous ai pas perdu, on passe à la 3eme étape
Maintenant que l'on a tous les mélanges de farine possible, je veux tester chaque mélange "gramme par gramme".
Par exemple dans notre mélange :

Farine de blé

Farine de mais

Farine de soja

Farine de machin

Farine de truc

Je veux voir entre 0 et la valeur maxi de chaque farine si un mélange peut fonctionner. J'aurais par exemple :
Farine de blé de 0 à 200 grammes
Farine de mais de 0 à 300 grammes
Farine de soja de 0 à 100 grammes
Farine de machin de 0 à 400 grammes
Farine de truc de 0 à 200 grammes

Il se peut par exemple que pour 200g de blé, 200g de mais, 200g de soja , 200g de machin, 200g de truc j'obtienne une valeur de prot comprise entre la mini et la maxi du début, pareil pour les lipides, pareil pour les glucides.
Je fais donc :

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
16
17
18
19
 
while i <= blé_max 
   while j <= mais_max
     while k<= soja_max
       while l<= machin_max
          while m<=truc_max 
 
        if (i*prot_blé + j*prot_mais + k*prot_soja + .....) and (i*lipides_blé + j*lipides_mais + k * lipides_soja + ....) and (i*glucides_blé + j * glucides_mais + ...) 
then "Ca marche !"
 
        m=m+10
     wend
    l=l+10
   wend 
   k=k+10
  wend
 j=j+10
wend
i=i+10

(+10 à chaque fois, je ne suis pas à 1 gramme près dans le mélange)

J'ignore si l'on peut rendre ce programme plus rapide, plus logique, plus ceci ou cela, ce que je voudrais déjà c'est le faire marcher.
A moins que quelque chose vous ai choqué, pour moi jusqu'ici mon programme est "logique", il doit fonctionner. Mais ce n'est pas le cas. Il ne me trouve aucun mélange possible.
Alors que pour les mêmes valeurs d'entrée, mon programme en C++ me trouvait pas moins de 6000 solutions...

Je vous met le code entier ici, peut-être une erreur se balade dedans, perso je n'ai rien vu.
Je vous mets aussi un lien onedrive pour accéder à mon classeur excel, avec les tableaux que j'utilise pour le code.

https://1drv.ms/x/s!As_1dT1GCGFznVu_kuBwPaaurii0

Je compte sur vous pour trouver comment me débloquer !
Merci d'avance.

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
Sub code()
 
'Je récupère les valeurs mini et maxi que je recherche'
prot_mini = Feuil2.Cells(5, 9).Value
prot_maxi = Feuil2.Cells(5, 10).Value
 
lipides_mini = Feuil2.Cells(6, 9).Value
lipides_maxi = Feuil2.Cells(6, 10).Value
 
glucides_mini = Feuil2.Cells(7, 9).Value
glucides_maxi = Feuil2.Cells(7, 10).Value
 
'je définie un pas pour mes boucles, dans l'exemple donné je faisais 10 par 10, mais je peux très bien faire 50 par 50'
pas = Feuil2.Cells(5, 6).Value
 
test = 10
 
 
'Première boucles pour trouver un mélange faisait 1000grammes
For prot = prot_mini To prot_maxi
    For lipides = lipides_mini To lipides_maxi
        For glucides = glucides_mini To glucides_maxi
 
            'Comme jutilise des oeufs dans le mélange, ils ont une certaine quantité de prot/lipides/glucides
            'Je noublis pas de les rajouter dans mon if, comme je les ai retiré juste avant. (Ce n'est qu'un détail)
            If prot + lipides + glucides + Feuil2.Cells(9, 4) * Feuil2.Cells(11, 3) + Feuil2.Cells(9, 4) * Feuil2.Cells(11, 4) + Feuil2.Cells(9, 4) * Feuil2.Cells(11, 5) = 1000 Then
                 Feuil2.Cells(1, 1).Value = "ok"
 
            'Mes boucles for, pour passer d'une ligne à lautre
             For ligne1 = 2 To 15 'farine 1
                 For ligne2 = ligne1 To 16 'farine 2
                     For ligne3 = ligne2 To 17 'farine 3
                        For ligne4 = ligne3 To 18 'farine 4
                            For ligne5 = ligne4 To 19 'farine 5
 
                               'Les boucles pour chercher "gramme par gramme"
                                While i <= Feuil1.Cells(ligne1, 5).Value 'Farine 1, de 0 à sa valeur maxi stockée dans le tableau
                                    While j <= Feuil1.Cells(ligne2, 5).Value 'farine 2 de 0 à sa valeur maxi
                                        While k <= Feuil1.Cells(ligne3, 5).Value 'farine 3 de 0 à sa valeur maxi
                                            While l <= Feuil1.Cells(ligne4, 5).Value 'farine 4 de 0 à sa valeur maxi
                                                While m <= Feuil1.Cells(ligne5, 5).Value 'farine 5 de 0 à sa valeur maxi
 
 
                                                        'Si on tombe sur une valeur de prot/lipides/glucides que l'ont recherche, alors
                                                        If ((i * Feuil1.Cells(ligne1, 2).Value + j * Feuil1.Cells(ligne2, 2).Value + k * Feuil1.Cells(ligne3, 2).Value + l * Feuil1.Cells(ligne4, 2).Value + m * Feuil1.Cells(ligne5, 2).Value) = prot And (i * Feuil1.Cells(ligne1, 3).Value + j * Feuil1.Cells(ligne2, 3).Value + k * Feuil1.Cells(ligne3, 3).Value + l * Feuil1.Cells(ligne4, 3).Value + m * Feuil1.Cells(ligne5, 3).Value) = lipides And (i * Feuil1.Cells(ligne1, 4).Value + j * Feuil1.Cells(ligne2, 4).Value + k * Feuil1.Cells(ligne3, 4).Value + l * Feuil1.Cells(ligne4, 4).Value + m * Feuil1.Cells(ligne5, 4).Value) = glucides) Then
 
 
                                                        'On affiche le nom de chaque farine (qui se trouve dans le tableau d'origine)
                                                        Feuil2.Cells(test, 10).Value = Feuil1.Cells(ligne, 1).Value 'farine 1
                                                        Feuil2.Cells(test + 1, 10).Value = Feuil1.Cells(ligne + 1, 1).Value 'farine 2
                                                        Feuil2.Cells(test + 2, 10).Value = Feuil1.Cells(ligne + 2, 1).Value 'farine 3
                                                        Feuil2.Cells(test + 3, 10).Value = Feuil1.Cells(ligne + 3, 1).Value 'farine 4
                                                        Feuil2.Cells(test + 4, 10).Value = Feuil1.Cells(ligne + 4, 1).Value 'farine 5
 
                                                        'Puis à coté on affiche la quantité de gramme de chaque farine
                                                        Feuil2.Cells(test, 11).Value = i ' nombre de grammes de farine 1
                                                        Feuil2.Cells(test + 1, 11).Value = j ' nombre de grammes de farine 2
                                                        Feuil2.Cells(test + 2, 11).Value = k ' nombre de grammes de farine 3
                                                        Feuil2.Cells(test + 3, 11).Value = l ' nombre de grammes de farine 4
                                                        Feuil2.Cells(test + 4, 11).Value = m ' nombre de grammes de farine 5
 
                                                        test = test + 7
 
                                                        End If
 
 
                                                    'On rajoute le pas à chaque farine
                                                    m = m + pas
                                                    Wend
                                                l = l + pas
                                                Wend
                                            k = k + pas
                                            Wend
                                        j = j + pas
                                        Wend
                                    i = i + pas
                                    Wend
 
 
                                'On passe à la ligne suivante pour y trouver la farine suivante
                                Next ligne5
                            Next ligne4
                        Next ligne3
                    Next ligne2
                Next ligne1
 
            End If
 
        'On vérifie entre les valeur mini et maxi tous les mélanges qui font 1kilo
        Next glucides
    Next lipides
Next prot
 
 
 
 
End Sub

**riaolle** · 11/04/2017, 08h21

Bonjour et bienvenue au forum

Je suis un peu perdue dans toutes les explications, mais en lisant rapidement ton code, je ne vois pas vraiment d'erreur. Tu peux peut-être essayé de bien déclarer toutes tes variables et d'utiliser l'option Explicit, afin de voir si tu n'aurais pas un problème avec un nom de variable.
Option Explicit : http://silkyroad.developpez.com/VBA/LesVariables/#LIII
Variables : http://silkyroad.developpez.com/VBA/LesVariables/#LI

Invité · 11/04/2017, 12h40

Bonjour,
tu nous fournis le code et c'est bien; mais tu ne nous donne pas la recette de cuisine!

je veux dire par là, que ce qui compte pour nous ce n'est pas d'ingérer le processus mais ce que nous appelons le règles de nommage!

pour Protéines 390 à 410 je recherche dans la feuil1 la ou les farines qui en les sommant produise le même valeurs ou le plus approchant par exemple! car si j'ai bien compris mais c'est pas sur tu veux connaitre le mélange le plus approprié!

**Quentin78** · 11/04/2017, 13h09

Bonjour,

Riaolle tout d'abord, merci à toi j'ai trouvé une erreur dans le code avec ta technique, mais c'était dans mon "if" final, étant donné que mon code n'arrive pas à aller dans ce if, c'était pas ça le problème :/.

dysorthographie, j'avoue que mon programme n'est pas simple à comprendre, mais ce n'est pas simple à expliquer non plus

.

Pour protéine 390 à 410, je vais effectivement chercher des farines dans feuil1.
Je fais des groupes de 5 farines. A chaque fois je commence à 0 grammes par farines, puis je monte petit à petit le poids des farines, par exemple :
Farine 1 = 0g
Farine 2 = 0g
Farine 3 = 0g
Farine 4 = 0g
Farine 5 = 0g

Puis j'aurais Farine 1,2,3,4 =0g, et Farine 5 = 10g, puis 20g, puis 30g etc jusqu'à sa valeur max.
Ensuite j'aurais donc : Farine 1,2,3 =0g, farine 4 = 10g, farine 5 = 0g, puis 10g, puis 20g etc.

Si j'obtiens un mélange qui me donne la valeur des prot que je cherche, c'est un bon point !

La valeur que je cherche, entre 390 et 410, je vais les passer une à une en revue. C'est à dire je vais chercher pour 390, je vérifie chaque farine, si ça ne marche pas je regarde pour 391, je vérifie, puis 392 etc jusqu'à 410.

Cela revient à vérifier si le mélange est > prot_mini et < à prot_maxi

J'espère avoir répondu à la question ?

**Quentin78** · 11/04/2017, 13h29

Je pense avoir trouvé mon problème, à savoir pourquoi le programme ne trouvais aucun mélange possible.
Dans les boucles "while" j'avais oublié de remettre i, j, k, l et m à 0. Ils restaient donc à leur valeur maxi.

Maintenant que j'ai résolu ce problème (du moins je pense), je rencontre à nouveau mon plus gros problème avec Excel : Dès qu'il doit effectuer beaucoup de calculs, celui-ci "ne répond pas". Je dois donc le couper avec le gestionnaire de tache.

Pour info avec mon programme en C, visual studio pouvait mettre jusqu'à 30min avant de tester tous les mélanges.

Comme dans le gestionnaire je vois qu'excel utilise 30% du processeur, cela veut dire qu'il continue de calculer et que si j'attends assez longtemps il répondra à nouveau et il aura effectué tout le code ?
Je ne sais pas quoi faire

**Marc-L** · 11/04/2017, 18h33

Bonjour !

« Excel ne répond pas » veut bien dire qu'il travaille !

Placer dans la boucle un DoEvents toutes les n itérations (100 / 500 / 1000 à définir selon l'apparition du message)
mais il y a de fortes chances de calculs bien plus longs comparativement à Visual Studio …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**Quentin78** · 11/04/2017, 19h32

Ah donc c'est "normal" ce message comme quoi il ne répond pas ?
Si je le laisse par exemple une nuit entière il y a des chances que le lendemain j'ai un résultat ? Si oui je testerais ça.

Excel doit faire, à la louche, une 10ene de millions de calculs. Visual me faisait le boulot en 30-40min pour donner une idée.
Je vais tester de laisser tourner la nuit entière. Si j'obtiens des solutions au final, ce sera toujours mieux que de tout faire à la main !

**clementmarcotte** · 12/04/2017, 03h57

Envoyé par Quentin78

Pour info avec mon programme en C, visual studio pouvait mettre jusqu'à 30min avant de tester tous les mélanges.

Avec VBA, tu est presque sûr de dépasser les 30 minutes, à moins d'avoir un code extrêmement optimisé. C est un langage compilé où chaque instruction est traduite une seule fois en langage machine, ou en IL dans le cas de .net. VBA est un langage interprété, où chaque instruction est traduite en langage-machine, chaque fois qu'elle doit être exécutée.

Je ne sais pas quoi faire

Reste en C. Ou bien tu essaies d'appliquer la règle de Pareto. Tu testes les 20 % de mélanges qui vont te donner 80 % des réponses. Probablement même que les 20 % de réponses qui manqueraient, seraient les mèlanges "exotiques' qui servent une fois aux 10 ou 20 ansé.

Bon, je viens de voir ta dernière réponse, qui n'était pas là quand j'ai commencé, mais j'ai la flemme.

Donc, la question à poser c'est pourquoi as-tu besoin d'Excel ? Parce qu'avec Visual Studio, tu as au moins quatre (4) façons différentes d'utiliser des fichiers Excel à partir de Visual C++, VB.net, C#, ou tout autre langage .net.

System.IO.Packaging
SDK Open XML ou ses variantes EPPlus ou ClosedXML
OLEDB
Piloter Excel par Automation comme dans n'importe quelle application Office

Et puis, si tu as une édition Community ou supérieure de Visual Studio, tu peux faire une application (addin, addon, add-in, add-on, complément) compilée pour remplacer VBA par ton code C++.

Et puis, si tu veux Excel pour les fonctions mathématiques, il existe un paquet de bibliothèques mathématiques compatibles .net, des gratuites et des payantes, un peu partout. Et si ton Visual Studio est compatible, il y en a plusieurs de disponibles en packages NuGet. Cela fait que, entre nous deux et la boîte à bois, passer à VBA doit être utilisé en dernier recours.

Si c'est pour le Solver; c'est un peu plus compliqué, parce qu'il existe des versions payantes du Solver. (Il y a bien un démo gratuit, mais ce n'est pas une solution à long terme.)

**clementmarcotte** · 12/04/2017, 04h34

Bonjour,

Envoyé par dysorthographie

pour Protéines 390 à 410 je recherche dans la feuil1 la ou les farines qui en les sommant produise le même valeurs ou le plus approchant par exemple! car si j'ai bien compris mais c'est pas sur tu veux connaitre le mélange le plus approprié!

Je suppute, une deuxième étape. Une fois qu'il va avoir toutes les combinaisons possibles, il veut les envoyer en pâture, soit au solver d'Excel, soit à un autre programme ou algorithme d'optimisation, pour trouver la "bonne"

Mais quand je lui parle de la règle de Pareto, c'est juste une demi-boutade. Parce que je doute réellement de l'utilité de tout tester. Parce que le temps mis à trouver LE mélange, va probablement coûter plus cher que l'augmentation des revenus par rapport au résultat d'une recherche limitée aux mélanges les plus courants ou même un mélange disons "passe-partout".

**Quentin78** · 12/04/2017, 11h45

J'ai besoin d'excel car ce code est pour un ami, qui va monter sa boite mais qui ne connait rien de rien en programmation.
Le programme en C++ fonctionne, mais s'il a besoin de faire la moindre modification (changer une farine par exemple) il devra modifier du C. C'est très simple quand on connait les bases, mais il n'en a aucune.
Excel me permet de faire un tableau avec toutes les farines très simple à modifier.
Il est aussi bien plus simple à utiliser rien que pour lancer le code.

Il m'a demandé si c'était faisable sur Excel car il était un peu pommé

Donc, la question à poser c'est pourquoi as-tu besoin d'Excel ? Parce qu'avec Visual Studio, tu as au moins quatre (4) façons différentes d'utiliser des fichiers Excel à partir de Visual C++, VB.net, C#, ou tout autre langage .net.

Je vais essayer d'utiliser les fichier excel en C++ dans visual, je n'y avais pas pensé mais ça pourrait-être un bon compromis

Une fois qu'il va avoir toutes les combinaisons possibles, il veut les envoyer en pâture, soit au solver d'Excel, soit à un autre programme ou algorithme d'optimisation, pour trouver la "bonne"

En soit pas vraiment. Je cherche juste les mélanges possibles, celui qui trouvera la bonne sera mon ami qui saura en fonction des prix des farines ou autre ce qui lui sera le plus rentable.

Mais quand je lui parle de la règle de Pareto, c'est juste une demi-boutade. Parce que je doute réellement de l'utilité de tout tester. Parce que le temps mis à trouver LE mélange, va probablement coûter plus cher que l'augmentation des revenus par rapport au résultat d'une recherche limitée aux mélanges les plus courants ou même un mélange disons "passe-partout".

Je lui avait déjà proposé, mais il aura besoin en fonction de beaucoup de variables des mélanges différents et assez précis, voir très précis.
Impossible d'avoir un mélange passe-partout. Si c'était si simple on ne se prendrait pas autant la tête !

Soucis de compilation (macro excel)

Macros et VBA Excel

Discussions similaires

Partager

Partager