Optimisation de la syntaxe d'un code (pour gain en rapidité)

Optimisation de la syntaxe d'un code (pour gain en rapidité) - VB/VBA/VBS - Programmation

Marsh Posté le 21-05-2008 à 15:15:20    

Salutations,
 
Je manque encore de connaissances en syntaxe VBA, je me réfère donc à vous.
 
J'ai un bout de code qui a une durée d'exécution impossible, et j'espère que vous pourrez m'aider à l'optimiser. (essai avec 64fichiers sur un réseau = presque 2min de traitement ... à terme il y aura plusieurs centaines de fichiers à traiter...)

Code :
  1. Sub btnImport3_QuandClic()
  2. Dim NumeroLigne As Integer, i As Integer
  3. Dim NomFichier As String
  4. Dim NomDossier As String
  5. Dim J As Integer
  6. Dim K As Integer
  7. Dim L As String
  8. Dim CellOperation As String
  9. Dim CellTpsOpe As String
  11. ' On démarre à cette ligne
  12.     NumeroLigne = 9
  14.         For i = 1 To NbFichiers     'NbFichiers = Nombre de fichiers traités, c'est une variable comptée dans une autre procédure           
  15.             NomFichier = ShComplet.Range("N" & NumeroLigne)
  16.             NomDossier = BackSlashDossier(ShComplet.Range("Q" & NumeroLigne))
  17.             NomFeuille = "Chrono page 1"
  19.             With ShComplet
  20.                 .Cells(NumeroLigne, 2) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B3" )
  21.                 .Cells(NumeroLigne, 3) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "H2" )
  22.                 .Cells(NumeroLigne, 4) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "H3" )
  23.                 .Cells(NumeroLigne, 5) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B4" )
  24.                 .Cells(NumeroLigne, 6) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B2" )
  25.                 .Cells(NumeroLigne, 10) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "N37" )
  26.                 .Cells(NumeroLigne, 11) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "G4" )
  27.                 .Cells(NumeroLigne, 12) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "O1" )
  28.                 .Cells(NumeroLigne, 13) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "H1" )
  29.                 .Cells(NumeroLigne, 15) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B1" )
  30.                 .Cells(NumeroLigne, 16) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "O3" )
  32.            
  33.                 For J = 1 To 5      'Variable J représentant les 5 feuilles des fichiers Excel traités
  34.                     NomFeuille = "Chrono page " & J
  35.                    
  36.                     For K = 1 To 11         '11 cellules à extraire sur chaque page (pour les mettres à la suite dans une colonne)
  37.                         If K = 1 Then L = "B" Else
  38.                             If K = 2 Then L = "C" Else
  39.                                 If K = 3 Then L = "D" Else
  40.                                     If K = 4 Then L = "E" Else
  41.                                         If K = 5 Then L = "F" Else
  42.                                             If K = 6 Then L = "G" Else
  43.                                                 If K = 7 Then L = "H" Else
  44.                                                     If K = 8 Then L = "I"
  45.                                                         If K = 9 Then L = "J"
  46.                                                             If K = 10 Then L = "K"
  47.                                                                 If K = 11 Then L = "L"
  48.                     CellOperation = L & "7"
  49.                     CellTpsOpe = L & "42"
  50.                     .Cells(NumeroLigne, 7) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, CellOperation)
  51.                     .Cells(NumeroLigne, 8) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, CellTpsOpe)
  52.                    
  53.                     NumeroLigne = NumeroLigne + 1
  54.                     InsererLigne
  55.                     If Not Cells(NumeroLigne - 1, 7) > 0 Then Exit For     'Ce que je voudrais faire (pas réussi), c'est : s'il n'y a rien dans la cellule, on passe au fichier i suivant et non à la feuille suivante...                    
  56.                     Next
  57.                 Next
  58.                     EffacerLigne                        'Efface la ligne vide générée lorsqu'on
  59.                                                         'passe au traitement du fichier suivant
  60.             End With
  61.             Application.StatusBar = i & " / " & NbFichiers
  62.         Next
  64.         Application.StatusBar = "Terminé : " & Format((Time() - Debut) * 100000, "0.00" )
  65.         SupprimeVide
  66.     Application.ScreenUpdating = True
  67. End If
  68. End Sub
  69.  
  70. Private Function ExtraireValeur(ByVal Dossier As String, ByVal Fichier As String, _
  71.                                 ByVal Feuille As String, ByVal Cellule As String)
  72. Dim Argument As String
  73.     Dossier = Replace(Dossier, "'", "''" )
  74.     Fichier = Replace(Fichier, "'", "''" )
  75.     Feuille = Replace(Feuille, "'", "''" )
  76.     Argument = "'" & Dossier & "[" & Fichier & "]" & Feuille & "'!" & Range(Cellule).Address(, , xlR1C1)
  77.     ExtraireValeur = ExecuteExcel4Macro(Argument)
  78. End Function
  81. Private Function BackSlashDossier(ByVal TstDossier As String) As String
  82.     If Right(TstDossier, 1) <> "\" Then TstDossier = TstDossier & "\"
  83.     BackSlashDossier = TstDossier
  84. End Function
  86. Private Sub InsererLigne()
  87. Dim DerniereLigne As Long
  88.     DerniereLigne = Cells(Rows.Count, "G" ).End(xlUp).Row       'Regarde la dernière cellule utilisée dans la colonne E   
  89.     Rows(DerniereLigne + 1).Insert                             'Insert une ligne en dessous   
  90.     Range("A" & DerniereLigne, "F" & DerniereLigne).Copy Range("A" & DerniereLigne + 1) 'Copie les cellules du dessus
  91.     Range("I" & DerniereLigne, "Q" & DerniereLigne).Copy Range("I" & DerniereLigne + 1) 'Suite de la copie des cellules du dessus
  92. End Sub
  94. Private Sub EffacerLigne()
  95. Dim DerLigne As Long
  96.     DerLigne = Cells(Rows.Count, "G" ).End(xlUp).Row
  97.     Rows(DerLigne + 1).Delete
  98. End Sub
  100. Private Sub SupprimeVide()
  101. Rows(4).Insert
  102. With Range("G4", Range("G65536" ).End(xlUp))
  103. .AutoFilter field:=1, Criteria1:="=0"
  104. .SpecialCells(xlCellTypeVisible).EntireRow.Delete
  105. End With
  106. End Sub


 
Merci grandement à ceux qui vont consacrer du temps à ce post.

Reply

Marsh Posté le 21-05-2008 à 15:15:20   

Reply

Marsh Posté le 21-05-2008 à 15:22:58    

Regarde du coté de Select Case à la place de :
 
                        If K = 1 Then L = "B" Else
                            If K = 2 Then L = "C" Else
                                If K = 3 Then L = "D" Else
                                    If K = 4 Then L = "E" Else
                                        If K = 5 Then L = "F" Else
                                            If K = 6 Then L = "G" Else
                                                If K = 7 Then L = "H" Else
                                                    If K = 8 Then L = "I"
                                                        If K = 9 Then L = "J"
                                                            If K = 10 Then L = "K"
                                                                If K = 11 Then L = "L"


---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm
Reply

Marsh Posté le 21-05-2008 à 15:42:30    

Salut, à lire http://fordom.free.fr/tuto/OPTIMISATION.htm
Malheureusement en réseau il ne faut pas rêver, à part si Gigabits et encore , reste à voir si la copie des fichiers et son traitement en local sur ton PC ne serait pas plus rapide...


Message édité par kiki29 le 21-05-2008 à 15:52:43
Reply

Marsh Posté le 21-05-2008 à 15:57:34    

Merci à vous, je vais me plonger dans la lecture du lien, et dans la fonction Select Case pour voir comment elle marche

Reply

Marsh Posté le 22-05-2008 à 00:39:25    

Bonjour,
 
pour moi le pb vient de la logique ExecuteExec4Macro, pour moi, à chaque appel = Ouverture du ficher + lecture de la valeur de la cellule pointée + Fermeture du fichier...
 
Or l'algorithme à l'air de faire appel à cette fonction (5*11*2+11)*Nb fichier = 121 * 64 = 7740 Ouverture / Fermeture...
 
Heureusement qu'il y a des effets de caches pour que ça tienne, sinon, ça pourrait durer des heures...
 
Perso, je verrais plutôt qq chose du style (j'ai mis en gras les qqs points importants)
 

Code :
  1. Sub btnImport3_QuandClic()
  2. Dim CurrentWorkbook as Workbook
  3. Dim CurrentWorksheet as Worksheet
  4. // ....
  5.         For i = 1 To NbFichiers
  6.             NomFichier = ShComplet.Range("N" & NumeroLigne)
  7.             NomDossier = BackSlashDossier(ShComplet.Range("Q" & NumeroLigne))
  8.             NomFeuille = "Chrono page 1"
  9.             Set CurrentWorkbook =  Workbooks.Open(NomDossier & NomFichier) ' regarder l'aide pour plus d'options
  10.             Set CurrentWorksheet = CurrentWorkbook.Sheets(NomFeuille)
  11.             With ShComplet
  12.                 .Cells(NumeroLigne, 2) = ExtraireValeur(CurrentWorksheet, "B3" )
  13.                 // Remettre le code des autres copies ici en prenant comme exemple la ligne ci-dessus
  14.          
  15.                 For J = 1 To 5      'Variable J représentant les 5 feuilles des fichiers Excel traités
  16.                     NomFeuille = "Chrono page " & J
  17.                     Set CurrentWorksheet = CurrentWorkbook.Sheets(NomFeuille)
  18.                     For K = 1 To 11         '11 cellules à extraire sur chaque page (pour les mettres à la suite dans une colonne)
  19.                     L = Chr$(Asc("A" )+K) 'Bien plus efficace qu'un select case
  20.                     CellOperation = L & "7"
  21.                     CellTpsOpe = L & "42"
  22.                     .Cells(NumeroLigne, 7) = ExtraireValeur(CurrentWorksheet, CellOperation)
  23.                     .Cells(NumeroLigne, 8) = ExtraireValeur(CurrentWorksheet, CellTpsOpe)
  24.                  
  25.                     NumeroLigne = NumeroLigne + 1
  26.                     InsererLigne
  27.                     If Not Cells(NumeroLigne - 1, 7) > 0 Then Exit For
  28.                     Next
  29.                     If Not Cells(NumeroLigne - 1, 7) > 0 Then Exit For ' Pour sortir de la boucle sur les feuilles aussi
  30.                 Next
  31.                 CurrentWorkbook.Close
  32.                     EffacerLigne                        'Efface la ligne vide générée lorsqu'on
  33.                                                         'passe au traitement du fichier suivant
  34.             End With
  35.             Application.StatusBar = i & " / " & NbFichiers
  36.         Next
  37.         Application.StatusBar = "Terminé : " & Format((Time() - Debut) * 100000, "0.00" )
  38.         SupprimeVide
  39.     Application.ScreenUpdating = True
  40. End If
  41. End Sub
  43. Private Function ExtraireValeur(shSource as Worksheet, ByVal Cellule As String)
  44. Dim Argument As String
  45.     ExtraireValue = shSource.Range(Cellule)
  46. End Function


 
Il y a probablement 2/3 coquilles, j'ai pas essayé le code, mais ça doit pas être loin de la vérité
ça devrait aller beaucoup plus vite
 
Note Importante : il faut utiliser shComplet aussi pour les fonctions EffacerLigne et SupprimeVide pour eviter des effets de changement de workbook actif à l'ouverture
 
En espérant que ça améliore les choses


Message édité par dreameddeath le 22-05-2008 à 00:52:56
Reply

Marsh Posté le 22-05-2008 à 03:15:26    

Pour dreameddeath : Salut, justement ExecuteExec4Macro permet de lire dans un fichier sans l'ouvrir à l'écran ce qui n'est pas le cas de Workbooks.Open..... en plus si tu es en réseau avec un plug-in antivirus qui perd 2 à 3 s par fichier si on l'ouvre à l'écran je t'explique pas la cata de ta solution. Par contre Chr$(Asc("A" )+K) apparait de bon aloi.


Message édité par kiki29 le 22-05-2008 à 03:54:15
Reply

Marsh Posté le 22-05-2008 à 14:41:18    

Bonjour à tous.
Merci pour ce script ! :  
                                   L = Chr$(Asc("A" )+K)
 
En moyenne, le tps d'execution pour ces 62 fichiers test (sur réseau) était de 233 sec avec les fonctions IF
Il passait à 228sec en utilisant ElseIf
Ensuite j'ai testé en écrivant les 11 cellules K sous forme :

Code :
  1. .Cells(NumeroLigne, 7) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B7" )
  2. .Cells(NumeroLigne, 8) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "B42" )
  3. If Not Cells(NumeroLigne, 7) > 0 Then Exit For      'S'il n'y a plus d'operations, on passe au fichier suivant
  4. InsererLigne
  5. NumeroLigne = NumeroLigne + 1
  6. .Cells(NumeroLigne, 7) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "C7" )
  7. .Cells(NumeroLigne, 8) = ExtraireValeur(NomDossier, NomFichier, NomFeuille, "C42" )
  8. If Not Cells(NumeroLigne, 7) > 0 Then Exit For      'S'il n'y a plus d'operations, on passe au fichier suivant
  9. InsererLigne
  10. NumeroLigne = NumeroLigne + 1
  11.         'etc etc ...


Malgré l'encombrement du code je passais à environ 177 secondes d'execution pour les 62fichiers
 
Enfin, avec ce script qui n'encombre pas mon code je passe à 173sec de moyenne !
 
Merci beaucoup pour cette optimisation, je vais continuer à essayer de trouver des points améliorables.
 
@Kiki29 qui m'avait indirectement fourni la macro ExecuteExec4Macro via un post sur lequel j'étais tombé hehe.
Actuellement je pointe une cellule par une cellule qu'ensuite j'extrais avec cette macro...
Est ce possible de pointer plusieurs cellules avant d'exécuter cette macro pour extraire les valeurs ? Ce serait un gain de tps monstrueux.


Message édité par Silarion le 22-05-2008 à 14:42:58
Reply

Marsh Posté le 22-05-2008 à 15:10:00    

Salut, dans le cas de cellules contigues on peut utiliser ADO pour faire la même chose, suivant la quantité de cellules à rapatrier il y aura dans ce cas de contiguité un choix à faire entre les 2 méthodes pour le temps d'exécution.
 
Il y a des choix à faire également , souvent la rapidité du code se fait au détriment de la lisibilté et donc de la maintenance.


Message édité par kiki29 le 22-05-2008 à 15:13:50
Reply

Marsh Posté le 22-05-2008 à 16:21:35    

Pas facile d'optimiser quand les données sont aussi éparpillées.
Histoire de te faire gagner quelques ms je vais te suggérer de sortir

NomFeuille = "Chrono page 1"

de la boucle

For i = 1 To NbFichiers

et d'utiliser Right$ au lieu de Right (truc que tu as dû lire dans la page fournie par kiki29)
 
Comme astuce à étudier, tu peux mettre à jour une plage de cellules en utilisant un tableau VBA de variants
Encore faut-il que le remplissage du tableau ne fasse pas perdre le temps gagné sur les mises à jour .Cells(...) = ...
 
Le principe est d'avoir une variable VBA de type Range dont tu initialises la propriété .Value avec la variable tableau.
En gros (et sans contrôle) ça donne un truc du genre :


Dim vbaRange as Range
Dim tab() as variant
 
nbLignes= ...
nbColonnes=...
ReDim tab(nbLignes, nbColonnes) as variant
 
Set vbRange = ActiveWorbook.ActiveSheet.Range(...)
vbaRange.Value = tab

Reste à savoir si c'est applicable efficacement dans ton cas.
 

Reply

Marsh Posté le 23-05-2008 à 00:38:33    

Bonjour à tous,
 
sinon, je reste persuadé que le ExecuteExcel4Macro est couteux car je ne vois pas comment Excel peut faire autrement qu'une ouverture/fermeture à chaque appel en mode excel, donc je ne suis pas sûr de ce que l'on gagne...  
 
Si les fichiers sont assez petits, ça peux aller assez vite, même si on est à travers le réseau...
 
 
C'est vrai que le fait que Excel "affiche" le workbook qui est ouvert, il est vraiment possible que ça ralentisse l'execution...
 
Mais si l'on commence par faire une nouvelle instance d'excel en back ground (via un new, ça pourrait aller beaucoup plus vite non?), ça pourrait résoudre le problème (en partie du moins)...
 
Donc en reprenant mon code (toujours non testé en soit), ça donnerait :  

Code :
  1. Sub btnImport3_QuandClic()
  2.    Dim CurrentWorkbook as Workbook
  3.    Dim CurrentWorksheet as Worksheet
  4.    Dim xls_hidden as Excel.Application
  6.    [b}Set xls_hidden = New Excel.Application[/b]
  7.    //...
  8.    For i = 1 To NbFichiers
  9.             NomFichier = ShComplet.Range("N" & NumeroLigne)
  10.             NomDossier = BackSlashDossier(ShComplet.Range("Q" & NumeroLigne))
  11.             NomFeuille = "Chrono page 1"
  12.             Set CurrentWorkbook =  xls_hidden.Workbooks.Open(NomDossier & NomFichier,UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True, AddToMru:=False) ' j'ai mis les options qui me parraissaient bien
  13.            //...


 
Il serait possible d'essayer pour voir ? Je pense vraiment que ça peut valoir le coup, surtout que je ne pense pas que la modif soit très couteuse...


Message édité par dreameddeath le 23-05-2008 à 00:43:27
Reply

Marsh Posté le 23-05-2008 à 00:38:33   

Reply

Marsh Posté le 23-05-2008 à 04:10:23    

Workbooks.Open de toute façon ouvrira à l'écran le fichier
Contrairement à toi je suis sur du gain, si en plus tu es en réseau avec un plug-in antivirus qui s'active à chaque fichier excel en ouverture.... par contre via ExecuteExcel4Macro il n'y a pas d'ouverture à l'écran ni d'activation éventuelle d'un antivirus , voir http://silkyroad.developpez.com/VBA/ClasseursFermes/ sur la question
un graphe fait il y a longtemps et qui dans un contexte peut-être particulier reste sans appel sur http://cjoint.com/?fxeTJBkQCv


Message édité par kiki29 le 23-05-2008 à 05:00:07
Reply

Marsh Posté le 24-05-2008 à 12:48:31    

Je travaille dans un monde on l'ont doit traiter des centaines de millions d'enreg jours et je connais un certain nombre de principes aussi bien logiciels que matériels sur ce qui peut impacter les perfs (effets de cache, cout des fonctions système d'ouverture fermeture, latence réseau, ...).
 
Mais au cours de ces activités, j'ai surtout appris une chose : Les perfs, ça se teste dans le contexte "précis" du problème dans lequel on se trouve, surtout quand on ne sait pas comment se comporte le système (pas de modèle "fiable" ). Un truc qui marche bien dans un contexte A peu s'avérer foireux dans un contexte B, les DBA ne me contrediront pas...
 
Visiblement tu n'as jamais fait d'OLE Automation, sinon tu saurais que l'on peut utiliser Excel sans que l'utilisateur "le vois". Avant de dire que le fichier s'affiche, tu pourrais au moins le vérifier, car je l'avais fait avant d'écrire mon code.
 
Et puisque tu es sûr du gain sans même réfuter les principes de mon argumentaire (ouverture/fermeture à chaque appel, ...), j'ai voulu vérifier....
 
Pour moi ExecuteExcel4Macro ouvre et ferme le fichier à chaque appel, même si c'est une ouverture "optimisée"...
 
Donc :
1) ExecuteExcel4Macro est plus performante si on extrait peu de donnée par fichier et si le cout d'ouverture est faible (reseau = Cout bien plus important)
2) Donc j'ai voulu vérifier cela en :
    * me mettant au plus près du contexte présenté (5 feuilles, 2*11 valeurs par feuilles, ...)
    * en faisant varier la quantité de donnée à extraire par fichier (en bouclant n fois sur les 5 feuilles)
    * en faisant le test en local et en reseau (et en Wifi pour maximiser l'effet réseau)
 
J'ai fait les tests en réel, et les résultats sont surprenants.
 
J'ai voulu être assez proche du contexte énoncé au debut, donc j'ai fait ce qui suit :  
1) Je me suis créé un pool de 50 fichiers Excels de 100Ko environ contenant 5 feuilles chacun (via macro avec save as). Pour simplifier tous les fichiers et toutes les feuilles sont identiques
2) Pour comparer j'ai fait l'algorithme suivant :
     * Je boucle sur les 50 fichiers
     * Pour chaque fichier je boucle un certain nombre de fois sur :
         * Je boucle sur les 5 feuilles
         * Si c'est la feuille 1, Je récupère 11 valeurs sur la feuille (cellule k,k)
         * Pour tout les feuilles (les 5), je récupère 2*11 valeurs (cellule k,k et k+20,k)
3) évidemment, j'ai fait le test en ExecuteExcel4Macro et via workbook.open avec le new Excel.application
 
Les résultats sont :
 
En local :
N_internal_loop 1 (121 données/fichiers):
     ExecExcel4Macro = 6,78 s
     Workbook.Open   = 12,51s
N_internal_loop 2 (242 données/fichiers):
     ExecExcel4Macro = 13,23 s
     Workbook.Open   = 15,11 s
N_internal_loop 3 (363 données/fichiers): Open est déjà plus performant
     ExecExcel4Macro = 19,73 s
     Workbook.Open   = 18,71 s
N_internal_loop 4 (484 données/fichiers):
     ExecExcel4Macro = 26,23 s
     Workbook.Open   = 21,14 s
 
On voit bien que extraire + de données d'un même fichier est + couteux en ExecExcel4Macro qu'en Background Workbook.Open et qu'à un moment ExecuteExcel4Macro devient moins performant
 
En réseau (via wifi) :
N_internal_loop 1 (121 données/fichiers):
     ExecExcel4Macro = 439 s
     Workbook.Open   = 36,5 s
N_internal_loop 2 (242 données/fichiers):
     ExecExcel4Macro = 906 s
     Workbook.Open   = 37,7 s
 
Là le score est sans appel : le workbook.open est infiniment plus rapide...
 
Conclusion
 
Je ne dis pas que ça marchera mieux car :
1) Je n'ai fait que récupérer la valeur (value = ...), et donc, je n'ai pas simulé tout ce qui se trouve autour (les autres fonctions)
2) Le fait par exemple que le Chr$(Asc("A" )+k) est fait gagné autant me laisse à penser que le gain sera beaucoup moins important que ce que j'ai pu avoir...
 
Néanmoins, je maintiens que ça vaut le coup d'essayer
 
PS: j'ai mis ce le fichier de donnée et le fichier excel de la macro dans le zip à http://cjoint.com/?fymVbTNzbv
PPS : merci pour le service Cjoint, je ne le connaissais pas et c'est bien pratique (même pas de login/pwd)
 
Cordialement,


Message édité par dreameddeath le 24-05-2008 à 13:04:36
Reply

Marsh Posté le 24-05-2008 à 15:02:22    

Salut, cela ne fait que confirmer ce que j'ai déjà dit plus haut.
Pour l'avoir expérimenté x fois depuis des années je sais qu'il y a un seuil
en nb de cellules à lire au delà duquel une méthode l'emporte sur l'autre.
et qu'ADO devient intéressant dans le cas de contiguité des cellules.
 
Par contre dans la réalité j'ai souvent rencontré des plug-in antivirus qui sont activés et là
tu verras la justesse des méthodes ExecExcel4Macro / ADO , car malheureusement c'est 2 à 3 s de  
perdues par fichier à ouvrir, voir plus
 
http://silkyroad.developpez.com/VBA/ClasseursFermes/
 
 ExecuteExcel4Macro avec/sans AV  
1 5,89s      
2 11,77s      
 BackGround sans AntiVirus
1 13,76 s  
2 13,22 s  
 BackGround avec AntiVirus
1 166,50 s
2 132,94 s
 
Et cela confirme ce que je disais plus haut et que tu mettais en doute quant à l'ouverture des fichiers Excel


Message édité par kiki29 le 24-05-2008 à 18:15:58
Reply

Marsh Posté le 24-05-2008 à 20:05:12    

Donc si j'extrapole, même avec antivirus, il est fort probable que ça soit plus rapide en workbook.open à travers le réseau (enfin, de type Wifi)...
 
Sinon, visiblement, il y a de gros effets de cache, car il n'est pas logique autrement que l'on gagne du temps en faisant plus de chose (et en background, on gagne à chaque fois pour le Nb_Internal_Loop à 2)...
 
Sinon, sache que je n'ai jamais remis en cause le fait que le temps d'ouverture était plus long en Open, et encore moins que l'antivirus s'activait pour open et non pour ExecExcel4Macro.
 
Ce que je remets en cause, c'est que tu semblais (mais j'ai peut-être mal interprété) dire à Silaron : "teste pas la solution de Dreameddeath, il n'y a aucune chance qu'elle améliore la situation"...
 
Hors mon test montre que dans le cas réseau, il n'y a pas photo entre Open et ExecuteExcel4Macro, et j'ai tendance à penser que même avec l'antivirus, open garderait l'avantage.
 
Là encore, je ne dis pas que ça va forcément améliorer son cas (je ne sais même pas s'il a un plugin antivirus dans Excel), mais que mon test montre que l'on peut gagner énormément en cas idéal...
 
Je persiste à dire à Silaron qu'il faut tenter le coup (j'ai quand même un facteur 10 de gain sur mon test Unitaire)...


Message édité par dreameddeath le 24-05-2008 à 20:05:37
Reply

Marsh Posté le 24-05-2008 à 20:11:35    

Ton extrapolation est hasardeuse, les chiffres fournis par ton benchmark sont là pour le prouver, d'autant plus que la quantité de cellules à lire est faible.Si le "client" a du WiFi à lui d'essayer


Message édité par kiki29 le 24-05-2008 à 20:19:21
Reply

Marsh Posté le 25-05-2008 à 11:04:05    

Hasardeuse ?
 
Pas plus que tes affirmations :

Citation :


Workbooks.Open de toute façon ouvrira à l'écran le fichier
Contrairement à toi je suis sur du gain, si en plus tu es en réseau


Que de certitudes non éttayées par des faits qui volent en éclat dès que confrontés à la réalité avec code pour le vérifier chez toi à l'appui...

Citation :


Salut, cela ne fait que confirmer ce que j'ai déjà dit plus haut.  
Pour l'avoir expérimenté x fois depuis des années je sais qu'il y a un seuil  
en nb de cellules à lire au delà duquel une méthode l'emporte sur l'autre.  


Tu as dis ça plus haut? Où ça?
 
La seule chose que tu as montré c'est un graphe ne fait dire que quoi qu'il arrive, les perfs sont meilleures en ExecuteExcel4Macro sans possibilité de croisement, grâce à pente bien plus avantageuse pour ExecuteExcel4Macro, "fait" qui a été contredit par tes propres chiffres, certes basés sur mon fichier Excel a priori, 2 posts plus loins, où dès 250 données/fichier, on revient presqu'à l'équilibre Open/ExecuteExcel4Macro...  
 
Mais bon reste campé sur tes positions à tout prix, c'est toujours comme cela que l'on avance

Reply

Marsh Posté le 25-05-2008 à 16:20:42    

Et toi de même , Non ? j'ai bien précisé pour le graphe : "un graphe fait il y a longtemps et qui dans un contexte peut-être particulier reste sans appel ", encore faudrait-il que tu saches lire.Le plug-in AV apporte la preuve que même si la fenêtre n'apparait pas à l'écran que le fichier est "ouvert" qqpart contrairement à ExecuteExcel4Macro.Tes affirmations sont dans le presque,à peu près,et OLE ....
Dans toutes les boites ou j'ai eu à mettre en oeuvre ce genre de solution jamais un administrateur n'a accepté de désactiver son AV sur les fichiers Office ce qui oblige à passer par ExecuteExcel4Macro ou ADO


Message édité par kiki29 le 25-05-2008 à 16:42:03
Reply

Marsh Posté le 25-05-2008 à 18:30:03    

Sinon, pour en arrêter avec le débat qui peut être sans fin sur le Exec/Open, on va repartir sur les autres pistes d'optims...  
 
Avec tout le code que j'ai écrit, j'ai fini pas (je crois) comprendre ce tu voulais faire (Silaron)
 
Excel n'aime généralement pas quand on l'oblige à "reparcourir" toute la feuille quand on fait une lecture/ecriture ligne par ligne
 
Donc pour moi, il faudrait mieux remplacer les  
 

Code :
  1. .Cells(NumeroLine,...) =


 
par un
 

Code :
  1. Dim rgCurrLine as Range
  3. Set rgCurrLine = shComplet.Rows(NumeroLine)
  4. ...
  5. rgCurrLine.Cells(1,...) = ....


 
ça doit un peu accélérer
 
Ensuite la fonction InsertLigne me chiffone un petit peu, car elle est appellé à chaque boucle sur "k", il devrait être possible de ne l'executer qu'à chaque fichiers, vu qu'il s'agit de recopier les cellules A:E et I:Q...
 
Pour moi :
1) Pour moi le copy peut fonctionner avec comme destination un "range" en "tableau"
2) Pour moi faire Rows(...).insert n'est pas nécessaire (un tableau fait toujours 65535 cellules)
 
 
Donc :
1) il faudrait sauvegarder la ligne courante au début de fichier
2) à la fin d'un fichier appeler une fonction de type "FinalizeTableau"
3) FinalizeTableau pourrait être du type :
    a) Récupérer la dernière ligne du tableau (ligne courante)
    b) faire une copie sur l'ensemble debut:fin du tableau qui vient d'être 'ajouté' dans le cadre du traitement du fichier en cours
 
Ainsi, qqch du style de ce qui suit à la fin de chaque fichier en lieu et place des InsertLigne/EffacerLigne pourrait s'avérer payant, car ça evite de faire 121 copies de lignes par fichiers (1 seule copie en une seule passe)

Code :
  1. Private Sub FinalizeTableau(num_row_debut as Integer, num_row_fin as Integer)
  2.    Range("A" & num_row_debut, "F" & num_row_debut).Copy Range("A" & num_row_debut  & ":A" & num_row_fin)
  3.    Range("I" & num_row_debut, "Q" & num_row_debut).Copy Range("I" & num_row_debut  & ":I"  & num_row_fin)
  4. End Sub


 
voilà en espérant que ça apporte un peu d'eau au moulin "des optims" potentielles


Message édité par dreameddeath le 25-05-2008 à 18:34:21
Reply

Marsh Posté le 27-05-2008 à 09:51:36    

Bonjour Dreameddeath, bonjour kiki29,
 
Merci pour le temps que vous consacrez à ce post qui m'aide beaucoup.
 
@Dreameddeath :  
J'avais prévu de tester ta solution dès que possible dans tous les cas, elle n'a jamais été exclue  ;) . Au pire ça m'aurait fait progresser encore en VB :) ou même rebondir sur une autre partie du code. J'ai eu quelques empêchements mais je vais regarder ça dès aujourd'hui. Je vais également étudier les petites astuces dans ton dernier post. Merci pour le temps que tu consacres à ce post.
 
Je vous transmettrai les résultats des tests dans ce post sans aucun parti prit, j'ai bien saisi qu'un code peut parfaitement bien fonctionner selon le cas traité et le système.

Reply

Marsh Posté le 27-05-2008 à 14:40:53    

Et bien dans ma situation, et sur le système réseau sur lequel je suis, la solution d'ouvrir/fermer les fichiers (sans les afficher à l'écran) via xls_hidden.Workbooks.open, semble optimale ! L'exécution du code sur mes 62 fichiers tests se fait en 24 secondes   :love:   en moyenne, contre 173 sec dans mon précédent code.
 
Merci infiniment !
Impressionnant les différences obtenues pour la même opération avec des algorithmes / syntaxes différents  :pt1cable:  (avec mon tout premier code, je faisais cette même opération en plus de 780secondes...
 
Pour un éventuel futur lecteur du forum, voici l'algorithme que j'ai pour le moment : (Maintenant je vais essayer de faire un code propre en me penchant sur le bricolage insérer ligne / effacer ligne ou autres déclarations de variables acrobatiques  :jap:  
 

Code :
  1. For i = 1 To NbFichiers
  2.             NomFichier = ShComplet.Range("N" & NumeroLigne)
  3.             NomDossier = BackSlashDossier(ShComplet.Range("Q" & NumeroLigne))
  4.             NomFeuille = "Chrono page 1"
  5.             Set CurrentWorkbook = xls_hidden.Workbooks.Open(NomDossier & NomFichier, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True, AddToMru:=False)
  6.             Set CurrentWorksheet = CurrentWorkbook.Sheets(NomFeuille)
  8.        
  9.         'B3 H2 H3 B4 B2 B7-L7 B42-L42 H4 N37 C5 H1 B1 O3
  10.             With ShComplet
  11.                 .Cells(NumeroLigne, 2) = ExtraireValeur(CurrentWorksheet, "B3" )
  12.                 .Cells(NumeroLigne, 3) = ExtraireValeur(CurrentWorksheet, "H2" )
  13.                 .Cells(NumeroLigne, 4) = ExtraireValeur(CurrentWorksheet, "H3" )
  14.                 .Cells(NumeroLigne, 5) = ExtraireValeur(CurrentWorksheet, "B4" )
  15.                 .Cells(NumeroLigne, 6) = ExtraireValeur(CurrentWorksheet, "B2" )
  16.                 .Cells(NumeroLigne, 10) = ExtraireValeur(CurrentWorksheet, "N37" )
  17.                 .Cells(NumeroLigne, 11) = ExtraireValeur(CurrentWorksheet, "G4" )
  18.                 .Cells(NumeroLigne, 12) = ExtraireValeur(CurrentWorksheet, "O1" )
  19.                 .Cells(NumeroLigne, 13) = ExtraireValeur(CurrentWorksheet, "H1" )
  20.                 .Cells(NumeroLigne, 15) = ExtraireValeur(CurrentWorksheet, "B1" )
  21.                 .Cells(NumeroLigne, 16) = ExtraireValeur(CurrentWorksheet, "O3" )
  23.            
  24.                 For J = 1 To 5      'Variable J représentant les 5 feuilles des fichiers Chronos Excel
  25.                     NomFeuille = "Chrono page " & J
  26.                     Set CurrentWorksheet = CurrentWorkbook.Sheets(NomFeuille)
  27.                    
  28.                     For K = 1 To 11         '11 cellules à extraire sur chaque page (pour les mettres à la suite dans une colonne)                    L = Chr$(Asc("A" ) + K)
  29.                     CellOperation = L & "7"
  30.                     CellTpsOpe = L & "42"
  31.                     .Cells(NumeroLigne, 7) = ExtraireValeur(CurrentWorksheet, CellOperation)
  32.                     .Cells(NumeroLigne, 8) = ExtraireValeur(CurrentWorksheet, CellTpsOpe)
  33.                     NumeroLigne = NumeroLigne + 1
  34.                     InsererLigne
  35.                    
  36.                     If Not Cells(NumeroLigne - 1, 7) > 0 Then Exit For      'S'il n'y a plus d'operations, on passe au fichier suivant                   
  37.                     Next
  38.                     If Not Cells(NumeroLigne - 1, 7) > 0 Then Exit For
  39.                 Next
  40.                     EffacerLigne                        'Efface la ligne vide générée lorsqu'on                    NumeroLigne = NumeroLigne - 1       'passe au traitement du fichier suivant           
  41. End With
  42.             CurrentWorkbook.Close SaveChanges:=False
  43.             Application.StatusBar = i & " / " & NbFichiers
  44.         Next
  46. Private Function ExtraireValeur(shSource As Worksheet, ByVal Cellule As String)Dim Argument As String
  47.     ExtraireValeur = shSource.Range(Cellule)
  48. End Function
  50. Private Sub InsererLigne()Dim DerniereLigne As Long
  51. ShComplet.Activate
  52.     DerniereLigne = Cells(Rows.Count, "G" ).End(xlUp).Row       'Regarde la dernière cellule utilisée dans la colonne E    Rows(DerniereLigne + 1).Insert                             'Insert une ligne en dessous
  53.     Range("A" & DerniereLigne, "F" & DerniereLigne).Copy Range("A" & DerniereLigne + 1) 'Copie les cellules du dessus
  54.     Range("I" & DerniereLigne, "Q" & DerniereLigne).Copy Range("I" & DerniereLigne + 1) 'Suite de la copie des cellules du dessus
  55. End Sub
  57. Private Sub EffacerLigne()
  58. Dim DerLigne As Long
  59. ShComplet.Activate
  60.     DerLigne = Cells(Rows.Count, "G" ).End(xlUp).Row
  61.     Rows(DerLigne + 1).Delete
  62.     Rows(DerLigne + 1).Delete
  63. End Sub


Message édité par Silarion le 27-05-2008 à 14:42:00
Reply

Marsh Posté le 27-05-2008 à 19:54:30    

Merci de dire merci,
 
en fait, je l'admet, ça n'aurait bien pu ne pas marcher (surtout si tu avais le fameux plug-in AV), mais le "modèle" que j'avais monté de ExecExcel4Macro me laissait vraiment penser qu'il y avait un gain énorme...
 
La pratique n'a fait que valider le modèle, mais ce n'est pas toujours le cas...
 
Sinon, Silaron, je ne sais pas ce que t'en penses, mais pour moi, il faut plutôt oublier les optims, car je pense que tu ne gagneras presque rien maintenant (4/5 secs max pour moi)
 
La seule chose à faire est de "nettoyer" le code : par exemple le .Cells(NumLine,...) par rgCurrLine.Cells(1,...) semble de bon aloi ainsi que l'ajout suppression de ligne à chaque boucle...
 
Dernier point, surtout déclarer toutes les variables avec un type différent de variant (cela vaut aussi pour les retours de fonctions) : il n'y a que très peu de cas ou variant est vraiment obligatoire ...
 
Sinon, bon courage pour la suite, mais pour moi sur les perfs, il y 95% du possible déjà fait, il ne faut pas en faire plus sauf si tu as vraiment beaucoup de temps...
 
A +


Message édité par dreameddeath le 27-05-2008 à 19:55:22
Reply

Marsh Posté le 28-05-2008 à 09:31:03    

Yep je m'arrête là, je vais juste essayer de nettoyer un peu, mais je suis très satisfait du temps d'exécution du code maintenant  :) Je crois aussi que les éventuels gains possibles sont négligeables par rapport à ce qui a été fait. Après à la limite si j'ai du temps ... mais pas pour le moment ;)
 
Je passe donc à une autre partie du code (je vais me pencher du côté des userforms maintenant, mais faut que je me renseigne un peu)
 
Salutations, et merci pour ton aide !

Reply

Marsh Posté le 10-08-2016 à 16:57:51    

Bonjour,
 
Je me suis inscris sur ce forum juste pour te dire merci dreameddeath!
J'ai pu remplacer ExecuteExcel4Macro par ta méthode et ça marche du feu de dieu.
 
Ma macro déroule désormais en 15 secondes au lieu de 10 minutes ...
 
La biz

Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed