fonction ISNA et comment l’utiliser pour ignorer l’erreur dans la formule Vlookup

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

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 la feuille de calcul sont répertoriées dans la classe Application.Worksheet Function. Et en utilisant l’opérateur point, vous pouvez tous y accéder.

Dans n’importe quel sous, écrivez Application.Worksheet Function. 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 plage 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 VLOOKUP. 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 une puissance de calculs immenses 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 une utilisation dans VBA. Vous n’avez pas besoin d’utiliser l’objet Application.WorksheetFunction.

Par exemple, des fonctions comme Len () qui est utilisée 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 comme 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

When you run the above sub, it will return:

J’espère que cet article sur l’utilisation des fonctions de feuille de calcul comme VLOOKUP dans VBA dans Excel est explicatif. Trouvez plus d’articles sur les formules VBA et les formules Excel associées ici. Si vous avez aimé nos blogs, partagez-les avec vos amis sur Facebook. Et vous pouvez également nous suivre sur Twitter et Facebook. Nous serions ravis de vous entendre, faites-nous savoir comment nous pouvons améliorer, compléter ou innover notre travail et le rendre meilleur pour vous. Écrivez-nous à [email protected].

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 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 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-using-vba-in-microsoft-excel [Formater les données avec des formats numériques personnalisés à l’aide de VBA dans Microsoft Excel]: Pour changer le nombre format de colonnes spécifiques dans Excel utilise cet extrait de code VBA. Il couvre le format numérique du format spécifié au format spécifié en un clic.

link: / 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.

link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Exécuter une macro si une modification est apportée sur la feuille dans la plage spécifiée]: Pour exécuter votre code de macro lorsque la valeur dans une plage spécifiée, utilisez ce code VBA. Il détecte tout changement effectué dans la plage spécifiée et déclenche l’événement.

link: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [Simplest VBA Code to Highlight Current Row and Column Using]: Utilisez ce petit extrait de code VBA pour mettre en évidence la ligne et la colonne actuelles de la feuille.

Articles populaires:

link: / tips-if-condition-in-excel [Comment utiliser la fonction IF dans Excel]: L’instruction IF dans Excel vérifie la condition et renvoie une valeur spécifique si la condition est TRUE ou renvoie une autre valeur spécifique si FALSE .

link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser 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 dans différentes plages et feuilles.

lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.

link: / tips-countif-in-microsoft-excel [Comment utiliser la fonction COUNTIF dans Excel]: 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.