Gagner quelques précieuses secondes sur un programme VBA [résolu]

[stephsteph]

Bonjour,
Ma démarche est très inhabituelle, je l'admet.
J'ai un VBA (excel 2000) qui marche OK (bien sûr, ce n'est pas moi qui l'est écrit).
A l'origine (2001), sur un PC à 400 Mhz, le programme tournait en 4h30, mais je ne le faisais tourner que une fois par an et le nombre de lignes de base était de l'ordre de 3000.
Aujourd'hui avec mon i5, il met un peu plus de 90 mn même si le nombre de lignes de base a triplé.
Mon problème : je dois le faire tourner maintenant au moins une fois par semaine et cela devient un poids.
Je me demande si quelqu'un de calé pourrait jeter un coup d’œil sur le code pour l'alléger et le "rapidifier" (histoire de gagner quelques secondes ou dixièmes de secondes par ligne traitée).
Si une personne du forum réagit favorablement je mettrai le code (rien de bien sorcier, c'est très mécaniste) dans le prochain message.
Merci d'avance
Steph

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Merci beaucoup Pierre-Jean,
Pour l'instant, le débogueur bloque à la ligne (surlignée en jaune) suivante:
ActiveWorkbook.Worksheets("Feuil2").Sort.SortFields.Clear
Comme il est très tard, je ne dois plus avoir les yeux en face des trous.
Un petit souci, c'est qu'il vient de me tomber dessus un déplacement pro de 5 jours.
Donc il faut me pardonner, mais sauf si j'y arrive demain (gros fichier compris), je ne pourrai pas revenir vers toi tout de suite.
Pas vexée pour 2 sous pour les blagounettes !
Et puis, pour le nez, le mien est bien ordinaire (rien à voir avec Cléopatre), mais pas question de le faire refaire avec tous ces problèmes sur les prothèses PIP!!!
Encore merci
Steph

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re
Question bête:
As-tu mis une Feuil2 dans le fichier indexweb ?

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re
Ayant testé sous Excel 2000 et ayant le debug sur la même ligne , je suppose que 2007 ne supporte pas non plus cette ligne
Voici la version revue sous Excel 2000

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour Pierre Jean
Me voici de retour et après plusieurs heures Dimanche et hier soir, je suis perplexe.
J'ai fait tourner et c'est 100% OK.
Seulement voilà, quand je copie le gros fichier précédent à la place du précédent "échantillon" ou même en dessous de l'échantillon, j'ai l'erreur suivante dès le début.

Erreur d'exécution '9'. L'indice n'appartient pas à la sélection.

avec la ligne suivante surlignée en jaune par le débogueur.
tabres(2, UBound(tabres, 2)) = tablo(m, 2) & tablo(m, 3) & tablo(m, 4) & "{" & tablo(m, 5) & tablo(m, 6) & "}:" & Split(tablo(m, n), ":")(1)

Malheureusement cette Erreur d'exécution '9' est une bouteille à l'encre (y compris sur ce forum) et j'ai trouvé plein de topics similaires mais aucun avec le type d'instruction ci-dessus.
Je me suis juste demandée si dans mon échantillon il n'y avait pas 3 confusions qui pouvaient faire planter la macro:
- la première est que l'année en colonne C est parfois en texte, comme 1989a, ou 2003f
- la seconde est que mes codes d'exemples de 1 à 9 (avant les ":" séparateurs) sont en réalité des textes de longueur variable (de 3 à 30 caractères parfois même en 2 morceaux, par exemple "Blablebli:" ou "abdcef Zyxw.:") et qu'il y en a aujourd'hui plus de 2000 différents
- la troisième est que le fichier réel est très gros en nombre de lignes, que les 7 premières colonnes sont toujours remplies (notamment la 7ème colonne avec le 1er code) mais que le nombre de codes dans une ligne varie de 1 (au moins) à environ 150, avec tous les intermédiaires possibles.

Si la confusion vient de moi j'en suis vraiment désolée.

Merci encore, Steph

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re
Peux-tu selectionner quelques lignes du fichier réel avec les caracteristiques que tu cites ,et poster le fichier ainsi constitué pour me permettre de faire des tests ??

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour Pierre Jean,
Aïe, aïe, aïe, c'est pas simple.
J'ai eu une idée toute simple, c'est de modifier mon fichier échantillon en incorporant des contenus qui miment complètement la réalité (année avec 1976a. et codes avec les 2 types de possibilités comme "Blablebli:" ou "abdcef Zyxw.:").
Mais cela marche toujours !!!
Quel os! (je joins quand même ce fichier)
Envoyer sur le Net un fichier réel ne serait pas opérant car il faudrait beaucoup de lignes pour avoir avec une forte probabilité des codes identiques dans au moins 2 lignes (rappel il y a au moins 2000 codes différents et au moins 5 fois plus de lignes).
Donc mon hypothèse restante concerne la taille du fichier, mais cela peut être sans rapport avec le problème.
Cela va te titiller peut-être, moi je suis total larguée.
Merci Steph

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re
Comme tu le dis cela me titille un zest
J'entrevois une possibilité qui me met en cause, moi et ma manie d'utiliser la possiblité qu'offre Excel de se dispenser de declarer les variables
Voici donc une version avec les dites declarations de variable
Vois si cela va mieux pour ton gros fichier
Sinon contacte moi par Message privé : je te donnerai une adresse ou envoyer ton gros fichier , ce qui me permettra de tester en vraie grandeur (discretion garantie)

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour Pierre Jean

Merci de ton aide, voici le résultat des courses.
- avec les 4 lignes, çà marche (pas de surprise)
- avec les 4 lignes + tout le gros fichier, çà bugge avec erreur d'exécution '9' (comme avant) pour la ligne
tabres(2, UBound(tabres, 2)) = tablo(m, 2) & tablo(m, 3) & tablo(m, 4) & "{" & tablo(m, 5) & tablo(m, 6) & "}:" & Split(tablo(m, n), ":")(1)
- avec les 4 lignes +95 lignes du gros fichier, çà bugge avec erreur d'exécution '13' pour la ligne
Sheets("Feuil2").Range("A1").Resize(UBound(tabres, 2), 3) = Application.Transpose(tabres)
- avec les 4 lignes +45 lignes du gros fichier, çà bugge avec erreur d'exécution '13' pour la ligne
Sheets("Feuil2").Range("A1").Resize(UBound(tabres, 2), 3) = Application.Transpose(tabres)
- avec les 4 lignes +25 lignes du gros fichier, çà bugge avec erreur d'exécution '13' pour la ligne
Sheets("Feuil2").Range("A1").Resize(UBound(tabres, 2), 3) = Application.Transpose(tabres)
- avec les 4 lignes +5 lignes du gros fichier, çà marche !! (il se trouve que, hasard, une des nouvelles lignes possède 15 codes, les autres de 1 à 3 codes seulement)
- avec les 4 lignes +7 lignes du gros fichier, dont 2 lignes ajoutées très longues avec 116 et 126 codes (en dessous de la ligne 95 précédente, donc en dehors des cas 3-4-5), çà bugge avec erreur d'exécution '13' pour la ligne
Sheets("Feuil2").Range("A1").Resize(UBound(tabres, 2), 3) = Application.Transpose(tabres)
Elémentaire mon cher Watson !!!
Là tu es encore plus titillé.
A ta dispo et merci encore !
Et si nécessaire message privé, il faudra m'expliquer le mode de fonctionnement !
Steph

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re

Si ton fichier avec les 95 lignes du gros fichiers peut-etre posté (eventuellement zippé) , je travaillerais dessus
Sinon vois le MP que je t'adresse

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour Pierre Jean

Il faut se méfier des hypothèses restantes qui deviennent soi-disant solides.
Erreur de ma part, cela ne semble pas un problème de taille.
J'ai en effet par hasard testé une seule ligne pour voir.
La plus longue est OK.
La 2ème plus longue n'est pas OK.

Et le problème devient alors très sioux, car j'ai testé en enlevant des cellules de code peu à peu (c'est très long, plus de 100).
Pour me rendre compte avec une seule cellule de code (en colonne 7) que ce n'était toujours pas OK (pas OK = erreur '13').
Alors j'ai tatonné, pour me rendre compte que le texte ", errata corrigenda." dans la colonne 6 faisait tout bugger.
Mais une fois enlevé avec toutes les cellules, ce n'était toujours pas OK.
J'ai repeigné cellule de code par cellule de code, car par hasard j'ai vu qu'avec l'une d'entre elles, c'était OK.
Pour finir, c'est un type de cellule de code qui bugge, mais c'est très sioux, à savoir (selon contenu de la cellule c'est OK ou pas OK):
Austrolampo: @gonus/Sys OK, Austrolampo: @gonus/Sys/Ost OK, Austrolampo: @gonus/Sys/Ost/Dis pas OK, Austrolampo: @gonus/Ost/Dis/Sys pas OK, Austrolampo: Sys/Ost/Dis/@gonus pas OK, Austrolampo: Ost/Dis/Sys/@gonus pas OK, Austrolampo: Sys/Ost/Dis/@valor pas OK, Austrolampo: Sys/Ost/Dis/gonus pas OK, Banane: @gonus/Sys/Ost/Dis pas OK, mais Austrolampo: Sys/Ost/Dis OK
Incroyable. Et très inquiétant pour moi, car avec la vieille macro je n'avais pas d'erreur (il y a un système de signalement des erreurs), alors j'ai vérifié si la cellule avec "errata corrigenda" aurait pu être scratchée sans que je m'en rende compte... mais non tout est bien là.
Donc en résumé il semblerait que la nouvelle macro n'aime pas certaines choses, mais que je ne peux pas le prévoir.
J'ai fait un autre test avec 10 lignes prises au hasard et parfois cela marche, parfois cela bugge et j'ai commencé à regarder si je retrouvais errata corrigenda ou bien @gonus/Sys/Ost/Dis, mais non cela devait être encore autre chose...
J'avoue que je suis dans un état pas possible...
J'ai bien reçu ton MP et je peux t'envoyer un fichier de 100 lignes avec des bugs, mais avant il me semblait plus raisonnable de poster le message général ci-dessus pour que tu vois si c'est pas autre chose.

Merci 100 fois et désolée de te mettre dans cette situation
Steph

 

[pierrejean]

Re : Gagner quelques précieuses secondes sur un programme VBA

Re

Je viens de tester avec les austrolampo diverses et cela fonctionne aussi bien sous Excel 2000 que sous Excel 2010
Il va te falloir poster un fichier sinon on n'en sortira pas

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour Pierre Jean
De plus en plus mystérieux !
J'ai envoyé un extrait du fichier.
Et je me croise les doigts.
Merci, Steph

 

[stephsteph]

Re : Gagner quelques précieuses secondes sur un programme VBA

Bonjour à tous,
La suite s'est passée en direct avec Pierre Jean.
Et grâce à sa bonne volonté, son expertise, sa gentillesse, sa modestie, toutes ses qualités (je sais il va rougir, mais ce n'est pas une blagounette), le code a pu être finalisé et surtout le temps de réalisation du gros fichier avec plusieurs milliers de lignes, Excel 2000 et un i7 (cette fois) est passé de 53 mn.... à moins de 2 mn (avec le même ordi, oui!)

donc je suis aux anges et je dis merci 1000 fois à Pierre Jean!

pour les membres il me semble normal de mettre le fichier xls avec la macro VBA finale en ligne en souhaitant que cela profite à qui le souhaite.
Un fil pleinement résolu
Encore merci, Steph