Accélération code VBA

Question

Je voudrais savoir s'il y a possibilité d'accélérer ce code VBA : Dim i As Long Application.ScreenUpdating = False For i = 2 To &#91;A65000&#93;.End(xlUp).Row If Cells(i, 1).Interior.ColorIndex = xlNone Then Cells(i, 1).EntireRow.Hidden = True End If Next i Merci d'avance pour votre aide...

Usb512 · Answer

Essaye peut-être ceci Dim Plage As Object Dim Cellule As Range Application.ScreenUpdating = False Set Plage = Range("A2:A" &amp; &#91;A65000&#93;.End(xlUp).Row) For Each Cellule In Plage If Cellule.Interior.ColorIndex = xlNone Then Cellule.EntireRow.Hidden = True Next Cellule Set Plage = Nothing Mais je ne suis pas certain pour la rapidité Mytå Edition : Ajout de Application.ScreenUpdating = False pour comparer des Pommes avec des Pommes...

Nad-Dan · Answer

Comme je l'avais déjà mentionné dans une autre fil pour Claude Dubois, évite les Crochets &#91; &#93; dans le code qui ralentissent l'exécution. Il vaut mieux mettre Range("A65000"). Amicalement Dan Edit Dan : code supprimé le 31/08/09...

galopin01 · Answer

Topikalakon ! Ce topic aurait aussi pu s'appeler se méfier des interprètations simplistes... Je me suis livré à une petite étude sur la base des indications fournies dont vous trouverez le détail en pièce jointe. Pour résumer, je me suis livré à trente tests sur la macro de Myta trente test sur la macro de Nad-Dan trente test sur ma propre macro trente test sur ma probre macro version crochets &#91; &#93; Pour que les tests soient significatifs dans la première colonne les 65000 premières lignes ont été remplies de "x" Pour chacun, les dix premiers tests concernaient 1 ligne sur 2 à masquer pour les dix suivants 9 lignes sur 10 étaient incolores (donc à msquer !) pour les dix derniers 1 lignes sur 10 étaient incolore (...) Si l'on considère la seule première batterie de test (1/2) La macro de Myta met presque 43 secondes à s'exécuter et toutes les autres moins de 5 secondes... And the winner is... ??? Galopin bien sur ! Je n'ai d'ailleurs pas grand mérite : j'ai juste repris et améliorées les macros des uns et des autres ! Tous les détails des résultats dans le fichier A+...

cousinhub · Answer

Testalakon...... Mettre une 2CV dans un grand prix de F1.... Facile, pour Ferrari.... Euh, Galopin, mets toutes les écuries sur le même pied d'égalité..... Commence par mettre : Application.ScreenUpdating = False dans le code de Mytå.... Et recommence ton "Test"..... Pour moi,; le code de Mytå est le plus rapide..... Désolé......

galopin01 · Answer

Bah, tu n'as pas à être désolé...

Le but n'était pas de modifier les macros des uns et des autres, mais de vérifier ce qui était dit et fait, de faire mieux si possible... et de vérifier l'assertion au sujet des crochets [ ].

Bien entendu il est possible de recommencer le test avec ScreenUpdating= False dans la macro de Myta et il est vrai que... c'est encore plus rapide dans tous les cas !

A+

Nad-Dan · Answer

Re, Assez bizarre tout de même cette histoire de crochets sachant que ces conseils sont donnés par Microsoft lui même dans ce lien : http://support.microsoft.com/kb/104502/fr Mais aussi par J. Walkenbach : Related to this is the shortcut method of refering to cells. VBA will allow you reference cells with &#91;A1&#93; rather than Range("A1"). While the &#91;A1&#93; syntax is easier to type, it is slower to execute than the Range("A1") syntax. On se demanderait bien comment ils ont contrôlé cela. Amicalement Dan...

galopin01 · Answer

C'est tout à fait vrai. Sur la base de mon test de ce matin, j'ai refait un test sur 2 macros srictement identiques dont l'une fait appel à range et l'autre utilise des crochets. Sub TestRange() Dim i&amp; Application.ScreenUpdating = False For i = 2 To Range("A65536").End(xlUp).Row If Cells(i, 1).Interior.ColorIndex = xlNone Then Rows(i).Hidden = True Next i End Sub Sub TestCrochets() Dim i&amp; Application.ScreenUpdating = False For i = 2 To &#91;A65536&#93;.End(xlUp).Row If Cells(i, 1).Interior.ColorIndex = xlNone Then Rows(i).Hidden = True Next i End Sub Le nombre de boucles est de 65000 et les tests ont été réalisés 10 fois par macros sur une batterie de tests les moyennes des résultats respectifs sont les suivants : 5.203125 et 5.2046875 La méthode Timer donne des temps avec une précision de l'ordre du millième. Ont peut donc dire que sur cet exemple l'avantage est de 15/1000 pour laméthode Range. Sur d'autres séries de tests identiques j'ai mesuré des écarts moyens avec une différence jusqu'à 160/1000 ! C'est négligeable sans doute car dans cette démo le nombre d'appels est relativement limité. On peut donc en déduire que pour un appel unique à : i=&#91;A65536&#93;.End(xlUp).Row La notation par crochet ne pose alors aucun problème. Mais on imagine sans peine que certaines boucles peuvent évaluer des crochets plusieurs centaines de milliers de fois et dans ce cas le gain est réel... Le A+...

cousinhub · Answer

Pour ma part, je pense que le fait de travailler dans un tableau VBA, comme le fait Mytå (Set Plage....) donne un gain significatif... D'une part, un calcul dans un tableau VBA est nettement plus rapide Et ensuite, le calcul de la dernière ligne n'est fait qu'une seule fois : &#91;A65000&#93;.End(xlUp).Row Maintenant, le code de Dan utilisait également un tableau, tu me diras... Oui, mais le contrôle de la couleur se fait quand même sur l'onglet, il incrémente toutes les cellules, une à une (comme le font tous les autres codes, d'ailleurs...) avec le code de Mytå, on incrémente dans le tableau VBA... Peut-être une explication??...

galopin01 · Answer

Bbonjour,

Je n'avais pas envie de répondre à cette question sur laquelle je me suis déjà suffisament exprimé, mais je vais quand même m'y coller.

Quand on fait des tests de ce genre et qu'on les rend publique, ça veut dire qu'on a un minimum de rigueur scientifique. et un maximum d'objectivité dans l'analyse de ces résultats.

Donc on ne peut pas dire que -tel quel- le code de Myta est plus rapide alors qu'il est 10 fois plus lent... La faute en est qu'il a oublié le ScreenUpdating mais la moindre des choses est de dire que tu as forcément corrigé ce "détail" avant d'affirmer qu'il est plus rapide...

cousinhub a donné une partie des précisions qui manquaient : Une fois corrigé le code de myta est plus rapide car il travaille sur une instance et non pas sur le tableau lui même, Ce qui s'apparente à un travail sur Array et est toujours au minimum 10 fois plus rapide qu'un travail sur Sheet. (car une instance d'un tableau restreint est entièrement chargée en mémoire) et ne comporte aucun des attributs du classeur ou du tableau d'origine. Un véritable Array ne comporte que des données brutes pas de couleur, pas de format, pas de bordures, pas de liens hypertextes, pas d'images... donc l'accès et les calculs sont forcement plus simples.

Ici Myta ne travaille pas sur un Array mais sur une instance de plage et qui conserve encore quelques attributs. Le principal intéret me semble être le travail sur une zone mémoire restreinte, ce qui lui confère encore un net avantage. Cette zone mémoire est forcément plus réduite car elle n'a pas à explorer tout le territoire d'un classeur... ni même d'une feuille. Rien que des données Range. Le gain est donc du même ordre

Au moins dans des conditions significatives : J'entend par là qu'il faut travailler sur un nombre significatif de lignes pour obtenir un résultat significatif.

Parlons en. Il me parait curieux qu'un code sur 1800 lignes mette autant de temps à s'éxécuter qu'un autre sur 65000 lignes mébon...

De plus il manque une précision importante dans l'exposé de vba-new : c'est le nombre de lignes réellement explorées :

La méthode ...End(xlUp).Row) ne retient que les lignes contenant des données pas des couleurs en d'autre terme si ton tableau contient 65000 lignes dont seulement les 25 premières sont remplies la méthode ne renvoie qu'une plage de 25 lignes...

Le code de Dan : Bien qu'il comporte le mots Tablo en fait celui-ci ne sert à rien, je l'ai d'ailleurs discrètement supprimé de mon test car il ne faisait que ralentir le code. Ce tableau n'était pas chargé avec la plage de référence juste dimensionné à la valeur de j (End(xlUp).Row)... mais parfaitement inutile.

La mesure des différences entre Range et crochets : Pour être rigoureux, il faut utiliser ma deuxième démo qui ne comporte aucune autre différence que ces fameux crochets et travailler sur un minimum de boucles et faire plusieurs tests... En dessous de 50 000 boucles et une dizaines de tests la mesure parait insuffisante pour un gain aussi infinitésimal : Sur cette batterie de tests et sur 25 000 lignes seulement on arrive à des résultats parfois contradictoires.

En pratique on ne se soucie guère de tels détails sur des macros qui durent moins d'une dizaine de secondes. Les sources de gains les plus significatives sont ailleurs... On commence la plupart du temps à s'y intéresser sur des macros qui durent plusieurs minutes, voire plusieurs heures.

C'est toutes ces imprécisions que j'ai sanctionné par un "topikalakon" qui se voulait plus humoristique que qualitatif : Quand on veut parler mesure et évaluation, il faut être rigoureux et prouver ce qu'on affirme.

Ce que j'ai essayé de préciser ici.

A+

dubois · Answer

Je ne sais pas le faire, mais en cherchant du coté: SpecialCells(Type, Valeur) Exemple d'un code qui fonctionne Sub SupprimeLigne() 'Supprime lignes dont colonne A est en texte Dim Lg As Long, Plg As Range Lg = Range("A65536").End(xlUp).Row On Error Resume Next Set Plg = Range("a1:a" &amp; Lg).SpecialCells(xlTextValues, 2) Plg.EntireRow.Delete End Sub en adaptant le Type xl Set Plg = Range("a1:a" &amp; Lg).SpecialCells(xlTextValues, 2) avec On ne traite que les cellules concernées Votre avis sur la faisabilité ? Bonne journée Claude...

cousinhub · Answer

Galopin..... Ce qui a été fait, il me semble.... PS, toi-même, tu t'es empressé de le mettre, à ce que je vois.... Pas de soucis, tu as sans doute testé avec le "détail"..... Euh, je ne ne pense pas qu'il y avait de la place, pour ce type d'embrouille, et comme tu le précises toi-même, on ne joue que sur des dixièmes, voire centièmes de secondes... Donc, inutile de se prendre la tête... Au plaisir, de te croiser sur un autre fil @ Claude, le Type des cellules "Spéciales" est défini... Pour les valeurs : et pour les Types : De plus, selon le nombre de lignes, cette méthode n'est pas forcément la plus rapide.... Bonne soirée à Tous...

Nad-Dan · Answer

Re, Vu le "topikalakon" et "interprétations simplistes", le code n'apporte rien au forum et à ce qui est dit ici. Je l'ai donc supprimé de mon post du jeudi 27/08. Dan...

galopin01 · Answer

Bon, on va arrêter là. J'ai bien intégré qu'on est sur un forum de débutant et je n'ai pas l'habitude de pinailler sur des queues de cerises , Seulement si on laisse toujours dire et faire n'importe quoi, à la longue ça n'a plus ni queue ni tête. C'est pourquoi j'ai cru bon faire remarquer que ce topic marqué résolu était un peu... léger. C'est un Forum sur Excel, pas sur la dialectique et ma remarque ne se voulait pas aussi désobligeante que ramenée aux seuls mots qui fâchent. A ceux qui se sont senti inutilement agressés qu'ils veulent bien m'en excuser. Pour ma part la question est close et je n'y reviendrai pas. A+...