Forums XLPages

Excel & VBA Espace Débutants bouton qui permets une itération dans VBA lorsqu'on clique dessus

bouton qui permets une itération dans VBA lorsqu'on clique dessus
#1
Gigi
Débutant XLPages

Inscription: 26/05/2011

Messages: 2

Système d'exploitation:
PC
Version Excel utilisée:
Excel 2010
Posté le : 27-05-2011 12h27
 Bonjour,

Je viens à vous car j'ai réussi à comprendre un petit petit peu le langage VBA grâce à vos différents posts, étant totalement novice dans le domaine. Je cherche donc à faire un code dans VBA pour éviter que dès que je change la valeur de n'importe quelle cellule de la feuille, le calcul itératif se fasse dans les cellules C2 et C3, mais que ça se fasse bien quand je j'incrémente C20 de +1.
De plus, je ne sais toujours pas si c'est réalisable, C20 = 1 alors C2 et C3 = 0, et C20 = 2 alors C3 = 0.

Enfin, voilà je me sens un peu perdu et j'en ai besoin pour faire un petit 'logiciel' de calcul sur la RT 2012 (ces feuilles ne sont qu'une petite partie).


Merci de l'aide que vous m'apporterez !



Pièce jointe:
xlsm Classeur1.xlsm   [ Taille: 42.29 Ko - Téléchargements: 445 ]
Edité par Gigi le 27/05/2011 15:38:31
Hors Ligne
 Rapport   Haut 

Re: bouton qui permets une itération dans VBA lorsqu'on clique dessus
#2
JeanMarie
Accro XLPages

Inscription: 09/05/2007
De Courcelles-les-Montbéliard

Messages: 286

Système d'exploitation:
PC & Mac
Version Excel utilisée:
Mac 2004 - 2011, et PC 2003 - 2010
Posté le : 27-05-2011 22h38
Bonsoir Gigi, Didier, le forum

Comme tu utilises le VBA pour incrémenter le compteur de la cellule C20, tu pourrais dans ton code faire le calcul et écrire la nouvelle valeur dans les cellules C2 et C3.
Dans ce cas attention au calcul de la feuille, les formules "itératoire" sont calculées différement des autres.

Je te propose de résoudre tes problèmes, non pas par code VBA mais par formules.

1) C20 = 1 alors C2 et C3 = 0, et C20 = 2 alors C3 = 0

Tu rajoutes la condition dans tes deux formules
en C2 =SI(C20=1;0;0,8 * C2 + 0,2 * C4)
en C3 =SI(C20<3;0;0,8 * C3 + 0,2 * C5)
2) dès que je change la valeur de n'importe quelle cellule de la feuille


Ce problème est le plus compliqué à résoudre (enfin )

Dans ta feuille rajoute dans la cellule A1 la formule ci-dessous
=SI(C20=1;0;A1+(C20-A1))
dans la cellule A23 la formule ci-dessous
=SI(C20=1;0;A23+(C20-A23))
ou
=A1
Ces deux formules vont permettre de savoir si les deux formules en C2 et C3 doivent être calculées. Les formules "itératoires" sont calculées dans une ordre stricte, en commençant en A1 puis vers la droite et vers le bas. Au moment du calcul de la cellule C2, la valeur de la cellule en A1 aura été recalculée, contrairement à la valeur de la cellule A23 qui sera calculée plus tard, elle conserve la valeur calculée dans le cycle itératoire précédent. 
En comparant les valeurs des cellules A1 et A23, on sait si la valeur doit être recalculée.

Tu dois changer les formules en C2 et C3
=SI(C20=1;0;SI(A1<>A23;0,8 * C2 + 0,2 * C4;C2))
=SI(C20<3;0;SI(A1<>A23;0,8 * C3 + 0,2 * C5;C3))
Voilà pour ton problème.

@+Jean-Marie 
Edité par JeanMarie le 31/05/2011 18:49:01
Hors Ligne
 Rapport   Haut 

Re: bouton qui permets une itération dans VBA lorsqu'on clique dessus
#3
Gigi
Débutant XLPages

Inscription: 26/05/2011

Messages: 2

Système d'exploitation:
PC
Version Excel utilisée:
Excel 2010
Posté le : 06-06-2011 16h05
 Merci bien Jean Marie !

Tes indications m'ont bien aidées, je pensais faire qqch dans le style au début, mais je pensais qu'il serait plus pratique de passer par VBA. Donc un grand merci, et tu aurais une idée pour que je puisse enregistrer chaque valeur avant itération dans une cellule sur une autre feuille ? Comme ça ça me permets ensuite de faire qqch d'automatisé, qui me permettrais de faire tourner, lorsque j'appuie sur un bouton, tout les calculs sur une année jour par jour et chaque valeur serait enregistrée sur une autre page au cas où j'ai besoin d'y faire référence.

Merci encore pour ton / votre aide !

Cordialement,

florian 
Hors Ligne
 Rapport   Haut 

Re: bouton qui permets une itération dans VBA lorsqu'on clique dessus
#4
JeanMarie
Accro XLPages

Inscription: 09/05/2007
De Courcelles-les-Montbéliard

Messages: 286

Système d'exploitation:
PC & Mac
Version Excel utilisée:
Mac 2004 - 2011, et PC 2003 - 2010
Posté le : 08-06-2011 00h20
Bonsoir Gigi, Didier, le forum

Dans ton projet, tu as la chance de pouvoir utiliser trois solutions différentes :
  • la recopie de formules dans un tableau,
  • les formules circulaires et l'option de calcul d'itération.
  • le VBA.
Ces trois possibilités sont toutes aux simples ou complexes à aborder (suivant ou on se trouve ).

Que veux-tu réellement ?

En lisant les commentaires de ta réponse, je suis dubitatif, as-tu assimilés les concepts des formules circulaires et de l'itération !

Je pourrais t'expliquer :
  • la création du tableau en rajoutant ce qu'il faut sur la feuille "Feuil1", (tu as déjà une partie de la réponse dans la plage 'Gestion froid - chaud'!K20:K37).
  • la mémorisation des valeurs calculées en mode itération et de l'utilisation de l'option "Nombre maximal d'itérations".

Je laisserai la partie VBA, je ne voudrais pas de dire d'anneries ou te montrer des instructions qui ne te serais pas utilie, Cordialement,

@+Jean-Marie
Hors Ligne
 Rapport   Haut 

Re: bouton qui permets une itération dans VBA lorsqu'on clique dessus
#5
JeanMarie
Accro XLPages

Inscription: 09/05/2007
De Courcelles-les-Montbéliard

Messages: 286

Système d'exploitation:
PC & Mac
Version Excel utilisée:
Mac 2004 - 2011, et PC 2003 - 2010
Posté le : 13-06-2011 18h45
Bonjour Gigi, Didier, le forum,

Construction du tableau par formules et sans recours à l'itération

Dans ta feuille de calcul "Gestion froid - chaud", quand tu changes la valeur de la cellule C20, cela fait claculer les formules
I5 et I6, puis les formules C2 à C5 et les formules C7 à C9.
Ces formules dépendent aussi de données dans les cellules F2, F3, F7 à F9.
Je ne parlerai pas des autres cellules, elles ne varient pas lors du changement de la valeur en C20.
Pour la construction du tableau que je place en feuille "Feuil1"
les données fixes :
  • θ iich (h) sera en H4, θ iifr (h) en H5
  • θ op_inc_C1, θ op_inc_C2, θ op_inc_C3 respenctivement en H7, H8, H9

les valeurs calculées :

En colonne J en parant de la ligne 3, j'ai reporté la valeur de la cellule C20, celle-ci allant de 1 à 365
Dans les colonnes suivantes, les valeurs des champs calculés :
  • θ rm (j)
  • θ rm (j-1)
  • θ ei,moy (j)
  • θ ei,moy (j-1)
  • θ op_inc_maxC1 (h)
  • θ op_inc_maxC2 (h)
  • θ op_inc_maxC3 (h)
Pour les formules du basic, elles reprennent en gros les formules de la feuille, je n'ai adapté que les références, les cellules sont à copier en ligne 4
=0,8*K3+0,2*C4
=K3
=C4
=M3
=MAX(H$5;0,33*$K4+18,8+$H$7)
=MAX(H$5;0,33*$K4+18,8+$H$8)
=MAX(H$5;0,33*$K4+18,8+$H$9)
Après recopie vers le bas des formules, tu auras toutes les valeurs de ton tableau.

Construction du tableau par formules avec l'utilisation de l'itération.
Toujours sur la même feuille,

les données fixes
  • θ iich (h) sera en T4, θ iifr (h) en T5
  • θ op_inc_C1, θ op_inc_C2, θ op_inc_C3 respenctivement en T7, T8, T9
  • En colonne V, la valeur de J (identique à la colonne J du tableau précédent)
  • En colonne W, la valeur allant de 5 à 369,
Dans les colonnes suivantes les valeurs des champs calculés :
  • θ rm (j)
  • θ rm (j-1)
  • θ ei,moy (j)
  • θ ei,moy (j-1)
  • θ op_inc_maxC1 (h)
  • θ op_inc_maxC2 (h)
  • θ op_inc_maxC3 (h)
Les formules respectives à ces champs, à mettre en ligne 4
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W3;'Gestion froid - chaud'!$C$2;Feuil1!X4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W3;'Gestion froid - chaud'!$C$3;Feuil1!Y4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W2;'Gestion froid - chaud'!$C$5;Feuil1!AA4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W2;'Gestion froid - chaud'!$C$4;Feuil1!Z4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W3;'Gestion froid - chaud'!$C$7;Feuil1!AB4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W3;'Gestion froid - chaud'!$C$8;Feuil1!AC4))
=SI('Gestion froid - chaud'!C$20=1;0;SI('Gestion froid - chaud'!$C$20=$W3;'Gestion froid - chaud'!$C$9;Feuil1!AD4))
Ces formules récupèrent quand le jour est identique à la valeur de la cellule W, la valeur de la cellule correspondant de la feuille "Gestion Froid - chaud", dans le cas contraire elle conserve leur propre valeur.
Je ne peux t'expliquer pour quelle raison, il y a un décalage entre la valeur de la colonne V et de la colonne W !
Par contre, tu as peut-être remarqué, il y a un décalage sur les 3ème et 4ème formules. En fait cela est normal, comme je te l'indiquai dans mon premier post, les formules itératoires sont calculées différement des autres formules. Les formules "standard" sont calculées normalement avant les formules itératoires, donc si l'on veut garder la correspondance de ligne il faut un décalage. Les formules en C7, C8, C9 de la feuille "Gestion froid - chaud" ne sont pas des formules itératoires, mais comme elles en dépendent, elles seront calculées après les formules itératoires. L'ordre de calcul de formules, la priorité à une importance capitale.
La première partie de la formule SI('Gestion froid - chaud'!C$20=1;0; sert d'initialisation de la valeur de la cellule. Quand on joue avec l'itération et les formules itératoires, il est bon d'avoir une phase d'initialisation, elle permet de remettre en ordre les valeurs en cas d'une erreur.
Met la valeur de la cellule C20 de la feuille "Gestion Froid - chaud" à 1, et ensuite recopier les formules vers le bas.
Tu n'auras que des 0, et tu es encore loin d'avoir le tableau complet.
Clique sur ton bouton d'incrémentation jusqu'à la valeur 10, revient sur la feuille "Feuil1", normalement tu ne devrais avoir que les quatre premières lignes de ton tableau avec les valeurs mémorisées.
Pour remplir le tableau complétement jusqu'à la dernière ligne, tu as deux possibilités :
cliquer, cliquer, cliquer et recliquer......
ou utiliser l'option du nb maximal d'itérations.

On va utiliser les options d'itération. Va dans les options d'excel pour changer la valeur du nombre maximal d'itérations, tu dois indiquer une valeur supérieure à 369 (Excel permet de mettre une valeur entre 1 et 32767).
En changeant cette valeur, tu demandes à excel de boucler x fois le calcul de la feuille, quand une "événement calculate" ce produit.
Mais pour que le calcul boucle de lui même il y encore quelque changement à faire.
Supprime tes deux boutons, il ne vont plus te servir, même en VBA.
On va utiliser la la cellule C20 pour faire le compteur de la boucle, comme les formules dépendent déjà de cette valeur, cela évitera de tout modifier.
On va mettre dans cette cellule la formule ci-dessous
=SI(B23="Calcul du Tableau";SI(C20<369;C20+1;369);1)
Comme tu peux le voir, je fais appel au contenu de la cellule B23, cette cellule permettra de passer à passer du mode paramètres, mode qui te permettra de modifier les données fixes, et le mode de calcul du tableau.
Pour ce faire, créer une liste de validation sur la cellule B23. (je ne peux t'expliquer dans les détails la manip, la version 2003 étant différent à la verison 2010)
Dans la liste de validation, j'ai indiqué ceci
Modification des paramètres;Calcul du Tableau
le plus important étant que le texte du deuxième argument de la liste de validation soit identique au texte du comparateur dans la formule.
Si tu as suivi toute les manip, passe au mode "Calcul du Tableau", normalement le tableau doit être complet sur la feuille "Feuil1"

Malheureusement du dois avoir des erreurs sur la feuille "Gestion froid - chaud", il faut corriger quelques formules, pour soigner la présentation.
en I6 =SI(C20=1;0;SI(C20=369;Feuil1!C367;INDEX(Feuil1!C3:C367;C20;1)))
en I5 =SI(OU(C20=1;C20=2);0;SI(C20=369;Feuil1!C366;INDEX(Feuil1!C2:C366;C20;1)))
en C2 =SI(C20=1;0;SI(C20=369;Feuil1!X367;SI(A1<>A23;0,8 * C2 + 0,2 * C4;C2)))
en C3 =SI(C20<3;0;SI(C20=369;Feuil1!Y367;SI(A1<>A23;0,8 * C3 + 0,2 * C5;C3)))
Voilà on a fini avec la partie du tableau construit avec les formules itéraotires.

Pour être exhaustif avec les options d'itération, excel dans sa boucle itération, fait ceci
  • 1) prend en début une image des valeurs que contiennent les cellules ayant des formules, 
  • 2) calcul toutes les formules,
  • 3) compare les nouvelles valeurs avec le contenu de l'image, si il trouve des valeurs différentes repart au point 1, 
Il fait ceci jusqu'à à arriver au terme du Nb maximal d'itérations indiqués dans les options, ou si toutes les valeurs sont identiques (à la tolérance près) à l'images de départ. Cette tolérance est la valeur indiquée dans le champ écart maixal dans les options de calcul.
Encore une petite chose, A ce que j'ai pû voir, sur les nouvelles versions d'office 2007 et 2010 sur PC, et la version 2011 sur mac, sur la version d'office 2008 sur mac, l'itération ne fonctionne pas.
Microsoft a supprimé dans la boucle d'itération l'acutalisation de l'écran, on ne peut plus observer les modifications des valeurs dans les cellules. Snif. Mais le calcul est plus rapide.
Tu peux retrouver sur le site de didier un fichier à télécharger sur la réalisation de grille de sudoku à l'aide de formules et des options d'itération, ou j'utilise ce que je t'ai expliqué.

Construction du tableau par VBA.
Voici le code VBA

Sub GiGi()
'Déclaration de variable
Dim I As Integer 'cette variable sera utilisée pour la boucle
Dim TempOpInc As Single

'Les formules de la feuille ne seront pas recalculés pendant la création du tableau
Application.EnableEvents = False

'on ecrit les données fixes dans les cellules de la feuille Feuil1
With Worksheets("Gestion froid - chaud")
    Worksheets("Feuil1").Range("AG4") = .Range("F2")
    Worksheets("Feuil1").Range("AG5") = .Range("F3")

    Worksheets("Feuil1").Range("AG7") = .Range("F7")
    Worksheets("Feuil1").Range("AG8") = .Range("F8")
    Worksheets("Feuil1").Range("AG9") = .Range("F9")
End With

'on peut passer au calcul des valeurs du tableau par VBA
With Worksheets("Feuil1")
    'on écrit la valeur 1 dans la cellule AI3
    .Range("AI3") = 1
   
    'Creation de la boucle pour calculer les X lignes
    For I = 4 To 367
        .Cells(I, 35) = I - 2
        .Cells(I, 36) = 0.8 * .Cells(I - 1, 36) + 0.2 * .Cells(I, 3)
        .Cells(I, 37) = .Cells(I - 1, 36)
        .Cells(I, 38) = .Cells(I, 3)
        .Cells(I, 39) = .Cells(I - 1, 38)
       
        TempOpInc = 0.33 * .Cells(I, 36) + 18.8
        'Test MaxC1 (h)
        If .Range("AG5") > TempOpInc + .Range("AG7") Then
            .Cells(I, 40) = .Range("AG5")
        Else
            .Cells(I, 40) = TempOpInc + .Range("AG7")
        End If
       
        'Test MaxC2 (h)
        If .Range("AG5") > TempOpInc + .Range("AG8") Then
            .Cells(I, 41) = .Range("AG5")
        Else
            .Cells(I, 41) = TempOpInc + .Range("AG8")
        End If
       
        'Test MaxC3 (h)
        If .Range("AG5") > TempOpInc + .Range("AG9") Then
            .Cells(I, 42) = .Range("AG5")
        Else
            .Cells(I, 42) = TempOpInc + .Range("AG9")
        End If
    Next I
End With
'Les formules de la feuille de calcul se recalcul
Application.EnableEvents = True
End Sub
J'ai mis des instructions Application.EnableEvents = xxx (en début et fin de code), pour accélérer la création du tableau, je te déconseille d'utiliser cette instruction dans tes propres codes. 
Je ne vais pas m'étendre sur les fonctions ou instructions de ce code, nous avons tous nos manières de faire du code.
Si tu essayes ces trois possibilités tu veras que la plus rapide sera le tableau réalisé en VBA, la plus simple ou la plus abordable pour la première solution, et celle de l'itération te fait passer pour un monstre.
Voilà, j'attends tes remarques sur ce que je viens de t'expliquer. J'entends de ta part que cet échange soit construtif, si tu veux des éclairssiements j'essayerai d'y apporter de la clarté.

@+Jean-Marie




Edité par JeanMarie le 14/06/2011 07:05:25
Hors Ligne
 Rapport   Haut 


Vous pouvez voir les sujets.
Vous ne pouvez pas débuter de nouveaux sujets.
Vous ne pouvez pas répondre aux contributions.
Vous ne pouvez pas éditer vos contributions.
Vous ne pouvez pas effacez vos contributions.
Vous ne pouvez pas ajouter de nouveaux sondages.
Vous ne pouvez pas voter en sondage.
Vous ne pouvez pas attacher des fichiers à vos contributions.
Vous ne pouvez pas poster sans approbation.

[Recherche avancée]


Qui consulte actuellement ce sujet ?   1 Utilisateur(s) anonymes