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
Bonjour,

J'ai un problème assez récurent avec mes macros :
J'ai une macro qui met environ 3 minutes à s'exécuter.
Lorsque je l'interromps en appuyant sur "ESC" puis "End" et que je la relance... elle s'éxécute en environ 3 secondes !
Ca reste à 3 secondes tant que je ne ferme pas Excel.
Si je ferme Excel et que j'ouvre à nouveau le fichier -> idem (3min puis 3sec après une interruption)

Y a-t-il un moyen d'avoir un temps de réponse rapide du premier coup ?

merci

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
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






 
3
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
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
0
Renfield
Messages postés
17287
Date d'inscription
mercredi 2 janvier 2002
Statut
Modérateur
Dernière intervention
27 septembre 2021
71
12 juil. 2007 à 10:11
dépend de ton code....
0
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
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
0

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
71
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
0
jmfmarques
Messages postés
7668
Date d'inscription
samedi 5 novembre 2005
Statut
Membre
Dernière intervention
22 août 2014
27
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) ?
0
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
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.
0
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
C'est un PC pro.
Je ne sais pas du tout si des paramètres ont été modifiés.
0
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
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 ???
0
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
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 ???
0
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
En supprimant les .select, ca fonctionne parfaitement.

merci
0
cs_MPi
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
20
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
0
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
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
0