XL 2016 Augmenter la vitesse de FOR

[morest38]

Salut,
Voilà j'ai ce code qui marche parfaitement.

Sub DesignAUTO()

Application.ScreenUpdating = False
Range("A5:D5000") = ""

y = 4
'loop 1
For i002 = 1 To 57

'loop 2
For i003 = 1 to 20

'loop 3
For i004 = 1 To 5

'loop 4
For i005 = 1 To 6
y = y + 1

Cells(y, 1) = i002
Cells(y, 2) = i003
Cells(y, 3) = i004
Cells(y, 4) = i005

Next
Next
Next
Next

Application.ScreenUpdating = True
End Sub

Le problème est qu'il peut y avoir un énorme nombre de cas, du coup excel est extrêmement lent pour réaliser l'intégralité.

Comment faire pour améliorer sa vitesse? Je pense que j'utilise une fonction qui restera lente mais je ne vois pas comment la remplacer.

Merci d'avance.

 

[Dranreb]

bonjour.
Ce qui est surtout lent ce sont vos 136800 accès à une(des) cellule(s)
Un seul accès serait environ 136800 fois plus rapide (le nombre de cellules accédées lors de chacun d'eux est presque sans importance).
Vous auriez donc tout intérêt à préparer vos résultats dans un tableau VBA en mémoire que vous déposeriez dans la plage en une seule fois tout à la fin.

 

[morest38]

Re et merci pour la réponse,
Mais ces accès sont le départ d'un calcul plus complexe qui se réalise sur la fiche de calcul. Je pense que les array peuvent être la solution et encore pas sûr.
Je vais creuser d'avantage.

 

[Paf]

bonjour morest38, Dranreb,

La seule solution pour accélérer votre sub est celle préconisée par Dranreb ( passer par un tableau qui sera "collé" dans la feuille)

s'il y a un calcul complexe sur chaque cellule écrite par votre sub, il faut sans doute l'intégrer à la nouvelle sub pour bénéficier des avantages d'un tableau ?

A+

Edit: Sur mon vieux PC votre sub demande 6,9 secondes, contre 0,6 secondes avec utilisation d'un tableau.

 

[morest38]

Salut Paf,
Ok je n'avais pas compris le conseil de Dranreb ainsi, effectivement un tableau vba serait intéressant surtout si il se décharge en 10 fois moins de temps.
Excusez mon ignorance mais comment réaliser ce genre de tableau ainsi que le décharger ensuite dans le classeur .
Merci encore.

 

[Paf]

re,

Pour adapter votre sub, pas grandes modifications :

en tête de sub,déclarer un tableau à 2 dimension pour accueillir les données, par ex :

Dim T1(1 To 34200, 1 To 4)

remplacer

y = 4

par

y = 0

et

Cells(y, 1) = i002
Cells(y, 2) =...
...

par

T1(y, 1) = i002
T1(y, 2) =...
...

et après le dernier Next, ajouter la ligne pour copier dans la feuille:

Range("A5").Resize(UBound(T1, 1), UBound(T1, 2)) = T1

A+

NB: curieusement lors de mes essais j'avais écrit :Dim T1(1 To 57 * 20 * 5 * 6, 1 To 4), mais j'ai un message d"erreur : erreur d'exécution : 6 . Dépassement de capacité ??
Je ne comprend pas quelle capacité est dépassée, surtout que 1 to 34200 fonctionne.
Quelqu'un aurait une explication ?

 

[gosselien]

Bonjour, à tous

comme le disent les petits camarades de classe, un seul accès est bien rapide via un tableau

Sub DesignAUTO1()
Application.ScreenUpdating = False
Range("A5:D5000").ClearContents
Dim t()
ReDim t(1 To 50000, 1 To 4)
y = 4
'loop 1
For i002 = 1 To 57
'loop 2
   For i003 = 1 To 20
'loop 3
      For i004 = 1 To 5
'loop 4
         For i005 = 1 To 6
            y = y + 1
            t(y, 1) = i002
            t(y, 2) = i003
            t(y, 3) = i004
            t(y, 4) = i005
         Next
      Next
   Next
Next
[A5].Resize(UBound(t), 4) = t
Application.ScreenUpdating = True
End Sub

 

[Dranreb]

lors de mes essais j'avais écrit :Dim T1(1 To 57 * 20 * 5 * 6, 1 To 4), mais j'ai un message d"erreur

Dim T1(1 To 57& * 20 * 5 * 6, 1 To 4) devrait mieux passer parce qu'il n'essayerait plus d'évaluer cette multiplication en Integer mais en Long.

 

[Paf]

re,

Merci Dranreb, encore une notion qui m'échappait!

A+