Comment obtenir les cellules à l’intersection de deux plages en VBA sur Excel

Au cours des différents tutoriels ou cours présents sur le blog, nous avons déjà eu l’occasion d’utiliser à de nombreuses reprises l’instruction Intersect de VBA. Celle-ci permet de récupérer l’éventuelle plage de cellulese qui se trouve à l’intersection d’au moins deux plages de cellules données. Aujourd’hui, je vous propose de revenir sur cette méthode, dont la maîtrise devient rapidement indispensable, surtout lorsque nous commençons à manipuler des évènements de VBA.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation de la fonction Intersect()

La fonction Intersect est une méthode de l’objet principal de VBA, l’objet Application.

Celle-ci permet, comme nous venons de le voir dans l’introduction, de récupérer une plage d’une ou plusieurs cellules qui se trouve à l’intersection des plages passées en argument.

En d’autres termes, avec Intersect, nous allons récupérer un objet Range dans lequel nous retrouverons toutes les cellules communes aux objets Range donnés.

Pour illustrer l’utilisation que nous pouvons avoir d’Intersect, nous allons utiliser un tableau reprenant les ventes réalisées par une entreprise au cours des 12 mois de l’année 2020, réparties par région :

Ensuite, nous allons souhaiter extraire les ventes réalisées au cours d’un mois donné, pour un pays donné.

Par exemple, les ventes réalisées en Allemagne, durant le mois d’octobre 2020.

Pour cela, la première chose à faire va consister à relever les coordonnées des plages de cellules correspondantes :

• Les ventes réalisées en Allemagne se trouvent saisies aux coordonnées C9 à C21,

• Les ventes réalisées au cours du mois d’octobre sont quant à elles saisies au sein des cellules situées aux coordonnées B18 à D18 :

Pour connaître la cellule dans laquelle se trouve les ventes correspondantes (soit la cellule C18), nous allons donc pouvoir créer une macro-commande en VBA.

Pour cela, il suffit de lancer Visual Basic Editor qui est l’outil intégré dans les applications de la suite Office et qui permet de créer des macro-commandes en utilisant le langage de programmation VBA.

Pour lancer cette application, nous pouvons par exemple utilisant le raccourci clavier [Alt]+[F11] :

Une fois dans VBE nous allons pouvoir insérer la macro-commande à deux endroits différents :

• Soit dans le module de feuille spécifique à la feuille de calcul actuelle,
• Soit dans un module dédié

D’une manière générale, nous préférerons utiliser les modules de feuilles pour utiliser les évènements de feuille (comme nous le verrons un peu plus tard), et saisir les autres commandes dans un module classique, ce qui permettra notamment de pouvoir les appeler depuis n’importe quelle feuille de calcul du classeur et également de pouvoir l’exporter dans un autre classeur simplement.

Nous allons donc utiliser cette seconde option, et pour cela, nous nous rendons dans le menu Insertion > Module :

VBE nous insère alors un nouveau module (Module1) dans lequel nous allons pouvoir saisir notre macro :

Nous créons ainsi la macro en utilisant le mot-clé « Sub », suivi du nom de la macro (par exemple « ventesAllemagneOctobre ») :

Il suffit ensuite de valide la création de la procédure en appuyant sur la touche [Entrée] pour que VBE ajoute automatiquement la ligne « End Sub », ce qui signifie qu’à chaque fois que nous lancerons la macro, toutes les instructions situées entre ces deux lignes seront exécutées.

Pour afficher le montant des ventes, nous insérons donc la ligne suivante :

Sub ventesAllemagneOctobre()
    MsgBox Application.Intersect([c9:c21],  [b18:d18])
End Sub

Pour tester la macro, il suffit ensuite d’appuyer sur la touche [F5] du clavier :

Note : L’instruction Intersect est implicitement native de l’objet Application. Il n’est donc pas utile d’appeler celui-ci :

Sub ventesAllemagneOctobre()
    MsgBox Intersect([c9:c21],  [b18:d18])
End Sub

Pour simplifier la saisie de cette instruction, il est possible d’utiliser les noms de plages de cellules automatiques, ce qui nous permettra de les appeler directement par les en-têtes de lignes et de colonnes.

Pour cela, de retour sur la feuille de calcul nous allons procéder de la manière suivante :

• Tout d’abord, nous sélectionnons toutes les cellules du tableau :

• Puis nous nous rendons dans le menu Formules du ruban, afin de cliquer sur le bouton Depuis sélection dans le groupe Noms définis :

• Excel nous affiche alors une fenêtre afin de spécifier à partir de quels éléments nous souhaitons créer les noms de plages (nous choisissons alors d’utiliser la ligne du haut et la colonne de gauche) :

Si en apparence nous ne voyons aucune modification, il suffit de dérouler le menu de nom de cellules (situé à gauche de la barre de saisie des formules) pour y retrouver tous les noms créés automatiquement par Excel :

De cette manière nous allons pouvoir modifier l’instruction en appelant les plages directement avec les noms de plage :

Sub ventesAllemagneOctobre()
    MsgBox Intersect([Allemagne], [Octobre])
End Sub

Le résultat sera alors strictement identique, mais l’instruction est évidemment plus claire à saisir et à lire :

2. Colorer les cellules situées à l’intersection

Maintenant imaginons que nous souhaitions colorer la couleur de fond des cellules dans lesquelles se trouvent les ventes réalisées au cours du premier trimestre pour la France.

Pour cela, nous appelons l’instruction Union qui permet de fusionner les plages passées en argument afin de créer une plage unifiée avec les trois mois Janvier/Février/Mars.

Puis après avoir utilisé Intersect pour réduire cette plage aux seules réalisées en France, nous allons en modifier la couleur de fond en utilisant la sous-propriété ColorIndex d’Interior qui est elle-même une propriété de l’objet Range généré par l’intruction Intersect :

Sub colorerFranceT1()
    Intersect(Union([Janvier],  [Février], [Mars]), [France]).Interior.ColorIndex = 5
End Sub

Ce qui permet de modifier les couleurs de fond des cellules concernées :

3. Sommer les valeurs situées à l’intersection

En partant de la même méthodologie, nous pouvons également imaginer réaliser une somme des cellules situées à l’intersection de deux plages.

Pour cela, il va nous suffire d’utiliser une boucle For que nous avons déjà eu l’occasion de découvrir dans le cadre de la formation dédiée à l’apprentissage du VBA (cliquez-ici pour en savoir davantage).

Nous commençons ici par créer deux variables :

• Une variable r qui va permettre de passer en revue toutes les cellules contenues à l’intersection des plages étudiées,
• Une seconde variable total qui va permettre quant à elle de sommer le résultat des cellules

Sub totalFranceT1()
    Dim r As Range
    Dim total As Long
End Sub

Ensuite, nous allons pouvoir mettre en place une boucle pour passer en revue toutes les cellules qui nous intéressent :

For Each r In Intersect(Union([Janvier],  [Février], [Mars]), [France])
    Next

Puis nous réalisons la somme des cellules :

For Each r In Intersect(Union([Janvier],  [Février], [Mars]), [France])
        total = total + r
    Next

Et enfin nous pouvons afficher un message à l’utilisateur :

    MsgBox total 

4. Intersect et la gestion des évènements

Comme nous l’avons déjà vu dans le chapitre dédié à la découverte des évènements de la formation dédiée à l’apprentissage de VBA, un évènement permet déclencher une série d’instructions lorsque que quelque chose se passe sur le classeur ou la feuille de calcul.

Pour l’exemple, nous allons par exemple chercher à savoir si la cellule sélectionnée appartient au tableau de données.

Pour cela, revenons dans le projet VBA, puis nous allons entrer dans le module de la feuille de calcul en double-cliquant sur la feuille en question depuis l’explorateur de projet :

Pour ajouter automatiquement l’évènement, il suffit de sélectionner Worksheet dans le menu de sélection des objets :

Excel ajoute alors une procédure évènementielle qui sera lancée automatiquement à chaque fois qu’une cellule va être sélectionnée :

Comme vous pouvez le voir, la ou les cellules sélectionnées vont être retournées avec la variable Target.

Il suffit alors de vérifier si celle-ci appartient à la plage des cellules dans laquelle se trouve le tableau.

Et pour cela nous allons utiliser une particularité de Intersect : si aucune cellule ne se trouve à l’intersection des cellules passées en argument, Intersect retourne comme valeur Nothing :

Private Sub Worksheet_SelectionChange(ByVal Target  As Range)   If Intersect(Target, [ventes2020])  Is Nothing Then
        MsgBox "La cellule sélectionnée ne fait pas  partie du tableau"
    Else
        MsgBox "La cellule sélectionnée fait partie du  tableau"
    End If
    
End Sub

Enfin, si nous souhaitons uniquement à savoir si la cellule appartient au tableau, nous allons pouvoir réduire le test en utilisant le mot-clé Not :

    If Not Intersect(Target,  [ventes2020]) Is Nothing Then
        MsgBox "La cellule sélectionnée fait partie du  tableau"
    End If

De cette manière, rien ne sera affiché si la cellule sélectionnée se trouve en dehors du tableau.

Dans le cas contraire, Excel va afficher une notification pour prévenir l’utilisateur que la cellule appartient bien au tableau :