Проверка данных с помощью vba

Обычно можно применить проверку данных в excel через vba, но у меня возникла конкретная проблема.

Я использую косвенный метод для применения проверки, обычно, когда я не использую vba, я получаю предупреждение от excel о том, что «источник в настоящее время оценивается как ошибка, вы хотите продолжить?» (это потому, что ячейки, на которые косвенно ссылаются, могут быть пустыми), теперь я могу легко пропустить эту ошибку в Excel, нажав «Да»

Вот ссылка http://www.contextures.com/xldataval02.html (списки зависимой проверки)

Но когда я пытаюсь выполнить то же самое с помощью vba, я получаю ошибку времени выполнения, нет дружественной подсказки, которая позволяет мне продолжить. Как я могу справиться с такой ошибкой.

При ошибке возобновление не работает, потому что vba вообще не применяет проверку.

Вот код

Set rng = ThisWorkbook.Sheets("input").Range("AB11:AB65536")
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

cablecode — это статический именованный диапазон

Q11 и Z11 относятся к входам в этой конкретной строке

deep и values являются обычными строками.

cablecode может иметь два значения = "is" или "iec"

Q11 может быть "xlpe" или "pvc"

Z11 может быть "al" или "cu"

так как кабельный код является постоянным для всего проекта, я ссылался на него напрямую, Q11 и Z11 могут быть разными для разных строк, поэтому я ссылался на них отдельно.

Вся строка выглядит как "is_depth_al_xlpe_values" с аналогичными перестановками, и все именованные диапазоны уже определены.


excel vba excel-2003
person Kartik Anand    schedule 09.05.2012    source источник
comment
Это будет более понятно, если вы любезно опубликуете исходный код.   -  person Edwin Bautista    schedule 09.05.2012
comment
@EdwinBautista добавил исходный код   -  person Kartik Anand    schedule 09.05.2012
comment
Что за ошибка и в какой строке ошибка?   -  person shahkalpeshp    schedule 09.05.2012
comment
Ошибка возникает, когда vba пытается добавить проверку. Я получаю ошибку времени выполнения «1004», определенную приложением или ошибкой, определяемую объектом. Это связано с тем, что проверка в настоящее время оценивается как ошибка, и vba не позволяет мне продолжать. .. тогда как Excel делает ..   -  person Kartik Anand    schedule 09.05.2012

Ответы (2)


arrow_upward
1
arrow_downward

Я предполагаю, что следующая строка нуждается в исправлении от

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"

to

dim cellToBeReferred as string
cellToBeReferred = cablecode & "_depth_" & Range("$Q11").Value & "_" _
& Range("$Q11").Value & "_values"

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(" & cellToBeReferred  & ")"

Извлечение строки поможет вам отладить проблему и выяснить, какое значение именованного диапазона вы хотите использовать. Посмотрите, к какому результату приводит значение cellToBeReferred, и исправьте конкатенацию, используя приведенное выше.

ИЗМЕНИТЬ после прочтения комментария операционной системы

В этом случае окружите INDIRECT ISERROR и дайте фиктивную ссылку на ячейку. Например,

"=IFERROR(INDIRECT(" & cellToBeReferred  & "), Z1)"

Где Z1 — это ячейка, на которую следует ссылаться в случае сбоя INDIRECT.

person shahkalpeshp    schedule 09.05.2012
comment
я знаю, в чем ошибка.. просто когда Q11 и Z11 не указаны, строка станет is_depth_ (ничего здесь) _ (ничего здесь) _values ​​​​... поскольку это не именованный диапазон, он дает ошибку .. проблема Excel позволит мне продолжить, потому что позже я могу ввести Q11 и Z11, и проверка будет доступна ... но vba не позволит мне продолжить ... - person Kartik Anand; 09.05.2012
comment
но в случае фиктивной ссылки на ячейку исходной проверки не будет.. и если пользователь вводит данные в Z11 и Q11, он не получит список, который он должен был - person Kartik Anand; 09.05.2012
comment
Если именованный диапазон правильный и имеет данные, он будет указан. В случае ошибки будет использоваться Z1 (временная ссылка). - person shahkalpeshp; 09.05.2012
comment
он все еще не работает ... он не улавливает это конкретное предупреждение, которое показывает excel, если косвенное в настоящее время указывает на ошибку ... (и да, я использовал ISERROR) - person Kartik Anand; 09.05.2012
comment
Ну, я понял это после того, как получил вашу подсказку как ISERROR, дело в том, что он по-прежнему не поймает оператор ISERROR, потому что именованные диапазоны, состоящие из более чем одной ячейки, возвращают ошибку #value, если вы используете их в ячейке. Вместо этого я использовал ISREF Вот лучший формат =ЕСЛИ(ISREF(ДВССЫЛ(кабельный код&temp&$Q11&&$Z11&_values)), НЕПРЯМОЙ (кабельный код&_temp&$Q11&_&$Z11&_values),нет данных) - person Kartik Anand; 09.05.2012

arrow_upward
0
arrow_downward

Невозможно подавить предупреждение в VBA. Временно присвойте значение опорной ячейке и очистите его позже. Это предотвратит ошибку времени выполнения, которую избегает пользовательский интерфейс при выборе Да для Источник в настоящее время оценивается как ошибка. Вы хотите продолжить диалоговое окно.

ex.

thisworkbook.range("Q11").value = "1"

'data validation - i.e. Formula1:="=INDIRECT(Q11)"

thisworkbook.range("Q11").value = ""
person J B    schedule 12.11.2020