Discussion :

[retraite83]

Bonjour

je cherche maintenant à optimiser mon code VBA car j'ai constaté avec maintenant un peu d'expérience (je suis parti de zéro pointé) que je n'avais pas toujours bien codé et donc que l'exécution n'est pas optimale et peut poser des problèmes

Un exemple : lorsque dans une macro je code un "Select" ou "Activate"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
feuille.select

le code de la feuille se déclenche inutilement dans certains cas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Private Sub Worksheet_Activate()

La parade (?)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Application.EnableEvents = False
feuille.select
Application.EnableEvents = True

J'ai cherché et j'ai vu que pour tracer le code il y a 2 options dans "Débogage"
- Pas à pas principal
- Pas à pas sortant

Mais ces options ne sont pas accessibles dans mes Excels

Existe-t-il un outil ou un moyen de tracer le code lors de l'exécution d'une macro (Call à une macro ou une fonction, select ou activate d'une autre feuille, etc)

Comment optimisez-vous vos macros ?

Merci d'avance

[Philippe Tulliez]

Bonjour,
En VBA, sauf cas particulier, il n'y a aucune raison d'utiliser les méthodes Select et Activate.
On peut écrire et lire dans n'importe quelle cellule, feuille ou classeur sans utiliser ces méthodes.

Exemple : MsgBox Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value

[retraite83]

Merci je vais reprendre mes macros

Existe-t-il un moyen exhaustif de lister les "Select" et "Activate" d'un fichier sans rechercher macro par macro ?

[Philippe Tulliez]

Bonjour,

Existe-t-il un moyen exhaustif de lister les "Select" et "Activate" d'un fichier sans rechercher macro par macro ?

Il y a d'abord la boîte de dialogue Chercher (Ctrl + F) dans l'éditeur VBE et à chaque ligne trouvée, vous pouvez "Taguer" la ligne avec le premier drapeau (voir illustration), il faut pour cela afficher la boîte à outil "Edition", et ensuite il suffit d'aller de tag en tag avec les deux touches du milieu (voir illustration)

Sinon, il est possible d'écrire un code VBA qui peut effectuer la recherche et donner le nom des modules où se trouvent ces lignes (j'ai peut-être cela dans mes cartons mais je dois chercher)

Illustration

[retraite83]

Bonjour,

merci je ne connaissais pas le système de tag avec les drapeaux, cela va me permettre d'identifier les Select et Activate

Par contre je connaissais l'adressage du type " Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value" mais cela ne me paraissait pas très correct à utiliser d'où mes "Select"

[Philippe Tulliez]

Bonjour,

Par contre je connaissais l'adressage du type " Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value" mais cela ne me paraissait pas très correct à utiliser d'où mes "Select"

Les méthodes Select et Activate sont coûteuses en termes de temps d'exécution et peuvent entraîner des effets de flash lors de l'exécution de la procédure. Il est également important de ne pas oublier de libérer les objets Selection.

Il est évident que l'on n'utilise pas d'adressage aussi long comme dans l'exemple de code que j'ai publié mais plutôt des variables objets

Dès qu'une variable est déclarée comme un objet explicite (Workbook, Worksheet, Range, PivotTable, etc.), elle hérite de toutes les propriétés et méthodes de celui-ci.

Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub t()
  Dim Wkb As Workbook
  Dim Sht As Worksheet
  Set Wkb = Workbooks("2023 Projet.xlsx")
  Set Sht = Wkb.Worksheets("t_Project")
  MsgBox Sht.Range("B2").Value
  Set Wkb = Nothing: Set Sht = Nothing
End Sub

[retraite83]

Merci de vos précisions,

Je vais tranquillement revoir mes macros les plus importantes pour ne rien casser

je dois donc "libérer" en fin de chaque macro toutes les allocations par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set xx = Nothing: Set yy = Nothing

C'est bien cela ?

Pour revenir à ma question initiale cela va-t-il m'éviter de coder :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Application.EnableEvents = False
feuille.select
Application.EnableEvents = True

[Philippe Tulliez]

Bonjour,

je dois donc "libérer" en fin de chaque macro toutes les allocations par : Set xx = Nothing: Set yy = Nothing

Ce n'est pas obligatoire et ne provoquera pas d'erreur mais c'est préférable car cela libère la mémoire réservée par VBA pour ces objets

[retraite83]

Oui je me doutai de cela

Pour revenir à ma question initiale et dans l'exemple, cela devrait m'éviter de coder :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Application.EnableEvents = False
Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value
Application.EnableEvents = True

pour éviter le déclenchement de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Private Sub Worksheet_Activate()

de la feuille "t_Project" quand cela est inutile

Je m'explique : les utilisateurs sont un peu perdus lorsque par une mauvaise manip ils ne reviennent pas en haut d'une feuille après leur travaux, aussi j'ai codé dans certaines feuilles :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Private Sub Worksheet_Activate()
 
ActiveSheet.Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
 
End Sub

du coup ce code se déclenche bien au retour sur la feuille mais aussi dans mes macros

C'est une astuce de débutant je pense, cela marche bien mais génère des effets de bords indéniables...

[Philippe Tulliez]

Bonjour,
La ligne Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value renvoie une valeur et ne provoque pas d'événement. Il n'y a donc aucune raison d'utiliser l'instruction Application.EnableEvents
Attention que cette instruction renvoyant une valeur ne peut pas être utilisée comme telle soit avec MsgBox soit remplir la valeur d'une cellule

Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveSheet.Range(A5").Value = Workbooks("2023 Projet.xlsx").Worksheets("t_Project").Range("B2").Value

Une question cruciale est de savoir si vous utilisez l'événement Worksheet_Activate ?
Si c'est juste pour sélectionner la cellule A1 d'une feuille, je n'en vois pas la raison mais peut-être l'utilisez vous pour autre chose

Si vous souhaitez qu'à chaque fois qu'un utilisateur active une feuille, la cellule A1 soit sélectionnée, utilisez l'événement du classeur Workbook_SheetActivate

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Sh.Range("A1").Select
End Sub

Vous pouvez également expliquer aux utilisateurs que la touche de raccourci Ctrl + Home permet de revenir à la cellule A1

[retraite83]

Bonjour

en fait voilà une macro type qui recouvre 90% des cas

Cette macro permet d'afficher à l'utilisateur chaque feuille remontée au top de la feuille avec le curseur sur la 1ere cellule utilisable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
Private Sub Worksheet_Activate()
 
ActiveSheet.Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("L17").Select
 
End Sub

et donc on passe par cette macro lors d'un "Select" d'une feuille dans les macros, ce qui explique le code pour éviter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Application.EnableEvents = False
feuille.select
Application.EnableEvents = True

[Philippe Tulliez]

Bonjour,
Je ne comprends pas la raison de sélectionner A1 et ensuite L17 et pas directement L17
De plus les objets ActiveWindow sont inutiles

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Private Sub Worksheet_Activate()
 
ActiveSheet.Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("L17").Select
 
End Sub

Si vous voulez absolument sélectionner la cellule A1 de la feuille activée et si vous devez le faire avec plusieurs feuilles, il est préférable d'utiliser une fonction générique ou d'utiliser la procédure événementielle Workbook_SheetActivate comme je l'ai suggéré dans l'une de mes réponses précédentes

Solution 1 avec une procédure générique nommée RangeSelect (on peut bien entendu prévoir un troisième argument si une autre cellule doit être sélectionnée). Pas besoin ici de procédure événementielle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub RangeSelect(SheetName As String, Optional oWorkbook As Workbook)
  ' Fonction générique qui active la feuille SheetName du classeur oWorkbook
  '   par défaut oWorkbook est le classeur actif
  If oWorkbook Is Nothing Then Set oWorkbook = ActiveWorkbook
  With oWorkbook
    With .Worksheets(SheetName)
    .Activate
    .Range("A1").Select
    End With
  End With
End Sub

Procédure que l'on invoque avec cette instruction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RangeSelect "Rapport"

Solution 2 avec la procédure événementielle Workbook_SheetActivate du module de classe ThisWorkbook

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Sh.Range("A1").Select
End Sub

qui peut être améliorée par la procédure ci-dessous, si toutes les feuilles ne sont pas concernées

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  With Sh
    Select Case .Name
       Case "Accueil", "Menu", "Parameter" ' Les feuilles non concernées
       Case Else
            .Range("A1").Select
    End Select
  End With
End Sub

[retraite83]

Bonjour,

je suis en train de voir tout cela

Ma première remarque

Je ne comprends pas la raison de sélectionner A1 et ensuite L17 et pas directement L17

TOUT A FAIT !

Ma seconde

De plus les objets ActiveWindow sont inutiles

En les retirant la feuille ne remonte pas à la 1ere ligne

Ensuite en codant via

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

cela ne n'évite pas le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.EnableEvents = False
feuille.select
Application.EnableEvents = True

pour éviter de déclencher ce code lors d'un "Select" dans une macro

C'est en tout cas ce que je constate

Je vais approfondir les 2 solutions proposées pour comprendre

[Philippe Tulliez]

Bonjour,
Je ne comprends toujours pas l'utilisation de ces lignes de code puisque vous souhaitez, sauf erreur de ma part, sélectionner la cellule A1 lorsque l'on sélectionne une feuille. Avec l'instruction Application.EnableEvents = False, l'événement n'aura pas lieu et donc pas de cellule A1 sélectionnée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.EnableEvents = False
feuille.select
Application.EnableEvents = True

[retraite83]

Bonjour

Avec l'instruction Application.EnableEvents = False, l'événement n'aura pas lieu et donc pas de cellule A1 sélectionnée

justement je code ceci pour ne pas déclencher l'exécution inutile du code dans mes macros

Ce code est exécuté uniquement lorsque l'utilisateur arrive sur la feuille manuellement

[deedolith]

Salut,

Pour l'avoir expérimenté, et m'y être cassé les dents un certain nombre de fois, vouloir avoir le contrôle absolu sur ce que voit l'utilisateur, c'est casse-gueule.
Autant se contenter du minimum, ça attire moins d'emmerdes.

Ensuite, vouloir éviter un évènement, c'est bien souvent le signe d'un problème de conception. Revoit les fonctionnalités que tu as implémenté pour t'assurer qu'elles correspondent bien aux specs. Demandes-toi si tu ne pourrais pas faire autrement ect ...

[retraite83]

Merci de votre réponse,

#16
Pour avoir "subit" l'informatique pendant une grande partie de ma carrière professionnelle, je ne partage pas votre approche quand vous dites

Autant se contenter du minimum, ça attire moins d'emmerdes.

Pour moi c'est à l'informatique de s'adapter à l'utilisateur et non à l'utilisateur de s'adapter à ce que veut bien lui faire l'informaticien, mais ce n'est qu'un point de vue

sur votre 2éme remarque

vouloir éviter un évènement, c'est bien souvent le signe d'un problème de conception

vous avez probablement raison, je vais y réfléchir, je prends un exemple concret :
J'ai une feuille avec du code avec l'évènement "Private Sub Worksheet_Change(ByVal Target As Range)"

Quand l'utilisateur change une valeur sur la feuille le code se déclenche, c'est l'objectif. Cependant lorsque le besoin est de faire "Select" de la feuille dans une macro, inutile voir dangereux de laisser "Private Sub Worksheet_Change(ByVal Target As Range)" se déclencher

#10
Je vais appliquer votre préconisation par contre je ne saisis pas vraiment la différence entre Worksheet_Activate et Workbook_SheetActivate

[Philippe Tulliez]

Bonjour,

Je vais appliquer votre préconisation par contre je ne saisis pas vraiment la différence entre Worksheet_Activate et Workbook_SheetActivate

La première est un événement qui concerne l'activation de la feuille et la seconde concerne l'activation de n'importe quelle feuille du classeur mais je pensais vous l'avoir expliqué dans l'une de mes réponses "Si vous souhaitez qu'à chaque fois qu'un utilisateur active une feuille, la cellule A1 soit sélectionnée, utilisez l'événement du classeur Workbook_SheetActivate"

Pour en savoir plus, je vous conseille la lecture de ces deux tutoriels

• Les évènements dans la feuille de calcul Excel
• Les évènements du module objet ThisWorkbook, dans Excel

[retraite83]

#16
Merci je n'avais pas fait attention à la nuance (importante)

[Philippe Tulliez]

Bonjour,
Je me permet de rappeler la petite astuce du raccourci clavier Ctrl + Home qui permet d'atteindre immédiatement la cellule A1.