Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel temps éxécution macro trop long

temps éxécution macro trop long

23 réponses
Avatar
Caroual
Bonjour,
J'aurais aimé avoir un peu d'aide pour améliorer mon code.
J'ai un fichier texte sur 12000 lignes et 16 colonnes. Dans excel je
voudrais mettre 5 lignes sur une seule ligne et supprimer les lignes qui ont
été recopiées.
J'ai écrit ce code qui marche mais cela me semble anormalement long. (2
minutes d'éxécution)


Sub essai()
Application.ScreenUpdating = False
tata = Application.GetOpenFilename
Workbooks.OpenText Filename:=tata, Origin:=xlMSDOS, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(10, 1), Array(16, 1), Array(22,
1), Array(28, 1), _
Array(34, 1), Array(40, 1), Array(46, 1), Array(52, 1), Array(58, 1),
Array(64, 1), Array( _
70, 1), Array(76, 1), Array(82, 1), Array(88, 1), Array(94, 1)),
TrailingMinusNumbers _
:=True
fin = Range("a35000").End(xlUp)
Range("a1").Select
For k = 1 To fin
For n = 2 To 5
Cells(k, 1).Select
Range(Cells(n + k - 1, 2), Cells(n + k - 1, 16)).Copy
Cells(k, 200).End(xlToLeft).Offset(, 1).PasteSpecial
Paste:=xlPasteValues
Next
Range(Cells(k + 1, 1), Cells(k + 4, 18)).Delete
Next
End Sub

J'avais désactivé les calculs auto mais il n'y a aucune formule dans ce
fichier donc cela ne doit servir à rien.
Peut être est-ce normal. J'aimerais votre un avis.
Merci
Caroual
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2 3
Avatar
LSteph
peu importe!
avec step 5 tu traiteras toutes les 5
ce que tu n'as vraisemblablement pas bien compris et c'est
ce qu'essaye aussi de t'expliquer Philippe
je reprends mon propos

mets y la ligne pas la valeur
> fin = Range("a35000").End(xlUp)
fin = [a65536].end(xlup).row

l'un renvoie la valeur contenue à la dernière ligne
l'autre le numéro de la dernière ligne
c'est toute la différence!

Et je note que tu n'as toujours pas donné retour ou peut-être toujours
pas vu l'exemple proposé par MichDenis...

@+

--
lSteph


Caroual a écrit :

Pour info :
Je pense que je gère bien mon nombre de lignes :
Je récupère un fichier txt qui note des infos correspondant à un pas horaire
sur 5 lignes. donc dans ma première colonne j'ai mes pas horaires répétés 5
fois. Si je cherche la dernière ligne pour savoir combien de fois je vais
faire le traitement, je vais répéter l'action trop de fois puisque je
supprime 4 lignes sur 5. Par contre si je ne supprime pas les lignes
effectivement il vaut mieux que je travaille sur le numéro de la dernière
ligne.
Je ne sais jamais combien je vais avoir de lignes, par contre je sais
toujours que mes numéros de lignes sont répétés 5 fois.
Pour 3 pas horaires j'aurais 15 lignes mais seulement 3 traitements.
Si je récupère le numéro de la dernière ligne je vais faire 15 traitements.
Caroual





"LSteph" a écrit dans le message de news:
e9Xt$
Bonsoir,

Si je mets ta ligne, fin = 12000 et j'exécute sur 12000 lignes alors
qu'en




Pas du tout regarde l'exemple de MichDenis
Fin = Source.Range("A65000").End(xlUp).Row


Sauf si tuaqs mis 2400 dans la cellule Fin et que tusavais d'avance que
tuu n'aurais que 2400 lignes.
Dans ce cas l'instruction n'aurait plus de sens...

... le reste est à l'avenant,

Suppression des Select inutiles
ça ce n'est plus à démontrer!

hors mis la façon de traiter la suppression,
j'en reviens que si tu dois le faire en boucle
remonter est plus sûr!

Cordialement.

--
lSteph






Avatar
MichDenis
Ok, pour 5 minutes... ;-))

En mode de calcul manuel, pour traiter le même nombre de lignes (20 000)
ça lui prend 325 secondes. soit 6 minutes 9 secondes de plus.
Avatar
Caroual
Je ne trouve effectivement pas le message de MichDenis ...
Caroual

"LSteph" a écrit dans le message de news:

peu importe!
avec step 5 tu traiteras toutes les 5
ce que tu n'as vraisemblablement pas bien compris et c'est
ce qu'essaye aussi de t'expliquer Philippe
je reprends mon propos

mets y la ligne pas la valeur
> fin = Range("a35000").End(xlUp)
fin = [a65536].end(xlup).row

l'un renvoie la valeur contenue à la dernière ligne
l'autre le numéro de la dernière ligne
c'est toute la différence!

Et je note que tu n'as toujours pas donné retour ou peut-être toujours pas
vu l'exemple proposé par MichDenis...

@+

--
lSteph


Caroual a écrit :

Pour info :
Je pense que je gère bien mon nombre de lignes :
Je récupère un fichier txt qui note des infos correspondant à un pas
horaire sur 5 lignes. donc dans ma première colonne j'ai mes pas horaires
répétés 5 fois. Si je cherche la dernière ligne pour savoir combien de
fois je vais faire le traitement, je vais répéter l'action trop de fois
puisque je supprime 4 lignes sur 5. Par contre si je ne supprime pas les
lignes effectivement il vaut mieux que je travaille sur le numéro de la
dernière ligne.
Je ne sais jamais combien je vais avoir de lignes, par contre je sais
toujours que mes numéros de lignes sont répétés 5 fois.
Pour 3 pas horaires j'aurais 15 lignes mais seulement 3 traitements.
Si je récupère le numéro de la dernière ligne je vais faire 15
traitements.
Caroual





"LSteph" a écrit dans le message de news:
e9Xt$
Bonsoir,

Si je mets ta ligne, fin = 12000 et j'exécute sur 12000 lignes alors
qu'en




Pas du tout regarde l'exemple de MichDenis
Fin = Source.Range("A65000").End(xlUp).Row


Sauf si tuaqs mis 2400 dans la cellule Fin et que tusavais d'avance que
tuu n'aurais que 2400 lignes.
Dans ce cas l'instruction n'aurait plus de sens...

... le reste est à l'avenant,

Suppression des Select inutiles
ça ce n'est plus à démontrer!

hors mis la façon de traiter la suppression,
j'en reviens que si tu dois le faire en boucle
remonter est plus sûr!

Cordialement.

--
lSteph







Avatar
Mgr T. Banni
le voici, caroual
HTH
Mgr T.B.

"MichDenis" a écrit dans le message de news:

J'ai fait un petit test,

Pour traiter 20,000 lignes, environ 16 secondes.

De la macro originale, il faut enlever ces lignes de code :

ModCalcul = Application.Calculation
Application.Calculation = xlCalculationManual
Application.Calculation = ModCalcul

La version de la procédure suivante est corrigé.
Une question demeure: Si ton tableau à 16 colonnes (champ)
comment arrives-tu à ces array(46,1), array(70,1)...etc.
Le premier chiffre indique le numéro du champ, le deuxième
numéro de l'array indique le format du champ ?



'------------------------------------------------
Sub test()
Dim Fin As Long, K As Long, L As Long
Dim A As Integer, Lig As Long
Dim Source As Worksheet, Dest As Worksheet, N As String
Dim Fichier As Variant, Wk As Workbook

Application.ScreenUpdating = False
Application.Calculation =xlCalculationAutomatic
Application.EnableEvents = False

Fichier = Application.GetOpenFilename
If TypeName(Fichier) = "Boolean" Then Exit Sub
If LCase(Right(Fichier, 4)) <> ".txt" Then
MsgBox "Opération annulée, ce n'est pas un fichier texte"
Exit Sub
End If

Workbooks.OpenText Filename:=Fichier, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), _
Array(10, 1), Array(16, 1), Array(22, 1), Array(28, 1), _
Array(34, 1), Array(40, 1), Array(46, 1), Array(52, 1), _
Array(58, 1), Array(64, 1), Array(70, 1), Array(76, 1), _
Array(82, 1), Array(88, 1), Array(94, 1)), _
TrailingMinusNumbers:=True

Set Wk = ActiveWorkbook

With Wk
Set Source = Wk.ActiveSheet
Set Dest = Wk.Worksheets.Add
End With

Fin = Source.Range("A65000").End(xlUp).Row
A = 1
For K = 1 To Fin Step 5
L = K + 4
Lig = Lig + 1
For X = K To L
Source.Range("A" & X).Resize(, 16).Cut Dest.Cells(Lig, A)
A = A + 16
Next
A = 1
Next
Application.DisplayAlerts = False
N = Source.Name
Source.Delete
Dest.Name = N
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'------------------------------------------------






"Caroual" a écrit dans le message de groupe de
discussion : 49f7f6ed$0$12629$
Bonjour,
J'aurais aimé avoir un peu d'aide pour améliorer mon code.
J'ai un fichier texte sur 12000 lignes et 16 colonnes. Dans excel je
voudrais mettre 5 lignes sur une seule ligne et supprimer les lignes qui
ont
été recopiées.
J'ai écrit ce code qui marche mais cela me semble anormalement long. (2
minutes d'éxécution)


Sub essai()
Application.ScreenUpdating = False
tata = Application.GetOpenFilename
Workbooks.OpenText Filename:=tata, Origin:=xlMSDOS, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(4, 1), Array(10, 1), Array(16, 1), Array(22,
1), Array(28, 1), _
Array(34, 1), Array(40, 1), Array(46, 1), Array(52, 1), Array(58, 1),
Array(64, 1), Array( _
70, 1), Array(76, 1), Array(82, 1), Array(88, 1), Array(94, 1)),
TrailingMinusNumbers _
:=True
fin = Range("a35000").End(xlUp)
Range("a1").Select
For k = 1 To fin
For n = 2 To 5
Cells(k, 1).Select
Range(Cells(n + k - 1, 2), Cells(n + k - 1, 16)).Copy
Cells(k, 200).End(xlToLeft).Offset(, 1).PasteSpecial
Paste:=xlPasteValues
Next
Range(Cells(k + 1, 1), Cells(k + 4, 18)).Delete
Next
End Sub

J'avais désactivé les calculs auto mais il n'y a aucune formule dans ce
fichier donc cela ne doit servir à rien.
Peut être est-ce normal. J'aimerais votre un avis.
Merci
Caroual


Avatar
LSteph
Voici:


...de MD.....
'------------------------------------------------
Sub test()
Dim Fin As Long, K As Long, L As Long
Dim A As Integer, Lig As Long
Dim Source As Worksheet, Dest As Worksheet, N As String
Dim Fichier As Variant, Wk As Workbook

Application.ScreenUpdating = False
Application.Calculation =xlCalculationAutomatic
Application.EnableEvents = False

Fichier = Application.GetOpenFilename
If TypeName(Fichier) = "Boolean" Then Exit Sub
If LCase(Right(Fichier, 4)) <> ".txt" Then
MsgBox "Opération annulée, ce n'est pas un fichier texte"
Exit Sub
End If

Workbooks.OpenText Filename:=Fichier, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), _
Array(10, 1), Array(16, 1), Array(22, 1), Array(28, 1), _
Array(34, 1), Array(40, 1), Array(46, 1), Array(52, 1), _
Array(58, 1), Array(64, 1), Array(70, 1), Array(76, 1), _
Array(82, 1), Array(88, 1), Array(94, 1)), _
TrailingMinusNumbers:=True

Set Wk = ActiveWorkbook

With Wk
Set Source = Wk.ActiveSheet
Set Dest = Wk.Worksheets.Add
End With

Fin = Source.Range("A65000").End(xlUp).Row
A = 1
For K = 1 To Fin Step 5
L = K + 4
Lig = Lig + 1
For X = K To L
Source.Range("A" & X).Resize(, 16).Cut Dest.Cells(Lig, A)
A = A + 16
Next
A = 1
Next
Application.DisplayAlerts = False
N = Source.Name
Source.Delete
Dest.Name = N
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'------------------------------------------------



Caroual a écrit :
Je ne trouve effectivement pas le message de MichDenis ...
Caroual

"LSteph" a écrit dans le message de news:

peu importe!
avec step 5 tu traiteras toutes les 5
ce que tu n'as vraisemblablement pas bien compris et c'est
ce qu'essaye aussi de t'expliquer Philippe
je reprends mon propos

mets y la ligne pas la valeur
fin = Range("a35000").End(xlUp)


fin = [a65536].end(xlup).row

l'un renvoie la valeur contenue à la dernière ligne
l'autre le numéro de la dernière ligne
c'est toute la différence!

Et je note que tu n'as toujours pas donné retour ou peut-être toujours pas
vu l'exemple proposé par MichDenis...

@+

--
lSteph


Caroual a écrit :

Pour info :
Je pense que je gère bien mon nombre de lignes :
Je récupère un fichier txt qui note des infos correspondant à un pas
horaire sur 5 lignes. donc dans ma première colonne j'ai mes pas horaires
répétés 5 fois. Si je cherche la dernière ligne pour savoir combien de
fois je vais faire le traitement, je vais répéter l'action trop de fois
puisque je supprime 4 lignes sur 5. Par contre si je ne supprime pas les
lignes effectivement il vaut mieux que je travaille sur le numéro de la
dernière ligne.
Je ne sais jamais combien je vais avoir de lignes, par contre je sais
toujours que mes numéros de lignes sont répétés 5 fois.
Pour 3 pas horaires j'aurais 15 lignes mais seulement 3 traitements.
Si je récupère le numéro de la dernière ligne je vais faire 15
traitements.
Caroual





"LSteph" a écrit dans le message de news:
e9Xt$
Bonsoir,

Si je mets ta ligne, fin = 12000 et j'exécute sur 12000 lignes alors
qu'en




Pas du tout regarde l'exemple de MichDenis
Fin = Source.Range("A65000").End(xlUp).Row


Sauf si tuaqs mis 2400 dans la cellule Fin et que tusavais d'avance que
tuu n'aurais que 2400 lignes.
Dans ce cas l'instruction n'aurait plus de sens...

... le reste est à l'avenant,

Suppression des Select inutiles
ça ce n'est plus à démontrer!

hors mis la façon de traiter la suppression,
j'en reviens que si tu dois le faire en boucle
remonter est plus sûr!

Cordialement.

--
lSteph









Avatar
Misange
MichDenis a écrit :
Salut Denis
Je m'y perds dans cette ficelle.
Si j'ai bien compris, les données du classeur à traiter ne comportent
aucune formule ? Dans ce cas ça parait normal que le fait de passer en
mode manuel n'améliore pas la productivité mais de là à la pénaliser
très fortement...
Est ce que tu peux poster le classeur qui t'a servi à faire ce test ?
C'est clair que cette observation va complètement à l'encontre de ce qui
est généralement observé !
Est ce que le fait d'être en mode de calcul manuel conduit à stocker des
trucs en mémoire d'où possible saturation ? (pure hypothèse)

--
Misange migrateuse
http://www.excelabo.net : Participez à un travail collaboratif sur excel !
Avatar
Misange
MichDenis a écrit :
Bonjour Misange,

Voici un fichier exemple
http://cjoint.com/?fccS0ZeBLv



Impossible d'ouvrir l'archive qui semble endommagée. Tu peux me la
mettre en xls ou en zip ?


Ce matin, j'ai refait le test, et j'ai obtenu 18 secondes en mode de calcul automatique et 80 secondes en mode de calcul manuel.

Habituellement, on désactive le mode de calcul automatique pour empêcher la mise à jour des cellules contenant des formules pendant l'exécution d'une macro intervenant directement dans l'interface de calcul.

Moi, je n'ai jamais lu un papier qui expliquerait le processus engagé par la désactivation du mode de calcul automatique qui justifierait la différence notable du temps de traitement comparé au temps de traitement en mode automatique.



moi non plus, bien au contraire !

--
Misange migrateuse
http://www.excelabo.net : Participez à un travail collaboratif sur excel !
Avatar
HD
> Si je dis v1=mes 16 premiéres valeurs puis v2 les 16 suivantes etc ...
comment je fais pour les poser toutes sur une même ligne
J'ai essayé : Range("a1")=union(v1,v2,v3,v4,v5) mais cela ne marche pas.
Je dois oublier quelque chose



Attention, l'Union n'est pas utilisé pour mettre tes 16 valeurs en une seule
ligne mais est plutôt faites pour sélectionner toutes les lignes à supprimer
dans une plage de cellule que l'on détruit en fin de macro. ça va donner à
peu près ça :

Dim Adetruire as Range

Tant que non fin
Cells(i , 1 ) = Cells(i +1 , 1) + Cells(i +2 , 1) + Cells(i +3 , 1) +
Cells(i +4 , 1) + Cells(i +5 , 1) + ect...
Si Adetruire isnothing Alors
Set Adetruire = Union ( Cells(i +1 , 1) , Cells(i +2 , 1) , Cells(i
+3 , 1) , Cells(i +4 , 1) , Cells(i +5 , 1), ect...)
Sinon
Set Adetruire = Union ( Adetruire, Cells(i +1 , 1) + Cells(i +2 ,
1) + Cells(i +3 , 1) + Cells(i +4 , 1) + Cells(i +5 , 1) + ect...
Fin si
i = i + 16
Continuer

Adetruire.delete

Et là, tu vas voir une très belle accélération de ta macro

@+
HD
Avatar
Misange
Après avoir testé le fichier (reçu dans ma BAL):
en automatique 37 secondes
en manuel : plantage ! J'ai il est vrai beaucoup beaucoup d'applis
ouvertes en même temps et des plus gourmandes. Cela oriente à priori
d'autant plus sur un pb de gestion de la mémoire non ? Drole d'histoire...

--
Misange migrateuse
http://www.excelabo.net : Participez à un travail collaboratif sur excel !
Avatar
isabelle
salutatous,

j'ai fais le test avec (excel2002) 20000 lignes et j'obtiens à peu
près le même résultat soit 73.140999999999 secondes.
et en mode automatique, si je met la commande Application.Calculation =
xlCalculationManual mit en commentaire
le résultat est 78.795999999998 secondes.

isabelle

MichDenis a écrit :
Bonjour Misange,

Voici dans le haut du module, j'ai ajouté une procédure
pour vider le presse-papier à chaque 5 lignes.
Sur mon ordi. le temps d'exécution en mode manuel est sensiblement
le même 84 secondes contre 19 secondes en mode automatique.

Sur mon ordi, Seule l'application Excel 2003 était ouverte
en compagnie de Windows Mail Live.
L'ordi dispose de 4 Go de ram.

Si d'autres veulent tester, il s'agit de remplir sur la feuille active
20 lignes sur 16 colonnes d'un texte quelconque.


Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
'-----------------------------------------------
Sub Vide_presse_Papier()
OpenClipboard 0
EmptyClipboard
CloseClipboard
End Sub
'-----------------------------------------------
'--------------------------------------------------------------
Sub Mode_Calcul_Manuel()

Dim Fin As Long, K As Long, L As Long
Dim A As Integer, Lig As Long
Dim Source As Worksheet, Dest As Worksheet, N As String
Dim Fichier As Variant, Wk As Workbook
Dim T As Double
T = Timer

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

'*** Pour les besoins du test, ces lignes ont été désactivées
'*** car non pertinentes à la démonstration
'Fichier = Application.GetOpenFilename
'If TypeName(Fichier) = "Boolean" Then Exit Sub
'If LCase(Right(Fichier, 4)) <> ".txt" Then
' MsgBox "Opération annulée, ce n'est pas un fichier texte"
' Exit Sub
'End If

'Workbooks.OpenText Filename:=Fichier, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), _
Array(10, 1), Array(16, 1), Array(22, 1), Array(28, 1), _
Array(34, 1), Array(40, 1), Array(46, 1), Array(52, 1), _
Array(58, 1), Array(64, 1), Array(70, 1), Array(76, 1), _
Array(82, 1), Array(88, 1), Array(94, 1)), _
TrailingMinusNumbers:=True

Set Wk = ThisWorkbook ' ActiveWorkbook

With Wk
Set Source = Wk.ActiveSheet
Set Dest = Wk.Worksheets.Add
End With

Fin = Source.Range("A65000").End(xlUp).Row
Vide_presse_Papier
A = 1
For K = 1 To Fin Step 5
L = K + 4
Lig = Lig + 1
For X = K To L
Source.Range("A" & X).Resize(, 16).Cut Dest.Cells(Lig, A)
A = A + 16
Next
A = 1
Vide_presse_Papier
Next
Dest.UsedRange.EntireColumn.AutoFit
Application.DisplayAlerts = False
'N = Source.Name
'Source.Delete
'Dest.Name = N
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox Timer - T & " secondes."
Application.Calculation = xlCalculationAutomatic
End Sub
'--------------------------------------------------------------




"Misange" a écrit dans le message de groupe de discussion : #
Après avoir testé le fichier (reçu dans ma BAL):
en automatique 37 secondes
en manuel : plantage ! J'ai il est vrai beaucoup beaucoup d'applis
ouvertes en même temps et des plus gourmandes. Cela oriente à priori
d'autant plus sur un pb de gestion de la mémoire non ? Drole d'histoire...




1 2 3