Identifier les cellules spéciales en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Repérer les cellules spéciales

VBA Excel offre une méthode très efficace pour repérer facilement les cellules spéciales d'une feuille ou d'un tableau. Les cellules dites spéciales sont par exemple les cellules vides, celles dotées d'un commentaire ou encore celles portant des formules. Et dans ce nouveau volet, nous proposons de découvrir cette méthode.

Classeur Excel à télécharger
Pour les démonstrations, nous suggérons d'appuyer l'étude sur un tableau hébergeant de nombreuses données et accueillant ces cellules aux caractéristiques différentes.

• Télécharger le classeur cellules-speciales.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Cliquer sur les boutons Activer la modification et Activer le contenu des bandeaux de sécurité,

Vous découvrez un tableau qui s'apparente à une base de données hébergeant des activités de sorties par départements.



Certaines cellules sont vides et d'autres abritent effectivement des commentaires ou plutôt des notes comme on les appelle depuis la version 2019.

Sur la droite de ce tableau, vous notez la présence de quelques boutons dont la vocation est de repérer en couleur ces cellules spécifiques. Ce sont eux que nous allons devoir coder. Ils sont déjà associés à des procédures événementielles mais vides de code à ce stade, fort naturellement.

Repérer les cellules vides
Nous l'avons évoqué, c'est une seule et même méthode appliquée à un objet de type Range (Cells, Range, Selection) qui permet de déceler toutes ces cellules particulières. C'est l'argument variable qui lui est passé qui modifie son mode de détection. Cette méthode se nomme SpecialCells.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Si vous ne visualisez aucune des procédures, double cliquer sur l'élément Module1,

Il se trouve sur la gauche de l'écran, dans l'explorateur de projet. Il représente un module indépendant de code.

• Dès lors, cliquer entre les bornes de la procédure nommée vides,

Elle est associée au premier bouton de la feuille. Son rôle est de repérer les cellules vides.

• Ajouter alors la ligne de code VBA suivante :

[bdd].Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(244, 176, 132)

Nous sommes tout d'abord obligés de descendre dans la hiérarchie des objets. Comme nous codons depuis un module indépendant, nous désignons tout d'abord le tableau visé grâce à son nom de plage(bdd). Nous n'en avons pas parlé jusqu'alors mais c'est ainsi qu'il est nommé. Vous pouvez le constater en déployant la zone nom depuis la feuille Excel. En effet, nous bornons le champ de détection. Si nous avions désigné la feuille complète avec l'objet ActiveSheet, ce sont les innombrables cellules vides en dehors de la zone qui seraient détectées. Cette action n'aurait aucun sens.

C'est alors que nous atteignons l'objet enfant Cells. Sans argument, il désigne les cellules de la feuille au sens large. Il permet ainsi d'appeler la méthode SpecialCells. Notez son orthographe au pluriel prouvant a priori son intention de considérer toutes les cellules concernées dans un même traitement. Avec le paramètre xlCellTypeBlanks, nous indiquons que les cellules concernées sont les cellules vides. Dès lors, grâce à la propriété Interior nous désignons le remplissage de ces cellules. Et avec la propriété enfant Color, nous agissons sur la couleur de fond. C'est alors la fonction VBA RGB qui permet de définir cette couleur, ici en l'occurrence avec ces composantes, sur un orange clair.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Puis, cliquer sur le premier bouton de la feuille, intitulé Cellules vides,

Comme vous pouvez le voir, toutes les cellules vides sont effectivement repérées dans un même traitement et ce, avec une seule ligne de code s'il vous plaît.

Avant de poursuivre la détection des cellules spéciales, nous proposons de rendre les attributs d'origine aux cellules altérées. C'est le but du bouton intitulé Annuler vides et situé juste en-dessous du premier.

• Revenir dans l'éditeur VBA Excel (ALT + Tab),
• Cliquer entre les bornes de la procédure annulerVides,
• Puis, ajouter l'instruction VBA suivante :

[bdd].Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = Range("C4").Interior.Color

Plutôt que de chercher ces attributs de format, nous nous simplifions la vie. Nous prélevons ceux d'une cellule normale du tableau (arbritrairement C4) pour les appliquer à toutes les cellules vides.

Si vous revenez sur la feuille et que vous cliquez sur le deuxième bouton, vous constatez que toutes les cellules vides recouvrent effectivement leur aspect d'origine.

Repérer les cellules des commentaires
Maintenant et grâce au troisième bouton, nous proposons de repérer en couleur toutes les cellules portant des notes. Le procédé est identique à deux détails près. Cette fois, nous allons agir sur l'intégralité de la feuille, grâce à l'objet ActiveSheet. Il désigne la feuille active au moment de l'exécution du code. De plus et fort logiquement, le paramètre de la méthode SpecialCells doit changer.

• Dans les bornes de la procédure comment, ajouter l'instruction VBA suivante :

Sub comment()
ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = RGB(244, 176, 132)
End Sub

Les valeurs de ces paramètres sont finalement assez logiques. Seul le suffixe change pour désigner le type cherché. Et c'est donc la valeur xlCellTypeComments qui permet de pointer sur les cellules avec des notes.

• Dans l'enchaînement, implémenter la procédure annulerComment comme suit :

Sub annulerComment()
ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = Range("C4").Interior.Color
End Sub

Le principe est le même que précédemment pour rétablir les attributs d'origine des cellules de commentaires.



Si vous revenez sur la feuille et que vous cliquez sur le troisième bouton, toutes les cellules des commentaires sont aussitôt repérées. Si vous cliquez sur le quatrième bouton, toutes les distinctions visuelles disparaissent.

Repérer les cellules des formules
Nous allons maintenant nous soucier des sixième et septième boutons. Ils concernant les formules. Nous reviendrons sur le cinquième ensuite. Vous l'avez sans doute anticipé, c'est cette fois le paramètre xlCellTypeFormulas que nous devons passer à la méthode SpecialCells.

• Implémenter les deux procédures Formules et annulerFormules comme suit :

Sub formules()
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(244, 176, 132)
End Sub

Sub annulerFormules()
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Interior.Color = Range("C4").Interior.Color
End Sub

Maintenant, si vous cliquez sur le sixième bouton, vous illuminez les cellules des formules. Il n'y en a que deux : Une en haut à gauche du tableau et une sur la droite en-dessous des boutons.



Et si vous cliquez sur le septième bouton, tous ces attributs sont réinitialisés.

La dernière cellule de la feuille
Enfin, nous proposons de démontrer une méthode fort utile dans bien des cas en développement. Elle consiste à atteindre la dernière cellule utile de la feuille. Lorsque ses coordonnées sont connues automatiquement, on les exploite généralement dans des boucles pour parcourir l'ensemble des informations d'un tableau ou d'une feuille. Et c'est encore une fois la méthode SpecialCells qui autorise cette prouesse.

• Dans les bornes de la procédure derniere, ajouter l'instruction VBA suivante :

Sub derniere()
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
End Sub

C'est donc cette fois la valeur xlCellTypeLastCell qui permet de désigner la dernière cellule utile de la feuille. Et dans l'enchaînement, nous appliquons la méthode enfant Select pour la sélectionner.



Si vous cliquez sur le bouton intitulé Dernière cellule, étonnamment vous remarquez que ce n'est pas la dernière cellule du tableau qui est sélectionnée, mais celle située sur sa droite. En effet, si vous remontez tout en haut de la feuille, vous constatez que la colonne G porte un titre ainsi qu'une formule un peu plus bas. La méthode SpecialCells décèle le dernier indice de ligne ainsi que le dernier indice de colonne et repère la cellule située au croisement.

Mais si vous inscrivez une information un peu plus loin, par exemple en H1001 et que vous cliquez de nouveau sur le bouton, vous constatez cette fois que c'est bien cette cellule qui est repérée et sélectionnée sans ambiguïté.

Remarque : Nous avons employé le terme de méthode pour parler de la propriété SpecialCells. Il s'agit d'un abus de langage volontaire dans la mesure où cette propriété réalise des actions en désignant des cellules spéciales.