Что такое ошибка несоответствия типа?
Ошибка несоответствия часто может возникать при запуске кода VBA. Ошибка приведет к тому, что ваш код не будет полностью запущен, и появится сообщение о том, что эту ошибку необходимо устранить.
Обратите внимание, что если вы не полностью протестировали свой код перед распространением среди пользователей, это сообщение об ошибке будет видно пользователям и вызовет большую потерю доверия к вашему приложению Excel. К сожалению, пользователи часто делают с приложением очень необычные вещи, о которых вы, как разработчик, никогда не задумывались.
Ошибка несоответствия типа возникает из-за того, что вы определили переменную с помощью оператора Dim как определенный тип, например. целое число, дата и ваш код пытается присвоить значение переменной, которое неприемлемо, например. текстовая строка, присвоенная целочисленной переменной, как в этом примере:
Вот пример:
Нажмите «Отладка», и вызывающая нарушение строка кода будет выделена желтым цветом. Во всплывающем окне с ошибкой нет возможности продолжить, так как это серьезная ошибка, и код не может работать дальше.
В этом конкретном случае решение состоит в том, чтобы изменить оператор Dim на тип переменной, который работает со значением, которое вы присваиваете переменной. Код будет работать, если вы измените тип переменной на «String», и вы, вероятно, захотите также изменить имя переменной.
Однако изменение типа переменной потребует сброса вашего проекта, и вам придется снова запускать свой код с самого начала, что может быть очень раздражающим, если задействована длинная процедура.
Ошибка несоответствия, вызванная расчетом рабочего листа
Приведенный выше пример - очень простой пример того, как может возникнуть ошибка несоответствия, и в этом случае ее легко исправить.
Однако причина ошибок несоответствия обычно гораздо глубже, чем эта, и не так очевидна, когда вы пытаетесь отладить свой код.
В качестве примера предположим, что вы написали код для выбора значения в определенной позиции на листе, и он содержит зависимые от вычислений другие ячейки в книге (B1 в этом примере).
Рабочий лист выглядит как этот пример, с формулой для поиска определенного символа в строке текста.
С точки зрения пользователя, ячейка A1 имеет свободный формат, и они могут вводить любое значение, которое захотят. Однако формула ищет вхождение символа «B», и в этом случае он не найден, поэтому в ячейке B1 содержится значение ошибки.
Приведенный ниже тестовый код приведет к ошибке несоответствия, поскольку в ячейку A1 было введено неправильное значение.
| 1234 | Sub TestMismatch ()Dim MyNumber как целое числоMyNumber = Sheets ("Sheet1"). Range ("B1"). ValueКонец подписки |
Значение в ячейке B1 вызвало ошибку, потому что пользователь ввел текст в ячейку A1, который не соответствует ожидаемому и не содержит символа «B».
Код пытается присвоить значение переменной «MyNumber», которая была определена как ожидание целого числа, и поэтому вы получаете ошибку несоответствия.
Это один из тех примеров, когда тщательная проверка кода не дает ответа. Вам также необходимо посмотреть на лист, откуда берется значение, чтобы выяснить, почему это происходит.
Проблема на самом деле находится на рабочем листе, и формулу в B1 нужно изменить, чтобы значения ошибок были обработаны. Вы можете сделать это, используя формулу «ЕСЛИОШИБКА», чтобы указать значение по умолчанию 0, если поисковый символ не найден.
Затем вы можете включить код для проверки нулевого значения и отображения предупреждающего сообщения для пользователя о том, что значение в ячейке A1 недопустимо.
| 12345678 | Sub TestMismatch ()Dim MyNumber как целое числоMyNumber = Sheets ("Sheet1"). Range ("B1"). TextЕсли MyNumber = 0, тоMsgBox «Недопустимое значение в ячейке A1», vbCriticalВыйти из подводной лодкиКонец, еслиКонец подписки |
Вы также можете использовать проверку данных (группа «Инструменты данных» на вкладке «Данные» на ленте) в электронной таблице, чтобы не дать пользователю делать все, что ему нравится, и в первую очередь вызвать ошибки на листе. Разрешите им вводить только значения, которые не вызовут ошибок на листе.
Вы можете написать код VBA на основе события Change на листе, чтобы проверить, что было введено.
Также заблокируйте и защитите паролем рабочий лист, чтобы нельзя было ввести недопустимые данные.
Ошибка несоответствия, вызванная введенными значениями ячеек
Ошибки несоответствия могут быть вызваны в вашем коде путем ввода нормальных значений из рабочего листа (без ошибок), но когда пользователь ввел неожиданное значение, например текстовое значение, когда вы ожидали число. Возможно, они решили вставить строку в диапазон чисел, чтобы они могли поместить заметку в ячейку, объясняющую что-то о числе. В конце концов, пользователь понятия не имеет, как работает ваш код, и что он просто выбросил все из строя, введя свою заметку.
В приведенном ниже примере кода создается простой массив с именем MyNumber, определенный с целочисленными значениями.
Затем код выполняет итерацию по диапазону ячеек от A1 до A7, присваивая значения ячеек в массив, используя переменную «Coun» для индексации каждого значения.
Когда код достигает текстового значения, это вызывает ошибку несоответствия, и все останавливается
Нажав «Отладка» во всплывающем окне с ошибкой, вы увидите строку кода, в которой проблема выделена желтым цветом. Наведя курсор на любой экземпляр переменной «Coun» в коде, вы сможете увидеть значение «Coun», в котором произошел сбой кода, которое в данном случае равно 5.
Посмотрев на лист, вы увидите, что 5th ячейка вниз имеет текстовое значение, и это привело к сбою кода
Вы можете изменить свой код, поставив условие, которое сначала проверяет числовое значение, прежде чем добавлять значение ячейки в массив.
| 12345678910111213 | Sub TestMismatch ()Dim MyNumber (10) как целое число, счетчик как целоеCoun = 1ДелатьЕсли Coun = 11, то выйти из DoЕсли IsNumeric (Sheets ("sheet1"). Cells (Coun, 1) .Value), тоMyNumber (Coun) = Sheets ("sheet1"). Cells (Coun, 1) .ValueЕщеMyNumber (Coun) = 0Конец, еслиCoun = Coun + 1ПетляКонец подписки |
В коде используется функция IsNumeric, чтобы проверить, действительно ли значение является числом, и если это так, он вводит его в массив. Если это не число, то вводится нулевое значение.
Это гарантирует, что индекс массива будет соответствовать номерам строк ячеек в электронной таблице.
Вы также можете добавить код, который копирует исходное значение ошибки и сведения о местоположении в рабочий лист «Ошибки», чтобы пользователь мог видеть, что он сделал неправильно, при запуске вашего кода.
Числовой тест использует полный код ячейки, а также код для присвоения значения массиву. Вы можете возразить, что это должно быть присвоено переменной, чтобы не повторять один и тот же код, но проблема в том, что вам нужно будет определить переменную как «вариант», что не лучший вариант.
Вам также потребуется проверка данных на листе и защита рабочего листа паролем. Это предотвратит вставку пользователем строк и ввод неожиданных данных.
Ошибка несоответствия, вызванная вызовом функции или подпрограммы с использованием параметров
Когда функция вызывается, вы обычно передаете ей параметры, используя типы данных, уже определенные функцией. Эта функция может быть функцией, уже определенной в VBA, или это может быть функция, определенная пользователем, которую вы создали сами. Подпрограмма также может иногда требовать параметры
Если вы не будете придерживаться соглашений о том, как параметры передаются в функцию, вы получите ошибку несоответствия.
| 12345678 | Дополнительная функция вызова ()Dim Ret As IntegerRet = MyFunction (3, «тест»)Конец подпискиФункция MyFunction (N как целое число, T как строка) как строкаMyFunction = TКонечная функция |
Здесь есть несколько возможностей получить ошибку несоответствия
Возвращаемая переменная (Ret) определяется как целое число, но функция возвращает строку. Как только вы запустите код, он завершится ошибкой, потому что функция возвращает строку, а это не может быть передано в целочисленную переменную. Интересно, что запуск Debug для этого кода не обнаруживает эту ошибку.
Если вы поместите кавычки вокруг первого передаваемого параметра (3), он интерпретируется как строка, которая не соответствует определению первого параметра в функции (целое число)
Если вы сделаете второй параметр в вызове функции числовым значением, он завершится ошибкой с несоответствием, потому что второй параметр в строке определен как строка (текст)
Ошибка несоответствия, вызванная неправильным использованием функций преобразования в VBA
Существует ряд функций преобразования, которые вы можете использовать в VBA для преобразования значений в различные типы данных. Примером является «CInt», который преобразует строку, содержащую число, в целочисленное значение.
Если преобразуемая строка содержит какие-либо буквенные символы, вы получите ошибку несоответствия, даже если первая часть строки содержит числовые символы, а остальные - буквенные символы, например. «123abc»
Общее предотвращение ошибок несоответствия
В приведенных выше примерах мы видели несколько способов борьбы с потенциальными ошибками несоответствия в вашем коде, но есть ряд других способов, хотя они могут быть не лучшими вариантами:
Определите свои переменные как тип варианта
Вариантный тип - это тип переменной по умолчанию в VBA. Если вы не используете оператор Dim для переменной и просто начинаете использовать его в своем коде, то ему автоматически присваивается тип Variant.
Переменная Variant принимает данные любого типа, будь то целое число, длинное целое число, число двойной точности, логическое или текстовое значение. Это звучит как прекрасная идея, и вы задаетесь вопросом, почему все не устанавливают все свои переменные на вариант.
Однако вариантный тип данных имеет несколько недостатков. Во-первых, он занимает гораздо больше памяти, чем другие типы данных. Если вы определите очень большой массив как вариант, он поглотит огромный объем памяти при выполнении кода VBA и может легко вызвать проблемы с производительностью.
Во-вторых, это обычно медленнее по производительности, чем при использовании определенных типов данных. Например, если вы выполняете сложные вычисления с использованием чисел с плавающей десятичной запятой, вычисления будут значительно медленнее, если вы сохраните числа как варианты, а не числа с двойной точностью.
Использование вариантного типа считается небрежным программированием, если в этом нет абсолютной необходимости.
Используйте команду OnError для обработки ошибок
Команду OnError можно включить в ваш код для работы с перехватом ошибок, чтобы в случае возникновения ошибки пользователь видел значимое сообщение вместо стандартного всплывающего окна с ошибкой VBA.
| 1234567 | Sub ErrorTrap ()Dim MyNumber как целое числоПри ошибке GoTo Err_HandlerMyNumber = "тест"Err_Handler:MsgBox «Произошла ошибка» & Описание ошибки & «Произошла»Конец подписки |
Это эффективно предотвращает остановку бесперебойной работы вашего кода из-за ошибки и позволяет пользователю полностью восстановиться из ситуации с ошибкой.
Подпрограмма Err_Handler может показать дополнительную информацию об ошибке и с кем связаться по этому поводу.
С точки зрения программирования, когда вы используете процедуру обработки ошибок, довольно сложно найти строку кода, в которой возникла ошибка. Если вы выполняете код с помощью F8, как только вызывающая нарушение строка кода запускается, она переходит к процедуре обработки ошибок, и вы не можете проверить, где что-то пошло не так.
Один из способов обойти это - установить глобальную константу, имеющую значение True или False (логическое значение), и использовать ее для включения или выключения процедуры обработки ошибок с помощью оператора «If». Если вы хотите проверить ошибку, все, что вам нужно сделать, это установить для глобальной константы значение False, и обработчик ошибок больше не будет работать.
| 1 | Global Const ErrHandling = False |
| 1234567 | Sub ErrorTrap ()Dim MyNumber как целое числоЕсли ErrHandling = True, то при ошибке Перейти к Err_HandlerMyNumber = "тест"Err_Handler:MsgBox «Произошла ошибка» & Описание ошибки & «Произошла»Конец подписки |
Единственная проблема с этим заключается в том, что он позволяет пользователю восстановиться после ошибки, но остальная часть кода в подпрограмме не запускается, что может иметь огромные последствия позже в приложении.
Используя предыдущий пример перебора диапазона ячеек, код попадет в ячейку A5 и обнаружит ошибку несовпадения. Пользователь увидит окно сообщения с информацией об ошибке, но ничего из этой ячейки и далее в диапазоне не будет обработано.
Используйте команду OnError для подавления ошибок
Для этого используется команда «Возобновить при ошибке». Это очень опасно включать в ваш код, поскольку это предотвращает отображение любых последующих ошибок. Это в основном означает, что если ваш код выполняется, если в строке кода возникает ошибка, выполнение просто переместится на следующую доступную строку, не выполняя строку ошибки, и продолжится как обычно.
Это может устранить потенциальную ошибку, но все равно повлияет на каждую будущую ошибку в коде. Тогда вы можете подумать, что ваш код не содержит ошибок, но на самом деле это не так, и части вашего кода не делают то, что вы думаете, что они должны делать.
Есть ситуации, когда необходимо использовать эту команду, например, если вы удаляете файл с помощью команды 'Kill' (если файл отсутствует, будет ошибка), но перехват ошибок всегда должен быть переключен обратно сразу после того, как могла произойти потенциальная ошибка, используя:
| 1 | При ошибке Перейти к 0 |
В предыдущем примере цикла по диапазону ячеек с использованием 'On Error Resume Next' это позволило бы продолжить цикл, но ячейка, вызвавшая ошибку, не будет перенесена в массив, а элемент массива для этого конкретного индекса будет содержать нулевое значение.
Преобразование данных в тип данных, соответствующий декларации
Вы можете использовать функции VBA для изменения типа данных входящих данных, чтобы он соответствовал типу данных принимающей переменной.
Вы можете сделать это при передаче параметров функциям. Например, если у вас есть число, которое хранится в строковой переменной, и вы хотите передать его как число в функцию, вы можете использовать CInt
Существует ряд этих функций преобразования, которые можно использовать, но вот основные из них:
CInt - преобразует строку с числовым значением (ниже + или - 32 768) в целочисленное значение. Имейте в виду, что это обрезает все десятичные точки.
CLng - преобразует строку с большим числовым значением в длинное целое число. Десятичные точки обрезаются.
CDbl - преобразует строку, содержащую число с плавающей десятичной запятой, в число с двойной точностью. Включает десятичные дроби
CDate - преобразует строку, содержащую дату, в переменную даты. Частично зависит от настроек в Панели управления Windows и вашего языкового стандарта от того, как интерпретируется дата.
CStr - преобразует числовое значение или значение даты в строку.
При преобразовании строки в число или дату строка не должна содержать ничего, кроме чисел или даты. Если присутствуют буквенные символы, это приведет к ошибке несоответствия. Вот пример, который приведет к ошибке несоответствия:
| 123 | Подтест ()MsgBox CInt ("123abc")Конец подписки |
Тестирование переменных в вашем коде
Вы можете протестировать переменную, чтобы узнать, что это за тип данных, прежде чем назначать ее переменной определенного типа.
Например, вы можете проверить строку, чтобы узнать, является ли она числовой, используя функцию «IsNumeric» в VBA.
| 1 | MsgBox IsNumeric («123test») |
Этот код вернет False, потому что, хотя строка начинается с числовых символов, она также содержит текст, поэтому тест не пройден.
| 1 | MsgBox IsNumeric ("123") |
Этот код вернет True, потому что это все числовые символы.
В VBA есть ряд функций для тестирования различных типов данных, но это основные из них:
IsNumeric - проверяет, является ли выражение числом
IsDate - проверяет, является ли выражение датой.
IsNull - проверяет, является ли выражение нулевым или нет. Нулевое значение может быть помещено только в вариантный объект, в противном случае вы получите сообщение об ошибке «Недопустимое использование нуля». Окно сообщения возвращает нулевое значение, если вы используете его, чтобы задать вопрос, поэтому возвращаемая переменная должна быть вариантом. Имейте в виду, что любое вычисление с использованием нулевого значения всегда будет возвращать нулевой результат.
IsArray - проверяет, представляет ли выражение массив или нет
IsEmpty - проверяет, является ли выражение пустым или нет. Обратите внимание, что пустой - это не то же самое, что и null. При первом определении переменная пуста, но не является нулевым значением.
Как ни странно, для IsText или IsString нет функции, которая была бы действительно полезной.
Объекты и ошибки несоответствия
Если вы используете такие объекты, как диапазон или лист, вы получите ошибку несоответствия во время компиляции, а не во время выполнения, что даст вам должное предупреждение о том, что ваш код не будет работать.
| 123456 | Sub TestRange ()Dim MyRange As Range, I As LongУстановить MyRange = Range ("A1: A2")I = 10x = UseMyRange (I)Конец подписки |
| 12 | Функция UseMyRange (R как диапазон)Конечная функция |
В этом коде есть функция под названием UseMyRange и параметр, переданный как объект диапазона. Однако передаваемый параметр представляет собой длинное целое число, которое не соответствует типу данных.
Когда вы запускаете код VBA, он немедленно компилируется, и вы увидите это сообщение об ошибке:
Неверный параметр будет выделен синим фоном.
Как правило, если вы делаете ошибки в коде VBA, используя объекты, вы увидите это сообщение об ошибке, а не сообщение о несоответствии типа:
