Type mismatch excel как исправить
Type Mismatch Error VBA возникает при попытке назначить значение между двумя различными типами переменных.
Ошибка отображается как:
run-time error 13 – Type mismatch
Например, если вы пытаетесь поместить текст в целочисленную переменную Long или пытаетесь поместить число в переменную Date.
Давайте посмотрим на конкретный пример. Представьте, что у нас есть переменная с именем Total, которая является длинным целым числом Long.
Если мы попытаемся поместить текст в переменную, мы получим Type Mismatch Error VBA (т.е. VBA Error 13).
Давайте посмотрим на другой пример. На этот раз у нас есть переменная ReportDate типа Date.
Если мы попытаемся поместить в эту переменную не дату, мы получим Type Mismatch Error VBA.
В целом, VBA часто прощает, когда вы назначаете неправильный тип значения переменной, например:
Тем не менее, есть некоторые преобразования, которые VBA не может сделать:
Простой способ объяснить Type Mismatch Error VBA состоит в том, что элементы по обе стороны от равных оценивают другой тип.
При возникновении Type Mismatch Error это часто не так просто, как в этих примерах. В этих более сложных случаях мы можем использовать средства отладки, чтобы помочь нам устранить ошибку.
Использование отладчика
В VBA есть несколько очень мощных инструментов для поиска ошибок. Инструменты отладки позволяют приостановить выполнение кода и проверить значения в текущих переменных.
Вы можете использовать следующие шаги, чтобы помочь вам устранить любую Type Mismatch Error VBA.
- Запустите код, чтобы появилась ошибка.
- Нажмите Debug в диалоговом окне ошибки. Это выделит строку с ошибкой.
- Выберите View-> Watch из меню, если окно просмотра не видно.
- Выделите переменную слева от equals и перетащите ее в окно Watch.
- Выделите все справа от равных и перетащите его в окно Watch.
- Проверьте значения и типы каждого.
- Вы можете сузить ошибку, изучив отдельные части правой стороны.
Следующее видео показывает, как это сделать.
На скриншоте ниже вы можете увидеть типы в окне просмотра.
Используя окно просмотра, вы можете проверить различные части строки кода с ошибкой. Затем вы можете легко увидеть, что это за типы переменных.
В следующих разделах показаны различные способы возникновения Type Mismatch Error VBA.
Присвоение строки числу
Как мы уже видели, попытка поместить текст в числовую переменную может привести к Type Mismatch Error VBA.
Ниже приведены некоторые примеры, которые могут вызвать ошибку:
Недействительная дата
VBA очень гибок в назначении даты переменной даты. Если вы поставите месяц в неправильном порядке или пропустите день, VBA все равно сделает все возможное, чтобы удовлетворить вас.
В следующих примерах кода показаны все допустимые способы назначения даты, за которыми следуют случаи, которые могут привести к Type Mismatch Error VBA.
Ошибка ячейки
Если вы попытаетесь прочитать из этой ячейки, вы получите Type Mismatch Error.
Чтобы устранить эту ошибку, вы можете проверить ячейку с помощью IsError следующим образом.
Вы можете использовать следующую функцию, чтобы сделать это:
Ниже приведен пример использования этого кода.
Неверные данные ячейки
Пользователь может поместить текст, такой как «Нет», в числовое поле, не осознавая, что это приведет к Type Mismatch Error в коде.
Если мы прочитаем эти данные в числовую переменную, то получим
Type Mismatch Error VBA.
Вы можете использовать следующую функцию, чтобы проверить наличие нечисловых ячеек, прежде чем использовать данные.
Вы можете использовать это так:
Имя модуля
Если вы используете имя модуля в своем коде, это может привести к
Type Mismatch Error VBA. Однако в этом случае причина может быть не очевидной.
Например, допустим, у вас есть модуль с именем «Module1». Выполнение следующего кода приведет к о
Type Mismatch Error VBA.
Различные типы объектов
До сих пор мы рассматривали в основном переменные. Мы обычно называем переменные основными типами данных.
Они используются для хранения одного значения в памяти.
В VBA у нас также есть объекты, которые являются более сложными. Примерами являются объекты Workbook, Worksheet, Range и Chart.
Если мы назначаем один из этих типов, мы должны убедиться, что назначаемый элемент является объектом того же типа. Например:
Коллекция Sheets
Лист диаграммы создается, когда вы перемещаете диаграмму на собственный лист, щелкая правой кнопкой мыши на диаграмме и выбирая «Переместить».
Если вы читаете коллекцию Sheets с помощью переменной Worksheet, она будет работать нормально, если у вас нет рабочей таблицы.
Если у вас есть лист диаграммы, вы получите
Type Mismatch Error VBA.
В следующем коде Type Mismatch Error появится в строке «Next sh», если рабочая книга содержит лист с диаграммой.
Массивы и диапазоны
Вы можете назначить диапазон массиву и наоборот. На самом деле это очень быстрый способ чтения данных.
Проблема возникает, если ваш диапазон имеет только одну ячейку. В этом случае VBA не преобразует arr в массив.
Если вы попытаетесь использовать его как массив, вы получите
Type Mismatch Error .
В этом сценарии вы можете использовать функцию IsArray, чтобы проверить, является ли arr массивом.
Заключение
На этом мы завершаем статью об Type Mismatch Error VBA. Если у вас есть ошибка несоответствия, которая не раскрыта, пожалуйста, дайте мне знать в комментариях.
Эта статья содержит полное руководство по обработке ошибок VBA. Если вы ищете краткое резюме, посмотрите таблицу быстрого руководства в первом разделе.
Если вы ищете конкретную тему по обработке ошибок VBA, ознакомьтесь с приведенным ниже содержанием.
Если вы новичок в VBA, то вы можете прочитать пост от начала до конца, так как он выложен в логическом порядке.
Краткое руководство по обработке ошибок
Введение
Обработка ошибок относится к коду, который написан для обработки ошибок, возникающих во время работы вашего приложения. Эти ошибки обычно вызваны чем-то вне вашего контроля, например отсутствующим файлом, недоступностью базы данных, недействительными данными и т.д.
Если мы считаем, что ошибка может произойти в какой-то момент, рекомендуется написать специальный код для обработки ошибки, если она возникнет, и устранить ее.
Для всех остальных ошибок мы используем общий код для их устранения. Это где оператор обработки ошибок VBA вступает в игру. Они позволяют нашему приложению корректно обрабатывать любые ошибки, которые мы не ожидали.
Чтобы понять обработку ошибок, мы должны сначала понять различные типы ошибок в VBA.
Ошибки VBA
В VBA есть три типа ошибок
- Синтаксис
- Компиляция
- Время выполнения
Мы используем обработку ошибок для устранения ошибок во время выполнения. Давайте посмотрим на каждый из этих типов ошибок, чтобы было ясно, что такое ошибка во время выполнения.
Синтаксические ошибки
Некоторые примеры синтаксических ошибок
Синтаксические ошибки относятся только к одной строке. Они возникают, когда синтаксис одной строки неверен.
Примечание. Диалоговое окно «Ошибка синтаксиса» можно отключить, выбрав «Сервис» -> «Параметры» и отметив «Автосинтаксическая проверка». Строка по-прежнему будет отображаться красным цветом в случае ошибки, но диалоговое окно не появится.
Ошибки компиляции
Ошибки компиляции происходят более чем в одной строке. Синтаксис в одной строке правильный, но неверный, если учесть весь код проекта.
Примеры ошибок компиляции:
- Оператор If без соответствующего оператора End If
- For без Next
- Select без End Select
- Вызов Sub или Function, которые не существуют
- Вызов Sub или Function с неверными параметрами
- Присвоение Sub или Function того же имени, что и для модуля
- Переменные не объявлены (Option Explicit должен присутствовать в верхней части модуля)
На следующем снимке экрана показана ошибка компиляции, которая возникает, когда цикл For не имеет соответствующего оператора Next.
Использование Debug-> Compile
Чтобы найти ошибки компиляции, мы используем Debug-> Compile VBA Project из меню Visual Basic.
Когда вы выбираете Debug-> Compile, VBA отображает первую обнаруженную ошибку.
Когда эта ошибка исправлена, вы можете снова запустить Compile, и VBA найдет следующую ошибку.
Debug-> Compile также будет включать синтаксические ошибки в поиск, что очень полезно.
Если ошибок не осталось и вы запускаете Debug-> Compile, может показаться, что ничего не произошло. Однако «Компиляция» будет недоступна в меню «Отладка». Это означает, что ваше приложение не имеет ошибок компиляции в текущий момент.
Debug->Compile Error Summary
Debug-> Compile находит ошибки компиляции (проекта).
Он также найдет синтаксические ошибки.
Он находит одну ошибку каждый раз, когда вы ее используете.
Если нет ошибок компиляции, оставленная опция Компиляция будет отображаться серым цветом в меню.
Debug-> Compile Usage
Вы должны всегда использовать Debug-> Compile, прежде чем запускать свой код. Это гарантирует, что ваш код не будет иметь ошибок компиляции при запуске.
Если вы не запускаете Debug-> Compile, то VBA может обнаружить ошибки компиляции при запуске. Их не следует путать с ошибками времени выполнения.
Ошибки во время выполнения
Ошибки во время выполнения возникают, когда ваше приложение работает. Обычно они находятся вне вашего контроля, но могут быть вызваны ошибками в вашем коде.
Например, представьте, что ваше приложение читает из внешней рабочей книги. Если этот файл будет удален, то VBA отобразит ошибку, когда ваш код попытается открыть его.
Другие примеры ошибок времени выполнения
- база данных недоступна
- пользователь вводит неверные данные
- ячейка, содержащая текст вместо числа
Как мы уже видели, целью обработки ошибок является обработка ошибок времени выполнения, когда они возникают.
Ожидаемые и неожиданные ошибки
Когда мы думаем, что может произойти ошибка во время выполнения, мы помещаем код на место для ее обработки. Например, мы обычно помещаем код на место, чтобы иметь дело с файлом, который не найден.
Когда мы думаем, что в какой-то момент может произойти ошибка, рекомендуется добавить код для обработки ситуации. Мы обычно называем эти ошибки ожидаемыми.
Если у нас нет специального кода для обработки ошибки, это считается неожиданной ошибкой. Мы используем операторы обработки ошибок VBA для обработки непредвиденных ошибок.
Ошибки времени выполнения, которые не являются ошибками VBA
Прежде чем мы рассмотрим VBA Handling, мы должны упомянуть один тип ошибок. Некоторые ошибки во время выполнения не рассматриваются как ошибки VBA, а только пользователем.
Позвольте мне объяснить это на примере. Представьте, что у вас есть приложение, которое требует, чтобы вы добавили значения в переменные a и b
Допустим, вы по ошибке используете звездочку вместо знака плюс
Это не ошибка VBA. Ваш синтаксис кода является совершенно законным. Однако, с вашей точки зрения, это ошибка.
Заявление об ошибке
Как мы видели, есть два способа обработки ошибок во время выполнения
Оператор VBA On Error используется для обработки ошибок. Этот оператор выполняет некоторые действия при возникновении ошибки во время выполнения.
Есть четыре различных способа использовать это утверждение
Давайте посмотрим на каждое из этих утверждений по очереди.
On Error Goto 0
Это поведение по умолчанию VBA. Другими словами, если вы не используете On Error, это поведение вы увидите.
Давайте посмотрим на пример. В следующем коде мы не использовали строку On Error, поэтому VBA будет использовать поведение On Error Goto 0 по умолчанию.
Когда появляется ошибка, вы можете выбрать End или Debug
Если вы выберете Конец, то приложение просто остановится.
Если вы выберете Отладить, приложение остановится на строке ошибки, как показано на скриншоте ниже.
Это нормально, когда вы пишете код VBA, поскольку он показывает вам точную строку с ошибкой.
Это поведение не подходит для приложения, которое вы передаете пользователю. Эти ошибки выглядят непрофессионально и делают приложение нестабильным.
Подобная ошибка, по сути, приводит к сбою приложения. Пользователь не может продолжить работу без перезапуска приложения. Они могут вообще не использовать его, пока вы не исправите для них ошибку.
On Error Resume Next
Использование On Error Resume Next указывает VBA игнорировать ошибку и продолжать работу.
Есть конкретные случаи, когда это полезно. Большую часть времени вы должны избегать его использования.
Если мы добавим Resume Next к нашему примеру Sub, то VBA проигнорирует ошибку деления на ноль
Это не очень хорошая идея, чтобы сделать это. Если вы игнорируете ошибку, то поведение может быть непредсказуемым. Ошибка может повлиять на приложение несколькими способами. Вы можете получить неверные данные. Проблема в том, что вы не знаете, что что-то пошло не так, потому что вы подавили ошибку.
Приведенный ниже код является примером использования Resume Next.
В приведенном выше коде мы продолжаем, если есть ошибка. Затем в следующей строке мы проверяем значение переменной Outlook. Если произошла ошибка, тогда значение этой переменной будет установлено равным Nothing.
Это пример того, когда Резюме может быть полезным. Дело в том, что, хотя мы используем Resume, мы все равно проверяем наличие ошибки. Подавляющее большинство времени вам не нужно будет использовать Resume.
On Error Goto [label]
При возникновении ошибки вы отправляете ошибку на определенный ярлык. Обычно это внизу саба.
Давайте применим это к подводной лодке, которую мы использовали
Снимок экрана ниже показывает, что происходит при возникновении ошибки.
VBA переходит на метку eh, потому что мы указали это в строке «Перейти к ошибке».
Примечание 1: Метка, которую мы используем в операторе On… Goto, должна быть в текущей Sub / Function. Если нет, вы получите ошибку компиляции.
On Error Goto -1
Это утверждение отличается от других трех. Он используется для очистки текущей ошибки, а не для настройки конкретного поведения.
При возникновении ошибки с помощью функции On Error Goto [label] поведение обработки ошибки возвращается к поведению по умолчанию, т.е. On Error Goto 0 . Это означает, что если произойдет другая ошибка, код остановится на текущей строке.
Это поведение относится только к текущей подпрограмме. Как только мы выйдем из саба, ошибка будет очищена автоматически.
Посмотрите на код ниже. Первая ошибка приведет к переходу кода на метку eh. Вторая ошибка остановится на строке с ошибкой 1034.
Если мы добавим дальнейшую обработку ошибок, она не будет работать, поскольку ловушка ошибок не была очищена.
В коде ниже мы добавили строку
после того как мы поймаем первую ошибку.
Для устранения ошибки мы используем On Error Goto -1. Думайте об этом как об установке ловушки для мыши. Когда ловушка сработает, вам нужно установить ее снова.
В приведенном ниже коде мы добавляем эту строку, и вторая ошибка теперь приведет к переходу кода на метку eh_other.
Примечание 1. Вероятно, в редких случаях полезно использовать On Error Goto -1. Мне лично никогда не приходилось пользоваться этой линией. Помните, что как только вы выйдете из Sub, ошибка все равно будет очищена.
Примечание 2. у объекта Err есть член Clear. Использование Clear очищает текст и цифры в объекте Err, но НЕ сбрасывает ошибку.
Использование On Error
Как мы уже видели, VBA будет делать одну из трех вещей при возникновении ошибки:
- Остановитесь и отобразите ошибку.
- Игнорируйте ошибку и продолжайте.
- Перейти к определенной строке.
VBA всегда будет настроен на одно из этих действий. Когда вы используете On Error, VBA изменит ваше поведение и забудет о любом предыдущем.
В следующем подпункте VBA изменяет поведение ошибки каждый раз, когда мы используем оператор On Error
Err объект
При возникновении ошибки вы можете просмотреть детали ошибки, используя объект Err.
При возникновении ошибки времени выполнения VBA автоматически заполняет объект Err деталями.
Приведенный ниже код выведет «Error Number: 13 Type Mismatch», которое возникает, когда мы пытаемся поместить строковое значение в длинное целое число.
Свойство Err.Source кажется отличной идеей, но оно не работает при ошибке VBA. Источник вернет имя проекта, которое вряд ли сузит место возникновения ошибки. Однако, если вы создаете ошибку с помощью Err.Raise, вы можете установить источник самостоятельно, и это может быть очень полезно.
Получение номера строки
Функция Erl используется для возврата номера строки, где произошла ошибка.
Это часто вызывает путаницу. В следующем коде Erl вернет ноль.
Это потому, что нет номеров строк. Большинство людей не понимают этого, но VBA позволяет вам иметь номера строк.
Если мы изменим подпрограмму, указав номер строки, она теперь выведет 20.
Добавление номеров строк в код вручную затруднительно. Однако есть инструменты, которые позволят вам легко добавлять и удалять номера строк в подпрограмме.
Когда вы закончите работу над проектом и передадите его пользователю, в этот момент может быть полезно добавить номера строк. Если вы используете стратегию обработки ошибок в последнем разделе этого поста, то VBA сообщит строку, где произошла ошибка.
Использование Err.Raise
Когда мы создаем ошибку, используя Err.Raise, нам нужно присвоить ей номер. Мы можем использовать любое число от 513 до 65535 для нашей ошибки. Мы должны использовать vbObjectError с номером, например
Использование Err.Clear
Err.Clear используется для очистки текста и чисел из объекта Err.Object. Другими словами, он очищает описание и номер.
Редко вам понадобится его использовать, но давайте рассмотрим пример, где вы могли бы.
В приведенном ниже коде мы подсчитываем количество ошибок, которые могут возникнуть. Для простоты мы генерируем ошибку для каждого нечетного числа.
Мы проверяем номер ошибки каждый раз, когда проходим цикл. Если число не равно нулю, то произошла ошибка. Как только мы посчитаем ошибку, нам нужно установить номер ошибки на ноль, чтобы он был готов проверить следующую ошибку.
Логирование
Ведение журнала означает запись информации из вашего приложения, когда оно запущено. При возникновении ошибки вы можете записать детали в текстовый файл, чтобы у вас была запись об ошибке.
Код ниже показывает очень простую процедуру регистрации
Вы можете использовать это так:
Журнал не только для записи ошибок. Вы можете записывать другую информацию во время работы приложения. При возникновении ошибки вы можете проверить последовательность событий до того, как произошла ошибка.
Ниже приведен пример регистрации. То, как вы реализуете журналирование, зависит от характера приложения и его полезности.
Наличие большого количества информации при работе с ошибкой может быть очень полезным. Часто пользователь может не дать вам точную информацию об ошибке, которая произошла. Глядя на журнал, вы можете получить более точную информацию об информации.
Другие элементы, связанные с ошибками
В этом разделе рассматриваются некоторые другие инструменты обработки ошибок, которые есть в VBA. Эти элементы считаются устаревшими, но я включил их, поскольку они могут существовать в устаревшем коде.
Функция ошибки
Функция Error используется для печати описания ошибки с заданным номером ошибки. Он включен в VBA для обеспечения обратной совместимости и не нужен, поскольку вместо него можно использовать описание Err.Description.
Ниже приведены некоторые примеры
Заявление об ошибке
Заявление об ошибке позволяет имитировать ошибку. Он включен в VBA для обратной совместимости. Вместо этого вы должны использовать Err.Raise.
В следующем коде мы моделируем ошибку «Разделить на ноль».
Это утверждение включено в VBA для обратной совместимости. Вместо этого вы должны использовать Err.Raise.
Простая стратегия обработки ошибок
Со всеми различными опциями вы можете быть озадачены тем, как использовать обработку ошибок в VBA. В этом разделе я покажу вам, как реализовать простую стратегию обработки ошибок, которую вы можете использовать во всех своих приложениях.
Основная реализация
Это простой обзор нашей стратегии
- Поместите строку On Error Goto Label в начале нашего верхнего Sub.
- Поместите Label у обработки ошибок в конце нашего верхнего
Sub. - Если происходит ожидаемая ошибка, обработайте ее и продолжайте.
- Если приложение не может продолжить работу, используйте Err.Raise для перехода к метке обработки ошибок.
- В случае непредвиденной ошибки код автоматически перейдет к метке обработки ошибок.
На следующем рисунке показан обзор того, как это выглядит
Следующий код показывает простую реализацию этой стратегии
Это хороший способ реализации обработки ошибок, потому что
- Нам не нужно добавлять код обработки ошибок в каждую подпрограмму.
- Если возникает ошибка, то VBA корректно завершает работу приложения.
Полная стратегия обработки ошибок
Стратегия выше имеет один недостаток. Он не сообщает вам, где произошла ошибка. VBA не наполняет Err.Source чем-либо полезным, поэтому мы должны сделать это сами.
В этом разделе я собираюсь представить более полную стратегию ошибок. Я написал два сабвуфера, которые выполняют всю тяжелую работу, поэтому все, что вам нужно сделать, это добавить их в свой проект.
Целью этой стратегии является предоставление вам стека * и номера строки в случае возникновения ошибки.
Это наша стратегия
- Разместите обработку ошибок во всех подпрограммах.
- Когда происходит ошибка, обработчик ошибок добавляет подробности к ошибке и вызывает ее снова.
- Когда ошибка достигает самой верхней подпрограммы, она отображается.
Мы просто «всплываем» из-за ошибки. Следующая диаграмма показывает простое визуальное представление о том, что происходит, когда в Sub3 возникает ошибка
Это две вспомогательные подводные лодки
Пример использования этой стратегии
Вот простое кодирование, которое использует эти Sub. В этой стратегии мы не размещаем какой-либо код в верхнем подпрограмме. Мы только вызываем подводные лодки.
Результат выглядит так
Если в вашем проекте есть номера строк, результат будет содержать номер строки ошибки.
Примечание: вы можете получить следующую ошибку при использовании этого кода:
I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error:
Excel VBA Run-time error '13' Type mismatch
I didn't change anything in the macro and don't know why am I getting the error. Furthermore it takes ages to update the macro every time I put it running (the macro has to run about 9000 rows).
The error is on the line in the between ** **.
I'm using excel 2010 on windows 7.
7 Answers 7
You would get a type mismatch if Sheets(name).Cells(4 + i, 57) contains a non-numeric value. You should validate the fields before you assume they are numbers and try to subtract from them.
Update:
If you are trying to go for the quick fix of your code, however, wrap the ** line and the one following it in the following condition:
Note that your x value may not contain its expected value in the next iteration, however.
13.3k 11 11 gold badges 52 52 silver badges 81 81 bronze badges I got the error in the first line of the code you gave me "Compile error: Syntax error" Option Strict is not available in VBA for Excel, however, you can chose between these 4: Base, Compare, Explicit and Private.Thank you guys for all your help! Finally I was able to make it work perfectly thanks to a friend and also you! Here is the final code so you can also see how we solve it.
12.8k 4 4 gold badges 47 47 silver badges 81 81 bronze badges 141 1 1 gold badge 1 1 silver badge 5 5 bronze badgesJustin has given you some very fine tips :)
You will also get that error if the cell where you are performing the calculation has an error resulting from a formula.
I have made some slight changes to your code. Could you please test it for me? Copy the code with the line numbers as I have deliberately put them there.
For future readers:
This function was abending in Run-time error '13': Type mismatch
To solve it, I had to do this:
I had the same problem as you mentioned here above and my code was doing great all day yesterday.
I kept on programming this morning and when I opened my application (my file with an Auto_Open sub), I got the Run-time error '13' Type mismatch, I went on the web to find answers, I tried a lot of things, modifications and at one point I remembered that I read somewhere about "Ghost" data that stays in a cell even if we don't see it.
My code do only data transfer from one file I opened previously to another and Sum it. My code stopped at the third SheetTab (So it went right for the 2 previous SheetTab where the same code went without stopping) with the Type mismatch message. And it does that every time at the same SheetTab when I restart my code.
So I selected the cell where it stopped, manually entered 0,00 (Because the Type mismatch comes from a Summation variables declared in a DIM as Double) and copied that cell in all the subsequent cells where the same problem occurred. It solved the problem. Never had the message again. Nothing to do with my code but the "Ghost" or data from the past. It is like when you want to use the Control+End and Excel takes you where you had data once and deleted it. Had to "Save" and close the file when you wanted to use the Control+End to make sure Excel pointed you to the right cell.
Читайте также: