Convalida in VBA : impedire inserimento duplicati - pagina vista: volte

Ovvero: Attenzione al Registratore di Macro.

Requisiti: conoscenza dell'uso di Convalida sul foglio di lavoro.

Nel primo articolo che ho presentato sull'utilizzo del VBA per utilizzare l'oggetto Validation (vedi Convalida in VBA) suggerivo al lettore che poteva utilizzare il "Registratore di macro" (vedi sul sito) per imparare come venivano composte le necessarie istruzioni ed eventualmente per modificarle.

Suggerimento più che valido anche se, per effetto del tipo di Convalida da utilizzare, la compilazione di codice da parte del suddetto Registratore, può rivelarsi inesatta o addirittura fallata.

Questo articolo quindi ha il solo scopo di avvisare il lettore come a volte sia necessario intervenire con opportune modifiche in caso di errore segnalato del Debugger durante l'esecuzione di istruzioni.

A titolo di esempio imposteremo un esercizio per convalidare l'immissione di dati in un determinato intervallo: se il dato che stiamo immettendo è già presente nell'intervallo, desideriamo essere bloccati ed avvisati. Supponiamo che l'intervallo da convalidare sia tutta la colonna G.

Attiviamo come prima cosa il "Registratore di Macro" e quindi compiamo le operazioni che vogliamo siano registrate:

Selezioneremo tutta la colonna G e dal menù Dati/Convalida imposteremo Convalida a "Personalizzato", e nell'apposito spazio imposteremo la formula
"=CONTA.SE(G:G;G1)=1" (senza doppi apici, ma con il segno di uguale) cioè: se ciò che scriveremo in G1, per effetto della funzione CONTA.SE che controlla tutta la colonna G,  restituirà 1 vorrà dire che il dato immesso è già presente, e verremo bloccati e avvisati dopo aver deciso le giuste impostazioni (tipo di avviso e tipo di messaggi) nelle relative finestre di impostazione Convalida. Vediamo la sequenza

Dopo aver impostato Convalida e premuto OK, spegniamo il Registratore e ci rechiamo nel Visual Basic Editor (ALT + F11) e controllando cosa ci è stato compilato, troveremo una cosa del genere:

Sub Macro1()
'
' Macro1 Macro
' Macro registrata il xx/xx/xx da tuonome
'
Columns("G:G").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=CONTA.SE(G:G;G1)=1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Inserimento"
.ErrorTitle = "Actung!!!"
.InputMessage = "Inserire solo numeri"
.ErrorMessage = "dato inserito già presente"
.ShowInput = True
.ShowError = True
End With
End Sub

Senza soffermarci sulle varie istruzioni, (tutte rilevabili dalla vostra guida in linea) sottolineo solo che per il metodo Add (aggiungi), l'argomento "Formula1" che deve riportare la formula da noi inserita nella finestrina "Impostazioni" di Convalida, riporta l'istruzione compilata in italiano come per la proprietà "Formula" che è ben diversa dalla proprietà "Formula1" che restituisce il valore o l'espressione (la formula, appunto) associata al formato condizionale o alla convalida dei dati.

Se noi lanciamo la Macro1() scritta sopra, incorriamo nella lamentela del Debugger  (errore di run-time 1004) che segnala un errore e ci evidenzia in giallo la seguente istruzione:

Ora, al di là del fatto che possiamo "limare" alcune istruzioni eliminando il Select della Colonna G (non necessario, ma il Registratore è sempre un fedele e attento strumento che memorizza tutte le azioni che compiano sul foglio) e impostare l'intervallo subito dopo l'istruzione With , così:

  • With Columns("G:G").Validation    ecc. ecc.

la segnalazione dell'errore non è molto ben definita (non viene evidenziato quale tra gli argomenti del metodo Add è il responsabile dell'errore) , ma conoscendo un poco dove "prude" al buon Excel (per la verità al suo compilatore e interprete VBA) , basta provare a modificare la formula, riscrivendo la funzione CONTA.SE in inglese (COUNTIF) e modificando i separatori così come richiede la sintassi vba, cioè la virgola ( , ) come separatore di argomento anzichè il punto e virgola ( ; ) come richiesto sul foglio di lavoro (provvederà poi Excel a convertire di nuovo l'istruzione come formula in italiano),  infatti così corretta la macro svolgerà pienamente il suo lavoro:

Sub Macro1()
'
' Macro1 Macro
' Macro registrata il xx/xx/xx da tuonome
'
With Columns("G:G").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=COUNTIF(G:G,G1)=1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Inserimento"
.ErrorTitle = "Actung!!!"
.InputMessage = "Inserire solo numeri"
.ErrorMessage = "dato inserito già presente"
.ShowInput = True
.ShowError = True
End With
End Sub

Che altro dire? a me pare che una possibile motivazione (non ho trovato specifica documentazione sull'argomento) sia rappresentata dal fatto che il tipo di formula inserita sia vista dal codice più come una matriciale (proprio per il tipo di formula che viene inserita con l'autoaggiornamento dei riferimenti alle celle del secondo argomento (cosa cercare) nell'ambito dell'intervanto completo (dove cercare per contare se) e quindi necessita di istruzioni in lingua madre (che per il vba è l'inglese).

Ovviamente potremo tranquillamente usare questo tipo di Convalida direttamente sul foglio di lavoro senza usare il vba, ma l'esempio è servito ad evidenziare come poter intervenire (quando è possibile) in caso di errori.


buon lavoro.

prelevato sul sito www.ennius.altervista.org