Che cos'è un errore di mancata corrispondenza del tipo?
Un errore di mancata corrispondenza può verificarsi spesso quando si esegue il codice VBA. L'errore interromperà completamente l'esecuzione del codice e verrà segnalato tramite una finestra di messaggio che questo errore deve essere risolto
Tieni presente che se non hai testato completamente il tuo codice prima della distribuzione agli utenti, questo messaggio di errore sarà visibile agli utenti e causerà una grande perdita di fiducia nell'applicazione Excel. Sfortunatamente, gli utenti spesso fanno cose molto peculiari a un'applicazione e spesso sono cose che tu come sviluppatore non hai mai considerato.
Si verifica un errore di mancata corrispondenza del tipo perché è stata definita una variabile utilizzando l'istruzione Dim come un determinato tipo, ad es. intero, data e il tuo codice sta cercando di assegnare un valore alla variabile che non è accettabile, ad es. stringa di testo assegnata a una variabile intera come in questo esempio:
Ecco un esempio:
Fare clic su Debug e la riga di codice incriminata verrà evidenziata in giallo. Non c'è alcuna opzione sul pop-up di errore per continuare, poiché si tratta di un errore grave e non è possibile che il codice venga ulteriormente eseguito.
In questo caso particolare, la soluzione consiste nel modificare l'istruzione Dim in un tipo di variabile che funzioni con il valore che si sta assegnando alla variabile. Il codice funzionerà se cambi il tipo di variabile in "Stringa" e probabilmente vorrai cambiare anche il nome della variabile.
Tuttavia, la modifica del tipo di variabile richiederà il ripristino del progetto e dovrai eseguire nuovamente il codice dall'inizio, il che può essere molto fastidioso se è coinvolta una procedura lunga
Errore di mancata corrispondenza causato dal calcolo del foglio di lavoro
L'esempio sopra è molto semplice di come può essere prodotto un errore di mismatch e, in questo caso, è facilmente rimediabile
Tuttavia, la causa degli errori di mancata corrispondenza è in genere molto più profonda di questa e non è così ovvia quando si tenta di eseguire il debug del codice.
Ad esempio, supponiamo di aver scritto del codice per prelevare un valore in una determinata posizione su un foglio di lavoro e contenga un calcolo dipendente da altre celle all'interno della cartella di lavoro (B1 in questo esempio)
Il foglio di lavoro assomiglia a questo esempio, con una formula per trovare un carattere particolare all'interno di una stringa di testo
Dal punto di vista dell'utente, la cella A1 è in formato libero e può inserire qualsiasi valore desideri. Tuttavia, la formula sta cercando un'occorrenza del carattere "B" e in questo caso non viene trovata, quindi la cella B1 ha un valore di errore.
Il codice di prova seguente produrrà un errore di mancata corrispondenza perché è stato inserito un valore errato nella cella A1
| 1234 | Sottotest Mancata corrispondenza()Dim MyNumber As IntegerMioNumero = Fogli("Foglio1").Intervallo("B1").ValoreFine sottotitolo |
Il valore nella cella B1 ha prodotto un errore perché l'utente ha inserito un testo nella cella A1 che non è conforme a quanto previsto e non contiene il carattere "B"
Il codice cerca di assegnare il valore alla variabile 'MyNumber' che è stata definita per aspettarsi un numero intero, e quindi si ottiene un errore di mancata corrispondenza.
Questo è uno di questi esempi in cui il controllo meticoloso del codice non fornirà la risposta. Devi anche guardare sul foglio di lavoro da dove proviene il valore per scoprire perché questo sta accadendo.
Il problema è in realtà nel foglio di lavoro e la formula in B1 deve essere modificata in modo che i valori di errore vengano gestiti. Puoi farlo utilizzando la formula "IFERROR" per fornire un valore predefinito di 0 se il carattere di ricerca non viene trovato
È quindi possibile incorporare il codice per verificare la presenza di un valore zero e per visualizzare un messaggio di avviso per l'utente che il valore nella cella A1 non è valido
| 12345678 | Sottotest Mancata corrispondenza()Dim MyNumber As IntegerMioNumero = Fogli("Foglio1").Intervallo("B1").TestoSe MyNumber = 0 AlloraMsgBox "Il valore nella cella A1 non è valido", vbCriticalEsci SottoFinisci seFine sottotitolo |
Puoi anche utilizzare la convalida dei dati (gruppo Strumenti dati nella scheda Dati della barra multifunzione) sul foglio di calcolo per impedire all'utente di fare ciò che gli piace e causare errori nel foglio di lavoro in primo luogo. Consenti loro di inserire solo valori che non causeranno errori del foglio di lavoro.
Puoi scrivere il codice VBA in base all'evento Change nel foglio di lavoro per verificare cosa è stato inserito.
Blocca e proteggi con password anche il foglio di lavoro, in modo che i dati non validi non possano essere inseriti
Errore di mancata corrispondenza causato dai valori delle celle immessi
Gli errori di mancata corrispondenza possono essere causati nel codice inserendo valori normali da un foglio di lavoro (non errore), ma in cui l'utente ha inserito un valore imprevisto, ad es. un valore di testo quando ti aspettavi un numero. Potrebbero aver deciso di inserire una riga all'interno di un intervallo di numeri in modo da poter inserire una nota in una cella che spieghi qualcosa sul numero. Dopotutto, l'utente non ha idea di come funzioni il tuo codice e che ha appena messo tutto fuori gioco inserendo la sua nota.
Il codice di esempio seguente crea un semplice array chiamato "MyNumber" definito con valori interi
Il codice quindi scorre un intervallo di celle da A1 a A7, assegnando i valori delle celle nell'array, utilizzando una variabile "Coun" per indicizzare ciascun valore
Quando il codice raggiunge il valore del testo, viene causato un errore di mancata corrispondenza e tutto si ferma
Facendo clic su "Debug" nel pop-up di errore, vedrai la riga di codice che ha il problema evidenziato in giallo. Passando il cursore su qualsiasi istanza della variabile "Coun" all'interno del codice, sarai in grado di vedere il valore di "Coun" in cui il codice ha fallito, che in questo caso è 5
Guardando sul foglio di lavoro, vedrai che il 5ns la cella in basso ha il valore del testo e questo ha causato il fallimento del codice
Puoi cambiare il tuo codice inserendo una condizione che controlli un valore numerico prima di aggiungere il valore della cella nell'array
| 12345678910111213 | Sottotest Mancata corrispondenza()Dim MyNumber(10) As Integer, Conta As IntegerConte = 1FareSe Conte = 11 quindi esci DoIf IsNumeric(Sheets("sheet1").Cells(Coun, 1).Value) ThenMyNumber(Coun) = Sheets("sheet1").Cells(Coun, 1).ValoreAltroMioNumero(Conteggio) = 0Finisci seConte = Conte + 1Ciclo continuoFine sottotitolo |
Il codice utilizza la funzione "IsNumeric" per verificare se il valore è effettivamente un numero e, se lo è, lo inserisce nell'array. Se non è un numero, inserisce il valore zero.
Ciò garantisce che l'indice dell'array sia in linea con i numeri di riga delle celle nel foglio di calcolo.
Puoi anche aggiungere un codice che copia il valore di errore originale e i dettagli sulla posizione in un foglio di lavoro "Errori" in modo che l'utente possa vedere cosa ha fatto di sbagliato durante l'esecuzione del codice.
Il test numerico utilizza il codice completo per la cella e il codice per assegnare il valore nell'array. Potresti obiettare che questo dovrebbe essere assegnato a una variabile in modo da non continuare a ripetere lo stesso codice, ma il problema è che dovresti definire la variabile come una "Variante" che non è la cosa migliore da fare.
È inoltre necessaria la convalida dei dati sul foglio di lavoro e la protezione con password del foglio di lavoro. Ciò impedirà all'utente di inserire righe e immettere dati imprevisti.
Errore di mancata corrispondenza causato dalla chiamata di una funzione o di una sottoroutine utilizzando i parametri
Quando viene chiamata una funzione, di solito si passano i parametri alla funzione utilizzando i tipi di dati già definiti dalla funzione. La funzione potrebbe essere già definita in VBA o potrebbe essere una funzione definita dall'utente creata dall'utente. A volte anche una sottoroutine può richiedere parametri
Se non ti attieni alle convenzioni su come i parametri vengono passati alla funzione, otterrai un errore di mancata corrispondenza
| 12345678 | Funzione di chiamata secondaria()Dim Ret As IntegerRet = MyFunction(3, "test")Fine sottotitoloFunzione MyFunction(N As Integer, T As String) As StringMiaFunzione = TFine funzione |
Ci sono diverse possibilità qui per ottenere un errore di mancata corrispondenza
La variabile di ritorno (Ret) è definita come un numero intero, ma la funzione restituisce una stringa. Non appena esegui il codice, fallirà perché la funzione restituisce una stringa e questa non può essere inserita in una variabile intera. È interessante notare che l'esecuzione di Debug su questo codice non rileva questo errore.
Se metti le virgolette attorno al primo parametro che viene passato (3), viene interpretato come una stringa, che non corrisponde alla definizione del primo parametro nella funzione (intero)
Se trasformi il secondo parametro nella chiamata di funzione in un valore numerico, fallirà con una mancata corrispondenza perché il secondo parametro nella stringa è definito come una stringa (testo)
Errore di mancata corrispondenza causato dall'utilizzo errato delle funzioni di conversione in VBA
Esistono numerose funzioni di conversione che è possibile utilizzare in VBA per convertire i valori in vari tipi di dati. Un esempio è "CInt" che converte una stringa contenente un numero in un valore intero.
Se la stringa da convertire contiene caratteri alfabetici, verrà visualizzato un errore di mancata corrispondenza, anche se la prima parte della stringa contiene caratteri numerici e il resto è costituito da caratteri alfabetici, ad es. '123abc'
Prevenzione generale degli errori di mancata corrispondenza
Negli esempi precedenti abbiamo visto diversi modi per gestire potenziali errori di mancata corrispondenza all'interno del codice, ma ci sono molti altri modi, anche se potrebbero non essere le migliori opzioni:
Definisci le tue variabili come Tipo di variante
Un tipo di variante è il tipo di variabile predefinito in VBA. Se non si utilizza un'istruzione Dim per una variabile e si inizia semplicemente a utilizzarla nel codice, viene automaticamente assegnato il tipo di Variant.
Una variabile Variant accetterà qualsiasi tipo di dati, sia che si tratti di un numero intero, di un numero intero lungo, di un numero a doppia precisione, di un valore booleano o di testo. Sembra un'idea meravigliosa e ti chiedi perché tutti non impostano semplicemente tutte le loro variabili su variante.
Tuttavia, il tipo di dati della variante presenta diversi aspetti negativi. In primo luogo, occupa molta più memoria rispetto ad altri tipi di dati. Se definisci un array molto grande come variante, inghiottirà un'enorme quantità di memoria quando il codice VBA è in esecuzione e potrebbe facilmente causare problemi di prestazioni
In secondo luogo, le prestazioni sono generalmente più lente rispetto a quando si utilizzano tipi di dati specifici. Ad esempio, se si eseguono calcoli complessi utilizzando numeri a virgola mobile, i calcoli saranno notevolmente più lenti se si memorizzano i numeri come varianti, anziché numeri a doppia precisione
L'uso del tipo variante è considerato una programmazione sciatta, a meno che non sia assolutamente necessario.
Usa il comando OnError per gestire gli errori
Il comando OnError può essere incluso nel codice per gestire l'intrappolamento degli errori, in modo che se si verifica un errore l'utente vede un messaggio significativo invece del pop-up di errore VBA standard
| 1234567 | Sub ErrorTrap()Dim MyNumber As IntegerIn caso di errore GoTo Err_HandlerMioNumero = "prova"Err_Handler:MsgBox "Si è verificato l'errore " & Err.Description & ""Fine sottotitolo |
Ciò impedisce efficacemente all'errore di interrompere il corretto funzionamento del codice e consente all'utente di recuperare in modo pulito dalla situazione di errore.
La routine Err_Handler potrebbe mostrare ulteriori informazioni sull'errore e chi contattare a riguardo.
Da un punto di vista della programmazione, quando si utilizza una routine di gestione degli errori, è abbastanza difficile individuare la riga di codice su cui si trova l'errore. Se stai passando attraverso il codice usando F8, non appena viene eseguita la riga di codice incriminata, passa alla routine di gestione degli errori e non puoi controllare dove sta andando storto.
Un modo per aggirare questo è impostare una costante globale che sia True o False (Boolean) e usarla per attivare o disattivare la routine di gestione degli errori utilizzando un'istruzione "If". Quando vuoi testare l'errore, tutto ciò che devi fare è impostare la costante globale su False e il gestore degli errori non funzionerà più.
| 1 | Global Const ErrHandling = False |
| 1234567 | Sub ErrorTrap()Dim MyNumber As IntegerSe ErrHandling = True Then On Error GoTo Err_HandlerMioNumero = "prova"Err_Handler:MsgBox "Si è verificato l'errore " & Err.Description & ""Fine sottotitolo |
L'unico problema con questo è che consente all'utente di recuperare dall'errore, ma il resto del codice all'interno della sottoroutine non viene eseguito, il che potrebbe avere enormi ripercussioni in seguito nell'applicazione
Usando l'esempio precedente del ciclo attraverso un intervallo di celle, il codice arriva alla cella A5 e colpisce l'errore non corrispondente. L'utente vedrà una finestra di messaggio che fornisce informazioni sull'errore, ma non verrà elaborato nulla da quella cella in poi nell'intervallo.
Usa il comando OnError per sopprimere gli errori
Questo utilizza il comando "On Error Resume Next". Questo è molto pericoloso da includere nel codice in quanto impedisce la visualizzazione di eventuali errori successivi. Ciò significa sostanzialmente che durante l'esecuzione del codice, se si verifica un errore in una riga di codice, l'esecuzione si sposterà semplicemente alla riga disponibile successiva senza eseguire la riga di errore e continuerà normalmente.
Ciò potrebbe risolvere una potenziale situazione di errore, ma influenzerà comunque ogni errore futuro nel codice. Potresti quindi pensare che il tuo codice sia privo di bug, ma in realtà non lo è e parti del tuo codice non stanno facendo quello che pensi dovrebbe fare.
Ci sono situazioni in cui è necessario utilizzare questo comando, ad esempio se stai cancellando un file usando il comando 'Kill' (se il file non è presente, ci sarà un errore), ma l'errore di cattura dovrebbe sempre essere ripristinato immediatamente dopo dove potrebbe verificarsi il potenziale errore utilizzando:
| 1 | In caso di errore Vai a 0 |
Nell'esempio precedente del ciclo di un intervallo di celle, utilizzando "On Error Resume Next", ciò consentirebbe al ciclo di continuare, ma la cella che causa l'errore non verrebbe trasferita nell'array e l'elemento dell'array per quel particolare indice conterrebbe un valore nullo.
Conversione dei dati in un tipo di dati in modo che corrispondano alla dichiarazione
È possibile utilizzare le funzioni VBA per modificare il tipo di dati dei dati in ingresso in modo che corrispondano al tipo di dati della variabile ricevente.
Puoi farlo quando passi i parametri alle funzioni. Ad esempio, se hai un numero contenuto in una variabile stringa e vuoi passarlo come numero a una funzione, puoi usare CInt
Ci sono un certo numero di queste funzioni di conversione che possono essere utilizzate, ma qui ci sono le principali:
CInt - converte una stringa che ha un valore numerico (sotto + o - 32.768) in un valore intero. Tieni presente che questo tronca eventuali punti decimali fuori
CLng - Converte una stringa che ha un valore numerico grande in un intero lungo. I punti decimali vengono troncati.
CDbl - Converte una stringa contenente un numero a virgola mobile in un numero a doppia precisione. Include i punti decimali
CDate - Converte una stringa che contiene una data in una variabile di data. Dipende in parte dalle impostazioni nel Pannello di controllo di Windows e dalle impostazioni locali su come viene interpretata la data
CStr - Converte un valore numerico o di data in una stringa
Quando si converte da una stringa a un numero oa una data, la stringa non deve contenere altro che numeri o una data. Se sono presenti caratteri alfabetici, ciò produrrà un errore di mancata corrispondenza. Ecco un esempio che produrrà un errore di mancata corrispondenza:
| 123 | Sottoprova()MsgBox CInt("123abc")Fine sottotitolo |
Testare le variabili all'interno del tuo codice
Puoi testare una variabile per scoprire di che tipo di dati si tratta prima di assegnarla a una variabile di un tipo particolare.
Ad esempio, puoi controllare una stringa per vedere se è numerica usando la funzione "IsNumeric" in VBA
| 1 | MsgBox IsNumeric("123test") |
Questo codice restituirà False perché, sebbene la stringa inizi con caratteri numerici, contiene anche del testo, quindi non supera il test.
| 1 | MsgBox IsNumeric("123") |
Questo codice restituirà True perché sono tutti caratteri numerici
Esistono numerose funzioni in VBA per testare vari tipi di dati, ma queste sono le principali:
IsNumeric - verifica se un'espressione è un numero o no
IsDate - verifica se un'espressione è una data o meno
IsNull: verifica se un'espressione è nulla o meno. Un valore null può essere inserito solo in un oggetto variante, altrimenti otterrai un errore "Uso non valido di Null". Una finestra di messaggio restituisce un valore nullo se la si utilizza per porre una domanda, quindi la variabile di ritorno deve essere una variante. Tieni presente che qualsiasi calcolo che utilizza un valore null restituirà sempre il risultato di null.
IsArray - verifica se l'espressione rappresenta o meno un array
IsEmpty - verifica se l'espressione è vuota o meno. Nota che vuoto non è lo stesso di null. Una variabile è vuota quando viene definita per la prima volta ma non è un valore nullo
Sorprendentemente, non esiste alcuna funzione per IsText o IsString, che sarebbe davvero utile
Oggetti ed errori di mancata corrispondenza
Se stai utilizzando oggetti come un intervallo o un foglio, riceverai un errore di mancata corrispondenza in fase di compilazione, non in fase di esecuzione, che ti avvisa che il tuo codice non funzionerà
| 123456 | Intervallo di prova secondario()Dim MyRange As Range, I As LongImposta MyRange = Range("A1:A2")io = 10x = UsaMyRange(I)Fine sottotitolo |
| 12 | Funzione UseMyRange (R come intervallo)Fine funzione |
Questo codice ha una funzione chiamata "UseMyRange" e un parametro passato come oggetto intervallo. Tuttavia, il parametro passato è un numero intero lungo che non corrisponde al tipo di dati.
Quando esegui il codice VBA, viene immediatamente compilato e vedrai questo messaggio di errore:
Il parametro incriminato sarà evidenziato con uno sfondo blu
In genere, se si commettono errori nel codice VBA utilizzando gli oggetti, verrà visualizzato questo messaggio di errore, anziché un messaggio di mancata corrispondenza del tipo:
