Discussion :

[deedolith]

Bonjour,

Suite à plusieurs discussions sur la manipulation du statut de l'activation des évènements, calculs, affichage, protection des feuilles ect ... qui est source de conflit,
je me suis penché sur le problème.

Historiquement, des problèmes peuvent apparaitre lors de l'appel de sous-fonction qui manipulent ces status, dont la fonction appelante n'a pas conscience (et elle n'a pas à s'en préoccuper).
Par exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Public Sub Foo()
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
 
    Bar
    '// code qui manipule l'affichage
 
    Application.ScreenUpdating = True
End Sub
 
Private Sub Bar()
    Application.ScreenUpdating = False
 
    '// code qui manipule l'affichage
 
    Application.ScreenUpdating = True
End Sub

La fonction Bar rétablit l'affichage, en conséquence, les instructions suivantes mettant à jour l'affichage provoquent d'horribles clignotements à l'écran, ce qui dégrade l'expérience utilisateur.

Une première solution peut consister à ne désactiver l'affichage que dans la fonction de plus haut niveau:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Public Sub Foo()
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
 
    Bar
    '// code qui manipule l'affichage
 
    Application.ScreenUpdating = True
End Sub
 
Private Sub Bar()
    '// code qui manipule l'affichage
End Sub

Mais, cela implique que la fonction appelante aie une idée précise du comportement des fonctions appelées,
et que les fonctions appelées assument que la fonction appelante a pris ses dispositions.
Ce qui en contradiction avec la Loi de Demeter.

Une seconde solution peut consister à mémoriser l'état de l'affichage en début de fonction, et le restituer en fin de fonction:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Public Sub Foo()
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
 
    Bar
    '// code qui manipule l'affichage
 
    Application.ScreenUpdating = True
End Sub
 
Private Sub Bar()
    Dim ScreenUpdating As Boolean
    ScreenUpdating = Application.ScreenUpdating
 
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
 
    Application.ScreenUpdating = ScreenUpdating
End Sub

C'est correcte, mais c'est une approche défensive de la programmation, d'une part lourdingue, d'autre part qui détourne le développeur de sa tâche première: Produire une fonction avec le comportement escompté.

N'y a t'il pas un moyen d'automatiser la sauvegarde / restauration de ces status ?
Je pense que oui, via une classe au final fort simple, qu'il suffira d'instancier (et oublier sa présence):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
'// Class ApplicationStateHolder
Option Explicit
 
    '// référence vers l'application
Private mApp As Excel.Application
 
    '// Status que l'on désire mémoriser
Private mEnableEvents As Boolean
Private mScreenUpdating As Boolean
Private mCalculation As Boolean
 
    '// Pseudo-constructeur
    '// Memorisation des status
Friend Sub Create(ByRef App As Excel.Application)
    Set mApp = App
    mEnableEvents = mApp.EnableEvents
    mScreenUpdating = mApp.ScreenUpdating
    mCalculation = mApp.Calculation
End Sub
 
    '// Destructeur
    '// Restitution des status
Private Sub Class_Terminate()
    mApp.enableevent = mEnableEvents
    mApp.sceenupdating = mScreenUpdating
End Sub

Cette classe nécéssite un module Factory pour être instanciée:

Code :

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

1
2
3
4
5
6
7
8
9
10
'// Module: Factory
Option Explicit
 
Public Function CreateApplicationStateHolder(ByRef App As Excel.Application) As ApplicationStateHolder
    Dim State As ApplicationStateHolder
    Set State = New ApplicationStateHolder
 
    State.Create App
    Set CreateApplicationStateHolder = State
End Function

Demo:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Sub Foo()
    Dim State As ApplicationStateHolder
    Set State = Factory.CreateApplicationStateHolder(Application)
 
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
 
    Bar
    '// code qui manipule l'affichage
End Sub
 
Private Sub Bar()
    Dim State As ApplicationStateHolder
    Set State = Factory.CreateApplicationStateHolder(Application)
 
    Application.ScreenUpdating = False
    '// code qui manipule l'affichage
End Sub

Lorsqu'une fonction se termine, l'instance State est détruite, ce qui entraine la restauration des status sauvegardés en début de fonction.
Les fonctions Foo et Bar peuvent maintenant manipuler l'affichage pour leurs besoins propre, sans avoir à se soucier de ce qu'on fait leurs prédécesseurs, ni ce que feront leurs successeurs,
elles sont plus facile à lire et à comprendre.
Des classes reposant sur le même principe, concernant les feuilles (protection) ou autre objet sont envisageable.

[Pierre Fauconnier]

Bonjour.

Si on architecture correctement son code, on n'a pas pas besoin de tous ces montages.

Les procédures événementielles gèrent les propriétés d'application et appellent les fonctions qui réalisent le travail applicatif. Il n'y a alors jamais de conflit ni de restauration d'options d'application intempestives.

[deedolith]

C'est un vœux pieux.

Je craint que la réalité du terrain ne soit tout autre.

[Thumb down]

Bonsoir,
Ta proposition pour résoudre les conflits de manipulation des statuts dans les macros Excel est vraiment bien pensée. Voici quelques points à prendre en considération :

1. Simplicité d'utilisation : Ta solution avec la classe ApplicationStateHolder et le module Factory rend la gestion des statuts super facile. En encapsulant la logique de sauvegarde et de restauration dans une classe dédiée, ça simplifie grandement le travail pour les développeurs. Cela réduit aussi les risques d'erreurs et facilite la maintenance du code.
2. Conformité aux principes de programmation : Ta façon de faire respecte des principes solides de programmation comme la Loi de Demeter en réduisant les dépendances entre les différentes parties du code. Ça rend le code plus modulaire et réutilisable, ce qui est super important pour développer des applications solides et évolutives.
3. Extensibilité : Ta proposition suggère même d'étendre cette approche à d'autres aspects de la manipulation des feuilles Excel, comme la protection des feuilles. Ça montre que ton idée est flexible et peut répondre à différents besoins.
4. Gestion des erreurs : Ça pourrait être une bonne idée d'ajouter une gestion des erreurs dans ta classe ApplicationStateHolder pour être sûr que les statuts sont toujours restaurés même en cas d'erreur pendant l'exécution des macros.
5. Documentation et bonnes pratiques : Il serait bien d'accompagner cette solution d'une documentation claire expliquant son fonctionnement et ses avantages, ainsi que des bonnes pratiques pour son utilisation. Ça assurerait une bonne adoption par d'autres développeurs.

En résumé, ta proposition semble être une solution efficace et élégante pour résoudre les conflits de manipulation des statuts dans les macros Excel. Elle est simple, conforme aux principes de programmation et extensible pour répondre à différents besoins.

[Pierre Fauconnier]

C'est un vœux pieux.

Je craint que la réalité du terrain ne soit tout autre.

Dans un projet VBA, de mon expérience, on est souvent tout seul. Donc soit on code selon les règles et on n'a pas besoin d'une classe pour gérer les options d'application, soit pas et on oubliera d'utiliser une classe (dans chaque fonction?) et de toute façon le code sera merdique sous d'autres aspects. Et si on voulait être exhaustif, on ne se limiterait pas à trois propriétés d'application.

Donc perso, je ne vois pas l'utilité de la chose.

[laurent_ott]

Bonjour,
Je trouve l'approche intéressante.

J'ai testé après avoir corrigé quelques coquilles dans le module de classe présenté :
- lignes 24 et 25 : les noms des variables ne sont pas corrects soit "mApp.EnableEvents = mEnableEvents" et "mApp.ScreenUpdating = mScreenUpdating"
- il manque la suite logique : "mApp.Calculation = mCalculation" ;
- ligne 10 : "Private mCalculation As Long" et pas "As Boolean".

J'ai aussi ajouté une variable pour gérer la forme du curseur, et tout semble fonctionner correctement.

[deedolith]

@Laurent_ott:
C'est une proposition (ou un POC), il faut vraiment la prendre comme cela.
L'essentiel reste le principe.

A adapter selon les besoins.

[laurent_ott]

Bonjour,
Une autre proposition sans passer par un module de classe.
- La fonction MémoriserStatuts est appelée en début de traitement pour mémoriser les statuts.
- La fonction RestaurerStatuts est appelée à sa sortie (par End Sub, End Function ou Exit...)
J'ai prévu aussi une initialisation avec les valeurs par défaut des statuts (au cas où) : InitialiserStatuts

L'avantage est qu'il n'y a pas de variable à déclarer, l'inconvénient est qu'il ne faut pas oublié l'appelle de la fonction RestaurerStatuts car les statuts sont mémorisés sous forme de pile.

Exemple d'utilisation :

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
Public Sub Foo()
    MémoriserStatuts
    Application.ScreenUpdating = False
    Bar
    RestaurerStatuts
End Sub
 
Private Sub Bar()
    MémoriserStatuts
    Application.ScreenUpdating = False
    RestaurerStatuts
End Sub

Le code à mettre dans un module classique :

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Private Type TypeStatuts
    mEnableEvents As Boolean
    mScreenUpdating As Boolean
    mCalculation As Long
    mCursor As Long
End Type
 
'------------------------------------------------------------------------------------------------------
Public Sub MémoriserStatuts()
'------------------------------------------------------------------------------------------------------
Call StockStatuts(1)
End Sub
 
'------------------------------------------------------------------------------------------------------
Public Sub RestaurerStatuts()
'------------------------------------------------------------------------------------------------------
Call StockStatuts(2)
End Sub
 
'------------------------------------------------------------------------------------------------------
Public Sub InitialiserStatuts()
'------------------------------------------------------------------------------------------------------
Call StockStatuts(0)
End Sub
 
'------------------------------------------------------------------------------------------------------
Private Sub StockStatuts(Action As Byte)
'------------------------------------------------------------------------------------------------------
Static MémoStatus() As TypeStatuts
Static i As Integer
 
Select Case Action
 
    Case 1 ' Mémoriser
        If i < 32767 Then i = i + 1
        ReDim Preserve MémoStatus(0 To i)
        MémoStatus(i).mEnableEvents = Application.EnableEvents
        MémoStatus(i).mScreenUpdating = Application.ScreenUpdating
        MémoStatus(i).mCalculation = Application.Calculation
        MémoStatus(i).mCursor = Application.Cursor
 
    Case 2 ' Restaurer
        Application.EnableEvents = MémoStatus(i).mEnableEvents
        Application.ScreenUpdating = MémoStatus(i).mScreenUpdating
        Application.Calculation = MémoStatus(i).mCalculation
        Application.Cursor = MémoStatus(i).mCursor
        If i > 0 Then i = i - 1
        ReDim Preserve MémoStatus(0 To i)
 
    Case 0 ' Réinitialiser
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.Cursor = xlDefault
        i = 0
 
End Select
 
End Sub
'------------------------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------------------------

[deedolith]

@Laurent_ott:
C'est moins versatile,
en cause le fait que tu assumes qu'il n'existe qu'une seule et unique instance de type application, alors qu'il est possible que plusieurs instances coexistent.
Exemple:

Code :

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

1
2
3
4
5
6
7
8
Sub test()
    Dim App As Excel.Application
    Set App = CreateObject("Excel.application")
    App.EnableEvents = False
 
    Debug.Print "App.EnableEvents : " & App.EnableEvents
    Debug.Print "Application.EnableEvents : " & Application.EnableEvents
End Sub

Ce qui posduit l'affichage dans la fenêtre d'execution:

Code :

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

1
2
App.EnableEvents : Faux
Application.EnableEvents : Vrai

De plus, comme tu le soulignes, cette approche oblige à se soucier de défaire manuellement ce qui a été fait, ce qui est source d'erreur (un oubli est si vite arrivé).
Quand a gérer une pile avec un tableau ... à mon avis, c'est plus simple avec une collection.