Ошибка при вставке проверки в VBA - Советы по Excel

Если вы читали первые несколько глав VBA и макросов для Microsoft Excel, вы знаете, что я жалуюсь на то, что средство записи макросов Excel не справляется с задачей записи пригодного для использования кода. Обычно код работает нормально, но он записан таким образом, что может оказаться не очень полезным для наборов данных разного размера. Код может работать нормально сегодня, но не завтра.

Я столкнулся с действительно странной проблемой, когда макрос записывал код, который не работал. Я писал макрос, который пытался скопировать проверку из одной ячейки в диапазон ячеек. В Excel 2002 этот код был следующим:

Range(“E5”).Copy Range(“E6:E12”).PasteSpecial Paste:=xlPasteValidation

Этот код работал нормально в Excel 2002, но не работал на клиентском компьютере с Excel 2000. На одном из старых компьютеров в офисе все еще есть Excel 2000, поэтому я попробовал код там. Проблема была в xlPasteValidation. Каждый раз, когда я сталкиваюсь с чем-то необычным, я запускаю средство записи макросов, чтобы посмотреть, как средство записи макросов запишет код. Я настроил проверку в E5, включил регистратор макросов, скопировал E5 и использовал Специальную вставку - Проверка. После остановки средства записи макросов я заметил, что Excel 2000 записал константу как:

Range(“E6:E12”).PasteSpecial Paste:=xlDataValidation

Итак, я зашел в клиентское приложение, изменил код на xlDataValidation и снова запустил его. Как ни странно, возникла такая же ошибка!

Оказывается, в программе записи макросов Excel 2000 действительно есть ошибка. Он запишет константу xlDataValidation, но интерпретатор макросов не распознает ни xlDataValidation, ни xlPasteValidation. Справка Excel VBA в Excel 2000 делает вид, будто нет возможности вставить только проверки.

Чтобы это сработало, вам необходимо определить базовое значение xlPasteValidation. На моей машине XL2002 я зашел в редактор VBA. Наберите Ctrl + G, чтобы открыть окно сразу, и введите это в панели сразу:

Print xlPasteValidation

Нажмите Enter, и Excel 2002 сообщит вам, что xlPasteValidation - это удобный способ сказать «6». Вернувшись на компьютер Excel 2000, я попробовал этот код:

Range(“E6:E12”).PasteSpecial Paste:=6

К счастью, это работает. Фактически вы вынуждены использовать базовое значение вместо константы. Я предостерегаю от этой практики, описанной в книге, поскольку она делает программу действительно трудной для чтения для следующего человека, смотрящего на код. В данном конкретном случае у вас действительно нет выбора. Добавьте комментарий, объясняющий, почему вы это закодировали так:

‘Bug in XL2000; need to use “6” instead of the constant Range(“E6:E12”).PasteSpecial Paste:=6

Итак - небольшой урок сегодня - это очень конкретная проблема, как вставить специальную проверку в Excel 2000, но больший урок - это детективная работа, необходимая для выяснения того, что происходит, когда что-то странное происходит в Excel VBA.