Un oggetto ArrayList è simile a un oggetto Collection ma ha molti più metodi e proprietà e quindi una flessibilità molto maggiore dal punto di vista della programmazione.
Un oggetto Collection ha solo due metodi (Add, Remove) e due proprietà (Count, Item) mentre un Array List ne ha molti di più. Inoltre, l'oggetto Collection è di sola lettura. Una volta aggiunti i valori, il valore indicizzato non può essere modificato, mentre su un Array List è possibile la modifica.
Molti dei metodi Array List utilizzano parametri. A differenza di molti dei metodi VBA standard, nessuno di questi parametri è facoltativo. Inoltre, alcuni metodi e proprietà non sono sempre in maiuscolo quando vengono immessi nello stesso modo in cui lo fanno in Excel VBA. Tuttavia, funzionano ancora.
L'oggetto ArrayList si espande e si contrae in base al numero di elementi che contiene. Non ha bisogno di essere quotato prima dell'uso come un array.
L'elenco di array è unidimensionale (uguale all'oggetto Collection) e il tipo di dati predefinito è Variant, il che significa che accetterà qualsiasi tipo di dati, siano essi numerici, di testo o di data.
In molti modi l'elenco di array risolve una serie di carenze dell'oggetto Collection. È certamente molto più flessibile in ciò che può fare.
L'oggetto Array List non fa parte della libreria VBA standard. Puoi usarlo nel codice VBA di Excel utilizzando l'associazione tardiva o anticipata
| 1234 | Sub LateBindingEsempio()Dim MyList As ObjectImposta MyList = CreateObject("System.Collections.ArrayList")Fine sottotitolo |
| 123 | Sub EarlyBindingEsempio()Dim MyList As New ArrayListFine sottotitolo |
Per utilizzare l'esempio di associazione anticipata, è necessario prima inserire un riferimento in VBA al file "mscorlib.tlb"
Puoi farlo selezionando "Strumenti | Riferimenti dalla finestra di Visual Basic Editor (VBE). Apparirà una finestra pop-up con tutti i riferimenti disponibili. Scorri verso il basso fino a "mscorlib.dll" e seleziona la casella accanto. Fai clic su OK e quella libreria fa ora parte del tuo progetto:
Uno dei grandi svantaggi di un oggetto Array List è che non ha "Intellisense". Normalmente, quando si utilizza un oggetto in VBA come un intervallo, verrà visualizzato un elenco a comparsa di tutte le proprietà e i metodi disponibili. Questo non si ottiene con un oggetto Array List e talvolta è necessario un attento controllo per assicurarsi di aver digitato correttamente il metodo o la proprietà.
Inoltre, se si preme F2 nella finestra VBE e si cerca "arraylist", non verrà visualizzato nulla, il che non è molto utile per uno sviluppatore.
Il tuo codice verrà eseguito notevolmente più velocemente con l'associazione anticipata, perché è tutto compilato in anticipo. Con l'associazione tardiva, l'oggetto deve essere compilato durante l'esecuzione del codice
Distribuzione dell'applicazione Excel contenente un elenco di array
Come già sottolineato, l'oggetto ArrayList non fa parte di Excel VBA. Ciò significa che tutti i tuoi colleghi a cui distribuisci l'applicazione devono avere accesso al file "mscorlib.tlb"
Questo file si trova normalmente in:
C:\Windows\Microsoft.NET\Framework\v4.0.30319
Potrebbe valere la pena scrivere del codice (usando il metodo Dir) per verificare che questo file esista quando un utente carica l'applicazione in modo che sperimenti un "atterraggio morbido" se non viene trovato. Se non è presente e il codice viene eseguito, si verificheranno errori.
Inoltre, l'utente deve avere installata la versione corretta di .Net Framework. Anche se l'utente ha una versione successiva, deve essere installata la V3.5, altrimenti l'applicazione non funzionerà
Ambito di un oggetto elenco array
In termini di ambito, l'oggetto Array List è disponibile solo mentre la cartella di lavoro è aperta. Non viene salvato quando viene salvata la cartella di lavoro. Se la cartella di lavoro viene riaperta, l'oggetto Array List deve essere ricreato utilizzando il codice VBA.
Se vuoi che il tuo Array List sia disponibile per tutto il codice nel tuo modulo di codice, devi dichiarare l'oggetto Array List nella sezione Dichiara nella parte superiore della finestra del modulo
Ciò garantirà che tutto il codice all'interno di quel modulo possa accedere all'elenco degli array. Se vuoi che qualsiasi modulo all'interno della tua cartella di lavoro acceda all'oggetto Array List, definiscilo come oggetto globale
| 1 | Global MyCollection As New ArrayList |
Popolamento e lettura dall'elenco di array
L'azione di base che si desidera intraprendere è creare un elenco di array, inserire alcuni dati al suo interno e quindi dimostrare che i dati possono essere letti. Tutti gli esempi di codice in questo articolo presuppongono che tu stia utilizzando l'associazione anticipata e che sia stato aggiunto "mscorlib.tlb" ai riferimenti VBA, come descritto sopra
| 123456789101112 | Sub ArrayListEsempio()'Crea nuovo oggetto elenco arrayDim MyList As New ArrayList"Aggiungi elementi all'elenco"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"'Esegui l'iterazione attraverso l'elenco di array per dimostrare i valoriPer N = 0 a MyList.Count - 1MsgBox MyList(N)Successivo NFine sottotitolo |
Questo esempio crea un nuovo oggetto ArrayList, lo popola con 3 elementi e scorre l'elenco visualizzando ogni elemento.
Nota che l'indice ArrayList inizia da 0, non da 1, quindi devi sottrarre 1 dal valore Count
Puoi anche usare un ciclo "For… Each" per leggere i valori:
| 123456789101112 | Sub ArrayListEsempio()'Crea nuovo oggetto elenco arrayDim MyList As New ArrayList"Aggiungi elementi all'elenco"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"'Iter attraverso l'elenco di array per dimostrare i valoriPer ogni io in MyListMsgBox IAvanti ioFine sottotitolo |
Modifica e cambio di elementi in un elenco di array
Uno dei principali vantaggi di un elenco di array rispetto a una raccolta è che gli elementi nell'elenco possono essere modificati e modificati all'interno del codice. L'oggetto Collection viene letto solo mentre l'oggetto Array List viene letto / scritto
| 123456789101112131415 | Sub ArrayListEsempio()'Crea nuovo oggetto elenco arrayDim MyList As New ArrayList"Aggiungi elementi all'elenco"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3""Cambia elemento 1 da "Articolo2" a "Cambiato"MyList(1) = "Cambiato"'Esegui l'iterazione attraverso l'elenco di array per dimostrare che il cambiamento ha funzionatoPer ogni io in MyList'Visualizza nome elementoMsgBox IAvanti ioFine sottotitolo |
In questo esempio, il secondo elemento, "Item2" viene modificato con il valore "Changed" (ricorda che l'indice inizia da 0). Quando l'iterazione viene eseguita alla fine del codice, verrà visualizzato il nuovo valore
Aggiunta di un array di valori a un elenco di array
Puoi inserire valori nel tuo elenco di array utilizzando un array contenente un elenco di questi valori o riferimenti a valori di cella su un foglio di lavoro
| 123456789101112131415161718 | Sub AddArrayExample()"Crea oggetto elenco array"Dim MyList As New ArrayList'itera i valori dell'array aggiungendoli all'elenco degli arrayPer ogni v In Array("A1", "A2", "A3")'Aggiungi ogni valore dell'array all'elencoMyList.Add vProssimo'ripeti i valori dell'array con i riferimenti del foglio di lavoro aggiungendoli all'elenco degli array'Per ogni v In Array(Range("A5").Value, Range("A6").Value)MyList.Add vProssimo'Esegui l'iterazione attraverso l'elenco di array per dimostrare i valoriPer N = 0 a MyList.Count - 1‘Visualizza voce elencoMsgBox MyList.Item(N)Successivo NFine sottotitolo |
Lettura/recupero di un intervallo di elementi da un elenco di array
Utilizzando il metodo GetRange in un elenco di array, è possibile specificare una serie di elementi consecutivi da recuperare. I due parametri richiesti sono la posizione dell'indice iniziale e il numero di elementi da recuperare. Il codice popola un secondo oggetto Array List con il sottoinsieme di elementi che possono essere letti separatamente.
| 123456789101112131415161718 | Sub ReadRangeEsempio()"Definisci oggetti"Dim MyList As New ArrayList, MyList1 As Object'Aggiungi elementi all'oggetto 'MyList'MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"MyList.Aggiungi "Elemento6"MyList.Aggiungi "Elemento4"MyList.Aggiungi "Elemento7"'Cattura 4 elementi in 'MyList' a partire dalla posizione dell'indice 2Imposta MyList1 = MyList.GetRange(2, 4)'Esegui l'iterazione attraverso l'oggetto 'MyList1' per visualizzare il sottoinsieme di elementiPer ogni io in MyList1'Visualizza nome elementoMsgBox IAvanti ioFine sottotitolo |
Ricerca di elementi all'interno di un elenco di array
Puoi verificare se un elemento denominato è nel tuo elenco utilizzando il metodo "Contiene". Questo restituirà Vero o Falso
| 1 | MsgBox MyList.Contains("Articolo2") |
Puoi anche trovare la posizione effettiva dell'indice utilizzando il metodo "IndexOf". È necessario specificare l'indice iniziale per la ricerca (di solito 0). Il valore restituito è l'indice della prima istanza dell'elemento trovato. È quindi possibile utilizzare un ciclo per modificare il punto iniziale nel valore dell'indice successivo per trovare ulteriori istanze se sono presenti diversi valori duplicati.
Se il valore non viene trovato, viene restituito il valore -1
Questo esempio mostra l'utilizzo di "Contiene", elemento non trovato e il ciclo nell'elenco dell'array per trovare la posizione di tutti gli elementi duplicati:
| 1234567891011121314151617181920212223242526 | Elenco di ricerca secondariaEsempio()'Definire l'elenco degli array e le variabiliDim MyList As New ArrayList, Sp As Integer, Pos As Integer‘Aggiungi nuovi elementi incluso un duplicatoMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"MyList.Aggiungi "Elemento1"'Test per "Elemento2" nell'elenco - restituisce TrueMsgBox MyList.Contains("Articolo2")'Ottieni indice di valore inesistente - restituisce -1MsgBox MyList.IndexOf("Articolo", 0)'Imposta la posizione iniziale per la ricerca su zeroSp = 0'Scorri l'elenco per ottenere tutte le posizioni di 'Item1'Fare'Ottieni la posizione dell'indice del prossimo 'Item1' in base alla posizione nella variabile 'Sp'Pos = MyList.IndexOf("Item1", Sp)'Se non vengono trovate ulteriori istanze di 'Item1', uscire dal cicloSe Pos = -1 Quindi Esci Do'Visualizza la prossima istanza trovata e la posizione dell'indiceMsgBox MyList(Pos) & " all'indice " & Pos'Aggiungi 1 all'ultimo valore dell'indice trovato: questa ora diventa la nuova posizione iniziale per la ricerca successivaSp = Posizione + 1Ciclo continuoFine sottotitolo |
Si noti che il testo di ricerca utilizzato fa distinzione tra maiuscole e minuscole e i caratteri jolly non sono accettati.
Inserimento e rimozione di elementi
Se non desideri aggiungere i tuoi elementi alla fine dell'elenco, puoi inserirli in una particolare posizione dell'indice in modo che il nuovo elemento si trovi al centro dell'elenco. I numeri di indice verranno adattati automaticamente per gli elementi successivi.
| 123456789101112131415 | Sub InsertEsempio()"Definisci oggetto elenco array"Dim MyList As New ArrayList'Aggiungi elementi all'elenco degli arrayMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"MyList.Aggiungi "Elemento1"'Inserisci 'Elemento6' nella posizione dell'indice 2MyList.Inserisci 2, "Elemento6"'Esamina gli elementi nell'elenco dell'array per mostrare il nuovo ordine e la posizione dell'indicePer N = 0 a MyList.Count - 1MsgBox MyList(N) & " Indice " & NSuccessivo NFine sottotitolo |
In questo esempio, "Articolo6" viene aggiunto all'elenco nella posizione di indice 2, quindi "Articolo3" che si trovava nella posizione di indice 2 ora si sposta nella posizione di indice 3
Un singolo elemento può essere rimosso utilizzando il metodo "Rimuovi".
| 1 | MyList.Rimuovi "Articolo" |
Si noti che non viene prodotto alcun errore se il nome dell'elemento non viene trovato. Tutti i numeri di indice successivi verranno modificati per adattarsi alla rimozione.
Se conosci la posizione dell'indice dell'elemento, puoi utilizzare il metodo "RemoveAt", ad es.
| 1 | MyList.RimuoviA 2 |
Si noti che se la posizione dell'indice data è maggiore del numero di elementi nell'elenco dell'array, verrà restituito un errore.
È possibile rimuovere un intervallo di valori dall'elenco utilizzando il metodo "RemoveRange". I parametri sono l'indice iniziale e quindi il numero di elementi da rimuovere, ad es.
| 1 | MyList.RemoveRange 3, 2 |
Nota che otterrai un errore nel tuo codice se il numero di elementi offset dal valore iniziale è maggiore del numero di elementi nell'elenco dell'array.
In entrambi i metodi "RemoveAt" e "RemoveRange", sarebbe consigliabile del codice per verificare se i numeri di indice specificati sono maggiori del numero totale di elementi nell'elenco dell'array per intercettare eventuali errori. La proprietà 'Count' fornirà il numero totale di elementi nell'elenco dell'array.
| 12345678910111213141516171819202122232425 | Sub RimuoviEsempio()"Definisci oggetto elenco array"Dim MyList As New ArrayList'Aggiungi elementi all'elenco degli arrayMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Articolo4"MyList.Aggiungi "Elemento5"'Inserisci 'Elemento6' nella posizione dell'indice 2MyList.Inserisci 2, "Elemento6"'Rimuovi 'Elemento2'MyList.Rimuovi "Elemento2"'Rimuovi 'Elemento': questo non esiste nell'elenco dell'array ma non genera erroriMyList.Rimuovi "Articolo"'Rimuovi l'elemento nella posizione di indice 2'MyList.RimuoviA 2‘Rimuovi 2 elementi consecutivi a partire dalla posizione di indice 2MyList.RemoveRange 3, 2'Esegui l'iterazione attraverso l'elenco degli array per mostrare cosa è rimasto e in quale posizione di indice si trova oraPer N = 0 a MyList.Count - 1MsgBox MyList(N) & " Indice " & NSuccessivo NFine sottotitolo |
Si noti che se si utilizza "RemoveAt" per rimuovere un elemento in una posizione specifica, non appena tale elemento viene rimosso, tutte le successive posizioni dell'indice vengono modificate. Se hai più rimozioni utilizzando la posizione dell'indice, allora una buona idea è iniziare con il numero di indice più alto e tornare indietro fino alla posizione zero in modo da rimuovere sempre l'elemento corretto. In questo modo non avrai il problema
Ordinamento di un elenco di array
Un altro grande vantaggio rispetto a una raccolta è che puoi ordinare gli elementi in ordine crescente o decrescente.
L'oggetto Array List è l'unico oggetto in Excel VBA con un metodo di ordinamento. Il metodo di ordinamento è molto veloce e questa può essere una considerazione importante per l'utilizzo di un elenco di array.
Nell'oggetto della raccolta, è stato necessario un po' di pensiero "pronto all'uso" per ordinare tutti gli elementi, ma con un elenco di array è molto semplice.
Il metodo "Ordina" ordina in ordine crescente e il metodo "Inverti" ordina in ordine decrescente.
| 12345678910111213141516171819202122 | Sub ArrayListEsempio()"Crea oggetto elenco array"Dim MyList As New ArrayList'Aggiungi articoli in un ordine non ordinatoMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento3"MyList.Aggiungi "Elemento2""Ordina gli elementi in ordine crescente"MyList.Ordina'Esplora gli elementi per mostrare l'ordine crescentePer ogni io in MyList'Visualizza nome elementoMsgBox IAvanti io"Ordina gli elementi in ordine decrescente"MyList.Reverse'Iterare tra gli elementi per mostrare l'ordine decrescentePer ogni io in MyList'Visualizza nome elementoMsgBox IAvanti ioFine sottotitolo |
Clonazione di un elenco di array
Un elenco di array ha la possibilità di creare un clone o una copia di se stesso. Ciò è utile se un utente apporta modifiche agli elementi utilizzando un front-end e il codice VBA, ma è necessario conservare una copia degli elementi nel loro stato originale come backup.
Ciò potrebbe fornire all'utente una funzione "Annulla". Potrebbero aver apportato le modifiche e voler tornare all'elenco originale.
| 123456789101112131415 | Sub CloneEsempio()'Definisci due oggetti: elenco di array e un oggettoDim MyList As New ArrayList, MyList1 As Object'Popola il primo oggetto con elementiMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"‘Copia Mylist in MyList1Imposta MyList1 = MyList.Clone'Iter attraverso MyList1 per dimostrare la clonazionePer ogni io in MyList1'Visualizza nome elementoMsgBox IAvanti ioFine sottotitolo |
"MyList1" ora contiene tutti gli elementi di "MyList" nello stesso ordine
Copia di un array di elenchi in un oggetto array VBA convenzionale
È possibile utilizzare un metodo semplice per copiare l'elenco degli array in un normale array VBA:
| 123456789101112131415 | Sub ArrayEsempio()"Crea un oggetto elenco array e un oggetto array standard"Dim MyList As New ArrayList, NewArray As Variant"Popola l'elenco di array con elementi"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"'Copia l'elenco degli array nel nuovo arrayNewArray = MyList.ToArray'Esegui l'iterazione attraverso il nuovo array - nota che il conteggio dell'elenco di array fornisce l'indice massimoPer N = 0 a MyList.Count - 1'Visualizza nome elementoMsgBox NewArray(N)Successivo NFine sottotitolo |
Copia di un array di elenchi in un intervallo di fogli di lavoro
È possibile copiare l'elenco degli array in un foglio di lavoro e un riferimento di cella specifici senza la necessità di scorrere l'elenco degli array. Devi solo specificare il primo riferimento di cella
| 123456789101112131415 | SottointervalloEsempio()'Crea nuovo oggetto elenco arrayDim MyList As New ArrayList"Aggiungi elementi all'elenco"MyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"‘Cancella il foglio di destinazioneFogli("Foglio1").UsedRange.Clear"Copia elementi su una riga"Fogli("Foglio1").Range("A1").Resize(1, MyList.Count).Value = MyList.toArray'Copia gli elementi giù per una colonnaFogli("Foglio1").Intervallo("A5").Resize(MyList.Count, 1).Value = _Foglio di lavoroFunction.Transpose(MyList.toArray)Fine sottotitolo |
Svuota tutti gli elementi da un elenco di array
C'è una semplice funzione (Cancella) per cancellare completamente l'elenco degli array
| 1234567891011121314 | Sub ClearListEsempio()'Crea oggetto elenco arrayDim MyList As New ArrayList'Aggiungi nuovi elementiMyList.Aggiungi "Elemento1"MyList.Aggiungi "Elemento2"MyList.Aggiungi "Elemento3"'Mostra conteggio articoliMsgBox MyList.Count"Cancella tutti gli elementi"MyList.Clear"Mostra il conteggio degli elementi per dimostrare che clear ha funzionato"MsgBox MyList.CountFine sottotitolo |
Questo esempio crea elementi in un elenco di array e quindi cancella l'elenco di array. Le finestre di messaggio dimostrano prima e dopo il numero di elementi nell'elenco dell'array.
Riepilogo dei metodi dell'elenco di array per Excel VBA
| Compito | Parametri | Esempi |
| Aggiungi/Modifica elemento | Valore | MyList.Aggiungi “Elemento1” |
| MyList(4)= “Articolo2” | ||
| Clona un elenco di array | Nessuno | Dim MyList As Object |
| Imposta MyList2 = MyList.Clone | ||
| Copia su array | Nessuno | Dim MyArray As Variant |
| MyArray = MyList.ToArray | ||
| Copia in un intervallo di fogli di lavoro (riga) | Nessuno | Fogli(“Foglio1”).Range(“A1”).Resize(1, MyList.Count).Value = MyList.ToArray |
| Copia in un intervallo di fogli di lavoro (colonna) | Nessuno | Fogli(“Foglio1”).Range(“A3”).Resize(MyList.Count, 1).Value = WorksheetFunction.Transpose(MyList.ToArray) |
| Creare | "System.Collections.ArrayList" | Dim MyList As Object |
| Imposta MyList = CreateObject ("System.Collections.ArrayList") | ||
| Dichiarare | N / A | Dim MyList As Object |
| Trova/controlla se l'articolo esiste | Oggetto da trovare | MyList.Contains(“Articolo2”) |
| Trova la posizione di un elemento in ArrayList | 1. Oggetto da trovare. | Indice dim No come lungo |
| 2. Posizione da cui iniziare la ricerca. | IndexNo = MyList.IndexOf(“Articolo3”, 0) | |
| IndexNo = MyList.IndexOf(“Item5”, 3) | ||
| Ottieni il numero di articoli | Nessuno | MsgBox MyList.Count |
| Inserisci elemento | 1. Indice - posizione in cui inserire. | MyList.Insert 0, “Item5” |
| 2 Valore - oggetto o valore da inserire. | MyList.Insert 4, “Item7” | |
| Leggi articolo | Indice - intero lungo | MsgBox MyList.Item(0) |
| MsgBox MyList.Item(4) | ||
| Leggi l'ultimo elemento aggiunto | Indice - intero lungo | MsgBox MyList.Item(list.Count - 1) |
| Leggi l'elemento aggiunto prima | Indice - intero lungo | MsgBox MyList.Item(0) |
| Leggi tutti gli articoli (per ciascuno) | N / A | Dim elemento come variante |
| Per ogni elemento in MyList | ||
| Elemento MsgBox | ||
| Elemento successivo | ||
| Leggi tutti gli articoli (per) | Indice - intero lungo | Dim I As Long |
| Per i = 0 a MyList.Count - 1 | ||
| MsgBox io | ||
| Avanti io | ||
| Rimuovi tutti gli elementi | Nessuno | MyList.Clear |
| Rimuovi elemento in posizione | Posizione dell'indice in cui si trova l'elemento | MyList.RimuoviA 5 |
| Rimuovi articolo per nome | L'elemento da rimuovere da ArrayList | MyList.Rimuovi “Elemento3” |
| Rimuovi una serie di articoli | 1. Indice - posizione di partenza. | MyList.RemoveRange 4,3 |
| 2. Conteggio: il numero di elementi da rimuovere. | ||
| Ordina in ordine decrescente | Nessuno | MyList.Reverse |
| Ordina in ordine crescente | Non | MyList.Ordina |
