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:

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.

 

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.

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:

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:

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.
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”