Comment utiliser la feuille de fonctions telles que RECHERCHEV dans VBA Excel?
Les fonctions comme VLOOKUP, COUNTIF, SUMIF sont appelées fonctions de feuille de calcul. En règle générale, les fonctions prédéfinies dans Excel et prêtes à être utilisées sur une feuille de calcul sont des fonctions de feuille de calcul. Vous ne pouvez pas modifier ou voir le code derrière ces fonctions dans VBA.
D’autre part, les fonctions définies par l’utilisateur et les fonctions spécifiques à VBA comme MsgBox ou InputBox sont des fonctions VBA. Nous savons tous comment utiliser les fonctions VBA dans VBA. Mais que faire si nous voulons utiliser VLOOKUP dans un VBA. Comment fait-on cela? Dans cet article, nous explorerons exactement cela.
Utilisation des fonctions de feuille de calcul dans VBA
Pour accéder à la fonction de feuille de calcul, nous utilisons une classe Application. Presque toutes les fonctions de feuille de calcul sont répertoriées dans la classe Application.WorksheetFunction. Et en utilisant l’opérateur point, vous pouvez tous y accéder.
Dans n’importe quel sous, écrivez Application.WorksheetFunction. Et commencez à écrire le nom de la fonction. Intellisense de VBA affichera le nom des fonctions disponibles à utiliser. Une fois que vous avez choisi le nom de la fonction, il vous demandera les variables, comme toute fonction sur Excel. Mais vous devrez passer des variables au format compréhensible VBA. Par exemple, si vous voulez passer une plage A1: A10, vous devrez la passer en tant qu’objet range comme Range (« A1: A10 »).
Utilisons donc quelques fonctions de feuille de calcul pour mieux le comprendre.
Comment utiliser la fonction VLOOKUP dans VBA
Pour montrer comment vous pouvez utiliser une fonction VLOOKUP dans VBA, j’ai ici des exemples de données. Je dois afficher le nom et la ville de l’identifiant de connexion donné dans une boîte de message en utilisant VBA. Les données sont réparties dans la plage A1: K26.
Appuyez sur ALT + F11 pour ouvrir VBE et insérer un module. Voir le code ci-dessous.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Lorsque vous exécutez ce code, vous obtiendrez ce résultat.
Vous pouvez voir à quelle vitesse le VBA imprime le résultat dans une boîte de message. Examinons maintenant le code.
Comment ça marche?
1.
Dim loginID As String
Dim name, city As String
Nous avons d’abord déclaré deux variables de type chaîne pour stocker le résultat renvoyé par la fonction RECHERCHEV. J’ai utilisé des variables de type chaîne car je suis sûr que le résultat renvoyé par RECHERCHEV sera une valeur de chaîne. Si votre fonction de feuille de calcul doit renvoyer le type de valeur nombre, date, plage, etc., utilisez ce type de variable pour stocker le résultat. Si vous n’êtes pas sûr du type de valeur renvoyé par la fonction de feuille de calcul, utilisez des variables de type variant.
2.
loginID = « AHKJ_1-3357042451 »
Ensuite, nous avons utilisé la variable loginID pour stocker la valeur de recherche. Ici, nous avons utilisé une valeur codée en dur. Vous pouvez également utiliser des références. Par exemple.
Vous pouvez utiliser Range (« A2 »). Value pour mettre à jour dynamiquement la valeur de recherche de la plage A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range (« A1: K26 »), 2, 0) Ici, nous utilisons la fonction RECHERCHEV pour obtenir. Maintenant, lorsque vous redressez la fonction et ouvrez la parenthèse, elle vous montrera les arguments requis mais pas aussi descriptifs que sur Excel. Voir par vous-même.
Vous devez vous rappeler comment et quelle variable vous devez utiliser. Vous pouvez toujours revenir à la feuille de calcul pour voir les détails de la variable descriptive.
Ici, la valeur de recherche est Arg1. Pour Arg1, nous utilisons loginID. La table de recherche est Arg2. Pour Arg2, nous avons utilisé Range (« A1: K26 »). Notez que nous n’avons pas utilisé directement A2: K26 comme nous le faisons sur Excel. L’index des colonnes est Arg3.
Pour Arg3, nous avons utilisé 2, puisque le nom est dans la deuxième colonne. Le type de recherche est Arg4. Nous avons utilisé 0 comme Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range (« A1: K26 »), 4, 0)
De même, nous obtenons le nom de la ville.
4.
MsgBox (« Nom: » & nom & vbLf & « Ville: » & ville)
Enfin, nous imprimons le nom et la ville à l’aide de Messagebox.
Pourquoi utiliser la fonction de feuille de calcul dans VBA? Les fonctions de feuille de calcul possèdent la puissance d’immenses calculs et il ne sera pas intelligent d’ignorer la puissance des fonctions de feuille de calcul. Par exemple, si nous voulons l’écart type d’un ensemble de données et que vous voulez écrire le code entier pour cela, cela peut vous prendre des heures. Mais si vous savez comment utiliser la fonction de feuille de calcul STDEV.P dans VBA pour obtenir le calcul en une seule fois.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Utilisation de plusieurs fonctions de feuille de calcul VBA
Disons que nous devons utiliser une correspondance d’index pour récupérer certaines valeurs. Maintenant, comment feriez-vous la formule dans VBA. C’est ce que je suppose que vous écrirez:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Ce n’est pas faux mais c’est long. La bonne façon d’utiliser plusieurs fonctions consiste à utiliser un bloc With. Voir l’exemple ci-dessous:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Comme vous pouvez le voir, j’ai utilisé With block pour dire à VBA que j’utiliserai les propriétés et les fonctions de Application.WorksheetFunction. Je n’ai donc pas besoin de le définir partout. Je viens d’utiliser l’opérateur point pour accéder aux fonctions INDEX, MATCH, VLOOKUP et STDEV.P. Une fois que nous utilisons l’instruction End With, nous ne pouvons pas accéder aux fonctions sans utiliser de noms de fonction complets.
Donc, si vous devez utiliser plusieurs fonctions de feuille de calcul dans VBA, utilisez avec block.
Toutes les fonctions de feuille de calcul ne sont pas disponibles via Application.WorksheetFunction Certaines fonctions de feuille de calcul sont directement disponibles pour être utilisées dans VBA. Vous n’avez pas besoin d’utiliser l’objet Application.WorksheetFunction.
Par exemple, des fonctions comme Len () qui est utilisé pour obtenir le nombre de caractères dans une chaîne, gauche, droite, milieu, trim, offset etc. Ces fonctions peuvent être directement utilisées dans VBA. Voici un exemple.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Voir, ici, nous avons utilisé la fonction LEN sans utiliser l’objet Application.WorksheetFunction.
De même, vous pouvez utiliser d’autres fonctions telles que gauche, droite, milieu, char etc.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
Lorsque vous exécutez le sous-marin ci-dessus, il retournera:
5 He o ll
Alors oui les gars, voici comment vous pouvez utiliser la fonction de feuille de calcul d’Excel dans VBA. J’espère avoir été suffisamment explicatif et cet article vous a aidé. Si vous avez des questions concernant cet article ou tout autre élément lié à VBA, posez-les dans la section commentaires ci-dessous. Jusque-là, vous pouvez lire sur d’autres sujets connexes ci-dessous.
Articles liés:
link: / excel-macros-and-vba-what-is-csng-function-in-excel-vba [Qu’est-ce que la fonction CSng dans Excel VBA] | La fonction SCng est une fonction VBA qui convertit tout type de données en un nombre à virgule flottante simple précision («étant donné qu’il s’agit d’un nombre»). J’utilise principalement la fonction CSng pour convertir des nombres au format texte en nombres réels.
link: / vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [Comment obtenir le texte et le nombre en sens inverse via VBA dans Microsoft Excel] | * Pour inverser le nombre et le texte, nous utilisons des boucles et une fonction intermédiaire dans VBA. 1234 sera converti en 4321, « vous » sera converti en « uoy ». Voici l’extrait.
link: / formating-in-vba-format-table-dépendant-des-formats-numériques-utilisant-vba-in-microsoft-excel [Formater les données avec des formats numériques personnalisés en utilisant VBA dans Microsoft Excel] | Pour modifier le format numérique de colonnes spécifiques dans Excel, utilisez cet extrait de code VBA. Il couvre le format numérique du format spécifié au format spécifié en un clic.
lien: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Utilisation d’un événement de changement de feuille de travail pour exécuter une macro lorsqu’une modification est apportée] | Donc, pour exécuter votre macro chaque fois que la feuille est mise à jour, nous utilisons les événements de feuille de travail de VBA.
lien: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Exécuter une macro si un changement est effectué sur la feuille dans la plage spécifiée] | Pour exécuter votre code de macro lorsque la valeur d’une plage spécifiée change, utilisez ce code VBA. Il détecte tout changement effectué dans la plage spécifiée et déclenche l’événement.
lien: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [code VBA le plus simple pour mettre en évidence la ligne et la colonne actuelles en utilisant] | Utilisez ce petit extrait de code VBA pour mettre en évidence la ligne et la colonne actuelles de la feuille.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
lien: / formules-et-fonctions-introduction-de-vlookup-function [La fonction RECHERCHEV dans Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles.
lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques.
La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.