amélioration de code pour accelerer les copiés collés

[seb33]

bonjour à tous


bon, quand on en est là, c'est que le programme sent la fin de développement

lorsque j'ai rappatrié la DB access vers excel, je récupère les données.
ensuite, il faut, en fonction des macros, que je dispatch les données récupérées vers les bonnes cellules.

les cellules n'étant pas homogènes (elles ne se suivent pas car en fonction des macros, les données ne vont pas dans les mêmes cellules, et pas toutes ).

j'ai testé:
sheets("xx").cells(2,2)=sheets("yyy).cells(1,2)
meme chose avec range
même chose avec

Sheets("Calcul").Range("AG133").Copy
Sheets("Calcul").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

j'ai mis le maximum de boucles (là ou je ne pouvais).

bilan: entre 1m50 et 2mn10 selon les données et les méthodes.

ce log devant servir de base sur une 50 aines de tests par jour (et sur du process non rémunéré puisque simple rapatriement de données pour ensuite vraiment travailler), bonjour la perte de temps.

existe t il une solution pour accélérer un peu les copiés collés sans changer les PC (il est neuf, donc normalement il doit être au top)
gracie mille

 

[job75]

Re : amélioration de code pour accelerer les copiés collés

Bonjour seb33,

Quelques questions :

- combien de cellules sont modifiées ?

- y a-t-il beaucoup de formules recalculées et combien ?

- y a-t-il dans les feuilles des macros Worksheet_Change ?

A+

 

[seb33]

Re : amélioration de code pour accelerer les copiés collés

bonjour job75

non, il y a des worksheets change (la base est sur une feuille et les cibles dans une autre).
mais pas VBA en worksheets change.

combien de données? cellule par cellule environ 200-300 en comptant le nombre de valeurs dans les différentes boucles), mais cela peut varier fortement puisqu'il y a une condition de copié collé (la valeur d'origine doit être <>"" ou <>0 selon les cibles, c'est impératif pour réduire le temps et surtout pour les formules futures)

mais ce n'est pas de la reformulation, du simple copié collé sans .Value en fin de ligne

plus simple, voici une partie du code:

'Calcul
If Sheets("Calcul").Range("AU133") <> 0 Then
Sheets("Calcul").Range("D17") = Sheets("Calcul").Range("AU133")
End If
If Sheets("Calcul").Range("AV133") <> 0 Then
Sheets("Calcul").Range("C18") = Sheets("Calcul").Range("AV133")
End If
If Sheets("Calcul").Range("AW133") <> 0 Then
Sheets("Calcul").Range("D18") = Sheets("Calcul").Range("AW133")
End If
If Sheets("Calcul").Range("AZ133") <> 0 Then
Sheets("Calcul").Range("C20") = Sheets("Calcul").Range("AZ133")
End If
If Sheets("Calcul").Range("BA133") <> "" Then
Sheets("Calcul").Range("D20") = Sheets("Calcul").Range("BA133")
End

pour les boucles, je travaille en
i as byte
for i= 0 to 10
blabla
next i

la seule chose que j'ai pu optimiser pour la logique, c'est qu'il n'y a qu'une feuille origine et une feuille destination.

 

[seb33]

Re : amélioration de code pour accelerer les copiés collés

je te dis une bétise: l'une des feuilles possède une worksheets change.
en l'enlevant, je gagner 12 secondes (j'ai le chrono ) toujours ça. mais peut-on provisoirement bloquer un worksheet change?

 

[job75]

Re : amélioration de code pour accelerer les copiés collés

Re,

S'il n'y a que 200-300 cellules à modifier, cela se fait normalement en un clin d'oeil

A tout hasard, mettre en début et en fin de macro les lignes de codes suivantes :

Application.ScreenUpdating = False 'fige l'écran
Application.EnableEvents = False 'désactive l'action des évènements (macro Change par exemple)
Application.Calculation = xlManual 'recalcul manuel

'la macro

Application.Calculation = xlAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

Si cela fait de l'effet, désactiver (en les mettant en commentaires) les lignes de même nature 2 par 2, pour voir l'impact de chacune.

A+

 

[seb33]

Re : amélioration de code pour accelerer les copiés collés

Ah oui quand même (5 secondes de traitement).

très impressionnant et un effort merci

 

[job75]

Re : amélioration de code pour accelerer les copiés collés

Bonjour seb33,

Quels sont mes lignes de code qui ont amélioré la situation ?

Mais 5 secondes c'est encore trop...

Il faut sûrement alléger :

- éviter les .Select

- sur votre exemple, mettre une boucle With/End With :

'Calcul
[COLOR="Red"]With Sheets("Calcul")[/COLOR]
  If .Range("AU133") <> 0 Then .Range("D17") = .Range("AU133")
  If .Range("AV133") <> 0 Then .Range("C18") = .Range("AV133")
  If .Range("AW133") <> 0 Then .Range("D18") = .Range("AW133")
  If .Range("AZ133") <> 0 Then .Range("C20") = .Range("AZ133")
  If .Range("BA133") <> "" Then .Range("D20") = .Range("BA133")
[COLOR="Red"]End With[/COLOR]

A+

 

[seb33]

Re : amélioration de code pour accelerer les copiés collés

salut

Application.ScreenUpdating = False 'je l'avais déjà, donc pas de modification de durée
Application.EnableEvents = False 'un peu de temps, mais pas trop, car les events n'étaient pas très nombreux et gourmands en ressources
Application.Calculation = xlManual c'est là que nous avons gagné du temps: environ 400 cellules liées aux données copiées-collées.

pour ton nouveau code, je vais tester et je te tiens informé.
en fait des 5", j'ensuis arrivé à 3" en évitant de mélanger les ranges et les cells.

encore merci

 

[seb33]

Re : amélioration de code pour accelerer les copiés collés

salut job75

en fait de 5", c'est 3" (j'ai enlevé les mélanges cells et range)

pour ta solution, ce qui m'a fait réduire la durée était le calculation, puisque le screenupdating était déjà pris en compte et l'event n'est pas très lourd (mais j'ai laissé le tout, méthode bourrin )

je teste ta dernière préconisation et je te tiens informé