Funzione array UDF con argomenti in Excel
Come suggerisce il titolo, impareremo come creare una funzione definita dall'utente in Excel che restituisce un array. Abbiamo già imparato come creare una funzione definita dall'utente in VBA. Quindi, senza perdere tempo, iniziamo con il tutorial.
Che 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 o le formule di matrice come funzione e formule CSE.
La funzione matrice excel è spesso formule di matrice multicella. Un esempio è la 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.
Funzione ThreeEven() As Integer() 'definisce array Dim numbers(2) As Integer 'Assegna valori a array numbers(0) = 0 numeri(1) = 2 numeri(2) = 4 'restituisce valori ThreeEven = numeri End Function
Usiamo questa funzione sul foglio di lavoro.
Puoi vedere che, prima selezioniamo tre celle (in orizzontale, per il verticale dobbiamo usare un array bidimensionale. Lo abbiamo trattato di seguito). Quindi iniziamo a scrivere la nostra formula. Quindi premiamo CTRL+MAIUSC+INVIO. Questo riempie le celle selezionate con i valori dell'array.
Nota:
- In pratica, non saprai di quante celle avrai bisogno. In tal caso, seleziona sempre più celle rispetto alla lunghezza dell'array prevista. La funzione riempirà le celle con l'array e le celle extra mostreranno l'errore #N/A.
- 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.
Funzione functionName(variables) As returnType() dim resultArray(length) as dataType 'Assegna i valori all'array qui functionName =resultArray End Function
La dichiarazione della funzione deve essere come definita sopra. Questo ha dichiarato che si tratta di una funzione di matrice.
Durante l'utilizzo nel foglio di lavoro, è necessario utilizzare la combinazione di tasti CTRL+MAIUSC+INVIO. Altrimenti, restituirà solo il primo valore dell'array.
Funzione array VBA per restituire array verticale
Per far funzionare la tua funzione array UDF in verticale, non devi fare molto. Basta dichiarare gli array come array bidimensionali. Quindi nella prima dimensione aggiungere i valori e lasciare vuota l'altra dimensione. Ecco come lo fai:
Funzione ThreeEven() As Integer() 'definisce array Dim numbers(2,0) As Integer 'Assegna valori a array numbers(0,0) = 0 numeri(1,0) = 2 numeri(2,0) = 4 ' valori di ritorno TrePari = numeri Fine Funzione
Ecco come lo usi sul foglio di lavoro.
Funzione array UDF con argomenti in Excel
Negli esempi precedenti, abbiamo semplicemente stampato i valori statici di somma sul foglio. Diciamo che vogliamo che la nostra funzione accetti un argomento range, esegua alcune operazioni su di essi 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 utilizzare 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 la funzione di concatenazione, ma qui useremo una funzione di matrice.
Funzione CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Crea una collezione Dim col As New Collection 'Aggiunta valori alla collezione On Error Riprendi successivo per ogni v In rng col.Add v Next On Error GoTo 0 ' completando l'operazione su ciascun valore e aggiungendoli a Array ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Avanti CONCATDone = resulArr End Function
Spiegazione:
La funzione sopra accetterà un intervallo come argomento e aggiungerà "-done" a ciascun valore nell'intervallo.
Puoi vedere che abbiamo usato 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, ecco come è possibile creare una funzione array VBA personalizzata in grado di restituire un array. Spero di essere 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:
Crea la funzione VBA per restituire l'arrayArray in Excel Formul|Scopri cosa sono gli array in Excel.
Come creare una funzione definita dall'utente tramite VBA | Scopri come creare funzioni definite dall'utente in Excel
Utilizzo di una funzione definita dall'utente (UDF) da un'altra cartella di lavoro utilizzando VBA in Microsoft Excel | Usa la funzione definita dall'utente in un'altra cartella di lavoro di Excel
Restituisce i valori di errore dalle funzioni definite dall'utente utilizzando VBA in Microsoft Excel | Scopri come restituire valori di errore da una funzione definita dall'utente
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività
La funzione CERCA.VERT in Excel
CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel