Всем привет! 👋
Сегодня в уроке рассмотрим, как создавать зависимые выпадающие списки в Excel с помощью VBA и предоставим готовый пример решения, который вы сможете легко адаптировать для своих нужд.
Пример решения будет доступен для загрузки, и мы дадим подробные инструкции по его использованию. В результате вы сможете создавать зависимые выпадающие списки быстро и эффективно.
📢 Вы можете загрузить файл с исходным кодом в конце данной статьи 🔽
Зависимые выпадающие списки позволяют создавать более удобные и функциональные таблицы, где значения в одном списке зависят от выбора в другом списке. Это очень удобно для работы с большими объемами данных и для создания форм для пользователей.
Рассмотрим 2 варианта возможного решения.
▶️ Исходные данные
В качестве примера рассмотрим следующий диапазон данных:
▶️ Наша задача:
Поехали...
▶️ Вариант 1: Стандартная версия
Этот код - обработчик событий, который срабатывает при изменении данных на листе.
Код выполняет действия на основе того, в каком диапазоне произошли изменения, и обновляет список проверки данных в столбце B или C листа списка в соответствии с выбранными категориями или элементами:
🔔 Этот код должен быть помещен в модуль листа "Список".
🔘 Описание кода:
▶️ Вариант 2: Добавим функциональность
Изменим рассмотренный выше код чтобы очистить соответствующую ячейку в столбце C листа "Список", когда значение в столбце B изменяется:
🔔 Также мы можем добавить условие позволяющее очистить значения в соответствующих ячейках столбцов B и C листа "Список", когда значение в столбце A листа "Список" удаляется:
⚠️ Не забудьте добавить функцию GetUniqueValues
GetUniqueValues - это пользовательская функция VBA, которая используется в коде события Worksheet_Change, чтобы получить массив уникальных значений из указанного диапазона ячеек.
▶️ Описание функции GetUniqueValues
🔘 Как работает функция:
- Объявление функции с именем GetUniqueValues и тремя параметрами: rngData, rngCriteria и strCriteria. Возвращаемое значение функции - Variant.
- Объявление переменных dict (типа Object), cell (типа Range) и i (типа Long).
- Создание экземпляра объекта Scripting.Dictionary и присвоение его переменной dict.
- Если параметр rngCriteria не указан, то запускается цикл for each для итерации по всем ячейкам в диапазоне rngData, удовлетворяющим условию xlCellTypeConstants.
- Если значение ячейки не было ранее добавлено в словарь, то оно добавляется в него с ключом равным значению ячейки.
- Если параметр rngCriteria указан, то запускается цикл for для итерации по строкам в диапазоне данных rngData.
- Проверка, удовлетворяет ли значение ячейки в столбце критериев, расположенной в строке i, указанному критерию strCriteria.
- Если значение ячейки данных не было ранее добавлено в словарь и удовлетворяет критерию, то оно добавляется в словарь с ключом равным значению ячейки данных.
- Возвращение массива уникальных значений словаря.
- Конец функции.
⏩ Альтернативный прием позволяющий избежать замедления работы кода:
🔔 Эта версия функции использует массивы для ускорения обработки данных. Вы можете заменить рассмотренную выше версию функции GetUniqueValues на эту и проверить, улучшится ли производительность.