Offset - Resize

 

Capita a volte che, conoscendo una locazione più o meno nota di un determinato intervallo, volendo effettuare selezioni o operazioni su intervalli che ad essa si riferiscono, non sappiamo come fare.

Per ottenere più o meno facilmente la selezione di questi intervalli è conveniente affidarci ad alcune proprietà disponibili in VBA: Offset e Resize.

A volte potremmo aver bisogno anche di qualche altra proprietà dell'oggetto Range: "CurrentRegion" e "Count" che può applicarsi sia a Range che a Cells.

Ora cercherò , se ci riesco, di illustrare come usare le proprietà "Offset" e "Resize" combinate tra loro.

Offset: spostamento di un intervallo

fa riferimento ad un intervallo spostato nelle quattro direzioni (alto, basso, destra e sinistra) rispetto ad un intervallo preso come punto di partenza. Gli argomenti che accompagnano questa proprietà determinano lo spostamento da effettuare e possono essere positivi, negativi e/o 0 (zero). Se omessi assumono il valore predefinito uguale a 0 (zero). Unico limite è che tale spostamento non ci porti al di fuori degli intervalli predefiniti che sono:

1 - 256 per le colonne

1 - 65536 per le righe

La sintassi è la seguente: CellaDiPartenza.Offset(Riga, Colonna).Select

Range("C5:F12").Offset(2, 3).Select

sposta la selezione da C5:F12 a F7:I14

Resize: dimensionamento di un intervallo

determina il numero di righe e di colonne che deve contenere il nuovo intervallo partendo da un intervallo o una cella di origine. Gli argomenti che accompagnano la proprietà determinano la nuova dimensione che deve assumere il nuovo intervallo e possono essere solo numeri positivi. Se omessi mantengono gli stessi valori dell'intervallo di partenza.

Tuttavia non ha senso l'omissione di entrambi i parametri in quanto l'istruzione restituisce lo stesso intervallo rappresentato da "CellaDiPartenza".

Per questa proprietà è ininfluente che l'intervallo di partenza sia rappresentato da una o più celle:

E' ovvio che anche qui, nel determinare il nuovo intervallo, dobbiamo rimanere entro i limiti degli intervalli predefiniti.

La sintassi usata é la seguente: CellaDiPartenza.resize(Riga, Colonna).Select

Range("C5").Resize(2, 3).Select

Range("C5:G8").Resize(2, 3).Select

sortiscono lo stesso effetto restituendo un nuovo intervallo "C5:E6", cioè un intervallo che a partire dalla "C5" occupi 2 righe e 3 colonne.

 

Per la proprietà Offset     Per la proprietà Resize
Range("C5:F12").Offset(2, 3).Select     Range("C5:G8").Resize(2, 3).Select
  A B C D E F G H I J K                         A B C D E F G H I J K
1                           1                      
2                           2                      
3                           3                      
4                           4                      
5                           5                      
6                           6                      
7                           7                      
8                           8                      
9                           9                      
10                           10                      
11                           11                      
12                           12                      
13                           13                      
14                           14                      
15                           15                      

 

Abbinamento delle due proprietà

Con l'uso combinato delle due proprietà ed aiutati si possono effettuare selezioni interessanti.

Nelle istruzioni che andremo a vedere troveremo le azioni compiute dalle proprietà non potremo non notare il susseguirsi delle seguenti azioni:

Iniziamo subito partendo dalla seguente tabella:

 

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

Vediamo ora tre modi di effettuare delle particolari selezioni su questa tabella:

Per selezionare tutta la tabella:

Range("A1").CurrentRegion.Select

otteniamo questo:

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

 

Per selezionare la tabella al netto delle intestazioni di colonna:

With Range("A1").CurrentRegion
R = .Rows.Count
C = .Columns.Count
.Offset(1, 0).Resize(R - 1, C).Select
End With

con questo codice:

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

 

Per selezionare la tabella al netto delle intestazioni di riga:

With Range("A1").CurrentRegion
R = .Rows.Count
C = .Columns.Count
.Offset(0, 1).Resize(R, C - 1).Select
End With

con questo codice:

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


Per selezionare la tabella al netto delle intestazioni di riga e di colonna:

With Range("A1").CurrentRegion
R = .Rows.Count
C = .Columns.Count
.Offset(1, 1).Resize(R - 1, C - 1).Select
End With

con questo codice:

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

 

Un piccolo esempio: eseguire somme in verticale ed in orizzontale

La tabella è sempre quella sopra esposta.

Questa potrebbe essere una routine di esempio:

Sub DoppiaSomma()
Dim R, C, URiga, UColonna
Dim Tot
Dim Intervallo As Range
' definizione dell'intervallo al netto delle intestazioni di riga e di colonna e dei suoi limiti
With Range("A1").CurrentRegion
R = .Rows.Count
C = .Columns.Count
Set Intervallo = .Offset(1, 1).Resize(R - 1, C - 1)
End With
With Intervallo
URiga = .Rows.Count
UColonna = .Columns.Count
End With
' si esegue la somma per ogni riga della tabella
For R = 1 To URiga - 1
Tot = 0
For C = 1 To UColonna - 1
Tot = Tot + Intervallo(R, C)
Next
Intervallo(R, UColonna) = Tot
Next
' si esegue la somma per ogni colonna della tabella
For C = 1 To UColonna
Tot = 0
For R = 1 To URiga - 1
Tot = Tot + Intervallo(R, C)
Next
Intervallo(URiga, C) = Tot
Next
End Sub

Come si nota dai commenti posti nel codice, l'intera routine è suddivisa in tre settori:

  1. nel primo settore
    partendo dalla cella A1 si determina l'intervallo al netto delle intestazioni di riga e di colonna e si calcola l'ultima riga e l'ultima colonna
  2. nel secondo settore
    per calcolare i totali di ciascuna riga vengono istruiti due cicli nidificati: uno per le righe (quello esterno) ed uno per le colonne (quello interno)
    all'inizio del ciclo esterno si pone a 0 (zero) il totalizzatore
    all'interno del ciclo interno si valorizza il totalizzatore con le somme dei valori che si incontgrano
    alla fine del ciclo esterno viene scritto nell'ultima cella della riga il totale che è stato calcolato
  3. nel terzo settore
    i due cicli vengono organizzati in maniera speculare al precedente, ossia il ciclo esterno spazzola le colonne e quello interno le righe
    in questo modo otteniamo il totale per ogni singola colonna

 

Questo è il risultato finale:

 

  A B C D E F
1   Dettaglio Ingrosso Margine Tot orizz  
2 Alto 5,50 2,75 2,75 11,00  
3 Medio 4,50 2,25 2,25 9,00  
4 Basso 3,50 1,75 1,75 7,00  
5 Normale 4,35 2,38 1,97 8,70  
6 Tot vert 17,85 9,13 8,72    
7