Vitesse VBA excel

Fermé

asterrax Messages postés 12 Date d'inscription vendredi 9 mars 2012 Statut Membre Dernière intervention 16 décembre 2012 - 21 avril 2012 à 18:44
Le Pingou Messages postés 12182 Date d'inscription mercredi 11 août 2004 Statut Contributeur Dernière intervention 7 novembre 2024 - 23 avril 2012 à 10:31

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

A voir également:

Vitesse VBA excel
Vitesse processeur - Guide
Liste déroulante excel - Guide
Si et excel - Guide
Test vitesse ssd - Guide
Aller à la ligne excel - Guide

5 réponses

Réponse 1 / 5

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 705
21 avril 2012 à 21:57

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

Réponse 2 / 5

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 310
Modifié par michel_m le 22/04/2012 à 10:12

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

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 705
22 avril 2012 à 17:35

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.

Réponse 3 / 5

asterrax Messages postés 12 Date d'inscription vendredi 9 mars 2012 Statut Membre Dernière intervention 16 décembre 2012
21 avril 2012 à 22:24

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!!!!!
:)

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 705
21 avril 2012 à 22:33

En fait tu inhibes le calcul qui est inutile à chaque modification de ta boucle pour ne faire le calcul qu'en fin de traitement.

asterrax Messages postés 12 Date d'inscription vendredi 9 mars 2012 Statut Membre Dernière intervention 16 décembre 2012
21 avril 2012 à 23:09

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!
:)

Réponse 4 / 5

Le Pingou Messages postés 12182 Date d'inscription mercredi 11 août 2004 Statut Contributeur Dernière intervention 7 novembre 2024 1 448
22 avril 2012 à 18:10

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

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 310
22 avril 2012 à 18:37

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

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 705
Modifié par gbinforme le 22/04/2012 à 18:54

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 ;-)

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 310
22 avril 2012 à 18:52

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

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 705
22 avril 2012 à 18:59

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

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 310
23 avril 2012 à 08:30

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

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

Posez votre question

Réponse 5 / 5

Le Pingou Messages postés 12182 Date d'inscription mercredi 11 août 2004 Statut Contributeur Dernière intervention 7 novembre 2024 1 448
Modifié par Le Pingou le 23/04/2012 à 10:43

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