Vitesse For comparé à While ??

[dominique]

Salut !

J'ai crée un petit code VBA qui teste pas moins de 5000 lignes et l'execution prend quand meme 5 a 10 sec pr 1200 lignes testées ....
J'ai utilisé une boucle for puis while pr comparer la vitesse. Moi je n'ai pas vu de différence, qu'en pensez vous ?

voila mon code :

Sheets("Data").Activate
Sheets("Data").Select

Worksheets("Data").Range("B1").Sort Key1:=Worksheets("Data").Columns("B"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom

Worksheets("Data").Range("B2").Activate

'i = 7

'While ActiveCell.Offset(1, 0).Value <> ""
For j = 1 To Sheets("commandes").Range("D6").Value - 1 Step 1

If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then

ActiveCell.Offset(1, 0).Select

Else

Worksheets("divers").Range("H" & j + 6).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

'i = i + 1

End If

'Wend

Next


Voila voila, vous pensez que je devrais plutot utiliser un while ou un for ? merci pour vos conseils !!

a++
dom.

 

[stef]

salut Dominique

je n'ai pas la reponse a ta question mais tu peux etre fixé en utilisant la fonction 'Timer' dans VBA je crois. Tu initialise une variable en debut de code et une autre en fin de code. La difference des deux te donne le temps exact ecoulé. Y'a pas plus precis ..... ;-)

 

[dominique]

OK merci de l'info
le temps pr un for et un while est quasiement similaire.
Je me suis dis que c'etait le If qui prenait beaucoup de temps (6sec pr 1200lignes c tres lent!).
Finalement, en desactivant le screen updating, l'execution du code s'effectue en moins d'1 sec !

Donc info pour ceux qui testent des lignes, DESACTIVEZ LE SCREEN UPDATING, cela vous rendra service

a++

 

[Ti]

pas trop compris ce que tu cherches à faire dans ta macro, mais tant que tu y mettras des select, tu n'avanceras qu'à la vitesse d'un tracteur.
Donc pour accélérer, il y a plusieurs solutions : la première est d'utiliser une boucle du genre
For each Cel In Plage
Next Cel

La deuxième est, comme je l'ai dit, de supprimer tous les Select

La troisième, radicale, est de passer par un Tableau (du tracteur tu passes à la Ferrarri), mais cette dernière solution n'est pas toujours utilisable, selon ce que tu veux extraire de tes cellules, en outre, elle exige de bonnes connaissances en programmation.

 

[dominique]

salut Ti,

merci pr tes conseils, j'aimerais juste préciser un petit point sur ce que tu as dis,

tu disais que c'est l'appel a objet.select qui ralentissait mais en fait ce n'est pas tout a fait exact. Select est lent car il update l'écran et c'est le screen updating qui est lent ... donc si tu mets ton screenupdating a False tu vas voir que le select est tres rapide !

a++
dom.

 

[JPH]

J'avais cela quelque part, après dépoussierrage voici pour mesurer les performances des boucles dans le sens de ta question, pour cause de place sur le Forum le fichier est coupé, mais en allant sur :

http://www.fdjeux.com/resultat/telecharger_loto.php

Tu télécharges les résultats du Loto environ 3000 lignes, tu doubles le fichier par un copié/collé pour avoir environ 7000 lignes, car pour un PIV 2,5Mhz je voudrai bien connaître la différence !!!
Avec des appareils trop rapides ctrl C/Ctrl V pour 14000lignes
Avec un Pentium III 800Mhz voilà les résultats
00:00:01 For/Each - Events désactivés
00:00:01 For/Each - Events activés
00:00:05 For/next - Events activés
00:00:05 Do/Loop - Events désactivés
00:00:06 While/Wend - Events activés
00:00:06 For/next - Events désactivés
00:00:06 While/Wend - Events désactivés
00:00:06 Do/Loop - Events activés
00:00:08 For/next + select - Events désactivés
00:01:07 For/next + select - Events activés
De une minute à une seconde, j'ai pris en compte la colonne 4 à 10 mais en modifiant les scripts tout est permis.
Je ne m'explique pas les performances du "For/next - Events activés" et suis étonné du "Do/Loop - Events désactivés"
Voir le fichier joint.

Cordialement

 

[Ti]

non, dominique, évidemment si tu désactives les mises à jour d'écran, tu vas un peu plus vite, mais crois-moi, les Select, c'est lent, c'est ainsi.

 

[@+Thierry]

Bonsoir les gens de ce fil,

Moi, c'est simple, ceux qui me connaissent savent que je suis en guerre avec Select !! et il est indéniable que lorsque l'on n'a pas besoin rééllement de faire une selection, même en utilisant le paliatif ScreenUpdating à false, c'est vraiment pas le top au niveau rapidité... D'ailleurs le très beau test de JPH ne laisse pas d'équivoque possible. (bravo JPH !)

En voyant le EnableEvents à False, je me demandais si aussi, selon le type de modif que l'on fait sur la plage d'une feuille qui pourrait, par exemple, être en lien sur des formules... cette instruction empéchait l"évènement Calculate ?

Auquel cas le EnableEvents ne stop pas le Calculate, alors là aussi il y a un énorme gain de temps à commencer la procédure par :

Application.Calculation = xlCalculationManual

et, of course la terminer par :
Application.Calculation = xlCalculationAutomatic

A moins que celà fasse double emploi ?

Si quelqu'un à la réponse... Merci d'avance

Bonne Soirée
@+Thierry

 

[JPH]

Bonjour et merci Thierry pour ta réponse.
Voilà le resultat du test avec 7000 lignes et en colonne dix la somme des cls de 4 à 9.
00:00:03 For/Each - Events activés
00:00:04 For/Each - Events désactivés
00:00:09 For/next - Events activés
00:00:11 While/Wend - Events activés
00:00:11 Do/Loop - Events activés
00:00:16 For/next - Events désactivés
00:00:17 Do/Loop - Events désactivés
00:00:18 While/Wend - Events désactivés
00:00:24 For/next + select - Events désactivés
00:02:29 For/next + select - Events activés
Application.Calculation = xlCalculationManual, avec les Events désactivés, paradoxalement "For/Each - Events activés" est plus rapide d'une seconde !!!
For/next - Events activés est plus rapide de 7 secondes, etc ...
dans ce 2ème test dans désactivés, il faut comprendre uniquement :
Application.Calculation = xlCalculationManual

00:00:04 For/Each - Events désactivés
00:00:05 For/Each - Events activés
00:00:15 For/next - Events activés
00:00:16 Do/Loop - Events activés
00:00:16 Do/Loop - Events désactivés
00:00:16 For/next - Events désactivés
00:00:18 While/Wend - Events activés
00:00:18 While/Wend - Events désactivés
Donc le gain de désactiver les events est de 1 seconde par rapport à la désactivation du calcul uniquement.

Cordialement
Ps dans le test 2 j'ai bloqué select 2mn29 ouf!!!

 

[LaurentTBT]

Bonsoir tout le monde.

Thierry, regarde ce petit exemple:

avec A2: =A1+1

je fais tourner la macro suivante avec un point d'arrêt à Range("A1")=3 en la lançant en mettant 6 en A1:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1") = 3
Application.EnableEvents = True
End Sub

Lorsque la macro écrit 3 en A1, l'événement worksheet_change n'est pas relancé, mais le calcul de la cellule A2 est quand même effectué, alors que si je rajoute Application.Calculation = xlCalculationManual
avant, il ne l'est pas.
J'en déduis donc que enableEvents=false n'empèche pas le calculate.

Bonne nuit.

 

[@+Thierry]

Re JPH, le Fil

Donc si je te suis bien, c'est la preuve que les deux doivent être désactivé...

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
End With


Car dans ton test loto, çà ne joue pas énormément, mais imagines que l'on fasse écrire par VBA une valeur dans chaque cellules de la colonne B si la colonne A remplis une condition, alors que dans les Colonnes C, D, E etc tu as des formules qui pointent sur B !!!

Bon ben on le saura !!

Bonne Nuit

@+Thierry
PS

Au fait j'ai pas été sur le Site Loto, mais j'ai fait un drag sur 7000 lignes sur le fichier tel que tu l'as posté, et ma veille guimbarde (PIII 800, 512 RAM, WinME, Office 2000 Pro, donne ceci ) :
00:00:01 For/Each - Events activés
00:00:02 For/Each - Events désactivés
00:00:03 While/Wend - Events activés
00:00:03 For/next - Events désactivés
00:00:03 For/next - Events activés
00:00:03 While/Wend - Events désactivés
00:00:03 For/next + select - Events désactivés
00:00:03 Do/Loop - Events désactivés
00:00:04 Do/Loop - Events activés
00:00:29 For/next + select - Events activés


Je suis surpris de la différence avec toi pour :
00:01:07 For/next + select - Events activés
C'est ptet la RAM ...

 

[@+Thierry]

Bonsoir et Merci Laurent

Donc pour accélérer les grosses boucles qui travaillent sur des fichiers contenant des formules qui vont recalculer les modifs engendrée par VBA :

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Et pas de Select !!!!!!!

Et ne pas oublier de rétablir, sinon gare à vous !!

Bonne Nuit
@+Thierry

 

[JPH]

Pour paufiner j'avais fait une somme "=SOMME(C1:I1)" sur les 7000 lignes, mais sans les calculs j'arrive au même temps car visiblement nous avons les mêmes machines PIII 800, 256 RAM, WinME, Office 2000 Pro).
Enfin ce n'est pas évident même en poussant sur 14000 lignes les gains ne sont pas impressionants, tout réside dans le code, par exemple si tu fais ceci:
'For Each cellule In Range("D1:J" & derlig)
'If cellule = nbx Then cpt = cpt + 1
'Next
00:00:02 For/Each - Events désactivés
For Each cellule In Range("D1:J" & derlig)
Var = cellule
If Var = nbx Then cpt = cpt + 1
Next
00:00:06 For/Each - Events désactivés
Soit le simple attribut d'une variable triple le temps !!!
J'espère que quelqu'un sur P4 2,5Mhz fera le test, rien que pour voir si l'on met nos machines à la casse.

Cordialement

 

[@+Thierry]

RE bonsoir JPH,

Je ferais çà au bureau Lundi, j'ai de la grosse artillerie !!! En fait j'ai une collection de Pentium !! Du P1, au PIV , an passant par un PII ;-)

C'est sur le PII (400) que je fais tous les développement, si çà passe là je suis tranquille sur notre park.

Bonne Nuit
@+Thierry

 

[Moa]

Salut les gars !

J'ai une macro qui me sert pour trier de 34 000 à 55 000 lignes sur une largeur de 50 à 100 colonnes.

Dans chaque cellule, des formules, de la plus simple : NB.SI(C15:O15;"1")

à des plus complexes : SI(1+$AF$4>$AH$7;"0";SOMME(SI($AG$4=2;NB.SI(C1515;$AH$4));SI($AG$4=3;NB.SI(C15:E15;$AH$4));SI($AG$4=4;NB.SI(C15:F15;$AH$4));SI($AG$4=5;NB.SI(C15:G15;$AH$4));SI($AG$4=6;NB.SI(C15:H15;$AH$4));SI($AG$4=7;NB.SI(C15:I15;$AH$4));SI($AG$4=8;NB.SI(C15:J15;$AH$4));SI($AG$4=9;NB.SI(C15:K15;$AH$4));SI($AG$4=10;NB.SI(C15:L15;$AH$4));SI($AG$4=11;NB.SI(C15:M15;$AH$4));SI($AG$4=12;NB.SI(C15:N15;$AH$4));SI($AG$4=13;NB.SI(C15:O15;$AH$4))))

ou encore :SI(NB.SI(DP15:EA15;"11")>=1;1;0)+SI(NB.SI(DP15:EA15;"1N")>=1;1;0)+SI(NB.SI(DP15:EA15;"12")>=1;1;0)+SI(NB.SI(DP15:EA15;"N1")>=1;1;0)+SI(NB.SI(DP15:EA15;"NN")>=1;1;0)+SI(NB.SI(DP15:EA15;"N2")>=1;1;0)+SI(NB.SI(DP15:EA15;"21")>=1;1;0)+SI(NB.SI(DP15:EA15;"2N")>=1;1;0)+SI(NB.SI(DP15:EA15;"22")>=1;1;0)

Et avec des formules matricielles, ou des decaler faisant appel à d'autres formules etc...

Avec un Athlon 2000, 1Ghz ram, Xp et Office Pro 2000, ça rame pas mal, et autant vous dire que j'ai largement le temps d'aller me faire un café de le laisser un peu réfroidir et de le boire....héhéhé...!

En fait toutes ces formules croisées m'affichent 1, ou 0 ou Faux.

En colonne "A" j'ai une formule qui écrit "Faux", dès l'instant que j'ai un seul "Faux" dans ma ligne.

Puis j'ai une macro qui teste la colonne "A" et me delete toutes les lignes correspondante.

Voici ma macro :

Sub DeleteFaux()
'
' DeleteFaux Macro
' Macro enregistrée le 21/02/2003 par Moa

Sheets("Filtrage").Select

Application.Calculation = xlManual

Sheets("Filtrage").Activate
i = 1
While Range("A15").Offset(i).Value <> ""
If Range("A15").Offset(i).Value = False Then
Range("A15").Offset(i).EntireRow.Select
Selection.Delete Shift:=xlUp

i = i - 1
End If
i = i + 1
Wend
Application.Calculation = xlCalculationAutomatic

Call RazFiltres
Call CompteLigneFiltrage
Call MajFormules

End Sub

J'ai supprimé volontairement le "Application.ScreenUpdating = False", car de temps en temps le Pc Plante pendant cette macro, et je ne le vois pas.

J'ai éssayé de modifier pendant des heures cette macro afin de la rendre plus rapide.

Mais mon niveau en Vba est vraiment trop trop Débutant.

Donc, je pense que vous allez pouvoir m'aider à modifier cette macro.

Du moins si l'envie vous en prend, je vous remercie Grandement d'avance.

Merci

@ +

Moa