Modules, fonctions et sous-routines - Analysez des données avec Excel • Bibliothèque • Zeste de Savoir

Nous avons commencé à coder avec les variables et la programmation orientée objet. Maintenant on va continuer pour toujours améliorer nos programmes et permettre d'avoir une application digne de ce nom. Les modules, fonctions et sous-routines sont indispensables aux applications. Elles permettent d'alléger un programme et de le rendre plus rapide. Ne vous fiez pas aux apparences, l'image illustre juste les fonctions, ce n'est pas si compliqué .

C'est en faisant qu'on apprend et l'exemple est plus explicatif que du texte non ? Alors nous allons arrêter l'introduction et passer au vif du sujet !

(Re)présentation
Notre première fonction
L'intérêt d'une sous-routine
Publique ou privée ?
Un peu plus loin...

(Re)présentation

Les modules

Nous allons commencer en présentant ce qu'est un module. Et si je vous dis que vous le savez déjà, vous me croyez ? Il va bien falloir ! Un module représente la feuille dans lequel vous écrivez votre code depuis le début. Ils sont rattachés à des classeurs et le code de ces modules peut ainsi agir sur tout le classeur.

Vous pouvez aussi écrire du code qui est rattaché à une feuille ou au classeur en double cliquant sur les feuilles à gauche dans l'explorateur ou sur ThisWorkbook (qui est le classeur). On reviendra sur ce type de code plus tard.

Vous avez donc deux dossiers, les objets du classeur : Microsoft Excel Objets et les modules dans le classeur Modules. Pour ajouter un module, il suffit de faire un clic droit dans l'explorateur, aller sur insertion et cliquer sur Module.

Vous avez donc un module qui s'ajoute à l'explorateur et qui s'appelle "Module1". C'est dans celui-ci qu'on va écrire le code. Pour exécuter ce code, on a vu que l'on peut utiliser la touche F5. Mais l'utilisateur de votre application ne va pas s'amuser à ouvrir Visual Basic. On va alors affecter les macros à des Contrôles. On en a déjà parlé dans le chapitre sur la POO.

Les procédures codées peuvent aussi être exécutées en faisant appel à elle par le biais d'une autre procédure. Le détail de cette technique va bientôt venir. Les fonctions créées dans VBA peuvent aussi être appelées dans les cellules directement comme on appelle la fonction SOMME. Ça c'est top, vous aller pouvoir créer vos propres fonctions !

Différences entre sous-routine et fonctions

Vous savez qu'il y a deux types de procédures ? Non ? Et bien je vous le dis. Il existe d'un coté les sous-routines, de l'autre les fonctions. Elles sont très semblables mais en fait différentes.

Une sous-routine permet d'exécuter du code mais ne renvoie aucune valeur alors que la fonction renvoie une valeur. On a déjà utilisé des fonctions dans ce cours. C'est le cas de IsNumeric par exemple. Beaucoup de fonctions sont déjà existantes en VBA. Une grande partie des fonctions Excel sont dans VBA, il s'agira donc de les utiliser et non de les coder. Une sous routine se déclare à l'aide Sub et se termine par End Sub. Une fonction se déclare avec le mot clé Function et se termine par End Function. Si vous déclarez une fonction ou sous-routine, VBA inscrit la ligne de fin de code automatiquement, donc pas de soucis pour ça.

Le mot clé Function s'écrie avec un "u" et non un "o". C'est le mot fonction en anglais.

' On déclare une sous-routine
Sub ma_routine()

End Sub

'On déclare une fonction
Function ma_fonction()

End Function

Les fonction doivent être appelées via une variable pour qu'il y ait une valeur de retour.

1	maintenant = Now()

Ce code permet de mettre la date du jour dans une variable appelée maintenant.

Les deux types de procédures acceptent les paramètres. Un paramètre est une valeur que l'on envoie à une fonction ou sous-routine pour qu'elle effectue des calculs par exemple à partir de ces paramètres. Nous allons illustrer ça dans la section suivante avec un exemple.

La différence entre fonction et sous-routine est assez subtile mais doit bien être comprise pour que vous n'essayer pas de retourner une valeur avec une sous-routine par exemple.

Notre première fonction

Comment la créer ?

Nous allons maintenant créer notre première fonction. Elle sera toute simple mais c'est pour que vous compreniez le principe.

La fonction que l'on va créer existe déjà mais on va essayer de la coder quand même. Il s'agit de la fonction Multiplier appelée PRODUIT dans Excel. On va envoyer deux nombres à la fonction qui se charge de les multiplier et nous renvoie le résultat.

Je vous donne la réponse directement :

Function multiplier(nombre1, nombre2)

 multiplier = nombre1*nombre2

End Function

C'est aussi simple que ça ! . Une petite description s'impose. Pour commencer, on déclare la fonction à l'aide du mot-clé Function. On donne un nom à la fonction, ici multiplier puis entre parenthèses les paramètres que prend la fonction. On multiplie deux nombres entre eux ici. Le premier paramètre est le premier nombre (nombre1) et le second paramètre est le second nombre (nombre2). Chaque paramètre est séparé par une virgule. Ensuite on note ce qu'effectue la fonction, ici on multiplie le nombre1 et le nombre2 à l'aide de l'opérateur *. Enfin on ferme la fonction avec End Function.

Comment l'utiliser ?

Une fois votre fonction créée, elle apparait dans la liste déroulante de droite au dessus de la zone de code. Elle peut alors être utilisée dans VBA mais aussi dans Excel.

Dans Excel

Retournez dans un tableau Excel et saisissez dans une cellule :

=multiplier(3;4) Dans une cellule, le séparateur de paramètre est un point-virgule et non une simple virgule. Une fois que vous appuyez sur Entrée, le résultat (12) s'affiche. Elle s'utilise alors comme les autres fonctions.

Dans VBA

Vous allez créer une sous-routine classique avec le nom que vous voulez. J'ai choisi par soucis d'originalité de l'appeler test(). On va alors appeler la fonction, lui transmettre les paramètres et afficher le résultat dans une boite de dialogue. Vous pouvez y arriver seuls mais le code suit quand même pour une correction.

Sub test()
 Dim variable1 As Byte, variable2 As Byte
 variable1 = 3
 variable2 = 5

 resultat = multiplier(variable1, variable2)

 MsgBox resultat
End Sub

Notez que lorsque vous saisissez le mot multiplier et ouvrez la parenthèse, VBA affiche automatiquement le nom des paramètres attendus. En insérant la fonction dans le code, vous forcez un appel à cette fonction en utilisant les paramètres 3 et 5 qui remplacent nombre1 et nombre2 dans votre fonction. Le résultat est retourné dans la variable resultat. Lancez votre macro à l'aide de la touche F5. Que ce passe-t-il ? Le résultat 15 s'affiche dans la boite de dialogue.

L'intérêt d'une sous-routine

Nous avons vu qu'une sous-routine est différente d'une fonction. En ce sens, elle ne retourne rien du tout directement et elle ne peut donc pas être utilisée directement dans une feuille de calculs à la manière d'une fonction. Une sous-routine est un bloc de code que vous allez appeler d'où vous voulez dans votre programme, plusieurs fois peut-être pour vous éviter d'écrire plusieurs fois la même chose. On peut, comme pour une fonction, envoyer des paramètres mais ils seront utilisés dans la sous-routine en interne, dans le code lui-même. Aucune donnée ne peut être retournée.

Créer une sous-routine

On va dans un premier temps créer une sous-routine pour afficher le prénom de l'utilisateur.

Sub affichage_prenom(prenom)

 MsgBox "Votre prénom est : " & prenom

End Sub

Notez que cette sous-routine possède un paramètre pour une variable appelée prenom. Cela est dû au fait que vous allez appeler la sous-routine à partir d'une autre procédure et lui passer une variable.

Une ligne est tracée sous la sous-routine pour préciser où elle se termine. Elle s'ajoute alors à la liste déroulante en haut à droite.

Utiliser la sous-routine

On va maintenant utiliser cette sous-routine dans une procédure. Ce sont des procédures simples pour bien comprendre, vous pourrez par la suite complexifier vos sous-routines et procédures.

La sous-routine peut être appelée comme une fonction en affichant son nom puis les paramètres si elle en prend entre parenthèses. Mais on peut aussi utiliser le mot-clé Call.

1
2
3

' Ces deux lignes font la même chose
 Call ma_sous_routine(parametre)
 ma_sous_routine(parametre)

Dans la suite du cours, je n'utiliserais pas ce mot-clé, il permet de voir que c'est une sous-routine et qu'elle ne renvoie rien. Personnellement, je ne l'utilise pas.

Sub test()

 ' On crée une variable prénom et on lui attribue une valeur
 Dim prenom As String
 prenom = "Baptiste"

 ' On utilise notre sous-routine pour afficher le prénom
 affichage_prenom (prenom)

 ' On crée une variable âge ayant pour valeur 20
 Dim age As Byte
 age = 20

 ' On réutilise la sous-routine pour montrer que malgré le code entre les deux,
 ' la sous-routine continue de faire la même chose.
 affichage_prenom (prenom)

End Sub

A noter que j'utilise toujours la variable prenom dans mon code mais ce n'est pas obligatoire. La variable envoyée à la sous-routine ne doit pas forcément être la même que le nom de la variable utilisée dans la sous-routine. on peut très bien faire ça :

' On utilise toujours la même sous-routine avec la variable prenom

Sub test()

 Dim pseudo As String
 pseudo = "Baptiste"

 affichage_prenom (pseudo)

 Dim age As Byte
 age = 20

 affichage_prenom (pseudo)

End Sub

Vous voyez bien que j'envoie la variable pseudo à la sous-routine mais le code fonctionne de la même façon et affiche la même chose.

Les sous-routines permettent de décomposer un gros code en plusieurs portions. De cette façon, il est plus facile de résoudre un problème en prenant chaque morceaux de code les uns après les autres. C'est un premier pas vers des programmes lisibles.

Publique ou privée ?

VBA vous permet de définir vos fonctions ou sous-routines privées ou publiques à l'aide des mots-clés Private ou Public. Voici un exemple :

1 2	Private Sub sous_routine_privee() End Sub

Euh mes fonctions et sous-routines que j'ai créées, elles sont publiques ou privées ?

Jusque là, vous n'avez créé que des fonctions et sous-routines publiques. Par défaut, VBA les créées en publiques. Le fait qu'une fonction ou sous-routine soit publique permet à cette procédure d'être accessible dans tous les modules de l'application. De plus, elles feront partie de la liste des macros que l'on peut attribuer sur une feuille de calcul.

Vous vous doutez que pour les procédures privées, il suffit de mettre le mot-clé Private devant la déclaration de fonction ou sous-routine. Ainsi, vous allez pouvoir avoir des procédures qui ont le même nom dans des modules différents et qui exécutent un code différent. Ces fonctions privées ne peuvent être utilisées par un utilisateur dans la feuille de calcul.

C'est le même système que pour les variables locales ou globales.

Un peu plus loin...

Nous allons dans ce dernier point du chapitre aller plus loin en étudiant les types de données des arguments (ou paramètres) des fonctions et sous-routines.

Lorsque vous déclarez vos procédures avec des paramètres, par défaut, il sont de types Variant. On peut effectivement attribuer un autre type de données à ces paramètres (les types de données ont déjà été étudiés).

1	Function afficher_resultat(resultat As Integer)

Il y a un avantage à déclarer des types de données : vous introduisez une discipline dans votre code car la procédure recherche un certain type d'informations. Si vous ne spécifiez pas de type et utilisez par conséquent le type par défaut, Variant, alors votre procédure acceptera n'importe quoi, que ce soit un nombre ou une chaîne de caractères. Cela peut avoir des conséquences fâcheuses dans votre procédure si vous attendez une chaîne de caractères et recevez un nombre ou inversement. Si vous spécifiez que le paramètre est une chaîne de caractères, une erreur se produira si ce n'est pas une chaîne de caractères qui est passée en paramètre.

Les arguments optionnels

Vous pouvez rendre certains arguments optionnels en utilisant le mot-clé Optional.

1	Function test(parametre1 As Byte, Optional parametre2 As Byte)

Cette fonction demande un paramètre obligatoire de type Byte qui est parametre1 et un paramètre optionnel de type Byte qui est parametre2. Un paramètre optionnel doit être énoncé APRÈS les paramètres obligatoires.

Encore plus loin…

Et oui on peut toujours aller plus loin ! Dans ce dernier point, on va se rendre compte qu'on peut envoyer à une fonction une variable de deux façon différentes. Soit on envoie la variable elle-même, soit une copie.

Pourquoi envoyer une copie de la variable ?

Lorsque vous envoyez une variable dans une fonction ou sous-routine, vous souhaitez faire des calculs avec ce nombre mais pas forcement modifier ce nombre, dans ce cas on envoie une copie de la variable à la fonction.

Pour envoyer une copie de cette variable, on va utiliser le mot-clé ByVal. De ce fait, la valeur réelle de la variable n'est pas modifiée. Si on utilise le mot-clé ByRef, on envoie la référence de la variable (l'adresse), la sous-routine peut alors accéder à la vraie valeur et la modifier. Dans ce cas, la variable est modifiée même dans la procédure qui appelle la sous-routine ou la fonction. Par défaut, les arguments sont passés par référence.

Nous allons faire un test :

' Passe la référence en argument.
Sub MaProcedure_1(ByRef x As Integer)
 x = x * 2
End Sub

' Passe la valeur en argument.
Sub MaProcedure_2(ByVal y As Integer)
 y = y * 2
End Sub

' ByRef est la valeur par défaut si non spécifiée.
Sub MaProcedure_3(z As Integer)
 z = z * 2
End Sub

Sub Test()

 ' On utilise une variable pour la faire changer
 Dim nombre_a_change As Integer
 nombre_a_change = 50

 ' On applique à notre variable la première sous-routine
 MaProcedure_1 nombre_a_change
 MsgBox nombre_a_change

 ' On applique à notre variable la deuxième sous-routine
 MaProcedure_2 nombre_a_change
 MsgBox nombre_a_change

 ' On applique à notre variable la troisième sous-routine
 MaProcedure_3 nombre_a_change
 MsgBox nombre_a_change
End Sub

Dans un premier temps, on applique à notre variable la première procédure qui multiplie par 2 la valeur. Étant donné que la variable est passée grâce à la référence, la variable est modifiée dans la sous-routine mais aussi dans la procédure qui appelait la sous-routine et donc on affiche le double de le valeur de départ, c'est à dire 100.

Dans un second temps, on applique la deuxième sous-routine. Dans celle-ci, on envoie une copie de la valeur (on envoie donc la valeur 100). Elle fait le même calcul mais avec une copie. De ce fait, la procédure qui appelait la sous-routine n'affiche pas le double mais 100. En effet, c'est la copie qui a été multipliée par 2 et non la valeur réelle de la variable.

Enfin, dans la troisième, aucun mot-clé n'est utilisé. Par défaut nous envoyons la référence et donc la vraie valeur à la sous-routine. Celle-ci effectue donc le même calcul (multiplication par 2) et modifie la variable de la procédure appelante. Le résultat affiché est donc 200.

C'est bon, c'est fini ! Euh, je parle de ce chapitre, pas du tutoriel, nous en sommes encore assez loin d'ailleurs Les fonctions de ce chapitre ne sont pas très développées mais c'est en pratiquant que vous aller complexifier vos fonctions et sous-routines. La lecture des chapitres suivants vous aidera à faire tout ce que vous voulez facilement.