Excel vba если ячейка равна
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT'ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.
Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.
Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.
Загрузка книги и события
При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().
Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.
Защита
Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.
Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.
Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.
Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.
Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.
Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.
Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).
Заключение
VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.
Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.
Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
Для справки: выражение Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Для справки: порядковый номер ячейки в диапазоне считается построчно слева направо с перемещением к следующей строке сверху вниз.
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
Свойство | Описание |
---|---|
Address | Возвращает адрес ячейки (диапазона). |
Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
Name | Возвращает или задает имя ячейки (диапазона). |
NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Предположим, у меня есть несколько кодов макросов в моей книге, и теперь я хочу запускать эти коды на основе значения ячейки. В этой статье я расскажу о нескольких ситуациях, с которыми вы можете столкнуться в повседневной работе при использовании Excel.
Запустить или запустить макрос, если значение ячейки больше или меньше определенного значения с кодом VBA
Например, если значение в ячейке A1 находится в диапазоне от 10 до 50, запустите macro1, а если значение больше 50, запустите macro2. Чтобы решить эту задачу в Excel, примените следующий код VBA.
1. Щелкните правой кнопкой мыши вкладку листа, на которой вы хотите выполнить макрос на основе значения ячейки, а затем выберите Просмотреть код из контекстного меню, а в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой модуль:
Код VBA: запуск макроса, если значение ячейки больше или меньше:
Внимание: В приведенном выше коде:
A1 это ячейка, которая содержит конкретное значение, на основе которого вы хотите запустить макрос;
Случай с 10 по 50: Macro1: это означает, что если значение находится между 10 и 50, запустить Macro1;
Случай> 50: Macro2: это означает, что если значение больше 50, запустить Macro2.
Пожалуйста, измените названия макросов и критерии по своему усмотрению, и вы также можете добавить дополнительные критерии после Коробка скрипты.
2. Затем сохраните и закройте это окно кода, теперь, когда значение, которое вы вводите в ячейке A10, составляет от 50 до 1, будет запущен макрос Macro1, если введенное значение больше 50, выполняется макрос Macro2.
Запустить или запустить макрос, если значение ячейки равно определенному тексту с кодом VBA
Если вы хотите запускать макрос на основе определенного текста в ячейке, например, для запуска макроса 1, если введен текст «Удалить», и запуска макроса 2, если введен текст «Вставить». Следующий код может оказать вам услугу.
1. Щелкните правой кнопкой мыши лист, на котором вы хотите выполнить макрос на основе значения ячейки, а затем выберите Просмотреть код из контекстного меню, а в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой модуль:
Код VBA: запустить макрос, если значение ячейки больше или меньше:
Внимание: В приведенном выше коде "Удалить(Основной ключ) и Вставить»- это тексты ячеек, на основе которых вы хотите запускать макросы, и Macro1 и Macro2 макросы, которые вы хотите выполнить на основе текста. Пожалуйста, измените их по своему усмотрению.
2. Затем сохраните этот код и закройте окно, теперь при вводе текста «Удалить» в ячейку A1 срабатывает макрос 1, если вводится текст «Вставить», выполняется макрос 2.
Проверьте, соответствует ли ячейка любому значению в списке
Например, у меня есть список текстовых строк в столбце A, и теперь я хочу проверить, равно ли значение ячейки в этом столбце любому значению в другом списке столбца E. Если равно, отобразить ИСТИНА, в противном случае отображается значение ЛОЖЬ. как показано ниже.
Проверьте, совпадает ли ячейка с каким-либо значением в списке с функцией СУММПРОИЗВ
Чтобы проверить, соответствует ли ячейка какому-либо значению в другом списке данных, функция СУММПРОИЗВ может оказать вам услугу. Общая формула функции СУММПРОИЗВ:
- cell : Ячейка, которую вы хотите проверить;
- array : Диапазон ячеек, который вы хотите сравнить с определенной ячейкой.
1. Введите или скопируйте следующую формулу в пустую ячейку, в которую вы хотите поместить результат:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, теперь, если значение в ячейке A2 равно любому значению в списке E2: E6, отображается ИСТИНА, если не равно, отображается ЛОЖЬ. , см. снимок экрана:
Пояснение к формуле:
- - (A2 = $ E $ 2: $ E $ 6) : сравнить значение в A2 со значением каждой ячейки в диапазоне E2: E6, он вернет массив значений ИСТИНА или ЛОЖЬ, если ячейка A2 равна любому значению в диапазоне E2: E6, возвращается ИСТИНА, в противном случае оно будет ЛОЖНЫЙ. И двойной дефис (-) используется для преобразования ИСТИНА или ЛОЖЬ в 1 или 0;
- СУММПРОИЗВ (- (A2 = $ E $ 2: $ E $ 6))> 0 : SUMPRODUCT добавит элементы в массив, затем используйте> 1, чтобы получить результат ИСТИНА или ЛОЖЬ.
Внимание: Иногда вам может потребоваться использовать некоторые конкретные тексты, чтобы заменить ИСТИНА или ЛОЖЬ в результате, в этом случае, пожалуйста, примените любую из следующих формул по мере необходимости:
Используемая относительная функция:
- SUMPRODUCT:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
Другие статьи:
- Если ячейка содержит текст, отобразить в Excel
- Если у вас есть список текстовых строк в столбце A и строка ключевых слов, теперь вам нужно проверить, появляются ли ключевые слова в текстовой строке. Если ключевые слова появляются в ячейке, отображается ее отображение, если нет, отображается пустая ячейка. Как решить эту задачу в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: