Lavorare con gli intervalli denominati

Ultima modifica: 29-05-2016

 

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

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
cartella = ThisWorkbook.Path & "\"
NomeCompleto = cartella & "elenchi.xls" ' "elenchi.xls" è il secondo file su cui vogliamo lavorare
Set myBook = Workbooks.Open(Filename:=NomeCompleto)
Workbooks("Intervalli2.xls").Activate ' oppure
Workbooks(2).Activate ' se 2 è il numero di indice che si riferisce a "Intervalli2.xls"
B = Workbooks(2).Worksheets(7).Range("C5")
Workbooks(2).Close savechanges:=False

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.

  A B C D E F G H I J
1                                                                                          
2                    
3       Dettaglio Ingrosso Margine Tot orizz      
4     Alto 5,50 2,75 2,75        
5     Medio 4,50 2,25 2,25        
6     Basso 3,50 1,75 1,75        
7     Normale 4,35 2,38 1,97        
8     Tot vert              
9                    
10                    

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 esempio 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:

With Range("C3").CurrentRegion
Righe = .Rows.Count
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 1).Resize(Righe - 1, Colonne - 1)
End With

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

Righe = Intervallo.Rows.Count
Colonne = Intervallo.Columns.Count

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:

Set Riga = Range(Intervallo(1, 1), Intervallo(1, Colonne)) ' la prima riga
Set Riga = Range(Intervallo(Righe, 1), Intervallo(Righe, Colonne)) ' l'ultima riga
Set Colonna = Range(Intervallo(1, 1), Intervallo(Righe, 1)) ' la prima colonna
Set Colonna = Range(Intervallo(1, Colonne), Intervallo(Righe, Colonne)) 'l'ultima colonna

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.

Set Riga = Intervallo.Rows(1) '- restituisce l'indirizzo: "C3:K3"
Set Riga = Intervallo.Rows(Intervallo.Rows.Count) '- restituisce l'indirizzo: "C7:K7"
Set Colonna = Intervallo.Columns(1) '- restituisce l'indirizzo: "C3:C7"
Set Colonna = Intervallo.Columns(Intervallo.Columns.Count) '- restituisce l'indirizzo: "K3:K7"

 

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 esempio 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.

Sub CopiaTabella()
Dim Intervallo As Range, TabellaTemp As Range
Dim Posiz
Set TabellaTemp = ActiveSheet.UsedRange
Posiz = 1
While IsEmpty(TabellaTemp(Posiz))
Posiz = Posiz + 1
Wend
Set Intervallo = TabellaTemp(Posiz).CurrentRegion
Intervallo.Copy Intervallo.Offset(Intervallo.Rows.Count + 3, 0)
End Sub

 

Come copiare una tabella

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

La tabella di esempio è questa:

  A B C D E F G H I
1                  
2                  
3       Dettaglio Ingrosso Margine Tot orizz    
4     Alto 5,50 2,75 2,75 11,00 qui la formula =SOMMA(D4:F4)
5     Medio 4,50 2,25 2,25 9,00 oppure =SOMMA(RC[-3]:RC[-1])
6     Basso 3,50 1,75 1,75 7,00    
7     Normale 4,35 2,38 1,97 8,70    
8     Tot vert 17,85 9,13 8,72      
9       =SOMMA(D4:D7)          
        oppure          
        =SOMMA(R[-4]C:R[-1]C)          

 

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:

Sub CopiaTabella1()
Dim Intervallo As Range, TabellaTemp As Range
Dim Posiz
Set TabellaTemp = ActiveSheet.UsedRange
Posiz = 1
While IsEmpty(TabellaTemp(Posiz))
Posiz = Posiz + 1
Wend
Set Intervallo = TabellaTemp(Posiz).CurrentRegion
Intervallo.Copy Intervallo.Offset(Intervallo.Rows.Count + 3, 0)
Intervallo.Copy Intervallo.Offset(0, Intervallo.Columns.Count + 3)
End Sub

In questo esempio

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

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

Sub copia1Bis()
Dim Intervallo As Range, TabellaTemp As Range
Dim Posiz
Set TabellaTemp = ActiveSheet.UsedRange
Posiz = 1
While IsEmpty(TabellaTemp(Posiz))
Posiz = Posiz + 1
Wend
Set Intervallo = TabellaTemp(Posiz).CurrentRegion
Intervallo.Copy Worksheets("Foglio3").Range("C3")
End Sub

 

Taglia e copia in altra posizione o in altro foglio

La tecnica è simile alle due appena viste:

Sub Taglia2()
Dim Intervallo As Range, TabellaTemp As Range
Dim Posiz
Set TabellaTemp = ActiveSheet.UsedRange
Posiz = 1
While IsEmpty(TabellaTemp(Posiz))
Posiz = Posiz + 1
Wend
Set Intervallo = TabellaTemp(Posiz).CurrentRegion
'Intervallo.Cut Destination:=Intervallo.Offset(Intervallo.Rows.Count + 3, 0)
Intervallo.Cut Destination:=Worksheets("Foglio3").Range("C3")
End Sub

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

 

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.

Sub Copia4()
Dim Intervallo As Range, TabellaTemp As Range
Dim Posiz, Indirizzo, Righe, Colonne
Set TabellaTemp = ActiveSheet.UsedRange
Posiz = 1
While IsEmpty(TabellaTemp(Posiz))
Posiz = Posiz + 1
Wend
Set Intervallo = TabellaTemp(Posiz).CurrentRegion


Intervallo.Offset(Intervallo.Rows.Count + 3, 0) = Intervallo.Value


Indirizzo = Intervallo.Address
Worksheets("Foglio3").Range(Indirizzo) = Intervallo.Value


Righe = Intervallo.Rows.Count
Colonne = Intervallo.Columns.Count
With Range("E21")
.Range(Cells(1, 1), Cells(Righe, Colonne)) = Intervallo.Value
End With


With Worksheets("Foglio3").Range("E21")
Range(.Cells(1, 1), .Cells(Righe, Colonne)) = Intervallo.Value
End With
End Sub

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

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.