Intervalli e celle in VBA
I fogli di calcolo di Excel archiviano i dati in Celle. Le celle sono disposte in Righe e Colonne. Ogni cella può essere identificata dal punto di intersezione della sua riga e colonna (Es. B3 o R3C2).
Un intervallo di Excel si riferisce a una o più celle (es. A3: B4)
Indirizzo cella
Notazione A1
Nella notazione A1, una cella è indicata dalla sua lettera di colonna (da A a XFD) seguita dal suo numero di riga (da 1 a 1.048.576).
In VBA puoi fare riferimento a qualsiasi cella usando il Oggetto intervallo.
| 123456789 | 'Fai riferimento alla cella B4 del foglio attualmente attivoIntervallo MsgBox ("B4")' Fare riferimento alla cella B4 del foglio denominato 'Dati'Fogli di lavoro MsgBox ("Dati"). Intervallo ("B4")' Fare riferimento alla cella B4 sul foglio denominato 'Dati' in un'altra cartella di lavoro OPEN' denominato 'I miei dati'MsgBox Cartelle di lavoro ("I miei dati"). Fogli di lavoro ("Dati"). Intervallo ("B4") |
Notazione R1C1
Nella notazione R1C1 una cella è indicata da R seguito dal numero di riga, quindi dalla lettera "C" seguita dal numero di colonna. es. B4 nella notazione R1C1 sarà riferito da R4C2. In VBA usi il Oggetto Celle per usare la notazione R1C1:
| 12 | ' Fare riferimento alla cella R[6]C[4] cioè D6Celle (6, 4) = "D6" |
Gamma di celle
Notazione A1
Per fare riferimento a più di una cella, utilizzare un ":" tra l'indirizzo della cella iniziale e l'indirizzo dell'ultima cella. Quanto segue farà riferimento a tutte le celle da A1 a D10:
| 1 | Intervallo ("A1: D10") |
Notazione R1C1
Per fare riferimento a più di una cella, utilizzare un "", tra l'indirizzo della cella iniziale e l'indirizzo dell'ultima cella. Quanto segue farà riferimento a tutte le celle da A1 a D10:
| 1 | Intervallo(Celle(1, 1), Celle(10, 4)) |
Scrivere alle cellule
Per scrivere valori in una cella o in un gruppo contiguo di celle, fare semplicemente riferimento all'intervallo, inserire un segno = e quindi scrivere il valore da memorizzare:
| 12345678910 | ' Memorizza F5 nella cella con indirizzo F6Intervallo ("F6") = "F6"' Memorizza E6 nella cella con indirizzo R[6]C[5] cioè E6Celle(6, 5) = "E6"' Memorizza A1:D10 nell'intervallo A1:D10Intervallo ("A1: D10") = "A1: D10"' oIntervallo(Celle(1, 1), Celle(10, 4)) = "A1:D10" |
Leggere dalle cellule
Per leggere i valori dalle celle, fare semplicemente riferimento alla variabile per memorizzare i valori, inserire un segno = e quindi fare riferimento all'intervallo da leggere:
| 1234567891011 | Dim val1Dim val2'Leggi dalla cella F6val1 = Intervallo ("F6")'Leggi dalla cella E6val2 = Celle(6, 5)MsgBox val1Msgbox val2 |
Note: Per memorizzare i valori da un intervallo di celle, è necessario utilizzare una matrice invece di una semplice variabile.
Cellule non contigue
Per fare riferimento a celle non contigue utilizzare una virgola tra gli indirizzi delle celle:
| 123456 | ' Memorizza 10 nelle celle A1, A3 e A5Intervallo ("A1, A3, A5") = 10' Memorizza 10 nelle celle A1:A3 e D1:D3)Intervallo ("A1: A3, D1: D3") = 10 |
Intersezione di cellule
Per fare riferimento a celle non contigue utilizzare uno spazio tra gli indirizzi di cella:
| 123 | ' Memorizza 'Col D' in D1:D10' che è Comune tra A1:D10 e D1:F10Intervallo ("A1: D10 D1: G10") = "Col D" |
Offset da una cella o intervallo
Utilizzando la funzione Offset, puoi spostare il riferimento da un determinato intervallo (cella o gruppo di celle) del numero_di_righe e del numero_di_colonne specificati.
Sintassi offset
Intervallo.Offset(numero_di_righe, numero_di_colonne)
Offset da una cella
| 12345678910111213141516 | ' OFFSET da una cella A1'Fai riferimento alla cella stessa' Sposta 0 righe e 0 colonneIntervallo ("A1"). Offset (0, 0) = "A1"' Sposta 1 riga e 0 colonneIntervallo ("A1"). Offset (1, 0) = "A2"' Sposta 0 righe e 1 colonneIntervallo ("A1"). Offset (0, 1) = "B1"' Sposta 1 riga e 1 colonnaIntervallo ("A1"). Offset (1, 1) = "B2"' Sposta 10 righe e 5 colonneIntervallo ("A1"). Offset (10, 5) = "F11" |
Offset da un intervallo
| 123 | ' Sposta il riferimento all'intervallo A1: D4 di 4 righe e 4 colonne' Il nuovo riferimento è E5:H8Intervallo("A1:D4").Offset(4,4) = "E5:H8" |
Impostazione del riferimento a un intervallo
Per assegnare un intervallo a una variabile di intervallo: dichiarare una variabile di tipo Intervallo, quindi utilizzare il comando Set per impostarla su un intervallo. Si noti che è necessario utilizzare il comando SET poiché RANGE è un oggetto:
| 12345678 | 'Dichiara una variabile RangeDim myRange come Range' Imposta la variabile nell'intervallo A1: D4Imposta mioIntervallo = Intervallo ("A1: D4")' Stampa $A$1:$D$4MsgBox myRange.Address |
Ridimensiona un intervallo
Il metodo di ridimensionamento dell'oggetto Range modifica la dimensione dell'intervallo di riferimento:
| 1234567 | Dim myRange As Range'Intervallo da ridimensionareImposta mioIntervallo = Intervallo ("A1: F4")' Stampa $A$1:$E$10Debug.Print myRange.Resize(10, 5).Address |
La cella in alto a sinistra dell'intervallo ridimensionato è uguale alla cella in alto a sinistra dell'intervallo originale
Ridimensiona sintassi
Range.Resize(numero_di_righe, numero_di_colonne)
OFFSET vs Ridimensiona
L'offset non modifica le dimensioni dell'intervallo ma lo sposta del numero specificato di righe e colonne. Il ridimensionamento non modifica la posizione dell'intervallo originale ma modifica le dimensioni in base al numero specificato di righe e colonne.
Tutte le celle nel foglio
L'oggetto Cells fa riferimento a tutte le celle del foglio (1048576 righe e 16384 colonne).
| 12 | 'Cancella tutte le celle nei fogli di lavoroCelle.Cancella |
UsatoRange
La proprietà UsedRange fornisce l'intervallo rettangolare dalla cella utilizzata in alto a sinistra alla cella utilizzata in basso a destra del foglio attivo.
| 1234567 | Dim ws come foglio di lavoroImposta ws = ActiveSheet' $B$2:$L$14 se L2 è la prima cella con qualsiasi valore' e L14 è l'ultima cella con qualsiasi valore sul' foglio attivoDebug.Print ws.UsedRange.Address |
Regione attuale
La proprietà CurrentRegion fornisce l'intervallo rettangolare contiguo dalla cella in alto a sinistra alla cella utilizzata in basso a destra contenente la cella/l'intervallo di riferimento.
| 1234567891011 | Dim myRange As RangeImposta mioIntervallo = Intervallo("D4:F6")' Stampa $B$2:$L$14' Se c'è un percorso pieno da D4:F16 a B2 AND L14Debug.Print myRange.CurrentRegion.Address'Puoi fare riferimento anche a una singola cella di partenzaSet myRange = Range("D4") ' Stampa $B$2:$L$14 |
Proprietà dell'intervallo
Puoi ottenere l'indirizzo, il numero di riga/colonna di una cella e il numero di righe/colonne in un intervallo come indicato di seguito:
| 123456789101112131415161718192021 | Dim myRange As RangeImposta mioIntervallo = Intervallo ("A1: F10")' Stampa $A$1:$F$10Debug.Print myRange.AddressImposta mioIntervallo = Intervallo ("F10")' Stampa 10 per la riga 10Debug.Print myRange.Row' Stampa 6 per la colonna FDebug.Print myRange.ColumnImposta mioIntervallo = Intervallo ("E1: F5")'Stampa 5 per il numero di righe nell'intervalloDebug.Print myRange.Rows.Count'Stampa 2 per il numero di colonne nell'intervalloDebug.Print myRange.Columns.Count |
Ultima cella nel foglio
Puoi usare Conteggio.righe e Colonne.Conteggio proprietà con cellule oggetto per ottenere l'ultima cella del foglio:
| 1234567891011 | 'Stampa il numero dell'ultima riga'Stampa 1048576Debug.Print "Righe nel foglio: " & Rows.Count'Stampa il numero dell'ultima colonna'Stampa 16384Debug.Print "Colonne nel foglio: " & Columns.Count'Stampa l'indirizzo dell'ultima cella'Stampa $XFD$1048576Debug.Print "Indirizzo dell'ultima cella nel foglio: " & Cells(Rows.Count, Columns.Count) |
Ultimo numero di riga utilizzato in una colonna
La proprietà END ti porta all'ultima cella dell'intervallo e End(xlUp) ti porta alla prima cella utilizzata da quella cella.
| 123 | Dim lastRow As LonglastRow = Cells(Rows.Count, "A").End(xlUp).Row |
Ultimo numero di colonna utilizzato in una riga
| 123 | Dim lastCol As LonglastCol = Cells(1, Columns.Count).End(xlToLeft).Column |
La proprietà END ti porta all'ultima cella dell'intervallo e End(xlToLeft) ti porta a sinistra alla prima cella utilizzata da quella cella.
È inoltre possibile utilizzare le proprietà xlDown e xlToRight per passare alle prime celle utilizzate in basso oa destra della cella corrente.
Proprietà delle celle
Proprietà comuni
Ecco il codice per visualizzare le proprietà delle celle comunemente usate
| 12345678910111213141516171819202122 | Cella debole come intervalloImposta cella = Intervallo ("A1")cella.AttivaDebug.Stampa cella.Indirizzo'Stampa $A$1Debug.Stampa cella.Valore'Stampa 456' IndirizzoDebug.Stampa cella.Formula' Stampa =SOMMA(C2:C3)' CommentoDebug.Print cell.Comment.Text' StileDebug.Stampa cella.Stile"Formato cella"Debug.Print cell.DisplayFormat.NumberFormat |
Carattere cella
L'oggetto Cell.Font contiene le proprietà del carattere della cella:
| 1234567891011121314151617181920 | Cella debole come intervalloImposta cella = Intervallo ("A1")"Normale, corsivo, grassetto e grassetto corsivo"cell.Font.FontStyle = "Corsivo in grassetto"' Uguale acell.Font.Bold = Truecell.Font.Italic = True' Imposta il carattere su Couriercell.Font.FontStyle = "Corriere"'Imposta colore caratterecell.Font.Color = vbBlue' ocell.Font.Color = RGB(255, 0, 0)'Imposta dimensione caratterecella.Dimensione carattere = 20 |
Copia e incolla
Incolla tutto
Gli intervalli/celle possono essere copiati e incollati da una posizione all'altra. Il codice seguente copia tutte le proprietà dell'intervallo di origine nell'intervallo di destinazione (equivalente a CTRL-C e CTRL-V)
| 1234567 | "Copia semplice"Intervallo ("A1: D20"). CopiaFogli di lavoro ("Foglio2"). Intervallo ("B10"). Incolla'o' Copia dal foglio corrente al foglio denominato 'Foglio2'Intervallo ("A1: D20"). Destinazione copia: = Fogli di lavoro ("Foglio2"). Intervallo ("B10") |
Incolla speciale
Le proprietà selezionate dell'intervallo di origine possono essere copiate nella destinazione utilizzando l'opzione PASTEPECIAL:
| 123 | 'Incolla l'intervallo solo come valoriIntervallo ("A1: D20"). CopiaFogli di lavoro("Foglio2").Range("B10").PasteSpecial Paste:=xlPasteValues |
Ecco le possibili opzioni per l'opzione Incolla:
| 12345678910111213 | "Incolla tipi speciali"xlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulexlPasteFormuleENumeroFormatixlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Adatta automaticamente i contenuti
Le dimensioni di righe e colonne possono essere modificate per adattarle ai contenuti utilizzando il codice seguente:
| 12345 | 'Cambia le dimensioni delle righe da 1 a 5 per adattarle ai contenutiRighe("1:5").Adattamento automatico'Cambia le dimensioni delle colonne da A a B per adattarle ai contenutiColonne("A:B").AutoFit |
Altri esempi di gamma
Si consiglia di utilizzare Macro Recorder durante l'esecuzione dell'azione richiesta tramite la GUI. Ti aiuterà a capire le varie opzioni disponibili e come usarle.
Per ciascuno
È più facile scorrere un intervallo usando Per ciascuno costruire come mostrato di seguito:
| 123 | Per ogni cella nell'intervallo ("A1: B100")'Fai qualcosa con il cellulareCella successiva |
Ad ogni iterazione del ciclo una cella dell'intervallo viene assegnata alla variabile c e le istruzioni nel ciclo For vengono eseguite per quella cella. Il ciclo termina quando tutte le celle vengono elaborate.
Ordinare
Sort è un metodo dell'oggetto Range. È possibile ordinare un intervallo specificando le opzioni per l'ordinamento in Range.Sort. Il codice seguente ordinerà le colonne A: C in base alla chiave nella cella C2. L'ordinamento può essere xlAscending o xlDescending. Header:= xlYes dovrebbe essere usato se la prima riga è la riga di intestazione.
| 12 | Colonne("A:C").Chiave di ordinamento1:=Intervallo("C2"), _ordine1:=xlAscendente, Intestazione:=xlSì |
Trova
Trova è anche un metodo di Range Object. Trova la prima cella con contenuto che corrisponde ai criteri di ricerca e restituisce la cella come oggetto Range. ritorna Niente se non c'è corrispondenza.
Utilizzo Trova il prossimo metodo (o FindPrevious) per trovare l'occorrenza successiva (precedente).
Il codice seguente cambierà il carattere in "Arial Black" per tutte le celle nell'intervallo che iniziano con "John":
| 12345 | Per ogni c nell'intervallo ("A1: A100")Se c Mi piace "John*" Allorac.Font.Name = "Arial Black"Finisci seAvanti c |
Il codice seguente sostituirà tutte le occorrenze di "To Test" con "Passed" nell'intervallo specificato:
| 12345678910 | Con intervallo ("a1:a500")Imposta c = .Find("Da testare", LookIn:=xlValues)Se non c è niente allorafirstaddress = c.AddressFarec.Value = "Superato"Imposta c = .TrovaSuccessivo(c)Loop While Not c Is Nothing E c.Address firstaddressFinisci seTermina con |
È importante notare che è necessario specificare un intervallo per utilizzare FindNext. Inoltre è necessario fornire una condizione di arresto altrimenti il ciclo verrà eseguito per sempre. Normalmente l'indirizzo della prima cella che viene trovata viene memorizzato in una variabile e il ciclo viene interrotto quando si raggiunge nuovamente quella cella. È inoltre necessario verificare il caso in cui non venga trovato nulla per interrompere il ciclo.
Indirizzo intervallo
Usa Range.Address per ottenere l'indirizzo in A1 Style
| 123 | MsgBox Range ("A1: D10"). Indirizzo' oDebug.Print Range ("A1: D10"). Indirizzo |
Usa xlReferenceStyle (l'impostazione predefinita è xlA1) per ottenere indirizzi in stile R1C1
| 123 | MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)' oDebug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) |
Ciò è utile quando si tratta di intervalli archiviati in variabili e si desidera elaborare solo per determinati indirizzi.
Intervallo da array
È più rapido e semplice trasferire un intervallo in un array e quindi elaborare i valori. Dovresti dichiarare l'array come Variant per evitare di calcolare la dimensione richiesta per popolare l'intervallo nell'array. Le dimensioni dell'array sono impostate per corrispondere al numero di valori nell'intervallo.
| 123456789 | Dim DirArray As Variant' Memorizza i valori nell'intervallo nell'arrayDirArray = Range("a1:a5").Value'Ciclo per elaborare i valoriPer ogni c in DirArrayDebug.Print cProssimo |
Da matrice a intervallo
Dopo l'elaborazione è possibile riscrivere l'array in un intervallo. Per scrivere l'array nell'esempio sopra in un intervallo è necessario specificare un intervallo la cui dimensione corrisponde al numero di elementi nell'array.
Utilizzare il codice seguente per scrivere l'array nell'intervallo D1: D5:
| 123 | Range("D1:D5").Value = DirArrayRange("D1:H1").Value = Application.Transpose(DirArray) |
Si prega di notare che è necessario trasporre l'array se lo si scrive su una riga.
Somma intervallo
| 12 | SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))Debug.Stampa SumOfRange |
È possibile utilizzare molte funzioni disponibili in Excel nel codice VBA specificando Application.WorkSheetFunction. prima del nome della funzione come nell'esempio sopra.
Intervallo di conteggio
| 1234567 | 'Conta il numero di celle con i numeri nell'intervalloCountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))Debug.Print CountOfCells'Conta il numero di celle non vuote nell'intervalloCountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))Debug.Print CountOfNonBlankCells |
Scritto da: Vinamra Chandra
