Vitesse VBA excel

asterrax Messages postés 13 Statut Membre -  
Le Pingou Messages postés 12274 Date d'inscription   Statut Contributeur Dernière intervention   -
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 Messages postés 14930 Date d'inscription   Statut Contributeur Dernière intervention   4 744
     
    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
  2. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
     
    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
    1. gbinforme Messages postés 14930 Date d'inscription   Statut Contributeur Dernière intervention   4 744
       
      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.
      0
  3. asterrax Messages postés 13 Statut Membre
     
    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!!!!!
    :)
    0
    1. gbinforme Messages postés 14930 Date d'inscription   Statut Contributeur Dernière intervention   4 744
       
      En fait tu inhibes le calcul qui est inutile à chaque modification de ta boucle pour ne faire le calcul qu'en fin de traitement.
      0
    2. asterrax Messages postés 13 Statut Membre
       
      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!
      :)
      0
  4. Le Pingou Messages postés 12274 Date d'inscription   Statut Contributeur Dernière intervention   1 476
     
    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
    0
    1. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      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
      0
    2. gbinforme Messages postés 14930 Date d'inscription   Statut Contributeur Dernière intervention   4 744
       
      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 ;-)
      0
    3. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      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
      0
    4. gbinforme Messages postés 14930 Date d'inscription   Statut Contributeur Dernière intervention   4 744
       
      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
      0
    5. michel_m Messages postés 18903 Date d'inscription   Statut Contributeur Dernière intervention   3 320
       
      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
      0

  5. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. Le Pingou Messages postés 12274 Date d'inscription   Statut Contributeur Dernière intervention   1 476
     
    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
    0