Procédures "Fonction" et "Sous" dans VBA

Fonctions VBA intégrées

Avant de commencer à créer vos propres fonctions VBA, il est bon de savoir qu'Excel VBA possède une riche collection de fonctions intégrées prédéfinies que vous pouvez utiliser lors de l'écriture de votre code.

Une liste de ces fonctions peut être consultée dans l'éditeur VBA :

• Ouvrez un classeur Excel et lancez l'éditeur VBA (cliquez pour le faire Alt + F11), puis appuyez sur F2.
• Sélectionnez une bibliothèque dans la liste déroulante en haut à gauche de l'écran VBA.
• Une liste des classes et fonctions VBA intégrées apparaîtra. Cliquez sur le nom de la fonction pour afficher sa brève description en bas de la fenêtre. pressage F1 ouvrira la page d'aide en ligne de cette fonction.

En outre, une liste complète des fonctions VBA intégrées avec des exemples est disponible dans le centre de développement Visual Basic.

Procédures personnalisées "Fonction" et "Sous" dans VBA

Dans Excel Visual Basic, un ensemble de commandes qui exécutent une tâche spécifique est placé dans une procédure. Fonction (Fonction) ou Sol (Sous-programme). La principale différence entre les procédures Fonction и Sol c'est que la procédure Fonction renvoie le résultat, la procédure Sol - ne pas.

Par conséquent, si vous devez effectuer des actions et obtenir un résultat (par exemple, additionner plusieurs nombres), la procédure est généralement utilisée Fonction, et pour effectuer simplement certaines actions (par exemple, modifier la mise en forme d'un groupe de cellules), vous devez sélectionner la procédure Sol.

Arguments

Diverses données peuvent être transmises aux procédures VBA à l'aide d'arguments. La liste des arguments est spécifiée lors de la déclaration d'une procédure. Par exemple, la procédure Sol dans VBA ajoute l'entier donné (Integer) à chaque cellule de la plage sélectionnée. Vous pouvez passer ce numéro à la procédure en utilisant un argument, comme ceci :

Sub AddToCells(i As Integer) ... End Sub

Gardez à l'esprit qu'avoir des arguments pour les procédures Fonction и Sol en VBA est facultatif. Certaines procédures ne nécessitent pas d'arguments.

Arguments facultatifs

Les procédures VBA peuvent avoir des arguments facultatifs. Ce sont des arguments que l'utilisateur peut spécifier s'il le souhaite, et s'ils sont omis, la procédure utilise les valeurs par défaut pour eux.

Revenant à l'exemple précédent, pour rendre optionnel un argument entier à une fonction, il serait déclaré comme ceci :

Sub AddToCells(Facultatif i As Integer = 0)

Dans ce cas, l'argument entier i la valeur par défaut sera 0.

Il peut y avoir plusieurs arguments facultatifs dans une procédure, qui sont tous répertoriés à la fin de la liste des arguments.

Passer des arguments par valeur et par référence

Les arguments en VBA peuvent être passés à une procédure de deux manières :

• ParVal – passage d'un argument par valeur. Cela signifie que seule la valeur (c'est-à-dire une copie de l'argument) est transmise à la procédure et que, par conséquent, toute modification apportée à l'argument à l'intérieur de la procédure sera perdue lorsque la procédure se terminera.
• Byref – passage d'un argument par référence. Autrement dit, l'adresse réelle de l'emplacement de l'argument en mémoire est transmise à la procédure. Toute modification apportée à un argument à l'intérieur de la procédure sera enregistrée à la sortie de la procédure.

Utiliser des mots-clés ParVal or Byref dans la déclaration de procédure, vous pouvez spécifier comment l'argument est passé à la procédure. Ceci est illustré dans les exemples ci-dessous :

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

N'oubliez pas que les arguments dans VBA sont passés par référence par défaut. En d'autres termes, si des mots-clés ne sont pas utilisés ParVal or Byref, alors l'argument sera passé par référence.

Avant de poursuivre les démarches Fonction и Sol plus en détail, il sera utile de revenir sur les caractéristiques et les différences entre ces deux types de procédures. Voici de brèves discussions sur les procédures VBA Fonction и Sol et des exemples simples sont présentés.

Procédure VBA « Fonction »

L'éditeur VBA reconnaît la procédure Fonctionlorsqu'il rencontre un groupe de commandes entre les instructions d'ouverture et de fermeture suivantes :

Fonction ... Fin de fonction

Comme mentionné précédemment, la procédure Fonction en VBA (par opposition à Sol) renvoie une valeur. Les règles suivantes s'appliquent aux valeurs de retour :

• Le type de données de la valeur de retour doit être déclaré dans l'en-tête de la procédure Fonction.
• La variable qui contient la valeur de retour doit porter le même nom que la procédure Fonction. Cette variable n'a pas besoin d'être déclarée séparément, car elle fait toujours partie intégrante de la procédure. Fonction.

Ceci est bien illustré dans l'exemple suivant.

Exemple de fonction VBA : effectuer une opération mathématique sur 3 nombres

Voici un exemple de code de procédure VBA Fonction, qui prend trois arguments de type Double (nombres à virgule flottante double précision). En conséquence, la procédure renvoie un autre nombre de type Doubleégal à la somme des deux premiers arguments moins le troisième argument :

Fonction SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function

Cette procédure VBA très simple Fonction illustre comment les données sont transmises à une procédure via des arguments. Vous pouvez voir que le type de données renvoyé par la procédure est défini comme Double (les mots disent Comme Double après la liste des arguments). Cet exemple montre également comment le résultat de la procédure Fonction stocké dans une variable portant le même nom que le nom de la procédure.

Appel de la procédure VBA "Fonction"

Si la procédure simple ci-dessus Fonction inséré dans un module de l'éditeur Visual Basic, il peut être appelé à partir d'autres procédures VBA ou utilisé sur une feuille de calcul dans un classeur Excel.

Appelez la procédure VBA "Fonction" à partir d'une autre procédure

Procédure Fonction peut être appelé à partir d'une autre procédure VBA en affectant simplement cette procédure à une variable. L'exemple suivant montre un appel à une procédure Somme moins, qui a été défini ci-dessus.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Appelez la procédure VBA "Fonction" à partir d'une feuille de calcul

Procédure VBA Fonction peut être appelée à partir d'une feuille de calcul Excel de la même manière que toute autre fonction Excel intégrée. Par conséquent, la procédure créée dans l'exemple précédent Fonction - Somme moins peut être appelé en saisissant l'expression suivante dans une cellule de feuille de calcul :

=SumMinus(10, 5, 2)

Procédure VBA "Sous"

L'éditeur VBA comprend qu'il y a une procédure devant lui Sollorsqu'il rencontre un groupe de commandes entre les instructions d'ouverture et de fermeture suivantes :

Sous ... Fin Sous

Procédure VBA "Sub": Exemple 1. Alignement central et changement de taille de police dans une plage de cellules sélectionnée

Prenons un exemple de procédure VBA simple Sol, dont la tâche consiste à modifier la mise en forme de la plage de cellules sélectionnée. Les cellules sont centrées (à la fois verticalement et horizontalement) et la taille de la police est modifiée en fonction de celle spécifiée par l'utilisateur :

Sub Format_Centered_And_Sized(Facultatif iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Cette procédure Sol effectue des actions mais ne renvoie pas de résultat.

Cet exemple utilise également un argument optionnel Taille de police. Si l'argument Taille de police pas passé en procédure Sol, sa valeur par défaut est 10. Cependant, si l'argument Taille de police passé en procédure Sol, la plage de cellules sélectionnée sera définie sur la taille de police spécifiée par l'utilisateur.

Sous-procédure VBA : Exemple 2 : Alignement central et police en gras dans la plage de cellules sélectionnée

La procédure suivante est similaire à celle qui vient d'être décrite, mais cette fois, au lieu de redimensionner, elle applique un style de police gras à la plage de cellules sélectionnée. Ceci est un exemple de procédure Sol, qui ne prend aucun argument :

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Appel de la procédure "Sub" dans Excel VBA

Appelez la procédure VBA "Sub" à partir d'une autre procédure

Pour appeler une procédure VBA Sol à partir d'une autre procédure VBA, vous devez écrire le mot-clé Tél, nom de la procédure Sol et plus loin entre parenthèses sont les arguments de la procédure. Ceci est illustré dans l'exemple ci-dessous :

Sub main() Call Format_Centered_And_Sized(20) End Sub

Si la procédure Format_Centré_Et_Dimensionné a plus d'un argument, ils doivent être séparés par des virgules. Comme ça:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Appelez la procédure VBA "Sub" à partir de la feuille de calcul

Procédure Sol ne peut pas être saisi directement dans une cellule de feuille Excel, comme cela peut être fait avec une procédure Fonctionparce que la procédure Sol ne renvoie pas de valeur. Cependant, les procédures Sol, qui n'ont pas d'arguments et sont déclarés comme public (comme indiqué ci-dessous) seront disponibles pour les utilisateurs de la feuille de travail. Ainsi, si les procédures simples décrites ci-dessus Sol inséré dans un module de Visual Basic Editor, la procédure Format_Centré_Et_Gras sera disponible pour une utilisation dans une feuille de calcul Excel, et la procédure Format_Centré_Et_Dimensionné – ne sera pas disponible car il a des arguments.

Voici un moyen simple d'exécuter (ou d'exécuter) une procédure Sol, accessible depuis la feuille de travail :

• Presse Alt + F8 (appuyez sur la touche autre et tout en le maintenant enfoncé, appuyez sur la touche F8).
• Dans la liste des macros qui s'affiche, sélectionnez celle que vous souhaitez exécuter.
• Presse Courir (Cours)

Pour effectuer une procédure Sol rapidement et facilement, vous pouvez lui attribuer un raccourci clavier. Pour ça:

• Presse Alt + F8.
• Dans la liste des macros qui s'affiche, sélectionnez celle à laquelle vous souhaitez attribuer un raccourci clavier.
• Presse Paramètres (Options) et dans la boîte de dialogue qui s'affiche, entrez le raccourci clavier.
• Presse OK et fermer la boîte de dialogue Macro (Macro).

Attention : Lorsque vous affectez un raccourci clavier à une macro, assurez-vous qu'il n'est pas utilisé par défaut dans Excel (par exemple, Ctrl + C). Si vous sélectionnez un raccourci clavier déjà existant, il sera réaffecté à la macro et, par conséquent, l'utilisateur peut démarrer la macro par accident.

Portée de la procédure VBA

La partie 2 de ce didacticiel traite de la portée des variables et des constantes et du rôle des mots-clés. public и Chef. Ces mots clés peuvent également être utilisés avec les procédures VBA :

Public Sub AddToCells(i As Integer) ... End Sub

Private Sub AddToCells(i As Integer) ... End Sub

Rappelez-vous que si avant de déclarer une procédure VBA Fonction or Sol le mot-clé n'est pas inséré, la propriété par défaut est définie pour la procédure public (c'est-à-dire qu'il sera disponible partout dans ce projet VBA). Cela contraste avec les déclarations de variables, qui sont par défaut Chef.

Sortie anticipée des procédures VBA "Function" et "Sub"

Si vous devez arrêter l'exécution d'une procédure VBA Fonction or Sol, sans attendre sa fin naturelle, alors pour cela il y a des opérateurs Fonction de sortie и Exit Sub. L'utilisation de ces opérateurs est illustrée ci-dessous à l'aide d'une procédure simple à titre d'exemple. FonctionA qui s'attend à recevoir un argument positif pour effectuer d'autres opérations. Si une valeur non positive est transmise à la procédure, aucune autre opération ne peut être effectuée. L'utilisateur doit donc recevoir un message d'erreur et la procédure doit se terminer immédiatement :

Function VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate <= 0 Then MsgBox "Attendu une valeur positive de sVAT_Rate mais reçue " & sVAT_Rate Exit Function End If ... End Function

Veuillez noter qu'avant de terminer la procédure Fonction - Montant de la TVA, une fonction VBA intégrée est insérée dans le code MsgBox, qui affiche une fenêtre contextuelle d'avertissement à l'utilisateur.