Проверка корректности введенных данных в Excel может оказаться полезным инструментом, но также может создать серьезные проблемы. При вставке данных в ячейку, где реализуется проверка, не только не происходит проверка вставляемого значения, но и правила проверки, связанные с этой ячейкой, стираются. В результате инструмент проверки данных теряет свою эффективность в собственных приложениях.
Эта статья представляет первый метод использования события Change объекта Worksheet для создания процедур проверки корректности введенных данных. В этом методе используется свойство EnableEvents (статья № 79) для предотвращения бесконечного цикла событий Change.
(второй метод будет рассмотрен в следующей статье).
Процедура Worksheet_Change, представленная в коде ниже, срабатывает, когда пользователь изменяет ячейку. Проверка ограничивается диапазоном "МойДиапазон". В этом диапазоне допускается ввод только целых чисел от 1 до 10. В моем случае именованный диапазон ("B2: D10").
Ну а теперь давайте напишем процедуру…
и разберем пошагово:
1. Private Sub Worksheet_Change(ByVal Target As Excel.Range):
- Это объявление процедуры события Change для листа Excel. Она срабатывает каждый раз, когда пользователь вносит изменения в ячейки на листе.
2. Dim Объект As Range, cell As Range:
- Объявление переменных Объект и cell типа Range. Объект будет использоваться для представления диапазона, который нужно проверять, а cell - для циклического перебора ячеек в измененном диапазоне.
3. Dim Msg As String:
- Объявление переменной Msg типа String, которая будет использоваться для хранения текста сообщения об ошибке.
4. Dim КодПроверки As Variant:
- Объявление переменной КодПроверки типа Variant. В этой переменной будет храниться результат проверки, возвращаемый функцией ПроверкаВвода.
5. Set Объект = Range("МойДиапазон"):
- Задание значения переменной "Объект" – это диапазон ячеек, который будет проверяться на корректность. В данном случае, это диапазон с именем "МойДиапазон".
6. If Intersect(Объект, Target) Is Nothing Then Exit Sub:
- Проверка, произошли ли изменения в ячейках, которые находятся в диапазоне "Объект". Если изменений нет, процедура завершается.
7. For Each cell In Intersect(Объект, Target):
- Цикл для перебора каждой ячейки, которая находится в пересечении диапазона "Объект" и измененного диапазона Target.
8. КодПроверки = ПроверкаВвода(cell):
- Вызов пользовательской функции ПроверкаВвода, которая осуществляет проверку значения ячейки cell и возвращает результат в переменную КодПроверки.
9. If TypeName(КодПроверки) = "String" Then:
- Проверка типа результата проверки. Если это строка, то есть ошибка, выполняются следующие действия:
10. Msg = "Ячейка " & cell.Address(False, False) & ":":
- Формирование сообщения об ошибке, включающего адрес некорректной ячейки.
11. MsgBox Msg, vbCritical, "Некорректное значение":
- Вывод окна сообщения с текстом ошибки, поддерживающего критическую значок.
12. Application.EnableEvents = False:
- Отключение обработки событий, чтобы избежать бесконечного цикла.
13. cell.ClearContents:
- Очистка содержимого некорректной ячейки.
14. cell.Activate:
- Активация некорректной ячейки.
15. Application.EnableEvents = True:
- Включение обработки событий.
16. Next cell:
- Завершение цикла и переход к следующей измененной ячейке, если таковые имеются.
Процедура Worksheet_Change создает объект Range, который называется «Объект» и представляет проверяемый диапазон на рабочем листе. Затем циклически просматриваются все ячейки аргумента Target, представляющего диапазон измененных ячеек. В коде определяется, находится ли изменившаяся ячейка в проверяемом диапазоне, и, если это так, ячейка передается в качестве аргумента пользовательской функции (ПроверкаВвода), возвращающей значение True для действительного значения ячейки.
Если значение ячейки выходит за пределы допустимого диапазона, функция "ПроверкаВвода" возвращает строку, описывающую проблему. Затем выводится окно сообщения. После закрытия окна сообщения некорректное значение ячейки удаляется, и активизируется сама ячейка. Обратите внимание, что перед удалением значения ячейки отключаются события, чтобы избежать бесконечного цикла.
Так давайте в этом же модуле листа, напишем эту функцию…
и разберем нашу функцию шаг за шагом:
1. Private Function ПроверкаВвода(cell) As Variant:
Объявление пользовательской функции ПроверкаВвода. Эта функция возвращает результат проверки в виде значения типа Variant.
2. Комментарии: Комментарии предоставляют пояснения к коду:
- - Возвращает True, если ячейка содержит целое число между 1 и 10: Общее описание того, что делает функция.
- - Иначе возвращается строка с описанием проблемы: Указание на то, что в случае ошибки функция вернет строку с описанием проблемы.
3. If Not WorksheetFunction.IsNumber(cell) Then:
Проверка, является ли значение в ячейке числовым. WorksheetFunction.IsNumber возвращает True, если значение является числом.
- - Если значение не является числовым, выполняется следующее:
- - ПроверкаВвода = "Нечисловое значение.": Установка результата функции в строку "Нечисловое значение."
- - Exit Function: Выход из функции.
4. If CInt(cell) <> cell Then:
Проверка, является ли значение в ячейке целым числом. CInt преобразует значение в целое число.
- - Если значение не является целым числом, выполняется следующее:
- - ПроверкаВвода = "Требуется целое число.": Установка результата функции в строку "Требуется целое число."
- - Exit Function: Выход из функции.
5. If cell < 1 Or cell > 10 Then:
Проверка, находится ли значение в ячейке в диапазоне от 1 до 10.
- - Если значение не находится в этом диапазоне, выполняется следующее:
- - ПроверкаВвода = "Значение должно находиться в диапазоне между 1 и 10.": Установка результата функции в строку "Значение должно находиться в диапазоне между 1 и 10."
- - Exit Function: Выход из функции.
6. ПроверкаВвода = True:
Если все проверки пройдены успешно, устанавливается результат функции в значение True.
В итоге, эта функция возвращает True, если значение в ячейке является целым числом от 1 до 10, или строку с описанием проблемы в противном случае.