VBA Static Variable dans Excel — une mémoire qui survit à End Sub (et qui se souvient de trop)

TL;DR — Écrivez Static au lieu de Dim dans une procédure, et la variable garde sa valeur quand la procédure se termine : Static clicks As Long compte tous les appels, pas seulement celui-ci. C'est une mémoire privée : personne hors de la procédure ne peut la voir, mais elle vit aussi longtemps qu'une globale, jusqu'à la réinitialisation du projet VBA. C'est sa force et son piège. Une Static n'oublie jamais d'elle-même : elle se souvient donc aussi d'un indicateur resté bloqué parce qu'une erreur a sauté la ligne qui le remettait à zéro, et d'un cache construit à partir de données qui ont changé depuis. Utilisez Static quand une seule procédure possède la mémoire ; dès qu'une autre procédure doit la lire ou la réinitialiser, déplacez-la dans un Private de niveau module.

Sub CountClicks()
    Static clicks As Long            ' conserve entre les appels
    clicks = clicks + 1
    Application.StatusBar = "Clicked " & clicks & " times"
End Sub

Voici le deuxième volet d'une série de trois sur l'endroit où vit une valeur. Une variable globale est visible par tout le projet et vit jusqu'à la réinitialisation du projet. Une variable Static garde cette durée de vie, mais n'est visible que par une seule procédure. Un paramètre Optional ne dure qu'un appel. La règle commune aux trois : choisissez la portée la plus étroite qui fait le travail.

Ce que vous allez apprendre

• Le modèle mental : Static, c'est une portée locale avec une durée de vie de module
• Static, Dim et un Private de niveau module, côte à côte
• Quatre usages où Static excelle : compteurs, bascules, verrous anti-réentrance, caches
• Les deux façons dont une Static se souvient de trop : indicateurs bloqués et caches périmés
• Pourquoi toutes les cellules qui appellent une fonction de feuille partagent une même Static
• Static Sub, et quand sortir une Static de sa procédure

Le modèle mental : portée locale, durée de vie de module

Un Dim dans une procédure est un bloc-notes jetable : il est créé au démarrage de la procédure et jeté à la fin, si bien que l'appel suivant repart de zéro. Une Static dans une procédure est un tiroir dont seule cette procédure a la clé. La procédure peut y laisser quelque chose, se terminer, et le retrouver au prochain appel.

La deuxième ligne est la seule qui combine une portée étroite et une longue vie. « Jusqu'à la réinitialisation du projet » veut dire la même chose que pour une globale : End, le bouton Réinitialiser, une erreur non gérée que l'utilisateur termine, certaines modifications du code ou la fermeture du classeur, tout cela étant détaillé dans le guide des variables globales.

Déclarer une Static, et la valeur au premier appel

Static n'est autorisé qu'à l'intérieur d'une procédure, à la place de Dim :

Function NextInvoiceNo() As Long
    Static lastNo As Long            ' 0 au tout premier appel
    lastNo = lastNo + 1
    NextInvoiceNo = lastNo
End Function

VBA n'a pas d'initialiseur : Static lastNo As Long = 1000 est donc une erreur de syntaxe. Au premier appel, une Static contient la valeur vide de son type — 0, "", Empty ou Nothing — et c'est à vous de lui donner une vraie valeur de départ. Quand 0 peut être une valeur réelle, suivez le premier appel avec une seconde Static :

Function NextInvoiceNo() As Long
    Static lastNo As Long, started As Boolean
    If Not started Then
        lastNo = ThisWorkbook.Worksheets("Settings").Range("B3").Value
        started = True
    End If
    lastNo = lastNo + 1
    NextInvoiceNo = lastNo
End Function

Remarquez ce que cette version reconnaît : la Static n'est qu'une copie rapide, et le vrai numéro vit dans le classeur. Un compteur qui doit survivre à la fermeture du fichier doit y être réécrit, car aucune variable ne survit au projet.

Quatre usages où Static excelle

Une bascule. Un bouton unique qui active et désactive quelque chose doit connaître son dernier état :

Sub ToggleGridlines()
    Static isOff As Boolean
    isOff = Not isOff
    ActiveWindow.DisplayGridlines = Not isOff
End Sub

Un compteur ou un total cumulé qui appartient à une seule routine, comme le numéro de facture ci-dessus.

Un verrou anti-réentrance pour un gestionnaire d'événement qui modifie la feuille qu'il surveille. Écrire dans une cellule à l'intérieur de Worksheet_Change redéclenche Worksheet_Change ; un indicateur Static coupe la boucle :

Private Sub Worksheet_Change(ByVal Target As Range)
    Static busy As Boolean
    If busy Then Exit Sub
    busy = True
    On Error GoTo Done
    Target.Offset(0, 1).Value = Now  ' redeclencherait cet evenement
Done:
    busy = False
End Sub

Un cache pour une table de correspondance lente à construire et dont une seule procédure a besoin :

Function RegionOf(ByVal code As String) As String
    Static map As Object
    If map Is Nothing Then
        Dim r As Range
        Set map = CreateObject("Scripting.Dictionary")
        For Each r In ThisWorkbook.Worksheets("Regions").Range("A2:A500")
            map(r.Value) = r.Offset(0, 1).Value
        Next r
    End If
    If map.Exists(code) Then RegionOf = map(code)
End Function

Le test If map Is Nothing fait double emploi : il construit le cache au premier appel, et le reconstruit après qu'une réinitialisation du projet l'a vidé. C'est le schéma autoréparant du guide des variables globales, avec un cache gardé privé dans la seule fonction qui l'utilise.

La règle qui mord : une Static n'oublie jamais d'elle-même

Tous les bogues de Static sont le même bogue : la valeur a survécu à la situation pour laquelle elle était valable.

L'indicateur bloqué. Reprenez le verrou anti-réentrance ci-dessus et supprimez la ligne On Error GoTo Done. Désormais, toute erreur après busy = True — une cellule protégée, une incompatibilité de type — termine le gestionnaire avant que busy = False ne s'exécute. L'indicateur reste à True, et à partir de là la première ligne sort à chaque fois : le gestionnaire d'événement est mort en silence jusqu'à la réinitialisation du projet. L'utilisateur voit une feuille qui a cessé de se mettre à jour, sans aucun message d'erreur. Un indicateur de verrou doit être remis à zéro sur chaque chemin de sortie, et c'est pourquoi le gestionnaire d'erreur saute à la ligne qui le remet à zéro. Le guide EnableEvents couvre l'autre verrou courant, avec la même règle.

Le cache périmé. RegionOf construit sa table une seule fois. Si quelqu'un ajoute ensuite une région dans la feuille, la fonction continue de répondre à partir de l'ancienne table, toute la journée, car rien ne lui dit que les données ont changé. Et rien hors de la fonction ne peut le lui dire, puisque la Static est invisible de l'extérieur. C'est la vraie limite de Static : vous ne pouvez pas la réinitialiser depuis une autre procédure.

La surprise des UDF : toutes les cellules partagent une même Static

Une Static appartient à la procédure, pas à l'endroit d'où elle est appelée. Pour une fonction de feuille, cela signifie que toutes les cellules qui l'appellent partagent une seule variable :

Function CallCount() As Long
    Static n As Long
    n = n + 1
    CallCount = n
End Function
' =CallCount() en A1, A2 et A3 ne donne pas 1, 1, 1 -
' cela donne trois nombres differents, dans l'ordre de calcul

C'est Excel qui décide de l'ordre de calcul, et il peut recalculer n'importe quelle cellule à n'importe quel moment : une fonction personnalisée (UDF) dont la réponse dépend d'une Static donne donc des résultats qui changent tout seuls. Le guide Function explique pourquoi une fonction de feuille ne doit dépendre que de ses arguments. Un cache Static dans une UDF, comme la table de RegionOf, ne pose pas de problème, car il change la vitesse à laquelle la réponse arrive, pas la réponse elle-même. Un compteur Static ou un total cumulé dans une UDF est un bogue.

Static Sub, et Static dans un UserForm

Placer Static devant la procédure rend statiques toutes ses variables locales :

Static Sub Tally()
    Dim total As Double              ' statique elle aussi, a cause de Static Sub
    total = total + 1
End Sub

Cela existe, mais évitez-le. Un lecteur voit Dim et suppose une variable neuve, et le mot-clé unique en tête qui dit le contraire passe facilement inaperçu. Marquez individuellement les variables qui ont besoin de mémoire.

Les variables Static des procédures d'un UserForm ne durent que tant que le formulaire reste chargé. Après Unload, le Show suivant repart avec des valeurs neuves — la même durée de vie que les autres variables du formulaire, décrite dans le guide UserForm.

Le jugement : Static, jusqu'à ce qu'une deuxième procédure en ait besoin

Static est le moyen le plus étroit de donner une mémoire à une procédure, et l'étroitesse est une qualité : rien d'autre ne peut modifier la valeur par accident, et la déclaration se trouve juste à côté du code qui l'utilise. Ma règle : utilisez Static quand une seule procédure lit et écrit la mémoire, et que rien d'autre n'a jamais besoin de la vider. Dès qu'une deuxième procédure doit la voir, ou qu'il vous faut un bouton « Actualiser » qui vide un cache, déplacez la variable en tête du module en Private, et ajoutez une petite procédure qui la réinitialise :

Private mMap As Object

Sub ResetRegionCache()
    Set mMap = Nothing               ' la prochaine recherche la reconstruit
End Sub

Cela garde la portée aussi étroite que le nouveau besoin le permet, et évite de sauter directement à une globale Public.

Comment ExcelMaster vous aide

Les bogues de Static ressemblent à des fantômes : un gestionnaire d'événement qui cesse de se déclencher sans raison visible, une recherche qui ignore la ligne que vous venez d'ajouter, une UDF dont les nombres bougent à chaque recalcul de la feuille.

ExcelMaster vous laisse décrire ce dont la macro doit se souvenir, par exemple « numérote chaque nouvelle facture à partir de la dernière utilisée ». Il choisit le bon emplacement pour cette mémoire — une Static, une variable de niveau module avec une réinitialisation, ou une cellule du classeur — et ajoute la gestion d'erreur qui empêche un indicateur de verrou de rester bloqué.

Questions fréquentes

Que signifie Static en VBA ?

Static déclare, dans une procédure, une variable qui garde sa valeur après la fin de la procédure. L'appel suivant voit la valeur laissée par le précédent. Seule cette procédure peut voir la variable, et la valeur est perdue quand le projet VBA se réinitialise ou que le classeur se ferme.

Quelle est la différence entre Static et Dim en VBA ?

Une variable Dim dans une procédure démarre vide à chaque appel et disparaît à la fin de la procédure. Une variable Static ne démarre vide qu'au premier appel et garde sa valeur entre les appels. Les deux ne sont visibles qu'à l'intérieur de leur procédure.

Comment conserver la valeur d'une variable entre deux exécutions d'une macro ?

Déclarez-la avec Static dans la procédure si elle seule en a besoin, ou en Private en tête du module si plusieurs procédures en ont besoin. Les deux gardent la valeur jusqu'à la réinitialisation du projet. Pour la conserver après la fermeture du classeur, stockez-la dans une cellule ou un nom défini.

Peut-on donner une valeur de départ à une variable Static ?

Pas dans la déclaration : VBA n'a pas d'initialiseur, donc Static n As Long = 5 est une erreur de syntaxe. La variable démarre à 0, "", Empty ou Nothing. Définissez une valeur de départ au premier appel, en utilisant une seconde Static de type Boolean pour savoir si ce premier appel a déjà eu lieu.

Comment réinitialiser une variable Static ?

Seule la procédure qui la déclare peut la modifier : donnez donc à cette procédure un moyen de la réinitialiser, ou déplacez la variable au niveau module en Private et ajoutez une petite procédure de réinitialisation. Réinitialiser tout le projet VBA la vide aussi, avec toutes les autres variables de niveau module et variables publiques.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1 — dernière vérification le 01/10/2026.

Guides associés : VBA Global Variable · VBA Optional Parameter · VBA Dim · VBA Function · VBA Worksheet Change · VBA EnableEvents · VBA Dictionary · VBA UserForm