Dans ce tutoriel, nous allons découvrir la fonction Excel VBA {vide} 1) Qu’est-ce que Visual Basic dans Excel?

{vide} 2) Comment utiliser VBA dans Excel?

{vide} 3) Comment créer une fonction définie par l’utilisateur?

{vide} 4) Comment écrire une macro?

Comment écrire VBAcode Excel fournit à l’utilisateur une grande collection de fonctions prêtes à l’emploi, plus que suffisantes pour satisfaire l’utilisateur moyen. De nombreux autres peuvent être ajoutés en installant les différents compléments disponibles. La plupart des calculs peuvent être réalisés avec ce qui est fourni, mais il ne faut pas longtemps avant que vous souhaitiez qu’une fonction ait fait un travail particulier et que vous ne trouviez rien de convenable dans la liste. Vous avez besoin d’un UDF. Une UDF (User Defined Function) est simplement une fonction que vous créez vous-même avec VBA. Les fonctions UDF sont souvent appelées «fonctions personnalisées». Un UDF peut rester dans un module de code attaché à un classeur, auquel cas il sera toujours disponible lorsque ce classeur est ouvert. Vous pouvez également créer votre propre complément contenant une ou plusieurs fonctions que vous pouvez installer dans Excel comme un complément commercial. Les UDF sont également accessibles par des modules de code. Souvent, les UDF sont créés par les développeurs pour travailler uniquement dans le code d’une procédure VBA et l’utilisateur n’est jamais conscient de leur existence. Comme toute fonction, l’UDF peut être aussi simple ou aussi complexe que vous le souhaitez. Commençons par un simple …​

Une fonction pour calculer l’aire d’un rectangle Oui, je sais que vous pourriez le faire dans votre tête! Le concept est très simple, vous pouvez donc vous concentrer sur la technique. Supposons que vous ayez besoin d’une fonction pour calculer l’aire d’un rectangle. Vous parcourez la collection de fonctions d’Excel, mais il n’y en a pas une qui convient. Voici le calcul à faire:

AREA = LENGTH x WIDTH Ouvrez un nouveau classeur, puis ouvrez Visual Basic Editor (Outils> Macro> Visual Basic Editor ou ALT + F11).

Vous aurez besoin d’un module dans lequel écrire votre fonction, alors choisissez Insertion> Module. Dans le module vide, tapez: Function Area et appuyez sur ENTRÉE.L’éditeur Visual Basic complète la ligne pour vous et ajoute une ligne de fonction de fin comme si vous créiez un sous-programme.

Placez votre curseur entre les crochets après « Zone ». Si vous vous êtes déjà demandé à quoi servent les brackets, vous êtes sur le point de le découvrir! Nous allons spécifier les « arguments » que prendra notre fonction (un argument est une information nécessaire pour faire le calcul). Tapez Length comme double, Width comme double et cliquez dans la ligne vide en dessous. Notez qu’au fur et à mesure que vous tapez, une boîte de défilement apparaît indiquant toutes les choses appropriées à ce que vous tapez.

Function Area()

End Function

Cette fonctionnalité est appelée Liste automatique des membres. S’il n’apparaît pas, il est désactivé (activez-le dans Outils> Options> Éditeur) ou vous avez peut-être fait une erreur de frappe plus tôt. C’est une vérification très utile de votre syntaxe. Trouvez l’élément dont vous avez besoin et double-cliquez dessus pour l’insérer dans votre code. Vous pouvez l’ignorer et taper simplement si vous le souhaitez. Votre code ressemble maintenant à ceci …​

img2

La déclaration du type de données des arguments n’est pas obligatoire mais a du sens. Vous auriez pu taper Length, Width et le laisser tel quel, mais avertir Excel du type de données à attendre aide votre code à s’exécuter plus rapidement et détecte les erreurs d’entrée. Le type de données double fait référence au nombre (qui peut être très grand) et autorise les fractions. Maintenant, pour le calcul lui-même. Dans la ligne vide, appuyez d’abord sur la touche TAB pour indenter votre code (le rendant plus facile à lire) et tapez Area = Length * Width. Voici le code complété …​

Function Area(Length As Double, Width As Double)

End Function

Vous remarquerez qu’une autre des fonctionnalités d’aide de Visual Basic Editor apparaît lorsque vous tapez, Auto Quick Info …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Ce n’est pas pertinent ici. Son but est de vous aider à écrire des fonctions en VBA, en vous indiquant quels arguments sont nécessaires. Vous pouvez tester votre fonction tout de suite. Basculez vers la fenêtre Excel et entrez des chiffres pour la longueur et la largeur dans des cellules séparées. Dans une troisième cellule, entrez votre fonction comme s’il s’agissait de l’une des fonctions intégrées. Dans cet exemple, la cellule A1 contient la longueur (17) et la cellule B1 la largeur (6,5). En C1, j’ai tapé _ = aire (A1, B1) _ et la nouvelle fonction a calculé l’aire (110,5) …​

img31

Parfois, les arguments d’une fonction peuvent être facultatifs. Dans cet exemple, nous pourrions rendre l’argument Width facultatif. Supposons que le rectangle soit un carré de longueur et de largeur égales. Pour éviter que l’utilisateur n’ait à entrer deux arguments, nous pourrions les laisser entrer uniquement la longueur et demander à la fonction d’utiliser cette valeur deux fois (c’est-à-dire multiplier la longueur x la longueur). Ainsi, la fonction sait quand elle peut le faire, nous devons inclure une instruction IF pour l’aider à décider. Changez le code pour qu’il ressemble à ceci …​

img3

Notez que le type de données pour la largeur a été modifié en Variant pour permettre des valeurs nulles. La fonction permet désormais à l’utilisateur de n’entrer qu’un seul argument, par ex. _ = area (A1) _. L’instruction IF de la fonction vérifie si l’argument Width a été fourni et calcule en conséquence …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Une fonction pour calculer la consommation de carburant J’aime garder un contrôle sur la consommation de carburant de ma voiture, donc lorsque j’achète du carburant, je note le kilométrage et la quantité de carburant nécessaire pour remplir le réservoir.

img4

Ici, au Royaume-Uni, le carburant est vendu en litres. Le kilomètre de la voiture (OK, donc c’est un odomètre) enregistre la distance en miles. Et parce que je suis trop vieux et stupide pour changer, je ne comprends que MPG (miles par gallon). Maintenant, si vous pensez que tout cela est un peu triste, que diriez-vous de cela. Quand je rentre à la maison, j’ouvre Excel et entre les données dans une feuille de calcul qui calcule le MPG pour moi et trace les performances de la voiture. Le calcul est le nombre de miles parcourus par la voiture depuis le dernier plein, divisé par le nombre de gallons de carburant utilisés …​

MPG = (MILLES CE REMPLISSAGE – MILLES DERNIER REMPLISSAGE) / GALLONS DE CARBURANT mais parce que le carburant est en litres et qu’il y a 4,546 litres dans un gallon ..

MPG = (MILES CE REMPLISSAGE – MILES DERNIER REMPLISSAGE) / LITRES DE CARBURANT x 4,546 Voici comment j’ai écrit la fonction …​

et voici à quoi ça ressemble sur la feuille de calcul …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

Toutes les fonctions n’effectuent pas de calculs mathématiques. En voici un qui fournit des informations …​

img5

Une fonction qui donne le nom du jour On me demande souvent s’il existe une fonction de date qui donne le jour de la semaine sous forme de texte (par exemple, lundi). La réponse est non, mais il est assez facile d’en créer un. (Addendum: ai-je dit non? Consultez la note ci-dessous pour voir la fonction que j’ai oubliée!). Excel a la fonction WEEKDAY, qui renvoie le jour de la semaine sous la forme d’un nombre de 1 à 7. Vous pouvez choisir quel jour est 1 si vous n’aimez pas la valeur par défaut (dimanche). Dans l’exemple ci-dessous, la fonction renvoie « 5 » qui, je le sais, signifie « jeudi ».

Mais je ne veux pas voir de numéro, je veux voir « jeudi ». Je pourrais modifier le calcul en ajoutant une fonction RECHERCHEV qui faisait référence à une table contenant quelque part une liste de nombres et une liste correspondante de noms de jours. Ou je pourrais avoir le tout autonome avec plusieurs instructions IF imbriquées. Trop compliqué! La réponse est une fonction personnalisée …​

img6

J’ai appelé ma fonction « DayName » et elle prend un seul argument, que j’appelle « InputDate » qui (bien sûr) doit être une date. Voici comment ça marche …​

Function DayName(InputDate As Date)

Dim DayNumber As Integer

DayNumber = Weekday(InputDate, vbSunday)

Select Case DayNumber

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

  • La première ligne de la fonction déclare une variable que j’ai appelée « DayNumber » qui sera un entier (c’est-à-dire un nombre entier).

  • La ligne suivante de la fonction attribue une valeur à cette variable à l’aide de la fonction WEEKDAY d’Excel. La valeur sera un nombre compris entre 1 et 7.

Bien que la valeur par défaut soit 1 = dimanche, je l’ai quand même inclus pour plus de clarté.

Enfin, une instruction Case * examine la valeur de la variable et renvoie le morceau de texte approprié.

Voici à quoi ça ressemble sur la feuille de calcul …​

=== === Accès à vos fonctions personnalisées Si un classeur est associé à un module de code VBA contenant des fonctions personnalisées, ces fonctions peuvent être facilement abordées dans le même classeur, comme illustré dans les exemples ci-dessus. Vous utilisez le nom de la fonction comme s’il s’agissait de l’une des fonctions intégrées d’Excel.

img7

Vous pouvez également trouver les fonctions répertoriées dans l’assistant de fonction (parfois appelé outil Coller une fonction). Utilisez l’assistant pour insérer une fonction de la manière habituelle (Insertion> Fonction).

Faites défiler la liste des catégories de fonctions pour trouver Défini par l’utilisateur et sélectionnez-le pour voir une liste des UDF disponibles …​

Vous pouvez voir que les fonctions définies par l’utilisateur n’ont pas de description autre que le message inutile « Aucune aide disponible », mais vous pouvez ajouter une brève description …​

img8

img9

Assurez-vous que vous êtes dans le classeur qui contient les fonctions. Allez dans Outils> Macro> Macros. Vous ne verrez pas vos fonctions répertoriées ici, mais Excel les connaît! Dans la zone Nom de la macro en haut de la boîte de dialogue, saisissez le nom de la fonction, puis cliquez sur le bouton Options de la boîte de dialogue. Si le bouton est grisé, soit vous avez mal orthographié le nom de la fonction, soit vous vous trouvez dans le mauvais classeur, soit il n’existe pas! Cela ouvre une autre boîte de dialogue dans laquelle vous pouvez entrer une brève description de la fonction. Cliquez sur OK pour enregistrer la description et (voici le peu de confusion) cliquez sur Annuler pour fermer la boîte de dialogue Macro. N’oubliez pas d’enregistrer le classeur contenant la fonction. La prochaine fois que vous irez dans l’assistant de fonction, votre UDF aura une description …​

À l’instar des macros, les fonctions définies par l’utilisateur peuvent être utilisées dans n’importe quel autre classeur tant que le classeur les contenant est ouvert. Cependant, ce n’est pas une bonne pratique de le faire. Entrer la fonction dans un autre classeur n’est pas simple. Vous devez ajouter le nom de son classeur hôte au nom de la fonction. Ce n’est pas difficile si vous comptez sur l’assistant de fonction, mais maladroit à écrire manuellement. L’assistant de fonction affiche les noms complets de tous les UDF dans d’autres classeurs …​

img10

Si vous ouvrez le classeur dans lequel vous avez utilisé la fonction à un moment où le classeur contenant la fonction est fermé, vous verrez un message d’erreur dans la cellule dans laquelle vous avez utilisé la fonction. Excel l’a oublié! Ouvrez le classeur hôte de la fonction, recalculez et tout va bien à nouveau. Heureusement, il y a une meilleure façon.

img11

Si vous souhaitez écrire des fonctions définies par l’utilisateur à utiliser dans plusieurs classeurs, la meilleure méthode consiste à créer un complément Excel. Découvrez comment faire cela dans le didacticiel Créer un complément Excel.

Addendum Je devrais vraiment savoir mieux! Jamais, jamais, ne dites jamais! Après vous avoir dit qu’il n’y a pas de fonction qui donne le nom du jour, je me souviens maintenant de celle qui le peut. Regardez cet exemple …​

La fonction TEXT renvoie la valeur d’une cellule sous forme de texte dans un format numérique spécifique. Donc, dans l’exemple, j’aurais pu choisir = TEXT (A1, « ddd ») pour renvoyer « Thu », = TEXT (A1, « mmmm ») pour renvoyer « September » etc. L’aide d’Excel a quelques autres exemples d’utilisation cette fonction.

img12

_ _Si vous avez aimé nos blogs, partagez-les avec vos amis sur Facebook. Et vous pouvez aussi 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] __