RECHERCHEV en VBA: Tutoriel et code VBA déjà prêt


La fonction Excel RECHERCHEV est, à ce jour, la 3ème fonction la plus utilisée dans les classeurs Excel. Et vous êtes nombreux à se demander: "Comment reproduire RECHERCHEV en VBA?" Voici donc les explications et les codes VBA qui vous permettront d'utiliser cette fonction plus que pratique directement en VBA – qui n'a pas d'équivalent direct de cette fonction (comme vous pouvez le constater dans la Liste de toutes les fonctions VBA que je vous propose également).

Je me permets donc de vous proposer l'équivalent de la fonction RECHERCHEV en VBA, simple, efficace et prête à l'emploi (il vous suffit de la copier / coller dans votre projet) !

Comment faire? Il faut passer par Application.WorksheetFunction

Il est finalement relativement simple d'accéder à RECHERCHEV – il faut pour cela passer par Application.WorksheetFunction .

Si vous n'êtes pas familiers avec les WorksheetFunction, vous pouvez en apprendre plus dans le tutoriel que j'ai fait à ce sujet (ou vous pouvez aussi visiter la page Microsoft dédiée). Sachez que ce n'est pas bien compliqué – faites juste attention au fait qu'il faut utiliser les noms ANGLAIS des fonctions Excel et pas les noms en français. L'équivalent anglais de RECHERCHEV est "VLOOKUP", c'est donc VLOOKUP qu'il faudra évoquer dans le code VBA.

Astuce: Vous pouvez le nom anglais de n'importe quelle fonction Excel dans la Liste de toutes les fonctions Excel

Le code VBA officiel à utiliser a la forme suivante:

MonResultat = Application.WorksheetFunction.VLookup(Argument1, Argument2, Argument3, Argument4)

Le petit bémol de la commande WorksheetFunction est que dans l'éditeur VBA, vous ne verrez pas à quoi correspondent les arguments comme vous pouvez le voir directement dans Excel. Vous verrez uniquement le nombre d'arguments qui peuvent / doivent être utilisés ainsi que le fait s'ils sont obligatoires ou facultatifs…

Voici donc ce que vous verrez dans l'éditeur VBA:

RECHERCHEV en VBA dans l'éditeur VBA

Comme vous pouvez le constater, le Application.WorksheetFunction.VLookup s'attends à 4 arguments dont 1 (le dernier) est facultatif. Et ceci correspond évidemment aux arguments de la fonction RECHERCHEV

RECHERCHEV en VBA: arguments en Excel

Il faut donc se souvenir quel argument correspond à quel valeur. Ou bien jeter un coup d’œil dans Excel – et cela est valable pas seulement pour RECHERCHEV mais pour toutes les fonctions Excel utilisées en VBA via WorksheetFunction.

Equivalent complet de RECHERCHEV en VBA

Pour pallier au désavantage cité plus haut, on peut réécrire le RECHERCHEV en VBA en tant que fonction complète. Le code est assez simple et concis.

Function RECHERCHEV(Valeur_Cherchee As Variant, Table_matrice As Range, No_index_col As Single, Optional Valeur_proche As Boolean)
'par Excel-Malin.com ( https://excel-malin.com/ )
    RECHERCHEV = Application.WorksheetFunction.VLookup(Valeur_Cherchee, Table_matrice, No_index_col, Valeur_proche)
End Function

 

Quand vous allez appeler cette fonction dans l'éditeur VBA, elle se comportera exactement comme en Excel! C'est bien plus lisible et agréable à utiliser, vous ne trouvez pas?

RECHERCHEV en VBA par Excel-Malin.com

 

Exemple d'utilisation de la fonction RECHERCHEV en VBA

Et voici un exemple de l'utilisation de la fonction RECHERCHEV  – n'oubliez pas de copier dans votre projet non seulement cet exemple mais aussi la fonction dont le code se trouve plus haut.

Sub Exemple_d_utilisation_de_RECHERCHEV()
'par Excel-Malin.com ( https://excel-malin.com )
    Dim MaValeur As Variant
    Dim MaPlage As Range
    Dim MaColonne As Single
    Dim ValeurProche As Boolean
    
    MaValeur = "x"
    Set MaPlage = ThisWorkbook.Sheets("Feuil1").Range("B:E")
    MaColonne = 4
    ValeurProche = False
    
    MsgBox RECHERCHEV(MaValeur, MaPlage, MaColonne, ValeurProche)
    
End Sub

Remarque 1: n'oubliez pas de déclarer vos variables ( Dim ... As ... ) et d'attribuer la plage avec la table à utiliser ( Set ... = ... )

Remarque 2: contrairement à Excel, la fonction en VBA retourne une valeur "fixe". C'est à dire que la fonction fait le calcul (recherche) avec les données qu"elle a à disposition au moment de son utilisation. Si les données changent, le résultat ne va pas s'adapter comme une fonction Excel. Cela a ses avantages et désavantages. Tout dépend la situation et de l'utilisation que vous en faites.

–> Si vous changez les données, il faudra recalculer. Par contre le fait d'avoir une valeur plutôt qu'une formule Excel rend votre fichier plus petit et plus rapide…

 

Fonction améliorée: le choix de la colonne contenant la valeur à retourner

Nous pouvons dévier de la fonction de base et la rendre plus efficace – c'est l'avantage de VBA de pouvoir adapter les fonctions selon ses besoins…

On peut imaginer que plutôt que le numéro de colonne à retourner, vous voudriez utiliser la lettre de la colonne – cela semble assez logique. Pas de problème! Et on peut même aller encore un peu plus loin: on peut avoir les deux dans une seule fonction! Soit vous pouvez utiliser l'index de la colonne (comme dans Excel) soit le nom de la colonne. La version suivante de la fonction va reconnaître vos intentions et retourner d'office le bon résultat!

Voici le code VBA de la fonction RECHERCHEV améliorée:

Function RECHERCHEV(Valeur_Cherchee As Variant, Table_matrice As Range, Colonne_a_retourner As Variant, Optional Valeur_proche As Boolean)
'par Excel-Malin.com ( https://excel-malin.com )
    If IsNumeric(Colonne_a_retourner) = True Then
        RECHERCHEV = Application.WorksheetFunction.VLookup(Valeur_Cherchee, Table_matrice, Colonne_a_retourner, Valeur_proche)
    Else
        Dim ColonneDebut As Single
        Dim ColonneRecherchee As Single
        
        ColonneDebut = Table_matrice.Cells(1).Column
        ColonneRecherchee = Range(Colonne_a_retourner & "1").Column
        Colonne_Index = ColonneRecherchee - ColonneDebut + 1
        RECHERCHEV = Application.WorksheetFunction.VLookup(Valeur_Cherchee, Table_matrice, Colonne_Index, Valeur_proche)
    End If
End Function

Cette fonction a donc deux fonctionnalités en ce qui concerne le choix de la colonne qui contient le résultat à renvoyer. Soit vous utilisez le n° de la colonne de la table comme dans Excel, soit vous indiquez le nom de la colonne (la lettre: par ex. "D" ou "BA").

Voici donc les exemples de l'utilisation de cette fonction améliorée:

Sub Exemple_d_utilisation_de_RECHERCHEV()
'par Excel-Malin.com ( https://excel-malin.com )

    Dim MaValeur As Variant
    Dim MaPlage As Range
    Dim MaColonne As Variant
    Dim ValeurProche As Boolean
    
    MaValeur = "x"
    Set MaPlage = ThisWorkbook.Sheets("Feuil1").Range("B:E")
    MaColonne = 4
    ValeurProche = False

    MsgBox RECHERCHEV(MaValeur, MaPlage, MaColonne, ValeurProche) 'avec le n° de la colonne - ici 4 (à partir de "B") donc colonne "E"
    MsgBox RECHERCHEV(MaValeur, MaPlage, "C", ValeurProche) 'ici, le résultat provient de la colonne "C"
    
End Sub

Comme vous pouvez voir, les deux fonctionnent!

Remarque: si vous voulez ajouter dans votre Projet VBA les deux fonctions, n'oubliez pas de changer le nom d'une d'elles car dans un Projet, il ne peut pas y avoir plusieurs fonctions avec le même nom…

Voilà, j'espère que cette fonction vous sera utile et qu'elle va rendre votre travail encore plus efficace!

Pour aller plus loin en VBA…

Voici quelques articles qui pourrait vous intéresser en relation avec le VBA, Excel, etc.

 

5 0

Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée.