Wat is een Type Mismatch Error?
Een mismatch-fout kan vaak optreden wanneer u uw VBA-code uitvoert. De fout zorgt ervoor dat uw code niet volledig wordt uitgevoerd en wordt weergegeven door middel van een berichtvenster dat deze fout moet worden opgelost
Houd er rekening mee dat als u uw code niet volledig hebt getest voordat u deze aan gebruikers distribueert, dit foutbericht zichtbaar zal zijn voor gebruikers en een groot verlies van vertrouwen in uw Excel-toepassing zal veroorzaken. Helaas doen gebruikers vaak heel eigenaardige dingen met een applicatie en zijn het vaak dingen waar jij als ontwikkelaar nooit over na hebt gedacht.
Er treedt een type mismatch-fout op omdat u een variabele hebt gedefinieerd met behulp van de Dim-instructie als een bepaald type, b.v. geheel getal, datum en uw code probeert een waarde toe te kennen aan de variabele die niet acceptabel is, b.v. tekstreeks die is toegewezen aan een integer-variabele zoals in dit voorbeeld:
Hier is een voorbeeld:
Klik op Debug en de overtredende coderegel wordt geel gemarkeerd. Er is geen optie in de foutpop-up om door te gaan, aangezien dit een grote fout is en de code op geen enkele manier verder kan worden uitgevoerd.
In dit specifieke geval is de oplossing om de Dim-instructie te wijzigen in een type variabele dat werkt met de waarde die u aan de variabele toewijst. De code werkt als u het variabeletype wijzigt in 'String', en u zou waarschijnlijk ook de naam van de variabele willen wijzigen.
Als u echter het type variabele wijzigt, moet uw project opnieuw worden ingesteld en moet u uw code vanaf het begin opnieuw uitvoeren, wat erg vervelend kan zijn als er een lange procedure bij betrokken is
Mismatch-fout veroorzaakt door werkbladberekening
Het bovenstaande voorbeeld is een heel eenvoudig voorbeeld van hoe een mismatch-fout kan worden geproduceerd en in dit geval kan deze eenvoudig worden verholpen
De oorzaak van mismatch-fouten is echter meestal veel dieper dan dit, en is niet zo duidelijk wanneer u probeert uw code te debuggen.
Stel dat u bijvoorbeeld code hebt geschreven om een waarde op een bepaalde positie op een werkblad op te halen en dat het een berekening bevat die afhankelijk is van andere cellen in de werkmap (B1 in dit voorbeeld)
Het werkblad ziet eruit als dit voorbeeld, met een formule om een bepaald teken in een tekstreeks te vinden
Vanuit het oogpunt van de gebruiker is cel A1 vrij formaat en kunnen ze elke gewenste waarde invoeren. De formule zoekt echter naar een voorkomen van het teken 'B', en in dit geval wordt het niet gevonden, dus cel B1 heeft een foutwaarde.
De onderstaande testcode geeft een mismatch-fout omdat er een verkeerde waarde is ingevoerd in cel A1
| 1234 | Subtest komt niet overeen()Dim mijn nummer als geheel getalMijnNummer = Bladen("Blad1").Bereik("B1").WaardeEinde sub |
De waarde in cel B1 heeft een fout veroorzaakt omdat de gebruiker tekst in cel A1 heeft ingevoerd die niet overeenkomt met wat werd verwacht en het teken 'B' niet bevat
De code probeert de waarde toe te wijzen aan de variabele 'MyNumber' die is gedefinieerd om een geheel getal te verwachten, en dus krijg je een mismatch-fout.
Dit is een van deze voorbeelden waarbij het nauwgezet controleren van uw code niet het antwoord zal geven. Je moet ook op het werkblad kijken waar de waarde vandaan komt om erachter te komen waarom dit gebeurt.
Het probleem staat eigenlijk op het werkblad en de formule in B1 moet worden gewijzigd, zodat foutwaarden worden opgelost. U kunt dit doen door de formule 'IFERROR' te gebruiken om een standaardwaarde van 0 op te geven als het zoekteken niet wordt gevonden
U kunt dan code opnemen om te controleren op een nulwaarde en om een waarschuwingsbericht aan de gebruiker weer te geven dat de waarde in cel A1 ongeldig is
| 12345678 | Subtest komt niet overeen()Dim mijn nummer als geheel getalMijnNummer = Bladen("Blad1").Bereik("B1").TekstAls MijnNummer = 0 DanMsgBox "Waarde in cel A1 is ongeldig", vbCriticalSluit subStop alsEinde sub |
U kunt ook gegevensvalidatie gebruiken (groep Gegevenshulpmiddelen op het tabblad Gegevens van het lint) op de spreadsheet om te voorkomen dat de gebruiker doet wat hij maar wil en in de eerste plaats werkbladfouten veroorzaakt. Laat ze alleen waarden invoeren die geen werkbladfouten veroorzaken.
U kunt VBA-code schrijven op basis van de gebeurtenis Wijzigen in het werkblad om te controleren wat er is ingevoerd.
Vergrendel en beveilig het werkblad ook met een wachtwoord, zodat de ongeldige gegevens niet kunnen worden ingevoerd
Mismatch-fout veroorzaakt door ingevoerde celwaarden
Mismatch-fouten kunnen in uw code worden veroorzaakt door normale waarden uit een werkblad in te voeren (niet-fout), maar waarbij de gebruiker een onverwachte waarde heeft ingevoerd, b.v. een tekstwaarde wanneer u een getal verwachtte. Ze hebben misschien besloten om een rij binnen een reeks getallen in te voegen, zodat ze een notitie in een cel kunnen plaatsen waarin iets over het getal wordt uitgelegd. De gebruiker heeft immers geen idee hoe je code werkt en dat ze de boel gewoon uit de knoop hebben gehaald door hun notitie in te voeren.
De onderstaande voorbeeldcode maakt een eenvoudige array met de naam 'MyNumber' gedefinieerd met gehele waarden
De code doorloopt vervolgens een reeks cellen van A1 tot A7, waarbij de celwaarden aan de array worden toegewezen en een variabele 'Coun' wordt gebruikt om elke waarde te indexeren
Wanneer de code de tekstwaarde bereikt, wordt hierdoor een mismatch-fout veroorzaakt en komt alles tot stilstand
Door op 'Debug' te klikken in de pop-up met fouten, ziet u de coderegel waarin het probleem geel is gemarkeerd. Door uw cursor over een instantie van de variabele 'Coun' in de code te bewegen, kunt u de waarde van 'Coun' zien waar de code is mislukt, in dit geval 5
Als u op het werkblad kijkt, ziet u dat de 5e cel omlaag heeft de tekstwaarde en hierdoor is de code mislukt
U kunt uw code wijzigen door een voorwaarde in te voeren die eerst op een numerieke waarde controleert voordat de celwaarde aan de array wordt toegevoegd
| 12345678910111213 | Subtest komt niet overeen()Dim MyNumber (10) als geheel getal, aantal als geheel getalAantal = 1DoenAls Coun = 11 Sluit dan Do . afIf IsNumeric(Sheets("sheet1").Cells(Coun, 1).Value) DanMyNumber (Coun) = Sheets ("sheet1"). Cellen (Coun, 1). WaardeAndersMijnNummer(Coun) = 0Stop alsAantal = Aantal + 1LusEinde sub |
De code gebruikt de functie 'IsNumeric' om te testen of de waarde daadwerkelijk een getal is, en als dat zo is, wordt deze in de array ingevoerd. Als het geen getal is, voert het de waarde nul in.
Dit zorgt ervoor dat de matrixindex in lijn wordt gehouden met de celrijnummers in het werkblad.
U kunt ook code toevoegen die de oorspronkelijke foutwaarde en locatiegegevens kopieert naar een 'Fouten'-werkblad, zodat de gebruiker kan zien wat hij verkeerd heeft gedaan wanneer uw code wordt uitgevoerd.
De numerieke test gebruikt zowel de volledige code voor de cel als de code om de waarde aan de array toe te wijzen. Je zou kunnen stellen dat dit aan een variabele moet worden toegewezen om niet steeds dezelfde code te herhalen, maar het probleem is dat je de variabele als een 'Variant' zou moeten definiëren, wat niet het beste is om te doen.
U hebt ook gegevensvalidatie op het werkblad nodig en om het werkblad met een wachtwoord te beveiligen. Dit voorkomt dat de gebruiker rijen invoegt en onverwachte gegevens invoert.
Mismatch-fout veroorzaakt door het aanroepen van een functie of subroutine met behulp van parameters
Wanneer een functie wordt aangeroepen, geeft u meestal parameters door aan de functie met behulp van gegevenstypen die al door de functie zijn gedefinieerd. De functie kan er een zijn die al in VBA is gedefinieerd, of het kan een door de gebruiker gedefinieerde functie zijn die u zelf hebt gebouwd. Een subroutine kan soms ook parameters vereisen
Als u zich niet houdt aan de conventies van hoe de parameters aan de functie worden doorgegeven, krijgt u een mismatch-fout
| 12345678 | Sub-oproepfunctie()Dim Ret als geheel getalRet = MijnFunctie(3, "test")Einde subFunctie MyFunction(N As Integer, T As String) As StringMijnFunctie = TFunctie beëindigen |
Er zijn hier verschillende mogelijkheden om een mismatch-fout te krijgen
De retourvariabele (Ret) is gedefinieerd als een geheel getal, maar de functie retourneert een tekenreeks. Zodra u de code uitvoert, zal deze mislukken omdat de functie een string retourneert, en deze kan niet in een integer-variabele gaan. Interessant is dat het uitvoeren van Debug op deze code deze fout niet oppikt.
Als u aanhalingstekens plaatst rond de eerste parameter die wordt doorgegeven (3), wordt deze geïnterpreteerd als een tekenreeks, die niet overeenkomt met de definitie van de eerste parameter in de functie (geheel getal)
Als u van de tweede parameter in de functieaanroep een numerieke waarde maakt, zal deze mislukken met een mismatch omdat de tweede parameter in de tekenreeks is gedefinieerd als een tekenreeks (tekst)
Mismatch-fout veroorzaakt door verkeerd gebruik van conversiefuncties in VBA
Er zijn een aantal conversiefuncties die u in VBA kunt gebruiken om waarden naar verschillende gegevenstypen om te zetten. Een voorbeeld is 'CInt' dat een string met een getal omzet in een geheel getal.
Als de te converteren tekenreeks alfatekens bevat, krijgt u een mismatch-fout, zelfs als het eerste deel van de tekenreeks numerieke tekens bevat en de rest alfatekens zijn, b.v. ‘123abc’
Algemene preventie van niet-overeenkomende fouten
We hebben in de bovenstaande voorbeelden verschillende manieren gezien om mogelijke mismatch-fouten in uw code aan te pakken, maar er zijn een aantal andere manieren, hoewel dit misschien niet de beste opties zijn:
Definieer uw variabelen als Variant Type
Een varianttype is het standaardvariabeletype in VBA. Als je geen Dim-statement voor een variabele gebruikt en deze gewoon in je code gaat gebruiken, dan krijgt deze automatisch het type Variant.
Een Variant-variabele accepteert elk type gegevens, of het nu een geheel getal, een lang geheel getal, een getal met dubbele precisie, een boolean of een tekstwaarde is. Dit klinkt als een prachtig idee, en je vraagt je af waarom iedereen niet gewoon al zijn variabelen op variant zet.
Het variant gegevenstype heeft echter verschillende nadelen. Ten eerste neemt het veel meer geheugen in beslag dan andere gegevenstypen. Als u een zeer grote array als variant definieert, zal deze een enorme hoeveelheid geheugen opslokken wanneer de VBA-code wordt uitgevoerd en kan dit gemakkelijk prestatieproblemen veroorzaken
Ten tweede zijn de prestaties over het algemeen langzamer dan wanneer u specifieke gegevenstypen gebruikt. Als u bijvoorbeeld complexe berekeningen maakt met getallen met drijvende komma, zullen de berekeningen aanzienlijk langzamer zijn als u de getallen opslaat als varianten, in plaats van getallen met dubbele precisie.
Het gebruik van het varianttype wordt beschouwd als slordig programmeren, tenzij het absoluut noodzakelijk is.
Gebruik de opdracht OnError om fouten af te handelen
De opdracht OnError kan in uw code worden opgenomen om fouttrapping aan te pakken, zodat als er ooit een fout optreedt, de gebruiker een zinvol bericht ziet in plaats van de standaard VBA-foutpop-up
| 1234567 | Sub ErrorTrap()Dim mijn nummer als geheel getalBij fout Ga naar Err_HandlerMijnNummer = "test"Err_Handler:MsgBox "De fout " & Err.Description & " is opgetreden"Einde sub |
Dit voorkomt effectief dat de fout de goede werking van uw code verhindert en stelt de gebruiker in staat om netjes te herstellen van de foutsituatie.
De Err_Handler-routine kan meer informatie over de fout tonen en met wie hierover contact moet worden opgenomen.
Vanuit een programmeringsoogpunt is het, wanneer u een foutafhandelingsroutine gebruikt, vrij moeilijk om de regel code te lokaliseren waarop de fout zich bevindt. Als u met F8 door de code stapt, springt deze, zodra de aanstootgevende coderegel wordt uitgevoerd, naar de foutafhandelingsroutine en kunt u niet controleren waar het fout gaat.
Een manier om dit te omzeilen is door een globale constante in te stellen die True of False (Boolean) is en deze te gebruiken om de foutafhandelingsroutine in of uit te schakelen met behulp van een 'If'-statement. Als u de fout wilt testen, hoeft u alleen maar de globale constante in te stellen op False en de foutafhandelaar zal niet langer werken.
| 1 | Globale Const ErrHandling = False |
| 1234567 | Sub ErrorTrap()Dim mijn nummer als geheel getalAls ErrHandling = True, dan bij fout Ga naar Err_HandlerMijnNummer = "test"Err_Handler:MsgBox "De fout " & Err.Description & " is opgetreden"Einde sub |
Het enige probleem hiermee is dat de gebruiker de fout kan herstellen, maar de rest van de code in de subroutine wordt niet uitgevoerd, wat later in de toepassing enorme gevolgen kan hebben
Met behulp van het eerdere voorbeeld van het doorlopen van een reeks cellen, zou de code naar cel A5 gaan en de niet-overeenkomende fout raken. De gebruiker zou een berichtvenster zien met informatie over de fout, maar vanaf die cel zou niets in het bereik worden verwerkt.
Gebruik de opdracht OnError om fouten te onderdrukken
Dit maakt gebruik van de opdracht 'On Error Resume Next'. Dit is erg gevaarlijk om in uw code op te nemen, omdat het voorkomt dat eventuele latere fouten worden weergegeven. Dit betekent in feite dat terwijl uw code wordt uitgevoerd, als er een fout optreedt in een regel code, de uitvoering gewoon naar de volgende beschikbare regel gaat zonder de foutregel uit te voeren, en gewoon doorgaat.
Dit kan een mogelijke foutsituatie oplossen, maar het heeft nog steeds invloed op elke toekomstige fout in de code. Je zou dan kunnen denken dat je code vrij is van bugs, maar in feite is dat niet zo en delen van je code doen niet wat je denkt dat het zou moeten doen.
Er zijn situaties waarin het nodig is om deze opdracht te gebruiken, bijvoorbeeld als u een bestand verwijdert met de opdracht 'Kill' (als het bestand niet aanwezig is, treedt er een fout op), maar de fout-trapping moet altijd worden teruggezet aan onmiddellijk na waar de mogelijke fout zou kunnen optreden met behulp van:
| 1 | Bij fout Ga naar 0 |
In het eerdere voorbeeld van het doorlopen van een reeks cellen, met behulp van 'On Error Resume Next', zou dit de lus in staat stellen om door te gaan, maar de cel die de fout veroorzaakt, zou niet worden overgebracht naar de array, en het array-element voor die specifieke index een nulwaarde zou hebben.
De gegevens converteren naar een gegevenstype dat overeenkomt met de aangifte
U kunt VBA-functies gebruiken om het gegevenstype van inkomende gegevens te wijzigen, zodat het overeenkomt met het gegevenstype van de ontvangende variabele.
U kunt dit doen door parameters door te geven aan functies. Als u bijvoorbeeld een getal hebt dat in een tekenreeksvariabele zit en u wilt dit als een getal doorgeven aan een functie, kunt u CInt gebruiken
Er zijn een aantal van deze conversiefuncties die kunnen worden gebruikt, maar dit zijn de belangrijkste:
CInt - converteert een tekenreeks met een numerieke waarde (onder + of - 32.768) naar een geheel getal. Houd er rekening mee dat hiermee eventuele decimale punten worden afgekapt
CLng - Converteert een tekenreeks met een grote numerieke waarde naar een lang geheel getal. Decimale punten worden afgekapt.
CDbl - Converteert een tekenreeks met een getal met een zwevend decimaalteken naar een getal met dubbele precisie. Inclusief decimale punten
CDate - Converteert een string die een datum bevat naar een datumvariabele. Gedeeltelijk afhankelijk van de instellingen in het Configuratiescherm van Windows en uw landinstelling hoe de datum wordt geïnterpreteerd
CStr - Converteert een numerieke of datumwaarde naar een tekenreeks
Bij het converteren van een string naar een getal of een datum mag de string niets anders bevatten dan getallen of een datum. Als alfatekens aanwezig zijn, levert dit een mismatch-fout op. Hier is een voorbeeld dat een mismatch-fout oplevert:
| 123 | Subtest()MsgBox CInt("123abc")Einde sub |
Variabelen testen binnen uw code
U kunt een variabele testen om erachter te komen welk gegevenstype het is voordat u het toewijst aan een variabele van een bepaald type.
U kunt bijvoorbeeld een tekenreeks controleren om te zien of deze numeriek is door de functie 'IsNumeriek' in VBA te gebruiken
| 1 | MsgBox IsNumeric("123test") |
Deze code retourneert False, want hoewel de tekenreeks begint met numerieke tekens, bevat deze ook tekst, zodat deze niet door de test wordt gehaald.
| 1 | MsgBox IsNumeriek("123") |
Deze code retourneert True omdat het allemaal numerieke tekens zijn
Er zijn een aantal functies in VBA om te testen op verschillende gegevenstypen, maar dit zijn de belangrijkste:
IsNumeric - test of een uitdrukking een getal is of niet
IsDate - test of een uitdrukking een datum is of niet
IsNull - test of een expressie null is of niet. Een null-waarde kan alleen in een variantobject worden geplaatst, anders krijgt u de foutmelding 'Ongeldig gebruik van Null'. Een berichtvenster retourneert een null-waarde als u deze gebruikt om een vraag te stellen, dus de return-variabele moet een variant zijn. Houd er rekening mee dat elke berekening met een null-waarde altijd het resultaat null oplevert.
IsArray - test of de uitdrukking een array vertegenwoordigt of niet
IsEmpty - test of de expressie leeg is of niet. Merk op dat leeg niet hetzelfde is als null. Een variabele is leeg wanneer deze voor het eerst wordt gedefinieerd, maar het is geen null-waarde
Verrassend genoeg is er geen functie voor IsText of IsString, wat erg handig zou zijn
Objecten en niet-overeenkomende fouten
Als u objecten zoals een bereik of een blad gebruikt, krijgt u een mismatch-fout tijdens het compileren, niet tijdens runtime, waardoor u gewaarschuwd wordt dat uw code niet gaat werken
| 123456 | Subtestbereik()Dim MyRange als bereik, ik zo langStel MyRange = Bereik ("A1:A2") inik = 10x = GebruikMijnBereik(I)Einde sub |
| 12 | Functie UseMyRange(R As Range)Functie beëindigen |
Deze code heeft een functie genaamd 'UseMyRange' en een parameter die wordt doorgegeven als een bereikobject. De parameter die wordt doorgegeven, is echter een lang geheel getal dat niet overeenkomt met het gegevenstype.
Wanneer u VBA-code uitvoert, wordt deze onmiddellijk gecompileerd en ziet u deze foutmelding:
De gewraakte parameter wordt gemarkeerd met een blauwe achtergrond
Over het algemeen, als u fouten maakt in VBA-code met behulp van objecten, ziet u dit foutbericht in plaats van een bericht dat niet overeenkomt met het type:
