VBA
Déclaration des variables

Déclaration implicite et explicite

Si un module de code ne contient pas Option Explicit en haut du module, le compilateur créera automatiquement (c'est-à-dire "implicitement") des variables lorsque vous les utiliserez. Ils utiliseront par défaut le type de variable Variant .

Public Sub ExampleDeclaration()    

    someVariable = 10                  ' 
    someOtherVariable = "Hello World"
    'Both of these variables are of the Variant type.

End Sub

Dans le code ci-dessus, si Option Explicit est spécifié, le code sera interrompu car il manque les instructions Dim requises pour someVariable et someOtherVariable .

Option Explicit

Public Sub ExampleDeclaration()   

    Dim someVariable As Long 
    someVariable = 10

    Dim someOtherVariable As String
    someOtherVariable = "Hello World"

End Sub

Il est recommandé d’utiliser Option Explicit dans les modules de code pour vous assurer que vous déclarez toutes les variables.

Voir Meilleures pratiques de VBA pour définir cette option par défaut.

Les variables

Portée

Une variable peut être déclarée (au niveau de visibilité croissant):

• Au niveau de la procédure, en utilisant le mot-clé Dim dans n'importe quelle procédure; une variable locale .
• Au niveau du module, en utilisant le mot-clé Private dans tout type de module; un domaine privé .
• Au niveau de l'instance, en utilisant le mot clé Friend dans tout type de module de classe; un champ ami .
• Au niveau de l'instance, en utilisant le mot clé Public dans tout type de module de classe; un domaine public .
• Globalement, en utilisant le mot-clé Public dans un module standard ; une variable globale .

Les variables doivent toujours être déclarées avec la plus petite portée possible: préférer transmettre des paramètres aux procédures plutôt que de déclarer des variables globales.

Voir Modificateurs d'accès pour plus d'informations.

Variables locales

Utilisez le mot-clé Dim pour déclarer une variable locale :

Dim identifierName [As Type][, identifierName [As Type], ...]

La partie [As Type] de la syntaxe de déclaration est facultative. Une fois spécifié, il définit le type de données de la variable, qui détermine la quantité de mémoire allouée à cette variable. Cela déclare une variable String :

Dim identifierName As String

Lorsqu'un type n'est pas spécifié, le type est implicitement Variant :

Dim identifierName 'As Variant is implicit

La syntaxe VBA prend également en charge la déclaration de plusieurs variables dans une seule instruction:

Dim someString As String, someVariant, someValue As Long

Notez que le type [As Type] doit être spécifié pour chaque variable (autre que les variantes). C'est un piège relativement courant:

Dim integer1, integer2, integer3 As Integer 'Only integer3 is an Integer. 
                                            'The rest are Variant.

Variables statiques

Les variables locales peuvent également être Static . Dans VBA, le mot-clé Static est utilisé pour créer une variable "rappelant" la valeur obtenue, la dernière fois qu'une procédure a été appelée:

Private Sub DoSomething()
    Static values As Collection
    If values Is Nothing Then
        Set values = New Collection
        values.Add "foo"
        values.Add "bar"
    End If
    DoSomethingElse values
End Sub

Ici, la collection de values est déclarée en tant que local Static ; parce que c'est une variable objet , elle est initialisée à Nothing . La condition qui suit la déclaration vérifie si la référence à l'objet a été Set avant - si c'est la première fois que la procédure est exécutée, la collection est initialisée. DoSomethingElse peut ajouter ou supprimer des éléments, et ils seront toujours dans la collection la prochaine fois que DoSomething est appelé.

Alternative

Le mot-clé Static de VBA peut facilement être mal compris, en particulier par les programmeurs expérimentés qui travaillent généralement dans d'autres langues. Dans de nombreux langages, static est utilisé pour faire qu'un membre de classe (field, property, method, ...) appartienne au type plutôt qu'à l' instance . Le code en contexte static ne peut pas référencer le code dans le contexte de l' instance . Le mot-clé VBA Static signifie quelque chose de très différent.

Souvent, un local Static pourrait tout aussi bien être implémenté en tant que variable Private niveau module (module), mais cela remet en cause le principe selon lequel une variable doit être déclarée avec la plus petite portée possible; Faites confiance à vos instincts, utilisez celui que vous préférez - les deux fonctionneront ... mais l'utilisation de Static sans comprendre ce qu'il fait pourrait conduire à des bogues intéressants.

Dim vs privé

Le mot-clé Dim est légal au niveau des procédures et des modules. son utilisation au niveau du module équivaut à utiliser le mot clé Private :

Option Explicit
Dim privateField1 As Long 'same as Private privateField2 as Long
Private privateField2 As Long 'same as Dim privateField2 as Long

Le mot clé Private est uniquement légal au niveau du module. Cela invite à réserver Dim pour les variables locales et à déclarer les variables de module avec Private , en particulier avec le mot-clé Public contrastant qui devrait être utilisé pour déclarer un membre public. Vous pouvez également utiliser Dim partout - ce qui compte, c'est la cohérence :

"Champs privés"

• NE PAS utiliser Private de déclarer une variable au niveau du module.
• NE PAS utiliser Dim pour déclarer une variable locale.
• N'utilisez pas Dim pour déclarer une variable de niveau module.

"Dim partout"

• NE PAS utiliser Dim pour déclarer quoi que ce soit privé / local.
• N'utilisez PAS Private pour déclarer une variable de niveau module.
• ÉVITER de déclarer Public champs Public . *

_{* En général, il faut éviter de déclarer Public champs Public ou Global toute façon.}

Des champs

Une variable déclarée au niveau du module, dans la section déclarations en haut du corps du module, est un champ . Un champ Public déclaré dans un module standard est une variable globale :

Public PublicField As Long

Une variable avec une portée globale est accessible de n'importe où, y compris d'autres projets VBA qui référenceraient le projet dans lequel elle a été déclarée.

Pour rendre une variable globale / publique, mais uniquement visible depuis le projet, utilisez le modificateur Friend :

Friend FriendField As Long

Cela est particulièrement utile dans les compléments, où l'intention est que d'autres projets VBA référencent le projet de complément et puissent consommer l'API publique.

Friend FriendField As Long 'public within the project, aka for "friend" code
Public PublicField As Long 'public within and beyond the project

Les champs d'amis ne sont pas disponibles dans les modules standard.

Champs d'instance

Une variable déclarée au niveau du module, dans la section déclarations en haut du corps d'un module de classe (y compris ThisWorkbook , ThisDocument , Worksheet , UserForm et les modules de classe ), est un champ instance : il existe seulement tant qu'il y a une instance de la classe autour.

'> Class1
Option Explicit
Public PublicField As Long

'> Module1
Option Explicit
Public Sub DoSomething()
    'Class1.PublicField means nothing here
    With New Class1
        .PublicField = 42
    End With
    'Class1.PublicField means nothing here
End Sub

Champs d'encapsulation

Les données d’instance sont souvent conservées en mode Private et copiées sous forme de doublure. Un champ privé peut être exposé à l'aide d'une procédure Property . Pour exposer publiquement une variable privée sans donner d'accès en écriture à l'appelant, un module de classe (ou un module standard) implémente un membre Property Get :

Option Explicit
Private encapsulated As Long

Public Property Get SomeValue() As Long
    SomeValue = encapsulated
End Property

Public Sub DoSomething()
    encapsulated = 42
End Sub

La classe elle-même peut modifier la valeur encapsulée, mais le code appelant peut uniquement accéder aux membres Public (et aux membres Friend , si l'appelant est dans le même projet).

Permettre à l'appelant de modifier:

• Une valeur encapsulée, un module expose un membre Property Let .
• Une référence d'objet encapsulé, un module expose un membre de Property Set .

Constantes (Const)

Si vous avez une valeur qui ne change jamais dans votre application, vous pouvez définir une constante nommée et l'utiliser à la place d'une valeur littérale.

Vous pouvez utiliser Const uniquement au niveau du module ou de la procédure. Cela signifie que le contexte de déclaration d'une variable doit être une classe, une structure, un module, une procédure ou un bloc, et ne peut pas être un fichier source, un espace de noms ou une interface.

Public Const GLOBAL_CONSTANT As String = "Project Version #1.000.000.001"
Private Const MODULE_CONSTANT As String = "Something relevant to this Module"

Public Sub ExampleDeclaration()    

    Const SOME_CONSTANT As String = "Hello World"
    
    Const PI As Double = 3.141592653

End Sub

Bien qu'il puisse être considéré comme une bonne pratique de spécifier des types de constantes, cela n'est pas obligatoire. Ne pas spécifier le type entraînera toujours le type correct:

Public Const GLOBAL_CONSTANT = "Project Version #1.000.000.001" 'Still a string
Public Sub ExampleDeclaration()

    Const SOME_CONSTANT = "Hello World"           'Still a string
    Const DERIVED_CONSTANT = SOME_CONSTANT        'DERIVED_CONSTANT is also a string
    Const VAR_CONSTANT As Variant = SOME_CONSTANT 'VAR_CONSTANT is Variant/String
    
    Const PI = 3.141592653        'Still a double
    Const DERIVED_PI = PI         'DERIVED_PI is also a double
    Const VAR_PI As Variant = PI  'VAR_PI is Variant/Double
    
End Sub

Notez que ceci est spécifique aux constantes et que contrairement aux variables ne spécifiant pas le type, cela donne un type de variante.

Bien qu'il soit possible de déclarer explicitement une constante en tant que chaîne, il n'est pas possible de déclarer une constante en tant que chaîne en utilisant une syntaxe de chaîne à largeur fixe

'This is a valid 5 character string constant
Const FOO As String = "ABCDE"

'This is not valid syntax for a 5 character string constant
Const FOO As String * 5 = "ABCDE"

Modificateurs d'accès

L'instruction Dim doit être réservée aux variables locales. Au niveau du module, préférez les modificateurs d'accès explicite:

• Private pour les champs privés, auxquels on ne peut accéder que dans le module dans lequel ils ont été déclarés.
• Public pour les champs publics et les variables globales, accessibles par n'importe quel code d'appel.
• Friend pour les variables publiques dans le projet, mais inaccessible aux autres projets VBA de référence (pertinent pour les compléments)
• Global peut également être utilisé pour Public champs Public dans les modules standard, mais est illégal dans les modules de classe et est de toute façon obsolète - préférez plutôt le modificateur Public . Ce modificateur n'est pas légal pour les procédures non plus.

Les modificateurs d'accès sont applicables aux variables et aux procédures.

Private ModuleVariable As String
Public GlobalVariable As String

Private Sub ModuleProcedure()

    ModuleVariable = "This can only be done from within the same Module"

End Sub

Public Sub GlobalProcedure()

    GlobalVariable = "This can be done from any Module within this Project"

End Sub

Module privé d'option

Les Sub procédures publiques sans paramètre dans les modules standard sont exposées en tant que macros et peuvent être associées à des contrôles et à des raccourcis clavier dans le document hôte.

Inversement, les procédures de Function publique dans les modules standard sont exposées en tant que fonctions définies par l'utilisateur dans l'application hôte.

La spécification Option Private Module en haut d'un module standard empêche ses membres d'être exposés en tant que macros et UDF à l'application hôte.

Type conseils

Les indicateurs de type sont fortement déconseillés. Ils existent et sont documentés ici pour des raisons historiques et de compatibilité descendante. Vous devez utiliser la syntaxe As [DataType] place.

Public Sub ExampleDeclaration()

        Dim someInteger% '% Equivalent to "As Integer"
        Dim someLong&    '& Equivalent to "As Long"
        Dim someDecimal@ '@ Equivalent to "As Currency"
        Dim someSingle!  '! Equivalent to "As Single"
        Dim someDouble#  '# Equivalent to "As Double"
        Dim someString$  '$ Equivalent to "As String"

        Dim someLongLong^  '^ Equivalent to "As LongLong" in 64-bit VBA hosts
End Sub

Les indications de type réduisent considérablement la lisibilité du code et encouragent une notation hongroise héritée, ce qui entrave également la lisibilité:

Dim strFile$
Dim iFile%

Au lieu de cela, déclarez les variables plus proches de leur utilisation et nommez les choses pour ce qu'elles sont utilisées, et non après leur type:

Dim path As String
Dim handle As Integer

Les indications de type peuvent également être utilisées sur des littéraux pour appliquer un type spécifique. Par défaut, un littéral numérique inférieur à 32 768 sera interprété comme un littéral Integer , mais avec un indice de type, vous pouvez contrôler cela:

Dim foo 'implicit Variant
foo = 42& ' foo is now a Long
foo = 42# ' foo is now a Double
Debug.Print TypeName(42!) ' prints "Single"

Les indications de type ne sont généralement pas nécessaires sur les littéraux, car ils seraient affectés à une variable déclarée avec un type explicite ou convertis implicitement dans le type approprié lorsqu’ils sont transmis en tant que paramètres. Les conversions implicites peuvent être évitées en utilisant l'une des fonctions de conversion de type explicite:

'Calls procedure DoSomething and passes a literal 42 as a Long using a type hint
DoSomething 42&

'Calls procedure DoSomething and passes a literal 42 explicitly converted to a Long
DoSomething CLng(42)

Fonctions intégrées renvoyant des chaînes

La majorité des fonctions intégrées qui gèrent les chaînes sont disponibles en deux versions: une version faiblement typée qui renvoie un Variant et une version fortement typée (se terminant par $ ) qui renvoie une String . A moins que vous n'attribuez la valeur de retour à un Variant , vous devez préférer la version qui renvoie une String - sinon il y a une conversion implicite de la valeur de retour.

Debug.Print Left(foo, 2)  'Left returns a Variant
Debug.Print Left$(foo, 2) 'Left$ returns a String

Ces fonctions sont:

• VBA.Conversion.Error -> VBA.Conversion.Error $
• VBA.Conversion.Hex -> VBA.Conversion.Hex $
• VBA.Conversion.Oct -> VBA.Conversion.Oct $
• VBA.Conversion.Str -> VBA.Conversion.Str $
• VBA.FileSystem.CurDir -> VBA.FileSystem.CurDir $
• VBA. [_HiddenModule] .Input -> VBA. [_ HiddenModule] .Input $
• VBA. [_HiddenModule] .InputB -> VBA. [_ HiddenModule] .InputB $
• VBA.Interaction.Command -> VBA.Interaction.Command $
• VBA.Interaction.Environ -> VBA.Interaction.Environ $
• VBA.Strings.Chr -> VBA.Strings.Chr $
• VBA.Strings.ChrB -> VBA.Strings.ChrB $
• VBA.Strings.ChrW -> VBA.Strings.ChrW $
• VBA.Strings.Format -> VBA.Strings.Format $
• VBA.Strings.LCase -> VBA.Strings.LCase $
• VBA.Strings.Left -> VBA.Strings.Left $
• VBA.Strings.LeftB -> VBA.Strings.LeftB $
• VBA.Strings.LTtrim -> VBA.Strings.LTrim $
• VBA.Strings.Mid -> VBA.Strings.Mid $
• VBA.Strings.MidB -> VBA.Strings.MidB $
• VBA.Strings.Right -> VBA.Strings.Right $
• VBA.Strings.RightB -> VBA.Strings.RightB $
• VBA.Strings.RTrim -> VBA.Strings.RTrim $
• VBA.Strings.Space -> VBA.Strings.Space $
• VBA.Strings.Str -> VBA.Strings.Str $
• VBA.Strings.String -> VBA.Strings.String $
• VBA.Strings.Trim -> VBA.Strings.Trim $
• VBA.Strings.UCase -> VBA.Strings.UCase $

Notez que ce sont des alias de fonctions, pas des indications de type . La fonction Left correspond à la fonction B_Var_Left masquée, tandis que la version Left$ correspond à la fonction B_Str_Left masquée.

Dans les premières versions de VBA, le signe $ n'est pas un caractère autorisé et le nom de la fonction doit être placé entre crochets. Dans Word Basic, il y avait beaucoup plus de fonctions qui renvoyaient des chaînes se terminant par $.

Déclaration de chaînes de longueur fixe

En VBA, les chaînes peuvent être déclarées avec une longueur spécifique; ils sont automatiquement remplis ou tronqués pour conserver cette longueur comme déclaré.

Public Sub TwoTypesOfStrings()

    Dim FixedLengthString As String * 5 ' declares a string of 5 characters
    Dim NormalString As String

    Debug.Print FixedLengthString       ' Prints "     "
    Debug.Print NormalString            ' Prints ""

    FixedLengthString = "123"           ' FixedLengthString now equals "123  "
    NormalString = "456"                ' NormalString now equals "456"

    FixedLengthString = "123456"        ' FixedLengthString now equals "12345"
    NormalString = "456789"             ' NormalString now equals "456789"

End Sub

Quand utiliser une variable statique

Une variable statique déclarée localement n'est pas détruite et ne perd pas sa valeur lorsque vous quittez la procédure Sub. Les appels suivants à la procédure ne requièrent pas de réinitialisation ou d'affectation, bien que vous souhaitiez "mettre à zéro" une ou plusieurs valeurs mémorisées.

Celles-ci sont particulièrement utiles lors de la liaison tardive d'un objet dans un sous-ensemble appelé à plusieurs reprises.

Snippet 1: réutilise un objet Scripting.Dictionary sur plusieurs feuilles de calcul

Option Explicit

Sub main()
    Dim w As Long
    
    For w = 1 To Worksheets.Count
        processDictionary ws:=Worksheets(w)
    Next w
End Sub

Sub processDictionary(ws As Worksheet)
    Dim i As Long, rng As Range
    Static dict As Object
    
    If dict Is Nothing Then
        'initialize and set the dictionary object
        Set dict = CreateObject("Scripting.Dictionary")
        dict.CompareMode = vbTextCompare
    Else
        'remove all pre-existing dictionary entries
        ' this may or may not be desired if a single dictionary of entries
        ' from all worksheets is preferred
        dict.RemoveAll
    End If
    
    With ws
        
        'work with a fresh dictionary object for each worksheet
        ' without constructing/destructing a new object each time
        ' or do not clear the dictionary upon subsequent uses and 
        ' build a dictionary containing entries from all worksheets
    
    End With
End Sub

Extrait de code 2: créez un UDF de feuille de calcul qui lie l'objet VBScript.RegExp en retard

Option Explicit

Function numbersOnly(str As String, _
                     Optional delim As String = ", ")
    Dim n As Long, nums() As Variant
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once
    'this is beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    Else
        Set cmat = Nothing
    End If
    
    With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "[0-9]{1,999}"
        If .Test(str) Then
            Set cmat = .Execute(str)
            'resize the nums array to accept the matches
            ReDim nums(cmat.Count - 1)
            'populate the nums array with the matches
            For n = LBound(nums) To UBound(nums)
                nums(n) = cmat.Item(n)
            Next n
            'convert the nums array to a delimited string
            numbersOnly = Join(nums, delim)
        Else
            numbersOnly = vbNullString
        End If
    End With
End Function

^{Exemple de fichier UDF avec objet statique rempli par un demi-million de lignes}

* _{Temps écoulés pour remplir des lignes 500K avec UDF:}

_{- avec Dim rgx As Object : 148,74 secondes}

^{- avec rgx statique comme objet : 26,07 secondes}

* _{Ceux-ci doivent être pris en compte pour la comparaison relative uniquement. Vos propres résultats varieront selon la complexité et
étendue des opérations effectuées.}

N'oubliez pas qu'un fichier UDF n'est pas calculé une fois dans la durée de vie d'un classeur. Même un UDF non volatile sera recalculé chaque fois que les valeurs comprises dans la ou les plages auxquelles il fait référence sont susceptibles d'être modifiées. Chaque événement de recalcul ultérieur augmente uniquement les avantages d'une variable déclarée de manière statique.

• Une variable statique est disponible pour la durée de vie du module, et non pour la procédure ou la fonction dans laquelle elle a été déclarée et affectée.
• Les variables statiques ne peuvent être déclarées que localement.
• La variable statique contient un grand nombre des mêmes propriétés d'une variable de niveau de module privé, mais avec une portée plus restreinte.

_{Référence associée: Statique (Visual Basic)}