Table des matières:
- Exemples de fonctions de feuille de calcul
- Saisie de fonctions de feuille de calcul
- En savoir plus sur l'utilisation des fonctions de la feuille de calcul
Vidéo: Créer des fonctions Excel en VBA disponibles partout 2025
Bien que VBA offre un bon choix de fonctions intégrées, vous pourriez ne pas toujours trouver exactement ce dont vous avez besoin. Heureusement, vous pouvez également utiliser la plupart des fonctions de feuille de calcul d'Excel dans vos procédures VBA. Les seules fonctions de feuille de calcul que vous ne pouvez pas utiliser sont celles qui ont une fonction VBA équivalente. Par exemple, vous ne pouvez pas utiliser la fonction RAND d'Excel (qui génère un nombre aléatoire) car VBA a une fonction équivalente: Rnd.
VBA rend les fonctions de feuille de calcul Excel disponibles via l'objet WorksheetFunction, contenu dans l'objet Application. Voici un exemple d'utilisation de la fonction SOMME d'Excel dans une instruction VBA:
Total = Application. WorksheetFunction. SUM (Range ("A1: A12"))
Vous pouvez omettre la partie Application ou la partie WorksheetFunction de l'expression. Dans les deux cas, VBA détermine ce que vous faites. En d'autres termes, ces trois expressions fonctionnent toutes exactement de la même manière:
Total = Application. WorksheetFunction. SUM (plage ("A1: A12")) Total = WorksheetFunction. SUM (Intervalle ("A1: A12")) Total = Application. SUM (Range ("A1: A12"))
Ma préférence personnelle est d'utiliser la partie WorksheetFunction juste pour rendre parfaitement clair que le code utilise une fonction Excel.
Exemples de fonctions de feuille de calcul
Ici, vous découvrez comment utiliser les fonctions de feuille de calcul dans vos expressions VBA.
Recherche de la valeur maximale dans une plage
Voici un exemple qui montre comment utiliser la fonction de feuille de calcul MAX d'Excel dans une procédure VBA. Cette procédure affiche la valeur maximale dans la colonne A de la feuille de calcul active:
Sub ShowMax () Réduit TheMax As Double TheMax = Fonction feuille de travail. MAX (Range ("A: A")) MsgBox TheMax End Sub
Vous pouvez utiliser la fonction MIN pour obtenir la plus petite valeur d'une plage. Et comme vous pouvez vous y attendre, vous pouvez utiliser d'autres fonctions de feuille de calcul d'une manière similaire. Par exemple, vous pouvez utiliser la fonction LARGE pour déterminer la k valeur la plus élevée d'une plage. L'expression suivante illustre ceci:
SecondHighest = WorksheetFunction. LARGE (Range ("A: A"), 2)
Notez que la fonction LARGE utilise deux arguments. Le deuxième argument représente la k ième partie - 2, dans ce cas (la deuxième plus grande valeur).
Calcul d'un paiement hypothécaire
L'exemple suivant utilise la fonction de feuille de travail PMT pour calculer un paiement hypothécaire. Trois variables sont utilisées pour stocker les données transmises à la fonction Pmt en tant qu'arguments.Une boîte de message affiche le paiement calculé.
Sub PmtCalc () Dim IntRate Comme Double Dim LoanAmt As Double Dim Périodes Comme Long IntRate = 0. 0625/12 Périodes = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Periods, -LoanAmt) End Sub
Comme l'indique l'instruction suivante, vous pouvez également insérer les valeurs directement en tant qu'arguments de la fonction:
MsgBox WorksheetFunction. PMT (0625/12, 360, -150000)
Cependant, l'utilisation de variables pour stocker les paramètres facilite la lecture et la modification du code, si nécessaire.
Utilisation d'une fonction de recherche
L'exemple suivant utilise les fonctions InputBox et MsgBox de VBA, ainsi que la fonction VLOOKUP d'Excel. Il demande un numéro de pièce et obtient ensuite le prix à partir d'une table de recherche. Ci-dessous, la plage A1: B13 est nommée PriceList.
Sub GetPrice () Dim DimNum comme Variant Dim Prix en tant que Double PartNum = InputBox ("Entrez le numéro de pièce") Feuilles ("Prix"). Activer le prix = WorksheetFunction. VLOOKUP (PartNum, Range («PriceList»), 2, False) MsgBox PartNum & «costs» & Price End Sub
Voici comment fonctionne la procédure GetPrice:
-
La fonction InputBox de VBA demande à l'utilisateur un numéro de pièce.
-
Le numéro de pièce saisi par l'utilisateur est affecté à la variable PartNum.
-
L'instruction suivante active la feuille de calcul Prix, juste au cas où ce n'est pas déjà la feuille active.
-
Le code utilise la fonction VLOOKUP pour trouver le numéro de pièce dans la table.
-
Notez que les arguments que vous utilisez dans cette instruction sont les mêmes que ceux que vous utiliseriez avec la fonction dans une formule de feuille de calcul. Cette instruction affecte le résultat de la fonction à la variable Price.
-
Le code affiche le prix de la pièce via la fonction MsgBox.
Cette procédure ne gère pas les erreurs et échoue lamentablement si vous entrez un numéro de pièce inexistant. (Essayez-le.) S'il s'agissait d'une application utilisée dans une entreprise, vous voudriez ajouter quelques instructions qui traitent les erreurs plus gracieusement.
Saisie de fonctions de feuille de calcul
Vous ne pouvez pas utiliser la boîte de dialogue Fonction de collage Excel pour insérer une fonction de feuille de calcul dans un module VBA. Au lieu de cela, entrez ces fonctions à l'ancienne: à la main. Cependant, vous pouvez utiliser la boîte de dialogue Coller la fonction pour identifier la fonction que vous voulez utiliser et connaître ses arguments.
Vous pouvez également utiliser l'option Membres de la liste automatique de VBE, qui affiche une liste déroulante de toutes les fonctions de la feuille de calcul. Il suffit de taper Application. WorksheetFunction , suivi d'un point. Ensuite, vous voyez une liste des fonctions que vous pouvez utiliser. Si cette fonctionnalité ne fonctionne pas, choisissez la commande Outils → Options de VBE, cliquez sur l'onglet Editeur et cochez la case en regard de Membres de la liste automatique.
En savoir plus sur l'utilisation des fonctions de la feuille de calcul
Les nouveaux venus dans VBA confondent souvent les fonctions intégrées de VBA et les fonctions de classeur d'Excel. Une bonne règle à retenir est que VBA n'essaie pas de réinventer la roue.Pour l'essentiel, VBA ne duplique pas les fonctions de feuille de calcul Excel.
Pour la plupart des fonctions de feuille de calcul indisponibles en tant que méthodes de l'objet WorksheetFunction, vous pouvez utiliser un opérateur ou une fonction VBA intégrée équivalente. Par exemple, la fonction de feuille de calcul MOD n'est pas disponible dans l'objet WorksheetFunction, car VBA a un équivalent: son opérateur Mod intégré.
Ligne de fond? Si vous devez utiliser une fonction, déterminez d'abord si VBA a quelque chose qui répond à vos besoins. Sinon, vérifiez les fonctions de la feuille de calcul. Si tout le reste échoue, vous pouvez écrire une fonction personnalisée en utilisant VBA.
