Extractions dynamiques aux intersections 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 :


Extraire aux intersections des lignes et des colonnes

Avec cette nouvelle astuce VBA Excel, nous allons développer un code capable d'extraire en un seul jet, toutes les informations situées aux intersections de multiples lignes et colonnes.



Sur l'exemple illustré par la capture, nous travaillons à partir d'un tableau dans lequel les cellules sont simplement renseignées avec leurs indices de ligne et de colonne. Sur la droite et dans deux cellules précises, l'utilisateur indique les lignes et les colonnes, par leurs numéros séparés par des espaces, à partir desquelles il s'agit de produire les multiples extractions des informations situées à toutes les intersections.

Classeur Excel à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur un classeur offrant ce tableau et cette petite console.

• Télécharger le classeur extraire-aux-intersections.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• Dans le menu contextuel, choisir l'option Propriétés,
• En bas de la boîte de dialogue, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous découvrons le tableau rempli des indices de lignes et de colonnes. Sur la droite, en cellules respectives G6 et H6, l'utilisateur doit énumérer les indices de lignes et de colonnes à recouper, en les séparant avec des espaces. Les contenus des cellules placées aux intersections doivent être extraits dans les deux colonnes du dessous, à partir de la cellule G8.

Extraire au changement de valeur
Le code VBA que nous devons construire doit se déclencher automatiquement, dès qu'une valeur est modifiée et validée en cellule G6 ou en cellule H6. Pour cela et nous le savons, c'est une procédure événementielle spéciale qui doit être implémentée. Nous avons anticipé car elle existe déjà et nous proposons de le constater.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,

Nous y découvrons effectivement la présence de la procédure Worksheet_Change.

...
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
...

Son code se déclenchera au changement de valeur dans n'importe quelle cellule de la feuille Excel. Pour économiser les ressources, nous allons devoir limiter son champ d'action aux cellules G6 et H6. Le paramètre Target est en attente. Il représente la cellule modifiée au moment du déclenchement du code VBA. Nous l'exploiterons bien entendu pour vérifier les coordonnées de la cellule changée.

Les variables
Nous avons tout d'abord besoin de variables pour récolter les indices respectifs dans des matrices à construire. Nous exploiterons ensuite ces matrices dans une formule matricielle engageant la fonction d'extraction Index.

• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

...
Dim lesLignes As String: Dim lesColonnes As String
Dim formule As String
...

Nous construirons la matrice verticale des indices de lignes dans la variable lesLignes. Nous construirons la matrice horizontale des indices de colonnes dans la variable lesColonnes. Puis, nous stockerons la syntaxe de la formule d'extraction, exploitant ces deux matrices, dans la variable formule.

Contrôler la modification
Ensuite et comme nous l'avons annoncé pour économiser les ressources, nous devons contrôler les coordonnées de la cellule modifiée. Ainsi, c'est seulement lorsqu'il s'agira de la cellule G6 ou de la cellule H6 que nous déciderons de déclencher le traitement VBA.

• Après les variables, créer l'instruction conditionnelle suivante :

...
If Target.Address = "$G$6" Or Target.Address = "$H$6" Then

End If
...

Grâce à la propriété Address de l'objet Target passé en paramètre de la procédure événementielle, nous vérifions les coordonnées de la cellule modifiée par l'utilisateur. S'il s'agit bien (If) de la cellule G6 ou (Or) de la cellule H6, nous décidons de poursuivre le code VBA.

Les matrices d'indices
Il est temps de créer la matrice verticale des indices de lignes ainsi que la matrice horizontale des indices de colonnes. Comme nous l'avons appris avec les calculs matriciels, dans une matrice verticale, les numéros doivent être séparés par des points-virgules, tandis que dans une matrice horizontale, c'est le point qui s'impose.

• Dans les bornes de l'instruction conditionnelle, créer les deux matrices suivantes :

...
lesLignes = "{" & Replace([G6].Value, " ", ";") & "}"
lesColonnes = "{" & Replace([H6].Value, " ", ",") & "}"
...

Pour la matrice verticale, nous récupérons le contenu de la cellule G6. Puis, nous remplaçons (Fonction VBA Replace) tous les espaces par des points-virgules. Pour la matrice horizontale et aussi étonnant que cela puisse paraître, nous remplaçons les espaces de la cellule H6 par une virgule et non par un point. En effet, la virgule est le délimiteur par défaut en français et le point en américain. Comme le VBA est un langage américain, il va automatiquement transformer ces virgules en points pour donner vie à cette matrice horizontale des indices de colonnes.

La formule d'extraction
Puisque les matrices sont maintenant consolidées, nous allons pouvoir les exploiter dans l'argument de l'indice de ligne et de l'indice de colonne de la fonction d'extraction nommée Index. Dans ce raisonnement matriciel, ce sont toutes les informations situées aux croisements des lignes et des colonnes énumérées qui seront extraites.

• Toujours dans l'instruction conditionnelle et à la suite, ajouter les lignes VBA suivantes :

...
formule = "=INDEX(tab," & lesLignes & "," & lesColonnes & ")"
Range("G8:H16").FormulaArray = formule
Range("G8:H16").Value = Range("G8:H16").Value
[A1].Select
...

Dans la variable formule, nous construisons la formule d'extraction sur la base de la fonction Index. Nous la faisons agir sur l'intégralité des données, dont la plage est reconnue sous le nom tab. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Nous lui passons également les deux matrices pour les indices de lignes et de colonnes dans ses deux arguments suivants et respectifs. Grâce à la propriété FormulaArray, nous répliquons ce raisonnement matriciel sur toute la plage d'extraction (G8:H16). Puis, nous écrasons les formules pour ne garder que les valeurs et enfin, nous sélectionnons la cellule A1 pour remonter l'affichage en haut de la feuille.

Extraire aux croisements
Même si tout ne sera pas parfait et nous allons le constater, nous pouvons déjà tester les facultés de ce code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Double cliquer sur la cellule G6 pour activer sa saisie,
• Remplacer le dernier indice (9) par l'indice de ligne 11,
• Dès lors, valider la modification par la touche Entrée du clavier,

Comme vous pouvez l'apprécier et grâce à cette technique matricielle enclenchée par le code VBA, toutes les données situées aux intersections des lignes et des colonnes des deux matrices construites, sont extraites les unes en dessous des autres. En revanche, lorsque plus aucune information ne concorde, ce sont des messages d'erreur qui viennent polluer les cellules qui suivent : #N/A pour Not Availiable, soit Non Disponible.



Corriger les erreurs d'extraction
Pour corriger ces anomalies après les extractions, puisque les résultats de la formule matricielle ont été transformés en valeurs, l'astuce consiste à utiliser une fonction pour remplacer tous ces défauts par du vide ("").

• Revenir dans l'éditeur VBA Excel,
• A la fin de l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
Range("G8:H16").Replace "#N/A", ""
...

Sur la plage d'extraction (G8:H16), nous exploitons la fonction VBA Replace pour remplacer toutes les erreurs (#N/A) par du vide (""), soit pour les annihiler.

• Enregistrer les modifications et revenir sur la feuille Excel,
• Double cliquer sur la cellule H6 pour activer sa saisie,
• Remplacer le dernier numéro de colonne (4) par l'indice 3 puis valider par la touche Entrée,

Comme vous pouvez le constater, toutes les extractions sont parfaitement produites aux intersections et plus aucune erreur ne subsiste.