Notazione R1C1

E' costume generale quando dobbiamo riferirci ad una cella esprimerci in questo modo:

Range("A1").Value= MiaVariabile

oppure:

Cells(Riga,Colonna).value=MiaVariabile.

usando quasi sempre e quasi senza accorgercene la cosiddetta Notazione A1.

 

Questa volta vorrei parlare dell'uso di un altro tipo di notazione: la Notazione R1C1 altrettanto e forse più importante della prima (almeno per quanto mi riguarda; è da questo che sono decollato per questa avventura).

 

Ma cosa sono mai queste notazioni?

Le notazioni non sono altro che la maniera con cui ci riferiamo alle celle del foglio di Excel.

In altre parole per fare qualsiasi riferimento a tali celle usiamo la stessa tecnica con cui, quando ancora studenti, usavamo fare coi nostri compagnetti le famose battaglie navali. Chi non l'ha mai fato scagli la prima pietra!!!

 

Con la Notazione A1, sia nel formato: Range("A1") che nel formato: Cells(Riga,Colonna) di solito poniamo nella cella desiderata un valore, sia esso assegnato ad una variabile, sia in maniera diretta, come costante:

Range("A1").Value= MiaVariabile
Range("A1").Value= "Io Mi chiamo Michele"

Riga=3:Colonna=4
Cells(Riga,Colonna).Value= 58

Range("A1").Value= 58*31

Cells(Riga, Colonna).Value = Sheets("Foglio1").Range("B3").Value

e fin qui è tutto chiaro e non mi dilungo ulteriormente in quanto mi sembra che il discorso sia abbastanza chiaro.

 

Un po' di attenzione richiede, invece, l'argomento sulla Notazione R1C1 che altro non è che la notazione che usa Excel la quale provvede a scrivere nella calla attiva una formula anziché un semplice valore. Questo è un aspetto importante della preparazione ed uso di un foglio Excel in quanto permette di avere un foglio sempre dinamico e funzionale specie se ci troviamo spesso nella condizione di dover cambiare determinati elaborati cambiando semplicemente i valori di celle dipendenti da quelle che contengono formule.

 

Un esempio banale: se in D3 mettiamo questa formula:

=SOMMA(C3:C4;C6)

che è la formula per calcolare la somma dei valori compresi in un intervallo, possiamo in qualsiasi momento cambiare i valori delle celle C3, C4, C6 per vedere cambiare il valore della cella dove è scritta questa formula.

 

L'uso della notazione R1C1 anche da VBA ci permette appunto di mettere in determinate celle questo tipo di formule e non già dei semplici valori.

Se proviamo a registrare con il registratore di macro questa banale operazione e l'andiamo poi ad osservare nel modulo del VBA troveremmo una cosa del genere:

ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[1]C[-1],R[3]C[-1])"

Provando ad eseguire questa macro otterremmo lo stesso risultato che otterremmo digitando la funzione direttamente sul foglio (nella cella selezionata verrà posta una formula e non un semplice valore) e, cosa molto importante, nella barra della formula, vedremmo la formula: =SOMMA(C3:C4;C6)

Questo comporta che, se proviamo a cambiare un qualsiasi valore nelle celle indicate dalla formula verrà aggiornato automaticamente anche il risultato che risiede nella cella che ospita la formula.

 

Senza altro aggiungere a questa che potrebbe essere teoria passiamo alla pratica e cioè alla sintassi, o alle sintassi, che dobbiamo usare in VBA con questo tipo di notazione:

  1. ActiveCell.FormulaR1C1 = "=R3C2"
  2. ActiveCell.FormulaR1C1 = "=SUM(R3C3:R4C3,R14C3)"
  3. ActiveCell.FormulaR1C1 = "=R[-2]C"
  4. ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-4]:RC[-4]+R[2]C[-4])"
  5. ActiveCell.FormulaR1C1 = "=SUM(R[2]C*-1-R[-1]C+R[-2]C)"

    1. oppure facendo uso di Variabili come nei seguenti esempi:

  6. Riga1 = 1: Riga2 = 2: Riga3 = 3: Colonna = 4
    ActiveCell.FormulaR1C1 = _
    "=SUM(R" & Riga1 & "C" & Colonna & ":R" & Riga2 & "C" & Colonna & ",R" & Riga3 & "C" & Colonna & ")"
  7. ActiveCell.FormulaR1C1 = _
    "=SUM(R[" & Riga1 & "]C*-1-R[" & Riga3 & "]C+R[" & Riga2 & "]C)"
  8. ActiveCell.FormulaR1C1 = "=Foglio2!R[-3]C[-2]"

 

Quasi esclusivamente si usa con la proprietà FormulaR1C1 seguito dal segno "=" e preceduto dalla locazione dove porre la formula

Range("C11").FormulaR1C1 =

ActiveCell.FormulaR1C1 =

seguirà la funzione, con tutti i riferimenti necessari, racchiusa tra doppi apici e seguita a sua volta dal segno =, come negli esempi sopra mostrati.

 

Questo potrebbe essere tutto. Ma è veramente tutto? Onestamente penso proprio di no perché ora viene il bello.

A bella posta ho trascritto nel riquadro qui sopra le formule usando sintassi diverse per poter fare qui di seguito alcune considerazioni.

 

 

Prima osservazione: il fatto che vediamo tutte le istruzioni essenzialmente racchiuse tra due doppi apici sta ad indicare che l'intera istruzione è presentata dal VBA come stringa.

 

Seconda osservazione: vediamo i riferimenti con e senza parentesi quadre "[ ]".

L'assenza delle parentesi quadre come nell'esempio N° 1 e N° 2 nel riquedro superiore

ActiveCell.FormulaR1C1 = "=R3C2"
ActiveCell.FormulaR1C1 = "=SUM(R3C3:R4C3,R14C3)"

sta ad indicare che i riferimenti a celle da cui si prelevano i valori sono riferimenti assoluti che nella barra delle formule del foglio di Excel si vedono come:

=$B$3
=SOMMA($C$3:$C$4;$C$14)

In questo caso conosciamo il valore della riga per cui basta indicarla nel riferimento.

 

La presenza delle parentesi quadre come nell'esempio N° 3, N° 4 e N° 5 della tabella ci troviamo di fronte a dei riferimenti che sono relativi alla cella attiva: 2 righe più in basso della cella attiva, stessa colonna, ecc.

"=R[-2]C"
"=SUM(R[-1]C[-4]:RC[-4]+R[2]C[-4])"
"=SUM(R[2]C*-1-R[-1]C+R[-2]C)"

In questo caso non ha alcuna importanza conoscere a priori la posizione esatta dove si trova il valore da prelevare ma basta conoscere a quale distanza dalla cella attiva stanno le celle da cui prelevare i valori.

Il valore della riga sarà negativo se la riga della cella a cui si fa riferimento si trova sopra la cella attiva, positivo se si trova sotto.

Allo stesso modo per l'indicazione della colonna: il valore sarà negativo se la colonna della cella a cui si fa riferimento sta a sinistra della cella attiva, positivo se sta a destra.

 

Terza osservazione: nel 6° ed 7° esempio vediamo la presenza di segni di concatenamento stringhe e di variabili. Questo succede quando per indicare i riferimenti abbiamo necessità di far uso di variabili che indicano i valori delle righe e/o delle colonne.

ActiveCell.FormulaR1C1 = _
"=SUM(R" & Riga1 & "C" & Colonna & ":R" & Riga2 & "C" & Colonna & ",R" & Riga3 & "C" & Colonna & ")"

ActiveCell.FormulaR1C1 = _
"=SUM(R[" & Riga1 & "]C*-1-R[" & Riga3 & "]C+R[" & Riga2 & "]C)"

Ogni tanto possiamo avere la necessità di assegnare le coordinate a delle variabili. In questi casi possiamo usarle nelle nostre formule ma usando molta cautela e molta prudenza: perciò ora cercherò di chiarire meglio il concetto con alcuni esempi.

Una volta inizializzate le variabili che ci servono come nell'esempio:

Riga1 = 1: Riga2 = 2: Riga3 = 3: Colonna = 4

possiamo costruire la seguente stringa:

Range("G13").FormulaR1C1 = _
"=SUM(R" & Riga1 & "C" & Colonna & ":R" & Riga2 & "C" & Colonna & ",R" & Riga3 & "C" & Colonna & ")"

che scrive nella cella attiva la stessa formula vista sopra.

 

Quarta osservazione: e se, come nell'8° esempio i riferimenti alle celle da cui prelevare i dati si trovano su un altro foglio?

Una volta che sappiamo costruire una stringa come quella che abbiamo presentato nella formula 6 o 7 sapremo affrontare anche questo piccolo dettaglio: il caso è mostrato nella formula 8 del riquadro dove leggiamo:

ActiveCell.FormulaR1C1 = "=Foglio2!R[-3]C[-2]"

e, all'occorrenza, possiamo anche costruire delle formule usando delle variabili

 

 

Con questo pongo termine a questa disquisizione.

Buon lavoro a chi ha buona volontà di sperimentare queste tecniche