Code VBA beaucoup trop lent

[moteurV12]

Bonjour à Tous,

Tout est dans le titre
une optimisation du code est elle possible ??

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Bonjour moteurV12.

Sub toto()
Application.Calculation = xlCalculationManual

Dim i&, j&, k&, l&, m&, n&, Dat(1 To 200, 1 To 20), oColl As New Collection
Dim v2, V, w, X, y, Z, z3, an7, an8, an9, an10, an11, an12, an13, base1, v5, balise, an14, an15, an16, Difficult
V = Range("AN1").Value
w = Range("AN2").Value
X = Range("AN3").Value
y = Range("AN4").Value
Z = Range("AN5").Value
z3 = Range("AN6").Value
an7 = Range("AN7").Value
an8 = Range("AN8").Value
an9 = Range("AN9").Value
an10 = Range("AN10").Value
an11 = Range("AN11").Value
an12 = Range("AN12").Value
an13 = Range("AN13").Value
an14 = Range("AN14").Value
an15 = Range("AN15").Value
an16 = Range("AN16").Value
Difficult = Range("V2").Value


v5 = Range("AF6").Value
base1 = Range("AF5").Value
balise = Range("AK5").Value
base2 = Range("AG5").Value
balise2 = Range("AK6").Value
base3 = Range("AH5").Value
balise3 = Range("AM5").Value

    Columns("A:F").ClearContents


  For i = 1 To 200
    Set oColl = Nothing
    On Error Resume Next
    With Range("W1:AJ1")
      For j = 1 To .Count
        oColl.Add .Cells(1, j).Value, CStr(.Cells(1, j).Value)
      Next j
    End With
    l = oColl.Count
    For j = 1 To 20
      oColl.Add j, CStr(j)
    Next j
    On Error GoTo 0
    m = oColl.Count
    Randomize
    For k = 1 To 6
    n = l + 1 + Int((m - k - l + 1) * Rnd)
    Dat(i, k) = oColl(n)
      oColl.Remove n
    Next k
    



v2 = i

If Difficult = "" And v2 = i And v5 = "" And base1 = "" And Not _
((Dat(i, 1) = V Or Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y) _
And (Dat(i, 2) = V Or Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10) _
And (Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8)) Then i = i - 1: GoTo 1


If v2 = i And v5 <> "" And Not ((Dat(i, 6) = X Or Dat(i, 6) = y Or Dat(i, 6) = Z Or Dat(i, 6) = z3 Or Dat(i, 6) = an7 Or Dat(i, 6) = an8 _
Or Dat(i, 6) = an9 Or Dat(i, 6) = w Or Dat(i, 6) = V)) Then i = i - 1: GoTo 1

If Difficult = "" And v2 = i And v5 <> "" And Not Dat(i, 1) = v5 Then i = i - 1: GoTo 1

'If base1 = V Or base1 = w Or base1 = X Or base1 = y And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
'And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8) _
'And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1


If Difficult = "" And balise < 5 And balise2 = "" And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1

'----------------------------
If Difficult = "" And balise < 5 And balise2 < 6 And balise3 = "" And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And v2 = i And balise2 < 6 And base2 <> "" And Not Dat(i, 2) = base2 Then i = i - 1: GoTo 1
'------------------------------------------
'----------------------------
If Difficult = "" And balise < 5 And balise2 < 9 And balise2 > 5 And balise3 = "" And v2 = i And Not ((Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8 Or Dat(i, 2) = an9 Or Dat(i, 2) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And balise < 5 And balise2 < 9 And balise2 > 5 And balise3 = "" And v2 = i And Not Dat(i, 4) = base2 Then i = i - 1: GoTo 1
'------------------------------------------

If Difficult = "" And balise < 5 And balise2 < 6 And balise3 < 5 And v2 = i And Not ((Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And balise < 5 And balise2 < 6 And balise3 < 5 And v2 = i And base2 <> "" And Not Dat(i, 2) = base2 And Not Dat(i, 5) = base3 Then i = i - 1: GoTo 1

If Difficult = "" And balise > 4 And balise2 = "" And balise3 = "" And v2 = i And Not ((Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10) _
And (Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8 Or Dat(i, 2) = an9) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1



'------------------------------------------
'----------------------------------------------------------------------------------------------------------------------------------------------------
' ------------------- si facile ou super facile 1 BALISE
If Difficult = "Facile" And v2 = i And balise < 3 And balise2 = "" And v5 <> "" And Not (Dat(i, 2) = v5 Or Dat(i, 1) = base1) Then i = i - 1: GoTo 1

If Difficult = "Facile" And balise < 3 And balise2 = "" And v2 = i And Not _
((Dat(i, 4) = V Or Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 3) = w Or Dat(i, 3) = X Or Dat(i, 3) = y Or Dat(i, 3) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1


If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 2 And balise < 5 And v5 <> "" And Not Dat(i, 3) = v5 Then i = i - 1: GoTo 1

If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 2 And balise < 5 And balise2 = "" And v2 = i And Not _
((Dat(i, 4) = V Or Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1

If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 4 And v5 <> "" And Not Dat(i, 4) = v5 Then i = i - 1: GoTo 1

If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 4 And balise2 = "" And v2 = i And Not _
((Dat(i, 3) = V Or Dat(i, 3) = w Or Dat(i, 3) = X Or Dat(i, 3) = y Or Dat(i, 3) = Z Or Dat(i, 3) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1

' ------------------- si facile ou super facile 2 BALISES




'If base1 = V Or base1 = w Or base1 = X Or base1 = y And v2 = i And Not ((Dat(i, 2) = V Or Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y) _
'And (Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8) _
'And (Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10)) Then i = i - 1: GoTo 1


' le cheval de base
' If base1 <> "" And v2 = i And Not (Dat(i, 1) = base1 Or Dat(i, 2) = base1 Or Dat(i, 3) = base1 Or Dat(i, 4) = base1 Or Dat(i, 5) = base1) Then i = i - 1: GoTo 1
' chevaux entrés sur la ligne 6 à partir de AF donne l'ordre des combinaisons
    
    
1  Next i
  Range("A1").Resize(200, 6).Value = Dat
Application.Calculation = xlCalculationAutomatic
  
End Sub

Private Sub TIRAGE_Click()
  toto
End Sub

Ouf ! Joli code !
Ne pensez-vous pas qu'il serait utile que vous dissiez clairement ce qu'il est censé faire ?
Personnellement, je n'ai guère envie d'explorer ce labyrinthe pour comprendre où il mène.​

Bonne journée.


ℝOGER2327
#7663

Jeudi 5 As 142 (Saint Van Meegeren, faussaire - fête Suprême Quarte)
17 Brumaire An CCXXIII, 6,4019h - cresson
2014-W45-5T15:21:52Z

 

[moteurV12]

Re : Code VBA beaucoup trop lent

Hello Roger,

Et bien .......... je vais essayer d'être clair

En V2 on dit si le résultat a trouver est facile.
En colonne verte on a la liste classée de l'échantillon à étudier par combi de 5

Suivant la réponse de V2 l'extration des combis sera différente, et l'ordre aussi.

Le problème est que l'accumulation de IF ralenti de façon plus que conséquente le code. Dans l'exemple nous n'avons que 200 lignes à étudier mais il arrive qu'il y en ai plusieurs milliers.

En fait j'aurai juste besoin d'une astuce pour modifier la 1er partie du code qui m'éviterai l'utilisation des IF de la 2eme partie.
j'y suis tellement dedans que je ne sais si je suis vraiment clair ????

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Re...

Effectivement, je ne devine toujours pas ce que doit faire le code. Ce qui est sûr, c'est que ne sont pas les multiples If ... Then ... : GoTo 1 qui sont responsables de la durée d'exécution.
La cause principale est certainement lié à la conception du code : vous utilisez une structure
For i = 1 To 200
...
Next
dans laquelle i est souvent décrémenté. Conséquence : la boucle est effectuée beaucoup plus de deux cents fois. J'ai procédé à un essai en mettant un compteur dans la boucle : dans cet essai, la boucle a été exécutée 777 604 fois !
C'est la preuve qu'une boucle s'exécute rapidement.

En l'état, on pourrait remplacer l'utilisation d'une Collection par celle d'un Dictionnaire, nettement plus rapide. Mais le gain de temps ne suffirait pas à rendre la procédure agréable.

Pour vraiment gagner du temps, il faut reprendre le problème à la base pour concevoir quelque chose qui ne conduise pas à exécuter cent millions de lignes de code.

Pour imaginer une solution, il est absolument nécessaire que vous disiez quelles sont les données, comment on doit les organiser, quelles conditions on doit satisfaire, quel résultat on doit obtenir. À défaut, je doute qu'on puisse réellement vous aider.

Mais ce n'est qu'un point de vue : il ne manque pas d'esprits avisés sur ce forum et il se peut qu'une proposition vous soit faite qui contredira mon avis. Le cas échéant, j'en serai le premier ravi.​

Bonne soirée.


ℝOGER2327
#7665

Jeudi 5 As 142 (Saint Van Meegeren, faussaire - fête Suprême Quarte)
17 Brumaire An CCXXIII, 7,5923h - cresson
2014-W45-5T18:13:18Z

 

[moteurV12]

Re : Code VBA beaucoup trop lent

J'avais, moi aussi placé un compteur précédemment.
mais c'est bien le but de ce code, maximiser le hasard compte tenu des résultats expérimentaux.
Le grand nombre de solutions analysées par la boucle pour n'arriver qu'a 200 réponses montre bien que si on compte sur le hasard pur pour arriver à quelque chose ......

Pour le reste vous avez raison, je reviens vers vous plus tard dans la soirée pour essayer de mieux m'expliquer.

Un repas agréable si ce n'est déjà fait.

 

[camarchepas]

Re : Code VBA beaucoup trop lent

Bonjour Roger , Moteur , Toutes et tous

Bon ,
cela ne fera pas des miracles , mais tu pourrais déjà effectuer un premier test si difficulté = "" then

et à l'intérieur l'ensemble des autres tests
puis donc end if

idem pour difficulté = "Facile" then

mais tout comme Roger , je ne comprends pas trop le fil suivi donc j'attends également la lumière ....

 

[moteurV12]

Re : Code VBA beaucoup trop lent

Merci camarchepas c'est effectivement une piste, mais pas celle que je recherche, une solution plus globale qui compacte le code.

Je vais donc prendre un cas précis.

Dans le fichier j'ai mis 14 en cellule AF5 et AF6, AF5 signifie que ce N° doit figurer dans les 5 chiffres de toutes combinaisons, AF6 signifie que le chiffre doit être obligatoirement le 1er des 5 chiffres.

En AK5 on a une chiffre, en l’occurrence 3, c'est la place dans la liste de référence colonne AN qu'occupe le chiffre 14.

D'autre part en V2 on a " Facile".

Cela implique que l'on recherche toutes les combinaisons de 5 et uniquement celles la qui correspondent à :

1er colonne = 14
2eme colonne = 1 des 3 premiers chiffres de la colonne AN
3eme colonne = 1 des 6 premiers de la liste de référence
4eme colonne = idem 3eme
5eme colonne = du 7eme au 11eme inclus de la liste
6eme colonne optionnelle et libre de contrainte

je pensai qu'inclure ces choix dans la boucle K = 1 to 5 ou 6 serait plus performant en vitesse de calcul.
Mais Roger parle plutôt de faire appel à l'utilisation d'une dictionnaire.

Je suis ouvert à vos suggestions ou propositions.

Bonne soirée

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Bonjour à tous.

Un essai en pièce jointe.
Le code peut sans doute être amélioré : c'est juste pour savoir si ça va dans la bonne direction...​

Bonne journée.


ℝOGER2327
#7666

Vendredi 6 As 142 (Saint Omnibus, satyre - fête Suprême Quarte)
18 Brumaire An CCXXIII, 2,3452h - dentelaire
2014-W45-6T05:37:43Z

 

[moteurV12]

Re : Code VBA beaucoup trop lent

Bonjour à tous,

Matinal Roger bonjour,

Au niveau rapidité, pas photo c'est top.

Manque la liste des exclusions W1 AJ1.
Dans tous les cas q2 doit être AN1 AN20 avec exclusions précédentes.

Si p1 vide, doit être remplacé par un des 4 premiers de AN automatiquement 1er ou 2eme
Possibilité d'avoir un p2, p3, en AG5 AH5.

Donc oui ça part dans la bonne direction et le changement de temps de traitement est vraiment spectaculaire.

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Re...

Je ne suis pas sûr de bien saisir toutes vos explications. Je joins une nouvelle version, qui n'a plus grand chose à voir avec la précédente. Voyez si vous pouvez en tirer quelque chose...​

Bonne soirée.


ℝOGER2327
#7667

Dimanche 8 As 142 (Saint Rimbe, Oisif - fête Suprême Tierce)
20 Brumaire An CCXXIII, 7,5174h - herse
2014-W46-1T18:02:30Z

 

[moteurV12]

Re : Code VBA beaucoup trop lent

Merci Roger, effectivement codage très différent que je préfère au précédent car plus clair, accessible et souple pour adaptation perso.

Une bonne et agréable soirée et merci encore.

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Re...

Merci Roger, effectivement codage très différent que je préfère au précédent car plus clair, accessible et souple pour adaptation perso.

Une bonne et agréable soirée et merci encore.

D'accord, mais est-ce que ça répond à la question ? En particulier, je n'ai pas encore compris la notion de facile ou pas facile...​

Bonne soirée.


ℝOGER2327
#7668

Dimanche 8 As 142 (Saint Rimbe, Oisif - fête Suprême Tierce)
20 Brumaire An CCXXIII, 8,0305h - herse
2014-W46-1T19:16:23Z

 

[moteurV12]

Re : Code VBA beaucoup trop lent

En fait j'avais l'impression que cela ne vous intéressai pas plus que ça, et ceci malgré un super codage.
Si je me suis trompé, je préférerai continuer en mp si vous ni voyez pas d'objection.....

 

[moteurV12]

Re : Code VBA beaucoup trop lent

Bonjour Roger

Vous me demandez si le code répond à la question ...... oui et non, disons que nous avons juste effleurés le sujet.

Pour répondre à votre interrogation Roger, "facile" est un des critères donné par un autre classeur qui va classer l’événement de 1 à 10, trés facile, facile ..... etc et qui détermine l'étendue des critères nécessaires pour l'obtention du résultat recherché. Le code allait dans le bon sens mais n'est qu'une ébauche qualitative du but recherché.

Voici la feuille finale que le code final devra gérer.

 

[ROGER2327]

Re : Code VBA beaucoup trop lent

Re...

Bonjour Roger

Vous me demandez si le code répond à la question ...... oui et non, disons que nous avons juste effleurés le sujet.

Pour répondre à votre interrogation Roger, "facile" est un des critères donné par un autre classeur qui va classer l’événement de 1 à 10, trés facile, facile ..... etc et qui détermine l'étendue des critères nécessaires pour l'obtention du résultat recherché. Le code allait dans le bon sens mais n'est qu'une ébauche qualitative du but recherché.

Voici la feuille finale que le code final devra gérer.
Regarde la pièce jointe 320785

Tant mieux si ça va dans le bon sens. Sur cette base, on peut facilement modifier le code en faisant de la procédure TaTa une fonction paramétrée fTaTa.

Si on savait exactement ce que sont toutes les contraintes et leurs domaines de variation respectifs, on gagnerait du temps...

Dans le classeur joint, vous trouverez une version qui permet de varier le nombre de partants, le choix des partants affectés à telle ou telle place, etc., avec ce que j'ai plus ou moins deviné. Voyez si ça va encore dans le bon sens...
(Le code est encore assez brut de fonderie : si vous y trouvez des erreurs, merci de les signaler !)

À propos de l'image jointe : on voit bien que c'est compliqué, mais la résolution ne permet absolument pas la lecture.​

Bonne journée.


ℝOGER2327
#7676

Mardi 10 As 142 (Saint Abstrait, bourreau - fête Suprême Quarte)
22 Brumaire An CCXXIII, 0,9870h - azerole
2014-W46-3T02:22:08Z