Що таке помилка невідповідності типів?
Помилка невідповідності може часто виникати під час запуску коду VBA. Помилка зупинить повний запуск вашого коду та позначить його за допомогою вікна повідомлення, яке потрібно усунути
Зауважте, що якщо ви не повністю перевірили свій код перед розповсюдженням серед користувачів, це повідомлення про помилку буде видно користувачам і спричинить велику втрату довіри до вашого додатка Excel. На жаль, користувачі часто роблять дуже особливі речі для програми, і це часто речі, які ви як розробник ніколи не розглядали.
Помилка невідповідності типів виникає через те, що ви визначили змінну за допомогою оператора Dim як певного типу, наприклад ціле число, дата, і ваш код намагається присвоїти змінній значення, яке є неприйнятним, наприклад текстовий рядок, призначений цілочисельній змінній, як у цьому прикладі:
Ось приклад:
Натисніть «Налагодження», і код рядка, що порушує правила, виділиться жовтим кольором. У спливаючому вікні про помилку немає можливості продовжити, оскільки це серйозна помилка, і код не може працювати далі.
У цьому конкретному випадку рішення полягає в тому, щоб змінити оператор Dim на тип змінної, яка працює зі значенням, яке ви призначаєте змінній. Код буде працювати, якщо ви зміните тип змінної на "Рядок", і вам, ймовірно, також захочеться змінити назву змінної.
Однак зміна типу змінної потребує скидання проекту, і вам доведеться знову запускати код з самого початку, що може бути дуже дратівливим, якщо йдеться про довгу процедуру
Помилка невідповідності, викликана обчисленням аркуша
Наведений вище приклад - це дуже простий спосіб того, як можна створити помилку невідповідності, і в цьому випадку її легко усунути
Однак причина помилок невідповідності зазвичай набагато глибша і не така очевидна, коли ви намагаєтесь налагодити свій код.
Як приклад, припустимо, що ви написали код, щоб забрати значення у певній позиції на аркуші, і він містить інші клітинки, що залежать від обчислень у книзі (В1 у цьому прикладі)
Робочий лист виглядає так, як у цьому прикладі, з формулою пошуку певного символу в рядку тексту
З точки зору користувача, клітинка А1 є вільним форматом, і вони можуть вводити будь -яке значення, яке вони хочуть. Однак формула шукає входження символу "В", і в цьому випадку вона не знайдена, тому клітинка В1 має значення помилки.
Нижче наведений тестовий код призведе до помилки невідповідності, оскільки в комірку А1 було введено неправильне значення
| 1234 | Sub TestMismatch ()Dim MyNumber As IntegerMyNumber = Аркуші ("Аркуш1"). Діапазон ("В1"). ЗначенняEnd Sub |
Значення у комірці В1 спричинило помилку, оскільки користувач ввів у клітинку А1 текст, який не відповідає очікуваному та не містить символу «В»
Код намагається присвоїти значення змінній "MyNumber", яка була визначена для очікування цілого числа, і таким чином ви отримаєте помилку невідповідності.
Це один із таких прикладів, коли ретельна перевірка коду не дасть відповіді. Вам також потрібно подивитися на аркуші, звідки надходить значення, щоб з'ясувати, чому це відбувається.
Проблема насправді на робочому аркуші, і формулу в B1 потрібно змінити, щоб можна було вирішувати значення помилок. Ви можете зробити це за допомогою формули "IFERROR", щоб надати значення за замовчуванням 0, якщо символ пошуку не знайдено
Потім ви можете включити код для перевірки нульового значення та відображення попереджувального повідомлення для користувача про те, що значення у комірці A1 недійсне
| 12345678 | Sub TestMismatch ()Dim MyNumber As IntegerMyNumber = Аркуші ("Аркуш1"). Діапазон ("В1"). ТекстЯкщо MyNumber = 0 ТодіMsgBox "Значення в комірці A1 недійсне", vbCriticalВийти з підпЗакінчити ЯкщоEnd Sub |
Ви також можете скористатися валідацією даних (група Інструменти даних на вкладці Дані стрічки) в електронній таблиці, щоб зупинити користувача робити все, що йому подобається, і викликати помилки на робочому аркуші. Дозвольте їм вводити значення, які не спричинять помилок на аркуші.
Ви можете написати код VBA на основі події зміни на аркуші, щоб перевірити, що було введено.
Також блокування та пароль захищають аркуш, щоб неможливо було ввести недійсні дані
Помилка невідповідності, спричинена введеними значеннями комірки
Помилки невідповідності можуть бути викликані у вашому коді шляхом введення нормальних значень з аркуша (не помилка), але коли користувач ввів несподіване значення, наприклад текстове значення, коли ви очікували числа. Можливо, вони вирішили вставити рядок у діапазон чисел, щоб вони могли помістити примітку в клітинку, де щось пояснюється. Зрештою, користувач не має уявлення про те, як працює ваш код, і про те, що він щойно викинув усе з фільтру, ввівши свою замітку.
У наведеному нижче прикладі код створює простий масив під назвою "MyNumber", визначений цілими значеннями
Потім код перебирає діапазон клітинок від A1 до A7, призначаючи значення клітинок у масив, використовуючи змінну "Coun" для індексування кожного значення
Коли код досягає текстового значення, це викликає помилку невідповідності, і все зупиняється
Натиснувши «Налагодження» у вікні помилки, ви побачите рядок коду, у якому проблема виділена жовтим кольором. Навевши курсор на будь -який екземпляр змінної "Coun" у коді, ви зможете побачити значення "Coun", де код зазнав невдачі, що в цьому випадку дорівнює 5
Переглянувши аркуш, ви побачите, що 5го клітинка вниз має текстове значення, і це спричинило помилку коду
Ви можете змінити свій код, поставивши умову, яка спочатку перевіряє числове значення перед додаванням значення комірки до масиву
| 12345678910111213 | Sub TestMismatch ()Dim MyNumber (10) As Integer, Coun As IntegerCoun = 1ЗробитиЯкщо Coun = 11, то вийдітьЯкщо IsNumeric (Аркуші ("аркуш1"). Клітинки (Coun, 1). Значення) ТодіMyNumber (Coun) = Sheets ("sheet1"). Cells (Coun, 1) .ValueІнакшеМій номер (лічильник) = 0Закінчити ЯкщоCoun = Coun + 1ПетляEnd Sub |
Код використовує функцію "IsNumeric", щоб перевірити, чи є значення насправді числом, а якщо це так, то він вводить його в масив. Якщо це не число, то воно вводить значення нуля.
Це гарантує, що індекс масиву зберігається у відповідності з номерами рядків комірок у електронній таблиці.
Ви також можете додати код, який копіює вихідне значення помилки та деталі розташування на робочому аркуші "Помилки", щоб користувач бачив, що він зробив неправильно під час запуску вашого коду.
Числовий тест використовує повний код комірки, а також код для призначення значення в масиві. Ви можете стверджувати, що це має бути призначено змінній, щоб не повторювати один і той же код, але проблема в тому, що вам потрібно буде визначити змінну як "Варіант", що не найкраще робити.
Вам також потрібна перевірка даних на аркуші та захист аркуша паролем. Це не дозволить користувачеві вставляти рядки та вводити несподівані дані.
Помилка невідповідності, викликана викликом функції або підпрограми за допомогою параметрів
Під час виклику функції зазвичай передаються параметри функції за допомогою типів даних, які вже визначені функцією. Ця функція може бути тією, що вже визначена у VBA, або це функція, визначена користувачем, яку ви створили самостійно. Підпрограма може також іноді вимагати параметрів
Якщо ви не будете дотримуватися умов передачі параметрів функції, ви отримаєте помилку невідповідності
| 12345678 | Sub CallFunction ()Dim Ret як ціле числоRet = MyFunction (3, "test")End SubФункція MyFunction (N як ціле число, T як рядок) як рядокMyFunction = Т.Функція завершення |
Тут є кілька можливостей отримати помилку невідповідності
Повертаюча змінна (Ret) визначається як ціле число, але функція повертає рядок. Щойно ви запустите код, він вийде з ладу, оскільки функція повертає рядок, і це не може перейти до цілої змінної. Цікаво, що запуск Debug у цьому коді не сприймає цю помилку.
Якщо ви поставите лапки навколо першого переданого параметра (3), він інтерпретується як рядок, який не відповідає визначенню першого параметра у функції (ціле число)
Якщо ви зробите другий параметр у виклику функції числовим значенням, він зазнає невдачі з невідповідністю, оскільки другий параметр у рядку визначається як рядок (текст)
Помилка невідповідності, викликана неправильним використанням функцій перетворення у VBA
Існує ряд функцій перетворення, які можна використовувати у VBA для перетворення значень у різні типи даних. Наприклад, "CInt", який перетворює рядок, що містить число, у ціле число.
Якщо рядок, що підлягає конвертації, містить будь -які символи альфа -адреси, ви отримаєте помилку невідповідності, навіть якщо перша частина рядка містить числові символи, а решта - це букви алфавіту, напр. "123abc"
Загальна профілактика помилок невідповідності
У наведених вище прикладах ми бачили кілька способів боротьби з потенційними помилками невідповідності у вашому коді, але є ряд інших способів, хоча вони можуть бути не найкращими варіантами:
Визначте свої змінні як тип варіанту
Тип варіанта - це тип змінної за умовчанням у VBA. Якщо ви не використовуєте оператор Dim для змінної і просто починаєте використовувати його у своєму коді, то йому автоматично надається тип Variant.
Змінна Variant приймає будь -який тип даних, будь то ціле число, довге ціле число, число подвійної точності, логічне або текстове значення. Це звучить як чудова ідея, і вам цікаво, чому кожен не просто встановлює всі свої змінні на варіант.
Однак варіант типу даних має кілька недоліків. По -перше, він займає набагато більше пам'яті, ніж інші типи даних. Якщо ви визначите дуже великий масив як варіант, він зайняє величезну кількість пам’яті під час роботи коду VBA, і це може легко спричинити проблеми з продуктивністю
По -друге, продуктивність загалом повільніша, ніж при використанні певних типів даних. Наприклад, якщо ви робите складні обчислення з використанням чисел із плаваючою десятковою комою, розрахунки будуть значно повільнішими, якщо ви зберігаєте числа як варіанти, а не числа подвійної точності
Використання варіантного типу вважається недбалим програмуванням, якщо в цьому немає абсолютної необхідності.
Для виправлення помилок використовуйте команду OnError
Команда OnError може бути включена до вашого коду для вирішення проблеми з перехопленням помилок, так що якщо помилка все-таки трапиться, користувач побачить значуще повідомлення замість стандартного спливаючого вікна VBA
| 1234567 | Sub ErrorTrap ()Dim MyNumber As IntegerУвімкнено помилку Перейти до Err_HandlerMyNumber = "тест"Err_Handler:MsgBox "Помилка" & Err.Опис & "сталася"End Sub |
Це ефективно запобігає помилці, що зупиняє безперебійну роботу вашого коду, і дозволяє користувачеві повністю відновити ситуацію з помилкою.
Процедура Err_Handler може показати додаткову інформацію про помилку та до кого з нею звернутися.
З точки зору програмування, коли ви використовуєте процедуру обробки помилок, досить важко знайти рядок коду, у якому є помилка. Якщо ви переходите через код за допомогою F8, як тільки запускається порушуючий рядок коду, він переходить до процедури обробки помилок, і ви не можете перевірити, де це відбувається не так.
Шляхом цього можна налаштувати глобальну константу, яка є True або False (Boolean), і використати це, щоб увімкнути або вимкнути процедуру обробки помилок за допомогою оператора "If". Якщо ви хочете перевірити помилку, вам потрібно лише встановити глобальну константу на False, і обробник помилок більше не працюватиме.
| 1 | Global Const ErrHandling = Неправда |
| 1234567 | Sub ErrorTrap ()Dim MyNumber As IntegerЯкщо ErrHandling = Правда, тоді Помилка Перейти до Err_HandlerMyNumber = "тест"Err_Handler:MsgBox "Помилка" & Err.Опис & "сталася"End Sub |
Єдина проблема з цим полягає в тому, що він дозволяє користувачеві відновитись після помилки, але решта коду в підпрограмі не запускається, що може мати величезні наслідки пізніше в додатку
Використовуючи попередній приклад циклу через діапазон комірок, код потрапляв би до комірки А5 і потрапляв у невідповідну помилку. Користувач побачить вікно повідомлення з інформацією про помилку, але нічого з цієї комірки і далі в діапазоні не буде оброблено.
Використовуйте команду OnError для усунення помилок
Для цього використовується команда "Увімкнути помилку, продовжити далі". Це дуже небезпечно включати у ваш код, оскільки запобігає появі будь -яких наступних помилок. Це в основному означає, що під час виконання вашого коду, якщо в рядку коду виникає помилка, виконання просто переходить до наступного доступного рядка, не виконуючи рядка помилки, і продовжує роботу у звичайному режимі.
Це може розібратися з потенційною ситуацією помилки, але це все одно вплине на кожну майбутню помилку в коді. Тоді ви можете подумати, що ваш код не містить помилок, але насправді це не так, і частини вашого коду не роблять того, що ви вважаєте за потрібне.
Існують ситуації, коли необхідно використовувати цю команду, наприклад, якщо ви видаляєте файл за допомогою команди «Вбити» (якщо файлу немає, буде помилка), але перехоплення помилок завжди слід перемикати увімкнено одразу після того, як потенційна помилка може статися, використовуючи:
| 1 | У разі помилки Перейдіть до 0 |
У попередньому прикладі циклу по діапазону клітинок з використанням 'On Error Resume Next' це дозволило б продовжити цикл, але клітинка, що викликає помилку, не буде передана в масив, а елемент масиву для цього конкретного індексу буде мати нульове значення.
Перетворення даних у тип даних для відповідності декларації
Ви можете використовувати функції VBA, щоб змінити тип даних вхідних даних так, щоб він відповідав типу даних одержуваної змінної.
Це можна зробити при передачі параметрів функціям. Наприклад, якщо у вас є число, яке міститься у рядковій змінній, і ви хочете передати його як номер функції, ви можете використовувати CInt
Існує ряд цих функцій перетворення, які можна використовувати, але ось основні з них:
CInt - перетворює рядок, що має числове значення (нижче + або - 32,768) у ціле число. Майте на увазі, що це скорочує будь -які десяткові коми
CLng - Перетворює рядок з великим числовим значенням у довге ціле число. Десяткові точки обрізаються.
CDbl - Перетворює рядок, що містить число з плаваючою десятковою комою, у число з подвійною точністю. Включає десяткові крапки
CDate - Перетворює рядок, що містить дату, у змінну дати. Частково залежить від налаштувань на Панелі керування Windows та вашого регіону від того, як інтерпретується дата
CStr - Перетворює числове значення або значення дати в рядок
При перетворенні з рядка на число або дату рядок не повинен містити нічого іншого, крім цифр або дати. Якщо присутні альфа -символи, це призведе до помилки невідповідності. Ось приклад, який призведе до помилки невідповідності:
| 123 | Підтест ()MsgBox CInt ("123abc")End Sub |
Тестування змінних у вашому коді
Ви можете перевірити змінну, щоб дізнатися, який це тип даних, перш ніж призначити її змінній певного типу.
Наприклад, ви можете перевірити рядок, щоб побачити, чи є він числовим, за допомогою функції "IsNumeric" у VBA
| 1 | MsgBox IsNumeric ("123test") |
Цей код поверне False, оскільки, хоча рядок починається з цифрових символів, він також містить текст, тому він не проходить перевірку.
| 1 | MsgBox IsNumeric ("123") |
Цей код поверне Істину, оскільки всі це числові символи
У VBA існує ряд функцій для перевірки різних типів даних, але це основні з них:
IsNumeric - перевіряє, чи є вираз числом чи ні
IsDate - перевіряє, чи є вираз датою чи ні
IsNull - перевіряє, чи є вираз нульовим чи ні. Нульове значення можна вставити лише в об’єкт варіанта, інакше ви отримаєте повідомлення про помилку «Недійсне використання Null». Вікно повідомлення повертає нульове значення, якщо ви використовуєте його, щоб поставити запитання, тому повертається змінна має бути варіантом. Майте на увазі, що будь -яке обчислення з використанням нульового значення завжди повертатиме результат null.
IsArray - перевіряє, чи вираз представляє масив чи ні
IsEmpty - перевіряє, чи вираз порожній чи ні. Зауважте, що порожнє - це не те саме, що null. Змінна порожня, коли вона вперше визначена, але вона не є нульовим значенням
Як не дивно, але немає функції для IsText або IsString, яка була б дійсно корисною
Помилки об’єктів та невідповідності
Якщо ви використовуєте такі об'єкти, як діапазон або аркуш, ви отримаєте помилку невідповідності під час компіляції, а не під час виконання, що дає вам належне попередження про те, що ваш код не працюватиме
| 123456 | Sub TestRange ()Dim MyRange As Range, I As LongВстановити MyRange = Діапазон ("A1: A2")I = 10x = UseMyRange (I)End Sub |
| 12 | Функція UseMyRange (R як діапазон)Функція завершення |
Цей код має функцію під назвою "UseMyRange" і параметр, переданий як об'єкт діапазону. Однак параметр, який передається, - це довге ціле число, яке не відповідає типу даних.
Коли ви запускаєте код VBA, він негайно компілюється, і ви побачите це повідомлення про помилку:
Злочинний параметр буде виділено синім фоном
Як правило, якщо ви робите помилки у коді VBA за допомогою об’єктів, ви побачите це повідомлення про помилку, а не повідомлення про невідповідність типу:
