Всем привет! 👋
В предыдущем обзоре мы обсуждали пример как можно найти и заменить значение используя элементы управления и глобальные переменные.
В продолжение темы рассмотрим пример замены значений в источнике исходных данных с использованием пользовательской формы и циклов For и For Each.
📢 Скачать исходник с примером кода вы можете в конце статьи 🔽
🔔 Этот урок будет полезен если вы хотите научиться создавать свои приложения с использование VBA. Следите за серией обзоров, и уже через короткое время вы сможете создать свое первое приложение! 😉
Постановка задачи и исходные данные
Для целей данного примера рассмотрим следующие данные:
🎯 Задача на сегодня
Предоставить пользователю возможность:
- взаимодействовать с источником данных (кстати, он может скрыт) посредством пользовательской формы;
- осуществлять поиск и выбор посредством выпадающих списков;
- вносить изменения в источник данных.
🔔 Чтобы быть последовательным в действиях при создании приложений или даже небольших расчетных форм следует придерживаться определенного алгоритма. Это значительно облегчает работу!
⏺ В нашем случае алгоритм следующий ⬇️
- Создать пользовательскую форму с двумя ComboBox: ComboBox1 для выбора значения из столбца B и ComboBox2 для выбора значения из столбца C и кнопкой, на которую будет назначен макрос для поиска и замена значений.
- Задать зависимые выпадающие списки посредством которых: получить текст из источника (столбец В) в ComboBox1 и соответствующее ему значение (столбец С) в ComboBox2.
- Задать макрос, который (по нажатию кнопки):
- переберет каждую ячейку в столбце В на указанном листе;
- найдет соответствующие значения столбца C для каждого выбранного значения столбца B;
- выполнит замену указанным в ComboBox2 значением;
- выведет сообщение о произведенной замене.
▶️ Пользовательская форма и элементы управления
Первым шагом будет создание пользовательской формы, на которой разместим элементы управления для ввода данных и выполнения замены:
- Расположите элементы управления на форме: добавьте ComboBox1 (для выбора критерия поиска), ComboBox2 (для выбора значения из выпадающего списка) и CommandButton (для выполнения замены).
- Установите соответствующие названия и подсказки для элементов управления.
Если вы пропустили наши уроки рекомендуем данный обзор:
Теперь у вас есть пользовательская форма с которой пользователи будут взаимодействовать:
▶️ Выпадающий список
Чтобы у пользователя была возможность выбора первостепенной задачей является создание выпадающего списка.
Представленный ниже макрос выполняет инициализацию формы пользовательского интерфейса (UserForm) в Excel. В частности, настраивает ComboBox1, чтобы разрешить только выбор из выпадающего списка, а также заполняет его уникальными значениями из столбца B листа "Лист1".
Напомним, что у ComboBox есть свойство Style:
🔔 Свойство позволяет установить запрет на ввод данных с клавиатуры.
Аналогичный результат можно получить если разместить, например, в событии UserForm_Initialize (чтобы применить его при инициализации формы) следующую строку кода:
После установки Style в значение 2-fmStyleDropDownList, пользователи смогут выбирать только из существующих вариантов в выпадающем списке и не смогут вводить собственные значения с клавиатуры.
🔘 Для целей данного примера будем установим свойство Style непосредственно в коде:
Теперь, форма пользовательского интерфейса будет содержать ComboBox1, в котором можно выбирать значения из столбца B листа "Лист1", а также будет запрещен ввод с клавиатуры в этом комбобоксе:
📍 Примечание автора: В целях данного примера определено, что данные столбца не содержат повторяющихся значений.
🔘 Но в том случае, если все же требуется исключить повторы, создать выпадающий список уникальных значений можно следующим макросом:
▶️ Зависимый выпадающий список
Чтобы облегчить пользователя поиск и выбор данных для ComboBox2 установим зависимый от ComboBox1 выпадающий список.
🔔 Важно! чтобы ComboBox2 заполнялся после выбора значения в ComboBox1 нужно поместить код для заполнения ComboBox2 в событие ComboBox1_Change().
🔘 Вот как это можно сделать:
Теперь при каждом изменении значения в ComboBox1, ComboBox2 будет заполняться значениями из столбца C, соответствующими выбору в ComboBox1:
📍 Примечание автора: В контексте, что список по столбцу В уникальный найденное значение может быть сразу отражено. Для этого можно несколько изменить код или использовать TextBox вместо ComboBox:
🔘 Код в этом случае имел бы вид:
В этом случае соответствующее значение из столбца C будет сразу помещено в TextBox1:
🔔 Но, так как мы планируем установить в дальнейшем дополнительную связь между элементами управления и создать вариативный поиск, то сразу применяем ComboBox и задаем, что значение должно быть выбрано из выпадающего списка.
⚠️ Возможные ошибки
Если ComboBox2 не заполняется после выбора значения в ComboBox1, есть несколько правил, которые следует проверить:
- код в событии ComboBox1_Change() активируется при выборе значения в ComboBox1. Перейдите на форму и выберите значение в ComboBox1, чтобы убедиться, что событие выполняется.
- нет ли у других событий или условий, которые могут блокировать выполнение кода.
- объект ComboBox1 на форме называется именно "ComboBox1" (без кавычек). Если назначено другое имя для этого элемента управления, замените "ComboBox1" на соответствующее имя в коде.
- правильно определена переменная ws и что она указывает на правильный лист в вашей книге. Проверьте, что ws установлен на лист с данными, которые хотите использовать.
- в столбце B есть значения, соответствующие выбору в ComboBox1. Если столбец B пуст или не содержит совпадающих значений, ComboBox2 не заполнится.
- значения в столбце C, соответствующие выбору в ComboBox1.
▶️ Поиск и замена значений
Выполнить поиск строки содержащей искомое значение и выполнить замену используя дополнительный элемент управления или глобальные переменные мы рассмотрели в обзоре:
Предложенный ниже способ является еще одним из примеров того, как можно выполнить поставленную задачу.
Выбор способа определяет условие поставленной задачи.
В данном примере предполагается, что основной список по которому происходит сравнение уникальный.
Поэтому можем использовать один из следующих принципов.
▶️ Цикл For
- используется для выполнения одного и того же блока кода (тела цикла) несколько раз, основываясь на определенных условиях и параметрах.
📝 Краткая характеристика цикла For:
- Инициализация: Цикл For начинается с инициализации, где вы задаете начальное значение счетчика (часто называемого переменной цикла), указываете конечное значение и определяете шаг (на сколько увеличивать счетчик с каждой итерацией).
- Условие завершения: Во время выполнения цикла на каждой итерации счетчик увеличивается на указанный шаг, и цикл продолжает выполняться до тех пор, пока счетчик не достигнет или не пройдет через конечное значение.
- Тело цикла: Тело цикла представляет собой блок кода, который будет выполнен на каждой итерации. Здесь вы размещаете действия, которые должны повторяться.
- Изменение счетчика: Счетчик цикла автоматически увеличивается (или уменьшается, если шаг отрицателен) после каждой итерации. Это контролируется шагом, который вы определили при инициализации.
- Завершение цикла: Цикл завершает выполнение, когда счетчик достигает или проходит через конечное значение, указанное в условии завершения.
🔘 Представленный ниже макрос позволяет заменить значения в столбце C на листе, если значение в столбце B совпадает с выбранным значением из ComboBox1:
📝 Краткое описание:
- Определение рабочего листа (ws) в рабочей книге, указанной как "Лист1".
- Получение выбранных значений из ComboBox1 и ComboBox2 и сохранение их в переменные selectedValue1 и selectedValue2.
- Определение последней заполненной строки в столбце B на листе (lastRow).
- Запуск цикла For для перебора строк от 2 до lastRow.
- На каждой итерации цикла, код проверяет, совпадает ли значение в столбце B (на текущей строке) с selectedValue1.
- Если значение совпадает, то оно заменяется значением selectedValue2 в столбце C на той же строке.
▶️ Цикл For Each
- используется для перебора элементов в коллекции, массиве или другой структуре данных, выполняя определенные действия для каждого элемента в последовательности.
📝 Краткая характеристика цикла For Each:
- Итерация по коллекции: Цикл For Each предназначен для итерации по элементам коллекции, массиву или объекту, который может быть перечислен.
- Тип данных элемента: В цикле For Each необходимо указать переменную, которая будет представлять элемент коллекции на каждой итерации. Эта переменная должна быть того же типа данных, что и элементы коллекции.
- Тело цикла: Внутри цикла располагается блок кода, который будет выполняться на каждой итерации. В этом блоке можно выполнять операции с текущим элементом коллекции.
- Завершение цикла: Цикл For Each завершает выполнение, когда все элементы коллекции были перебраны. В этот момент управление передается за пределы цикла.
Для нашей задачи цикл For Each поможет перебирать каждую ячейку в столбце B и выполнять замену значений в столбце C, если они соответствуют выбранным значениям из ComboBox1 и ComboBox2.
🔘 Вот как это можно сделать:
📝 Краткое описание:
- В цикле For Each перебираются все ячейки в столбце B от B2 до последней заполненной строки (B & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row).
- Для каждой ячейки в столбце B проверяется, соответствует ли её значение выбранному значению из ComboBox1 (переменная selectedValue1).
- Если значение в столбце B соответствует выбранному значению, то значение в соседней ячейке столбца C (с помощью cell.Offset(0, 1).Value) заменяется значением из ComboBox2 (переменная selectedValue2).
▶️ Дополнения
Чтобы измененное значение сразу было отражено в выпадающем списке следует добавить условие:
Теперь, после замены значения в столбце C, ComboBox2 будет обновлен.
Для связи с пользователем и вывода соответствующих сообщений вы можете использовать оператор MsgBox:
Например, добавим строку:
где, selectedValue задана в качестве глобальной переменной, а для ComboBox2_Click() задан макрос:
▶️ Запуск приложения
Разместите на листе кнопку или любой рисунок, на который следует назначить макрос:
Макрос вызовет созданную форму для взаимодействия с пользователем
➡️ Выбрав нужные данные посредством выпадающих списков при нажатии на кнопку, значения в столбце C будут заменены на выбранные значения из ComboBox2, для всех соответствующих строк, где значение в столбце B совпадает с выбранным значением из ComboBox1:
А пользователю будет выведено соответствующее сообщение:
Какой бы из предложенных выше способа результат будет одинаков.
В следующем обзоре мы дополним интерфейс формы, добавим элементы управления и рассмотрим их взаимосвязь друг с другом 😉.
Оставайтесь на связи 💖
📍 Рекомендуемые статьи 🔽