Input controllato per i dati da inserire in un foglio

 

Questa volta vorrei vedere, assieme a chi ha la pazienza di seguirmi, come controllare i nostri inserimenti. A volte l'impegno più grande nel progettare un'applicazione è quello di far sì che l'utente finale commetta meno errori possibili nell'inserire i dati.

Passiamo quindi a vedere come fare per ottenere che un utente inserisca il dato che ci aspettiamo.

 

Controllo dell'input in un foglio di Excel

Le celle del foglio di Excel, possono essere controllate da due eventi, oltre che in modo tardivo da qualsiasi parte del codice:

Iniziamo subito con il preparare la nostra tabella che non ha alcuna pretesa:

 

  A B C D E
1 Cognome Nome CAP Tel Nato
2
3
4

 

Vogliamo essere sicuri che l'utente inserisca in ciascuna cella solo dati validi e cioè:

  1. nella colonna 1 e 2 solo caratteri alfabetici (è ammesso usare anche l'apostrofo per i cognomi o i nomi composti)
  2. nella colonna 3 solo 5 cifre
  3. nella colonna 4 i numeri telefonici possono essere separati da un solo trattino per distinguere il prefisso dal numero
  4. nella colonna 5 sono ammesse solo le date nel formato gg/mm/aaa

Per sapere da VBA la cella appena modificata abbiamo l'evento Worksheet_Change che con il suo argomento Target di tipo Range ci restituisce la cella dalla quale siamo appena usciti dopo averne cambiato il contenuto. Da non confondere con l'altro evento Worksheet_SelectionChange il cui argomento Target ci restituisce la cella appena selezionata.

 

L'argomento Target dell'evento Worksheet_Change ci restituisce la cella dalla quale siamo usciti, non importa come: con Invio, Tab, Frecce, Mouse, ecc

L'uso di questo argomento è semplicissimo: lo possiamo usare come un qualsiasi oggetto Range, quindi:

Contenuto = Target    per leggere il contenuto della cella appena modificata

Target.Select              per tornare alla cella appena modificata

Target = "Ciao"          per scrivere qualcosa nella cella appena modificata

ecc.

 

Vediamo come procediamo per

  1. verificare che nelle colonne 1 e 2 siano inseriti saranno esclusi tutti i caratteri non alfabetici ad eccezione del carattere ' (apice o apostrofo) che ci servirà nei casi di nomi composti e a tutte le vocali con accento
  2. verificare che nella colonna 3 siano inseriti solo 5 cifre
  3. verificare che nella colonna 4 siano presenti solo cifre ad eccezzione del solo carattere - (trattino) come carattere separatore tra il prefisso ed il resto del numero
  4. verificare che nella colonna 5 siano inserite le date

Per tutti e quattro questi casi controlliamo i caratteri della stringa appena inserita nella cella per vedere se rientrano nella categoria a cui debbono rientrare.

Perciò, per semplificare il codice da scrivere per ogni tipo di controllo scriviamo quattro funzioni che andremo a richiamare di volta in volta. Queste funzioni lavorano, come potrete vedere, coi codici ascii dei singoli caratteri.

L'elenco completo dei codici ascii li potrete vedere in queste due pagine:esempioe esempio

 

Scrittura delle funzioni

Il codice usato per le funzioni va scritto in un modulo standard e lo potete vedere, e, se vi interessa, copiare da questa pagina esempio.

Nelle quattro funzioni potrete notare il metodo usato per leggere il codice ascii dei caratteri delle stringhe usate.

Per leggere i codici dei singoli caratteri ho usato questo metodo che fa uso di un ciclo For ... Next e che scorre tutta la stringa in esame dal primo all'ultimo carattere:

For L = 1 To Len(Source)
Car = Asc(Mid(Source, L, 1))
............
Next

L'ultima funzione, Function ContaCaratteri...., serve per stabilire se i campi relativi al telefono ed alla data contengano il giusto numero di caratteri separatori (un trattini per il numero di telefono e due barre per la data).

Il controllo avviene istruendo il solito ciclo For ... Next tutta la stringa e contando i caratteri separatori indicati in uno degli argomenti della funzione:

Function ContaCaratteri(Stringa As String, searchTable As String, ...........

Il ciclo è questo:

For I = start To Len(Stringa)
If InStr(1, searchTable, Mid$(Stringa, I, 1), Compare) Then Tot = Tot + 1
Next

In questa funzione abbiamo:

Stringa: la stringa in esame (numero di telefono o data)

searchTable: la stringa di ricerca che contiene il carattere o i caratteri da cercare in stringa (nel nostro caso il carattere "-" per il telefono e "/" per la data)

Con Instr leggiamo di volta in volta la posizione che occupa il carattere della stringa che stiamo valutando nella stringa dei caratteri da cercare.

 

In questa circostanza il metodo scelto potrebbe causare un po' di confusione in quanto la stringa di ricerca contiene un solo carattere ed il carattere che stiamo leggendo dalla stringa è pure uno. In questo caso sarebbe stato sufficiente, invece di usare la funzione Instr, usare:

For I = start To Len(Stringa)
If searchTable = Mid$(Stringa, I, 1) Then Tot = Tot + 1
Next

Ho preferito questa metodica perchè più generica e può tornarci comoda in svariate circostanze dove l'elaborazione potrebbe essere più elaborata.

Immaginate di avere una stringa come questa:

"tutte quelle massime di uso comune che conservano un contenuto morale"

e volete sapere quante vocali ci sono.

La stringa è memorizzata nella variabile Stringa e le vocali sono memorizzate in Caratteri: Caratteri = "aeiuo"

In questo caso possiamo richiamare la funzione ContaCaratteri in questo modo:

Sub Prova()
Dim Stringa As String, Caratteri As String
Stringa = "tutte quelle massime di uso comune che conservano un contenuto morale"
Caratteri = "aeiuo"
MsgBox "nella frase: " & vbCrLf & Stringa & vbCrLf & " ci sono " _
& ContaCaratteri(Stringa, Caratteri) & " di queste vocali: " & Caratteri
End Sub

La funzione richiamata (Function ContaCaratteri...) la trovate in fondo a questa pagina esempio .

La risposta di questa chiamata sarà:

nella frase:
tutte quelle massime di uso comune che conservano un contenuto morale
ci sono 27 di queste vocali: aeiuo

Beh, ci avete guadagnato uno spunto per costruire qualcosa di interessante .......

 

Scritte queste quattro funzioni + una possiamo recarci nel modulo relativo al foglio che dobbiamo sottoporre al nostro controllo (nella finestra Progetto VBAProject facciamo doppio clic sul foglio da monitorare).

 

La routine principale nel modulo relativo al foglio da controllare

Finita la scrittura delle funzioni è giunta l'ora di scrivere la routine di controllo nel modulo relativo al foglio da controllare.

Scegliamo di lavorare con l'evento Worksheet_Change perchè con il suo argomento Target impostato a Range ci permette di conoscere la cella da cui siamo appena usciti e di conseguenza ci permette di interrogarla.

Percià scriviamo tutto il codice che ci serve in questa routine:

Private Sub Worksheet_Change(ByVal Target As Range)
.....................
End Sub

Per sapere in che colonna abbiamo inserito il dato e, quindi comportarci di conseguenza, usiamo:

Select Case Target.Column

Target.Column ci restituisce la colonna della cella da cui siamo usciti.

In questo costrutto:

Select Case Target.Column
......................
End Select

scriviamo vari blocchi di istruzioni basati su Case:

Select Case Target.Column

 Case 1, 2 --------> stiamo nella colonna 1 o nella colonna 2

 Case 3

 Case 4

 Case 5

 Case Else -------> se non stiamo in una delle colonne sopra menzionate

End Select

In ognuna delle proposizioni Case richiamiamo la funzione relativa:

per 1 e 2: -------> controllo stringhe alfabetiche

If Testo(Target.Value) = False Then
Messaggio = "Solo caratteri alfabetici"
End If

per 3: -----------> controllo numeri del CAP

If Numerica(Target.Value) = False Then
Messaggio = "valore errato per codice CAP"
End If

per 4: ----------> controllo numeri del telefono

If Telefonico(Target.Value) = False Then
Messaggio = "errato"
End If

per 5: ---------> controllo della data

If LaData(Target.Value) = False Then
Messaggio = "valore errato nella data inserita"
Else
.............

Per la questione data aggiungiamo alcune righe di codice perchè sul foglio la data sia visualizzata in modo omogeneo.

A parte gli errori di digitazione o di distrazione per i quali ci affidiamo alla funzione LaData, vogliamo in ogni caso, almeno in questo esempio che la data sia visualizzata nel formato: 01/01/2004

Per questo ci prepariamo a correggere date, seppur valide. scritte nelle forme: 1/1/4, oppure 01/01/04, ecc.

Un modo che ho trovato valido per eseguire queste correzioni è l'aggiunta di questo codice:

MiaData = Target.Value
Target = Right("00" & Day(MiaData), 2) & "/" & Right("00" & Month(MiaData), 2) & "/" & Year(MiaData)

con la quale eseguiamo questa operazione:

concateniamo "00" con il giorno ("001", oppure "0027") e della stringa risultante preleviamo i due caratteri a destra ("01", oppure "27")

concateniamo "00" con il mese ("001", oppure "0011") e della stringa risultante preleviamo i due caratteri a destra ("01", oppure "11")

Per l'anno non occorre nessun correttivo.

Queste istruzioni sono da inserire dopo la Else lasciata in sospeso qui sopra. Quindi avremo:

If LaData(Target.Value) = False Then
Messaggio = "valore errato nella data inserita"
Else
Application.EnableEvents = False
MiaData = Target.Value
Target = Right("00" & Day(MiaData), 2) & "/" & Right("00" & Month(MiaData), 2) & "/" & Year(MiaData)
Application.EnableEvents = True
End If

Il codice completo di questa routine la trovate sempre in questa pagina esempio subito dopo le funzioni.

 

Buon lavoro