Ce tutoriel vous apprendra à créer et utiliser des fonctions avec et sans paramètres en VBA
VBA contient une grande quantité de fonctions intégrées que vous pouvez utiliser, mais vous pouvez également écrire les vôtres. Lorsque vous écrivez du code en VBA, vous pouvez l'écrire dans une sous-procédure ou une procédure de fonction. Une procédure de fonction est capable de renvoyer une valeur à votre code. Ceci est extrêmement utile si vous souhaitez que VBA effectue une tâche pour renvoyer un résultat. Les fonctions VBA peuvent également être appelées depuis Excel, tout comme les fonctions Excel intégrées d'Excel.
Créer une fonction sans arguments
Pour créer une fonction, vous devez définir la fonction en lui donnant un nom. La fonction peut alors être définie comme un type de données indiquant le type de données que vous souhaitez que la fonction renvoie.
Vous pouvez créer une fonction qui renvoie une valeur statique à chaque fois qu'elle est appelée - un peu comme une constante.
| 123 | Fonction GetValue() en tant qu'entierObtenirValeur = 50Fonction de fin |
Si vous deviez exécuter la fonction, la fonction renverrait toujours la valeur 50.
Vous pouvez également créer des fonctions qui font référence à des objets dans VBA, mais vous devez utiliser le mot-clé Set pour renvoyer la valeur de la fonction.
| 123 | Fonction GetRange() en tant que plageDéfinir GetRange = Range("A1:G4")Fonction de fin |
Si vous deviez utiliser la fonction ci-dessus dans votre code VBA, la fonction renverrait toujours la plage de cellules A1 à G4 dans la feuille sur laquelle vous travaillez.
Appel d'une fonction à partir d'une sous-procédure
Une fois que vous avez créé une fonction, vous pouvez l'appeler de n'importe où ailleurs dans votre code en utilisant une sous-procédure pour appeler la fonction.
La valeur de 50 serait toujours renvoyée.
Vous pouvez également appeler la fonction GetRange à partir d'une sous-procédure.
Dans l'exemple ci-dessus, la fonction GetRange est appelée par la procédure Sub pour mettre en gras les cellules de l'objet plage.
Création de fonctions
Argument unique
Vous pouvez également affecter un ou plusieurs paramètres à votre fonction. Ces paramètres peuvent être appelés arguments.
| 123 | Fonction ConvertKilosToPounds (dblKilo en tant que Double) en tant que DoubleConvertKiloToPounds = dblKilo*2.2Fonction de fin |
Nous pouvons ensuite appeler la fonction ci-dessus à partir d'une sous-procédure afin de déterminer le nombre de livres d'un nombre spécifique de kilos.
Une fonction peut être appelée à partir de plusieurs procédures dans votre code VBA si nécessaire. C'est très utile car cela vous évite d'avoir à écrire le même code encore et encore. Il vous permet également de diviser de longues procédures en petites fonctions gérables.
Dans l'exemple ci-dessus, nous avons 2 procédures - chacune d'entre elles utilise la fonction pour calculer la valeur en livres des kilos qui leur ont été transmis dans le dblKilo Argument de la fonction.
Arguments multiples
Vous pouvez créer une fonction avec plusieurs arguments et transmettre les valeurs à la fonction au moyen d'une sous-procédure.
| 123 | Fonction CalculateDayDiff(Date1 as Date, Date2 as Date) as DoubleCalculateDayDiff = Date2-Date1Fonction de fin |
On peut alors appeler la fonction pour calculer le nombre de jours entre 2 dates.
Arguments facultatifs
Vous pouvez également passer des arguments optionnels à une fonction. En d'autres termes, vous pouvez parfois avoir besoin de l'argument, et parfois non, selon le code avec lequel vous utilisez la fonction .
| 123456 | Fonction CalculateDayDiff(Date1 as Date, optionnel Date2 as Date) as Double'vérifiez la deuxième date et si ce n'est pas le cas, rendez Date2 égal à la date d'aujourd'hui.Si Date2=0 alors Date2 = Date'calculer la différenceCalculateDayDiff = Date2-Date1Fonction de fin |
Valeur d'argument par défaut
Vous pouvez également définir la valeur par défaut des arguments facultatifs lorsque vous créez la fonction afin que si l'utilisateur omet l'argument, la valeur que vous avez définie par défaut soit utilisée à la place.
| 1234 | Fonction CalculateDayDiff(Date1 as Date, optionnel Date2 as Date="06/02/2020") as Double'calculer la différenceCalculateDayDiff = Date2-Date1Fonction de fin |
ByVal et ByRef
Lorsque vous passez des valeurs à une fonction, vous pouvez utiliser le ParVal ou ParRéf mots clés. Si vous omettez l'un de ces éléments, le ParRéf est utilisé par défaut.
ParVal signifie que vous passez une copie de la variable à la fonction, alors que ParRéf signifie que vous faites référence à la valeur d'origine de la variable. Lorsque vous passez une copie de la variable (ParVal), la valeur d'origine de la variable est NE PAS changé, mais lorsque vous référencez la variable, la valeur d'origine de la variable est modifiée par la fonction.
| 1234 | Fonction GetValue(ByRef intA As Integer) As IntegerintA = intA * 4ObtenirValeur = intAFonction de fin |
Dans la fonction ci-dessus, ByRef pourrait être omis et la fonction fonctionnerait de la même manière.
| 1234 | Fonction GetValue(intA As Integer) As IntegerintA = intA * 4ObtenirValeur = intAFonction de fin |
Pour appeler cette fonction, nous pouvons exécuter une sous-procédure.
| 123456789 | Valeurs de sous-test()Dim intVal en tant qu'entier'remplir la variable avec la valeur 10intVal = 10'exécute la fonction GetValue et affiche la valeur dans la fenêtre immédiateDebug.Print GetValue(intVal)'afficher la valeur de la variable intVal dans la fenêtre immédiateDebug.Print intValFin du sous-marin |
Notez que les fenêtres de débogage affichent la valeur 40 les deux fois. Lorsque vous passez la variable IntVal à la fonction - la valeur de 10 est transmise à la fonction et multipliée par 4. L'utilisation du mot clé ByRef (ou son omission totale) MODIFIE la valeur de la variable IntVal. Cela s'affiche lorsque vous affichez d'abord le résultat de la fonction dans la fenêtre immédiate (40), puis la valeur de la variable IntVal dans la fenêtre de débogage (également 40).
Si nous ne voulons PAS changer la valeur de la variable d'origine, nous devons utiliser ByVal dans la fonction.
| 1234 | Fonction GetValue(ByVal intA As Integer) As IntegerintA = intA * 4ObtenirValeur = intAFonction de fin |
Maintenant, si nous appelons la fonction depuis une sous-procédure, la valeur de la variable IntVal restera à 10.
Fonction de sortie
Si vous créez une fonction qui teste une certaine condition et qu'une fois la condition vérifiée, vous souhaitez renvoyer la valeur de la fonction, vous devrez peut-être ajouter une instruction Exit Function dans votre Function afin de quitter la fonction avant vous avez parcouru tout le code de cette fonction.
| 12345678910111213 | Fonction FindNumber (strSearch As String) As IntegerDiminuer en tant qu'entier'boucle à travers chaque lettre de la chaînePour i = 1 To Len(strSearch)'si la lettre est numérique, renvoie la valeur à la fonctionSi IsNumeric(Mid(strSearch, i, 1)) ThenFindNumber= Mid(strSearch, i, 1)'puis quitter la fonctionFonction de sortieFin siProchainFindNumber= 0Fonction de fin |
La fonction ci-dessus parcourt la chaîne fournie jusqu'à ce qu'elle trouve un nombre, puis renvoie ce nombre à partir de la chaîne. Il ne trouvera que le premier nombre de la chaîne car il Sortir la fonction.
La fonction ci-dessus peut être appelée par une sous-routine telle que celle ci-dessous.
| 1234567 | Sous-CheckForNumber()Dim NumIs en tant qu'entier'passe une chaîne de texte à la fonction de recherche de nombreNumIs = FindNumber ("Étage supérieur, 8 Oak Lane, Texas")'afficher le résultat dans la fenêtre immédiateDebug.Print NumIsFin du sous-marin |
Utilisation d'une fonction à partir d'une feuille Excel
En plus d'appeler une fonction à partir de votre code VBA à l'aide d'une sous-procédure, vous pouvez également appeler la fonction à partir de votre feuille Excel. Les fonctions que vous avez créées doivent par défaut apparaître dans votre liste de fonctions dans la section Défini par l'utilisateur de la liste de fonctions.
Clique sur le fx pour afficher la boîte de dialogue Insérer une fonction.
Sélectionner Défini par l'utilisateur de la liste des catégories
Sélectionnez la fonction dont vous avez besoin parmi les Fonctions définies par l'utilisateur (FDU).
Alternativement, lorsque vous commencez à écrire votre fonction dans Excel, la fonction doit apparaître dans la liste déroulante des fonctions.
Si vous ne souhaitez pas que la fonction soit disponible dans une feuille Excel, vous devez mettre le mot Privé devant le mot Fonction lorsque vous créez la fonction dans votre code VBA.
| 123 | Fonction privée CalculateDayDiff(Date1 as Date, Date2 as Date) as DoubleCalculateDayDiff = Date2-Date1Fonction de fin |
Il n'apparaîtra plus dans la liste déroulante affichant les fonctions Excel disponibles.
Chose intéressante, cependant, vous pouvez toujours utiliser la fonction - elle n'apparaîtra tout simplement pas dans la liste lorsque vous la chercherez !
Si vous avez déclaré le deuxième argument comme Optionnel, vous pouvez l'omettre dans la feuille Excel ainsi que dans le code VBA.
Vous pouvez également utiliser la fonction a que vous avez créée sans arguments dans votre feuille Excel.
