1 C’est quoi une procédure ?

Une procédure (ou Procédure Sub) est un morceau de code qui effectue un ensemble d’actions ou de calculs ou une combinaison des deux, qui réside dans un module VBA, auquel vous accédez dans Visual Basic Editor (VBE). Il peut former un élément constitutif dans un programme et peut parfois avoir besoin d’être répété. Il peut être appelé par plusieurs fois dans un programme VBA.

Le programmeur ne doit écrire une procédure qu’une seule fois, et il peut être appelé de n’importe où dans le programme autant de fois que nécessaire. Cependant, elle ne renvoie pas directement de valeur; s’elle effectue un calcul, il n’y a aucun moyen direct de trouver le résultat. Elle peut modifier les valeurs des variables si vous passez des paramètres à l’aide de l’instruction ByRef, et cela est expliqué plus loin dans ce chapitre. La plupart du code VBA est contenu dans des procédures. Sa syntaxe est :

[Private | Public | Friend] [Static] Sub NomDeLaProcedure [(Arguments)]
instructions
Exit Sub
instructions
End Sub

Commentaires

Tout comme les variables, les procédures ont aussi les portées.
■ Private est un mot-clé indiquant que la procédure est privée et sa portée est au niveau du module. Ainsi, les procédures privées peuvent être appelées par d’autres procédures dans le même module mais pas par des procédures dans d’autres modules.
■ Public est un mot-clé indiquant que la procédure est ouverte et disponible pour toutes les autres procédures de tous les modules. Par défaut, les procédures sont sur Public; autrement dit, il n’est pas nécessaire d’utiliser le mot-clé Public, mais les programmeurs l’incluent souvent pour plus de clarté.
■ Friend est un mot-clé, utilisé uniquement dans un module de classe, pour indiquer que la procédure est conviviale et qu’elle concerne le projet.
■ Static est un mot-clé indiquant que les variables de la procédure sont conservées à la fin de la procédure.
■ NomDeLaProcedure est le nom de la procédure qui satisfait aux règles standard de dénomination des variables. Le nom d’une procédure doit décrire ce qu’elle fait. Une bonne règle consiste à utiliser un nom qui comprend un verbe et un nom. Il faut éviter les noms dénués de sens.
■ arguments est une liste de paramètres dont les valeurs sont transmises à la procédure ou renvoyées par la procédure lorsqu’elle est appelée.

■ instructions : c’est un ensemble d’opérateurs exécuté dans la procédure.
■ Exit Sub : c’est un opérateur, conduisant à la sortie immédiate de la procédure. Prenons comme exemple la procédure suivante qui permet de permuter deux valeurs :

Sub PermutionVal()
Dim ContenuCell As String
ContenuCell = Range("A1").Value
Range("A1").Value = Range("B1").Value
Range("B1").Value = ContenuCell
End Sub

Commentaires

■ Tout d’abord, nous déclarons une variable appelée ContenuCell de type String.

■ Nous initialisons la variable ContenuCell avec la valeur de la cellule A1 (ContenuCell = Range (“A1”).Value)

■ Nous pouvons maintenant écrire en toute sécurité la valeur de la cellule B1 dans la cellule A1 (Range (“A1”).Value = Range (“B1”).Value). Nous avons stocké la valeur de la cellule A1 dans ContenuCell afin de ne pas perdre.

■ Enfin, nous écrivons la valeur de la cellule A1  dans la cellule B1 (Range (“B1”). Value = ContenuCell).

Une procédure peut être de n’importe quelle longueur, mais de nombreuses personnes préfèrent éviter de créer des procédures extrêmement longues qui exécutent de nombreuses opérations différentes. Vous trouverez peut-être plus facile d’écrire plusieurs procédures plus petites, chacune avec un seul objectif, puis de concevoir une procédure principale qui appelle ces autres procédures. Cette approche peut faciliter la maintenance de votre code.

REMARQUE                                                                                               

À quelques exceptions près, toutes les instructions VBA d’un module doivent être contenues dans des procédures, les exceptions incluent des déclarations de variables au niveau du module, des définitions de type de données définies par l’utilisateur et quelques autres instructions qui spécifient des options au niveau du module (par exemple, Option Explicit)

2 Créer des fonctions personnalisées

En plus de la procédure (ou procédure Sub), VBA a une procédure Function, ou simplement une fonction. Une fonction (ou procédure Function) est exactement comme une procédure, sauf qu’elle renvoie une valeur. Les fonctions commencent par Function (au lieu de Sub) et se terminent par End Function (au lieu de End Sub). Vous pouvez utiliser ces fonctions dans votre code Visual Basic for Applications (VBA) ou dans des formules de feuille de calcul. Les fonctions renvoient généralement une valeur unique (ou un tableau), tout comme les fonctions de feuille de calcul Excel et les fonctions intégrées de VBA. Comme pour les fonctions intégrées, vos fonctions peuvent utiliser des arguments. Les fonctions sont polyvalentes et vous pouvez les utiliser dans deux situations :

■ Dans le cadre d’une expression dans une procédure VBA

■ Dans les formules que vous créez dans une feuille de calcul

Vous êtes sans aucun doute familier avec les fonctions de feuille de calcul Excel; même les novices savent comment utiliser les fonctions de feuille de calcul les plus courantes, telles que SOMME, MOYENNE et SI. Excel comprend plus de 450 fonctions de feuille de calcul prédéfinies que vous pouvez utiliser dans les formules. En outre, vous pouvez créer des fonctions personnalisées à l’aide de VBA. Avec toutes les fonctions disponibles dans Excel et VBA, vous vous demandez peut-être pourquoi vous auriez besoin de créer de nouvelles fonctions. La réponse est de simplifier votre travail. Avec un peu de planification, les fonctions personnalisées sont utiles dans les formules de feuille de calcul et les procédures VBA.

Une fonction a beaucoup en commun avec une procédure. La syntaxe pour déclarer une fonction est la suivante:

[Public | Private | Friend] [Static] Function NomDeLaFonction _
[(Argument)] [As Type]
instructions
NomDeLaFonction = expression
[Exit Function]
instructions
NomDeLaFonction = expression
End Function

Commentaires 

La syntaxe d’une fonction contient les mêmes éléments qu’une procédure. Ainsi, la même discussion s’applique aux fonctions : la portée d’une fonction détermine si elle peut être appelée par des procédures dans d’autres modules ou dans des feuilles de calcul.

Voici quelques points à garder à l’esprit concernant la portée d’une fonction :

■ Si vous ne déclarez pas la portée d’une fonction, sa portée par défaut est Public.

■ L’instruction Exit Function entraîne la fermeture immédiate de la fonction.

■ Les fonctions déclarées comme Private n’apparaissent pas dans la boîte de dialogue Insérer une fonction d’Excel. Par conséquent, lorsque vous créez une fonction qui ne doit être utilisée que dans une procédure VBA, vous devez la déclarer Private afin que les utilisateurs n’essayent pas de l’utiliser dans une formule.

■ Si votre code VBA doit appeler une fonction définie dans un autre classeur, configurez une référence à l’autre classeur en choisissant la commande Outils / Références de Visual Basic Editor (VBE).

■ Vous n’êtes pas obligé d’établir une référence si la fonction est définie dans un complément. Une telle fonction est disponible pour une utilisation dans tous les classeurs.

Les noms de fonctions doivent respecter les mêmes règles que les noms de variables. Si vous prévoyez d’utiliser votre fonction personnalisée dans une formule de feuille de calcul, faites attention si le nom de la fonction est également une adresse de cellule. Par exemple, si vous utilisez quelque chose comme ABC123 comme nom de fonction, vous ne pouvez pas utiliser la fonction dans une formule de feuille de calcul, car ABC123 est une adresse de cellule. Si vous le faites, Excel affiche une erreur #REF !.

Une fonction est appelée dans une expression par son nom, suivi d’une liste de paramètres entre parenthèses. Pour renvoyer une valeur à partir d’une fonction, attribuez une valeur au nom de la fonction. La macro suivante montre un exemple de fonction qui trouve la somme de deux valeurs.

Public Function FonctionSomme (x As Double, y As Double) As Double
    FonctionSomme = x + y
End Function

Commentaires

■ Le but de cette fonction est de sommer deux valeurs. Au lieu de transmettre les valeurs réelles à la fonction, nous avons rendu la fonction plus flexible en lui fournissant les arguments sous forme de variables. En faisant cela, notre fonction personnalisée somme deux nombres que vous spécifiez.

■ Chacune des variables transmises (x, y) représente une valeur. Vous fournirez les valeurs de chacune de ces variables lorsque vous exécuterez cette fonction.

■ Pour spécifier la valeur que vous souhaitez que la fonction renvoie, saisissez le nom de la fonction suivi du signe égal et de la valeur que vous souhaitez qu’elle renvoie (x + y).

Après avoir écrit votre fonction personnalisée, vous pouvez l’essayer rapidement dans la fenêtre Exécution. Pour afficher la valeur d’une fonction, ouvrez la fenêtre Exécution et tapez un point d’interrogation (?) Suivi du nom de la fonction. N’oubliez pas de mettre les arguments des fonctions entre parenthèses. Par exemple, tapez:

? FonctionSomme (12, 13) et appuyez sur Entrée. La procédure de votre fonction s’exécute en utilisant les valeurs que vous avez transmises pour les arguments x et y. Le résultat de la fonction apparaît sur une ligne ci-dessous:

25

RAPPEL                                                                                                         

Pour accéder à la fenêtre Exécution, dans l’éditeur VBE, cliquez sur Affichage/Fenêtre Exécution. Vous pouvez aussi taper le raccourcis clavier  Ctrl + G

Vous pouvez aussi ajouter une fonction à partir de la boîte de dialogue Ajouter une procédure. Pour accéder à cette boîte, dans VBE, cliquez sur Insertion/Procédure…

Notez que les fonctions que vous créez, encore appelée les UDF  (User Defined Function) ou encore fonction défini par l’utilisateur, deviennent disponibles dans la liste Fonctions disponibles lorsque vous entrez des formules dans une cellule d’une feuille de calcul Excel. Pour utiliser la fonction que vous avez créée, accédez à l’onglet Formules du ruban et dans le groupe de commandes Bibliothèque de fonctions, cliquez sur le bouton Insérer une fonction. Dans la fenêtre Insérer une fonction qui s’ouvre, sélectionnez Personnalisé dans la liste Ou sélectionner une catégorie, puis spécifiez la fonction créée dont vous avez besoin.

2 Exécuter une procédure ou une fonction

Il existe deux principales méthodes d’exécuter une procédure :

■ Vous pouvez appeler la procédure Sub avec la commande Exécuter/Exécuter Sub / UserForm dans le menu VBE. Vous pouvez également exécuter une procédure en appuyant sur la touche de raccourci F5 ou vous pouvez cliquer sur le bouton Exécuter   dans la barre d’outils Standard. Cette méthode suppose que le curseur se trouve dans une procédure. Si le curseur ne se trouve pas dans une procédure, VBE affiche sa boîte de dialogue Macro afin que vous puissiez sélectionner une procédure à exécuter.

■ Vous pouvez appeler une procédure à partir de la boîte de dialogue Macro d’Excel. Pour cela, choisissez la commande Macros de l’onglet Developper comme le montre le figure suivante.

Boîte de dialogue Macro d’Excel

Vous pouvez également appuyer sur Alt + F8 pour accéder à cette boîte de dialogue. Utilisez la liste déroulante Macros dans pour limiter la portée des macros affichées. La boîte de dialogue Macro n’affiche pas les éléments suivants:

– les fonctions

–  les procédures déclarées avec le mot-clé Private

– les procédures nécessitant un ou plusieurs arguments

– les procédures contenues dans les compléments

– et les évènements.

Une fois la macro sélectionnée dans la boite de dialogue Macro, cliquez sur le bouton Exécuté.

NOTE                                                                                                            

Il existe d’autres méthodes pour exécuter une procédure : à partir d’une autre procédure, d’un événement, de la fenêtre Exécution, d’un menu contextuel personnalisé, du ruban, en cliquant sur un bouton dans une feuille de calcul, en cliquant sur une icône ajoutée à la barre d’outils d’accès rapide ou en cliquant sur un objet UserForm.

Bien que vous puissiez exécuter une procédure de plusieurs manières, vous ne pouvez exécuter une fonction que de quatre manières.

■ L’appeler depuis une autre procédure, comme nous le verrons dans la section suivante.

■ L’utiliser dans une formule de feuille de calcul

■ L’utilisez dans une formule pour spécifier la mise en forme conditionnelle

■ L’appeler depuis la fenêtre VBE Exécution

3 Exécuter une procédure ou une fonction à partir d’une autre

L’un des moyens les plus courants d’exécuter une procédure ou une fonction est de l’appeler à partir d’une autre. Si vous êtes novice en programmation, vous vous demandez peut-être pourquoi quelqu’un voudrait appeler une procédure ou une fonction à partir d’une autre. Pourquoi ne pas simplement mettre le code de la procédure ou la fonction dans l’autre et garder les choses simples ? Une des raisons est de clarifier votre code. Plus votre code est simple, plus il est facile à lire, à dépanner et à modifier. Les petites procédures sont plus faciles à déchiffrer et à déboguer. Une autre raison est d’éliminer la redondance. Supposons que vous deviez effectuer une opération à 5 endroits différents de votre procédure. Au lieu de saisir le code 5 fois, vous pouvez écrire une procédure pour effectuer l’opération, puis appeler simplement la procédure 5 fois. De plus, si vous devez effectuer un changement, vous ne le faites qu’une fois au lieu de 5.

Il existe plusieurs façons d’appeler une procédure ou une fonction :
■ Entrer le nom de la procédure ou la fonction, suivi de ses arguments :
Nom <arguments>
–  Nom : C’est le nom de la procédure appelée;

– <arguments> : C’est une liste de paramètres réels, c’est-à-dire une liste de paramètres passés à la procédure. Il doit correspondre en nombre et en type à la liste des paramètres spécifiés dans la procédure lors de sa définition.

■ La deuxième façon d’appeler une procédure ou une fonction consiste à utiliser l’instruction Call :
Call Nom (arguments)
Notez que dans ce cas, la liste des arguments est placée entre parenthèses et séparée par de virgules. Dans la première méthode, les parenthèses ne sont pas utilisées.

Une instruction dans une procédure peut transmettre des valeurs aux procédures appelées à l’aide d’ arguments nommés . Vous pouvez lister les arguments nommés dans n’importe quel ordre. Un argument nommé se compose du nom de l’argument suivi de deux points et d’un signe égal ( : = ), et de la valeur affectée à l’argument.
La procédure suivante appelle la fonction Fonctionsomme () et affiche le résultat du calcul à l’aide de la fonction MsgBox.

Sub DemoFonctionSomme ()
    MsgBox FonctionSomme(12, 13)
End Sub
Public Function FonctionSomme (x As Double, y As Double) As Double
    FonctionSomme = x + y
End Function

Commentaires

■ Lorsque l’instruction FonctionSomme = x + y de fonction FonctionSomme est exécutée, Visual Basic retourne à la procédure DemoFonctionSomme et utilise la fonction MsgBox pour imprimer le résultat.

■ Vous trouverez plus d’informations sur l’utilisation de la fonction MsgBox plus loin dans le chapitre 5.

4 Paramètres et arguments des procédures ou des fonctions

Les arguments d’une procédure ou d’une fonction lui fournissent des données qu’elle utilise dans ses instructions. Les données transmises par un argument peuvent être une variable, une constante, une expression, un tableau ou un objet.

Vous connaissez probablement de nombreuses fonctions de feuille de calcul d’Excel. Les arguments des procédures ou des fonctions sont similaires. Ainsi une procédure ou une fonction peut ne nécessiter aucun argument, un nombre fixe d’arguments, accepter un nombre indéfini d’arguments, peut nécessiter certains arguments et en laissant d’autres facultatifs, avoir tous les arguments facultatifs.

Par exemple, quelques-unes des fonctions de feuille de calcul d’Excel, telles que ALEA  et MAINTENANT, n’utilisent aucun argument. D’autres, comme NB.SI, nécessitent deux arguments. D’autres encore, comme SOMME, peuvent utiliser jusqu’à 255 arguments. Certaines fonctions de feuille de calcul ont des arguments facultatifs. La fonction VPM, par exemple, peut avoir cinq arguments (trois sont obligatoires ; deux sont facultatifs).

La plupart des procédures que vous avez vues jusqu’ici dans ce livre ont été déclarées sans arguments. Ils ont été déclarés avec juste le mot-clé Sub, le nom de la procédure et un ensemble de parenthèses vides. Les parenthèses vides indiquent que la procédure n’accepte pas les arguments.

Chaque nom d’argument fait référence à la valeur que vous fournissez au moment où la fonction est appelée. Lorsqu’une procédure appelle une fonction, elle lui transmet les arguments requis en tant que variables. Une fois que la fonction est exécutée, le résultat est affecté au nom de la fonction. Notez que le nom de la fonction est utilisé comme s’il s’agissait d’une variable. Comme les variables, les fonctions peuvent avoir des types. Le type de votre fonction peut être String, Integer, Long, etc. Pour spécifier le type de données du résultat de votre fonction, ajoutez le mot-clé As et le nom du type de données souhaité à la fin de la ligne de déclaration de la fonction.
La liste des paramètres et arguments est selon la syntaxe suivante :

[Optional] [ByVal | ByRef] [ParamArray] maVariable[()] _
[As Тype][= Valeur par défaut]

■ Optional est un mot-clé indiquant que le paramètre est facultatif. Lors de l’utilisation de cet élément, tous les paramètres suivants contenus dans la liste arguments doivent également être facultatifs et doivent être décrits à l’aide du mot clé Optional. Tous les paramètres décrits comme facultatifs doivent être de type Variant. Le mot clé Optional n’est autorisé pour aucun des paramètres si le mot clé ParamArray est spécifié.

■ ByVal est un mot-clé indiquant que ce paramètre est passé par valeur.

■ ByRef est un mot-clé indiquant que ce paramètre est passé par référence. ByRef est la valeur par défaut dans VBA.

■ ParamArray est un mot clé qui est utilisé uniquement comme dernier élément de la liste des arguments pour indiquer que le paramètre final est un tableau de valeurs Variant décrites comme facultatives. Il ne peut pas être utilisé avec les mots clés ByVal, ByRef ou Optional.
■ Type est le type des valeurs de paramètre transmises à la procédure. Valeurs valides : Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (chaînes de longueur variable uniquement), Object, Variant. Si le mot clé Optional est manquant, un type défini par l’utilisateur ou un type d’objet peut également être spécifié.

■ Valeur par défaut définit la valeur que le paramètre prend par défaut. Si Object est spécifié, la seule valeur par défaut est Nothing.

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

Dans certaines procédures ou fonction, lorsque vous passez des arguments en tant que variables, Visual Basic peut modifier la valeur des variables. Pour vous assurer que la fonction appelée ne modifie pas la valeur des arguments transmis, vous devez faire précéder le nom de l’argument dans la ligne de déclaration de la fonction du mot-clé ByVal. Jetons un coup d’œil dans la macro suivante :

Sub ByValRefProcedure()
    Dim nombre1 As Double, nombre2 As Double
    nombre1 = 15
    nombre2 = 35
    MsgBox LaMoyenne(nombre1, nombre2)
    MsgBox nombre1
    MsgBox nombre2
End Sub

Function LaMoyenne(ByVal nombre1, ByVal nombre2)
    nombre1 = nombre1 + 1
    LaMoyenne = (nombre1 + nombre2) / 2
End Function

Commentaires

■ Pour empêcher la fonction de modifier les valeurs des arguments, utilisez le mot-clé ByVal avant les noms des arguments.

■ La procédure ByValRefProcedure attribue des valeurs à deux variables, puis appelle la fonction LaMoyenne pour calculer et renvoyer la moyenne des nombres stockés dans ces variables.

■ Les arguments de la fonction sont les variables nombre1 et nombre2. Notez que tous les arguments de la fonction sont précédés du mot clé ByVal. Notez également qu’avant le calcul de la moyenne, la fonction LaMoyenne modifie la valeur de la variable nombre1.

■ Dans la fonction, la variable nombre1 est égale à 16 (15 + 1). Par conséquent, lorsque la fonction transmet la moyenne calculée à la procédure ByValRefProcedure, la fonction MsgBox affiche le résultat 25,5 et non 25, comme prévu. Les deux fonctions MsgBox suivantes affichent le contenu de chacune des variables. Les valeurs stockées dans ces variables sont les mêmes que les valeurs d’origine qui leur sont affectées : 15 et 35.

Que se passera-t-il si vous omettez le mot-clé ByVal devant l’argument nombre1 dans la ligne de déclaration de la fonction LaMoyenne? Le résultat de la fonction sera toujours le même, mais le contenu de la variable nombre1 affichée par la fonction MsgBox  est maintenant 16. La fonction LaMoyenne a non seulement renvoyé un résultat inattendu 25,5 au lieu de 25 mais a également modifié les données d’origine stockées dans la variable nombe1. Pour empêcher Visual Basic de modifier définitivement les valeurs fournies à la fonction, utilisez le mot clé ByVal.

Étant donné que l’une des variables passées à une procédure ou une fonction peut être modifiée par la procédure de réception, il est important de savoir comment protéger la valeur d’origine d’une variable. Visual Basic a deux mots clés ByRef et ByVal qui donnent ou refusent l’autorisation de modifier le contenu d’une variable.

Par défaut, Visual Basic transmet les informations à une procédure ou une fonction par référence (mot clé ByRef), en référence aux données d’origine spécifiées dans l’argument de la fonction au moment où la fonction est appelée. Ainsi, si la fonction modifie la valeur de l’argument, la valeur d’origine est modifiée. Vous obtiendrez ce résultat si vous omettez le mot clé ByVal devant l’argument nombre1 dans la ligne de déclaration de la fonction LaMoyenne.

Si vous souhaitez que la fonction modifie la valeur d’origine, vous n’avez pas besoin d’insérer explicitement le mot clé ByRef, car les variables transmises par défaut sont ByRef. Lorsque vous utilisez le mot clé ByVal devant un nom d’argument, Visual Basic transmet l’argument par valeur.

Cela signifie que Visual Basic effectue une copie des données d’origine et transmet cette copie à une fonction. Si la fonction change la valeur d’un argument passé par valeur, les données d’origine ne changent pas – seule la copie change. C’est pourquoi, lorsque la fonction LaMoyenne a modifié la valeur de l’argument nombre1, la valeur d’origine de la variable nombre1 est restée la même.

6 Quelques exemples des fonctions

La construction d’une fonction peut être aussi simple que complexes selon le besoin. Néanmoins, une analyse de quelques exemples peut vous aider à comprendre ce qui se passe.

6.1 Le maximum de deux nombres

L’exemple suivant appelle la fonction ValeurMax (). Elle détermine le maximum des deux paramètres passés et le renvoie au point d’appel.

Sub ExempleFonction()
    Dim x As Integer, y As Integer, z As Integer
    x = 15
    y = 40
    z = ValeurMax(x, y)
    MsgBox z
End Sub

Function ValeurMax(a As Integer, b As Integer) As Integer
    If a > b Then
        ValeurMax = a
    Else
        ValeurMax = b
    End If
End Function

Commentaires

■ Avec l’instruction z = ValeurMax (x, y), les opérations suivantes se succèdent:

– La fonction ValeurMax () est appelée et deux valeurs numériques sont transférées à la fonction.

– Dans la fonction, le maximum de ces deux nombres est déterminé à l’aide d’une condition If… Else et sauvegardé comme valeur de retour de la fonction.

– La fonction se termine et le déroulement du programme revient à la ligne avec l’appel. La fonction se termine et le déroulement du programme revient à la ligne avec l’appel.

– La valeur déterminée est transmise via l’affectation de la variable z.

■ Si l’instruction n’avait été que ValeurMax (x, y), toutes ces étapes auraient eu lieu, à l’exception du transfert vers z. Dans ce cas, appeler la fonction aurait été en vain, une erreur courante chez les débutants en programmation.

La fonction ValeurMax () peut également être utilisée dans la feuille de calcul de ce classeur. Entrez par exemple dans une cellule: = ValeurMax (A1; B1), le résultat attendu apparaît.

6.2 Calcul du dernier jour du mois

La fonction de FinDuMois () ci-dessous calcule le dernier jour du mois d’une année spécifique. Comme on le sait, la valeur 30 ou 31 résulte selon le mois. Pour le mois de février, la valeur 29 correspond à des années bissextiles, sinon la valeur 28. D’abord une procédure de test avec laquelle la fonction FinDuMois () est appelée :

Sub FinDuMoisTest()
    ThisWorkbook.Worksheets("Feuil3").Activate
    Range("C3").Value = _
    FinDuMois(Range("C1").Value, Range("C2").Value)
End Sub

Commentaires

■ La fonction FinDuMois () est appelée avec deux paramètres. Dans cette procédure, les deux valeurs sont l’année et le mois.

Function FinDuMois(Annee As Integer, Mois As Integer)
    If Mois = 2 Then
        If Annee Mod 4 = 0 And Annee Mod 100 <> 0 _
        Or Annee Mod 400 = 0 Then
            FinDuMois = 29
        Else
            FinDuMois = 28
        End If
    ElseIf Mois = 4 Or Mois = 6 Or Mois = 9 Or Mois = 11 Then
        FinDuMois = 30
    Else
        FinDuMois = 31
    End If
End Function

Commentaires

■ Les deux valeurs pour l’année et le mois sont transmises aux paramètres Annee et Mois lors de l’appel de la fonction.

■ S’il s’agit du mois de février, l’opérateur Mod permet de vérifier si l’année est une année bissextile, c’est-à-dire si l’année:

– peut être divisé par 4, mais pas par 100 sans reste

– ou peut être divisé par 400 sans reste

Sinon, la valeur est 30 ou 31, selon le mois.

■ Dans l’expression Annee Mod 4 = 0 And Annee Mod 100 <> 0 Or Annee Mod 400 = 0, l’ordre de priorité suivant s’applique aux opérateurs, en commençant par la priorité la plus élevée:

– Mod opérateur arithmétique

– Opérateurs de comparaison = ou <>

– Opérateur logique And

– Opérateur logique Or

Les parenthèses ne doivent à aucun moment être utilisés.

6.3 Paramètres facultatifs

Le nombre et l’ordre des paramètres dans l’appel et la déclaration d’une procédure (ou fonction) doivent correspondre. Cependant, vous pouvez également utiliser des paramètres facultatifs. Ceux-ci ne doivent pas être spécifiés lors de l’appel.

Les paramètres sont identifiés dans la liste de paramètres par le mot-clé optional, doivent toujours être à la fin de la liste de paramètres et peuvent être initialisés avec une valeur.

Dans l’exemple suivant, la fonction Additionner () est appelée trois fois au total, une fois avec deux paramètres, une fois avec trois paramètres et une fois avec quatre paramètres. Il calcule la somme des paramètres transférés et les renvoie.

Sub ParametreFacultatif()
    Dim a As Double, b As Double, c As Double, d As Double
    a = 3
    b = 10
    c = 15
    d = 7
    MsgBox Additionner (a, b, c, d)
    MsgBox Additionner (a, b, c)
    MsgBox Additionner (a, b)
    ' MsgBox Additionner (a)
End Sub

Function Additionner (x As Double, y As Double, _
    Optional z As Double = 0, Optional q _
    As Double = 0) As Double
    Additionner = x + y + z + q
End Function

Commentaires

■ La fonction Additionner () attend un total de quatre paramètres du type de données Double. Les deux derniers paramètres sont facultatifs. Vous pouvez initialiser des paramètres facultatifs avec une valeur par défaut.

■ Si les deux derniers paramètres ne sont pas spécifiés lors de l’appel de la fonction, ils ont la valeur par défaut 0.

■ Dans le cas de procédures ou de fonctions avec des paramètres optionnels qui doivent effectuer d’autres tâches, d’autres valeurs peuvent être utiles pour l’initialisation.

■ Dans la procédure ParametreFacultatif (), la fonction Additionner () est appelée avec quatre, trois ou deux paramètres. Dans tous les cas, cela conduit avec succès à l’addition et à la sortie des valeurs.

■ Un appel avec un seul paramètre aurait conduit à un message d’erreur, puisque le paramètre y n’est pas optionnel.

6.4 Un nombre quelconque de paramètres

À l’aide du mot-clé ParamArray, vous pouvez formuler une procédure (ou une fonction) à laquelle un nombre quelconque de paramètres peut être passé. ParamArray est incompatible avec Optional, vous devez donc décider laquelle des deux solutions est disponible.

Dans l’exemple suivant, la fonction Moyenne () est appelée trois fois au total, une fois sans paramètres, une fois avec deux paramètres et une fois avec quatre paramètres. Il calcule la valeur moyenne des paramètres transférés et les renvoie.

Sub ExempleParamArray()
    Dim a As Double, b As Double, c As Double, d As Double
    a = 3
    b = 10
    c = 15
    d = 7
    MsgBox Moyenne ()
    MsgBox Moyenne (a, b)
    MsgBox Moyenne (a, b, c, d)
End Sub

Function Moyenne(ParamArray x() As Variant) As Double
    Dim i As Integer
    Dim Somme As Double
    Dim Nombre As Double
    For i = 0 To UBound(x)
    Somme = Somme + x(i)
    Next
    Nombre = UBound(x) + 1
    If Nombre> 0 Then Moyenne = Somme / Nombre
End Function

Commentaires

■ La fonction moyenne () est appelée avec différents nombre de paramètres (0, 2 et 4).

■ Le champ de données de paramètre (ParamArray) x est disponible pour l’enregistrement des paramètres. Il s’agit d’un champ de données et sa taille n’est pas fixe. Ce champ de données doit être du type de données variant.

■ Dans la fonction, les paramètres sont résumés à l’aide d’une boucle. La limite supérieure de la boucle (c’est-à-dire la valeur la plus élevée de l’indice) est déterminée à l’aide de la fonction UBound (). Il faut d’abord déterminer le nombre d’éléments du champ de données de paramètre.

■ Comme on le sait, la moyenne d’une série de nombres est la somme des valeurs divisée par leur nombre. Si la fonction est appelée sans paramètres, UBound () donne la valeur 1. Une division par 0 serait alors effectuée. Il est important d’éviter cela.

■ S’il n’était pas possible de déterminer une valeur pour la fonction dans une fonction, la valeur de départ s’applique ici, comme pour les variables. Dans l’intérêt d’un style de programmation propre, vous devez également éviter cela. Une fonction doit toujours recevoir une valeur explicite au cours de son cours.

Il existe également la fonction LBound (), que vous pouvez utiliser pour déterminer la limite inférieure, c’est-à-dire la valeur la plus basse, pour l’index d’un champ. Les fonctions UBound () et LBound () sont capables de déterminer ces indices pour toutes les dimensions d’un champ de données à une ou plusieurs dimensions. Ils ont un deuxième paramètre optionnel, le numéro de la dimension (1, 2, 3 …). S’il n’est pas spécifié, alors la limite pour la première dimension est déterminée, comme dans la fonction valeur moyenne () donnée ci-dessus.

7 Utilisation de la fonction dans une feuille de calcul

Nous prendre l’exemple d’une fonction appelée VOYELLESSUPPRESSION. Cette fonction supprime toutes les voyelles dans une phrase.

Function VOYELLESSUPPRESSION (text) As String
    ' Transformer toutes les voyelles de l'argument text en majuscule
    Dim i As Long
    VOYELLESSUPPRESSION = "" 
    For i = 1 To Len(text)
    If Not UCase(Mid(text, i, 1)) Like "[AOEIU]" Then
    VOYELLESSUPPRESSION = VOYELLESSUPPRESSION & Mid(text, i, 1)
    End If
    Next i
End Function

Commentaires

■ Cette fonction personnalisée utilise un seul argument (text), placé entre parenthèses. As String définit le type de données de la valeur de retour de la fonction. Excel utilise le type de données Variant si aucun type de données n’est spécifié.

■ La première instruction de la boucle For-Next utilise la fonction Mid de VBA pour renvoyer un seul caractère de la chaîne d’entrée et convertit ce caractère en majuscule par la fonction Ucase. Ce caractère est ensuite comparé à une liste de caractères à l’aide de l’opérateur Like d’Excel. En d’autres termes, la clause If est vraie si le caractère n’est pas A, E, I, O ou U. Dans ce cas, le caractère est ajouté à la variable VOYELLESSUPPRESSION.

■ Lorsque la boucle est terminée, VOYELLESSUPPRESSION se compose de la chaîne d’entrée avec toutes les voyelles supprimées. Cette chaîne est la valeur que la fonction renvoie.

Cette fonction n’est certainement pas la fonction la plus utile, mais elle démontre certains concepts clés liés aux fonctions.

Lorsque vous entrez une formule qui utilise la fonction VOYELLESSUPPRESSION, Excel exécute le code pour obtenir le résultat renvoyé par la fonction. Voici un exemple d’utilisation de la fonction dans une formule :

= VOYELLESSUPPRESSION (A1)

Voir la Figure suivante pour des exemples de cette fonction en action. Les formules sont dans la colonne C et elles utilisent le texte de la colonne B comme arguments. Comme vous pouvez le voir, la fonction renvoie l’argument unique, mais avec les voyelles supprimées.

Utilisation d’une fonction personnalisée dans une formule de feuille de calcul

La fonction VOYELLESSUPPRESSION fonctionne comme n’importe quelle fonction de feuille de calcul intégrée. Vous pouvez l’insérer dans une formule. Vous pouvez également imbriquer des fonctions personnalisées et les combiner avec d’autres éléments dans vos formules.

Dans la boîte de dialogue Insérer une fonction, vos fonctions personnalisées se trouvent, par défaut, dans la catégorie Personnalisées, comme le montre la figure suivante.

Boite de dialogue Insérer une fonction

En plus d’utiliser des fonctions personnalisées dans les formules de feuille de calcul, vous pouvez les utiliser dans d’autres procédures VBA.

Ce que les fonctions de feuille de calcul personnalisées ne peuvent pas faire

Lorsque vous développez des fonctions personnalisées, il est important de comprendre une distinction clé entre les fonctions que vous appelez à partir d’autres procédures VBA et les fonctions que vous utilisez dans les formules de feuille de calcul. Les fonctions utilisées dans les formules de feuille de calcul doivent être passives. Par exemple, le code d’une fonction ne peut pas manipuler des plages ou modifier des éléments sur la feuille de calcul.

Vous pouvez être tenté d’écrire une fonction de feuille de calcul personnalisée qui modifie la mise en forme d’une cellule. Par exemple, il peut être utile d’avoir une formule qui utilise une fonction personnalisée pour modifier la couleur du texte dans une cellule en fonction de la valeur de la cellule. Essayez comme vous le pouvez, cependant une telle fonction est impossible à écrire. Quoi que vous fassiez, la fonction ne changera pas la feuille de calcul. N’oubliez pas qu’une fonction renvoie simplement une valeur. Il ne peut pas effectuer d’actions avec des objets. Cela dit, il faut signaler une exception notable. Vous pouvez modifier le texte d’un commentaire de cellule à l’aide d’une fonction VBA personnalisée.

8 Gestion de la boîte de dialogue Insérer une fonction

La boîte de dialogue Insérer une fonction d’Excel est un outil pratique. Lorsque vous créez une formule de feuille de calcul, cet outil vous permet de sélectionner une fonction de feuille de calcul particulière dans une liste de fonctions. Ces fonctions sont regroupées en différentes catégories pour faciliter la localisation d’une fonction particulière. Lorsque vous sélectionnez une fonction et cliquez sur OK, la boîte de dialogue Arguments de la fonction s’active pour aider à insérer les arguments de la fonction.

La boîte de dialogue Insérer une fonction affiche également vos fonctions de feuille de calcul personnalisées. Par défaut, les fonctions personnalisées sont répertoriées dans la catégorie Personnalisées. La boîte de dialogue Arguments de la fonction vous demande les arguments d’une fonction personnalisée.

La boîte de dialogue Insérer une fonction vous permet de rechercher une fonction par mot-clé. Malheureusement, vous ne pouvez pas utiliser cette fonction de recherche pour localiser les fonctions personnalisées créées dans VBA.

8.1 Utilisation de la méthode MacroOptions

Vous pouvez utiliser la méthode MacroOptions de l’objet Application pour faire apparaître vos fonctions comme des fonctions intégrées ou fonctions définies par l’utilisateur (UDF). Plus précisément, cette méthode vous permet d’effectuer les opérations suivantes :

■ Fournissez une description de la fonction.

■ Spécifiez une catégorie de fonction.

■ Fournissez des descriptions pour les arguments de la fonction.

La syntaxe de la fonction MacroOptions est :

Application MacroOptions (Macro , Description , HasMenu , MenuText , HasShortcutKey , ShortcutKey , Category , StatusBar , HelpContextID , HelpFile , ArgumentDescriptions ),

où:

■  Macro – Le nom de la macro ou le nom de la fonction définie par l’utilisateur.
■  Description – Le texte de description.
■ HasMenu – Cet argument est ignoré.
■ HasShortcutKey – Vous permet d’attribuer une touche de raccourci à votre macro. Si false, aucune touche de raccourci n’est attribuée. Le défaut est faux.
■ ShortcutKey – Il contient la touche de raccourci réelle lorsque HasShortcutKey est vrai.
■ Catégorie – Un entier spécifiant la catégorie de fonction. Vous pouvez également utiliser une chaîne pour une catégorie personnalisée. Si vous utilisez un nom identique à un nom intégré, il est mappé à cette catégorie.
■ StatusBar – Le texte de la barre d’état de la macro.
■ HelpContextID – Un entier qui spécifie l’ID de contexte pour la rubrique d’aide affectée à la macro.
■ HelpFile – Le nom du fichier d’aide contenant la rubrique d’aide.
■ ArgumentDescriptions – (Ajouté en 2010) – Un tableau unidimensionnel qui contient les descriptions des arguments d’une fonction définie par l’utilisateur. Ceux-ci sont affichés dans la boîte de dialogue Arguments de la fonction. Un autre avantage utile de l’utilisation de la méthode MacroOptions est qu’elle permet à Excel de corriger automatiquement la capitalisation de vos fonctions. Par exemple, si vous créez une fonction appelée MaFonction et que vous entrez la formule =mafonction(a), Excel changera automatiquement la formule en =MaFonction(a). Ce comportement fournit un moyen rapide et facile de savoir si vous avez mal orthographié le nom de la fonction. (Si les lettres minuscules ne s’ajustent pas automatiquement, le nom de la fonction est mal orthographié.)

Voici un exemple de procédure qui utilise la méthode MacroOptions pour fournir des informations sur une fonction:

Sub DescriptionFonction ()
Dim strFonction As String   ' nom de la fonction que vous souhaitez enregistrer
Dim strDescription As String   ' description de la fonction elle-même
Dim strCategorie As Long
Dim strArguments() As String ' description des arguments de fonction
    ' LINEAIREINTER : Fonction d'interpolation linéaire
    ReDim strArguments (1 To 3) 'La limite supérieure est le nombre ‘d'arguments dans votre fonction
    strFonction = "LINEAIREINTER"
    strDescription = "Fonction d'interpolation linéaire 2D qui sélectionne automatiquement la plage" & _
              " entre laquelle interpoler en fonction de la valeur KnownX la plus proche " & _
              " de la valeur NewX pour laquelle vous souhaitez interpoler."
strCategorie = 3
strArguments(1) = "Plage à 1 dimension contenant vos valeurs Y connues."
strArguments(2) = "Plage à 1 dimension contenant vos valeurs X connues."
strArguments(3) = "La valeur sur laquelle vous souhaitez effectuer une interpolation linéaire."
Application.MacroOptions Macro:=strFonction, _
                             Description:=strDescription, _
                             ArgumentDescriptions:=strArguments, _
                             Category:=strCategorie
End Sub
Function LINEAIREINTER(ByVal Yconnus As Range, ByVal Xconnus As Range, Xnouveau As Variant) As Double
End Function

Commentaires

■ Vous remarquerez que nous avons défini 3 variables en haut de la macro DescriptionFonction.

– strFonction: Le nom de la fonction que vous souhaitez enregistrer

– strDescription: Une description de ce que fait la fonction

– strArguments : Un tableau contenant la description de chacun des arguments de la fonction.

Les deux premières variables sont explicites. Si votre fonction définie par l’utilisateur a 3 arguments, vous devrez dimensionner la variable strArguments avec 3 éléments et ajouter une description pour chaque argument. Nous le faisons avec la commande ReDim, mais vous pouvez le faire lors de votre déclaration initiale si vous le souhaitez.

La fonction est affectée à la catégorie de fonction 3 (Math et Trigo)

■ Dans cet exemple, nous n’allons pas écrire le code de la fonction LINEAIREINTER car ca n’a aucun objectif dans cette section.

■ Vous devez exécuter la procédure Description Fonction une seule fois. Après cela, les informations affectées à la fonction sont stockées dans le classeur. Vous pouvez également omettre des arguments pour la méthode MacroOptions. Par exemple, si vous n’avez pas besoin que les arguments aient des descriptions, omettez simplement l’argument Argument Descriptions dans le code.

Vous n’êtes pas obligé de placer vos UDF dans de nouvelles catégories. En fait, vous n’avez pas du tout besoin d’inclure l’argument Category lors de l’appel de MacroOptions. Si vous omettez complètement l’argument Category, votre fonction personnalisée apparaîtra dans une nouvelle catégorie intitulée Personnalisées, chacune de ces catégories se voit attribuer un entier auquel vous pouvez vous référer au lieu d’une chaîne de caractère.

Le tableau 7.1 répertorie les numéros de catégorie que vous pouvez utiliser pour l’argument Category de la méthode MacroOptions. Certaines de ces catégories (10–13) ne sont normalement pas affichées dans la boîte de dialogue Insérer une fonction. Si vous attribuez votre fonction à l’une de ces catégories, la catégorie apparaîtra dans la boîte de dialogue.

Tableau : Catégories de fonctions

La figure suivante montre les boîtes de dialogue Insérer une fonction et des arguments de fonction après avoir exécuté cette procédure.

Boite de dialogue Insérer une fonction montrant la fonction LINEAIREINTER

Boite de dialogue des arguments de la fonction LINEAIREINTER

8.2 Ajout manuel d’une description de fonction

Au lieu d’utiliser la méthode Macro Options pour fournir une description de fonction, vous pouvez utiliser la boîte de dialogue Macro.

Si vous ne fournissez pas de description pour votre fonction personnalisée, la boîte de dialogue Insérer une fonction affiche Aucune aide disponible.

Suivez ces étapes pour fournir une description pour une fonction personnalisée :

1. Créez votre fonction dans VBE.
2. Activez Excel en vous assurant que le classeur qui contient la fonction est le classeur actif.
3. Choisissez Developer / Macros (ou appuyez sur Alt+F8). La boîte de dialogue Macro répertorie les procédures disponibles, mais votre fonction ne figurera pas dans la liste.
4. Dans la zone Nom de la macro, tapez le nom de votre fonction.
5. Cliquez sur le bouton Options pour afficher la boîte de dialogue Options de macro.
6. Dans la zone Description, saisissez la description de la fonction. Le champ Touche de raccourci n’est pas pertinent pour les fonctions.
7. Cliquez sur OK, puis sur Annuler.

Après avoir effectué les étapes précédentes, la boîte de dialogue Insérer une fonction affiche la description que vous avez entrée à l’étape 6 lorsque la fonction est sélectionnée.