Lavorare con gli Intervalli

Ultima modifica: 27-05-2016

Come creare routines o funzioni universali

 

E' un uso comune, per esplorare un intervallo, comportarci in questo modo:

Dim R, C, A
For R = 1 To 10
For C = 1 To 5
A = Cells(R, C)
Next
Next

Con questo è chiaro che esploriamo un intervallo di celle che va dalla cella "A1" alla cella "E10".

Se l'intervallo su cui intendiamo lavorare si trova a partire dalla cella "K11", siccome la cella K11 è dislocata alla riga 11, colonna 11, i riferimenti alle celle dovrebbero essere modificati così:

Dim R, C
For R = 11 To 20
For C = 11 To 15
Cells(R, C).Select
Next
Next

Fin qui è tutto giusto e non c'è nulla di nuovo se siamo coscienti che la postazione da cui lavoriamo è lo stesso foglio di Excel. Come i più sanno, e qualche volta è stato ribadito anche in questa sede, se Range o Cells sono utilizzati senza un qualificatore, è sottinteso che questo qualificatore è da intendersi ActiveSheet o Worksheets o Sheets che è il padre di Range o Cells. Quindi le istruzioni appena viste sottintendono un riferimento a ActiveSheet o Sheets("fogliox").

Nota importante:

nell'ambiente in cui stiamo lavorando è molto importante la gerarchia dei vari oggetti che così possiamo schematicamente rappresentare:

Application rappresenta l'intera applicazione Microsoft Excel e comprende l'oggetto Workbook

Workbook rappresenta una cartella di lavoro di Microsoft Excel e comprende l'insieme Worksheet

Worksheet rappresenta un foglio di lavoro e comprende l'insieme Range

Range rappresenta una cella, una riga, una colonna, un intervallo di celle

Se gli oggetti gerarchicamente superiori sono attivi, nei nostri riferimenti all'oggetto di livello inferiore possiamo sottintenderli. Così, invece di scrivere la seguente istruzione che sintatticamente è valida:

Application.Workbooks("Mia_Cartella.xls").Sheets("Foglio4").Range("A5").Select

oppure quest'altra:

Application.Workbooks("Mia_Cartella.xls").Sheets("Foglio4").cells(5,1).Select

possiamo semplicemente scrivere:

Range("A5").Select

 

Lavorare tra gli intervalli

Ma quello che chi sta alle prime armi non sa e che molto spesso sfugge anche a chi sta avanti nella programmazione, è che possiamo lavorare sugli intervalli in un'altra maniera. Semplicemente cambiando la postazione da cui lavoriamo.

Se per lavorare ci spostiamo tra le celle del foglio anziché rimanere nel foglio ci accorgeremo che possiamo lavorare usando gli indici da 1 a quello che vogliamo, per esplorare le celle ovunque esse siano.

Se ci poniamo sulla cella "A1" e vogliamo esplorare un intervallo di 10 righe e 5 colonne a partire da questa possiamo usare questa:

With Range("A1")
Dim R, C
For R = 1 To 10
For C = 1 To 5
.Cells(R, C).Select ' da notare il punto prima di Cells
Next
Next
End With

Potremmo scrivere anche:

Range("A1").Select
ActiveCell.Cells(4, 3).Select

Ma non finisce qui. Potremmo definire un intervallo e lavorare con lo stesso intervallo:

Set Intervallo = Range("A1").CurrentRegion
Intervallo(2, 5).Select

Ma se ci spostassimo sulla cella "K11" anziché rimanere nella cella "A1" possiamo usare le stesse istruzioni (indici compresi) avendo cura di cambiare solo il riferimento "A1" con "K11".

With Range("K1")
Dim R, C
For R = 1 To 10
For C = 1 To 5
.Cells(R, C).Select ' da notare il punto prima di Cells
Next
Next
End With

Range("K1").Select
ActiveCell.Cells(4, 3).Select

Set Intervallo = Range("K1").CurrentRegion
Intervallo(2, 5).Select

 

Con questo tipo di notazione (Cells(R, C)) possiamo addirittura usare dei riferimenti con numeri negativi.

ActiveCell.Cells(-4, -3).Select

Non solo: possiamo usare anche la notazione A1. Così potremmo scrivere:

With Range("K11")
.Range("A1").Select 'sarà selezionata la K11 (stessa cella di partenza)
.Range("K11").Select 'sarà selezionata la U21 (che sta 11 righe più sotto e 11 colonne più a destra rispetto alla K11)
End With

Oppure:

ActiveCell.Range("A1").Select
ActiveCell.Range("K11").Select

ottenendo gli stessi spostamenti riferiti, però, alla cella attiva.

Attenzione:

Questi usi, tuttavia, sono deprecabili, a mio avviso, quindi sconsigliati, in quanto possono causare confusione e, a volte, errori nell'esecuzione della macro.

Se, usando la notazione R1C1, ci troviamo in prossimità della prima riga o della prima colonna del foglio, l'istruzione con valori negativi ci porterebbe fuori del foglio stesso, con relativa arrabbiatura del VBA.

L'altra notazione, la A1, io la sconsiglierei perchè potrebbe far insorgere confusione in chi legge il codice o effettuarne una manutenzione. Infatti siamo abituati a chiamare A1 la vera cella A1 ed a chiamare K11 la vera cella K11.

 

Possiamo lavorare usando uno o due indici.

Usando un solo indice

Diverso è il comportamento degli spostamenti se facciamo riferimento ad un intervallo che si estende su una sola colonna o ad un intervallo che si estende su più colonne. Gli spostamenti ottenuti usando un solo indice seguono una sorta di tunnel delimitato dalle colonne comprese nel'intervallo.

Vediamolo in pratica.

Primo esempio: un intervallo che si estende su una sola colonna

With Range("A1", Range("A1").End(xlDown))
For Indice = 1 To 100
.Cells(Indice).Select
Next
End With

Lavorando su una sola colonna, ad ogni passo del ciclo viene selezionata una cella sottostante quella precedente (A1, A2, A3, ecc.) a prescindere di quanto lungo sia l'intervallo indicato da Range("A1", Range("A1").End(xlDown)).

 

Secondo esempio: un intervallo che si estende su più colonne

With Range("A1").CurrentRegion
For Indice = 1 To 100
.Cells(Indice).Select
Next
End With

Lavorando su più colonne la selezione usata in questo frammento di codice si sposta in una sorta di tunnel delimitato dalle due colonne estreme dell'intervallo prolungandosi fino alla fine del ciclo:

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
ecc      

 

Usando due indici

Diverso è il modo di lavorare con due indici. Partendo da un intervallo che può essere formato da una sola cella o da più celle disposte su più colonne o righe, possiamo spaziare su tutto il foglio.

A prescindere dalla dimensione dell'intervallo su cui lavoriamo, una sola cella, più celle sulla stessa colonna, più celle che si estendono su più colonne, è possibile spaziare su qualsiasi cella del foglio e gli spostamenti tra le celle faranno sempre riferimento alla cella attiva dell'intervallo che in genere è la prima in alto a sinistra.

With Range("K1")
' che poterbbe anche essere
'With Range("K1").CurrentRegion
Dim R, C
For R = 1 To 10
For C = 1 To 5
.Cells(R, C).Select ' da notare il punto prima di Cells
Next
Next
End With

Cells(R, C) ci porterà nella cella che sta alla riga R colonna C a partire dalla cella K1.Se R = 1 e C = 1 verrà selezioanta la stessa K1.

 

Arrivati a questo punto penso sia chiaro a tutti che questo è senz'altro un modo di lavorare più comodo e che potremmo usare e riciclare le nostre istruzioni a prescindere la zona del foglio a cui ci riferiamo.

 

Cerchiamo di completare con un esempio pratico

Vogliamo cercare un articolo nella tabella disegnata in questa pagina esempio e conoscerne la quantità

Sub CercaInElenco()
Dim Intervallo As Range
Dim Artquale, R1, C1, Tot
Dim NumR, NumC
'=============================
' leggo il nome dell'articolo
Worksheets("Foglio1").Select
Artquale = Range("H2")
If Artquale = "" Then Exit Sub
'=============================
' ora determino in quale intervallo cercare
With Range("A1").CurrentRegion
NumR = .Rows.Count
NumC = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(NumR - 1, NumC)
End With
Tot = 0: C1 = 1
'=============================
' ora inizia l'esplorazione della prima colonna della tabella
For R1 = 1 To Intervallo.Rows.Count
'se l'articolo cercato viene trovato se ne rileva l'indirizzo
If StrComp(Artquale, Intervallo(R1, C1), vbTextCompare) = 0 Then
Tot = Tot + Intervallo(R1, C1 + 1)
End If
Next
'=============================
' viene visualizzato il risultato
Range("H2").Offset(4, 0) = Tot
End Sub

In questa routine:

Per il confronto tra l'articolo da cercare e quelli che vengono letti nella tabella di origine viene usata la Funzione StrComp che mi sembra più valida del classico If A = B Then: StrComp(ArtQuale, Intervallo(R1, C1), vbTextCompare)

La funzione StrComp prende string1 e string2 come argomenti per il confronto, ed usa l' argomento compare per specificare il tipo di confronto.

Usando vbTextCompare nel confronto non si tiene conto della differenza tra maiuscolo e minuscolo.

Se viene omesso questo argomento, la funzione StrComp utilizza il metodo di confronto predefinito del modulo impostato con la direttiva Option compare Text scritta tra le primissime righe del modulo.

Questi sono i possibili valori restituiti dal la funzione StrComp:

 

Ora vorrei fare una cosa più complessa, concettualmente, ma più universale, praticamente.

Dato questo schema esempio voglio costruire una routine che vada bene qualsiasi sia la dislocazione della tabella principale e delle celle che contengono "Articolo cercato" e "Risultato ricerca". L'unica condizione che la routine funzioni è che in tre locazioni del foglio ci siano queste tre celle coi seguenti contenuti:

Sub CercaInElencoBis()
Dim Cella As Range
Dim Intervallo As Range
Dim FL As Boolean
Dim Indirizzo As String, Cercato As String, Risultato As String
Dim NumR, NumC, Artquale, Tot, C1, R1
Worksheets("Foglio1").Select
'=============================
' Cerco la posizione della tabella da esaminare

FL = False
For Each Cella In ActiveSheet.UsedRange
If Cella = "Articoli" Then
FL = True
Indirizzo = Cella.Address
Exit For
End If
Next
If FL = False Then
MsgBox "Tabella non trovata"
Exit Sub
End If
'=============================
' Cerco la posizione dell'articolo da cercare

FL = False
For Each Cella In ActiveSheet.UsedRange
If Cella = "Articolo cercato" Then
FL = True
Cercato = Cella.Offset(1, 0).Address
Exit For
End If
Next
If FL = False Then
MsgBox "Manca riferimento a Articolo cercato"
Exit Sub
End If
Range(Cercato).Select
'=============================
' Determino e preparo la posizione dove scrivere il risultato della ricerca

Range(Cercato).Offset(3, 0) = "Risultato ricerca"
Risultato = Range(Cercato).Offset(4, 0).Address
'=============================
' ora determino in quale intervallo cercare

With Range(Indirizzo).CurrentRegion
NumR = .Rows.Count
NumC = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(NumR - 1, NumC)
End With
Artquale = Range(Cercato)
If Artquale = "" Then Exit Sub
'=============================
' qui inizia la ricerca nella prima colonna dell'intervallo

Tot = 0: C1 = 1
For R1 = 1 To Intervallo.Rows.Count
If StrComp(Artquale, Intervallo(R1, C1), vbTextCompare) = 0 Then
Tot = Tot + Intervallo(R1, C1 + 1)
End If
Next
'=============================
' viene visualizzato il risultato
Range(Risultato) = Tot
End Sub

Bene. Noterete che la routine, che svolge lo stesso compito di quella precedente, è leggermente più lunga e sembra un pò più complessa.

In realtà non è poi così complessa se esaminiamo i singoli segmenti di codice.

Infatti i primi due segmenti sono perfettamente identici: in tutti e due questi segmenti cerchiamo in tutta un'area (ActiveSheet.UsedRange) le stringhe: Articoli, Articolo cercato. La cella del risultato (la terza) viene determinata tramite uno scarto (Offset) relativo alla seconda cella trovata

Trovate queste stringhe se ne trova i relativi indirizzi:

Questi indirizzi vengono poi usati nel frammento di codice principale in questo modo

Questa seconda routine si affida Proprietà UsedRange per trovare le due zone interessate alla ricerca ed allo scarto di quattro righe (Offset(4, 0)) rispetto alla cella individuata tramite Range(Cercato)

 

 

Troverete qui il file che ho usato per questa esercitazione: intervalli.zip.