Une fonction. nom ([liste_
arguments])
[instructions]
[nom = expression]
[instructions]
[nom = expression]
Fonction finale.
La valeur est toujours affectée au nom de la fonction au moins une fois et, en règle générale, lorsque la fonction a terminé l'exécution. Création d'une fonction utilisateur Commencez par créer un module VBA (vous pouvez également utiliser un module existant). Entrez le mot-clé de fonction, après quoi spécifier le nom de la fonction et la liste de ses arguments (le cas échéant) entre parenthèses. Vous pouvez également déclarer la valeur de type de données qui renvoie une fonction à l'aide du mot clé comme clé (il n'est pas nécessaire de le faire, mais recommandé). Insérez le code VBA qui effectue les actions requises et assurez-vous que la valeur nécessaire est attribuée à une procédure variable correspondant au nom de la fonction au moins une fois dans le corps de la fonction. La fonction se termine par l'opérateur de la fonction finale.
Les noms des fonctions sont soumis aux mêmes règles que. Si vous envisagez d'utiliser la fonction dans la formule de la feuille de travail, assurez-vous que le nom ne contient pas le formulaire de forme cellulaire. En outre, n'attribuez pas de noms correspondant aux noms des fonctions Excel incorporées. Si la zone de fonction n'est pas spécifiée, la valeur par défaut est le public. Les fonctions déclarées comme privées ne sont pas affichées dans la boîte de dialogue. Maître des fonctions.
La fonction peut être causée par l'une des manières suivantes:
- appelez-le d'une autre procédure;
- inclure-le dans la formule de la feuille de travail;
- inclure en format de formatage conditionnel;
- appelez-le dans la fenêtre de débogage VBE ( Immédiat.). Cette méthode est généralement appliquée au stade de test (Fig. 3).
Figure. 3. Appeler une fonction dans la fenêtre de débogage
Contrairement aux procédures, les fonctions ne sont pas affichées dans la boîte de dialogue. Macro (menu Développeur –> Le code –> Macros; ou alt + f8).
Arguments des fonctions
Les arguments peuvent être variables (y compris des tableaux), des constantes, des données ou des expressions symboliques. Certaines fonctions n'ont pas d'arguments. Les fonctions ont des arguments obligatoires et facultatifs.
Fonctions sans arguments
Excel a plusieurs fonctions intégrées qui n'ont pas d'arguments, par exemple, une tDATR, aujourd'hui, TDAT. Il est facile de créer des fonctions utilisateur similaires. Par example:
Utilisateur de fonction ()
"Retourne le nom d'utilisateur
User \u003d Application.Username.
Fonction finale.
Lors de la saisie de la formule \u003d utilisateur (), la cellule renvoie le nom de l'utilisateur actuel (Fig. 4). Remarque: lorsque vous utilisez la fonction sans argument dans la formule de la feuille de travail, vous devez spécifier des crochets vides.
Figure. 4. Formule \u003d utilisateur () renvoie le nom de l'utilisateur actuel
Les fonctions personnalisées se comportent comme des fonctionnalités Excel intégrées. Habituellement, la fonction utilisateur est recalculée lorsqu'elle est nécessaire, c'est-à-dire En cas de modification de l'un des arguments de la fonction. Cependant, vous pouvez effectuer la recalculition des fonctions plus souvent. La fonction est recalculée lorsque toute cellule change, si la procédure est ajoutée par l'opérateur
Application.Volatile VRAI.
La méthode de voltille de l'objet d'application a un argument (vrai ou faux). Si la fonction est mise en surbrillance comme volatil. (variable), il est recalculé chaque fois qu'une cellule cellulaire change. Lorsque vous utilisez le faux argument, la méthode de la tension n'est traduite que lorsque l'un de ses arguments varie à la suite de la recalculition.
Excel a une fonction adhésive intégrée. Mais je n'ai pas aimé trop que des chiffres aléatoires changent chaque fois que la feuille de travail est recalculée. Par conséquent, j'ai développé une fonction qui renvoie des nombres aléatoires qui ne changent pas lors de la recalculition des formules. Pour ce faire, la fonction de RND VBA intégrée a été utilisée:
Fonction staticrand ()
"Retourne un nombre aléatoire, pas une variable lors de la recalculition des formules
Staticrand \u003d rnd ()
Fonction finale.
Les valeurs obtenues à l'aide de cette formule ne changent jamais. Mais l'utilisateur reste la possibilité d'une recalculition forcée de la formule à l'aide de la combinaison de touches
Une fonction d'argument
Figure. 5. Table de la Commission
Il existe plusieurs façons de calculer les commissions. Par exemple, en utilisant la formule suivante (si le volume des ventes est placé dans la cellule D1):
Si (et (d1\u003e \u003d 0; d1<=9999,99);D1*0,08;ЕСЛИ(И(D1>\u003d 10 000; D1<=19999,99);D1*0,105; ЕСЛИ(И(D1>\u003d 20000; D1<=39999,99);D1*0,12;ЕСЛИ(D1>\u003d 40000; D1 * 0.14))))
Cette formule échoue pour plusieurs raisons. Premièrement, c'est difficile, il n'est pas facile de le composer, et dans le futur édition. Deuxièmement, les valeurs sont strictement définies dans la formule, c'est pourquoi il est difficile de changer. Il est beaucoup préférable d'utiliser HDP (fig. 6).
Figure. 6. Utilisation de la fonction PRD pour calculer la commission
Encore mieux (alors vous n'avez pas besoin d'utiliser une table assortie) Créer une fonction personnalisée:
Commission de la fonction (vente)
Const Tier1 \u003d 0.08
Const Tier2 \u003d 0.105
Const Tier3 \u003d 0.12
Const Tier4 \u003d 0,14
"Calcul des commissions des ventes
Sélectionnez les ventes de cas.
Case 0 à 9999.99: Commission \u003d Ventes * Tier1
CAS 10000 à 19999.99: Commission \u003d Ventes * Tier2
CAS 20000 à 39999.99: COMMISSION \u003d VENTE * TIER3
Cas est\u003e \u003d 40000: Commission \u003d Ventes * Tier4
Fin de sélection.
Fonction finale.
Après avoir entré dans le module VBA, cette fonction peut être utilisée dans la formule de la feuille de travail ou appeler d'autres procédures VBA. Lors de la saisie de la formule suivante, le résultat de 3000 sera obtenu dans la cellule:
Commission (B2)
Fonction Doublecell ()
DoubleCell \u003d gamme ("al") * 2
Fonction finale.
Bien que cette fonctionnalité fonctionne, dans certains cas, cela donne un résultat incorrect. La raison en est que le mécanisme d'informatique Excel ne prend pas en compte les plages qui ne sont pas transmises comme des arguments. En conséquence, parfois avant de retourner la fonction, toutes les valeurs associées ne sont pas calculées. Vous devez également écrire la fonction DoubleCell, comme argument dont la valeur de la cellule A1 est transmise.
Fonction DoubleCell (Cell)
DoubleCell \u003d Cell * 2
Fonction finale.
Fonction avec deux arguments
Imaginez que le gestionnaire dans le traitement de ce qui précède introduit une nouvelle politique conçue pour réduire le flux de personnel: le montant total de la commission payable est augmenté de 1% par an, quel employé a travaillé dans la société. Modifiez la commission de la fonction utilisateur afin qu'il faut deux arguments. Le nouvel argument représente le nombre d'années élaborées par l'employé de la société. Appelons cette nouvelle fonction de commission2:
COMMISSION DE FONCTION2 (Ventes, années) comme célibataire
"Calcul de la commission de vente basée sur
"La longueur de l'expérience
Commission2 \u003d Commission (Ventes) + _
(Commission (vente) * ans / 100)
Fonction finale.
Fonction avec l'argument sous la forme d'un tableau
En tant qu'arguments, la fonction peut prendre une ou plusieurs tableaux, traiter cette matrice (tableaux) et renvoyer la seule valeur. La fonction présentée ci-dessous accepte un tableau comme un argument et renvoie le montant de ses éléments.
Fonction Sumarray (liste) comme double
Dim item comme variante
Sumarray \u003d 0.
Pour chaque élément de la liste
Si la feuille de calcul.Snumber (article) puis _
Sumarray \u003d Sumarray + article
Prochain point.
Fonction finale.
La fonction Excel Encixer vérifie si chaque élément est un nombre avant d'ajouter à un entier commun. Ajout de cet opérateur de vérification de données simple élimine les types d'incohérences de type lorsque vous essayez d'effectuer une opération arithmétique sur la chaîne.
Fonction avec des arguments facultatifs
De nombreuses fonctions Excel intégrées ont des arguments facultatifs. Exemple - Fonction LEVSMB qui renvoie des caractères du bord gauche de la ligne. Il a la syntaxe suivante:
Levsimv ( texte, comte_simvolov)
Le premier argument est obligatoire, contrairement à la seconde. Si le deuxième argument n'est pas spécifié, Excel suggère la valeur 1.
Les fonctions personnalisées développées dans VBA peuvent également avoir des arguments facultatifs. Argument optionnel Vous spécifierez si vous entrez le mot-clé facultatif sur le nom de l'argument. Dans la liste des arguments, des arguments facultatifs sont déterminés après tout ce qui est obligatoire. Par example:
Fonction utilisateur2 (en option majuscule en tant que variante)
Si ismissing (majuscule), alors majuscule \u003d false
User2 \u003d Application.Asername.
Si majuscule, User2 \u003d Ucase (User2)
Fonction finale.
Si l'argument est faux ou omis, le nom d'utilisateur est renvoyé sans modification. Si l'argument est vrai, le nom d'utilisateur est renvoyé dans les symboles majuscules (à l'aide de la fonction Ucase VBA). Faites attention au premier opérateur de fonction - il contient la fonction VBA iSmissing, qui détermine la présence d'un argument. Si l'argument est manquant, l'opérateur attribue la variable majuscule à FALSE (par défaut).
Fonction VBA renvoyant un tableau
VBA contient une fonctionnalité très utile intitulée Tableau. Il renvoie une valeur avec un type de données de variante, qui contient un tableau (c'est-à-dire plusieurs valeurs). Si vous n'êtes pas familiarisé avec les formules des tableaux d'Excel, je propose de commencer avec. La formule de massif est entrée dans la cellule après avoir appuyé sur
La fonction MonthNames est un exemple simple d'application de la fonction de réseau dans la fonction utilisateur.
Fonction novices ()
MonthNames \u003d Array ("Janvier", "Février", "Mars", _
"Avril", "mai", "juin", "juillet", "août", _
" Septembre Octobre Novembre Décembre "
Fonction finale.
La fonctionnalité de MontNames renvoie une gamme horizontale de noms de mois. Sur la feuille de calcul, sélectionnez 12 cellules, entrez la formule \u003d Maut Noms () et appuyez sur
Fonction de la valeur d'erreur de retour de la fonction
- xLERRDIVO (pour erreur d'erreur / 0!);
- xlerrna (pour erreur # n / d);
- xlername (pour le nom d'erreur #?);
- xLERNERNULL (EMFED # vide!);
- xLERRNUM (pour le numéro d'erreur #!);
- xlerrref (pour référence d'erreur # référence!);
- xLERRVALUE (pour la moyenne d'erreur signifie!).
Vous trouverez ci-dessous la fonction de suppression reconvertie (voir exemple au début). La conception IF-TEN est utilisée pour effectuer une action alternative dans le cas où l'argument n'est pas un texte. Cette fonctionnalité appelle une fonction Excel Etext qui détermine si l'argument contient le texte. Si la cellule contient du texte, la fonction renvoie un résultat normal. Si la cellule ne contient pas de texte (ou vide), la fonction renvoie une erreur d'erreur!
Fonction SupprimerVowels3 (TXT) comme variante
"Supprime toutes les lettres vocelles de l'argument TXT
"Renvoie une erreur d'erreur signifie!, Si l'argument n'est pas une chaîne
Dim i aussi longtemps
Removevowels3 \u003d ""
Si application.worksheetfunction.istext (txt) alors
Pour i \u003d 1 à len (txt)
Sinon ucase (MID (TXT, I, 1)) comme "" alors
Removevowels3 \u003d supprimerVowels3 & Mid (txt, i, 1)
Fin si
ENSUITE JE.
AUTRE.
SupprimerVowels3 \u003d CVERR (XLERRVALUE)
Fin si
Fonction finale.
Veuillez noter que le type de données a été modifié pour la valeur renvoyée par la fonction. Étant donné que la fonction peut renvoyer quelque chose d'autre, à l'exception de la chaîne, le type de données a été modifié en variante.
Fonction avec un nombre indéfini d'arguments
Il est possible de créer des fonctions utilisateur qui ont un nombre indéfini d'arguments. Appliquez un tableau en tant que dernier argument (ou uniquement) et ajoutez un mot-clé PARAMARRAY avant (Paramarray ne s'applique qu'au dernier argument de la liste des arguments de procédure. Il a toujours un type de données de variante et est toujours un argument facultatif). La fonction suivante renvoie le montant de tous les arguments, qui peuvent agir comme une seule valeur (cellule) et une plage.
Fonction Simpleplesum (arglist Paramarray () comme variante) comme double
Dim cellulaire comme plage
Dim Arg comme variante
Pour chaque argliste
Pour chaque cellule en Arg
Simpleplesum \u003d Simpleplesum + Cell
Next cellule.
Prochain argument
Fonction finale.
Fonctions de débogage
Lorsque vous utilisez la formule de la feuille de travail pour tester les fonctions survenant pendant l'exécution de l'erreur, ne sont pas affichés dans la boîte de dialogue Message familière. La formule renvoie simplement la valeur d'erreur (# moyenne!). Heureusement, cela ne représente pas un problème important lorsque les fonctions de débogage, car il y a toujours plusieurs chemins de dérivation.
- Placez la fonction MsgBox dans des endroits importants pour surveiller les valeurs des variables individuelles.
- Testez la fonction en l'appelant à partir de la procédure et non dans la formule de la feuille de travail. Les erreurs dans le processus d'exécution sont affichées de la manière habituelle.
- Déterminez le point d'arrêt dans la fonction et affichez la fonction étape par étape. Dans ce cas, vous pouvez utiliser tous les outils de débogage standard. Pour ajouter un point d'arrêt, placez le curseur dans l'opérateur dans lequel vous décidez de mettre en pause l'exécution et de sélectionner la commande. Déboguer. –> Basculer le point d'arrêt. (Débogage –> Point d'arrêt) ou cliquez sur
. - Utilisez un ou plusieurs opérateurs temporaires d'impression dans le programme pour afficher les valeurs de la fenêtre. Immédiat. Éditeur VBA. Par exemple, pour surveiller la valeur changeante cyclique, utilisez la méthode suivante:
Figure. 7. Utilisez la fenêtre de débogage pour afficher les résultats lorsque vous effectuez une fonction.
Dans ce cas, les valeurs de deux variables, ch et i sont affichées dans la fenêtre de débogage ( Immédiat.) Chaque fois que l'opérateur de débogage.Print est trouvé dans le programme. Portez le curseur à n'importe quelle procédure de test de lieu () et appuyez sur F5. En figue. 7 montre le résultat pour le cas lorsque la fonction accepte l'argument de Tusconarizona.
Utilisation de la méthode Macrooptions
Vous pouvez utiliser la méthode Macrooptions de l'objet Application, ce qui vous permet d'activer les fonctions des fonctions intégrées de Excel. Cette méthode vous permet de:
- ajouter une description de la fonction (à partir d'Excel 2010;
- spécifiez la catégorie des fonctions;
- ajouter une description des arguments de la fonction.
Sous-décrivant ()
Dim Funcname comme chaîne
Dim Funcdesc en tant que chaîne
Dim Funccat aussi longtemps
Dim arg1Desc en tant que chaîne, arg2desc en tant que chaîne
Funcname \u003d "Draw"
Funcdesc \u003d "le contenu d'une plage aléatoire"
FuncCat \u003d 5 "Liens et tableaux
Arg1Desc \u003d "plage contenant des valeurs"
Arg2desc \u003d "(facultatif) si faux ou absent, _
La fonction RND n'est pas recalculée. "
Arg2Desc \u003d arg2DESC & "Si la fonction VRAI, RND est recalculé"
Arg2desc \u003d arg2desc & "avec tout changement sur une feuille".
Application.macrooptions _
Macro: \u003d funcname, _
Description: \u003d Funcdesc, _
Catégorie: \u003d Funccat, _
Argumentdescriptions: \u003d Array (arg1Desc, arg2Desc)
End sous.
En figue. 8 montre les boîtes de dialogue Maître des fonctions et Fonction des argumentsaprès avoir exécuté la procédure décrypfunction ().
Figure. 8. Type de boîte de dialogue Maître des fonctions et Fonction des arguments Pour la fonction utilisateur
DescriptionFunction () La procédure doit être appelée qu'une seule fois. Après son appel, les informations associées à la fonction sont enregistrées dans le classeur. Mais si vous avez modifié la procédure, répétez son appel.
Si vous ne spécifiez pas la catégorie des fonctionnalités à l'aide de la méthode Macrooptions, la fonction utilisateur de l'utilisateur apparaîtra dans la catégorie. Défini par l'utilisateur boite de dialogue Maître des fonctions. La table (Fig. 9) répertorie les numéros de catégories pouvant être utilisés comme valeurs d'argument de catégorie de la méthode Macrooptions. Notez que certaines de ces catégories (de 10 à 13) ne sont généralement pas affichées dans la boîte de dialogue. Maître des fonctions. Si vous pouvez attribuer l'une des fonctions utilisateur dans une catégorie similaire, elle apparaîtra dans la boîte de dialogue.
Utilisation des add-ons pour stocker des fonctions utilisateur
Si vous le souhaitez, vous pouvez enregistrer des fonctions utilisateur fréquemment utilisées dans le fichier add-on. Le principal avantage de cette approche est le suivant: Les fonctions peuvent être appliquées dans des formules sans le spécificateur de nom de fichier. Supposons que vous ayez une fonction d'utilisateur Zapspaces; Il est stocké dans le fichier myfuncs.xlsm. Pour l'appliquer dans la formule d'un autre ouvrage de travail (autre que MyFunConcs.xlsm), vous devez entrer la formule suivante: \u003d myfuncs.xlsm! Zapspaces (A1: C12).
Si vous créez un complément sur la base du fichier MyFunConcs.xlsm et que ce complément sera chargé dans la session d'opération Excel actuelle, le lien vers le fichier peut être ignoré en entrant la formule suivante: \u003d Zapspaces (A1: C12) . La création de suppléments sera considérée séparément.
Un problème potentiel pouvant survenir dû à l'utilisation de compléments pour stocker des fonctions personnalisées est lié à la dépendance du carnet de travail du fichier add-on. Si vous passez l'employé du classeur, n'oubliez pas de transférer également une copie de la superstructure contenant les fonctions requises.
Utilisation des fonctions de l'API Windows
VBA peut emprunter des méthodes d'autres fichiers qui n'ont rien à voir avec Excel ou VBA, par exemple des fichiers DLL (bibliothèque de liaisons dynamiques - bibliothèque connectée dynamique) utilisée par Windows et d'autres programmes. En conséquence, la VBA apparaît la capacité d'effectuer des opérations qui sans méthodes empruntées dépassent les limites des capacités linguistiques.
Windows API (Interface de programmation d'application - L'interface de programmation d'applications) est un ensemble de fonctions disponibles pour les programmeurs dans l'environnement Windows. Lorsque vous appelez la fonctionnalité Windows à partir du VBA, vous accédez à Windows API. De nombreuses ressources Windows utilisées par les programmeurs Windows peuvent être obtenues à partir des fichiers DLL dans lesquels des programmes et des fonctions sont connectés dans le processus d'exécution du programme et non pendant la compilation.
Avant d'utiliser la fonction Windows API, il doit être annoncé en haut du module logiciel. Si le module logiciel n'est pas un module VBA standard (i.e. module pour Userform, Feuille ou alors Ce livre), la fonction API doit être annoncée comme privée.
Une annonce de fonction API a une certaine difficulté - la fonction doit être déclarée aussi précise que possible. Un opérateur de publicité indique VBA comme suit:
- quelle fonction API utilisez-vous?
- dans quelle bibliothèque il y a une fonction API;
- arguments fonctions API.
Après les annonces, la fonction API peut être utilisée dans le programme VBA.
Considérez un exemple de fonction d'API qui affiche le nom du dossier Windows (à l'aide des instructions Standard VBA, cette tâche est parfois impossible). Pour commencer, nous allons déclarer la fonction API:
Déclarer la fonction ptrsafe getwindowsdirectorya lib "kernel32" _
(Bival LPBuffer sous forme de chaîne, Byval Nsize aussi longtemps)
Cette fonctionnalité ayant deux arguments renvoie le nom du dossier dans lequel le système d'exploitation Windows est installé. Après avoir appelé cette fonction, le dossier Windows sera enregistré dans la variable LPBuffer et la longueur de la chaîne de longueur est dans la variable NSIZE.
L'exemple suivant affiche le résultat dans la fenêtre Messages:
Sous-showwindowsDir ()
Dim WinPath en tant que chaîne * 255
Dim Windir en tant que chaîne
WinPath \u003d espace (255)
WinDr \u003d Gauche (WinPath, GetWindowsDirectorya _
(WinPath, Len (WinPath)))
Msgbox windrir, vbinformation, "répertoire Windows"
End sous.
Pendant le processus d'exécution, la procédure ShowWindowsDir affiche la fenêtre de message avec l'emplacement du dossier Windows.
Parfois, vous devez créer une coquille (wrapper) pour les fonctions API. En d'autres termes, vous créerez votre propre fonction à l'aide de la fonction API. Cette approche simplifie grandement l'utilisation de la fonction API. Vous trouverez ci-dessous un exemple d'une telle fonction VBA:
Fonction WindowsDir () en tant que chaîne
"Nom du dossier Windows
Dim WinPath en tant que chaîne * 255
WinPath \u003d espace (255)
WindowsDir \u003d gauche (WinPath, GetWindowsDirectorya _
(WinPath, Len (WinPath)))
Fonction finale.
Après avoir déclaré cette fonctionnalité, vous pouvez l'appeler à partir d'une autre procédure: MsgBox WindowsDir (). Vous pouvez également utiliser cette fonctionnalité dans la formule de la feuille de travail: \u003d WindowsDir ().
Attention! Ne soyez pas surpris de l'échec du système lors de l'utilisation des fonctions de l'API Windows dans VBA. Enregistrez votre travail à l'avance avant de tester.
Déterminer l'état de la clé
Supposons que vous ayez écrit une macro VBA qui sera effectuée à l'aide du bouton de la barre d'outils. Il est nécessaire que cette macro soit effectuée différemment, si l'utilisateur après avoir cliqué sur le bouton contient la touche
Vous trouverez ci-dessous un programme qui révèle que lorsque la procédure d'événement Button_Click est exécutée, la touche a été enfoncée.
Figure. 10. Vérification des touches Maj, Ctrl et Alt
Le code de fonction VBA peut être trouvé dans le fichier Excel ci-joint.
Utilisation des fonctions de l'API Windows peut être assez compliquée. Dans de nombreux livres de programmation, les opérateurs AD de la fonction API sont répertoriés avec des exemples appropriés. En règle générale, vous pouvez simplement copier les expressions publicitaires et utiliser les fonctions, non attaquées dans leur essence. La plupart des programmeurs VBA à Excel considèrent que l'API fonctionne comme une panacée pour résoudre la plupart des tâches. Sur Internet, vous trouverez des centaines d'exemples assez fiables pouvant être copiés et insérés dans votre propre programme.
Un fichier texte contient des annonces et des constantes d'API Windows. Vous pouvez ouvrir ce fichier dans un éditeur de texte et copier les annonces appropriées sur le module VBA.
Selon les matériaux du livre. - M: dialectique, 2013. - P. 287-323.
Une fonction est un groupe de code réutilisé pouvant être appelé n'importe où dans votre programme. Cela élimine le besoin de réécrire le même code. Cela permet aux programmeurs de diviser un vaste programme à une variété de fonctions petites et gérées.
Outre les fonctions intégrées, VBA vous permet également d'écrire des fonctions utilisateur. Dans cet article, vous apprendrez comment écrire vos propres fonctions dans VBA.
Définition de la fonction
La fonction VBA peut avoir une déclaration de retour en option. Ceci est nécessaire si vous souhaitez renvoyer la valeur de la fonction.
Par exemple, vous pouvez passer deux numéros dans la fonction, puis vous pouvez vous attendre à la fonction de renvoi de votre multiplication à votre appelant.
Noter . La fonction peut renvoyer plusieurs valeurs séparées par une virgule comme une matrice attribuée au nom de la fonction elle-même.
Avant d'utiliser la fonction, nous devons définir cette fonction spécifique. Le moyen le plus courant de déterminer la fonction dans VBA consiste à utiliser le mot-clé de fonction, suivi d'un nom de fonctionnalité unique, et il peut contenir ou ne contenir pas de liste de paramètres et d'un opérateur avec une fonction de fin du mot clé, qui indique la fin. de la fonction. Vous trouverez ci-dessous la syntaxe de base.
Syntaxe
Ajoutez un bouton et ajoutez la fonction suivante.
Fonction FunctionName (liste des paramètres) Instruction 1 Énoncé 2 Déclaration 3 ....... Déclaration N Fonction finale
Exemple
Ajoutez la fonction suivante qui renvoie la zone. Notez que la valeur / valeur peut être renvoyée avec le nom de la fonction elle-même.
Fonction Find_Area (longueur en tant que double, largeur optionnelle comme variante) Si Ismissing (largeur) puis Find_Area \u003d Longueur * Longueur eleLe Find_Area \u003d Longueur * Entrée de largeur Si la fonction finale
Fonction d'appel
Pour appeler une fonction, appelez la fonction à l'aide du nom de la fonction, comme indiqué dans le tir à l'écran suivant.
La sortie de la zone, comme indiqué ci-dessous, sera affichée à l'utilisateur.
Création d'une fonction utilisateur dans VBA Excel, sa syntaxe et ses composants. Description de la fonction utilisateur et de ses arguments. Application.macrooptions Méthode.
Fonction personnalisée - Il s'agit d'une procédure VBA qui produit des calculs spécifiés et renvoie le résultat. Utilisé pour insérer dans les cellules de la feuille de travail Excel ou appeler d'autres procédures.
Annonce de la fonction utilisateur
Fonction de syntaxe
Nom de la fonction ([Liste)) [Opérateurs] [Nom \u003d Expression] [Opérateurs] [Nom \u003d Expression] Fonction finaleComposants de la fonction
- Statique. - Mot-clé optionnel indiquant que les valeurs des variables déclarées dans des fonctions sont enregistrées entre ses défis.
- Nom - Composant obligatoire, nom de la fonction utilisateur.
- Torts - Composant facultatif, une ou plusieurs variables représentant des arguments transmis à la fonction. Les arguments sont entre crochets et séparés par des virgules.
- Les opérateurs - Composant optionnel, bloc d'opérateurs (instructions).
- Nom \u003d expression - Composant optionnel *, attribuant le nom de la fonction d'expression ou de variable. Habituellement, la valeur est attribuée aux fonctions immédiatement avant de la quitter.
- Fonction de sortie - Composant optionnel, sortie forcée de la fonction, s'il a déjà été attribué la valeur finale.
*Un des composants Nom \u003d expression Il devrait être considéré comme obligatoire, car si vous n'attribuez pas les fonctions de la valeur, le sens de son utilisation est perdu.
Visibilité de la fonction
La visibilité de la fonction utilisateur est déterminée par les mots-clés optionnels de public et privé, qui peuvent être répertoriés devant l'opération de fonction (ou statique, en cas d'utilisation).
Mot-clé Publique Indique que la fonction sera disponible pour appeler d'autres procédures dans tous les modules de livres ouverts Excel. Fonction déclarée comme PubliqueAffiche la boîte de dialogue Assistant Fonctions.
Mot-clé Privé Indique que la fonction sera disponible à partir d'autres procédures uniquement dans le module logiciel dans lequel il se trouve. Fonction déclarée comme PrivéN'affiche pas dans la boîte de dialogue Assistant Fonctions, mais elle peut être entrée manuellement dans la cellule.
Si le mot clé public ou privé n'est pas spécifié, la fonction est considérée comme étant déclarée par défaut comme publique.
Pour vous assurer que la fonction utilisateur a toujours été disponible dans tous les livres Excel ouverts, enregistrez-le sans visibilité publicitaire ni en public. Mais si vous envisagez de transférer un classeur avec une fonction utilisateur sur un autre ordinateur, le code de fonction doit figurer dans le module de programme du livre transmis.
Un exemple de fonction utilisateur
Par exemple, nous examinerons la fonction utilisateur la plus simple qui, dans le paragraphe suivant, ajoutera une description. La fonction "décision" est appelée avec un type de données de variante, car sa valeur de retour peut être un nombre et un texte. Arguments de fonction - Deli et diviseur - annoncé également comme variante, comme dans les cellules Excel, il peut y avoir des valeurs numériques de différents types, et la fonction isnumérique vérifie également différents types de données et nécessite que ses arguments soient annoncés comme variant.
Livraison de fonctions (dividimi comme variante, DIMEL comme variante) comme variante si ISnumeric (dividimy) \u003d false ou isnumeric (diviseur) \u003d false puis division \u003d "Erreur: Delimi et Divider doivent être des chiffres!" Fonction de sortie ElseIf division \u003d 0 puis délection \u003d "Erreur: division à zéro!" Fonction de sortie Sinon Delection \u003d extrémité Delime / Deliatel Si la fonction finale
Cette fonctionnalité équivaut aux valeurs des deux cellules de la feuille de travail Excel. Deux conditions de bloc sont vérifiées avant la division:
- Si un séparateur ou un diviseur n'est pas un nombre, la fonction renvoie la valeur: "Erreur: Delimi et Divider doivent être des chiffres!", Et une sortie forcée est faite à partir de la fonction de fonctionnement de la fonction de sortie.
- Si le diviseur est zéro, la fonction renvoie la valeur: "Erreur: division à zéro!", Et une sortie forcée de la fonction par l'opérateur de fonction de sortie est effectuée.
Si les valeurs de validation ne sont pas exécutées (renvoient la valeur de FALSE), les numéros sont effectués et la fonction renvoie le résultat privé (division).
Vous pouvez copier cette fonction sur votre module standard et il sera disponible dans la section "Définition de l'utilisateur" de la maîtrise des fonctions. Essayez d'insérer la fonction "Division" dans la cellule de la feuille de travail à l'aide de l'assistant et de l'expérience avec elle.
Pratique signifiant que la fonction "Division" n'a pas, mais elle démontre bien comme annoncée, les fonctions personnalisées sont créées dans VBA Excel. Et cela aidera à démontrer comment ajouter une description aux fonctions et arguments. Avec une fonction utilisateur à part entière, vous pouvez vous familiariser.
Ajout d'une description de la fonction
Dans la liste des fonctions affichées par l'assistant, il est impossible d'ajouter ou d'éditer leur description. La liste des macros vous permet d'ajouter des procédures de description, mais elle n'a pas de fonctions. Le problème est résolu comme suit:
- Exécutez les assistants fonciers, voyez comment le nom de la fonction souhaité est affiché et fermez-le.
- Ouvrez et entrez le nom de la fonction utilisateur dans le champ "Nom macro".
- Cliquez sur le bouton "Paramètres" et dans la fenêtre qui s'ouvre, ajoutez ou modifie la description.
- Cliquez sur le bouton "OK", puis dans la fenêtre de la liste de macro - "Annuler". Description Prêt!
Ajout d'une description sur l'exemple de la fonction "Division":
Description de la fonction "Décision" dans la boîte de dialogue Master of Function Arguments ":
Utilisation de la fenêtre "Liste de macro", vous pouvez ajouter une description de la fonction elle-même et ses arguments ne peuvent pas être. Mais cela peut être fait en utilisant la méthode Application.MacroOptions.
Application.Macrooptions Méthode
Application.Macrooptions Vous permet d'ajouter une description de la fonction utilisateur, attribuez une combinaison de touches, spécifier une catégorie, ajouter des descriptions d'arguments et ajouter ou modifier d'autres paramètres. Regardons les possibilités de cette méthode utilisée le plus souvent.
Exemple de code avec application.macrooptions:
Sous-nameProgram () Application.macrooptions _ Macro: \u003d "Fonction Nom", _ Description: \u003d "Description de la fonction", _ Catégorie: \u003d "Nom de la catégorie", _ ArgumentDescriptions: \u003d Array ("Description 1", "Description 2", "Description 3", ...) fin
- Nom de la libramme - Tout nom unique adapté aux procédures.
- Nom de nom - Le nom de la fonction dont les paramètres sont ajoutés ou modifiés.
- Description de la fonction - Description de la fonction ajoutée ou varie.
- Nom de la catégorie - Le nom de la catégorie à laquelle la fonction sera placée. Si le paramètre de catégorie est manquant, la fonction utilisateur sera enregistrée dans la section par défaut - "Définition de l'utilisateur". Si le nom de catégorie spécifié correspond à l'un des noms de la liste standard, la fonction sera enregistrée. Si le nom de la catégorie n'est pas répertorié, une nouvelle section sera créée avec ce nom et la fonction sera placée.
- "Description 1", "Description 2", "Description 3", ... - Descriptions d'arguments dans l'ordre comme se situent dans la déclaration de fonction utilisateur.
Ce sous-programme commence une fois, après quoi il peut être supprimé ou utilisé comme modèle pour ajuster les paramètres d'autres fonctions utilisateur.
À l'aide de la méthode Application.MacroOptions, essayons de modifier la description de la fonction utilisateur "Division" et ajoutez des descriptions d'arguments.
Sous-changement () Application.Macrooptions _ Macro: \u003d "Division", _ Description: \u003d "Description de la fonction de délection modifiée par application.macrooptions", _ argumentdescriptions: \u003d tableau ("- toute valeur numérique", valeur numérique "," sauf zéro ") end
Après un lancement ponctuel de ce sous-programme, nous obtenons le résultat suivant:
La méthode Application.MacroOptions ne fonctionne pas, mais ici, vous pouvez trouver une solution. Ajoutez des descriptions aux fonctions utilisateur et de leurs arguments dans un livre Excel ordinaire, puis exportez un module avec des fonctionnalités dans n'importe quel répertoire de disque dur et de là importer des macros à un livre personnel. Toutes les descriptions seront sauvegardées.
La plupart des fonctionnalités de la feuille de travail Microsoft Excel peuvent être utilisées dans le code Visual Basic (liste de ces fonctionnalités: Liste des fonctions de la feuille de calcul disponibles sur Visual Basic)
Une partie des fonctions Excel dans Visual Basic n'est tout simplement pas nécessaire. Par exemple, au lieu d'une fonction Enchaîner. (Rattraper) dans le code de base visuel pour combiner les textes à l'aide de l'opérateur &
.
Appeler la fonction de la feuille de travail de Visual Basic
Dans Visual Basic, la feuille de travail Fonctions (feuille de calcul) Microsoft Excel est disponible via l'objet. Feuille de travail..
Par exemple, ici utilise une fonction Min. Pour déterminer la plus petite valeur dans la gamme de cellules:
Sous-utérité () Dim MyRange comme jeu de plage MyRange \u003d Feuilles de calcul ("Stade1"). Plage ("A1: C10") Réponse \u003d Application.worksheetfunction.min (myRange) MSGBox Répondre
Ici, la variable MyRange est d'abord annoncée sous forme de type d'objet. Varier.Et puis il est attribué à la plage de cellules A1: C10 sur feuille1. La variable de réponse est attribuée le résultat de l'application de la fonction Min. À myRange. La réponse résultante est affichée dans la fenêtre de message.
La gamme de cellules doit être déclarée comme objet Varier.!
Par exemple, dans la formule de la cellule de table Excel
\u003d Match (9, A1: A10,0)
Et dans le code Visual Basic -
Sub FindFirst () myvar \u003d Application.worksheetCunction.Match (9, feuilles de calcul (1) .Range ("A1: A10"), 0) msgbox myvar end sous
Caractéristiques Visual Basic (sans préfixe Feuille de travail.) Ils peuvent avoir le même nom que la fonction Excel, mais pour travailler autrement. Par exemple, application.worksheetfunction.log et journal renvoie différentes valeurs.
Insérer des fonctions de feuille de travail dans la cellule
Pour insérer une fonction dans une cellule, vous devez l'affecter à la propriété. Formule. Objet pertinent Varier..
Par example:
Sub Insertuula () Feuilles de calcul ("Stade1"). Plage ("A1: B3"). Formule \u003d "\u003d rand ()" fin
le résultat de la fonction RAND (renvoie un nombre aléatoire) est attribué à la propriété Formula de la gamme de cellules A1: B3 sur la feuille de feuille de la feuille d'active Excel.
Utilisation des fonctions de la feuille de calcul Microsoft Excel dans Visual Basic Office 2003.)
La fonction écrite sur VBA est un code qui effectue des calculs et renvoie une valeur (ou un tableau de valeurs). En créant une fonction, vous pouvez l'utiliser de trois manières:
- Comme formule sur une feuille où il peut prendre des arguments et retourner des valeurs.
- Dans le cadre de votre sous-programme VBA. Dans la sous-procédure ou dans d'autres fonctions.
- Dans les règles de formatage conditionnel.
Bien que Excel contienne déjà plus de 450 fonctions intégrées, mais elles manquent également périodiquement. Les fonctions parfois intégrées ne peuvent pas faire ce que vous voulez faire. Parfois, pour atteindre le résultat, il est nécessaire de créer une formule énorme et complexe qui n'est pas compréhensible pour les autres. Dans ce cas, vous pouvez créer une fonction personnalisée facile à lire et à utiliser.
Notez que les fonctions utilisateur créées à l'aide de VBA sont généralement beaucoup plus lentes que les fonctions intégrées. Par conséquent, ils conviennent mieux à des situations lorsque vous ne pouvez pas obtenir le résultat en utilisant des fonctions intégrées ou des calculs et la réduction de la productivité n'est pas critique.
Les fonctions intégrées peuvent utiliser non seulement vous, mais également vos collègues. Les fonctions écrites apparaîtront avec d'autres dans la boîte de dialogue Master de fonction. Peut-être êtes-vous effrayé par le processus de création de fonctions, mais je vous dépêche de vous assurer que c'est assez simple.
Quelle est la différence entre la procédure (SUB) de la fonction (fonction)?
La principale différence est que la procédure (SUB) est utilisée pour effectuer un ensemble de commandes et n'est pas destinée à renvoyer la valeur (ou une matrice de valeurs), contrairement à la fonction (fonction).
Pour une démonstration, nous donnons un exemple. Par exemple, il existe un certain nombre de nombres de 1 à 100 et il est nécessaire de séparer même d'impair.
En utilisant la procédure (SUB), vous pouvez, par exemple, passer par les cellules et allouer impair par remplissage. Et la fonction peut être utilisée dans la colonne adjacente et elle retournera la vérité ou réside en fonction de la valeur pair ou non. Ceux. Vous ne pourrez pas changer la couleur du remplissage avec la fonction sur la feuille.
Création d'une simple fonction utilisateur dans VBA
Créons une simple fonction utilisateur sur la VBA et voyons comment tout fonctionne là-bas.
Ce qui suit est un code de fonction qui ne laisse que des chiffres du texte, en disant les valeurs de la lettre.
Numéros de fonctions (texte sous forme de chaîne) TIM IM I comme résultat sombre à long terme que la chaîne pour i \u003d 1 à len (texte) si ISnumeric (Mid (Texte, I, 1)), Résultat \u003d Résultat \u003d Résultat (texte, I, 1 ) Figurines suivantes \u003d CLNG (résultat) Fonction finale
Pour que tout gagné gagné, vous devez insérer ce code dans le module de livre. Si vous ne savez pas comment le faire, commencez à partir de l'article comment enregistrer une macro dans Excel.
Voyons maintenant comment fonctionne la fonction, essayez de l'utiliser sur la feuille:
Avant de désassembler la fonction elle-même, nous notons 2 moments agréables qui sont apparus après la création:
- Il est devenu disponible, comme toute autre fonction intégrée (comment créer une fonction cachée, disons plus tard).
- Lorsque vous avez entré le signe "\u003d" et commencez à entrer le nom de la fonction, Excel affiche toutes les coïncidences et affiche non seulement les fonctions intégrées, mais également sur mesure.
Nous désassemblons le pas à pas
Maintenant, plongons profondément et voyons comment cette fonctionnalité a été créée. La fonction de la ligne commence
Figures de fonction (en tant que texte à chaîne) tant que longs
Mot Une fonction. Parle du début de la fonction, alors son nom va, dans notre cas Nombres.
- Le nom de la fonction ne peut pas contenir d'espaces. De plus, vous ne pouvez pas appeler la fonction s'il est fait face au nom du lien vers la cellule. Par exemple, vous ne pouvez pas nommer la fonction ABC123, car ce nom fait également référence à la cellule de la feuille Excel.
- Vous n'avez pas besoin d'indiquer votre fonction le même nom que la fonction existante. Si vous le faites, Excel donnera la préférence à la fonction intégrée.
- Vous pouvez utiliser le symbole de l'accent si vous voulez diviser les mots. Par example, Suma en cuirivesiveest un nom admissible.
Après le nom entre parenthèses, les arguments de la fonction sont décrits. Par analogie avec des caractéristiques d'excellence intégrées. Dans notre cas, le seul argument est utilisé. Texte. Après le nom de l'argument, nous avons indiqué En tant que chaîneCela signifie que notre argument est une valeur de texte ou une référence à une cellule contenant une valeur de texte. Si vous ne spécifiez pas le type de données, VBA le considérera comme Une variante. (ce qui signifie que vous pouvez utiliser n'importe quel type de données, VBA le déterminera vous-même).
Dernière partie de la première ligne Aussi long. Spécifie le type de données qui renvoie une fonction. Dans notre cas, la fonction retournera les valeurs entières. Ce n'est pas non plus nécessaire.
Les deuxième et troisième rangées de la fonction annoncent des variables internes supplémentaires que nous utiliserons.
Dim i aussi longtemps que la chaîne
Variable jE. Nous serons utilisés pour énumérer des caractères. Une variable rÉSULTAT Stocker le résultat intermédiaire de la fonction.
La fonction de la fonction est de passer par tous les symboles du texte de variable et d'enregistrer uniquement celles qui sont des chiffres. Par conséquent, lancez le cycle de 1 au dernier symbole.
Pour i \u003d 1 à len (texte)
Len est une fonction qui définit le nombre de caractères.
La ligne principale de la fonction consiste à vérifier si le prochain symbole de texte est le numéro et si oui -, puis la sauvegarde dans la variable de résultat
Si ISnumeric (Mid (Texte, I, 1)), Résultat \u003d Résultat & Mid (Texte, I, 1)
Pour cela, nous avons besoin d'une fonction Isnumérique- Elle revient Vrai.si le texte est le numéro et Fauxautrement.
Une fonction MILIEUprend du texte de l'argument jE.symbole (valeur 1 indique que la fonction MILIEUseulement 1 symbole prend) /
Une fonction Suivant - ferme le cycle Pour Tout est clair ici.
Figures \u003d clng (résultat)
Nous convertissons la variable de texte à cette chaîne. rÉSULTATqui contient toutes les figures de l'argument Texte, signification numérique. Et nous disons quel genre de résultat devrait-il faire notre fonction Nombres.
Dernière ligne ligne - Fonction finale.. Il s'agit d'une ligne de code obligatoire qui rapporte VBA que le code de fonction se termine ici.
Le code ci-dessous décrit les différentes parties d'une fonction utilisateur typique créée dans VBA. Dans les articles suivants, nous analyserons ces éléments plus en détail et envisagerons divers moyens d'exécuter la fonction VBA dans Excel.
