Lavorare con gli intervalli denominati

Nei nostri lavori siamo abituati a scrivere istruzioni del genere:

Range("A10").Select per selezionare la cella "A10" del foglio attivo. Tuttavia l'istruzione completa dovrebbe essere:

Application.ActiveWorkbook.ActiveSheet.Range("A10").Select.

Ma il VBA ci consente di sottintendere gli oggetti già noti o comunque già attivi.

E' come se dicessimo: "adesso mi ritiro in camera da letto per dormire".

E' ovvio che il nostro interlocutore capisce subito che ci stiamo ritirando

• nella camera da letto della nostra casa che sta
• nel nostro paese
• della provincia di appartenenza
• situato in un posto preciso dell'Italia
• che appartiene all'Europa
• che è uno dei continenti della Terra.

Ma se diciamo: "adesso prendo l'aereo e questa notte mi ritiro nella camera da letto per dormire", dovremmo quanto meno spiegare al nostro interlocutore in che continente, nazione, città, via siamo diretti e quale casa abbiamo scelto per poter avere a disposizione una camera da letto dove andare a dormire.

Ciò che ci permette di raggiungere o selezionare una determinata cella o intervallo di celle sono gli indici.

Per accedere alle celle utilizziamo i seguenti metodi:

1. Range("A1") che impiega come indici una lettera alfabetica ed un numero per indicare rispettivamente la coordinata di Colonna e di Riga
2. Cells(1, 1) che impiega come indici due numeri che si riferiscono rispettivamente alle coordinate di Riga e di Colonna
3. Cells(1) con un solo indice. In questo caso l'indice indica la posizione assoluta della cella nel foglio di lavoro o nell'intervallo
4. Cells senza alcun indice fa riferimento a tutte le celle del foglio di lavoro o dell'intervallo a cui ci stiamo riferendo
5. Worksheets(3).Range("C5") fa riferimento ad una determinata cella del terzo foglio
6. molto più complesso è lavorare con più cartelle. In alcuni casi è necessario
   1. aprire prima la nuova cartella
   2. leggere o scrivere il dato o i dati dai fogli e dalle celle della nuova cartella
   3. chiudere la cartella

ma questa è una eventualità che in questa sede non tratteremo perchè non amo lavorare in queste condizioni.

A seconda che usiamo le celle appartenenti all'intero foglio di lavoro o ad un intervallo di celle, gli indici che usiamo nelle nostre istruzioni ci restituiscono delle celle dislocate nelle posizioni più disparate.

Così per le seguenti istruzioni:

Range("K8").Select

ci restituisce la cella "K8" che si trova alla 11^ colonna e 8^ riga a partire dalla prima cella del foglio

Range("C3:K7").Range("K8").Select

ci restituisce la cella "M10" che si trova 10 colonne a destra e 7 righe in basso rispetto alla cella attiva dell'intervallo di origine che è la "C3"

Cells(15).Select

ci restituisce la cella "O1" che è la quindicesima cella del foglio

Range("C3:K7").Cells(15).Select

ci restituisce la cella "H4" che è la quindicesima cella rispetto a C3:K7 (il conteggio avviene in una sorta di corridoio lungo le colenne C e K

Ma esiste anchce un altro modo di lavorare e cioè riferendoci ad un determinato intervallo, anzichè all'intero foglio. In questi casi è senz'altro più conveniente denominare gli intervalli su cui intendiamo lavorare.

Gli intervalli denominati

Per lavorare con un intervallo denominato è necessario memorizzarlo in una variabile oggetto. E' appunto su questo argomento che verte la discussione odierna.

Questa assegnazione si esegue usando la parola chiave "Set": Set Intervallo = Range ......

Set Intervallo = Range("C3").CurrentRegion

Dopo questa assegnazione possiamo usare tranquillamente il nome dell'intervallo per eseguire qualsiasi operazione:

Intervallo.Select

anziché: Range(".....").Select.

oppure: Intervallo(1,5).Select

Ma ora vediamo un po' più da vicino come lavorare con gli intervalli denominati prendendo come esempio questa tabella.

L'utilità di lavorare con intervalli denominati sta nel fatto che ci si può riferire all'intervallo usando semplicemente 1 o 2 indici, anche senza riferimenti a Cells o Range.

Abbiamo già visto qualcosa del genere nell'articolo Offset - Resize con una tabella simile a questa. Ma ora rivediamo la tabella da un'altra prospettiva. Infatti abbiamo concluso quell'articolo calcolando le somme delle righe e delle colonne della tabella.

Definizione di righe o colonne

Questa volta vediamo come, da un intervallo che copre il Range D4:G8 (epurato delle righe e delle colonne di intestazione) possiamo trovare vari altri intervalli lungo le righe e le colonne.

Intanto, a partire dalla cella C3 impostiamo la zona che ci interessa al netto dalle intestazioni di riga e di colonna:

rileviamo nuovamente il numero di righe e di colonne dell'intervallo trovato:

Da questo punto possiamo rilevare ed impostare qualsiasi altro intervallo che riesca a coprire ogni singola riga o colonna all'interno della nostra tabella:

veniamo alla scelta delle singole righe e colonne:

C'è un altro modo per scegliere le righe e le colonne da un determinato intervallo. Come potete osservare, le istruzioni sono più semplici, tuttavia il loro uso è leggermente diverso dall'uso della prima serie di istruzioni.

Mentre con la prima serie di istruzioni si possono scegliere le singole celle dell'intervallo, con questa seconda serie è possibile scegliere l'intera colonna.

Alcuni piccoli esempi: Ricerca di una tabella in un foglio di lavoro

Può capitare, quando si mette mano a scrivere un programma più o meno complesso, di ignorare la posizione della nostra tabella o, addirittura, di più tabelle.

Nelle routines che presento presumo di non conoscere la precisa dislocazione degli Intervalli su cui dovrò lavorare, né le sue dimensioni. In questo esempio farò ricorso ad alcune Proprietà e metodi, già viste in Selezioni interessanti e precise e ad altre ancora, poco note, ma molto utili in certe circostanze: Proprietà UsedRange, Proprietà CurrentRegion, Metodo Intersect, Metodo Union, Proprietà Areas.

Non mi dilungo qui a descrivere il loro uso. La guida in linea è più che esplicativa, ma se necessario, tornerò sull'argomento in un prossimo futuro.

Ricerca di una singola tabella o la prima di una serie di tabelle

Con questa routine, una volta individuata la tabella tramite la Proprietà "UsedRange" passo ad individuarne la prima cella non vuota. Trovata questa cella non vuota, aiutato dall'altra proprietà "CurrentRegion" riesco ad individuare tutta la tabella e dopo averla individuata la copio 3 righe sotto. Con la funzione IsEmpty verifichiamo se la cella è vuota. In questo caso continuiamo la ricerca fino a chè non incontriamo una prima cella che contenga un valore.

Come copiare una tabella

Qui vediamo alcuni metodi che si possono usare per copiare una tabella.

La tabella di esempio è questa:

Ora vediamo alcune tecniche di copia

Copia di valori e formato in un sol colpo

Cominciamo con un esempio che copia la nostra tabella tre colonne o tre righe dopo la stessa facendo uso della proprietà Copy che non si limita a copiare i soli dati di origine, ma anche il formato e persino le formule.

La sintassi di questa proprietà è: Origine.Copy Destinazione.

questa la tabella di esempio:

Questo il codice che si può usare:

In questo esempio

• prima si individua la dislocazione della tabella con la Proprietà UsedRange e successiva esplorazione delle celle alla ricerca di una cella non vuota
• si identifica la tabella tramite la Proprietà CurrentRegion
• quindi si effettua la copia dell'intera tabella

Per la copia della tabella ho usato le due righe evidenziate che effettuano

• una copia la tabella 3 righe sotto la tabella originaria (Offset(Intervallo.Rows.Count + 3, 0))
• una copia la tabella 3 colonne più a destra della tabella originaria (Offset(0, Intervallo.Columns.Count + 3))

E' ovvio che in una situazione reale si userà una sola di queste due istruzioni e dopo il Rows.Count o il Columns.Count si indicherà il numero di righe o colonne desiderato.

Copia di valori e formato in un altro foglio

Il metodo è simile a quello usato precedentemente ma incolliamo la tabella a partire dalla cella "C3" del quarto foglio

Taglia e copia in altra posizione o in altro foglio

La tecnica è simile alle due appena viste:

Anche qui ci sono due istruzioni evidenziate ed è ovvio che in pratica solo una di esse viene usata:

• la prima taglia la tabella dalla zona originaria e la incolla in un'altra parte dello stesso foglio
• la seconda la sposta in un altro foglio

Copia dei soli valori in un sol colpo

In questo esempio copiamo a fianco della tabella originale oppure in un altro foglio solo i valori di questa tabella. La copia avviene in un sol colpo.

Non occorre usare i metodi Copy e Cut ma una semplice assegnazione, tuttavia occorre che l'intervallo che deve ricevere i dati abbia le stesse dimensioni dell'intervallo di origine.

Come il solito questa volta ho messo nella stessa routine ben quattro metodi diversi dei quali, normalmente, ne viene usato uno solo:

• la copia dei valori nello stesso foglio spostato di tre righe sotto la tabella originale (in questo caso è sufficiente spostarsi con la Proprietà Offset)
• la copia in un foglio diverso la copia ma nella stessa posizione della tabella originale( in questo caso dobbiamo leggere le coordinate dell'intervallo tramite la Proprietà Address)
• la copia nello stesso foglio ma a partire da una ben precisa cella (in questo caso dobbiamo conoscere le dimensioni dell'intervallo leggendo le proprietà Rows.Count e Columns.Count) per ricostruire un intervallo delle stesse dimensioni della tabella di origine. La sintassi è la seguente incapsulata nel costrutto With ... End With:
  .Range(Cells(1, 1), Cells(Righe, Colonne)) = Intervallo.Value ' attenzione al punto prima di .Range
• la copia in un altro foglio a partire da una determinata cella (anche in questo caso occorre conoscere, come nel punto precedente, le dimensioni dell'intervallo di origine). La sintassi usata è la seguente incapsulata nel costrutto With ... End With:
  Range(.Cells(1, 1), .Cells(Righe, Colonne)) = Intervallo.Value ' attenzione ai punti prima di .Cells

Mi sembra che il discorso sugli intervalli possa essere concluso qui. Naturalmente il discorso sarebbe ben più ampio, ma se riusciamo ad assimilare questi concetti penso che possiamo superare qualsiasi altra problematica legata a questa. Magari non sempre potremmo ottenere quel che vorremmo con le istruzioni che desideriamo. Basta sapere che se abbiamo difficoltà a raggiungere il nostro obbiettivo con una determinata istruzione, c'è sempre un metodo alternativo per aggirare l'ostacolo. Magari non ci piacerà e potremmo non essere soddisfatti. Ma alla fine quel che conta è poter dire: questo l'ho fatto io.

Buon lavoro.