Tutoriel Excel VBA: utiliser la fonction VLOOKUP() dans un Userform

La fonction VLOOKUP() peut être une fonction Excel utile. Même si elle est simple à utiliser, elle peut souvent être déroutante lorsqu'elle est utilisée dans VBA. Dans ce tutoriel, je vous montrerai comment n'importe qui peut facilement utiliser la fonction VLOOKUP() dans VBA. Je couvrirai également les écueils et comment les éviter.

Si vous n'êtes pas familier avec VLOOKUP (RECHERCHEV) dans Excel, ce tutoriel fournit une excellente introduction.

Table des matières

La fonction Vlookup dans Excel VBA

Pourquoi utiliser les fonctions de feuille de calcul dans VBA ?

Créer un formulaire utilisateur personnalisé

Ajouter deux zones de sortie au formulaire

Coder le bouton Rechercher un prix

Ajouter un bouton

La fonction Vlookup dans Excel VBA

La fonction Vlookup dans Excel est utilisée pour rechercher une valeur dans un tableau et renvoyer sa valeur correspondante à partir d'une autre colonne. La valeur à rechercher doit être présente dans la première colonne. Il est également nécessaire de mentionner s'il faut rechercher une correspondance exacte ou une correspondance approximative. La fonction de feuille de calcul VLOOKUP peut être utilisée dans le codage VBA. Elle n'est pas intégrée dans VBA et ne peut donc être appelée qu'en utilisant la feuille de calcul.

La fonction Vlookup dans Excel a la syntaxe suivante :

Vlookup(lookup_value, table_arrray, col_index_num, [range_lookup])

Où lookup_value est la valeur à rechercher, table_arrray est la table, col_index_num est le numéro de colonne de la valeur de retour, range_lookup signifie si la correspondance est exacte ou approximative. range_lookup peut être VRAI / FAUX ou 0/1.

En code VBA, la fonction Vlookup peut être utilisée comme :

Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)

Choses à retenir sur VBA VLOOKUP :

• Dans VBA aussi, VLOOKUP fonctionne de la même manière que la fonction de feuille de calcul.
• La déclaration des variables et l'attribution du type de données est la chose importante.
• Dans la cellule résultante, nous ne voyons aucun type de formule. Toute valeur renvoyée par la fonction VBA ne contient aucun type de formule.

Pourquoi utiliser les fonctions de feuille de calcul dans VBA ?

C'est plus compliqué que d'utiliser simplement la fonction dans Excel, alors pourquoi écririez-vous une macro VBA pour une Vlookup ?

Toute fonction présente dans Excel va être légèrement plus compliquée à utiliser dans VBA. Mais y avoir accès vous donne la possibilité d'une automatisation très puissante.

Par exemple, si vous souhaitez exécuter plusieurs Vlookup et que les résultats soient placés dans un tableau, vous constaterez peut-être que vous devez créer manuellement chaque Vlookup dans Excel.

Avec VBA, vous pouvez écrire un script qui automatise le processus en vous faisant gagner du temps. Même s'il est possible d'utiliser des fonctions dans Excel, il sera probablement beaucoup plus efficace dans VBA. Vous pouvez également enregistrer le script et l'exécuter sur d'autres feuilles de calcul.

Créer un formulaire utilisateur personnalisé

Visual Basic pour Applications fait d'Excel plus qu'une simple feuille de calcul pour enregistrer, trier et analyser des données. VBA vous permet de créer de petits programmes ou routines dans Excel que les utilisateurs peuvent utiliser pour accéder aux données de la feuille de calcul. Dans ce tutoriel VBA, je vais utiliser la fonction VLOOKUP pour créer un formulaire qui permet aux utilisateurs de rechercher le prix et le stock d'un article particulier rapidement et facilement.

Je vais utiliser les données suivantes pour ce tutoriel :

Pour ajouter un formulaire utilisateur, accédez à l’onglet « Développeur » puis cliquez sur « Visual Basic » pour ouvrir l'éditeur VBA. Un nouvel éditeur s'ouvrira.

Pour ajouter un formulaire utilisateur personnalisé à l'éditeur, sélectionnez "Insertion" et cliquez sur "UserForm".

Un nouveau formulaire sera créé dans la fenêtre de l'éditeur :

Maintenant, ajoutez une zone de liste déroulante au formulaire. On va utiliser cette case pour permettre aux utilisateurs de sélectionner l'élément qu'ils recherchent. Pour créer la zone de liste déroulante, j’utilise une plage nommée dans la feuille de calcul appelée « articles ». Pour créer une plage de noms, sélectionnez les éléments dans votre feuille de calcul, faites un clic droit puis sélectionnez «Définir le nom» et nommez la plage «articles».

Ajoutez maintenant une zone de liste modifiable à votre formulaire en sélectionnant l'option zone de liste modifiable, puis en faisant glisser et en dessinant une zone de liste sur votre nouveau formulaire :

Dans la zone des propriétés, renommez la zone de liste déroulante «ItemChosen» et changez la propriété de la source RowSource en «articles». Cela chargera la liste des éléments dans la zone de liste déroulante pour que l'utilisateur puisse sélectionner un élément.

Ajoutez une étiquette appelée «Article» en ajoutant un intitulé de la boîte à outils, puis en changeant la légende sous la section des propriétés en «Article» :

Ajouter deux zones de sortie au formulaire

Maintenant que j’ai un champ de saisie, je vais ajouter deux étiquettes pour la sortie en fonction des résultats de la fonction VLOOKUP. Je vais ajouter quatre étiquettes. Deux étiquettes seront des descriptions et deux contiendront les résultats de la fonction Vlookup :

Renommez le stock d'articles label4 en remplaçant la propriété name par «ItemPrice» :

Et renommez label5 «ItemStock» en utilisant la fenêtre des propriétés :

Ajoutez maintenant un bouton de prix "Rechercher le prix" en sélectionnant un bouton de commande dans la boîte à outils et en changeant la légende en « Rechercher le prix »:

Coder le bouton Rechercher un prix

Après avoir ajouté le bouton de commande, vous devez le coder en utilisant la fonction Vlookup pour trouver le prix et le stock d'un article que l'utilisateur sélectionne.

Pour coder le bouton « Rechercher un prix », double-cliquez dessus pour ouvrir l'éditeur VBA.

La fonction Vlookup nécessite 4 arguments. On doit indiquer à VBA quoi rechercher, dans quelle plage rechercher, dans quelle colonne renvoyer la valeur et si nous recherchons une correspondance exacte ou nous recherchons la correspondance la plus proche.

Pour rechercher le prix, le code ressemblera à ceci :

ItemPrice = Application.WorksheetFunction.VLookup(ItemChosen, Range("A1:C7"), 2, False)

 J’attribue la réponse au libellé que j’ai nommé «ItemPrice». Ainsi, la valeur sera affichée dans le champ d'étiquette «ItemPrice». J’utilise ensuite la fonction Vlookup pour trouver la valeur.

• Application.WorksheetFunction nous indique quelle feuille de calcul utiliser.
• Vlookup appelle la fonction. «ItemChosen» provient de la zone de liste déroulante qu’on a créée.
• La plage ("A1:C7") indique à Excel où rechercher les données.
• Le chiffre 2 fait référence à la colonne qui contient les données (Les données de prix sont dans la colonne 2 de la feuille de calcul).
• False signifie qu’on recherche une correspondance exacte.

Je vais maintenant ajouter une ligne de code similaire pour rechercher la quantité de stock restant par article:

ItemStock = Application.WorksheetFunction.VLookup (ItemChosen, Range («A1: C10»), 3, False)

Donc, votre routine entière ressemblera à ceci :

Il ne vous reste plus qu'à ajouter un bouton pour lancer le formulaire.

Ajouter un bouton

Choisissez « Insertion » dans le menu développeur, puis sélectionnez « Bouton de commande » dans les contrôles ActiveX. Double-cliquez sur le bouton pour lancer l'éditeur VBA. Remplacez la légende du bouton par «Rechercher les prix» dans les propriétés de contrôle du bouton:

Ajoutez maintenant le code suivant au bouton dans VBA:

Cela lancera votre formulaire lorsque l'utilisateur cliquera sur le bouton.

Vous êtes maintenant prêt à tester votre bouton et votre formulaire. Fermez l'éditeur et cliquez sur le bouton Rechercher les prix. Le formulaire suivant devrait s'ouvrir:

Lorsqu'un utilisateur sélectionne un article et clique sur «Rechercher le prix», les champs Prix et Stock sont automatiquement mis à jour:

L'ajout de formulaires et l'utilisation d'une feuille de calcul est simple et l'ajout de la puissance de fonctions comme la fonction Excel VBA Vlookup peut vraiment faire passer vos feuilles de calcul au niveau supérieur.

Une fois que vous avez appris à utiliser les fonctions de feuille de calcul d'Excel à partir de VBA, vous êtes prêt à commencer à créer des scripts très puissants. Essayez d'utiliser quelques fonctions de feuille de calcul dans les scripts VBA au cours de la semaine à venir. Vous pourriez être surpris du nombre d'utilisations intéressantes que vous allez découvrir.

Télécharger le fichier Excel de ce tutoriel