Effectuer une boucle dans une procédure exploitant un calcul sur une feuille exc

Signaler
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014
-
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
-
Bonjour à tous; ça faisait bien longtemps que je n'étais pas passé sur ce forum...

Je suis actuellement en train de travailler sur une feuille de calcul Excel qui effectue des calculs en utilisant des fonctions natives d'excel.

Afin de chercher une solution optimale je dois créer mon propre solveur en effectuant une boucle sur une valeur qui se met à jour sur la feuille excel, puis reprendre le résultat par VBA, le comparer, et décider de la valeur suivante à utiliser pour le calcul.
Pour résumer:
- je lance la procédure VBA (avec un bouton)
- La procédure met une valeur dans une cellule de la feuille
- La feuille effectue un calcul à partir de cette valeur
- Je récupère le résultat du calcul par code
- En fonction d'une condition je spécifie la nouvelle valeur à prendre pour le calcul
- La boucle s'arrête dès que j'ai une erreur inférieure à 0.001 entre le résultat et une valeur cible.

Le problème que je rencontre est que le code en VBA est plus rapide que le calcul sur la feuille excel; le résultat n'est pas encore calculé que je suis à la boucle suivante.
Existe il un moyen de forcer l'attente du résultat de la feuille pour continuer le déroulement de la procédure ?

En vous remerciant d'avance pour l'aide.

38 réponses

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Bonjour,
montre ton code.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Et explique ce genre de chose :
La feuille effectue un calcul à partir de cette valeur

Une feuille ne calcule en soi rien. Qu'est-ce, alors, exactement, qui calcule ?
____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

Voici le code

'initialisation de la 1ere valeur
Approche = Range("C4")

'valeur cible
Cible = Range("C5")

'100 iterations effectuées
For i = 1 To 100

Range("A1") = Approche
'Recuperation du resultat du calcul
Resultat = Range("A2")

'Determinattion de l'approche suivante
If Resultat - Cible > 0 Then Approche = (1 - Approche) / 2
If Resultat - Cible < 0 Then Approche = (Approche) / 2

Next i

Ce n'est pas mon code car ce serait assez compliqué à expliquer comment ça marche, mais c'est le même principe:
- On initialise une valeur d'approche
- Cette valeur est recopiée sur une feuille excel; une formule directement inscrite dans une cellule permet de calculer le résultat
- Le code récupère ce résultat; en fonction d'une condition le code définie la valeur d'approche suivante.

Le principe est de diminuer par 2 l'encadrement de l'approche, jusqu'à tomber sur une valeur qui permette d'avoir résultat - cible le plus faible possible. C'est en fait un solveur basique.

Le problème est que le résultat du calcul effectué par la formule sur la feuille excel n'a pas le temps de se mettre en jour, donc d'une boucle à l'autre on a le même résultat.

Ce que je voudrais c'est continuer l’exécution du code quand le résultat est effectif.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Pense dorénavant à présenter tes bouts de code entre balises code, s'il te plait.

- les formulations du genre
Approche = Range("C4") 

sont certres tolérées par VBA, mais sont à proscrire. Toujours préciser la propriété.
Exemple :
Approche = Range("C4").Value 

Il me manque l'essentiel : la formule contenue dans la cellule A2
____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

En réalité ce n'est pas exactement une formule mais un traitement effectué sur des données assez complexes (recherche dans des tables, calcul sur les valeurs de la table, recherche dans une autre table etc).

La nature de la formule est en fait secondaire; disons que A2 = 2xA1
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
J'ai bien évidemment besoin de "voir", si tu veux de l'aide (sinon, cela put durer une éternité).

Notamment ce qui se passe entre la ligne 1 et la ligne 3 de ceci :
Range("A1") = Approche
'Recuperation du resultat du calcul
Resultat = Range("A2")

Et quand je dis "voir", je veux dire le code (tout le code) qui aboutit à la modification de A2.

A défaut : je te laisse.
Fais très attention à ne pas m'obliger à te reposer une question à ce propos. Je ne la poserai pas !

____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Pour être précis :
Toutes les lignes de code intervenant dans le mécanisme de modification de A2 à partir du clic sur ton bouton


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

Je me suis sans doute mal exprimé; il n'y a pas d'autre code. Tous les calculs sont effectués directement sur excel en utilisant des formules et des fonctions natives (des recherchev par exemple)

Cette petite procédure sert uniquement à faire varier la valeur d'entrée de ma feuille de calcul, à récupérer le résultat, puis à décider de la prochaine valeur d'entrée à traiter en fonction de conditions.

je vais donner un autre exemple:

Sub Bouton2_QuandClic()

'valeur cible
Cible = Range("B4").Value
Approche = Range("B1").Value

' -> le resultat est directement calculé sur la feuille excel, avec une formule

'Recuperation du resultat du calcul
Resultat = Range("B2").Value

'Determinattion de l'approche suivante
If Resultat > Cible Then Approche = Approche - 1
If Resultat < Cible < 0 Then Approche = Approche + 1
Range("B1").Value = Approche

End Sub


Si le résultat de mon calcul (effectué sur la feuille) est trop bas alors j'augmente la valeur à traiter de 1, si il est trop haut je la diminue de 1.

Ce code marche très bien si je le lance en cliquant sur un bouton (le calcul a le temps de se faire). Si par contre j'utilise une boucle pour répéter cette action plusieurs fois la valeur récupérée (le résultat du calcul effectué sur la feuille excel) n'a pas le temps de se mettre à jour.

Et ce que c'est plus clair ?
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Et qu'attends-tu, comme réponse ?
Un coup tu dis :

Le problème est que le résultat du calcul effectué par la formule sur la feuille excel n'a pas le temps de se mettre en jour, donc d'une boucle à l'autre on a le même résultat.

et ensuite
En réalité ce n'est pas exactement une formule mais un traitement effectué sur des données assez complexes (recherche dans des tables, calcul sur les valeurs de la table, recherche dans une autre table etc).

[quote]La nature de la formule est en fait secondaire; disons que A2 = 2xA1

et maintenant
Tous les calculs sont effectués directement sur excel en utilisant des formules et des fonctions natives (des recherchev par exemple)/quote
Etonnant !
Et pour ton info : ce n'était pas la nature même de la ou des formules, du ou des codes, qui m'intéressent, mais la chronologie de ce qu'ils modifient ! (et pas par tes explications, mais en les voyant).
Mon sentiment ? tu es avec une usine avec plein de bouteille de gaz !
Tu insistes avec tes visions des choses ? ===>> Amen et sous ta seule responsabilité ===>>*
Tu dis vouloir attendre que tes "mécanismes inconnus" aient terminé de calculer ce qui va aller en A2 ? ===>> fastoche ===>>
Tu mets dans l'évènement Worksheet_Change ce que tu veux exécuter lorsque le contenu de A2 change de valeur !

Et j'ai tout-à-fait le sentiment de ce que je t'aide ainsi à te jouer à toi-même un très vilain tour : celui de perpréter une usine à gaz.
Voilà !
J'en ai terminé dans ces conditions avec ce sujet, en ce qui me concerne



____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

En effet ma feuille est une usine à gaz, mais il n'y a pas moyen d’effectuer ces traitements simplement.
Je te remercie pour l'idée d'utiliser Worksheet_Change; je vais l'essayer de ce pas.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
1) Ca (que ce soit une usine à gaz), il n'y a nul besoin d'être sorcier pour le deviner.
2) je t'ai parlé de "mauvais tour". J'aurais préféré te dire ce qui n'allait pas, dans ton mécanisme, plutôt que de t'aider à cacher ainsi la crotte.
Mais tu as choisi et j'arrête donc là.
Bonne chance.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

J'ai choisi quoi ? j'avoue ne pas très bien te comprendre. Tu voudrais que je décrive tout le traitement ?
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
J'ai choisi quoi ? j'avoue ne pas très bien te comprendre. Tu voudrais que je décrive tout le traitement ?

Tu veux que je reprenne tout ce que je t'ai en vain demandé de montrer ?
Je croyais avoir été clair. Non ?
Excuse-moi, mais nous en sommes au 13ème message, pour rien ===>> comme si l'on en était au 1er !
Je t'abandonne donc là. Peut-être quelqu'un d'autre sera-t-il volontaire pour prendre le relais de ce jeu de devinettes ?
Bonne chance.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

Je suis désolé de te dire ça car tu m'a apporté ton aide, mais le fait que je sois novice en VBA n'est pas une justification pour se montrer aussi condescendant.
Le traitement effectué est une réalisation complète de diagramme de MC Cabe et Thiele, avec tracé des plateaux et tout.
La boucle permet de suivre l'évolution du diagramme en fonction de la variation d'un paramètre d'entrée (composition de l'alimentation, flux d’énergie, hauteur de plateau etc).
La réalisation de ce graphique est détaillée ici: http://en.wikipedia.org/wiki/McCabe%E2%80%93Thiele_method
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Ce n'est pas de la "condescendance", ami, mais une prise de conscience (celle de la perte de mon temps).
J'ai depuis très longtemps passé l'âge de jouer aux devinettes, comprends-tu ? (je ne sais même pas si tu comprendras cette question là !)
Débutant en développement, je veux bien ! Mais on se prête alors à ce qui est demandé, dans un tel cas ! Et ce n'est pas l'attitude que t as observée (en dépit de demandes réitérées) !
Je suis bien plus désolé, crois-moi, de cet état de fait, que de tout le reste. Et vais au dodo ...



____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
42
Date d'inscription
mercredi 10 mai 2006
Statut
Membre
Dernière intervention
31 janvier 2014

J'ai depuis très longtemps passé l'âge de jouer aux devinettes, comprends-tu ? (je ne sais même pas si tu comprendras cette question là !)


Ce n'est pas de la condescendance ça peut être ?
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Ouais, je vais malgré tes manifestations totalement injustifiées (notamment en raison de ton obstination à ne pas montrer ton code) de susceptibilité, t'informer de ce qui t'échappe, dans ton usine.

Ce n'est pas que pour toi, que je vais le faire. Je le fais également pour d'autres, notamment pour un ami pour lequel je me creuse en ce moment la tête et qui, j'espère, passera par ici et se reconnaîtra (je lui fais un petit coucou).

On y va === >>

Excel traite ses calculs de formules dans un fil (thread) totalement distinct du fil VBA. Un peu, pour que l'on comprenne mieux, comme il en va des feux de circulation et de la circulation elle-même. Les feux ignorent tout du flux de circulation et la circulation n'a aucune prise sur les feux, qui continuent, eux, à fonctionner à leur rythme. Aucun conducteur ne peut alors décider d'avancer ou de reculer le passage d'un feu d'un état à l'autre.
Il peut toutefois arriver que l'on place un agent de la circulation pour se substituer aux feux. Ce dernier, placé dans le même contexte et donc en parfaite communion avec la circulation, décidera de la pacifier à un rythme, distinct du rythme préréglé d'un feu, en fonction de ce qu'il constate.
Mais alors : là où on met un agent de la circulation à cette tâche à un carrefour, on commence par y inhiber le feu. C'est soit l'un, soit l'autre, à ce carrefour, pas les deux !
Cet agent de la circulation, c'est VBA.

Rien n'interdit, dans une ville, de laisser les feux à certains carrefours et de les remplacer à d'autres par des agents de la circulation. Il suffit et il faut alors que les premiers et les seconds ne traitent pas un ou plusieurs même(s) carrefour(s).

Revenons maintenant à Excel (ses formules) et VBA. Ils peuvent cohabiter sans problèmes mais uniquement si VBA ne se sert pas du résultat de calculs faits (dans un autre fil) par Excel (ses formules)

Re-coucou à un autre forumeur ami.

Voilà voilà !
Si (et c'est probablement le cas) tu n'as pas prévu ce coup-là dans ton usine, il va falloir y remédier ou mettre des pansements un peu partout (bricolage sur bricolage).
Y remédier nécessite l'analyse du code.

Re voilà voilà.

Bonne nuit.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Et à propos de "pansements" en tous genre :
J'ai ce matin une mauvaise nouvelle pour toi :
Si ta cellule A2 est elle-même modifiée par une formule, cette modification n'entraînera pas l'évènement Worksheet_change. Et tu peux alors oublier ce pansement-là. Il va te falloir en trouver un autre. J'en connais un, mais alors : en ajoutant encore un tuyau de plus à la tour de Babel qui semble être déjà en place.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient
Messages postés
377
Date d'inscription
lundi 3 avril 2006
Statut
Membre
Dernière intervention
22 août 2018

Petit coucou à Ucfoutu,
Bonjour Dudule32 ; effectivement on ne t'avait pas vu depuis un "petit" moment.

Perso, j'adhère :
uniquement si VBA ne se sert pas du résultat de calculs faits (dans un autre fil) par Excel (ses formules)

Si je reprends ton premier message:
- je lance la procédure VBA (avec un bouton)

Il n'y a qu'un "Call" dans le code de ton bouton?
- La procédure met une valeur dans une cellule de la feuille

Quelle cellule ? C'est quoi qui est utilisé pour ça: .value? .Formula? .Text?
- La feuille effectue un calcul à partir de cette valeur

Non! les formules de la feuille donnent leur résultats
- Je récupère le résultat du calcul par code

Depuis où et c'est quoi de "Code de récupération?
- En fonction d'une condition je spécifie la nouvelle valeur à prendre pour le calcul

C'est quoi "en fonction d'une condition"? un If? Une entrée à la mimine?
C'est quoi "je spécifie" : Tu tapes une valeur? C'est une macro qui doit être lancée? et alors par quoi est-elle déclenchée?

Tout ça fait beaucoup de questions, et il me paraît sage que tu te les poses et y répondes : Notamment celles de ucfoutu !

J'ai surtout l'impression que ce que tu veux, c'est "automatiser"
j'augmente la valeur à traiter de 1, si il est trop haut je la diminue de 1.

Tu fais ça à la mimine APRES avoir cliqué sur ton bouton, puis, si le résultat ne te convient pas tu TAPES une autre valeur, et tu recliques ton bouton.

Tu veux donc qu'un code "sache"
répéter cette action plusieurs fois
: Un clic sur ton bouton, et roule ma poule, la "valeur à traiter" s'affecte toute seule!

Y en a qu'on essayé ce genre d'aventure avec "OnTime" ; ça peut marcher, paraît-il.

Voilà, voilà

Cordialement
Rataxes64
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
239
Bonjour, CerberusPau

Il peut en effet penser au ontime.
Il peut également (moins lourd) :
- lire dan un variable toto sa cellule A2 avant chaque tour de sa boucle
- attendre qu'elle soit modifiée avant de lancer le reste de la procédure dans chaque tour de sa boucle, genre
Do While Range("A2") = toto
  DoEvents
Loop

- et d'autres petits pansements de l'espère, ici et là (où ? ===>> faudrait connaître son code !)

Mais toutes ces bidouilles ne feront qu'alourdir, rendre plus difficile la maintenance, etc...
Sans parler des risques !
Tiens, avec la boucle Do While montrée quelques lignes au-dessus. Imaginons que pour une raison ou une autre (maîtrisée ou non) les calculs ne conduisent pas à une modification de A2 ===>>> En plein dans une boucle dans fin !

On ne joue pas n'importe comment avec n'importe quoi.



____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient