Excel vba как сделать ячейку неактивной

Обновлено: 07.07.2024

Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

Предполагается, что вы знаете основы 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. сделать условие: если нажали на какую либо ячейку, то повторное нажатие нужно сделать только на одну из закрашенных ячеек, иначе из всех окрашенных ячеек(макросом) убирается заливка, и закраска идет как при первом нажатии.(например, нажали на ячейку C5, закрасился диапазон B4:D6. если следующее нажатие будет по ячейке D6, то закрашиваются дополнительно все крайние ячейки: C5:E7. если мы бы нажали на ячейку F9, тогда убираем заливку с ячеек, которые были закрашены перед этим нажатием, и закрашиваем ячейки во круг F9. это E8:G10)

Если это подскажите, огромное спасибо!

Если это подскажите, огромное спасибо! lFJl

Если это подскажите, огромное спасибо! Автор - lFJl
Дата добавления - 02.09.2015 в 09:13

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:

если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

А на диапазон ячеек с A1 по D10 — так:

Dim oRange As Range

С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:

Set oRange1 = Worksheets("Лист1").Range("C1")

пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).

второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4)

Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:

Set oRange = Range(Cells(1, 1), Cells(5, 3))

третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.

Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:

Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().

If Selection.Areas.Count > 1 Then

Debug.Print "Диапазон с несмежными областями"

Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address 'Так оно и есть

Точно такие же особенности у свойств Row и Rows, Column и Columns.

Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом

Dim oRange As Range

Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.

Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом

Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
Worksheets("Лист1").Activate
ActiveCell.Dependents.Select

Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.

В Microsoft Excel обычно выбирается ячейка или ячейки, а затем выполняется действие, например форматирование ячеек или ввод значений. В Visual Basic обычно не требуется выбирать ячейки перед их изменением.

Например, чтобы ввести формулу в ячейку D6 с помощью Visual Basic, не нужно выбирать диапазон D6. Просто возвратите объект Range для этой ячейки, а затем присвойте свойству Formula нужную формулу, как показано в следующем примере.

Дополнительные сведения и примеры использования других методов для управления ячейками без их выбора см. в статье Ссылки на ячейки и диапазоны.

Использование метода Select и свойства Selection

Метод Select активирует листы и объекты на листах; свойство Selection возвращает объект, представляющий текущее выделение на активном листе в активной книге. Перед использованием свойства Selection необходимо активировать книгу, активировать или выбрать лист, а затем выбрать диапазон (или другой объект) с помощью метода Select.

Средство записи макросов часто создает макрос, использующий метод Select и свойство Selection. Следующая процедура Sub была создана с помощью средства записи макросов и показывает, как Select и Selection работают вместе.

В следующем примере выполняется та же задача без активации или выбора листа или ячеек.

Выбор ячеек на активном листе

Если для выбора ячеек используется метод Select, имейте в виду, что Select работает только на активном листе. Если выполнить процедуру Sub из модуля, метод Select завершится ошибкой, если процедура не активирует лист перед использованием метода Select для диапазона ячеек. Например, следующая процедура копирует строку из Листа1 в Лист2 в активной книге.

Активация ячейки в выделенном фрагменте

Метод Activate можно использовать для активации ячейки в выделенном фрагменте. Активной может быть только одна ячейка, даже если выделен диапазон ячеек. Следующая процедура выделяет диапазон и активирует ячейку в диапазоне, не изменяя выделенный фрагмент.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

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

Активная ячейка является собственностью в VBA. Мы используем его в разных ситуациях в VBA. Мы можем присвоить значения активной ячейке, используя функцию активной ячейки VBA, или получить адрес активной ячейки. Что вернули эти функции? Функция активной ячейки возвращает свойство диапазона активной ячейки в активной рабочей таблице. Как объяснено в приведенном выше утверждении в определении, если лист 2 активен, а ячейка B4 является активной ячейкой, функция активной ячейки в VBA извлекает свойства диапазона ячейки B4 на листе 2.

Синтаксис активной ячейки в Excel VBA

Ниже приведен синтаксис Active Cell в Excel VBA

Синтаксис используется для присвоения определенного значения активной ячейке.

Синтаксис выберет значение или свойство активной ячейки в активной рабочей таблице.

Если нам нужно изменить шрифт активной ячейки, то синтаксис будет следующим

Мы также можем отобразить строки и столбец активной ячейки, используя следующий синтаксис

Давайте используем синтаксис, описанный в нескольких примерах, и научимся играть с активными ячейками.

Примечание: Для того, чтобы использовать VBA убедитесь, что на вкладке разработчика включен в закладке Файл из раздела настроек.

Примеры активной ячейки Excel VBA

Ниже приведены различные примеры активной ячейки VBA в Excel:

Вы можете скачать этот шаблон VBA Active Cell Excel здесь - Шаблон VBA Active Cell Excel

VBA Active Cell - Пример № 1

В этом примере мы хотим изменить значение текущей ячейки на какую-то ячейку. Например, на листе 1 выберите ячейку A2 и вставьте значение как ANAND, и мы хотим изменить значение для этой активной ячейки как ARAN.

Выполните следующие шаги, чтобы использовать VBA Active Cell в Excel.

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: Нажмите на вкладку «Вставка» и нажмите на модули, чтобы вставить новый модуль.

Шаг 3: Объявите подфункцию, чтобы начать писать код.

Код:

Шаг 4: Активируйте рабочий лист 1 с помощью функции ниже.

Код:

Шаг 5: Мы можем проверить, что в ячейке A2 на листе 1 у нас есть значение как ANAND, и это активная ячейка.

Шаг 6: Теперь используйте следующую инструкцию, чтобы изменить значение активной ячейки.

Код:

Шаг 7: Запустите приведенный выше код с помощью кнопки запуска или нажмите F5.

Мы видим, что значение в ячейке A2 было изменено.

VBA Active Cell - Пример № 2

Теперь мы изменили значение активной ячейки с ANAND на ARAN. Как мы отображаем текущее значение активной ячейки? Об этом мы узнаем в этом примере.

Выполните следующие шаги, чтобы использовать VBA Active Cell в Excel.

Шаг 1: Перейдите на вкладку разработчика и нажмите Visual Basic, чтобы открыть редактор VB.

Шаг 2: В том же самом модуле объявить подфункцию, чтобы начать писать код.

Код:

Шаг 3: Активируйте лист 1 с помощью следующего кода.

Код:

Шаг 4: Теперь давайте выберем активную ячейку с помощью следующего кода.

Код:

Шаг 5: Теперь давайте отобразим значение выбранной ячейки с помощью следующего кода.

Код:

Шаг 6: Запустите приведенный выше код, нажав клавишу F5 или предоставленную кнопку запуска, и просмотрите следующий результат.

Активная ячейка была A2 и имеет значение ARAN, поэтому отображаемое свойство - ARAN.

VBA Active Cell - Пример № 3

Давайте изменим шрифт ячейки A2, которая была выбранной ячейкой. Давайте сделаем шрифт как жирный. Изначально не было выбрано ни одного шрифта.

Для этого выполните следующие действия, чтобы использовать VBA Active Cell в Excel.

Шаг 1: Перейдите на вкладку разработчика и нажмите Visual Basic, чтобы открыть VB Editor.

Шаг 2: В том же модуле объявите подфункцию, чтобы начать писать код.

Код:

Шаг 3: Давайте сначала активируем рабочий лист, чтобы использовать активную ячейку.

Код:

Шаг 4: Давайте изменим шрифт выбранной ячейки следующим кодом.

Код:

Шаг 5: Запустите приведенный выше код, нажав клавишу F5 или предоставленную кнопку запуска, и посмотрите результат.

Шрифт активной ячейки изменяется на BOLD.

VBA Active Cell - Пример № 4

Теперь мы хотим знать, в какой строке или в каком столбце находится текущая активная ячейка. Как это сделать, мы узнаем в этом примере.

Для этого выполните следующие действия, чтобы использовать VBA Active Cell в Excel.

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: В том же модуле объявите подфункцию, чтобы начать писать код.

Код:

Шаг 3: Давайте сначала активируем рабочий лист, чтобы использовать свойства активной ячейки.

Код:

Шаг 4: Теперь мы выбираем активную ячейку с помощью следующего кода.

Код:

Шаг 5: Теперь мы можем отобразить текущую строку активной ячейки с помощью следующего кода.

Код:

Шаг 6: Мы также можем получить текущий столбец активной ячейки с помощью следующего кода.

Код:

Шаг 7: Теперь нажмите F5 или кнопку запуска, чтобы запустить приведенный выше код и увидеть следующий результат.

Вышеуказанным результатом была строка активной ячейки. Нажмите ОК, чтобы увидеть столбец активной ячейки.

То, что нужно запомнить

Есть несколько вещей, которые мы должны помнить об активной ячейке в VBA:

Активная ячейка - это текущая или выбранная ячейка на любом листе.
Мы можем отобразить или изменить свойства активного адреса ячейки в VBA.
Чтобы использовать свойства активной ячейки, мы должны сначала активировать текущую рабочую таблицу.

Excel vba как сделать ячейку неактивной

Visual Basic

Опции

Возможности языка

Ускорение работы макросов

Загрузка книги и события

Защита

Заключение

Использование метода Select и свойства Selection

Выбор ячеек на активном листе

Активация ячейки в выделенном фрагменте

Поддержка и обратная связь

Синтаксис активной ячейки в Excel VBA

Примеры активной ячейки Excel VBA

VBA Active Cell - Пример № 1

VBA Active Cell - Пример № 2

VBA Active Cell - Пример № 3

VBA Active Cell - Пример № 4

То, что нужно запомнить

Рекомендуемые статьи