Excel. Создание выпадающего списка (Validation) со смещением

@Serg34 · Регистрация: 20.09.2014

Студворк — интернет-сервис помощи студентам

Доброго времени суток.
Пытаюсь в Excel сделать программно выпадающий список со смещением, так, что при выборе значений в главном столбце (A) менялся выпадающий список в столбце B.
Если делать вручную это выглядит так:

Excel. Создание выпадающего списка (Validation) со смещением

где Items - именованный диапазон. В моём случае столбец E
Результат:

Если же делать программно, то выдаёт ошибку "Исключение из HRESULT: 0x800A03EC"
Вот код:

C#
1
2
3
4
5
6
7
8
9
var column = sheet.Range[sheet.Cells[2, 2], sheet.Cells[10, 2]];
var formula = "=OFFSET(Items;;MATCH(A2;Items;0);;)";
column.Validation.Delete();
column.Validation.Add(Type: Excel.XlDVType.xlValidateList,
    AlertStyle: Excel.XlDVAlertStyle.xlValidAlertInformation,
    Operator: Excel.XlFormatConditionOperator.xlBetween,
    Formula1: formula);
column.Validation.IgnoreBlank = true;
column.Validation.InCellDropdown = true;

Пробовал в formula точку с запятой менять на запятую, с ковычками, без ковычек и прочее - ничего не помогает.
Кто-нибудь сталкивался с таким?

@nicolas2008 · 09.07.2021, 00:52

Этот код компилируется?
Validation.Add содержит больше параметров, не хватает 'Formula2'
Для неспользуемых параметров надо указывать Missing.Value

@Serg34 · 09.07.2021, 13:01 **[ТС]**

Сообщение от nicolas2008

Этот код компилируется?

Да, компилируется. Исключения же во время выполнения возникают.

Сообщение от nicolas2008

Для неспользуемых параметров надо указывать Missing.Value

Это необязательно.
Если в Formula1 подставить просто диапазон ("=E:E") или список ("Раз;Два;Три"), то и компилируется и работает.

Не в этом дело

Даже сам Excel после записи макроса не хочет собственный код воспроизводить (он, правда, по русски функции пишет, но даже если поменять на английские, то всё-равно ошибка). Тут явно что-то под капотом Экселя недоработано.
Такое ощущение, что любая нетривиальная формула вызывает ошибку во время выполнения.

Ещё заметил, что даже формулы типа "Раз;Два;Три;=E:E" работают.
А вот "=E:E;=F:F" вызывает ошибку.
И любые смещения, индексы и прочие формулы тоже Excel не может прожевать.

Про Formula2 тоже думал, но справки очень мало, даже примера не нашёл. Методом тыка тоже ничего не получается

Добавлено через 1 час 38 минут
Про Formula2 нашёл здесь, но это тоже не помогло

Добавлено через 2 часа 26 минут
Заключив формулу в скобки научил Excel понимать макрос:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Макрос1()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
            Formula1:="=(OFFSET(Items,,MATCH($A2,Items,0),,))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Но C# не поддаётся пока.
Причём формула написана корректно. Код ниже отрабатывает нормально:

C#
1
((Excel.Range)sheet.Cells[4, 3]).Value2 = $"=(OFFSET(Items,,MATCH($A2,Items,0),,))";

Видимо, в Validation проверка параметра Formula1 хитрее идёт

@nicolas2008 · 10.07.2021, 20:57

Можно сделать хитрее. Создать документ с нужной валидацией, открыть его в c# и посмотреть значение в поле Formula1

@Serg34 · 10.07.2021, 22:33 **[ТС]**

nicolas2008, Ну офигеть.
Наконец, happy end.
Оказывается для валидации нужна формула по русски и с точкой с запятой:

C#
1
Formula1 = "=(СМЕЩ(Items;;ПОИСКПОЗ(A2;Items;0);;))"

Спасибо огромное

Я бы сам, наверно, не догадался

@nicolas2008 · 11.07.2021, 00:54

Serg34, да уж

Похоже что эксель понимает только формулы на языке который выбран в региональных настройках Windows.
В таком случае ваша программа будет работать ровно до тех пор пока выбран русский язык.
Есть гипотеза как это сделать надежнее, чтобы работало вне зависимости от выбраного языка.
В программе в самом начале установить региональные настройки на en-US.
По идеи после этого манипуляции с экселем должны происходить в контексте этого языка, вне зависимости от настроек виндовс и можно (и нужно) будет указывать формулы на английском.

C#
1
2
CultureInfo.DefaultThreadCurrentCulture = new CultureInfo("en-US");
CultureInfo.DefaultThreadCurrentUICulture = new CultureInfo("en-US");

@Serg34 · 11.07.2021, 13:03 **[ТС]**

nicolas2008, ах если бы
Видимо, Excel не под управлением моей программы работает, а под управлением Windows и культура моей программы ему не особо интересна.
По крайней мере у меня не получилось

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Работа со звуком через SDL3_mixer 8Observer8 08.02.2026 Содержание блога Пошагово создадим проект для загрузки звукового файла и воспроизведения звука с помощью библиотеки SDL3_mixer. Звук будет воспроизводиться по клику мышки по холсту на Desktop и по. . .	SDL3 для Web (WebAssembly): Основы отладки веб-приложений на SDL3 по USB и Wi-Fi, запущенных в браузере мобильных устройств 8Observer8 07.02.2026 Содержание блога Браузер Chrome имеет средства для отладки мобильных веб-приложений по USB. В этой пошаговой инструкции ограничимся работой с консолью. Вывод в консоль - это часть процесса. . .	SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .
SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .

@nicolas2008 1152 / 860 / 263 Регистрация: 30.04.2009 Сообщений: 3,603
	09.07.2021, 00:52
	Этот код компилируется? Validation.Add содержит больше параметров, не хватает 'Formula2' Для неспользуемых параметров надо указывать Missing.Value 0

@nicolas2008 1152 / 860 / 263 Регистрация: 30.04.2009 Сообщений: 3,603
	10.07.2021, 20:57
	Сообщение было отмечено Serg34 как решение Решение Можно сделать хитрее. Создать документ с нужной валидацией, открыть его в c# и посмотреть значение в поле Formula1 0

@Serg34 100 / 100 / 33 Регистрация: 20.09.2014 Сообщений: 457 Записей в блоге: 3
	11.07.2021, 13:03 [ТС]
	nicolas2008, ах если бы Видимо, Excel не под управлением моей программы работает, а под управлением Windows и культура моей программы ему не особо интересна. По крайней мере у меня не получилось 0

Excel. Создание выпадающего списка (Validation) со смещением

Решение