VBA: quel type de variable pour Application.ScreenUpdating?

Bonjour à tous,

Je développe une macro sous VBA. Le temps d'exécution devenant problématique j'essaie de l'optimiser (comme je peux). J'ai trouvé sur le web une 1ère astuce qui consiste à inhiber certaines fonctionnalités superflues d'Excel - telle l'instruction Application.ScreenUpdating - j'ai donc codé ce qui suit ci-dessous

Sub MaMacro()
'Save the current state of Excel settings
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.ScreenUpdating
calcState = Application.Calculation
eventsState = Application.EnableEvents
'Note: the following is a sheet-level setting
displayPageBreakState = ActiveSheet.DisplayPageBreak

'Turn off Excel functionality to improve performance
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'Note: this is a sheet-level setting
ActiveSheet.DisplayPageBreaks = False

'My code...

'At the end of the code
'Restore Excel settings to original state
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
'Note: this is a sheet-level setting
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

End Sub

Jusque là tout va bien.

J'ai ensuite lu une autre astuce trouvée sur Internet:

La déclaration du type de variable est une première optimisation extrêmement classique et indispensable. Afin de n'oublier aucune déclaration, utiliser en tête de module ou de Form etc... la fonction :

Option Explicit

d’une manière générale, l’emploi du type Variant (autre que dans les appels de fonctions) n'est jamais indispensable, et est à proscrire pour l’optimisation. Ce type est surtout intéressant pour faciliter la programmation aux débutants. Mais cette facilité se paye par une baisse importante des performances

Du coup j'ai ajouté

Option Explicit
Sub MaMacro()

mais ce faisant j'ai ensuite le message d'erreur "Erreur de compilaltion ; Variable non définie" sur l'instruction

screenUpdateState = Application.ScreenUpdating

ce que je comprends assez bien, après coup.

D'où ma question: de quels types dois-je déclarer les variables suivantes ?

screenUpdateState

statusBarState

calcState

eventsState

displayPageBreakState

Nota: si je mets dans mon code - par exemple

Dim screenUpdateState
screenUpdateState = Application.ScreenUpdating

je n'ai plus de message d'erreur mais je me demande si ma déclaration 'simple' (sans rien)

Dim screenUpdateState

est adéquate?

Vos avis et éléments de réponses me seront d'une aide certaine.

Merci pour le temps que vous avez pris à lire ce post.

Pascal

Bonjour

De tous les états "avant" le début d'un macro, le rafraichissement écran n'est pas utile de sauvegarder

Une astuce : Tu te positionnes sur un mot et tu appuies sur F1

Par exemple en te positionnant sur le mot ScreenUpdating tu obtiendrais l'aide sur ce mot et entre autre tu aurais

Cette propriété a la valeur True si l'actualisation de l'écran est activée. Type de données Boolean en lecture-écriture.

Comme ça tu connais le type de donnée pour typer ta variable de sauvegarde

Dans toute la liste des "états" à sauvegarder 1 seul est important (tout est relatif), c'est Calculation, c'est pour replacer dans le même état à la sortie de la macro

Mon avis : Les principaux utilisés (sauf erreur)

Option Explicit

Sub Test()
Dim Calcul As Integer

  With Application
    .ScreenUpdating = False
    Calcul = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
  End With

  '
  ' Ta macro
  '

  With Application
    .ScreenUpdating = True  ' Inutile 99 fois sur 100
    .EnableEvents = True    ' Important
    .Calculation = Calcul   ' Replace dans le même état
  End With
End Sub

Un dernier truc : Pour accélérer sensiblement une macro c'est passer par des tableaux virtuels (en mémoire) mais c'est une autre façon de programmer, car le temps d’accès à un élément pour le lire ou pour écrire est très rapide

Merci Banzai64 pour l'astuce:

Tu te positionnes sur un mot et tu appuies sur F1

merci aussi pour le type "Boolean" pour la déclaration des variables, ça répond à ma question première.

En revanche concernant

Calcul = .Calculation

tu le déclares Integer alors que d'autres le déclareraient en Long.

Bonsoir

C'est vrai que dans l'aide de Calculation on n'a pas le type de donnée

mais on a ça

xlCalculationAutomatic -4105 Excel contrôle le recalcul.

xlCalculationManual -4135 Le calcul est effectué sur demande de l'utilisateur.

xlCalculationSemiautomatic 2 Excel contrôle le recalcul, mais ignore des modifications apportées aux tableaux

On voit que les valeurs s'accordent bien à un type Integer Les variables de type Integer sont stockées sous la forme de nombres de 16 bits (2 octets) dont la valeur est comprise entre -32 768 et 32 767.

Bien sur Long Les variables de type Long (entier long) sont stockées sous la forme de nombres signés de 32 bits (4 octets) dont la valeur est comprise entre -2 147 483 648 et 2 147 483 647 irait aussi bien mais c'est utiliser un canon pour tuer un moustique (c'est démésuré)