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 trouverez 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:

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…

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.

Remarque importante: Solution à WorksheetFunction qui ne sait pas gérer les erreurs #N/A

Je viens de découvrir que si l'on passe par Application.WorksheetFunction.VLookup, le code VBA va bloquer si la valeur recherchée n'est pas trouvée. Là où la fonction RECHERCHEV d'Excel renvoie l'erreur #N/A, le code VBA s'arrête et même la gestion d'erreur n'y fera rien.

MAIS… Il y a une solution. Et elle est même assez simple: j'ai trouvé qu'il était possible d'appeler la fonction VLookup directement à partir de Application. sans passer par WorksheetFunction. Et étonnamment, une valeur non trouvée ne bloque plus le code et la fonction sait la gérer correctement.

Les codes VBA qui suivent tiennent désormais compte de ce comportement.

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/ )

On Error GoTo RECHERCHEVerror
    RECHERCHEV = Application.VLookup(Valeur_Cherchee, Table_matrice, No_index_col, Valeur_proche)
    If IsError(RECHERCHEV) Then RECHERCHEV = "#N/A"
    
Exit Function
RECHERCHEVerror:
    RECHERCHEV = "#N/A"
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?

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 )
    
On Error GoTo RECHERCHEVerror
    
    If IsNumeric(Colonne_a_retourner) = True Then
        RECHERCHEV = Application.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.VLookup(Valeur_Cherchee, Table_matrice, Colonne_Index, Valeur_proche)
    End If

If IsError(RECHERCHEV) Then RECHERCHEV = "#N/A"
    
Exit Function
RECHERCHEVerror:
    RECHERCHEV = "#N/A"
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…

Et question vitesse de RECHERCHEV en VBA? Mieux qu'un TGV!

Si vous vous demandez si cela vaut la peine d'utiliser RECHERCHEV en VBA plutôt que des boucles, soyez rassurés: RECHERCHEV est beaucoup plus rapide!

Je voulais en avoir le cœur net donc j'ai fait un test. Très simple: deux tableaux, tout deux avec 10.000 lignes. On recherche pour chaque ligne de la table A une valeur correspondante de la table B.

Et les résultats?

• deux boucles For … Next imbriquées: 6 minutes 37 secondes
• une boucle For … Next avec la fonction RECHERCHEV: 0 minutes 5 secondes!

Le résultat est tout à fait sans appel! Donc oui, servez vous des fonctions Excel dès que vous le pouvez! Attention, le code avec les deux boucles fonctionnera dans toutes les applications de Office. Le code avec la fonction RECERCHEV ne fonctionnera que dans VBA d'Excel…

Sub TestVitesse()
Dim i As Single
Dim TermeRecherche As String
TimeStart = Now

For i = 1 To 10000
    TermeRecherche = Sheets("Feuil1").Cells(i, 1).Value
    For j = 1 To 10000
        If Sheets("Feuil2").Cells(j, 1).Value = TermeRecherche Then
            Sheets("Feuil1").Cells(i, 2).Value = Sheets("Feuil2").Cells(j, 2).Value
            Exit For
        Else
        End If
    Next j
Next i

TimeEnd = Now

MsgBox Format(TimeEnd - TimeStart, "hh:mm:ss")
End Sub


Sub TestVitesse2()
Dim i As Single
Dim TermeRecherche As String
Dim MaPlage As Range
TimeStart = Now

Set MaPlage = ThisWorkbook.Sheets("Feuil2").Range("A1:B10000")

For i = 1 To 10000
    TermeRecherche = Sheets("Feuil1").Cells(i, 1).Value
    Sheets("Feuil1").Cells(i, 4).Value = RECHERCHEV(TermeRecherche, MaPlage, 2, False)
Next i

TimeEnd = Now

MsgBox Format(TimeEnd - TimeStart, "hh:mm:ss")
End Sub


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/ )

On Error GoTo RECHERCHEVerror
    RECHERCHEV = Application.VLookup(Valeur_Cherchee, Table_matrice, No_index_col, Valeur_proche)
    If IsError(RECHERCHEV) Then RECHERCHEV = "#N/A"
    
Exit Function
RECHERCHEVerror:
    RECHERCHEV = "#N/A"
End Function

Pour aller plus loin en VBA…

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

• Formation en ligne gratuite – "VBA: droit au but!"
• Liste de toutes les fonctions disponibles en VBA
• Différent manières d'effectuer une SOMME en VBA
• Envoyer un email avec VBA
• D'autres codes sources VBA prêts à l'emploi
• Tant que l'on parle de RECHECHEV, vous trouverez tout les détails su cette fonction dans le tutoriel sur la "recherche verticale"
• Et pour terminer, vous pouvez découvrir la nouvelle fonction Excel RECHERCHEX