Vitesse d'exécution d'une macro
Résolu
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
-
12 juil. 2007 à 09:59
mortalino Messages postés 6786 Date d'inscription vendredi 16 décembre 2005 Statut Membre Dernière intervention 21 décembre 2011 - 15 juil. 2007 à 08:48
mortalino Messages postés 6786 Date d'inscription vendredi 16 décembre 2005 Statut Membre Dernière intervention 21 décembre 2011 - 15 juil. 2007 à 08:48
13 réponses
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
13 juil. 2007 à 08:49
13 juil. 2007 à 08:49
Merci pour vos réponses,
J'en déduit que :
- Pour mettre à jour un grand nombre de cellules, utiliser les "activecell.offset(x,x).select / activecell.value = "XXX" "est pratique lorsqu'on est pas un spécialiste VBA (c'est visuel et facile à debugguer) mais les temps de traitements sont très long.
- Il est préférable d'utiliser activecell.offset(x,y).value = "XXX". c'est beaucoup plus rapide
- Pour les macro déjà développées avec des .select partout et si le fichier n'est pas trop partagé, il semble qu'en interrompant la première exécution et en la relancant, les temps de réponse soit bien meilleurs.
Bonne journée
Josea
J'en déduit que :
- Pour mettre à jour un grand nombre de cellules, utiliser les "activecell.offset(x,x).select / activecell.value = "XXX" "est pratique lorsqu'on est pas un spécialiste VBA (c'est visuel et facile à debugguer) mais les temps de traitements sont très long.
- Il est préférable d'utiliser activecell.offset(x,y).value = "XXX". c'est beaucoup plus rapide
- Pour les macro déjà développées avec des .select partout et si le fichier n'est pas trop partagé, il semble qu'en interrompant la première exécution et en la relancant, les temps de réponse soit bien meilleurs.
Bonne journée
Josea
mortalino
Messages postés
6786
Date d'inscription
vendredi 16 décembre 2005
Statut
Membre
Dernière intervention
21 décembre 2011
18
12 juil. 2007 à 10:02
12 juil. 2007 à 10:02
salut,
comportement étrange ! Si elle n'est pas trop longue, tu pourrais nous la présenter afin que l'on y jette un oeil, sinon, peut-être au moins le début (et le but)
@++
<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA
comportement étrange ! Si elle n'est pas trop longue, tu pourrais nous la présenter afin que l'on y jette un oeil, sinon, peut-être au moins le début (et le but)
@++
<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA
Renfield
Messages postés
17287
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
27 septembre 2021
74
12 juil. 2007 à 10:11
12 juil. 2007 à 10:11
dépend de ton code....
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 10:49
12 juil. 2007 à 10:49
Voici le code (ce n'est pas très optimisé mais ca fonctionne) :le but est de gérer des back-up pendant les périodes de congés. c'est un calendrier (lignes personnes; colonnes dates)
Dans un premier onglet, on saisi les congés.
Dans le deuxieme, on saisi les backup.
La macro permet de lire les congés, colore les cellules de l'onglet backup pour les dates de congés, vérifie si un backup et saisi (et s'il n'est pas lui même en congé), retourne dans l'onglet congés et colore les dates pour lequelles un personne est backup.
Bouton d'exécution :
Private Sub CommandButton1_Click()
Dim ret As Integer
ret = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call GETCP(ret)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Code de la macro :
Sub GETCP(ret)
'
' GETCP Macro
' Macro recorded 05/07/2007 by Josea
'
Dim tableau(100, 100) As String
Dim backuptab(100, 100) As String
Dim nbdate As Integer
Dim nbcons As Integer
Dim i As Integer
Dim j As Integer
Dim consultant(100) As String
Dim trigram(100) As String
Dim acdate(100) As Integer
Dim indexcons As Integer
Dim trouve As String
Dim ongcp As String
Dim ongbu As String
Dim CPdate As Integer
Dim CPcons As Integer
Dim BUdate As Integer
Dim BUcons As Integer
feuilleactive = ActiveWorkbook.ActiveSheet.Name
'-----------------------------------------------------------------------------'
' LECTURE DU PARAMETRAGE
'-----------------------------------------------------------------------------'
Sheets("Paramétrage").Select
Application.StatusBar = "lecture du paramétrage"
Range("B5").Select
ongcp = ActiveCell.Value
Range("B6").Select
CPdate = ActiveCell.Value
Range("B7").Select
CPcons = ActiveCell.Value
Range("B19").Select
ongbu = ActiveCell.Value
Range("B20").Select
BUdate = ActiveCell.Value
Range("B21").Select
BUcons = ActiveCell.Value
'-----------------------------------------------------------------------------'
' LECTURE DU TABLEAU DES CONGES
'-----------------------------------------------------------------------------'
Sheets(ongcp).Select
'Récupération du nombre de dates à traiter
Range("A1").Select
ActiveCell.Offset(CPcons, CPdate).Select
ActiveCell.Offset(-2, -1).Select
Do While ActiveCell.Value <> ""
nbdate = nbdate + 1
ActiveCell.Offset(0, 1).Select
Loop
'lecture du tableau des congés
Range("A1").Select
ActiveCell.Offset(CPcons, 0).Select
ActiveCell.Offset(-1, 0).Select
i = 1
Do While ActiveCell.Value <> ""
Application.StatusBar = "Lecture des absences : " & i
consultant(i) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
trigram(i) = ActiveCell.Value
ActiveCell.Offset(0, CPdate).Select
ActiveCell.Offset(0, -2).Select
For j = 1 To nbdate
tableau(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -j).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(0, -CPdate).Select
i = i + 1
Loop
nbcons = i - 1
'-----------------------------------------------------------------------------'
' RETOUR AU TABLEAU DES BACKUP
'-----------------------------------------------------------------------------'
Sheets(ongbu).Select
Range("A1").Select
ActiveCell.Offset(CPcons, 0).Select
ActiveCell.Offset(-1, 0).Select
'identification du consultant
Do While ActiveCell.Value <> ""
For j = 1 To nbcons
If consultant(j) = ActiveCell.Value Then
indexcons = j
Exit For
End If
Next
Application.StatusBar = "Traitement des Backup : " & indexcons & " sur " & nbcons
' remplissage des cellule d'absence
ActiveCell.Offset(0, BUdate).Select
ActiveCell.Offset(0, -1).Select
For j = 1 To nbdate
If tableau(indexcons, j) <> "" Then
'--------------------- le consultnat est absent-------------------------------
If ActiveCell.Value = "" Then 'Pas de backup identifié
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else 'un backup est saisi
trouve = ""
For i = 1 To nbcons
If trigram(i) = ActiveCell.Value Then 'Le Backup existe
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
trouve = "X"
backuptab(i, j) = "X" 'stockage pour maj congés
Exit For
End If
Next
If trouve = "" Then 'Le Backup n'existe pas
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Else
If tableau(i, j) <> "" Then 'Le Backup est absent
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
End If
End If
End If
Else
Selection.Interior.ColorIndex = xlNone
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -nbdate).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -BUdate).Select
Loop
'-----------------------------------------------------------------------------'
' RETOUR AU TABLEAU DES Congés
'-----------------------------------------------------------------------------'
Sheets(ongcp).Select
'traitement des journées de backup
Range("A1").Select
ActiveCell.Offset(CPcons, CPdate).Select
ActiveCell.Offset(-1, -1).Select
For i = 1 To nbcons
Application.StatusBar = "Mise à jours Absences " & i & " sur " & nbcons
For j = 1 To nbdate
If backuptab(i, j) = "X" Then
Selection.Font.ColorIndex = 46
Selection.Interior.ColorIndex = 46
Selection.Font.Bold = True
Else
Selection.Font.ColorIndex = 0
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -nbdate).Select
Next
'-----------------------------------------------------------------------------'
' Fin des traitements
'-----------------------------------------------------------------------------'
Sheets(feuilleactive).Select
Range("A1").Select
Application.StatusBar = "Terminé"
End Sub
Dans un premier onglet, on saisi les congés.
Dans le deuxieme, on saisi les backup.
La macro permet de lire les congés, colore les cellules de l'onglet backup pour les dates de congés, vérifie si un backup et saisi (et s'il n'est pas lui même en congé), retourne dans l'onglet congés et colore les dates pour lequelles un personne est backup.
Bouton d'exécution :
Private Sub CommandButton1_Click()
Dim ret As Integer
ret = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call GETCP(ret)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Code de la macro :
Sub GETCP(ret)
'
' GETCP Macro
' Macro recorded 05/07/2007 by Josea
'
Dim tableau(100, 100) As String
Dim backuptab(100, 100) As String
Dim nbdate As Integer
Dim nbcons As Integer
Dim i As Integer
Dim j As Integer
Dim consultant(100) As String
Dim trigram(100) As String
Dim acdate(100) As Integer
Dim indexcons As Integer
Dim trouve As String
Dim ongcp As String
Dim ongbu As String
Dim CPdate As Integer
Dim CPcons As Integer
Dim BUdate As Integer
Dim BUcons As Integer
feuilleactive = ActiveWorkbook.ActiveSheet.Name
'-----------------------------------------------------------------------------'
' LECTURE DU PARAMETRAGE
'-----------------------------------------------------------------------------'
Sheets("Paramétrage").Select
Application.StatusBar = "lecture du paramétrage"
Range("B5").Select
ongcp = ActiveCell.Value
Range("B6").Select
CPdate = ActiveCell.Value
Range("B7").Select
CPcons = ActiveCell.Value
Range("B19").Select
ongbu = ActiveCell.Value
Range("B20").Select
BUdate = ActiveCell.Value
Range("B21").Select
BUcons = ActiveCell.Value
'-----------------------------------------------------------------------------'
' LECTURE DU TABLEAU DES CONGES
'-----------------------------------------------------------------------------'
Sheets(ongcp).Select
'Récupération du nombre de dates à traiter
Range("A1").Select
ActiveCell.Offset(CPcons, CPdate).Select
ActiveCell.Offset(-2, -1).Select
Do While ActiveCell.Value <> ""
nbdate = nbdate + 1
ActiveCell.Offset(0, 1).Select
Loop
'lecture du tableau des congés
Range("A1").Select
ActiveCell.Offset(CPcons, 0).Select
ActiveCell.Offset(-1, 0).Select
i = 1
Do While ActiveCell.Value <> ""
Application.StatusBar = "Lecture des absences : " & i
consultant(i) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
trigram(i) = ActiveCell.Value
ActiveCell.Offset(0, CPdate).Select
ActiveCell.Offset(0, -2).Select
For j = 1 To nbdate
tableau(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -j).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(0, -CPdate).Select
i = i + 1
Loop
nbcons = i - 1
'-----------------------------------------------------------------------------'
' RETOUR AU TABLEAU DES BACKUP
'-----------------------------------------------------------------------------'
Sheets(ongbu).Select
Range("A1").Select
ActiveCell.Offset(CPcons, 0).Select
ActiveCell.Offset(-1, 0).Select
'identification du consultant
Do While ActiveCell.Value <> ""
For j = 1 To nbcons
If consultant(j) = ActiveCell.Value Then
indexcons = j
Exit For
End If
Next
Application.StatusBar = "Traitement des Backup : " & indexcons & " sur " & nbcons
' remplissage des cellule d'absence
ActiveCell.Offset(0, BUdate).Select
ActiveCell.Offset(0, -1).Select
For j = 1 To nbdate
If tableau(indexcons, j) <> "" Then
'--------------------- le consultnat est absent-------------------------------
If ActiveCell.Value = "" Then 'Pas de backup identifié
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else 'un backup est saisi
trouve = ""
For i = 1 To nbcons
If trigram(i) = ActiveCell.Value Then 'Le Backup existe
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
trouve = "X"
backuptab(i, j) = "X" 'stockage pour maj congés
Exit For
End If
Next
If trouve = "" Then 'Le Backup n'existe pas
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Else
If tableau(i, j) <> "" Then 'Le Backup est absent
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
End If
End If
End If
Else
Selection.Interior.ColorIndex = xlNone
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -nbdate).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -BUdate).Select
Loop
'-----------------------------------------------------------------------------'
' RETOUR AU TABLEAU DES Congés
'-----------------------------------------------------------------------------'
Sheets(ongcp).Select
'traitement des journées de backup
Range("A1").Select
ActiveCell.Offset(CPcons, CPdate).Select
ActiveCell.Offset(-1, -1).Select
For i = 1 To nbcons
Application.StatusBar = "Mise à jours Absences " & i & " sur " & nbcons
For j = 1 To nbdate
If backuptab(i, j) = "X" Then
Selection.Font.ColorIndex = 46
Selection.Interior.ColorIndex = 46
Selection.Font.Bold = True
Else
Selection.Font.ColorIndex = 0
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -nbdate).Select
Next
'-----------------------------------------------------------------------------'
' Fin des traitements
'-----------------------------------------------------------------------------'
Sheets(feuilleactive).Select
Range("A1").Select
Application.StatusBar = "Terminé"
End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Renfield
Messages postés
17287
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
27 septembre 2021
74
12 juil. 2007 à 10:58
12 juil. 2007 à 10:58
je suis choqué par tant de .Select...
ils sont inutiles, la plupart du temps.
place un DoEvents dand ta boucle, ca reglera surement ton soucis
ils sont inutiles, la plupart du temps.
place un DoEvents dand ta boucle, ca reglera surement ton soucis
jmfmarques
Messages postés
7666
Date d'inscription
samedi 5 novembre 2005
Statut
Membre
Dernière intervention
22 août 2014
27
12 juil. 2007 à 11:14
12 juil. 2007 à 11:14
Bonjour,
ce qui m'interpelle est ceci :
"Ca reste à 3 secondes tant que je ne ferme pas Excel."
Question : travailles-tu sur ta machine ou sur une machine qui ne t'appartient pas ?
Es-tu certain de ce que les settings d'Excel n'ont pas été personnalisés (pour des raisons de sécurité, par exemple) ?
ce qui m'interpelle est ceci :
"Ca reste à 3 secondes tant que je ne ferme pas Excel."
Question : travailles-tu sur ta machine ou sur une machine qui ne t'appartient pas ?
Es-tu certain de ce que les settings d'Excel n'ont pas été personnalisés (pour des raisons de sécurité, par exemple) ?
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 11:26
12 juil. 2007 à 11:26
Pour le DoEvents...je ne sais pas franchement ce que c'est (j'ai apris le VBA sur le tas...et sur le tard) je vais essayer de voir ce que c'est... mais si tu as une piste à me donner.
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 11:27
12 juil. 2007 à 11:27
C'est un PC pro.
Je ne sais pas du tout si des paramètres ont été modifiés.
Je ne sais pas du tout si des paramètres ont été modifiés.
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 14:45
12 juil. 2007 à 14:45
merci beaucoup,
Ca y est, je pense que j'ai compris... je vais certainement pouvoir enlever 90% des .select.
je fait un truc du style :
For i = 0 To 10
For j = 0 To 60
tableau(i, j) = ActiveCell.Offset(i, j).Value
Next
Next
Ca va effectivement beaucoup, beaucoup + vite.
Par contre, pourquoi est-ce que c'est parfois lent et parfois rapide ???
Ca y est, je pense que j'ai compris... je vais certainement pouvoir enlever 90% des .select.
je fait un truc du style :
For i = 0 To 10
For j = 0 To 60
tableau(i, j) = ActiveCell.Offset(i, j).Value
Next
Next
Ca va effectivement beaucoup, beaucoup + vite.
Par contre, pourquoi est-ce que c'est parfois lent et parfois rapide ???
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 14:45
12 juil. 2007 à 14:45
merci beaucoup,
Ca y est, je pense que j'ai compris... je vais certainement pouvoir enlever 90% des .select.
je fait un truc du style :
For i = 0 To 10
For j = 0 To 60
tableau(i, j) = ActiveCell.Offset(i, j).Value
Next
Next
Ca va effectivement beaucoup, beaucoup + vite.
Par contre, pourquoi est-ce que c'est parfois lent et parfois rapide ???
Ca y est, je pense que j'ai compris... je vais certainement pouvoir enlever 90% des .select.
je fait un truc du style :
For i = 0 To 10
For j = 0 To 60
tableau(i, j) = ActiveCell.Offset(i, j).Value
Next
Next
Ca va effectivement beaucoup, beaucoup + vite.
Par contre, pourquoi est-ce que c'est parfois lent et parfois rapide ???
cs_josea
Messages postés
8
Date d'inscription
jeudi 12 juillet 2007
Statut
Membre
Dernière intervention
13 juillet 2007
12 juil. 2007 à 18:25
12 juil. 2007 à 18:25
En supprimant les .select, ca fonctionne parfaitement.
merci
merci
cs_MPi
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
17 août 2018
23
12 juil. 2007 à 23:37
12 juil. 2007 à 23:37
Pour le temps d'exécution variable, c'est assez difficile à dire.
Toute la semaine, je roule une macro qui prend 2-3 secondes. Le vendredi je fais quelques copier/coller et là elle met beaucoup plus de temps à rouler..., je dirais 20 à 30 secondes
Sur Excel 2000, j'ai créé une autre macro qui prend 20 secondes. Je roule le même programme sur 2003 et ça prend 200 secondes. Et j'ai entendu des gens qui disaient que 2007 était plus lent que 2003... (???) Peut-être est-ce mieux de rester sous 2000 dans ce cas (?)
Parfois le simple fait de sauvegarder le programme avant l'exécution d'une macro accélère celle-ci... un peu comme si ça vidait la mémoire RAM et c'est peut-être ce que ça fait réellement, je n'en sais trop rien...
MPi
Toute la semaine, je roule une macro qui prend 2-3 secondes. Le vendredi je fais quelques copier/coller et là elle met beaucoup plus de temps à rouler..., je dirais 20 à 30 secondes
Sur Excel 2000, j'ai créé une autre macro qui prend 20 secondes. Je roule le même programme sur 2003 et ça prend 200 secondes. Et j'ai entendu des gens qui disaient que 2007 était plus lent que 2003... (???) Peut-être est-ce mieux de rester sous 2000 dans ce cas (?)
Parfois le simple fait de sauvegarder le programme avant l'exécution d'une macro accélère celle-ci... un peu comme si ça vidait la mémoire RAM et c'est peut-être ce que ça fait réellement, je n'en sais trop rien...
MPi
mortalino
Messages postés
6786
Date d'inscription
vendredi 16 décembre 2005
Statut
Membre
Dernière intervention
21 décembre 2011
18
15 juil. 2007 à 08:48
15 juil. 2007 à 08:48
J'en déduit que :
- Pour mettre à jour
un grand nombre de cellules, utiliser les
"activecell.offset(x,x).select / activecell.value = "XXX" "est
pratique lorsqu'on est pas un spécialiste VBA (c'est visuel et facile à
debugguer) mais les temps de traitements sont très long.
Facile pour gagner du temps, dans cette config :
Tu mets Application.ScreenUpdating = False au début de ta procédure. Tu le mets à True à la fin.
Quand tu testes, suffit de mettre une apostrophe devant ces deux lignes (devient donc un commentaire), pis quand t'as fini et que c'est opérationnel, t'enlèves les apostrophes
@++
<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA
- Pour mettre à jour
un grand nombre de cellules, utiliser les
"activecell.offset(x,x).select / activecell.value = "XXX" "est
pratique lorsqu'on est pas un spécialiste VBA (c'est visuel et facile à
debugguer) mais les temps de traitements sont très long.
Facile pour gagner du temps, dans cette config :
Tu mets Application.ScreenUpdating = False au début de ta procédure. Tu le mets à True à la fin.
Quand tu testes, suffit de mettre une apostrophe devant ces deux lignes (devient donc un commentaire), pis quand t'as fini et que c'est opérationnel, t'enlèves les apostrophes
@++
<hr size="2" width="100%" />( Nouveau forum : Exclusivement Office & VBA