Array VBA

In VBA, an Vettore è una singola variabile che può contenere più valori. Pensa a un array come un intervallo di celle: ogni cella può memorizzare un valore. Gli array possono essere unidimensionali (si pensi a una singola colonna), bidimensionali (si pensi a più righe e colonne) o multidimensionali. È possibile accedere ai valori dell'array in base alla loro posizione (numero di indice) all'interno dell'array.

Foglio rapido per array VBA

matrici

DescrizioneCodice VBACreareDim arr (da 1 a 3) come variante
arr(1) = “uno”
arr(2) = “due”
arr(3) = “tre”Crea da ExcelDim arr (da 1 a 3) come variante
Dim cell As Range, i As Integer
i = LBound(arr)
Per ogni cella nell'intervallo ("A1: A3")
io = io + 1
arr(i) = cella.valore
Cella successivaLeggi tutti gli articoliDim io come Long
Per i = LBound(arr) a UBound(arr)
MsgBox arr(i)
Avanti ioCancellareCancella arrangiamentoDa array a stringaDim sName As String
sName = Join(arr, “:”)Aumenta dimensioneReDim Mantieni arr(da 0 a 100)Valore impostatoarr(1) = 22

Esempi rapidi di array VBA

Diamo un'occhiata a un esempio completo prima di immergerci nello specifico:

12345678910

Sub ArrayEsempio()Dim strNames (da 1 a 4) come StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"msgbox strNames(3)Fine sottotitolo

Qui abbiamo creato l'array di stringhe unidimensionale: strNames con dimensione quattro (può contenere quattro valori) e assegnato i quattro valori. Infine mostriamo il 3° valore in una Message Box.

In questo caso, il vantaggio dell'utilizzo di un Array è piccolo: è richiesta solo una dichiarazione di variabile invece di quattro.

Tuttavia, diamo un'occhiata a un esempio che mostrerà la vera potenza di un array:

12345678

SottoarrayEsempio2()Dim strNames (da 1 a 600000) come stringaDim I As LongPer i = 1 a 60000strNames(i) = Cells(i, 1).ValueAvanti ioFine sottotitolo

Qui abbiamo creato un array che può contenere 60.000 valori e abbiamo rapidamente popolato l'array dalla colonna A di un foglio di lavoro.

Vantaggi dell'array? - Velocità!

Potresti pensare a matrici simili ai fogli di lavoro di Excel:

• Ogni cella (o elemento in un array) può contenere il proprio valore
• È possibile accedere a ogni cella (o elemento in un array) dalla posizione di riga e colonna.
  • Foglio di lavoro es. cell(1,4).value = "Riga 1, colonna 4"
  • matrice es. arrVar(1,4) = “Riga 1, Colonna 4”

Allora perché preoccuparsi degli array? Perché non leggere e scrivere i valori direttamente nelle celle in Excel? Una parola: Velocità!

La lettura/scrittura nelle celle di Excel è un processo lento. Lavorare con gli array è molto più veloce!

Crea/Dichiara un array (Dim)

Nota: gli array possono avere più "dimensioni". Per semplificare le cose, inizieremo lavorando solo con array unidimensionali. Più avanti nel tutorial ti presenteremo gli array a più dimensioni.

Matrice statica

Array statici sono array che non possono cambiare dimensione. Al contrario, Array dinamici può cambiare dimensione. Sono dichiarati in modo leggermente diverso. Per prima cosa, diamo un'occhiata agli array statici.

Nota: se le dimensioni dell'array non cambiano, utilizza un array statico.

La dichiarazione di una variabile array statica è molto simile alla dichiarazione di una variabile regolare, tranne per il fatto che è necessario definire la dimensione dell'array. Esistono diversi modi per impostare la dimensione di un array.

Puoi dichiarare esplicitamente le posizioni di inizio e fine di un array:

123456789101112

Sub StaticArray1()'Crea array con posizioni 1,2,3,4'Dim arrDemo1 (da 1 a 4) come stringa'Crea array con posizioni 4,5,6,7Dim arrDemo2 (da 4 a 7) come a lungo'Crea array con posizioni 0,1,2,3'Dim arrDemo3(da 0 a 3) il più a lungoFine sottotitolo

Oppure puoi inserire solo la dimensione dell'array:

123456

Sub StaticArray2()'Crea array con posizioni 0,1,2,3'Dim arrDemo1(3) As StringFine sottotitolo

Importante! Si noti che per impostazione predefinita, gli array iniziano dalla posizione 0. Quindi Dim arrDemo1(3) crea un array con posizioni 0,1,2,3.

Puoi dichiarare Opzione Base 1 nella parte superiore del modulo in modo che l'array inizi invece dalla posizione 1:

12345678

Opzione Base 1Sub StaticArray3()'Crea array con posizioni 1,2,3'Dim arrDemo1(3) As StringFine sottotitolo

Tuttavia, trovo che sia molto più semplice (e meno confuso) dichiarare esplicitamente le posizioni di inizio e fine degli array.

Stanco di cercare esempi di codice VBA? Prova AutoMacro!

Array dinamico

Array dinamici sono array la cui dimensione può essere modificata (o la cui dimensione non deve essere definita).

Esistono due modi per dichiarare un array dinamico.

Array di varianti

Il primo modo per dichiarare un array dinamico è impostare l'array su type Variante.

1	Dim arrVar() come variante

Con un Variante Arrayy, non è necessario definire la dimensione dell'array. La dimensione si regolerà automaticamente. Ricorda solo che l'array inizia con la posizione 0 (a meno che tu non aggiunga Option Base 1 nella parte superiore del tuo modulo)

12345678910111213

Sub VariantArray()Dim arrVar() come variante'Definisci valori (dimensione = 0,1,2,3)arrVar = Array(1, 2, 3, 4)'Cambia valori (dimensione = 0,1,2,3,4)arrVar = Array("1a", "2a", "3a", "4a", "5a")'Posizione di uscita 4 ("5a")MsgBox arrVar(4)Fine sottotitolo

Array dinamici non varianti

Con gli array non varianti, è necessario definire la dimensione dell'array prima di assegnare i valori all'array. Tuttavia, il processo per creare l'array è leggermente diverso:

1234567

Sub DynamicArray1()Dim arrDemo1() As String'Ridimensiona array con posizioni 1,2,3,4ReDim arrDemo1 (da 1 a 4)Fine sottotitolo

Per prima cosa dichiari l'array, simile all'array statico, tranne per il fatto che ometti la dimensione dell'array:

1	Dim arrDemo1() As String

Ora, quando vuoi impostare la dimensione dell'array, usi il ReDim comando per ridimensionare l'array:

12	'Ridimensiona array con posizioni 1,2,3,4ReDim arrDemo1 (da 1 a 4)

ReDim ridimensiona l'array. Leggi sotto per la differenza tra ReDim e ReDim Preserve.

ReDim vs. ReDim Preserve

Quando usi il ReDim comando cancella tutti i valori esistenti dall'array. Invece puoi usare ReDim Preserve per preservare i valori dell'array:

12	'Ridimensiona l'array con le posizioni 1,2,3,4 (Preservando i valori esistenti)ReDim Mantieni arrDemo1 (da 1 a 4)

Dichiarazione di array semplificata

Potresti sentirti sopraffatto dopo aver letto tutto quanto sopra. Per semplificare le cose, lavoreremo principalmente con array statici per il resto dell'articolo.

Imposta valori array

L'impostazione dei valori dell'array è molto semplice.

Con un array statico, devi definire ogni posizione dell'array, una alla volta:

12345678

Sub ArrayEsempio()Dim strNames (da 1 a 4) come StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"Fine sottotitolo

Con un Variant Array puoi definire l'intero array con una riga (pratico solo per piccoli array):

123456

Sub ArrayEsempio_1Linea()Dim strNames() come variantestrNames = Array("Shelly", "Steve", "Neema", "Jose")Fine sottotitolo

Se si tenta di definire un valore per una posizione dell'array che non esiste, si riceverà un errore Subscript Out of Range:

1	strNames(5) = "Shannon"

Nella sezione "Assegna intervallo all'array" di seguito ti mostreremo come utilizzare un ciclo per assegnare rapidamente un numero elevato di valori agli array.

Ottieni valore array

Puoi recuperare i valori dell'array allo stesso modo. Nell'esempio seguente scriveremo i valori dell'array nelle celle:

1234	Intervallo ("A1"). Valore = strNames(1)Range("A2").Value = strNames(2)Intervallo ("A3"). Valore = strNames(3)Intervallo("A4").Valore = strNames(4)

Programmazione VBA | Il generatore di codice funziona per te!

Assegna intervallo all'array

Per assegnare un intervallo a un array puoi utilizzare un ciclo:

12345678

Sub RangeToArray()Dim strNames (da 1 a 600000) come stringaDim I As LongPer i = 1 a 60000strNames(i) = Cells(i, 1).ValueAvanti ioFine sottotitolo

Questo scorrerà le celle A1: A60000, assegnando i valori della cella all'array.

Da array di output a intervallo

Oppure puoi usare un ciclo per assegnare un array a un intervallo:

123	Per i = 1 a 60000Cells(i, 1).Value = strNames(i)Avanti io

Questo farà il contrario: assegna i valori dell'array alle celle A1: A60000

Array 2D/multidimensionali

Finora abbiamo lavorato esclusivamente con array monodimensionali (1D). Tuttavia, gli array possono avere fino a 32 dimensioni.

Pensa a un array 1D come una singola riga o colonna di celle Excel, un array 2D come un intero foglio di lavoro Excel con più righe e colonne e un array 3D è come un'intera cartella di lavoro, contenente più fogli ciascuno contenente più righe e colonne (Tu potrebbe anche pensare a un array 3D come a un cubo di Rubik).

Esempi di array multidimensionali

Ora dimostriamo esempi di utilizzo di array di dimensioni diverse.

Programmazione VBA | Il generatore di codice funziona per te!

Esempio di matrice 1D

Questa procedura combina gli esempi di array precedenti in un'unica procedura, dimostrando come è possibile utilizzare gli array in pratica.

1234567891011121314

SottoarrayEx_1d()Dim strNames (da 1 a 600000) come stringaDim I As Long"Assegna valori all'array"Per i = 1 a 60000strNames(i) = Cells(i, 1).ValueAvanti io"Valori dell'array di output nell'intervallo"Per i = 1 a 60000Fogli("Output").Cells(i, 1).Value = strNames(i)Avanti ioFine sottotitolo

Esempio di matrice 2D

Questa procedura contiene un esempio di un array 2D:

123456789101112131415161718

SottoarrayEx_2d()Dim strNames (da 1 a 60000, da 1 a 10) come stringaDim i As Long, j As Long"Assegna valori all'array"Per i = 1 a 60000Per j = da 1 a 10strNames(i, j) = Cells(i, j).ValueAvanti jAvanti io"Valori dell'array di output nell'intervallo"Per i = 1 a 60000Per j = da 1 a 10Fogli("Output").Cells(i, j).Value = strNames(i, j)Avanti jAvanti ioFine sottotitolo

Esempio di matrice 3D

Questa procedura contiene un esempio di array 3D per lavorare con più fogli:

12345678910111213141516171819202122

SottoarrayEx_3d()Dim strNames (da 1 a 60000, da 1 a 10, da 1 a 3) As StringDim i As Long, j As Long, k As Long"Assegna valori all'array"Per k = da 1 a 3Per i = 1 a 60000Per j = da 1 a 10strNames(i, j, k) = Sheets("Sheet" & k).Cells(i, j).ValueAvanti jAvanti ioAvanti k"Valori dell'array di output nell'intervallo"Per k = da 1 a 3Per i = 1 a 60000Per j = da 1 a 10Fogli("Output" & k).Cells(i, j).Value = strNames(i, j, k)Avanti jAvanti ioAvanti kFine sottotitolo

Lunghezza/dimensione dell'array

Finora, ti abbiamo presentato i diversi tipi di array e ti abbiamo insegnato come dichiarare gli array e ottenere/impostare i valori dell'array. Successivamente ci concentreremo su altri argomenti necessari per lavorare con gli array.

Programmazione VBA | Il generatore di codice funziona per te!

Funzioni UBound e LBound

Il primo passo per ottenere la lunghezza/dimensione di un array è usare le funzioni UBound e LBound per ottenere i limiti superiore e inferiore dell'array:

123456

Sub UBoundLBound()Dim strNames (da 1 a 4) come stringaMsgBox UBound(strNames)MsgBox LBound(strNames)Fine sottotitolo

Sottraendo i due (e aggiungendo 1) otterrai la lunghezza:

1	GetArrLength = UBound(strNames) - LBound(strNames) + 1

Funzione lunghezza array

Ecco una funzione per ottenere la lunghezza di un array a dimensione singola:

1234567

Funzione pubblica GetArrLength(a As Variant) As LongSe è vuoto (a) AlloraGetArrLength = 0AltroGetArrLength = UBound(a) - LBound(a) + 1Finisci seFine funzione

Hai bisogno di calcolare la dimensione di un array 2D? Dai un'occhiata al nostro tutorial: Calcola la dimensione dell'array.

Loop Through Array

Ci sono due modi per scorrere un array. Il primo scorre i numeri interi corrispondenti alle posizioni numeriche dell'array. Se conosci la dimensione dell'array puoi specificarla direttamente:

12345678910111213

Sub ArrayExample_Loop1()Dim strNames (da 1 a 4) come stringaDim I As LongstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"Per i = da 1 a 4MsgBox strNames(i)Avanti ioFine sottotitolo

Tuttavia, se non si conosce la dimensione dell'array (se l'array è dinamico), è possibile utilizzare le funzioni LBound e UBound della sezione precedente:

12345678910111213

Sub ArrayExample_Loop2()Dim strNames (da 1 a 4) come stringaDim I As LongstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"Per i = LBound(strNames) To UBound(strNames)MsgBox strNames(i)Avanti ioFine sottotitolo

Per ogni ciclo di array

Il secondo metodo è con un For Each Loop. Questo passa attraverso ogni elemento nell'array:

12345678910111213

Sub ArrayExample_Loop3()Dim strNames (da 1 a 4) come stringaOggetto fiocostrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"Per ogni elemento in strNamesOggetto MsgBoxArticolo successivoFine sottotitolo

Il ciclo For Each Array funzionerà con array multidimensionali oltre a array unidimensionali.

Programmazione VBA | Il generatore di codice funziona per te!

Loop attraverso array 2D

È inoltre possibile utilizzare le funzioni UBound e LBound per eseguire il ciclo anche in un array multidimensionale. In questo esempio eseguiremo il ciclo di un array 2D. Si noti che le funzioni UBound e LBound consentono di specificare quale dimensione dell'array trovare i limiti superiore e inferiore (1 per la prima dimensione, 2 per la seconda dimensione).

1234567891011121314151617181920

Sub ArrayExample_Loop4()Dim strNames(da 1 a 4, da 1 a 2) As StringDim i As Long, j As LongstrNames(1, 1) = "Shelly"strNames(2, 1) = "Steve"strNames(3, 1) = "Neema"strNames(4, 1) = "Giuseppe"strNames(1, 2) = "Shelby"strNames(2, 2) = "Steven"strNames(3, 2) = "Nemo"strNames(4, 2) = "Jesse"Per j = LBound(strNames, 2) To UBound(strNames, 2)Per i = LBound(strNames, 1) To UBound(strNames, 1)MsgBox strNames(i, j)Avanti ioAvanti jFine sottotitolo

Altre attività di array

Cancella matrice

Per cancellare un intero array, usa l'istruzione Erase:

1	Cancella strNames

Esempio di utilizzo:

12345678910

Sub ArrayEsempio()Dim strNames (da 1 a 4) come StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"Cancella strNamesFine sottotitolo

In alternativa, puoi anche ReDim l'array per ridimensionarlo, cancellando parte dell'array:

1	ReDim strNames (da 1 a 2)

Questo ridimensiona l'array alla dimensione 2, eliminando le posizioni 3 e 4.

Conteggio matrice

Puoi contare il numero di posizioni in ogni dimensione di un array usando le funzioni UBound e LBound (discusse sopra).

Puoi anche contare il numero di elementi inseriti (o elementi che soddisfano determinati criteri) scorrendo l'array.

Questo esempio scorrerà un array di oggetti e conterà il numero di stringhe non vuote trovate nell'array:

123456789101112131415

Sub ArrayLoopandCount()Dim strNames (da 1 a 4) come stringaDim i As Long, n As LongstrNames(1) = "Shelly"strNames(2) = "Steve"Per i = LBound(strNames) To UBound(strNames)Se strNames(i) "" Thenn = n + 1Finisci seAvanti ioMsgBox n & " sono stati trovati valori non vuoti."Fine sottotitolo

Programmazione VBA | Il generatore di codice funziona per te!

Rimuovi duplicati

Ad un certo punto, potresti voler rimuovere i duplicati da un array. Sfortunatamente, VBA non ha una funzione integrata per farlo. Tuttavia, abbiamo scritto una funzione per rimuovere i duplicati da un array (è troppo lungo da includere in questo tutorial, ma visita il link per saperne di più).

Filtro

La funzione di filtro VBA consente di filtrare un array. Lo fa creando un nuovo array con solo i valori filtrati. Di seguito è riportato un rapido esempio, ma assicurati di leggere l'articolo per ulteriori esempi per esigenze diverse.

1234567891011121314

Sottofiltro_Partita()"Definisci matrice"Dim strNames As VariantstrNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")"Filtro matrice"Dim strSubNames As VariantstrSubNames = Filter(strNames, "Smith")"Conteggio array filtrato"MsgBox "Trovato " & UBound(strSubNames) - LBound(strSubNames) + 1 & " nomi."Fine sottotitolo

IsArray Function

Puoi verificare se una variabile è un array usando la funzione IsArray:

123456789101112

Sub IsArrayEx()'Crea array con posizioni 1,2,3'Dim arrDemo1(3) As String'Crea una variabile stringa regolareDim str As StringMsgBox IsArray(arrDemo1)MsgBox IsArray(str)Fine sottotitolo

Unisciti all'array

Puoi "unire" rapidamente un intero array insieme alla funzione Join:

123456789101112

Sub Array_Unisci()Dim strNames (da 1 a 4) come stringaDim joinNames As StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Giuseppe"joinNames = Join(strNames, ", ")MsgBox joinNamesFine sottotitolo

Programmazione VBA | Il generatore di codice funziona per te!

Dividi stringa in array

La funzione VBA Split dividerà una stringa di testo in una matrice contenente i valori della stringa originale. Vediamo un esempio:

123456789

Sub Array_Split()Dim Names() As StringDim joinNames As StringjoinNames = "Shelly,Steve,Nema,Jose"Nomi = Dividi(Nomi uniti, ",")Nomi MsgBox(1)Fine sottotitolo

Qui dividiamo questa stringa di testo "Shelly, Steve, Nema, Jose" in un array (dimensione 4) utilizzando un delimitatore di virgola (,").

Matrice Const

Un array non può essere dichiarato come una costante in VBA. Tuttavia, puoi aggirare questo problema creando una funzione da utilizzare come Array:

123456789

'Definisci ConstantArrayFunzione ConstantArray()ConstantArray = Array(4, 12, 21, 100, 5)Fine funzione' Recupera valore ConstantArraySotto valori di recupero()MsgBox ConstantArray(3)Fine sottotitolo

Copia matrice

Non esiste un modo integrato per copiare un array utilizzando VBA. Sarà invece necessario utilizzare un ciclo per assegnare i valori da un array a un altro.

12345678910111213141516171819

Sub CopyArray()Dim Arr1 (da 1 a 100) come a lungoDim Arr2 (da 1 a 100) come a lungoDim I As Long"Crea matrice1"Per i = 1 a 100Arr1(i) = iAvanti io'CopiaArray1 in Array2'Per i = 1 a 100Arr2(i) = Arr1(i)Avanti ioMsgBox Arr2(74)Fine sottotitolo

Trasporre

Non esiste una funzione VBA incorporata per consentire di trasporre un array. Tuttavia, abbiamo scritto una funzione per trasporre un array 2D. Leggi l'articolo per saperne di più.

Programmazione VBA | Il generatore di codice funziona per te!

Matrice di ritorno della funzione

Una domanda comune che gli sviluppatori VBA hanno è come creare una funzione che restituisca un array. Penso che la maggior parte delle difficoltà vengano risolte utilizzando Variant Array. Abbiamo scritto un articolo sull'argomento: VBA Function Return Array.

Utilizzo di array in Access VBA

La maggior parte degli esempi di array sopra funzionano esattamente allo stesso modo in Access VBA come in Excel VBA. L'unica differenza principale è che quando si desidera popolare un array utilizzando i dati di Access, è necessario eseguire il ciclo dell'oggetto RecordSet anziché dell'oggetto Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess()In caso di errore Riprendi AvantiDim strNames() As StringDim I As LongDim iCount As LongDim dbs come databaseDim prima come RecordsetImposta dbs = CurrentDbSet rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)con prima.SpostaUltimo.Sposta primaiCount = .RecordCountReDim strNames(1 in iCount)Per i = 1 per iCountstrNames(i) = rst.Fields("ClientName").Sposta AvantiAvanti ioTermina conprimo.ChiudiImposta primo = NienteImposta dbs = NienteFine sottotitolo

Tutorial sugli array
Array Mega-Guida	sì
Ottieni la dimensione dell'array
Cancella matrice
Matrice di filtri
Trasposizione matrice
Matrice di ritorno della funzione
Rimuovi duplicati