Créer VBA fonction de retour Tableau
Comme le titre l’indique, nous allons apprendre à créer une fonction définie par l’utilisateur dans Excel qui renvoie un tableau. Nous avons déjà appris comment lier: / vba-user-defined-functions [créer une fonction définie par l’utilisateur dans VBA]. Alors sans perdre de temps, commençons avec le tutoriel.
Qu’est-ce qu’une fonction de tableau?
Les fonctions de tableau sont les fonctions qui renvoient un tableau lorsqu’elles sont utilisées. Ces fonctions sont utilisées avec les combinaisons de touches CTRL + SHIFT + ENTRÉE et c’est pourquoi nous préférons appeler la fonction de tableau ou les formules comme fonction et formules CSE.
La fonction de tableau Excel est souvent des formules de tableau multicellulaire. Un exemple est le lien: / fonctions-mathématiques-excel-transpose-function [fonction TRANSPOSE].
Création d’une fonction de tableau UDF dans VBA
Donc, le scénario est que je veux simplement renvoyer les 3 premiers nombres pairs en utilisant la fonction ThreeEven ().
Le code ressemblera à ceci.
Function ThreeEven() As Integer() 'define array Dim numbers(2) As Integer 'Assign values to array numbers(0) = 0 numbers(1) = 2 numbers(2) = 4 'return values ThreeEven = numbers End Function
Utilisons cette fonction sur la feuille de calcul.
You can see that, we first select three cells (horizontally, for vertical we have to use two-dimensional array. We have it covered below.). Then we start writing our formula. Then we hit CTRL+SHIFT+ENTER. This fills the selected cells with the array values.
Remarque:
-
En pratique, vous ne saurez pas de combien de cellules vous aurez besoin. Dans ce cas, sélectionnez toujours plus de cellules que la longueur de tableau attendue. La fonction remplira les cellules avec un tableau et des cellules supplémentaires afficheront l’erreur # N / A.
-
Par défaut, cette fonction de tableau renvoie des valeurs dans un tableau horizontal.
Si vous essayez de sélectionner des cellules verticales, toutes les cellules afficheront uniquement la première valeur du tableau.
Comment ça marche?
Pour créer une fonction de tableau, vous devez suivre cette syntaxe.
Function functionName(variables) As returnType() dim resultArray(length) as dataType 'Assign values to array here functionName =resultArray End Function
La déclaration de fonction doit être telle que définie ci-dessus. Cela a déclaré qu’il s’agissait d’une fonction de tableau.
Lors de son utilisation dans la feuille de calcul, vous devez utiliser la combinaison de touches CTRL + MAJ + ENTRÉE. Sinon, il renverra la première valeur du tableau uniquement.
Fonction de tableau VBA pour renvoyer un tableau vertical
Pour que votre fonction de tableau UDF fonctionne verticalement, vous n’avez pas besoin de faire grand-chose. Déclarez simplement les tableaux comme un tableau à deux dimensions. Ensuite, dans la première dimension, ajoutez les valeurs et laissez l’autre dimension vide. Voici comment vous procédez:
Function ThreeEven() As Integer() 'define array Dim numbers(2,0) As Integer 'Assign values to array numbers(0,0) = 0 numbers(1,0) = 2 numbers(2,0) = 4 'return values ThreeEven = numbers End Function
Voici comment vous l’utilisez sur la feuille de calcul.
===
UDF Array Function with Arguments in Excel* In the above examples, we simply printed sum static values on the sheet.
Disons que nous voulons que notre fonction accepte un argument de plage, effectue certaines opérations sur eux et renvoie le tableau résultant.
Exemple Ajouter « -done » à chaque valeur de la plage
Maintenant, je sais que cela peut être fait facilement, mais juste pour vous montrer comment utiliser les fonctions de tableau VBA définies par l’utilisateur pour résoudre les problèmes.
Donc, ici, je veux une fonction de tableau qui prend une plage comme argument et ajoute « -done » à chaque valeur de la plage. Cela peut être fait facilement en utilisant le lien: / tips-concatenate-formula-in-microsoft-excel [fonction de concaténation] `mais nous utiliserons une fonction de tableau ici.
Function CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Create a collection Dim col As New Collection 'Adding values to collection On Error Resume Next For Each v In rng col.Add v Next On Error GoTo 0 'completing operation on each value and adding them to Array ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Next CONCATDone = resulArr End Function
Explication:
La fonction ci-dessus acceptera une plage comme argument et ajoutera « -done » à chaque valeur de la plage.
Vous pouvez voir que nous avons utilisé une collection VBA ici pour contenir les valeurs du tableau, puis nous avons effectué notre opération sur chaque valeur et les avons ajoutées sur un tableau à deux dimensions.
Alors oui les gars, voici comment vous pouvez créer une fonction de tableau VBA personnalisée qui peut renvoyer un tableau. J’espère que c’était suffisamment explicatif. Si vous avez des questions concernant cet article, placez-le dans la section commentaires ci-dessous.
Cliquez sur le lien ci-dessous pour télécharger le fichier de travail:
`link: /wp-content-uploads-2019-12-Create-VBA-Function-to-Return-Array-1.xls [__ Créer une fonction VBA pour renvoyer le tableau]
Articles liés:
link: / vba-user-defined-function [Comment créer une fonction définie par l’utilisateur via VBA] | Apprenez à créer des fonctions définies par l’utilisateur dans Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Using a User Defined Function (UDF) from another workbook using VBA dans Microsoft Excel] `| Utilisez la fonction définie par l’utilisateur dans un autre classeur d’Excel `link: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Renvoie les valeurs d’erreur de l’utilisateur fonctions utilisant VBA dans Microsoft Excel] `| Découvrez comment vous pouvez renvoyer des valeurs d’erreur à partir d’une fonction définie par l’utilisateur
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité]
link: / formulas-and-functions-introduction-of-vlookup-function [La fonction RECHERCHEV dans Excel]
lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016]
link: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]