Crea una funzione VBA per tornare Array
Come suggerisce il titolo, impareremo come creare una funzione definita dall’utente in Excel che restituisce un array. Abbiamo già imparato come link: / vba-user-defined-functions [creare una funzione definita dall’utente in VBA]. Quindi, senza perdere tempo, iniziamo con il tutorial.
Cos’è una funzione array?
Le funzioni array sono le funzioni che restituiscono un array quando vengono utilizzate. Queste funzioni vengono utilizzate con le combinazioni di tasti CTRL + MAIUSC + INVIO ed è per questo che preferiamo chiamare la funzione di matrice o le formule come funzioni e formule CSE.
La funzione di matrice di Excel è spesso formule di matrice multicella. Un esempio è link: / funzioni-matematiche-excel-trasposizione-funzione [funzione TRANSPOSE].
Creazione di una funzione array UDF in VBA
Quindi, lo scenario è che voglio solo restituire i primi 3 numeri pari usando la funzione ThreeEven ().
Il codice sarà simile a questo.
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
Usiamo questa funzione nel foglio di lavoro.
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.
Nota:
-
In pratica, non saprai di quante celle avrai bisogno. In tal caso selezionare sempre più celle della lunghezza prevista della matrice. La funzione riempirà le celle con un array e le celle extra mostreranno l’errore # N / D.
-
Per impostazione predefinita, questa funzione di matrice restituisce i valori in una matrice orizzontale.
Se provi a selezionare celle verticali, tutte le celle mostreranno solo il primo valore dell’array.
Come funziona?
Per creare una funzione array devi seguire questa sintassi.
Function functionName(variables) As returnType() dim resultArray(length) as dataType 'Assign values to array here functionName =resultArray End Function
La dichiarazione della funzione deve essere come definita sopra. Questo ha dichiarato che si tratta di una funzione array.
Durante l’utilizzo nel foglio di lavoro, è necessario utilizzare la combinazione di tasti CTRL + MAIUSC + INVIO. In caso contrario, restituirà solo il primo valore dell’array.
Funzione array VBA per restituire array verticale
Per far funzionare verticalmente la funzione array UDF, non è necessario fare molto. Dichiara semplicemente gli array come array bidimensionali. Quindi nella prima dimensione aggiungi i valori e lascia vuota l’altra dimensione. Ecco come lo fai:
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
Ecco come lo usi nel foglio di lavoro.
===
UDF Array Function with Arguments in Excel* In the above examples, we simply printed sum static values on the sheet.
Diciamo che vogliamo che la nostra funzione accetti un argomento di intervallo, esegua alcune operazioni su di esso e restituisca l’array risultante.
Esempio Aggiungi “-done” a ogni valore nell’intervallo
Ora, so che questo può essere fatto facilmente, ma solo per mostrarti come puoi usare le funzioni di array VBA definite dall’utente per risolvere i problemi.
Quindi, qui voglio una funzione array che prenda un intervallo come argomento e aggiunga “-done” a ogni valore nell’intervallo. Questo può essere fatto facilmente usando link: / tips-concatenate-formula-in-microsoft-excel [funzione di concatenazione] ma qui useremo una funzione array.
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
Spiegazione:
La funzione precedente accetterà un intervallo come argomento e aggiungerà “-done” a ciascun valore nell’intervallo.
Puoi vedere che abbiamo utilizzato una raccolta VBA qui per contenere i valori dell’array e quindi abbiamo eseguito la nostra operazione su ciascun valore e li abbiamo aggiunti di nuovo su un array bidimensionale.
Quindi sì ragazzi, è così che puoi creare una funzione di array VBA personalizzata che può restituire un array. Spero sia stato abbastanza esplicativo. Se hai domande su questo articolo, inseriscilo nella sezione commenti qui sotto.
Fare clic sul collegamento sottostante per scaricare il file di lavoro:
`link: /wp-content-uploads-2019-12-Create-VBA-Function-to-Return-Array-1.xls [__ Crea funzione VBA per restituire array]
Articoli correlati:
link: / vba-user-defined-function [Come creare una funzione definita dall’utente tramite VBA] | Impara come creare funzioni definite dall’utente in 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 in Microsoft Excel] `| Utilizzare la funzione definita dall’utente in un’altra cartella di lavoro di Excel `link: / funzioni-personalizzate-restituire-valori-errore-da-funzioni-definite-dall’utente-using-vba-in-microsoft-excel [Restituire valori di errore da definiti dall’utente funzioni utilizzando VBA in Microsoft Excel] `| Scopri come restituire valori di errore da una funzione definita dall’utente
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-scorciatoie-per-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività]
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel]
link: / tips-countif-in-microsoft-excel [CONTA.SE in Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SOMMA.SE in Excel]