Vitesse VBA excel

asterrax -  
Bonjour,
J'ai un fichier contenant 1 048 576 lignes et trois colonnes et je cherche à faire des calculs comme des moyennes mobiles sur la totalité de une de mes trois colonnes.

Cependant j'ai un problème de lenteur de mon code et je n'ai qu'un peu plus de 140 000 lignes qui sont calculées. Pour l'instant je n'ai qu'un calcul sur mon code soit une boucle.

Une illustration :

For i=1 to 1 048 576
cells(i,4)=cells(i,2)+2
Next i

Comment arriver à faire ce simple calcul par exemple jusqu'à la dernière ligne ou comment optimiser la rapidité de ce calcul?
Je travaille sur excel 64 bits.
Merci

5 réponses

  1.  gbinforme Contributeur
    bonjour,

    Comme ceci cela devrait être plus rapide (20 secondes pour moi) : 

    Dim i As Long
Application.Calculation = xlCalculationManual
For i = 1 To 1048576
    Cells(i, 4) = Cells(i, 2) + 2
Next i
Application.Calculation = xlCalculationAutomatic
    1. asterrax Auteur
      effectivement cette technique est superbe!

      Application.Calculation = xlCalculationManual
      Application.Calculation = xlCalculationAutomatic
      je n'ai jamais vu ces lignes mais ça rend la boucle très rapide!
      je pense que je vais utiliser ces codes tout le temps maintenant!!!

      merci beaucoup!!!!!
      :)
      1. gbinforme Contributeur
        En fait tu inhibes le calcul qui est inutile à chaque modification de ta boucle pour ne faire le calcul qu'en fin de traitement.
      2. asterrax Auteur
        ok merci beaucoup, c'est un code que l'on devrait systématiquement écrire pour optimiser la vitesse! même sur 100 lignes, on peut voir la différence!
        :)
    2. michel_m Contributeur
      Bonjour

      jusqu'à 65536 lignes (les variables tableaux ne vont que jusque là) 

      Sub speedy_vba() 
Start = Timer 

T_in = Application.Transpose(Columns("B")) 
T_out = Application.Transpose(Columns("D")) 
For cptr = 1 To UBound(T_in) 
     T_out(cptr) = T_in(cptr) + 2 
Next 

Application.ScreenUpdating = False 
Range("D1:D65536") = Application.Transpose(T_out) 

MsgBox Timer - Start & " secondes" 

End Sub

      Michel
      1. gbinforme Contributeur
        Bonjour Michel,

        J'étais resté dans la logique du demandeur, mais il est évident que lorsque l'on fonctionne avec des arrays, les vitesses de traitement n'ont strictement rien à voir en rapidité.

        Il faut par contre pouvoir les utiliser car ce n'est pas toujours aussi simple et l'on a éventuellement d'autres blocages comme cela m'est arrivé récemment où les fonctions de gestions des arrays ne me le permettaient pas.

        Merci en tout cas de ta suggestion qui est totalement adaptée : à asterrax d'en faire bon usage avec un tuto assez complet pour l'utilisation.
    3. Le Pingou
      Bonjour,
      Hier soir j'avais posté la solution qui suit et permet de traiter les 1048576 lignes , 
      Option Base 1
Sub col4_col2_2()
Dim temp(1048576, 1)
t = Timer
colB = [B1:B1048576]
For c = 1 To UBound(colB)
    temp(c, 1) = colB(c, 1) + 2
Next c
[d1].Resize(UBound(temp, 1), UBound(temp, 2)) = temp
MsgBox Timer - t
End Sub
      1. michel_m Contributeur
        Bonjour Le Pingou,

        Je viens d'essayer ta procédure c'est OK pour =<8 sec chez moi (vieux coucou)

        bin, je ne comprend pas pourquoi j'ai eu une limite à 65536...

        Je regarderai ca à la fraiche demain

        Merci en tout cas
      2. gbinforme Contributeur
        Bonjour Michel, Le Pingou,

        je ne comprend pas pourquoi j'ai eu une limite à 65536...

        Quand on est uptodate avec 2007 faut aussi sauvegarder en xlsm et non xls limité à 65536 ;-)

        Pas de souci les arrays sont beaucoup, beaucoup, beaucoup, plus performants puisqu'il n'ont pas d'adresse à recalculer : chez moi 0.3 secondes au lieu de 20 ! mais ce sont les chiffres habituels constatés ;-)
      3. michel_m Contributeur
        Ok, j'ai vu mais ça ne résout pas le pourquoi du comment ! :o/

        c'est le transpose qui limite à 65536
        T_in = Application.Transpose(Columns("B"))
        et ca fonctionne avec
        T_in=[B1:B1048576]

        Bonne soirée
      4. gbinforme Contributeur
        c'est le transpose qui limite à 65536 T_in =Application.Transpose(Columns("B"))

        Normal en colonne B sur un xls tu n'as que 65536 mais en 2007 tu peux utiliser en mémoire 1048576 qui ne seront pas sauvegardés même avec
        Range("D:D") = Application.Transpose(T_out) si tu as un classeur xls
      5. michel_m Contributeur
        bonjour,
        ben non, c'était en xlsm !....

        mais je me dis que des tableaux avec autant de lignes, 65000 ou plus, ne sont pas trop réalistes (utilisation Access?) et reste un cas d'école ou d'essai des limites d'Excel.

        en attendant, c'est la proposition de le Pingou qui reste la + efficace
    5. Le Pingou
      Bonjour gbinforme, michel_m,
      Sauf erreur de ma part il s'agit de la limite native de la fonction TRANSPOSE qui est de : 65'536
      Un petit test pour voir, si vous dépassez la valeur 65536 vous avez une Erreur d'exécution 13 : 
      Sub test()
X = Application.Transpose(Application.Transpose(Evaluate("ROW(1:70000)")))
Range("A1:A70000").Value = X
End Sub

      Salutations.
      Le Pingou