Azioni ambivalenti: matrici o intervalli
Fin qui, finora, abbiamo lavorato o sulle celle del foglio di calcolo o sulle matrici.
Questa volta vorrei soffermarmi per approfondire una questione che potrà farci vedere un modo diverso di lavorare sia con l'uno che con l'altro metodo: ossia cerchiamo di vedere se e come è possibile istruire azioni che possano andare bene per entrambe le metodologie.
| Questa è una visualizzazione schematica di un foglio di lavoro: | Questa invece lo schema di una matrice | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Lavorando con le celle possiamo far in modo di riferirci alle celle con gli indici, per le righe e le colonne, che iniziano da 1.
Lavorando con le matrici ci accorgiamo che gli indici, se non diversamente dichiarati, possono iniziare anche da 0 (zero).
Vediamo come possiamo organizzarci.
Per creare delle routines che vadano bene sia per gli intervalli di un foglio di Excel sia per le matrici, occorre seguire delle piccole procedure.
Per creare una Matrice o un Intervallo:
- che l'intervallo sia dichiarato in una variabile oggetto:
Set MioIntervallo = Range("A1").CurrentRegion ' è solo un esempio di come si determina un intervallo - che la matrice sia a base 1:
Dim MiaMatrice(1 To 20, 1 To 7)
oppure:
ReDim MiaMatrice(1 To Nr, 1 To Nc)
Per conoscere l'indice superiore di questi due oggetti:
- Per leggere le dimensioni di un intervallo si può usare:
RI = MioIntervallo.Rows.Count
CI = MioIntervallo.Columns.Count - Per leggere le dimensioni di una matrice si può usare:
RM = UBound(MiaMatrice, 1)
CM = UBound(MiaMatrice, 2)
Per conoscere l'indice inferiore di questi due oggetti:
- L'indice inferiore di un intervallo è sempre 1, sia per quello delle righe, sia per quello delle colonne
RInf = 1
CInf = 1 - Incerti sono gli indici inferiori di una matrice. Perciò possiamo leggerli con:
RInf = LBound(MiaMatrice, 1)
CInf = LBound(MiaMatrice, 2)
Per leggere o scrivere un valore contenuto in una determinata locazione di un intervallo o di una matrice:
- per leggere in una determinata cella di un Intervallo:
Valore = MioIntervallo(3, 5) - per scrivere in una qualsiasi cella dell'intervallo:
MioIntervallo(4, 5) = Valore - per leggere il valore da una determinata locazione di una matrice:
Valore = MiaMatrice(3, 5) - per scrivere un valore in una determinata locazione di una matrice:
MiaMatrice(4, 5) = Valore
C'è una lieve differenza per trattare delle locazioni che sono al di fuori della dimensione massima di un Intervallo o di una Matrice.
- Per un intervallo possiamo leggere o scrivere dei valori in tutte le locazioni disponibili nel foglio, anche se l'intervallo di partenza è una singola cella
- Per una matrice queste operazioni sono consentite solo nei limiti definiti nel loro dimensionamento.
Per le matrici tuttavia:- se si tratta di una matrice Statica i limiti sono quelli dichiarati nel loro primo dimensionamento e non possiamo in nessun caso superarli:
Dim MiaMatrice(1 To 20, 1 To 7) - se si tratta di una matrice Dinamica è possibile aumentare le sue dimensioni in due modi:
- con perdita dei dati: ReDim Mia_Matrice(1 To Nr, 1 To Nc), ed in questto caso possiamo aumentare a nostro piacimento qualsiasi dimensione
- senza perdita di dati: ReDim Preserve matrice(1 To Nr, 1 To Nc + 4)
in questo secondo caso è possibile aumentare dinamicamente solo la seconda dimensione
- se si tratta di una matrice Statica i limiti sono quelli dichiarati nel loro primo dimensionamento e non possiamo in nessun caso superarli:
Fatte queste premesse possiamo provare con una piccolo applicativo.
Cercare la prima occorrenza di un valore
Per poter scrivere del codice che vada bene sia per una matrice che per un intervallo la cosa migliore è creare una funzione che possa essere semplicemente copiata ed incollata in qualsiasi file di Excel.
Immaginiamo di avere una tabella del genere:
| Nome | Indirizzo |
|---|---|
| Giuliana | P.za della Pace, 40 |
| Michele | Via Tulipani, 24 |
| Gabriele | viale dello Splendore |
| Gabriele | Lungomare Rodi |
| Stefano | via G. Leopardi |
| Marcello | via Milano 13 |
| Giuliana | via Cona |
| Maria | via C. Battisti |
| Edith | via Rossini |
| Alessandra | via Marconi |
| Gabriele | Via P. Montesi, 52 |
| Maria | Via P. Montesi, 88 |
| Stefano | via Fermi |
| Alessandra | via S. Maria dell’Arco |
| Michele | Cologna |
| Stefania | Via P. Montesi, 52 |
| Michele | Via Pietro Montesi, 52 |
| Maria | P.za della Pace, 40 |
| Francesco | Via Tulipani, 24 |
e che in essa vogliamo cercare un nome.
La funzione
Dim RMax, CMax, rMin, cmin, R, C
If IsObject(Elenco) Then
RMax = Elenco.Rows.Count
rMin = 1
CMax = Elenco.Columns.Count
cmin = 1
Else
RMax = UBound(Elenco, 1)
rMin = LBound(Elenco, 1)
CMax = UBound(Elenco, 2)
cmin = LBound(Elenco, 2)
End If
For R = rMin To RMax
For C = cmin To CMax
If LCase(Cosa) = LCase(Elenco(R, C)) Then
MiaTabellaSta = CStr(R) & "*" & CStr(C)
Exit Function
End If
Next
Next
MiaTabellaSta = ""
End Function
In questa funzione:
come prima cosa occorre stabilire se l'elenco (Elenco) che gli abbiamo mandato è un oggetto (Intervallo di celle) o una matrice
- se è un Intervallo di celle:
definiamo i limiti minimi e massimi usando le funzioni proprie degli intervalli (impostiamo ad 1 gli indici relativi alle righe ed alle colonne, per gli indici inferiori e Elenco.Rows.Count, Elenco.Columns.Count per gli indici massimi) - se è una matrice:
definiamo i limiti minimi e massimi usando le funzioni proprie delle matrici
RMax = UBound(Elenco, 1) ' queste per le righe
rMin = LBound(Elenco, 1)
CMax = UBound(Elenco, 2) ' queste per le colonne
cmin = LBound(Elenco, 2)
Fatto questo possiamo scrivere la funzione che ci serve. In questo caso la funzione cerca un determinato valore che dovrebbe essere nell'elenco.
Le eventuali routines che possono usare questa funzione
Trova Valore In Intervallo
Dim Intervallo As Range
Dim NR, NC, Mess
Dim Valore As String, Localizzato As String
Dim Coord
Worksheets("Foglio1").Select
Set Intervallo = Range("A1").CurrentRegion
NR = Intervallo.Rows.Count - 1
NC = Intervallo.Columns.Count
Set Intervallo = Intervallo.Offset(1, 0).Resize(NR, NC)
Valore = InputBox("Dimmi un nome da cercare")
Localizzato = MiaTabellaSta(Valore, Intervallo)
Mess = "Quel che cerchi "
If Localizzato <> "" Then
Coord = Split(Localizzato, "*")
Mess = Mess & "ha l'indice in riga " & Coord(0) & " col " & Coord(1) & "dell'intervallo"
Else
Mess = Mess & "NON ci sta..."
End If
MsgBox Mess
End Sub
In questa routine, dopo aver definito l'intervallo da usare per la ricerca (primo settore):
- Chiediamo cosa cercare
- Andiamo alla funzione coi due argomenti:
- Valore, che è la stringa da cercare
- Intervallo, che è l'intervallo dove cercare
- Al ritorno dalla funzione in localizzato possiamo avere:
- le coordinate trovate sotto forma di "X*Y"
- oppure nulla
- se la stringa contiene le coordinate le mostriamo
- altrimenti mandiamo un messaggio che ci avverte del fallimento
Trova Valore In Matrice
Dim Intervallo As Range
Dim NR, NC, R, C, Mess
Dim Valore As String, Localizzato As String
Dim Coord
Worksheets("Foglio1").Select
Set Intervallo = Range("A1").CurrentRegion
NR = Intervallo.Rows.Count - 1
NC = Intervallo.Columns.Count
Set Intervallo = Intervallo.Offset(1, 0).Resize(NR, NC)
ReDim Matrice(1 To NR, 1 To NC)
For R = 1 To NR
For C = 1 To NC
Matrice(R, C) = Intervallo(R, C)
Next
Next
Valore = InputBox("Dimmi un nome da cercare")
Localizzato = MiaTabellaSta(Valore, Matrice)
Mess = "Quel che cerchi "
If Localizzato <> "" Then
Coord = Split(Localizzato, "*")
Mess = Mess & "ha l'indice in riga " & Coord(0) & " col " & Coord(1) & "della matrice"
Else
Mess = Mess & "NON ci sta..."
End If
MsgBox Mess
End Sub
Identico lavoro per questa routine, ma, prima di andare alla funzione, viene creata la matrice raccigliendo i dati dal foglio.
Trova Valore In Dati esterni
Dim Valore As String, Localizzato As String
Dim NomeFile, FileNum
Dim R, C, MatriceTemp(), MiaMatrice(), Mess, Coord
Dim Test, RMax, CMax
Worksheets("Foglio1").Select
NomeFile = ActiveWorkbook.Path & "\database.txt"
If Dir(NomeFile) = "" Then
MsgBox "impossibile trovarfe il file"
Exit Sub
End If
Close
FileNum = FreeFile
Open NomeFile For Input As #FileNum
R = 0
Do While Not EOF(FileNum)
R = R + 1
ReDim Preserve MatriceTemp(1 To R)
Line Input #FileNum, MatriceTemp(R)
Loop
Close #FileNum
Test = Split(MatriceTemp(1), "|")
RMax = UBound(MatriceTemp)
CMax = UBound(Test)
ReDim MiaMatrice(1 To RMax, 0 To CMax)
For R = 1 To RMax
Test = Split(MatriceTemp(R), "|")
For C = LBound(Test) To UBound(Test)
MiaMatrice(R, C) = Test(C)
Next
Next
Valore = InputBox("Dimmi un nome da cercare")
Localizzato = MiaTabellaSta(Valore, MiaMatrice)
Mess = "Quel che cerchi "
If Localizzato <> "" Then
Coord = Split(Localizzato, "*")
Mess = Mess & "ha l'indice in riga " & Coord(0) & " col " & Coord(1) & " della matrice"
Else
Mess = Mess & "NON ci sta..."
End If
MsgBox Mess
End Sub
In questa ultima routine ho usato questo file di testo
:
- viene verificata l'esistenza del file di testo per evitare un eventuale errore se questo non viene trovato
If Dir(NomeFile) = "" Then
MsgBox "impossibile trovarfe il file"
Exit Sub
End If - di seguito viene aperto il file e se ne esegue la lettura una riga alla volta memorizzandola in una matrice temporanea
- I dati nel file di testo sono così rappresentati:
Giuliana|P.za della Pace, 40
Michele|Via Tulipani, 24 - Di conseguenza, per poterli utilizzare correttamente occorre dividere ogni singolo record nei vari campi utilizzando il carattere separatore di campo rappresentato dal carattere "|"
- La divisione viene effettuata usando la Funzione Split:
Test = Split(MatriceTemp(1), "|") - In Test avremo un Vettore di due record:
uno con indice 0 (zero)
uno con indice 1 - Questi due record li mettiamo nella matrice di lavoro come campi:
For C = LBound(Test) To UBound(Test)
MiaMatrice(R, C) = Test(C)
Next - Eseguito questo lavoro possiamo richiamare la funzione alla solita maniera:
Localizzato = MiaTabellaSta(Valore, MiaMatrice)
Considerazioni finali:
Con questa prospettiva potremmo risparmiare molto tempo per la creazione di codice scritto ad hoc e, soprattutto avremo meno confusione se dobbiamo trattare intervalli o celle dalle tipologie più varie.
Buon lavoro