Was ist ein Typ-Mismatch-Fehler?
Beim Ausführen Ihres VBA-Codes kann häufig ein Nichtübereinstimmungsfehler auftreten. Der Fehler verhindert, dass Ihr Code vollständig ausgeführt wird und zeigt in einer Meldungsbox an, dass dieser Fehler behoben werden muss
Beachten Sie, dass diese Fehlermeldung für die Benutzer sichtbar ist und zu einem großen Vertrauensverlust in Ihre Excel-Anwendung führt, wenn Sie Ihren Code vor der Verteilung an die Benutzer nicht vollständig getestet haben. Leider machen Benutzer oft sehr eigenartige Dinge mit einer Anwendung und sind oft Dinge, die Sie als Entwickler nie in Betracht gezogen haben.
Ein Typ-Mismatch-Fehler tritt auf, weil Sie eine Variable mit der Dim-Anweisung als einen bestimmten Typ definiert haben, z. Integer, Datum und Ihr Code versucht, der Variablen einen Wert zuzuweisen, der nicht akzeptabel ist, z. Textzeichenfolge, die einer Integer-Variablen zugewiesen wird, wie in diesem Beispiel:
Hier ist ein Beispiel:
Klicken Sie auf Debug und die fehlerhafte Codezeile wird gelb hervorgehoben. Im Fehler-Popup gibt es keine Option zum Fortfahren, da dies ein schwerwiegender Fehler ist und der Code nicht weiter ausgeführt werden kann.
In diesem speziellen Fall besteht die Lösung darin, die Dim-Anweisung in einen Variablentyp zu ändern, der mit dem Wert arbeitet, den Sie der Variablen zuweisen. Der Code funktioniert, wenn Sie den Variablentyp in „String“ ändern, und Sie möchten wahrscheinlich auch den Variablennamen ändern.
Das Ändern des Variablentyps erfordert jedoch ein Zurücksetzen Ihres Projekts und Sie müssen Ihren Code von Anfang an erneut ausführen, was bei einem langen Vorgang sehr ärgerlich sein kann
Nichtübereinstimmungsfehler verursacht durch Arbeitsblattberechnung
Das obige Beispiel ist ein sehr einfaches Beispiel dafür, wie ein Mismatch-Fehler erzeugt werden kann und in diesem Fall leicht behoben werden kann
Die Ursache von Nichtübereinstimmungsfehlern liegt jedoch normalerweise viel tiefer und ist nicht so offensichtlich, wenn Sie versuchen, Ihren Code zu debuggen.
Angenommen, Sie haben Code zum Aufnehmen eines Werts an einer bestimmten Position in einem Arbeitsblatt geschrieben und er enthält eine berechnungsabhängige andere Zellen in der Arbeitsmappe (B1 in diesem Beispiel).
Das Arbeitsblatt sieht wie in diesem Beispiel aus, mit einer Formel, um ein bestimmtes Zeichen in einer Textfolge zu finden
Aus Sicht des Benutzers ist Zelle A1 ein freies Format und er kann jeden beliebigen Wert eingeben. Die Formel sucht jedoch nach einem Vorkommen des Zeichens „B“ und wird in diesem Fall nicht gefunden, sodass Zelle B1 einen Fehlerwert hat.
Der folgende Testcode erzeugt einen Nichtübereinstimmungsfehler, da in Zelle A1 ein falscher Wert eingegeben wurde
| 1234 | Unter TestMismatch()Dim MyNumber als IntegerMyNumber = Sheets("Sheet1").Range("B1").ValueEnd Sub |
Der Wert in Zelle B1 hat einen Fehler erzeugt, weil der Benutzer Text in Zelle A1 eingegeben hat, der nicht den Erwartungen entspricht und das Zeichen "B" nicht enthält.
Der Code versucht, den Wert der Variablen „MyNumber“ zuzuweisen, die so definiert wurde, dass sie eine ganze Zahl erwartet, und Sie erhalten einen Nichtübereinstimmungsfehler.
Dies ist eines dieser Beispiele, bei denen eine sorgfältige Überprüfung Ihres Codes keine Antwort liefert. Sie müssen auch auf dem Arbeitsblatt nachsehen, woher der Wert stammt, um herauszufinden, warum dies geschieht.
Das Problem befindet sich tatsächlich auf dem Arbeitsblatt, und die Formel in B1 muss geändert werden, damit Fehlerwerte behandelt werden. Sie können dies tun, indem Sie die Formel „IFERROR“ verwenden, um einen Standardwert von 0 bereitzustellen, wenn das Suchzeichen nicht gefunden wird
Sie können dann Code integrieren, um nach einem Nullwert zu suchen und dem Benutzer eine Warnmeldung anzuzeigen, dass der Wert in Zelle A1 ungültig ist
| 12345678 | Unter TestMismatch()Dim MyNumber als IntegerMyNumber = Sheets("Sheet1").Range("B1").TextWenn MyNumber = 0 DannMsgBox "Wert in Zelle A1 ist ungültig", vbCriticalAbo beendenEnde WennEnd Sub |
Sie können auch die Datenvalidierung (Gruppe Datentools auf der Registerkarte Daten des Menübands) in der Tabelle verwenden, um den Benutzer daran zu hindern, alles zu tun, was er möchte, und Arbeitsblattfehler von vornherein zu verursachen. Erlauben Sie ihnen nur, Werte einzugeben, die keine Arbeitsblattfehler verursachen.
Sie können VBA-Code basierend auf dem Change-Ereignis im Arbeitsblatt schreiben, um zu überprüfen, was eingegeben wurde.
Sperren und schützen Sie das Arbeitsblatt auch mit einem Passwort, damit die ungültigen Daten nicht eingegeben werden können
Nichtübereinstimmungsfehler verursacht durch eingegebene Zellenwerte
Nichtübereinstimmungsfehler können in Ihrem Code verursacht werden, indem normale Werte aus einem Arbeitsblatt (kein Fehler) eingefügt werden, der Benutzer jedoch einen unerwarteten Wert eingegeben hat, z. einen Textwert, wenn Sie eine Zahl erwartet haben. Sie haben sich möglicherweise entschieden, eine Zeile innerhalb eines Zahlenbereichs einzufügen, damit sie eine Notiz in eine Zelle einfügen können, in der etwas über die Zahl erklärt wird. Schließlich hat der Nutzer keine Ahnung, wie Ihr Code funktioniert und dass er das Ganze mit der Eingabe seiner Notiz einfach aus dem Gleichgewicht gebracht hat.
Der folgende Beispielcode erstellt ein einfaches Array namens „MyNumber“, das mit ganzzahligen Werten definiert ist
Der Code durchläuft dann einen Bereich der Zellen von A1 bis A7 und weist die Zellenwerte dem Array zu, wobei eine Variable "Count" verwendet wird, um jeden Wert zu indizieren
Wenn der Code den Textwert erreicht, wird dadurch ein Mismatch-Fehler verursacht und alles kommt zum Stillstand
Wenn Sie im Fehler-Popup auf „Debug“ klicken, sehen Sie die Codezeile, in der das Problem gelb hervorgehoben ist. Wenn Sie mit dem Cursor über eine beliebige Instanz der Variablen „Coun“ im Code fahren, können Sie den Wert von „Coun“ sehen, bei dem der Code fehlgeschlagen ist, in diesem Fall 5
Auf dem Arbeitsblatt sehen Sie, dass die 5NS Zelle unten hat den Textwert und dies hat dazu geführt, dass der Code fehlgeschlagen ist
Sie können Ihren Code ändern, indem Sie eine Bedingung eingeben, die zuerst nach einem numerischen Wert sucht, bevor Sie den Zellenwert zum Array hinzufügen
| 12345678910111213 | Unter TestMismatch()Dim MyNumber(10) As Integer, Count As IntegerAnzahl = 1TunWenn Coun = 11 Dann Beenden DoIf IsNumeric(Sheets("sheet1").Cells(Count, 1).Value) ThenMyNumber(Count) = Sheets("sheet1").Cells(Count, 1).ValueAndersMyNumber(Count) = 0Ende WennLand = Land + 1SchleifeEnd Sub |
Der Code verwendet die Funktion 'IsNumeric', um zu testen, ob der Wert tatsächlich eine Zahl ist, und wenn dies der Fall ist, trägt er ihn in das Array ein. Wenn es keine Zahl ist, wird der Wert Null eingegeben.
Dadurch wird sichergestellt, dass der Array-Index mit den Zellenzeilennummern in der Kalkulationstabelle übereinstimmt.
Sie können auch Code hinzufügen, der den ursprünglichen Fehlerwert und die Positionsdetails in ein Arbeitsblatt "Fehler" kopiert, damit der Benutzer sehen kann, was er bei der Ausführung Ihres Codes falsch gemacht hat.
Der numerische Test verwendet den vollständigen Code für die Zelle sowie den Code, um den Wert dem Array zuzuweisen. Sie könnten argumentieren, dass dies einer Variablen zugewiesen werden sollte, um nicht immer denselben Code zu wiederholen, aber das Problem besteht darin, dass Sie die Variable als „Variante“ definieren müssten, was nicht das Beste ist.
Sie benötigen außerdem eine Datenvalidierung auf dem Arbeitsblatt und einen Kennwortschutz für das Arbeitsblatt. Dadurch wird verhindert, dass der Benutzer Zeilen einfügt und unerwartete Daten eingibt.
Nichtübereinstimmungsfehler durch Aufruf einer Funktion oder Unterroutine mit Parametern
Wenn eine Funktion aufgerufen wird, übergeben Sie normalerweise Parameter an die Funktion unter Verwendung von Datentypen, die bereits von der Funktion definiert wurden. Die Funktion kann eine bereits in VBA definierte oder eine benutzerdefinierte Funktion sein, die Sie selbst erstellt haben. Eine Unterroutine kann manchmal auch Parameter erfordern
Wenn Sie sich nicht an die Konventionen halten, wie die Parameter an die Funktion übergeben werden, erhalten Sie einen Nichtübereinstimmungsfehler
| 12345678 | Sub CallFunction()Dim Ret als IntegerRet = MyFunction(3, "test")End SubFunktion MyFunction(N als Integer, T als String) als StringMyFunction = TEndfunktion |
Hier gibt es mehrere Möglichkeiten, einen Mismatch-Fehler zu erhalten
Die Rückgabevariable (Ret) ist als Integer definiert, aber die Funktion gibt einen String zurück. Sobald Sie den Code ausführen, wird er fehlschlagen, da die Funktion einen String zurückgibt, der nicht in eine Integer-Variable eingehen kann. Interessanterweise wird dieser Fehler nicht behoben, wenn Debug für diesen Code ausgeführt wird.
Wenn Sie den ersten übergebenen Parameter in Anführungszeichen setzen (3), wird er als String interpretiert, der nicht mit der Definition des ersten Parameters in der Funktion (Ganzzahl) übereinstimmt.
Wenn Sie den zweiten Parameter im Funktionsaufruf zu einem numerischen Wert machen, schlägt dies mit einer Nichtübereinstimmung fehl, da der zweite Parameter in der Zeichenfolge als Zeichenfolge (Text) definiert ist.
Nichtübereinstimmungsfehler durch falsche Verwendung von Konvertierungsfunktionen in VBA
Es gibt eine Reihe von Konvertierungsfunktionen, die Sie in VBA verwenden können, um Werte in verschiedene Datentypen zu konvertieren. Ein Beispiel ist „CInt“, das einen String mit einer Zahl in einen Integer-Wert umwandelt.
Wenn die zu konvertierende Zeichenfolge alphabetische Zeichen enthält, erhalten Sie einen Nichtübereinstimmungsfehler, selbst wenn der erste Teil der Zeichenfolge numerische Zeichen enthält und der Rest aus alphabetischen Zeichen besteht, z. „123abc“
Allgemeine Vermeidung von Mismatch-Fehlern
In den obigen Beispielen haben wir mehrere Möglichkeiten zum Umgang mit potenziellen Nichtübereinstimmungsfehlern in Ihrem Code gesehen, aber es gibt eine Reihe anderer Möglichkeiten, obwohl dies möglicherweise nicht die besten Optionen sind:
Definieren Sie Ihre Variablen als Variantentyp
Ein Variantentyp ist der Standardvariablentyp in VBA. Wenn Sie für eine Variable keine Dim-Anweisung verwenden und sie einfach in Ihrem Code verwenden, wird ihr automatisch der Typ Variant zugewiesen.
Eine Variant-Variable akzeptiert jeden Datentyp, sei es eine Ganzzahl, eine lange Ganzzahl, eine Zahl mit doppelter Genauigkeit, ein boolescher Wert oder ein Textwert. Das klingt nach einer wunderbaren Idee, und Sie fragen sich, warum nicht jeder alle seine Variablen auf Variant setzt.
Der Datentyp Variant hat jedoch mehrere Nachteile. Erstens benötigt es viel mehr Speicher als andere Datentypen. Wenn Sie ein sehr großes Array als Variante definieren, wird es beim Ausführen des VBA-Codes sehr viel Speicher verbrauchen und kann leicht zu Leistungsproblemen führen
Zweitens ist die Leistung im Allgemeinen langsamer, als wenn Sie bestimmte Datentypen verwenden. Wenn Sie beispielsweise komplexe Berechnungen mit Gleitkommazahlen durchführen, werden die Berechnungen erheblich langsamer, wenn Sie die Zahlen als Varianten und nicht als Zahlen mit doppelter Genauigkeit speichern
Die Verwendung des Variantentyps gilt als schlampige Programmierung, es sei denn, es besteht eine absolute Notwendigkeit dafür.
Verwenden Sie den OnError-Befehl, um Fehler zu behandeln
Der Befehl OnError kann in Ihren Code eingefügt werden, um Fehlerabfangen zu beheben, sodass der Benutzer bei Auftreten eines Fehlers eine aussagekräftige Meldung anstelle des standardmäßigen VBA-Fehler-Popup-Fensters sieht
| 1234567 | Sub ErrorTrap()Dim MyNumber als IntegerBei Fehler GoTo Err_HandlerMyNumber = "test"Err_Handler:MsgBox "Der Fehler " & Err.Beschreibung & " ist aufgetreten"End Sub |
Dies verhindert effektiv, dass der Fehler den reibungslosen Ablauf Ihres Codes stoppt, und ermöglicht dem Benutzer eine saubere Wiederherstellung der Fehlersituation.
Die Err_Handler-Routine könnte weitere Informationen über den Fehler anzeigen und an wen man sich wenden kann.
Aus programmiertechnischer Sicht ist es bei Verwendung einer Fehlerbehandlungsroutine ziemlich schwierig, die Codezeile zu finden, in der sich der Fehler befindet. Wenn Sie den Code mit F8 durchgehen, springt er, sobald die fehlerhafte Codezeile ausgeführt wird, zur Fehlerbehandlungsroutine, und Sie können nicht überprüfen, wo er schief geht.
Eine Möglichkeit, dies zu umgehen, besteht darin, eine globale Konstante einzurichten, die True oder False (Boolean) ist, und diese zu verwenden, um die Fehlerbehandlungsroutine mit einer 'If'-Anweisung ein- oder auszuschalten. Wenn Sie den Fehler testen möchten, müssen Sie nur die globale Konstante auf False setzen und die Fehlerbehandlung funktioniert nicht mehr.
| 1 | Global Const ErrHandling = False |
| 1234567 | Sub ErrorTrap()Dim MyNumber als IntegerWenn ErrHandling = True, dann bei Fehler GoTo Err_HandlerMyNumber = "test"Err_Handler:MsgBox "Der Fehler " & Err.Beschreibung & " ist aufgetreten"End Sub |
Das einzige Problem dabei ist, dass der Benutzer den Fehler beheben kann, der Rest des Codes innerhalb der Unterroutine jedoch nicht ausgeführt wird, was später in der Anwendung enorme Auswirkungen haben kann
Bei Verwendung des früheren Beispiels des Durchlaufens einer Reihe von Zellen würde der Code zu Zelle A5 gelangen und den nicht übereinstimmenden Fehler treffen. Der Benutzer würde ein Meldungsfeld mit Informationen zu dem Fehler sehen, aber ab dieser Zelle im Bereich würde nichts mehr verarbeitet.
Verwenden Sie den OnError-Befehl, um Fehler zu unterdrücken
Dies verwendet den Befehl 'On Error Resume Next'. Es ist sehr gefährlich, dies in Ihren Code aufzunehmen, da es verhindert, dass nachfolgende Fehler angezeigt werden. Dies bedeutet im Wesentlichen, dass bei der Ausführung Ihres Codes, wenn ein Fehler in einer Codezeile auftritt, die Ausführung einfach zur nächsten verfügbaren Zeile übergeht, ohne die Fehlerzeile auszuführen, und wie gewohnt fortfahren.
Dies kann eine potenzielle Fehlersituation beheben, wirkt sich jedoch weiterhin auf jeden zukünftigen Fehler im Code aus. Sie denken dann vielleicht, dass Ihr Code fehlerfrei ist, aber in Wirklichkeit ist dies nicht der Fall und Teile Ihres Codes tun nicht das, was Sie denken, dass er tun sollte.
Es gibt Situationen, in denen es notwendig ist, diesen Befehl zu verwenden, z. B. wenn Sie eine Datei mit dem Befehl 'Kill' löschen (wenn die Datei nicht vorhanden ist, tritt ein Fehler auf), aber die Fehlerabfangfunktion sollte immer zurückgeschaltet werden unmittelbar danach, wo der potenzielle Fehler auftreten könnte, mit:
| 1 | Bei Fehler Gehe zu 0 |
In dem früheren Beispiel des Schleifens durch einen Zellbereich mit 'On Error Resume Next' würde dies die Fortsetzung der Schleife ermöglichen, aber die Zelle, die den Fehler verursacht, würde nicht in das Array übertragen und das Array-Element für diesen bestimmten Index würde einen Nullwert halten.
Konvertieren der Daten in einen Datentyp, der der Deklaration entspricht
Sie können VBA-Funktionen verwenden, um den Datentyp eingehender Daten so zu ändern, dass er dem Datentyp der empfangenden Variablen entspricht.
Sie können dies tun, wenn Sie Parameter an Funktionen übergeben. Wenn Sie beispielsweise eine Zahl in einer String-Variablen haben und diese als Zahl an eine Funktion übergeben möchten, können Sie CInt . verwenden
Es gibt eine Reihe dieser Konvertierungsfunktionen, die verwendet werden können, aber hier sind die wichtigsten:
CInt - wandelt einen String mit einem numerischen Wert (unter + oder - 32.768) in einen ganzzahligen Wert um. Beachten Sie, dass hierdurch alle Dezimalpunkte abgeschnitten werden
CLng - Konvertiert eine Zeichenfolge mit einem großen numerischen Wert in eine lange Ganzzahl. Dezimalpunkte werden abgeschnitten.
CDbl - Konvertiert einen String, der eine Gleitkommazahl enthält, in eine Zahl mit doppelter Genauigkeit. Enthält Dezimalpunkte
CDate - Konvertiert einen String, der ein Datum enthält, in eine Datumsvariable. Hängt teilweise von den Einstellungen in der Windows-Systemsteuerung und Ihrem Gebietsschema ab, wie das Datum interpretiert wird
CStr - Konvertiert einen numerischen oder Datumswert in eine Zeichenfolge
Beim Konvertieren von einer Zeichenfolge in eine Zahl oder ein Datum darf die Zeichenfolge nichts anderes als Zahlen oder ein Datum enthalten. Wenn Alphazeichen vorhanden sind, führt dies zu einem Nichtübereinstimmungsfehler. Hier ist ein Beispiel, das einen Nichtübereinstimmungsfehler erzeugt:
| 123 | Teiltest()MsgBox CInt("123abc")End Sub |
Testen von Variablen in Ihrem Code
Sie können eine Variable testen, um herauszufinden, um welchen Datentyp es sich handelt, bevor Sie sie einer Variablen eines bestimmten Typs zuweisen.
Sie können beispielsweise eine Zeichenfolge überprüfen, um festzustellen, ob sie numerisch ist, indem Sie die Funktion "IsNumeric" in VBA verwenden
| 1 | MsgBox IsNumeric("123test") |
Dieser Code gibt False zurück, da die Zeichenfolge zwar mit numerischen Zeichen beginnt, aber auch Text enthält, sodass sie den Test nicht besteht.
| 1 | MsgBox IsNumeric("123") |
Dieser Code gibt True zurück, da es sich ausschließlich um numerische Zeichen handelt
Es gibt eine Reihe von Funktionen in VBA, um verschiedene Datentypen zu testen, aber dies sind die wichtigsten:
IsNumeric - testet, ob ein Ausdruck eine Zahl ist oder nicht
IsDate - testet, ob ein Ausdruck ein Datum ist oder nicht
IsNull - testet, ob ein Ausdruck null ist oder nicht. Ein Nullwert kann nur in ein Variantenobjekt eingefügt werden, andernfalls erhalten Sie eine Fehlermeldung „Ungültige Verwendung von Null“. Ein Meldungsfeld gibt einen Nullwert zurück, wenn Sie es verwenden, um eine Frage zu stellen, daher muss die Rückgabevariable eine Variante sein. Beachten Sie, dass jede Berechnung mit einem Nullwert immer das Ergebnis Null zurückgibt.
IsArray - testet, ob der Ausdruck ein Array darstellt oder nicht
IsEmpty - testet, ob der Ausdruck leer ist oder nicht. Beachten Sie, dass leer nicht gleich null ist. Eine Variable ist bei ihrer ersten Definition leer, aber kein Nullwert
Überraschenderweise gibt es keine Funktion für IsText oder IsString, die wirklich nützlich wäre
Objekte und Nichtübereinstimmungsfehler
Wenn Sie Objekte wie einen Bereich oder ein Blatt verwenden, erhalten Sie zur Kompilierzeit einen Nichtübereinstimmungsfehler, nicht zur Laufzeit, was Sie rechtzeitig warnt, dass Ihr Code nicht funktioniert
| 123456 | Untertestbereich()Dim MyRange As Range, I As LongSet MyRange = Range("A1:A2")ich = 10x = UseMyRange(I)End Sub |
| 12 | Funktion UseMyRange(R als Bereich)Endfunktion |
Dieser Code hat eine Funktion namens ‚UseMyRange‘ und einen Parameter, der als Range-Objekt übergeben wird. Der übergebene Parameter ist jedoch ein Long Integer, der nicht zum Datentyp passt.
Wenn Sie VBA-Code ausführen, wird er sofort kompiliert und Sie sehen diese Fehlermeldung:
Der anstößige Parameter wird mit einem blauen Hintergrund hervorgehoben
Wenn Sie im VBA-Code mit Objekten Fehler machen, wird im Allgemeinen diese Fehlermeldung anstelle einer Typkonfliktmeldung angezeigt:
