Riferimenti e definizione di un Rif.to - Range : Celle & Intervalli - come identificarli. - dal 04/09/04 pagina vista: volte

Un foglio di lavoro di Excel, sappiamo tutti che è formato da celle organizzate in righe e colonne. Sappiamo anche che nelle formule o nelle macro, per utilizzare una cella o il suo contenuto, dobbiamo indicare ad Excel di quale cella si tratta. Per identificare la cella, usiamo l'accoppiata "lettera di colonna-numero di riga". Questo è necessario altrimenti Excel non capirebbe a quale cella (o a quale dato) intendiamo riferirci. Usiamo quindi la sintassi, ad esempio,  B2 che identifica la cella che si trova all'intersezione della colonna B con la riga n. 2 . Questa sintassi, che si definisce in "Stile A1", rappresenta l'identificativo della cella ed altro non è che il Riferimento alla cella. Quando si legge nella guida in linea o in istruzioni sulla materia, il termine "riferimento", ora ne conosciamo il significato.

il Riferimento ad una o più celle rappresenta quindi il loro "indirizzo" (in inglese: Address) sul foglio di lavoro, necessario per Excel per sapere a quale cella (o celle) stiamo "mirando" nelle nostre formule o istruzioni macro.

Stabilito questo semplice concetto, passiamo ad esaminare come utilizzare il vba per definire la sintassi del riferimento ad una cella o un insieme di celle (Intervallo). Prima è necessario assimilare anche che cosa è un Oggetto Range, il fatidico Range che tanto spesso usiamo dando per scontato di sapere cosa è. Per il VBA il Range (dall' inglese= Intervallo) identifica una cella o un insieme (intervallo) di celle, o una riga o una colonna. Un intervallo può essere una qualsiasi selezione rettangolare di celle, oppure una selezione discontinua di celle. Vediamo degli esempi:

  • esempio 1 - la cella B2 è il Range("B2")

  • esempio 2 - l'intervallo di celle che partono dalla B2 e fino alla D3 (zona gialla) è il Range("B2:D3")

esempio 1
  A B C D E
1          
2          
3          
4          

esempio 2
  A B C D E
1          
2          
3          
4          

  • esempio 3 - tutta la riga 2 è il Range("2 : 2") o tutta la colonna D è il Range("D : D")

  • esempio 4 - tutte le celle gialle, insieme formano un intervallo, sono il Range("B2 ; C3 ; D4") sintassi usata per il foglio di lavoro, ma per il VBA scritto così genera un errore perchè la sintassi in VBA NON prevede il punto e virgola ma la virgola ( , ) e andrà così compilato: Range("B2 , C3 , D4").

esempio 3
  A B C D E
1          
2          
3          
4          

esempio 4
  A B C D E
1          
2          
3          
4          

Come si nota, la sintassi prevede che si indichi la parola Range e tra parentesi tonde e doppi apici, il riferimento alla cella o, se intervallo, l'uso del separatore di intervallo ( : ) due punti tra i due riferimenti, Ma nel caso di zone discontinue (insiemi di celle discontinue), esempio 5, la sintassi sarebbe la seguente: Range("A1:C1,A3:C4,E1:E4") cioè uso del separatore ( : ) di intervallo tra i riferimenti alle celle contigue, e la virgola ( , ) come concatenatore di intervalli, meglio definito come operatore Unione (la virgola). Esiste poi un'altro operatore , uno spazio (tra gli intervalli), che è l'operatore di Intersezione, Range("A1:D3  B2:E3"), vedi esempio 6, che equivale al Range("B2:D3") (area verde chiaro).

esempio 5
  A B C D E
1          
2          
3          
4          

esempio 6
  A B C D E
1          
2          
3          
4          

Proseguendo in questo articolo, che non può essere completo nè tantomeno esaustivo (sui libri che trattano il vba, gli argomenti Range & simili (Cells, CurrentRegion, UsedRange, ecc. ) occupano diverse pagine), ci limiteremo a descrivere alcuni modi per identificare celle e intervalli.

La sintassi che utilizza Range più il riferimento cella o all'intervallo, tra parentesi tonde e doppi apici, può essere sostituito, con evidente risparmio nello scrivere, dal riferimento alla cella o intervallo racchiuso tra parentesi quadre:

  • [A1].Select   equivale a    Range("A1").Select

  • [A1:A5].Select   equivale a  Range("A1:A5").Select

Le celle o intervalli possono essere identificati oltre che con la sintassi Range, con la sintassi Cells. Cells è l'insieme di tutte le celle di un foglio di lavoro, e come nel linguaggio parlato per identificare una cella diremmo:"la cella della riga 2 all'incrocio con la colonna 3" per identificare la cella C2, anche la sintassi Cells usa lo stesso sistema. Da notare che mentre nell'uso di Range si indica per prima la colonna e poi il numero di riga, con Cells si inverte la notazione: prima la riga e poi la colonna:

  • Cells(1, 1) - la cella della riga 1, colonna 1 ed equivale a Range("A1") opp. [A1]

  • Cells(2, 5) - la cella della riga 2, colonna 5 ed equivale a Range("E2") opp. [E2]

E ci siamo avvicinati ad altri due "oggetti" che servono per identificare celle o aree sul foglio di lavoro: Rows (righe) e Columns (colonne). Sono oggetti che vengono identificati dal numero "Indice" che li identifica sul foglio di lavoro: esistono 65536 righe e 255 colonne. E' il loro "Indice" che viene utilizzato nella sintassi Cells. L'utilizzo di Cells al posto di Range si rivela particolarmente utile quando si creano Cicli per "Spazzolare" determinate aree, potendo rendere variabile sia i numero indice di riga o di colonna, o entrambe, nella sintassi Cells. Un istruzione del genere:

  • Row = 2

  • For N = Row To 10

  • Cells(N, 2) = "Ciao"

  • Next

rende variabile il numero di riga rappresentato da N, che parte dalla riga (Row) 2, e tramite il ciclo For Next si incrementa di una valore ad ogni ciclo fino a 10. Quindi avremo ad ogni ciclo l'equivalente di Cells(2, 2), Cells(3, 2), Cells(4, 2), Cells(5, 2) ecc. ecc fino a Cells(10, 2) che essendo il limite massimo del ciclo, termina. Come si nota, cambia il numero di riga mentre la colonna rimane fissa ( la 2 ). L'impiego del ciclo For Next è solo un'esempio per mostrare come sia più facile identificare una cella usando la sintassi Cells piuttosto che Range quando si devono usare riferimenti variabili a celle.

Vediamo ora come identificare un intervallo di celle usando la sintassi Cells. Senza addentrarci in definizioni di "Oggetti" e "Proprietà" sia di Range che di Cells, diciamo che il connubio tra le proprietà Range e Cells ci consente di ottenere risultati interessanti, vediamo qualche esempio di sintassi:

  • Range(Cells(1, 2), Cells(3, 4)).Select - seleziona tutte le celle comprese tra B1 (Cells(1, 2)) e D3 (Cells(3, 4)). Come si nota, per indicare l'intervallo tra le celle Cells, non si usa i due punti (:), ma una virgola (,)

  • Range([A1], Cells(20, 5)).Font.Bold = True - si imposta il carattere grassetto a tutte le celle che vanno dalla cella A1 alla cella E20.

  • With Range("A1")

  •     Range(.Cells(1), .Cells(20, 5)).Select

  • End With  -  in questo esempio si imposta con With una cella iniziale (la A1) e poi si fa la selezione delle celle che vanno dalla cella iniziale ( .Cells(1) ) fino alla cella E20. Da notare il punto ( . ) che precede Cells, necessario quando si usa With. (vedi articolo "Istruzione With" sull'altro sito, sezione vba).

La sintassi appena vista ci torna utile quando vogliamo selezionare un intervallo SENZA conoscere la cella finale. E' il caso ad esempio di un elenco che iniziando dalla cella A1, e venendo aggiornato con frequenza aggiungendo dati nelle righe, varia continuamente la lunghezza rendendo impossibile predeterminare la cella finale. In questo caso ci serviremo della funzione End, che rintraccia l'ultima cella occupata, partendo dalla cella iniziale. Potremo usare sia Range che Cells, vediamo i casi:

  • Range(Range("A1"), Range("A1").End(xlDown)).Select o l'equivalente:

  • Range([A1], [A1].End(xlDown)).Select o l'equivalente:

  • Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select

in tutti e tre i casi l'intervallo si identifica indicando la cella iniziale, e la cella finale con End verso il basso (xlDown) partendo dalla cella iniziale. E verrà selezionata tutta l'area che contiene dati in celle contigue nella colonna A. Se si volesse scorrere le colonne alla ricerca dell'ultima colonna occupata, la sintassi sarebbe la stessa ma cambierebbe la direzione di ricerca con End, cioè andrebbe verso destra (o sinistra, dipende dove risiede la cella di inizio), nell'esempio sopra, sarebbe:

  • Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Select

Verrebbero selezionate tutte le colonne con dati a partire dalla colonna 1 e sulla riga 1.

Un altro modo per identificare intervalli detti anche nel linguaggio comune, aree di lavoro, oppure zona dati, o ancora Range di dati, o database, oppure tabella, insomma quando si voglia comprendere tutta un'area di celle occupate da dati, possiamo ricorrere alla proprietà UsedRange che restituisce un oggetto Range, che rappresenta l'intervallo utilizzato del foglio di lavoro specificato, indipendentemente dalla posizione delle celle contenenti dati, che potranno essere anche distanti tra loro. Vediamo subito un esempio:  solo in B2 e in D5 esistono dati, ma se usiamo questa semplice istruzione per selezionare l'intervallo con UsedRange, vedremo che verrà selezionata tutta l'area colorata verde chiaro:

  • Sub Selez()
    ActiveSheet.UsedRange.Select
    End Sub

  A B C D E F
1            
2    Pippo        
3            
4            
5       12/01/04    
6            
7            


Attenzione: come UsedRange, Excel considera TUTTA l'area che contiene dati, e ci comprende anche celle che contengano formule (anche se la formula non fornisce un risultato (e quindi noi non la vediamo, ma Excel si)), o celle in cui non esistono valori ma che portino commenti, oppure abbiano i bordi ingrossati. Vediamo due esempi, il primo con la cella E6 in cui è presente questa ipotetica formula =SE(A1<>"";A1;"")

  A B C D E F
1            
2    Pippo        
3            
4            
5       12/01/04    
6        

formula

 
7            

il secondo dove tutti i bordi siano evidenziati, anche se non contengono dati:

  A B C D E F
1            
2            
3            
4            
5            
6        

 
7            

In tutti questi casi l'area colorata corrisponde all' UsedRange. Se questo sarà corrispondente a ciò che cerchiamo di ottenere, sarà sufficiente impostare un'istruzione che sfrutti una variabile per restituire l'intervallo che ci interessa:

  • Set AreaMia = ActiveSheet.UsedRange  - e AreaMia corrisponderà, come nell'esempio della tabella sopra, all'intervallo B2:E6

Possiamo controllare, ed ottenere, anche i riferimenti dell'intervallo, con questa semplice istruzione, utilizzando Address:

  • X = ActiveSheet.UsedRange.Address
    MsgBox X
       -  e il messaggio sarà questo: $B$2:$E$6

Va da se che la variabile X corrisponderà all'intervallo dell'area dati, e sarà sufficiente usare la variabile per successive istruzioni come riferimento all'intervallo stesso, per esempio:

  • X = ActiveSheet.UsedRange.Address
    Range(X).Select
    - è necessario definire X come Range, pena lamentele del debugger.

oppure un'altro esempio per assegnare a tutta l'area con X reperita, il grassetto:

  • X = ActiveSheet.UsedRange.Address
    With Range(X).Font
    .Bold = True
    End With

Un altro modo per identificare intervalli, specie quando esistono celle vuote tra i vari dati, e la funzione End non la possiamo quindi usare perche si fermerebbe alla prima cella vuota, è quella di usare ancora UsedRange. Lo scopo è quello di definire l'intera area dati, compreso le celle vuote tra la prima cella contenente dati, e l'ultima contenente dati, poi contare quante sono le righe dell'area cosi individuata, e quante sono le colonne. Questi due numeri che otterremo, ci possono servire per utilizzare i numeri in una istruzione che usi il riferimento alle celle, vediamo un esempio sulla tabella precedente:

  • R = ActiveSheet.UsedRange.Rows.Count
    C = ActiveSheet.UsedRange.Columns.Count

Con R otterremo 5, e con C otterremo 4; infatti l'area è formata da 5 righe e 4 colonne, ma ATTENZIONE: questi sono numeri che identificano SOLO la dimensione dell'area, NON TENGONO CONTO delle righe e/o delle colonne che intercorrono tra l'area e l'inizio foglio (la riga1 e la colonna 1), per cui nell'istruzione basata sui riferimenti a righe e colonne, ANDRANNO SOMMATI i numeri di quante righe e quante colonne distaccano l'area dall'inizio foglio. Quindi un' esempio sempre basato sull'ultima tabella, che si trova 1 riga e 1 colonna spostata rispetto all'inizio foglio, dovrà essere:

  • R = ActiveSheet.UsedRange.Rows.Count
    C = ActiveSheet.UsedRange.Columns.Count
    Range(Cells(2, 2), Cells(R + 1, C + 1)).Select
    - con Cells(2, 2) dobbiamo fornire la cella iniziale da cui parte l'intervallo, mentre per la cella finale usiamo C e R ai quali viene aggiunto 1, e quindi equivale ad aver scritto Cells(riga 6, colonna 5) che corrisponde infatti alla Cella E6, ultima dell'intervallo.

Quando le aree sono flottanti, cioè non sappiamo da dove comincia l'intervallo, conviene usare l'istruzione vista sopra con Address. Esistono anche altri modi di riferirsi e di intercettare areee e celle, ma per ora ci fermiamo qui.

Spero di avere reso un pò più comprensibili questi concetti.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org