Una raccolta è un oggetto che contiene un numero di elementi simili. Questi possono essere facilmente accessibili e manipolati, anche se c'è un gran numero di elementi all'interno della raccolta.
Esistono già raccolte integrate con Excel VBA. Un esempio è la raccolta Fogli. Per ogni foglio di lavoro all'interno di una cartella di lavoro, c'è un elemento all'interno della raccolta Fogli.
Le raccolte integrate hanno molte più proprietà e metodi a tua disposizione, ma queste non sono disponibili nelle tue raccolte che crei.
Ad esempio, è possibile utilizzare la raccolta per ottenere informazioni su un particolare foglio di lavoro. Ad esempio, puoi vedere il nome del foglio di lavoro e anche se è visibile o meno. Usando un ciclo For Each, puoi scorrere ogni foglio di lavoro nella raccolta.
| 1234567 | Fogli di lavoro sub-test()Dim Sh come foglio di lavoroPer ogni Sh in fogliMsgBox Sh.NomeMsgBox Sh.VisibleSuccessivo ShFine sottotitolo |
Puoi anche indirizzare un foglio di lavoro specifico nella raccolta utilizzando il valore dell'indice o il nome effettivo del foglio di lavoro:
| 12 | Fogli MsgBox(1).NomeFogli MsgBox("Foglio1").Nome |
Man mano che i fogli di lavoro vengono aggiunti o eliminati, la raccolta Fogli aumenta o si riduce di dimensioni.
Nota che con le raccolte VBA il numero di indice inizia con 1 non con 0
Collezioni contro array
Le matrici e le raccolte sono simili nelle loro funzioni in quanto sono entrambe metodologie che consentono di archiviare una grande quantità di dati che possono quindi essere facilmente referenziati utilizzando il codice. Tuttavia, presentano una serie di differenze nel modo in cui operano:
- Gli array sono multidimensionali mentre le raccolte sono solo a dimensione singola. È possibile dimensionare un array con diverse dimensioni, ad es.
| 1 | Dim MyArray(10, 2) As String |
Questo crea una matrice di 10 righe con 2 colonne, quasi come un foglio di lavoro. Una raccolta è effettivamente una singola colonna. L'array è utile se è necessario memorizzare un numero di elementi di dati correlati tra loro, ad es. nome e indirizzo. Il nome sarebbe nella prima dimensione dell'array e l'indirizzo nella seconda dimensione.
- Quando si popola l'array, è necessaria una riga di codice separata per inserire un valore in ciascun elemento dell'array. Se avessi un array bidimensionale avresti effettivamente bisogno di 2 righe di codice: una riga per indirizzare la prima colonna e una riga per indirizzare la seconda colonna. Con l'oggetto Collection, si utilizza semplicemente il metodo Add in modo che il nuovo elemento venga appena aggiunto alla raccolta e il valore dell'indice venga automaticamente adattato.
- Se è necessario eliminare un elemento di dati, è più complicato nell'array. È possibile impostare i valori di un elemento su un valore vuoto, ma l'elemento stesso esiste ancora all'interno dell'array. Se stai usando un ciclo For Next per scorrere l'array, il ciclo restituirà un valore vuoto, che avrà bisogno di codifica per assicurarsi che il valore vuoto venga ignorato. In una raccolta si utilizzano i metodi Aggiungi o Rimuovi e l'indicizzazione e il ridimensionamento vengono eseguiti automaticamente. L'elemento che è stato rimosso scompare completamente. Gli array sono utili per una dimensione fissa di dati, ma le raccolte sono migliori per i casi in cui la quantità di dati è soggetta a modifiche.
- Le raccolte sono di sola lettura mentre i valori dell'array possono essere modificati utilizzando VBA. Con una raccolta, dovresti prima rimuovere il valore da modificare e poi aggiungere il nuovo valore modificato.
- In un array è possibile utilizzare solo un singolo tipo di dati per gli elementi che viene impostato quando si dimensiona l'array. Tuttavia, nell'array è possibile utilizzare tipi di dati personalizzati progettati dall'utente. Potresti avere una struttura di array molto complicata utilizzando un tipo di dati personalizzato che a sua volta ha diversi tipi di dati personalizzati sotto di esso. In una raccolta, puoi aggiungere i tipi di dati di utilizzo dei dati per ogni elemento. Potresti avere un valore numerico, una data o una stringa: l'oggetto della raccolta prenderà qualsiasi tipo di dati. Se si tentasse di inserire un valore stringa in una matrice dimensionata come numerica, si produrrebbe un messaggio di errore.
- Le raccolte sono generalmente più facili da usare rispetto agli array. In termini di codifica, quando crei un oggetto raccolta, ha solo due metodi (Aggiungi e Rimuovi) e due proprietà (Conteggio e Elemento), quindi l'oggetto non è affatto complicato da programmare.
- Le raccolte possono utilizzare le chiavi per individuare i dati. Gli array non hanno questa funzione e richiedono il codice di ciclo per scorrere l'array per trovare valori specifici.
- La dimensione di un array deve essere definita quando viene creata per la prima volta. Devi avere un'idea di quanti dati verranno archiviati. Se è necessario aumentare le dimensioni dell'array, è possibile utilizzare "ReDim" per ridimensionarlo, ma è necessario utilizzare la parola chiave "Preserva" se non si desidera perdere i dati già contenuti nell'array. Non è necessario definire una dimensione della raccolta. Cresce e si riduce automaticamente man mano che gli elementi vengono aggiunti o rimossi.
Ambito di un oggetto da collezione
In termini di ambito, l'oggetto della raccolta è 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, la raccolta deve essere ricreata utilizzando il codice VBA.
Se vuoi che la tua collezione sia disponibile per tutto il codice nel tuo modulo di codice, allora devi dichiarare l'oggetto della collezione nella sezione Dichiara nella parte superiore della finestra del modulo
Ciò garantirà che tutto il codice all'interno di quel modulo possa accedere alla raccolta. Se vuoi che qualsiasi modulo all'interno della tua cartella di lavoro acceda alla raccolta, definiscilo come un oggetto globale
| 1 | Global MyCollection come nuova collezione |
Creazione di una raccolta, aggiunta di elementi e accesso agli elementi
Un semplice oggetto di raccolta può essere creato in VBA utilizzando il seguente codice:
| 123456 | Sub CreateCollection()Dim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2"MyCollection.Aggiungi "Articolo3"Fine sottotitolo |
Il codice dimensiona un nuovo oggetto chiamato "MyCollection" e quindi le seguenti righe di codice utilizzano il metodo Add per aggiungere 3 nuovi valori.
Puoi quindi utilizzare il codice per scorrere la tua raccolta per accedere ai valori
| 123 | Per ogni articolo in MyCollectionOggetto MsgBoxArticolo successivo |
Puoi anche scorrere la tua raccolta utilizzando un ciclo For Next:
| 123 | Per n = 1 In MyCollection.CountMsgBox MyCollection(n)Successivo n |
Il codice ottiene la dimensione della raccolta utilizzando la proprietà Count e quindi la utilizza a partire da un valore 1 per indicizzare ogni elemento
Il ciclo For Each è più veloce del ciclo For Next ma funziona solo in una direzione (da indice basso ad alto). Il For Next Loop ha il vantaggio di poter usare una direzione diversa (da indice alto a basso) e puoi anche usare il metodo Step per cambiare l'incremento. Ciò è utile quando si desidera eliminare diversi elementi poiché sarà necessario eseguire l'eliminazione dalla fine della raccolta all'inizio poiché l'indice cambierà man mano che le eliminazioni avvengono.
Il metodo Add in una raccolta ha 3 parametri opzionali: Key, Before e After
Puoi utilizzare i parametri "Prima" e "Dopo" per definire la posizione del tuo nuovo articolo rispetto agli altri già presenti nella collezione
Questo viene fatto specificando il numero di indice a cui vuoi che il tuo nuovo elemento sia relativo.
| 123456 | Sub CreateCollection()Dim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2", , 1MyCollection.Aggiungi "Articolo3"Fine sottotitolo |
In questo esempio è stato specificato 'Item2' da aggiungere prima del primo elemento indicizzato nella raccolta (che è 'Item1'). Quando si scorre questa raccolta, apparirà prima di tutto "Articolo2", seguito da "Articolo1" e "Articolo3"
Quando specifichi un parametro "Prima" o "Dopo", il valore dell'indice viene regolato automaticamente all'interno della raccolta in modo che "Articolo2" diventi il valore dell'indice di 1 e "Articolo1" venga spostato su un valore dell'indice di 2
Puoi anche utilizzare il parametro "Chiave" per aggiungere un valore di riferimento che puoi utilizzare per identificare l'elemento della collezione. Si noti che un valore chiave deve essere una stringa e deve essere univoco all'interno della raccolta.
| 1234567 | Sub CreateCollection()Dim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Item2", "MyKey"MyCollection.Aggiungi "Articolo3"MsgBox MyCollection("MyKey")Fine sottotitolo |
A "Articolo2" è stato assegnato un valore "Chiave" di "MyKey" in modo che tu possa fare riferimento a tale articolo utilizzando il valore di "MyKey" anziché il numero di indice (2)
Nota che il valore "Chiave" deve essere un valore stringa. Non può essere nessun altro tipo di dati. Si noti che la raccolta è di sola lettura e non è possibile aggiornare il valore della chiave una volta impostato. Inoltre, non è possibile verificare se esiste un valore chiave per un elemento specifico nella raccolta o visualizzare il valore chiave, il che è un po' uno svantaggio.
Il parametro "Chiave" ha l'ulteriore vantaggio di rendere il codice più leggibile, soprattutto se viene consegnato a un collega per l'assistenza e non è necessario scorrere l'intera raccolta per trovare quel valore. Immagina se avessi una collezione di 10.000 articoli quanto sarebbe difficile fare riferimento a un articolo specifico!
Rimozione di un oggetto da una collezione
Puoi utilizzare il metodo "Rimuovi" per eliminare elementi dalla tua raccolta.
| 1 | MyCollection.Rimuovi (2) |
Sfortunatamente, non è facile se la raccolta ha un numero elevato di elementi elaborare l'indice dell'elemento che si desidera eliminare. È qui che il parametro "Chiave" torna utile quando viene creata la raccolta
| 1 | MyCollection.Remove ("MyKey") |
Quando un elemento viene rimosso da una raccolta, i valori dell'indice vengono reimpostati automaticamente durante tutta la raccolta. È qui che il parametro "Chiave" è così utile quando si eliminano più elementi contemporaneamente. Ad esempio, puoi eliminare l'indice dell'elemento 105 e immediatamente l'indice dell'elemento 106 diventa l'indice 105 e tutto ciò che si trova sopra questo elemento ha il suo valore di indice spostato verso il basso. Se si utilizza il parametro Key, non è necessario preoccuparsi di quale valore di indice debba essere rimosso.
Per eliminare tutti gli elementi della raccolta e creare una nuova raccolta, si utilizza nuovamente l'istruzione Dim che crea una raccolta vuota.
| 1 | Dim MyCollection come nuova collezione |
Per rimuovere completamente l'oggetto della collezione attuale, puoi impostare l'oggetto su niente
| 1 | Imposta MyCollection = Niente |
Questo è utile se la raccolta non è più richiesta dal tuo codice. L'impostazione dell'oggetto della raccolta su niente rimuove tutti i riferimenti ad esso e rilascia la memoria che stava utilizzando. Ciò può avere importanti implicazioni sulla velocità di esecuzione del codice, se in memoria è presente un oggetto di grandi dimensioni che non è più necessario.
Conta il numero di elementi in una collezione
Puoi facilmente scoprire il numero di articoli nella tua collezione utilizzando la proprietà "Conteggio"
| 1 | MsgBox MyCollection.Count |
Useresti questa proprietà se stessi usando un For Next Loop per scorrere la raccolta in quanto ti fornirà il limite superiore per il numero di indice.
Raccolta di test per un valore specifico
Puoi scorrere una raccolta per cercare un valore specifico per un elemento utilizzando un For Each Loop
| 123456789101112 | Raccolta di ricerca secondaria()Dim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2"MyCollection.Aggiungi "Articolo3"Per ogni articolo in MyCollectionSe Articolo = "Articolo2" AlloraElemento MsgBox & "Trovato"Finisci seProssimoFine sottotitolo |
Il codice crea una piccola raccolta, quindi scorre attraverso di essa alla ricerca di un elemento chiamato "elemento2". Se trovato, visualizza una finestra di messaggio che ha trovato l'elemento specifico
Uno degli svantaggi di questa metodologia è che non è possibile accedere al valore dell'indice o al valore della chiave
Se invece utilizzi un ciclo For Next, puoi utilizzare il contatore For Next per ottenere il valore dell'indice, sebbene non sia ancora possibile ottenere il valore "Key"
| 123456789101112 | Raccolta di ricerca secondaria()Dim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2"MyCollection.Aggiungi "Articolo3"Per n = 1 In MyCollection.CountSe MyCollection.Item(n) = "Item2" ThenMsgBox MyCollection.Item(n) & " trovato alla posizione dell'indice " & nFinisci seSuccessivo nFine sottotitolo |
Il contatore For Next (n) fornirà la posizione dell'indice
Ordinamento di una raccolta
Non esiste una funzionalità integrata per ordinare una raccolta, ma utilizzando un pensiero "pronto all'uso", il codice può essere scritto per eseguire un ordinamento, utilizzando la funzione di ordinamento del foglio di lavoro di Excel. Questo codice utilizza un foglio di lavoro vuoto chiamato "SortSheet" per eseguire l'ordinamento effettivo.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sotto ordinamentoRaccolta()Dim MyCollection come nuova collezioneContatore scuro quanto più a lungo"Costruisci una collezione con articoli in ordine casuale"MyCollection.Aggiungi "Articolo5"MyCollection.Aggiungi "Articolo2"MyCollection.Aggiungi "Articolo4"MyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo3"'Cattura il numero di elementi nella raccolta per un uso futuroContatore = MyCollection.Count'Esplora la raccolta copiando ogni elemento in una cella consecutiva su 'SortSheet' (colonna A)Per n = 1 In MyCollection.CountFogli("Foglio Sort").Cells(n, 1) = MyCollection(n)Successivo n"Attiva il foglio di ordinamento e usa la routine di ordinamento di Excel per ordinare i dati in ordine crescente"Fogli("Foglio di ordinamento").AttivaIntervallo("A1:A" & MyCollection.Count).SelezionaActiveWorkbook.Worksheets("Foglio di ordinamento").Sort.SortFields.ClearActiveWorkbook.Worksheets ("Foglio di selezione"). Sort.SortFields.Add2 Chiave:=Intervallo( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSortNormalCon ActiveWorkbook.Worksheets ("Foglio di ordinamento"). Ordina.SetRange Range ("A1: A5").Intestazione = xlIndovina.MatchCase = False.Orientamento = xlTopToBottom.SortMethod = xlPinYin.ApplicareTermina con'Elimina tutti gli elementi nella raccolta - nota che questo For Next Loop viene eseguito in ordine inversoPer n = MyCollection.Count fino a 1 passaggio -1MyCollection.Rimuovi (n)Successivo n'Copia nuovamente i valori della cella nell'oggetto di raccolta vuoto utilizzando il valore memorizzato (Contatore) per il 'loop'Per n = 1 Al contatoreMyCollection.Add Sheets("SortSheet").Cells(n, 1).ValueSuccessivo n"Esamina la raccolta per provare l'ordine in cui si trovano ora gli articoli"Per ogni articolo in MyCollectionOggetto MsgBoxArticolo successivo'Cancella il foglio di lavoro (foglio di selezione) - se necessario, eliminalo ancheFogli("Foglio Ordinamento").Intervallo(Celle(1, 1), Celle(Contatore, 1)).CancellaFine sottotitolo |
Questo codice crea prima una raccolta con gli elementi aggiunti in ordine casuale. Quindi li copia nella prima colonna di un foglio di lavoro (SortSheet).
Il codice utilizza quindi la funzione di ordinamento di Excel per ordinare i dati nella colonna in ordine crescente. Il codice potrebbe anche essere modificato per ordinare in ordine decrescente.
La raccolta viene quindi svuotata dei dati utilizzando un For Next Loop. Si noti che l'opzione del passaggio viene utilizzata in modo che venga cancellata dalla fine della raccolta all'inizio. Questo perché quando si cancella i valori dell'indice vengono resettati, se si cancellasse dall'inizio, non si cancellerebbe correttamente (l'indice 2 diventerebbe indice 1)
Infine, utilizzando un altro For Next Loop, i valori degli elementi vengono trasferiti nuovamente nella raccolta vuota
Un ulteriore For Each Loop dimostra che la raccolta è ora in buon ordine crescente.
Sfortunatamente, questo non riguarda alcun valore di chiave che potrebbe essere stato inserito originariamente, poiché i valori di chiave non possono essere letti
Passaggio di una raccolta a un sottotitolo/funzione
Una raccolta può essere passata a un sottotitolo oa una funzione allo stesso modo di qualsiasi altro parametro
| 1 | Funzione MyFunction(ByRef MyCollection as Collection) |
È importante passare la raccolta utilizzando "ByRef". Ciò significa che viene utilizzata la raccolta originale. Se la raccolta viene passata utilizzando "ByVal", viene creata una copia della raccolta che può avere ripercussioni sfortunate
Se viene creata una copia utilizzando "ByVal", tutto ciò che modifica la raccolta all'interno della funzione avviene solo sulla copia e non sull'originale. Ad esempio, se all'interno della funzione viene aggiunto un nuovo elemento alla raccolta, questo non apparirà nella raccolta originale, il che creerà un bug nel codice.
Restituzione di una raccolta da una funzione
Puoi restituire una raccolta da una funzione allo stesso modo della restituzione di qualsiasi oggetto. Devi usare la parola chiave Set
| 12345 | Sub ReturnFromFunction()Dim MyCollection As CollectionImposta MyCollection = PopulateCollectionMsgBox MyCollection.CountFine sottotitolo |
Questo codice crea una sottoroutine che crea un oggetto chiamato "MyCollection" e quindi utilizza la parola chiave "Set" per chiamare efficacemente la funzione per popolare quella raccolta. Una volta fatto, viene visualizzata una finestra di messaggio per mostrare il conteggio di 2 elementi
| 1234567 | Funzione PopulateCollection() As CollectionDim MyCollection come nuova collezioneMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2"Set PopulateCollection = MyCollectionFine funzione |
La funzione PopulateCollection crea un nuovo oggetto di raccolta e lo popola con 2 elementi. Quindi passa questo oggetto all'oggetto di raccolta creato nella sottoroutine originale.
Conversione di una raccolta in un array
Potresti voler convertire la tua collezione in un array. Potresti voler memorizzare i dati dove possono essere modificati e manipolati. Questo codice crea una piccola raccolta e poi la trasferisce in un array
Si noti che l'indice della raccolta inizia da 1 mentre l'indice dell'array inizia da 0. Considerando che la raccolta ha 3 elementi, l'array deve essere dimensionato solo a 2 perché c'è un elemento 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray()Dim MyCollection come nuova collezioneDim MyArray(2) As StringMyCollection.Aggiungi "Articolo1"MyCollection.Aggiungi "Articolo2"MyCollection.Aggiungi "Articolo3"Per n = 1 In MyCollection.CountMyArray(n - 1) = MyCollection(n)Successivo nPer n = da 0 a 2MsgBox MyArray(n)Successivo nFine sottotitolo |
Conversione di un array in una raccolta
Potresti voler convertire un array in una raccolta. Ad esempio, potresti voler accedere ai dati in modo più rapido ed elegante rispetto all'utilizzo del codice per ottenere un elemento dell'array.
Tieni presente che funzionerà solo per una singola dimensione dell'array perché la raccolta ha una sola dimensione
| 123456789101112131415 | Sub ConvertArrayIntoCollection()Dim MyCollection come nuova collezioneDim MyArray(2) As StringMyArray(0) = "elemento1"MyArray(1) = "Elemento2"MyArray(2) = "Articolo3"Per n = da 0 a 2MyCollection.Add MyArray(n)Successivo nPer ogni articolo in MyCollectionOggetto MsgBoxArticolo successivoFine sottotitolo |
Se si desidera utilizzare un array multidimensionale, è possibile concatenare i valori dell'array per ogni riga all'interno dell'array utilizzando un carattere delimitatore tra le dimensioni dell'array, in modo che durante la lettura del valore della raccolta sia possibile utilizzare a livello di codice il carattere delimitatore per separare i valori.
È anche possibile spostare i dati nella raccolta in base al fatto che viene aggiunto il valore della prima dimensione (indice 1), quindi viene aggiunto il valore della dimensione successiva (indice 2) e così via.
Se l'array avesse, ad esempio, 4 dimensioni, ogni quarto valore nella raccolta sarebbe un nuovo insieme di valori.
Puoi anche aggiungere valori di array da utilizzare come chiavi (a condizione che siano univoci) che aggiungerebbe un modo semplice per individuare dati specifici.
