Utilizzo di un dizionario VBA
Un dizionario VBA funziona in modo simile a un oggetto di raccolta, ma ha più proprietà e metodi e offre maggiore flessibilità
Il dizionario memorizza i dati in memoria e può essere facilmente manipolato. Non sono necessari calcoli automatici, backup in background e aggiornamento dello schermo, quindi il tuo codice verrà eseguito molto più velocemente.
L'oggetto dizionario funziona in modo simile a un normale dizionario che utilizzeresti se vuoi scoprire il significato di una parola. Ogni voce nell'oggetto del dizionario ha un valore "chiave" e un valore "elemento". Utilizzi la "chiave" il valore della chiave per cercare il valore dell'elemento nell'oggetto dizionario, in modo simile a come utilizzeresti un dizionario convenzionale.
A causa del modo in cui funziona l'oggetto dizionario, i valori chiave devono essere tutti univoci, allo stesso modo di un dizionario convenzionale. Immagina se aprissi il tuo dizionario convenzionale per cercare il significato di una parola e trovassi la parola elencata più di una volta con due definizioni completamente diverse. Saresti molto confuso!
I valori chiave sono in genere testo o numeri o entrambi. Gli utenti spesso trovano più facile ricordare i nomi delle chiavi come testo piuttosto che solo numeri.
Rispetto a un oggetto di raccolta, l'oggetto di raccolta è di sola lettura. Ha solo due metodi (Aggiungi e Rimuovi) e due proprietà (Conteggio e Articolo). Una volta che un elemento è stato aggiunto a un oggetto della raccolta, può essere solo rimosso, ma non modificato, il che è una procedura macchinosa se è necessario modificare il valore di un elemento.
Un oggetto dizionario cambierà automaticamente di dimensione per adattarsi al numero di elementi al suo interno. Non ha bisogno di essere definito in termini di dimensioni, come un array convenzionale
L'oggetto dizionario è unidimensionale e il tipo di dati è "Variant", quindi è possibile inserire qualsiasi tipo di dati, ad es. numerico, testo, data
Il dizionario VBA non è nativo di Excel e deve essere accessibile tramite l'associazione anticipata o tardiva durante la definizione dell'oggetto dizionario
| 123 | Sub EarlyBindingEsempio()Dim MyDictionary come nuovo Scripting.DictionaryFine sottotitolo |
| 1234 | Sub LateBindingEsempio()Dim MyDictionary As ObjectImposta MyDictionary = CreateObject("Scripting.Dictionary")Fine sottotitolo |
Se utilizzi l'associazione anticipata, devi aggiungere un riferimento alla libreria "Microsoft Scripting Runtime"
Puoi farlo selezionando "Strumenti | Riferimenti' sulla barra dei menu della finestra di Visual Basic Editor (VBE) e apparirà una finestra pop-up con un elenco di librerie disponibili.
Scorri verso il basso fino a "Microsoft Scripting Runtime" e seleziona la casella accanto ad essa. Fare clic su OK e questa libreria fa ora parte del progetto VBA e può essere referenziata utilizzando l'associazione anticipata. Tutti gli esempi di codice in questo articolo utilizzeranno l'associazione anticipata.
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
La libreria Scripting Runtime ha "Intellisense". Mentre scrivi il tuo codice, vedrai apparire elenchi di metodi e proprietà disponibili, il che aiuta a prevenire errori di ortografia, che causeranno bug nel tuo programma
Inoltre, se si preme F2 all'interno del VBE e si seleziona la libreria "Scripting", verranno visualizzati tutti i metodi e le proprietà disponibili e i parametri richiesti per ciascuno
Distribuzione dell'applicazione Excel contenente un dizionario
Come già sottolineato, la libreria Scripting Runtime non fa parte di Excel VBA quindi se distribuisci la tua applicazione ad altri utenti, questi devono avere accesso alla libreria Scripting Runtime sul proprio computer. In caso contrario, si verificherà un errore.
È una buona idea includere del codice VBA per verificare che questa libreria sia presente quando viene caricata l'applicazione Excel. Puoi usare il comando "Dir" per farlo nell'evento "Workbook Open"
La posizione del file è C:\Windows\SysWOW64\scrrun.dll
Ambito di un oggetto dizionario
L'oggetto Dizionario è disponibile solo mentre la cartella di lavoro di Excel è aperta. Non viene salvato quando viene salvata la cartella di lavoro.
Se il tuo dizionario deve essere disponibile per tutte le routine all'interno del tuo modulo, devi dichiararlo (Dim) nella sezione Dichiara nella parte superiore del modulo
Lo definisci come un oggetto globale se vuoi che il tuo dizionario venga utilizzato in tutto il codice.
| 1 | Global MyDictionary come nuovo dizionario |
Popolazione e lettura dal tuo dizionario
Per cominciare, devi creare un dizionario, popolarlo con alcuni dati e quindi scorrere attraverso di esso per dimostrare che i dati esistono
| 1234567891011 | Sub PopulateReadDictionary()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "MyItem1", 10MyDictionary.Aggiungi "MyItem2", 20MyDictionary.Aggiungi "MyItem3", 30Per n = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)Successivo nFine sottotitolo |
Questo codice crea un nuovo oggetto dizionario chiamato "MyDictionary" e quindi lo popola con tre elementi. Il metodo Add ha due parametri: Key e Item, entrambi obbligatori
I tipi di dati per Chiave e Articolo sono entrambi varianti, quindi accettano qualsiasi tipo di dato: numerico, testo, data, ecc
Il primo elemento nel dizionario potrebbe essere aggiunto come:
| 1 | MyDictionary.Aggiungi 10, "MyItem1" |
I valori sono stati invertiti tra Key e Item, ma funzionerebbe ancora, anche se la chiave di ricerca ora diventerebbe 10.
Tuttavia, è importante comprendere che il valore chiave è il valore di ricerca nel dizionario. Funziona in modo molto simile alla funzione CERCA.VERT in Excel. Poiché tutte le chiavi devono avere valori univoci, puoi specificare un valore chiave e restituire immediatamente il valore dell'elemento per quella chiave.
Nota che l'indice del dizionario inizia da 0, quindi devi sottrarre 1 dal conteggio del dizionario utilizzato nel ciclo For… Next
Puoi anche usare un ciclo For… Each per leggere i valori nel dizionario:
| 1234567891011 | Sub PopulateReadDictionary()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Aggiungi "MyItem1", 10MyDictionary.Aggiungi "MyItem2", 20MyDictionary.Aggiungi "MyItem3", 30Per ogni io in MyDictionary.KeysMsgBox I & " " & MyDictionary(I)Avanti ioFine sottotitolo |
Questo codice scorrerà ogni elemento e visualizzerà la chiave dell'elemento e il valore dell'elemento
Utilizzo del numero di indice dell'articolo
È possibile utilizzare il numero di indice di una chiave o di un elemento per leggere il valore
| 123456789101112 | Sub IndiceNumeri()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Aggiungi "Articolo1", 10MyDictionary.Aggiungi "Articolo2", 20MyDictionary.Aggiungi "Articolo3", 30MsgBox MyDictionary.Keys(2)MsgBox MyDictionary.Items(1)Fine sottotitolo |
Questo codice restituirà la chiave "elemento3" poiché l'indice inizia da 0 e il valore dell'elemento è 20
È possibile fare riferimento a singoli valori di chiavi o elementi all'interno delle raccolte Keys o Items utilizzando i numeri di indice.
Filtrare il dizionario
Non esiste un metodo diretto per farlo, ma è abbastanza semplice scrivere codice per farlo:
| 1234567891011 | SottofiltroDizionario()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "AAItem1", 10MyDictionary.Aggiungi "BBItem2", 20MyDictionary.Aggiungi "BBItem3", 30Per ogni filtro I in (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item(I)Avanti ioFine sottotitolo |
Il valore del filtro funziona solo dall'inizio del valore della chiave. Non è possibile utilizzare caratteri jolly nel filtro. Questo codice restituirà i due valori degli articoli con i nomi delle chiavi che iniziano con "BB"
Questo ti darà un sottoinsieme del dizionario in base al valore del tuo filtro, che potresti poi trasferire in un altro dizionario o foglio di lavoro. Con un'attenta pianificazione sui nomi delle chiavi, assicurandosi che ci sia un prefisso significativo per ciascuno, si sarebbe facilmente in grado di dividere il dizionario in varie parti componenti.
Modifica del valore di un elemento di una chiave
L'oggetto dizionario ha un grande vantaggio rispetto a una raccolta in quanto il valore dell'elemento può essere modificato, ad es.
| 1 | MyDictionary("MyItem4") = "40" |
Nella raccolta, dovresti eliminare quella voce e quindi ricrearla.
Ecco un esempio di codice:
| 12345678910111213 | SubPopolaLeggiDizionario()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "MyItem1", 10MyDictionary.Aggiungi "MyItem2", 20MyDictionary.Aggiungi "MyItem3", 30MyDictionary("MyItem2") = "25"MyDictionary("MyItem4") = "40"Per n = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)Successivo nFine sottotitolo |
Il codice sopra imposta tre elementi all'interno del dizionario, quindi modifica il valore di "MyItem2" da 20 a 25.
Cambia anche il valore di "MyItem4" in 40. Nota che nelle istruzioni add del codice non è stato aggiunto "MyItem4". Quando modifichi il valore di una chiave che non esiste, questa viene creata automaticamente. Questo è estremamente conveniente, poiché non viene attivato alcun errore, ma significa che è necessario fare attenzione con i nomi delle chiavi. Un errore di ortografia involontario nel nome della chiave significherebbe che viene creata una nuova chiave e il nome della chiave originale avrebbe ancora il vecchio valore.
Ciò potrebbe facilmente portare a problemi di integrità nell'oggetto dizionario.
Verifica se esiste una chiave
Puoi controllare se esiste un valore chiave all'interno del dizionario
| 123456789 | Sub CheckExistsDictionary()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "MyItem1", 10MyDictionary.Aggiungi "MyItem2", 20MyDictionary.Aggiungi "MyItem3", 30MsgBox MyDictionary.Exists("MyItem8")Fine sottotitolo |
Il codice aggiunge tre elementi a un nuovo oggetto dizionario, quindi verifica una chiave ("MyItem8") che non è nel dizionario. Questo restituisce False, ma se fosse stata utilizzata una delle chiavi esistenti, restituirebbe True
I caratteri jolly non sono accettati. Anche il testo di ricerca fa distinzione tra maiuscole e minuscole per impostazione predefinita, ma questo può essere modificato (vedi più avanti nell'articolo)
Utilizzo di più valori in un dizionario
A differenza di un array, l'oggetto dizionario è solo unidimensionale. Questo può portare a problemi se hai diversi valori che vuoi mettere su una chiave.
Un modo per aggirare questo è concatenare ogni valore di elemento utilizzando un carattere delimitatore tra ogni valore, ad es. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Sottovalori multipli()'Crea oggetto dizionario e variabiliDim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 As Date, Temp As String, N As Integer'Popola 3 variabili per dimostrare più valoriV1 = 5V2 = "Esempio di più valori"V3 = "22-lug-2020"'Aggiungi il valore concatenato al dizionario usando "|" delimitatoreMyDictionary.Aggiungi "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Cattura il valore del dizionario concatenato dal dizionario in una variabileTemp = MyDictionary("MyMultipleItem")'Itera attraverso la stringa concatenata per separare i singoli valoriFare'Trova la posizione di un delimitatoreN = InStr(Temp, "|")'Se non ci sono più delimitatori, l'uscita Do loopSe N = 0 Allora Esci Do'Visualizza testo relativo alla posizione del delimitatore trovatoMsgBox Left(Temp, N - 1)'Tronca la stringa concatenata al carattere successivo dopo il delimitatore trovatoTemp = Media (Temp, N + 1)Ciclo continuoFine sottotitolo |
Un altro modo per aggirare questo problema è progettare il proprio sistema di sub-script per i nomi delle chiavi. Non c'è motivo per cui non dovresti usare parentesi e numeri nei nomi delle chiavi
| 1234567891011 | Sottovalori multipli()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "Multiplo (1)", 5MyDictionary.Add "Multiplo(2)", "Esempio di più valori"MyDictionary.Aggiungi "Multiplo(3)", "22-lug-2020"Per N = da 1 a 3MsgBox MyDictionary("Multiplo(" & N & ")")Successivo NFine sottotitolo |
Questo codice aggiunge tre chiavi al dizionario, ma ogni nome di chiave contiene un numero di subscript tra parentesi. È quindi possibile fare riferimento al nome della chiave, ma utilizzando il numero di subscript concatenato in. È molto simile all'utilizzo di un oggetto array
Eliminazione di elementi
Puoi rimuovere singoli elementi facendo riferimento al valore chiave
| 1 | MyDictionary.Remove ("MyItem2") |
Nota che poiché i nomi delle chiavi sono univoci, questo rimuove solo quella particolare chiave e valore dell'elemento
Puoi anche cancellare completamente il dizionario
| 1 | MyDictionary.Rimuovitutto |
Ecco un esempio di utilizzo di "Rimuovi" in VBA:
| 12345678910111213141516 | Sub RemoveValues()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "Articolo1", 10MyDictionary.Aggiungi "Articolo2", 20MyDictionary.Aggiungi "Articolo3", 30MyDictionary.Rimuovi ("Elemento2")Per N = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)Successivo NMyDictionary.RimuovituttoMsgBox MyDictionary.CountFine sottotitolo |
Il codice aggiunge tre elementi al dizionario, quindi rimuove "Articolo2". Quindi scorre il dizionario per dimostrare che "Item2" non esiste più
Infine, il codice rimuove tutti gli elementi nel dizionario e visualizza il conteggio del dizionario, che ora è zero.
Modifica della distinzione tra maiuscole e minuscole per le ricerche
Se si esegue una ricerca per una chiave, per impostazione predefinita viene fatta distinzione tra maiuscole e minuscole. Tuttavia, puoi utilizzare la proprietà "CompareMode" per cambiarlo.
Si noti che questa operazione deve essere eseguita immediatamente nel codice dopo aver creato l'oggetto dizionario, ma prima di aggiungere dati nel dizionario. Una volta che la modalità di confronto è stata impostata, non può essere modificata all'interno di quel dizionario.
| 12345678910 | Sub ChangeCaseSensitivity()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Aggiungi "Articolo1", 10MyDictionary.Aggiungi "Articolo2", 20MyDictionary.Aggiungi "Articolo3", 30MsgBox MyDictionary.Exists("item2")Fine sottotitolo |
In questo esempio, la modalità di confronto è impostata su "TextCompare", il che significa che non fa distinzione tra maiuscole e minuscole. L'istruzione "Esiste" alla fine dell'esempio restituirà True, nonostante il testo di ricerca sia tutto in minuscolo.
In Excel ci sono solo due valori che possono essere usati per la modalità di confronto. Binary Compare fa distinzione tra maiuscole e minuscole e Text Compare non fa distinzione tra maiuscole e minuscole
Se la modalità di confronto è impostata su Binary Compare, è necessario prestare attenzione nel nominare le chiavi. Se imposti un nome in modo che abbia una lettera maiuscola come primo carattere, quando cambi il valore, devi assicurarti di scrivere ancora il primo carattere in maiuscolo. Se inizi con un carattere minuscolo, questo verrà interpretato come una nuova chiave e potrebbe facilmente portare a confusione ed errori nel tuo dizionario
Ricorda che se modifichi un valore per una chiave e il nome della chiave non esiste a causa dell'utilizzo di un confronto binario, una nuova chiave e un nuovo valore verranno aggiunti al dizionario.
Se invece utilizzi Confronto testo, qualsiasi modifica al valore andrà alla chiave indipendentemente dal caso. Se provi ad aggiungere lo stesso elemento ma scritto con un carattere maiuscolo diverso, riceverai un errore perché esiste già.
Ordinamento del dizionario
Come con l'oggetto raccolta, non è previsto alcun metodo per poter ordinare il dizionario, utilizzando chiavi o valori di elemento.
Tuttavia, poiché il codice VBA si trova in una cartella di lavoro di Excel, i dati del dizionario possono essere trasferiti in Excel in forma tabellare e quindi è possibile applicare la funzione di ordinamento di Excel. Il dizionario può quindi essere cancellato utilizzando "Rimuovi tutto" e i valori ordinati aggiunti dal foglio di lavoro.
Questo codice ordinerà sia le chiavi che i valori degli elementi
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary()Dim MyDictionary come nuovo dizionarioContatore scuro quanto più a lungo"Costruisci un dizionario con elementi in ordine casuale"MyDictionary.Aggiungi "Articolo5", 5MyDictionary.Aggiungi "Articolo2", 15MyDictionary.Aggiungi "Item4", 11MyDictionary.Aggiungi "Articolo1", 2MyDictionary.Aggiungi "Articolo3", 19'Cattura il numero di elementi nel dizionario per un uso futuroContatore = MyDictionary.Count'Iterare il dizionario copiando ogni chiave ed elemento in una cella consecutiva su 'Foglio1' (colonna A)Per N = 0 a MyDictionary.Count - 1Fogli("Foglio1").Celle(N + 1, 1) = MyDictionary.Keys(N)Fogli("Foglio1").Celle(N + 1, 2) = MyDictionary.Items(N)Successivo N'Attiva il Foglio1 e usa la routine di ordinamento di Excel per ordinare i dati in ordine crescenteFogli("Foglio1").AttivaIntervallo("A1:B" & MyDictionary.Count).SelezionaActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add2 Chiave:=Intervallo( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSortNormalCon ActiveWorkbook.Worksheets ("Foglio1"). Ordina.SetRange Range ("A1: A5").Intestazione = xlIndovina.MatchCase = False.Orientamento = xlTopToBottom.SortMethod = xlPinYin.ApplicareTermina con'Cancella tutti gli elementi dal dizionarioMyDictionary.Rimuovitutto'Copia nuovamente i valori della cella nell'oggetto dizionario vuoto utilizzando il valore memorizzato (Contatore) per il 'loopPer N = 1 al contatoreMyDictionary.Add Sheets("Sheet1").Cells(N, 1).Value, Sheets("Sheet1").Cells(N, 2).ValueSuccessivo N'Esamina il dizionario per provare l'ordine in cui si trovano ora gli elementiPer N = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)Successivo N'Cancella il foglio di lavoro (Foglio1) - se necessario, eliminalo ancheFogli("Foglio1").Intervallo(Celle(1, 1), Celle(Contatore, 2)).CancellaFine sottotitolo |
Questo codice crea un dizionario con cinque valori di ordine casuale aggiunti. Cattura il numero di elementi in una variabile, quindi scorre il dizionario, trasferendo i valori della chiave e dell'elemento in colonne separate su un foglio di lavoro.
Quindi ordina l'intervallo scaricato, utilizzando la colonna A come campo di ordinamento. Il dizionario viene cancellato completamente utilizzando il metodo "RemoveAll" e il codice scorre quindi i valori delle celle nel foglio di lavoro aggiungendoli al dizionario.
Infine, il codice scorre il dizionario, visualizzando i valori della chiave e dell'elemento concatenati per dimostrare che l'ordinamento ha funzionato.
Modificando i parametri nel codice di ordinamento, i dati potrebbero essere ordinati per valori dell'articolo.
Copiare un elenco di chiavi in un foglio di lavoro
È possibile copiare un elenco di tutti i valori chiave in un foglio di lavoro utilizzando il codice seguente:
| 12345678910 | Sotto CopyKeyList()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Aggiungi "Articolo1", 10MyDictionary.Aggiungi "Articolo2", 20MyDictionary.Aggiungi "Articolo3", 30Fogli("Foglio1").Range("A1").Value = Join(MyDictionary.Keys, vbLf)Fine sottotitolo |
Questo produrrà il risultato nel tuo foglio di lavoro:
Puoi copiare un intero dizionario in un foglio di lavoro usando questo codice:
| 12345678910 | Sub CopyIntoWorksheet()Dim MyDictionary come nuovo Scripting.DictionaryMyDictionary.Aggiungi "Articolo1", 10MyDictionary.Aggiungi "Articolo2", 20MyDictionary.Aggiungi "Articolo3", 30Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)Fine sottotitolo |
Il tuo foglio di lavoro sarà simile a questo:
Confrontare un dizionario con una raccolta
Il dizionario è più veloce di una raccolta.
Una raccolta è già all'interno di VBA. Un dizionario ha bisogno di un riferimento al dizionario di script Microsoft per essere aggiunto o di un oggetto creato utilizzando l'associazione tardiva
Un elemento della Collezione può essere scritto solo una volta e letto molte volte. In un dizionario, il valore dell'elemento può essere modificato. Con una raccolta, l'elemento deve essere rimosso e quindi l'elemento modificato deve essere riaggiunto.
La Collezione lavora sui valori dell'indice, che può essere difficile capire a quale valore dell'indice appartiene e dove. Il dizionario funziona su valori chiave univoci che vengono utilizzati per individuare un elemento
Il recupero di un singolo elemento è più lento in una raccolta di grandi dimensioni che in un dizionario
In una raccolta, le chiavi vengono utilizzate solo per cercare dati e non sono recuperabili. In un dizionario, le chiavi possono essere verificate per l'esistenza e possono essere utilizzate per trovare un particolare elemento.
Le raccolte fanno distinzione tra maiuscole e minuscole e questo non può essere modificato. In un dizionario, la modalità di confronto può essere impostata per fornire la distinzione tra maiuscole e minuscole o meno
In una raccolta, i valori chiave devono essere stringhe. In un dizionario possono essere di qualsiasi tipo di dati, ad es. numerico, data, ecc
La rimozione di tutti gli elementi in una raccolta implica la ridefinizione dell'oggetto della raccolta. Il dizionario ha il metodo "RemoveAll" per questo.
