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:

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.

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?

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 )
    
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 ForNext imbriquées: 6 minutes 37 secondes
  • une boucle ForNext 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…

Les codes VBA utilisés pour le test comparatif
Et voici les deux codes que j’ai utilisé pour faire le test. Sachez que les valeurs utilisées dans le test étaient triées dans le même ordre dans les deux tableaux. Cela signifie que le code avec les deux boucles a dû parcourir 10.000 itérations dans la table B lors do sa recherche pour la 10.000ème ligne du tableau A.

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

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.

 

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

19 commentaires sur “RECHERCHEV en VBA: Tutoriel et code VBA déjà prêt”