Fonction VBA Excel pour extraire toutes les données

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


Sujets que vous pourriez aussi aimer :


Fonction VBA pour extraction matricielle

A l'occasion du volet précédent, nous avons découvert la technique pour créer des fonctions Excel en VBA, capables de retourner des plages de données. Ici, nous proposons d'affiner la méthode pour créer une fonction capable d'extraire toutes les informations d'un tableau, correspondant à un critère défini par l'utilisateur.



Sur l'exemple illustré par la capture, ce sont toutes les activités du département choisi à l'aide d'une liste déroulante qui sont extraites. La base de données source est située dans une feuille annexe. Et pour cela, c'est une unique fonction VBA Excel qui est exploitée et avec laquelle il n'est pas nécessaire de reproduire la logique avec la poignée. Elle se répand toute seule, comme le ferait un calcul matriciel.

Classeur Excel à télécharger
Pour développer cette précieuse nouvelle fonction Excel, nous suggérons d'appuyer les travaux sur un classeur offrant cette base de données.

• Télécharger le classeur fonction-recherche-matricielle.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la deuxième feuille de ce classeur. Elle est nommée Extraire. Une liste déroulante permet de choisir un département en cellule D3. L'extraction des activités correspondantes doit débuter à partir de la ligne 6. Ces activités sont recensées pêlemêle dans un tableau de la première feuille qui est nommée Id2Sorties. Ce tableau est reconnu sous le nom Bdd. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

Création de la fonction d'extraction
La fonction que nous devons créer doit attendre deux paramètres. Il s'agit premièrement du tableau à analyser (Bdd ici) et deuxièmement du critère à trouver dans ce dernier (Le département ici).

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, déployer le menu Insertion,
• Dans les propositions, choisir l'option Module,

C'est ainsi que nous créons un nouveau module et que sa feuille de code, vierge pour l'instant, apparaît au centre de l'écran.

• Dans cette feuille de code, créer la fonction rechercheVba, comme suit :

Function rechercheVba(Tableau As Range, Critere As String) As Variant

End Function

Le tableau à analyser est forcément typé comme un Objet Range, représentant une plage de cellules. Le critère est typé comme un String, soit comme un texte.

Les variables
Maintenant, nous avons besoin d'un objet pour prendre possession de ce tableau passé en paramètre et d'un autre pour représenter un tableau de variables dans lequel nous insèrerons toutes les données concordantes, à restituer comme une plage de cellules sur la feuille Extraire. Mais ce n'est pas tout. Nous avons aussi besoin de variables pour manipuler les lignes et les colonnes mais aussi pour connaître la feuille sur laquelle la fonction est enclenchée. En effet, nous codons dans un module indépendant de toute feuille de ce classeur.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim leTableau As Range: Dim extract As Variant
Dim premLigne As Integer: Dim premColonne As Byte
Dim nbCol As Byte: Dim i As Integer: Dim j As Byte
Dim feuille As Worksheet

Set leTableau = Tableau: i = 0
Set feuille = leTableau.Worksheet
premLigne = leTableau.Row: premColonne = leTableau.Column
nbCol = leTableau.Columns.Count
...

Nous exploitons notre objet leTableau en l'initialisant (Set) sur la plage de cellules transmise en premier paramètre de la fonction. Dès lors, il hérite des objets, propriétés et méthodes d'un objet de type Range usuel. Grâce à son objet Worksheet, nous initialisons (Set) l'objet feuille de manière à ce qu'il pointe sur la feuille du tableau passé en premier paramètre. Grâce à ses propriétés Row et Column, nous stockons l'indice de la première ligne et de la première colonne de ce tableau dans les variables premLigne et premColonne. Grâce à la propriété Count de sa collection Columns, nous stockons le nombre de colonnes dans la variable nbCol. L'idée est que la réponse de notre fonction soit capable de s'adapter à tout tableau pour fournir toutes les données en largeur comme en hauteur, bien qu'elles soient variables. La variable i sera utilisée dans deux contextes. Elle doit connaître le nombre de lignes concordant avec le critère. Nous allons très vite le comprendre. Puis, nous l'exploiterons dans un deuxième temps pour pointer sur les rangées du tableau de variables à implémenter.

Dimensionner le tableau de variables
A ce stade, il n'y a que le tableau de variables (extract) que nous n'avons pas encore initialisé, et pour cause ! Pour que la fonction VBA réponde sans erreur, nous devons le dimensionner très précisément sur le nombre de lignes qui doivent le remplir. Pour connaître ce nombre de lignes, nous devons engager une première boucle parcourant toutes les cellules de la première colonne du tableau passé en paramètre. Dès que le critère est trouvé, nous devons incrémenter la variable i. C'est elle que nous utiliserons alors pour le dimensionnement en ligne.

• A la suite du code, ajouter les instructions VBA suivantes :

...
Do While Sheets(feuille.Name).Cells(premLigne, premColonne).Value <> ""
If (Sheets(feuille.Name).Cells(premLigne, premColonne).Value = Critere) Then
i = i + 1
End If
premLigne = premLigne + 1
Loop

ReDim extract(1 To i, 1 To nbCol)
premLigne = leTableau.Row: i = 1
...

Nous engageons une classique boucle Do consistant à poursuivre son analyse tant qu'une cellule non vide (<>"") est rencontrée. Pour pointer sur la feuille du tableau à analyser, nous exploitons notre objet feuille avec sa propriété Name dans l'objet Sheets. Ensuite, nous descendons jusqu'à la collection Cells de cette feuille, pour déterminer la ligne en cours et la colonne figée sur la première rangée du tableau, telle que nous l'avons initialisée. En effet, au gré de l'analyse, la ligne progresse quant à elle (premLigne = premLigne + 1). Sur la première colonne de cette feuille et pour la ligne en cours, si le critère est trouvé, la variable i est incrémentée (i = i +1 ).

Une fois le traitement de la boucle terminé, nous exploitons cette variable i avec l'instruction Redim pour définir le nombre de lignes de ce tableau (1 To i), mais aussi pour définir son nombre de colonnes qui était déjà connu (1 To nbCol). Dès lors, nous réaffectons la variable premLigne sur la première ligne du tableau. En effet, nous avons une seconde boucle à engager mais cette fois pour récolter les informations concordantes. Et pour stocker ces informations, nous réinitialisons la variable i à 1 pour qu'elle pointe au départ sur la première rangée du tableau de variables.

Chercher le critère dans chaque ligne
Nous allons maintenant devoir engager une boucle identique à la précédente. Mais son rôle n'est plus de connaître le nombre de lignes concordantes pour dimensionner le tableau de variables. Son objectif est désormais de remplir chaque colonne du tableau de variables pour chaque ligne répondant favorablement au critère émis. Rappelons-le, ces nombres de lignes et de colonnes sont variables pour que la fonction VBA soit capable de s'adapter à tout contexte et à toute base de données.

• A la suite du code, reproduire la précédente boucle, comme suit :

...
Do While Sheets(feuille.Name).Cells(premLigne, premColonne).Value <> ""
If (Sheets(feuille.Name).Cells(premLigne, premColonne).Value = Critere) Then

i = i + 1
End If
premLigne = premLigne + 1
Loop
...

Remplir le tableau de variables
Pour chaque ligne analysée répondant favorablement au critère émis, nous devons remplir chaque colonne du tableau de variables pour l'indice de ligne en cours (i). Rappelons-le, ce nombre de colonnes est variable. Il dépend de la base de données sur laquelle la fonction est exercée. Pour les passer toutes en revue, à l'intérieur de la première boucle et de l'instruction conditionnelle, nous proposons d'engager une boucle For Next.

• Dans l'instruction conditionnelle, avant l'incrémentation, ajouter la boucle For Next suivante :

...
For j = 1 To nbCol
extract(i, j) = Sheets(feuille.Name).Cells(premLigne, premColonne + j - 1).Value
Next j
...

Pour chaque ligne (i), nous faisons varier toutes les colonnes (1 To nbCol). C'est ainsi que nous inscrivons les données concordantes aux bons emplacements dans le tableau de variables. Nous allons les chercher sur la ligne en cours d'analyse dans la base de données (premLigne) et sur la colonne parcourue par la seconde boucle (premColonne + j - 1). Le retranchement (-1) est nécessaire, puisque la variable premColonne inclus la première cellule dans l'énumération.

Retourner la plage de valeurs
Pour une fonction aussi souple et aussi puissante, nous en avons déjà terminé. Il ne nous reste plus qu'à retourner cette plage de résultats, traduite par le tableau de variables. Pour cela et comme vous le savez, nous devons affecter le nom même de la fonction sur le tableau de variables.

• Après la boucle, donc avant le End Function, ajouter l'affectation suivante :

...
rechercheVba = extract
...

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Extraire (ALT + Tab),
• Sélectionner la case de la première extraction à livrer en cliquant sur sa cellule B6,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire le nom de la nouvelle fonction, suivi d'une parenthèse, soit : rechercheVba(,
• Désigner le tableau de recherche par son nom, soit : Bdd,

Vous pouvez aussi le sélectionner intégralement à la souris.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Désigner le département sélectionné en cliquant sur sa cellule D3,
• Fermer la parenthèse de la fonction RechercheVba,
• Puis, valider la formule en toute simplicité avec la touche Entrée du clavier,

Comme vous pouvez l'apprécier, tous les enregistrements concordants sont extraits en un clin d'oeil. Et pour cela, nous avons bâti une fonction qui n'attend que la source de données et le critère d'extraction. Bien entendu, si vous changez de département avec la liste déroulante en cellule D3, les importations s'actualisent instantanément.



De plus et vous l'avez compris, cette fonction a la capacité de s'adapter à tout tableau pour ajuster sa réponse en nombre de lignes et en nombre de colonnes.



Voilà donc une fonction précieuse à ajouter dans la bibliothèque des fonctions Excel, pour qu'elle soit disponible tout le temps.