Quando si lavora con Excel, la maggior parte del tempo viene speso nell'area del foglio di lavoro, occupandosi di celle e intervalli.
E se vuoi automatizzare il tuo lavoro in Excel usando VBA, devi sapere come lavorare con celle e intervalli usando VBA.
Ci sono molte cose diverse che puoi fare con gli intervalli in VBA (come selezionare, copiare, spostare, modificare, ecc.).
Quindi, per trattare questo argomento, suddividerò questo tutorial in sezioni e ti mostrerò come lavorare con celle e intervalli in Excel VBA usando esempi.
Iniziamo.
Tutti i codici che menziono in questo tutorial devono essere inseriti nell'editor VB. Vai alla sezione "Dove inserire il codice VBA" per sapere come funziona.Se sei interessato a imparare VBA nel modo più semplice, dai un'occhiata a my Formazione online su VBA di Excel.
Selezione di una cella/intervallo in Excel utilizzando VBA
Per lavorare con celle e intervalli in Excel utilizzando VBA, non è necessario selezionarlo.
Nella maggior parte dei casi, è meglio non selezionare celle o intervalli (come vedremo).
Nonostante ciò, è importante leggere questa sezione e capire come funziona. Questo sarà cruciale nel tuo apprendimento VBA e molti concetti trattati qui verranno utilizzati in questo tutorial.
Quindi iniziamo con un esempio molto semplice.
Selezione di una singola cella utilizzando VBA
Se vuoi selezionare una singola cella nel foglio attivo (diciamo A1), puoi utilizzare il codice seguente:
Sub SelectCell() Range ("A1"). Seleziona End Sub
Il codice sopra ha la parte obbligatoria "Sub" e "End Sub" e una riga di codice che seleziona la cella A1.
Range ("A1") indica a VBA l'indirizzo della cella a cui vogliamo fare riferimento.
Selezionare è un metodo dell'oggetto Range e seleziona le celle/l'intervallo specificato nell'oggetto Range. I riferimenti di cella devono essere racchiusi tra virgolette.
Questo codice mostrerebbe un errore nel caso in cui un foglio grafico sia un foglio attivo. Un foglio grafico contiene grafici e non è molto utilizzato. Poiché non contiene celle/intervalli, il codice sopra non può selezionarlo e finirebbe per mostrare un errore.
Si noti che poiché si desidera selezionare la cella nel foglio attivo, è sufficiente specificare l'indirizzo della cella.
Ma se vuoi selezionare la cella in un altro foglio (diciamo Foglio2), devi prima attivare Foglio2 e quindi selezionare la cella al suo interno.
Sub SelectCell() Worksheets ("Sheet2"). Attiva intervallo ("A1"). Seleziona End Sub
Allo stesso modo, puoi anche attivare una cartella di lavoro, quindi attivare un foglio di lavoro specifico in essa e quindi selezionare una cella.
Sub SelectCell() Workbooks ("Book2.xlsx"). Worksheets ("Sheet2"). Attiva intervallo ("A1"). Seleziona End Sub
Tieni presente che quando fai riferimento alle cartelle di lavoro, devi utilizzare il nome completo insieme all'estensione del file (.xlsx nel codice sopra). Nel caso in cui la cartella di lavoro non sia mai stata salvata, non è necessario utilizzare l'estensione del file.
Ora, questi esempi non sono molto utili, ma vedrai più avanti in questo tutorial come possiamo usare gli stessi concetti per copiare e incollare celle in Excel (usando VBA).
Proprio come selezioniamo una cella, possiamo anche selezionare un intervallo.
Nel caso di un intervallo, potrebbe essere un intervallo di dimensioni fisse o un intervallo di dimensioni variabili.
In un intervallo di dimensioni fisse, sapresti quanto è grande l'intervallo e puoi utilizzare la dimensione esatta nel tuo codice VBA. Ma con un intervallo di dimensioni variabili, non hai idea di quanto sia grande l'intervallo e devi usare un po' di magia VBA.
Vediamo come farlo.
Selezione di un intervallo di dimensioni fisse
Ecco il codice che selezionerà l'intervallo A1: D20.
Sub SelectRange() Range ("A1: D20"). Seleziona End Sub
Un altro modo per farlo è utilizzare il codice seguente:
Sub SelectRange() Range("A1", "D20").Seleziona End Sub
Il codice sopra prende l'indirizzo della cella in alto a sinistra (A1) e l'indirizzo della cella in basso a destra (D20) e seleziona l'intero intervallo. Questa tecnica diventa utile quando si lavora con intervalli di dimensioni variabili (come vedremo quando la proprietà End sarà trattata più avanti in questo tutorial).
Se vuoi che la selezione avvenga in una cartella di lavoro diversa o in un foglio di lavoro diverso, devi dire a VBA i nomi esatti di questi oggetti.
Ad esempio, il codice seguente selezionerebbe l'intervallo A1: D20 nel foglio di lavoro Sheet2 nella cartella di lavoro Book2.
Sub SelectRange() Cartelle di lavoro ("Book2.xlsx"). Fogli di lavoro ("Foglio1"). Attiva intervallo ("A1: D20"). Seleziona End Sub
Ora, cosa succede se non sai quante righe ci sono. E se volessi selezionare tutte le celle che contengono un valore.
In questi casi, è necessario utilizzare i metodi mostrati nella sezione successiva (sulla selezione dell'intervallo di dimensioni variabili).
Selezione di un intervallo di dimensioni variabili
Esistono diversi modi per selezionare un intervallo di celle. Il metodo che scegli dipende da come sono strutturati i dati.
In questa sezione, tratterò alcune tecniche utili che sono davvero utili quando lavori con intervalli in VBA.
Seleziona Usando la proprietà CurrentRange
Nei casi in cui non sai quante righe/colonne contengono i dati, puoi usare la proprietà CurrentRange dell'oggetto Range.
La proprietà CurrentRange copre tutte le celle riempite contigue in un intervallo di dati.
Di seguito è riportato il codice che selezionerà la regione corrente che contiene la cella A1.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
Il metodo sopra è buono quando hai tutti i dati come una tabella senza righe/colonne vuote.
Ma nel caso tu abbia righe/colonne vuote nei tuoi dati, non selezionerà quelle dopo le righe/colonne vuote. Nell'immagine sottostante, il codice CurrentRegion seleziona i dati fino alla riga 10 poiché la riga 11 è vuota.
In questi casi, potresti voler utilizzare la proprietà UsedRange dell'oggetto foglio di lavoro.
Seleziona Usa proprietà Range usato
UsedRange ti consente di fare riferimento a tutte le celle che sono state modificate.
Quindi il codice seguente selezionerebbe tutte le celle utilizzate nel foglio attivo.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Nota che nel caso in cui tu abbia una cella lontana che è stata utilizzata, sarebbe considerata dal codice sopra e tutte le celle fino a quella cella utilizzata sarebbero selezionate.
Seleziona Usando la proprietà End
Ora, questa parte è davvero utile.
La proprietà Fine consente di selezionare l'ultima cella riempita. Ciò consente di imitare l'effetto del tasto freccia Ctrl giù/su o dei tasti Ctrl destro/sinistro.
Proviamo a capirlo usando un esempio.
Supponiamo di avere un set di dati come mostrato di seguito e di voler selezionare rapidamente le ultime celle riempite nella colonna A.
Il problema qui è che i dati possono cambiare e non sai quante celle sono riempite. Se devi farlo usando la tastiera, puoi selezionare la cella A1, quindi usare Ctrl + Freccia giù e selezionerà l'ultima cella riempita nella colonna.
Ora vediamo come farlo usando VBA. Questa tecnica è utile quando si desidera passare rapidamente all'ultima cella riempita in una colonna di dimensioni variabili
Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub
Il codice sopra passerebbe all'ultima cella riempita nella colonna A.
Allo stesso modo, puoi usare End(xlToRight) per saltare all'ultima cella piena di una riga.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub
Ora, cosa succede se vuoi selezionare l'intera colonna invece di saltare all'ultima cella piena.
Puoi farlo usando il codice qui sotto:
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub
Nel codice sopra, abbiamo usato il primo e l'ultimo riferimento della cella che dobbiamo selezionare. Non importa quante celle piene ci sono, il codice sopra selezionerà tutto.
Ricorda l'esempio sopra in cui abbiamo selezionato l'intervallo A1: D20 utilizzando la seguente riga di codice:
Intervallo ("A1", "D20")
Qui A1 era la cella in alto a sinistra e D20 era la cella in basso a destra nell'intervallo. Possiamo usare la stessa logica nella selezione di intervalli di dimensioni variabili. Ma poiché non conosciamo l'indirizzo esatto della cella in basso a destra, abbiamo usato la proprietà End per ottenerlo.
In Range("A1", Range("A1").End(xlDown)), "A1" si riferisce alla prima cella e Range("A1").End(xlDown) si riferisce all'ultima cella. Poiché abbiamo fornito entrambi i riferimenti, il metodo Select seleziona tutte le celle tra questi due riferimenti.
Allo stesso modo, puoi anche selezionare un intero set di dati che ha più righe e colonne.
Il codice seguente selezionerebbe tutte le righe/colonne riempite a partire dalla cella A1.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select End Sub
Nel codice sopra, abbiamo usato Range(“A1”).End(xlDown).End(xlToRight) per ottenere il riferimento della cella riempita in basso a destra del set di dati.
Differenza tra l'utilizzo di CurrentRegion e End
Se ti stai chiedendo perché utilizzare la proprietà End per selezionare l'intervallo pieno quando abbiamo la proprietà CurrentRegion, lascia che ti dica la differenza.
Con la proprietà End è possibile specificare la cella iniziale. Ad esempio, se hai i tuoi dati in A1:D20, ma la prima riga sono intestazioni, puoi usare la proprietà End per selezionare i dati senza le intestazioni (usando il codice seguente).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub
Ma CurrentRegion selezionerebbe automaticamente l'intero set di dati, incluse le intestazioni.
Finora in questo tutorial, abbiamo visto come fare riferimento a un intervallo di celle utilizzando modi diversi.
Ora vediamo alcuni modi in cui possiamo effettivamente usare queste tecniche per portare a termine del lavoro.
Copia celle / intervalli utilizzando VBA
Come ho detto all'inizio di questo tutorial, la selezione di una cella non è necessaria per eseguire azioni su di essa. Vedrai in questa sezione come copiare celle e intervalli senza nemmeno selezionarli.
Iniziamo con un semplice esempio.
Copiare una singola cella
Se vuoi copiare la cella A1 e incollarla nella cella D1, il codice seguente lo farebbe.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Nota che il metodo di copia dell'oggetto intervallo copia la cella (proprio come Control + C) e la incolla nella destinazione specificata.
Nel codice di esempio sopra, la destinazione è specificata nella stessa riga in cui si utilizza il metodo Copy. Se vuoi rendere il tuo codice ancora più leggibile, puoi utilizzare il codice seguente:
Sub CopyCell() Range("A1").Copia Destinazione:=Range("D1") End Sub
I codici sopra copieranno e incolleranno il valore e la formattazione/formule al suo interno.
Come avrai già notato, il codice sopra copia la cella senza selezionarla. Non importa dove ti trovi nel foglio di lavoro, il codice copierà la cella A1 e la incollerà su D1.
Inoltre, tieni presente che il codice precedente sovrascriverebbe qualsiasi codice esistente nella cella D2. Se vuoi che Excel ti faccia sapere se c'è già qualcosa nella cella D1 senza sovrascriverlo, puoi usare il codice qui sotto.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Vuoi sovrascrivere i dati esistenti", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1 ") End If End Sub
Copia di un intervallo di dimensioni fisse
Se vuoi copiare A1: D20 in J1: M20, puoi utilizzare il codice seguente:
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
Nella cella di destinazione, devi solo specificare l'indirizzo della cella in alto a sinistra. Il codice copierà automaticamente l'esatto intervallo copiato nella destinazione.
Puoi usare lo stesso costrutto per copiare i dati da un foglio all'altro.
Il codice seguente copierà A1: D20 dal foglio attivo a Sheet2.
Sub CopyRange() Range("A1:D20").Copia fogli di lavoro("Sheet2").Range("A1") End Sub
Quanto sopra copia i dati dal foglio attivo. Quindi assicurati che il foglio che contiene i dati sia il foglio attivo prima di eseguire il codice. Per sicurezza, puoi anche specificare il nome del foglio di lavoro durante la copia dei dati.
Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
La cosa buona del codice sopra è che, indipendentemente dal foglio attivo, copierà sempre i dati da Sheet1 e li incollerà in Sheet2.
È inoltre possibile copiare un intervallo denominato utilizzando il suo nome anziché il riferimento.
Ad esempio, se disponi di un intervallo denominato "SalesData", puoi utilizzare il codice seguente per copiare questi dati in Sheet2.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Se l'ambito dell'intervallo denominato è l'intera cartella di lavoro, non è necessario essere nel foglio che contiene l'intervallo denominato per eseguire questo codice. Poiché l'intervallo denominato è limitato alla cartella di lavoro, è possibile accedervi da qualsiasi foglio utilizzando questo codice.
Se hai una tabella con il nome Table1, puoi usare il codice seguente per copiarla in Sheet2.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub
Puoi anche copiare un intervallo in un'altra cartella di lavoro.
Nell'esempio seguente, copio la tabella Excel (Tabella1) nella cartella di lavoro Book2.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Questo codice funzionerebbe solo se la cartella di lavoro è già aperta.
Copia di un intervallo di dimensioni variabili
Un modo per copiare intervalli di dimensioni variabili consiste nel convertirli in intervalli denominati o in una tabella Excel e utilizzare i codici come mostrato nella sezione precedente.
Ma se non puoi farlo, puoi usare la proprietà CurrentRegion o End dell'oggetto range.
Il codice seguente copierà la regione corrente nel foglio attivo e la incollerà in Sheet2.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Se vuoi copiare la prima colonna del tuo set di dati fino all'ultima cella riempita e incollarla in Sheet2, puoi usare il codice seguente:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub
Se vuoi copiare sia le righe che le colonne, puoi utilizzare il codice seguente:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub
Nota che tutti questi codici non selezionano le celle durante l'esecuzione. In generale, troverai solo una manciata di casi in cui devi effettivamente selezionare una cella/intervallo prima di lavorarci.
Assegnazione di intervalli alle variabili oggetto
Finora abbiamo utilizzato l'indirizzo completo delle celle (come Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)).
Per rendere il tuo codice più gestibile, puoi assegnare questi intervalli alle variabili oggetto e quindi utilizzare tali variabili.
Ad esempio, nel codice seguente, ho assegnato l'intervallo di origine e di destinazione alle variabili oggetto e quindi ho utilizzato queste variabili per copiare i dati da un intervallo all'altro.
Sub CopyRange() Dim SourceRange As Range Dim DestinationRange As Range Imposta SourceRange = Worksheets("Sheet1").Range("A1:D20") Imposta DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange Fine sotto
Iniziamo dichiarando le variabili come oggetti Range. Quindi assegniamo l'intervallo a queste variabili utilizzando l'istruzione Set. Una volta che l'intervallo è stato assegnato alla variabile, puoi semplicemente utilizzare la variabile.
Inserisci i dati nella cella vuota successiva (usando la casella di input)
È possibile utilizzare le caselle di input per consentire all'utente di inserire i dati.
Ad esempio, supponiamo di avere il set di dati di seguito e di voler inserire il record delle vendite, è possibile utilizzare la casella di input in VBA. Utilizzando un codice, possiamo assicurarci che riempia i dati nella riga vuota successiva.
Sub EnterData() Dim RefRange As Range Imposta RefRange = Range("A1").End(xlDown).Offset(1, 0) Imposta ProductCategory = RefRange.Offset(0, 1) Imposta Quantità = RefRange.Offset(0, 2 ) Imposta importo = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Product Category") Quantity.Value = InputBox("Quantità") Amount.Value = InputBox("Amount") End Sub
Il codice precedente utilizza la casella Input VBA per ottenere gli input dall'utente, quindi inserisce gli input nelle celle specificate.
Nota che non abbiamo usato riferimenti di cella esatti. Invece, abbiamo usato la proprietà End and Offset per trovare l'ultima cella vuota e riempire i dati in essa.
Questo codice è tutt'altro che utilizzabile. Ad esempio, se inserisci una stringa di testo quando la casella di input richiede la quantità o l'importo, noterai che Excel lo consente. È possibile utilizzare una condizione If per verificare se il valore è numerico o meno e quindi consentirlo di conseguenza.
Loop attraverso celle / intervalli
Finora abbiamo visto come selezionare, copiare e inserire i dati in celle e intervalli.
In questa sezione, vedremo come scorrere un insieme di celle/righe/colonne in un intervallo. Questo potrebbe essere utile quando si desidera analizzare ogni cella ed eseguire alcune azioni basate su di essa.
Ad esempio, se si desidera evidenziare ogni terza riga nella selezione, è necessario scorrere e controllare il numero di riga. Allo stesso modo, se vuoi evidenziare tutte le celle negative cambiando il colore del carattere in rosso, devi scorrere e analizzare il valore di ogni cella.
Ecco il codice che scorrerà le righe nelle celle selezionate ed evidenzierà le righe alternate.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selezione per ogni Myrow in Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Il codice sopra utilizza la funzione MOD per controllare il numero di riga nella selezione. Se il numero di riga è pari, viene evidenziato in colore ciano.
Ecco un altro esempio in cui il codice attraversa ogni cella ed evidenzia le celle che contengono un valore negativo.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Mycell As Range Imposta Myrange = Selezione per ogni Mycell in Myrange If Mycell < 0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Nota che puoi fare la stessa cosa usando la formattazione condizionale (che è dinamica e un modo migliore per farlo). Questo esempio ha solo lo scopo di mostrarti come funziona il ciclo con celle e intervalli in VBA.
Dove inserire il codice VBA
Ti chiedi dove va il codice VBA nella cartella di lavoro di Excel?
Excel ha un backend VBA chiamato editor VBA. Devi copiare e incollare il codice nella finestra del codice del modulo VB Editor.
Ecco i passaggi per farlo:
- Vai alla scheda Sviluppatore.
- Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
- Nel riquadro Project Explorer nell'editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice.Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
- Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
- Copia e incolla il codice nella finestra del modulo.
