Assegnare nomi a celle o intervalli

Assegnare nomi a celle o intervalli, tramite vba. - pagina vista: volte

Sappiamo che per poter lavorare su fogli di cartelle di Excel, è necessario specificare la cella o le celle su cui desideriamo intervenire, attraverso un riferimento che identifichi la cella o l'intervallo di celle (area).

Sappiamo pure che un modo abbastanza pratico per definire celle o intervalli di celle è quello di assegnargli un "nome", selezionando la cella o l'area, e dal menù "Inserisci/Nome/definisci" scrivere un nome che identificherà ciò che avremo selezionato.

Ovviamente è possibile usare istruzioni vba per assegnare un "nome", ma prima di vedere le istruzioni, è opportuno fare due puntualizzazioni:

In questo caso la proprietà "Names" è relativa all'oggetto Application (Excel) e a sua volta è relativa al suo oggetto Workbook (la cartella di lavoro), e ci restituisce l'insieme di tutti i "Names" presenti nella cartella di lavoro attiva.
Utilizzare un "Nome" nell'identificazione di celle o aree, implica automaticamente definire anche il nome del foglio sul quale la cella o l'area è collocata.

Infatti quando si inserisce un "nome" dal menù "Inserisci/Nome/definisci", Excel usa la sintassi : " FoglioX!Range(x : y) ", memorizzando quindi il nome del foglio insieme all'intervallo (o la singola cella), e basterà usare in istruzioni vba il "nome" perchè Excel identifichi foglio e intervallo.

Questo ci consentirà di usare moltissime istruzioni lanciate da qualsiasi foglio, con la certezza che verranno eseguite "a distanza", cioè senza prima selezionare (Select) o attivare (Activate) il foglio contenente l'intervallo: se, per esempio, avremo assegnato il nome "pippo" all'intervallo A1:A5 del Foglio2, potremo istruire istruzioni, in questo caso lanciate dal Foglio1, esempi:

Range("pippo").Interior.ColorIndex = 3 - coloriamo di rosso le celle del Foglio2, da A1 a A5
Dim Cella As Object - incrementiamo di 5, iniziando con 10, le celle del Foglio2, da A1 a A5
x = 10
For Each Cella In Range("pippo")
Cella = x
x = x + 5
Next

Lanciate da un altro foglio, senza aver assegnato un "nome" all'intervallo, avremmo necessariamente dovuto scrivere:

Sheets(2).Range("A1:A5").Interior.ColorIndex = 3
......For Each Cella In Sheets(2).Range("A1:A5")

Se invece vorremo selezionare (Select) la cella o l'intervallo (relativo ad un foglio diverso da quello attivo), e rappresentato con un "nome", senza prima aver selezionato il Foglio che lo contiene, otterremo la protesta del debugger, che ci segnalerà un errore nell'uso del metodo Select, esempio:

Range("pippo").Select - errato
Sheets(2).Activate - giusto
Range("pippo").Select

L'uso di un "nome" ci può tornar utile anche quando dovremo assegnare un'intervallo (RowSource o ListFillRange) ad una ComboBox o ListBox ActiveX (anche poste su UserForm), ad esempio:

ComboBox1.RowSource = "pippo" - ComboBox posta su UserForm
ComboBox1.ListFillRange = "pippo" - ComboBox posta sul Foglio1

E' evidente che l'assegnazione di un "nome" presuppone che la cella o l'intervallo restino fissi nel tempo, sia come dimensione dell'intervallo, sia come posizione sul foglio. E' possibile tuttavia impostare istruzioni che "rileggano" una nuova dimensione dell'intervallo e riassegnino il nuovo intervallo allo stesso "nome". Ma vediamo intanto l'istruzione di base per assegnare via vba un nome ad un'intervallo; l'istruzione si basa sul metodo "Add" dell'oggetto "Names" relativo all'oggetto Workbook, e bisogna specificare quale nome assegnare e a quale cella o intervallo di un foglio, così:

ActiveWorkbook.Names.Add Name:="pippo", RefersTo:="Sheets(2).Range("A1:A5")"

se non avessimo specificato il foglio (Sheets(2)) in "ReferTo", l'intervallo sarebbe stato assunto come intervallo del foglio in quel momento attivo e memorizzato come tale.

Vediamo ora come assegnare, usando un ciclo For Next, dei nomi ad intervalli multipli. Per evitare di assegnare lo stesso nome due volte, incrementeremo il nome aggiungendovi un numero, in modo da ottenere "pippo1", "pippo2" ecc. ecc.

In questo primo esempio assegneremo un nome ad ogni cella, nell'intervallo A1:A10, e non specificheremo il foglio. Lanciando la macro dal Foglio1, i nomi saranno da Excel identificati come intervalli (celle) del Foglio1:

Sub AssegnaNomeCella()
For N = 1 To 10 'ripetiamo il ciclo per 10 volte
nome = "pippo" & N 'impostiamo la variabile "nome" come "pippo" seguito dal numero (N) assegnato dal ciclo
ActiveWorkbook.Names.Add Name:=nome, RefersTo:=Cells(N, 1) 'aggiungiamo il nome col riferimento alla cella 'N, colonna 1, e senza specificare il foglio, verrà memorizzata col nome del foglio ora attivo
Next
End Sub

Ora invece vediamo come assegnare nomi a più intervalli di celle. In questo caso dovremo impostare istruzioni che prevedano il numero di quante righe e quante colonne formeranno l'intervallo, poi dovremo decidere lo scarto, cioè se vorremo che gli intervalli si susseguano, o se invece vorremo saltare una o più righe (o colonne) tra un'intervallo e l'altro. Supponiamo quindi di voler definire nomi a intervalli di 4 righe e 3 colonne; useremo ancora il nome "pippo" che incrementeremo di numero per differenziare i nomi; anche in questo caso non specificheremo il nome del foglio:

Sub MultiArea()
riga = 1 'decidiamo da quale riga iniziare, in questo caso dalla riga 1
For N = 1 To 3 'ora decidiamo a quanti intervalli assegnare un nome, quindi 3 in questo caso
nome = "pippo" & N 'impostiamo la variabile "nome" come "pippo" seguito dal numero (N) assegnato dal ciclo
ActiveWorkbook.Names.Add Name:=nome, RefersTo:=Range(Cells(riga, 1), Cells(riga + 3, 3)) ' **
riga = riga + 4 'incrementiamo di 4 il valore di riga, così il prossimo intervallo inizierà dalla riga 5
Next
End Sub

** : con RefersTo:=Range(Cells(riga, 1), Cells(riga + 3, 3)) indichiamo che l'intervallo sarà dalla (iniziale) A1 (Cells(riga(1), 1)) alla cella riga1 +3 quindi riga4) (Cells(riga + 3, 3)), e fino alla colonna 3, quindi A1:C4, al successivo ciclo, avendo incrementato la variabile "riga" di 4, ci troveremo alla A5:C8, ecc. Se volessimo uno scarto di una riga tra un'intervallo e l'altro, basterebbe variare il numero di incremento riga, cioè riga = riga + 5 .

e invece avessimo voluto assegnare nomi ad intervalli ma sul Foglio3, avremmo dovuto scrivere il numero del foglio in ReferTo, così:

.....RefersTo:=Sheets(3).Range(Cells(riga, 1), Cells(riga + 3, 3)) oppure usando il nome del foglio:
.....RefersTo:=Sheets("Foglio3").Range(Cells(riga, 1), Cells(riga + 3, 3))

Ora invece vediamo come eliminare solo un "nome" assegnato o tutti i "nomi".

I "Names" sono identificati da Excel attraverso appunto un "nome" che lo identifichi, oppure, come in tutti gli insiemi, dal suo numero indice. Questa sotto è l'istruzione per eliminare un solo "nome":

ActiveWorkbook.Names("pippo1").Delete - usando il nome assegnato
ActiveWorkbook.Names(1).Delete - usando un suo numero indice

Questa sotto invece è una routine che prima conta quanti nomi sono presenti nella cartella attiva, e poi usa questo numero per effettuare un ciclo che elimini tutti i "nomi", iniziando il ciclo dal limite superiore, e tornando indietro :

Sub EliminaNomi()
Dim G, X As Integer
X = ActiveWorkbook.Names.Count 'con X otteniamo il numero di quanti nomi sono nel workbook
For G = X To 1 Step -1 'si inizia un ciclo a scalare, usando G come contatore del ciclo
ActiveWorkbook.Names(G).Delete 'e si cancella il nome il cui numero indice è uguale al contatore (G)
Next
End Sub

Ora vediamo un esempio in cui sia necessario rendere "variabile" l'intervallo assegnato ad un "nome". Potrebbe essere il caso di un "nome" usato per definire il RowSource (o il ListFillRange) di una ComboBox o ListBox ActiveX. Non è raro infatti che uno di questi "oggetti ActiveX" peschi i dati da mostrare nella propria lista, in un'intervallo che si modifica in continuazione, per aggiunte o eliminazione dell'elenco originale. Supponiamo quindi di avere una ComboBox posta su una UserForm; potremo sfruttare l'evento Activate della userform per "controllare" la lunghezza dell'intervallo "nome" assegnato al RowSource della ComboBox, e nello stesso evento, ricreare il "nome" e riassegnarlo.

Per poter contare quante righe sono contenute in un "Name" (visto quindi come intervallo), è necessario sfruttare la proprietà ReferToRange che restituisce l'oggetto Range a cui un oggetto Name fa riferimento. Se quindi il nome "pippo" l'avessimo assegnato all'intervallo F1:F10 del foglio attivo, useremo queste istruzioni, tenendo ben presente che il nome "pippo" deve essere già stato assegnato (altrimenti dovremmo istruire altre istruzioni che controllino se il nome "pippo" esiste)

Private Sub UserForm_Activate()
Dim M, T As Integer
M = ActiveWorkbook.Names("pippo").RefersToRange.Rows.Count 'contiamo le righe nell'intervallo "pippo"
T = ActiveSheet.Range("F1").End(xlDown).Rows 'contiamo, cercando l'ultima riga occupata, le righe nell'intervallo 'che inizia da F1, sul foglio attivo
If M <> T Then 'se M è diverso da T, ricreiamo il nome sovrascrivendolo col nuovo intervallo (sotto : )
ActiveWorkbook.Names.Add Name:="pippo", RefersTo:=Range("F1:F" & T)
End If
ComboBox1.RowSource = "pippo" 'e infine si assegna l'intervallo "pippo" al RowSource della ComboBox
End Sub

Esistono comunque sistemi diversi per rendere variabili i riferimenti da assegnare ad un RowSource, anche più semplici, ma visto che si sta parlando di "Names", era giusto presentare un esempio basato sui Names.

Buon Lavoro.

prelevato sul sito www.ennius.altervista.org