Les plages nommées avec Excel en VBA

Depuis Excel, les plages nommées se gèrent avec le groupe "Noms définis" du ruban "Formules".

'

Pour le fil rouge de ce billet, nous démarrons en ajoutant trois feuilles à notre classeur : "Paramètres", "Lundi" et "Mardi". Leur contenu sera rempli au fur et à mesure.

Définition d’un nom

Il y a plusieurs façons de définir un nom. Ici, nous optons pour le choix "Définir un nom" du menu contextuel ou depuis le groupe "Noms définis" :

Une fenêtre apparaît. Nous modifions le nom et le commentaire comme montré ci-dessous. La zone et la référence nous conviennent pour ce premier exemple.

Après avoir validé, nous voyons que notre cellule C3 a désormais le nom créé.

Gestionnaire de noms

Le gestionnaire de noms liste les noms définis pour les plages standards, mais aussi pour d’autres éléments tels que les listes de données ou encore les zones d’impression.

Nous ouvrons le gestionnaire des noms via le bouton approprié du ruban. La fenêtre se présente ainsi :

Nous pouvons filtrer (avec la zone liste à droite) et trier (avec les entêtes de colonne) ces noms. De plus, nous pouvons également ajouter, modifier ou supprimer des noms définis.

Jusqu’à présent, nous n’avons créé qu’un seul nom qui a l’étendue classeur. Nous allons en ajouter trois avec le bouton "Nouveau…" pour la feuille "Lundi" : "QTE_PRODUITE", "QTE_REBUT" et "TAUX_REBUT".

Nos plages nommées créés, nous en profitons pour définir une formule. Remarquons que celle-ci est ainsi plus aisée à écrire et à lire :

Notons encore une fois comment la "Zone Nom" (encadrée en orange ci-dessus) nous permet de naviguer rapidement dans les plages nommées de la feuille courante et du classeur.

Passons maintenant aux autres jours de la semaine.

Nous pourrions, ça nous ferait de l’entraînement, mais il y a plus rapide ! Nous pouvons simplement copier-coller notre tableau d’une feuille à l’autre, en l’occurrence de "Lundi" à "Mardi". Nous vérifions, et il faut tout de même redéfinir le nom pour le taux de rebut en Mardi!C5. Cela fait, nous modifions nos valeurs :

Pour les derniers jours, nous allons faire encore mieux en utilisant la fonctionnalité "Déplacer ou copier…" puis en renommant les feuilles collées. Cette fois-ci, tous les noms sont bien reportés.

Le gestionnaire de noms contient bien tous nos noms définis selon l’étendue souhaitée.

Au cours de cette section, nous avons vu comment gérer les noms définis du classeur avec Excel.

Il est temps de voir un peu de syntaxe VBA concernant les plages nommées.

Lecture d’une plage nommée

L’accès à une plage nommée peut se faire avec son nom au lieu de son adresse :

Sheets("Paramètres").Range("TAUX_MAX_REBUT_TOLERE") ' 0.03
' équivalent de Sheets("Paramètres").Range("C3")

La collection Names

Les noms définis sont stockés dans la collection Names de la feuille, du classeur ou encore de l’application.

Ajouter un nom

Pour rajouter un nom, il faut faire appel à la méthode Add de celle-ci en indiquant au moins un nom et une adresse.

Private Sub Exemple_Ajout_Names()
    Sheets("Paramètres").Range("B4") = "Quantité min. à produire"
    Sheets("Paramètres").Range("C4") = 1000
    
    ' Ajout étendue classeur
    ActiveWorkbook.Names.Add Name:="QTE_MIN_PRODUCTION", RefersTo:="=Paramètres!$C$4"
     
    ' Ajout étendue feuille paramètres
    ActiveWorkbook.Sheets("Paramètres").Names.Add Name:="QTE_MIN_PRODUCTION", RefersTo:="=Paramètres!$C$4"
End Sub

Elle accepte plusieurs types de coordonnées possibles comme indiqué dans la documentation. Si nous souhaitons ajouter un commentaire, il faut le faire ensuite en modifiant la propriété adéquate.

Parcourir les noms

Comme Names est une collection, nous la parcourons comme attendu :

Private Sub Exemple_Iteration_Names()
    Dim vName As Variant
    For Each vName In ActiveWorkbook.Names
        Debug.Print vName.Name, vName.Comment
    Next vName
End Sub

L’objet Name

L’objet Name correspond à un nom défini.

Modification

Il possède plusieurs propriétés, que ce soit pour lire/écrire le commentaire (Comment) ou encore la référence (RefersTo / RefersToR1C1 / …).

Private Sub Exemple_Ajout_Modification_Name()
    ' Nom défini étendue classeur
    ActiveWorkbook.Sheets("Paramètres").Names("QTE_MIN_PRODUCTION").Comment = "Feuille"
End Sub

Suppression

Un nom peut être supprimé avec son unique méthode Delete.

Private Sub Exemple_Suppression_Name()
    ' Nom défini étendue feuille
    ActiveWorkbook.Sheets("Paramètres").Names("QTE_MIN_PRODUCTION").Delete
End Sub

Durant cette section, nous avons vu comment se traduisaient les plages nommées en VBA.

Voici un exemple final afin de montrer la flexibilité apportée par les plages nommées dans les macros.

Dans celui-ci, nous nous basons sur le classeur construit jusqu’à présent pour déterminer les jours où le taux de rebut a été supérieur au toléré ou bien la production n’a pas atteint son objectif (hors vendredi que nous considérons comme non travaillé en entier pour cette seconde condition).

Option Explicit

' Feuille des paramètres
Private Const FEUILLE_PARAMS_NOM As String = "Paramètres"
Private Const PLAGE_TAUX_MAX_REBUT_TOLERE As String = "TAUX_MAX_REBUT_TOLERE"
Private Const PLAGE_QTE_MIN_PRODUCTION As String = "QTE_MIN_PRODUCTION"

' Autres feuilles
Private Const PLAGE_QTE_PRODUITE As String = "QTE_PRODUITE"
Private Const PLAGE_TAUX_REBUT As String = "TAUX_REBUT"

Private Const JOUR_VENDREDI As String = "Vendredi"
Private Function JOURS()
    JOURS = Array("Lundi", "Mardi", "Mercredi", "Jeudi", JOUR_VENDREDI)
End Function

Private Sub Main()
    Application.ScreenUpdating = False
    
    Dim dTauxMaxRebutTolere As Double
    Dim dQteMinProductionTotere As Double
    Dim vJour As Variant
    
    dTauxMaxRebutTolere = Sheets(FEUILLE_PARAMS_NOM).Range(PLAGE_TAUX_MAX_REBUT_TOLERE)
    dQteMinProductionTotere = Sheets(FEUILLE_PARAMS_NOM).Range(PLAGE_QTE_MIN_PRODUCTION)
    For Each vJour In JOURS()
        With Sheets(vJour)
            If .Range(PLAGE_TAUX_REBUT) > dTauxMaxRebutTolere Or _
                .Name <> JOUR_VENDREDI And .Range(PLAGE_QTE_PRODUITE) < dQteMinProductionTotere Then
                    Debug.Print (vJour)
                    Beep
                    ' Lundi
                    ' Mercredi
            End If
        End With
    Next vJour
    
    Application.ScreenUpdating = True
End Sub