При работе с Excel большую часть времени вы проводите в области рабочего листа, работая с ячейками и диапазонами.
А если вы хотите автоматизировать свою работу в Excel с помощью VBA, вам нужно знать, как работать с ячейками и диапазонами с помощью VBA.
С диапазонами в VBA можно делать много разных вещей (например, выбирать, копировать, перемещать, редактировать и т. Д.).
Чтобы охватить эту тему, я разделю это руководство на разделы и покажу вам, как работать с ячейками и диапазонами в Excel VBA на примерах.
Давайте начнем.
Все коды, которые я упоминаю в этом руководстве, необходимо поместить в редактор VB. Перейдите в раздел «Где разместить код VBA», чтобы узнать, как это работает.Если вы заинтересованы в изучении VBA простым способом, ознакомьтесь с моими Онлайн-обучение по Excel VBA.
Выбор ячейки / диапазона в Excel с помощью VBA
Чтобы работать с ячейками и диапазонами в Excel с помощью VBA, вам не нужно выбирать его.
В большинстве случаев лучше не выбирать ячейки или диапазоны (как мы увидим).
Несмотря на это, важно, чтобы вы прошли через этот раздел и поняли, как он работает. Это будет иметь решающее значение в вашем обучении VBA, и многие концепции, описанные здесь, будут использоваться в этом руководстве.
Итак, давайте начнем с очень простого примера.
Выбор отдельной ячейки с помощью VBA
Если вы хотите выбрать одну ячейку на активном листе (скажем, A1), вы можете использовать приведенный ниже код:
Sub SelectCell () Диапазон ("A1"). Выберите End Sub
В приведенном выше коде есть обязательные части «Sub» и «End Sub», а также строка кода, которая выбирает ячейку A1.
Диапазон («A1») сообщает VBA адрес ячейки, на которую мы хотим сослаться.
Выбирать является методом объекта Range и выбирает ячейки / диапазон, указанные в объекте Range. Ссылки на ячейки необходимо заключать в двойные кавычки.
Этот код покажет ошибку, если лист диаграммы является активным. Лист диаграммы содержит диаграммы и не используется широко. Поскольку в нем нет ячеек / диапазонов, приведенный выше код не может выбрать его, и в результате будет отображаться ошибка.
Обратите внимание: поскольку вы хотите выбрать ячейку на активном листе, вам просто нужно указать адрес ячейки.
Но если вы хотите выделить ячейку на другом листе (скажем, Sheet2), вам нужно сначала активировать Sheet2, а затем выбрать ячейку в нем.
Sub SelectCell () Worksheets ("Sheet2"). Активируйте диапазон ("A1"). Выберите End Sub
Точно так же вы также можете активировать книгу, затем активировать в ней определенный рабочий лист, а затем выбрать ячейку.
Sub SelectCell () Рабочие книги («Book2.xlsx»). Рабочие листы («Sheet2»). Активировать диапазон («A1»). Выберите End Sub
Обратите внимание, что при обращении к книгам необходимо использовать полное имя вместе с расширением файла (.xlsx в приведенном выше коде). Если книга никогда не сохранялась, вам не нужно использовать расширение файла.
Эти примеры не очень полезны, но позже в этом руководстве вы увидите, как мы можем использовать те же концепции для копирования и вставки ячеек в Excel (с использованием VBA).
Так же, как мы выбираем ячейку, мы также можем выбрать диапазон.
В случае диапазона это может быть диапазон фиксированного или переменного размера.
В диапазоне фиксированного размера вы будете знать, насколько велик этот диапазон, и можете использовать точный размер в своем коде VBA. Но с диапазоном переменного размера вы не представляете, насколько велик диапазон, и вам нужно использовать немного магии VBA.
Посмотрим, как это сделать.
Выбор диапазона фиксированного размера
Вот код, который выберет диапазон A1: D20.
Sub SelectRange () Диапазон ("A1: D20"). Выберите End Sub
Другой способ сделать это - использовать приведенный ниже код:
Sub SelectRange () Диапазон ("A1", "D20"). Выберите End Sub
Приведенный выше код берет адрес верхней левой ячейки (A1) и адрес нижней правой ячейки (D20) и выбирает весь диапазон. Этот метод становится полезным, когда вы работаете с диапазонами переменного размера (как мы увидим, когда позже в этом руководстве будет рассмотрено свойство End).
Если вы хотите, чтобы выбор происходил в другой книге или на другом листе, вам необходимо сообщить VBA точные имена этих объектов.
Например, приведенный ниже код выберет диапазон A1: D20 на листе Sheet2 в книге Book2.
Sub SelectRange () Рабочие книги ("Book2.xlsx"). Рабочие листы ("Sheet1"). Активировать диапазон ("A1: D20"). Выбрать End Sub
А что, если вы не знаете, сколько там строк. Что, если вы хотите выбрать все ячейки, в которых есть значение.
В этих случаях вам необходимо использовать методы, показанные в следующем разделе (при выборе диапазона переменного размера).
Выбор диапазона переменного размера
Вы можете выбрать диапазон ячеек разными способами. Выбранный вами метод будет зависеть от того, как структурированы данные.
В этом разделе я расскажу о некоторых полезных методах, которые действительно полезны при работе с диапазонами в VBA.
Выберите с помощью свойства CurrentRange
В случаях, когда вы не знаете, сколько строк / столбцов содержат данные, вы можете использовать свойство CurrentRange объекта Range.
Свойство CurrentRange охватывает все смежные заполненные ячейки в диапазоне данных.
Ниже приведен код, который выберет текущий регион, содержащий ячейку A1.
Sub SelectCurrentRegion () Диапазон ("A1"). CurrentRegion.Select End Sub
Вышеупомянутый метод хорош, когда у вас есть все данные в виде таблицы без пустых строк / столбцов в ней.
Но если у вас есть пустые строки / столбцы в ваших данных, он не будет выбирать те, которые находятся после пустых строк / столбцов. На изображении ниже код CurrentRegion выбирает данные до строки 10, поскольку строка 11 пуста.
В таких случаях вы можете использовать свойство UsedRange объекта Worksheet.
Выберите с помощью свойства UsedRange
UsedRange позволяет ссылаться на любые измененные ячейки.
Таким образом, приведенный ниже код выберет все используемые ячейки на активном листе.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Выберите End Sub
Обратите внимание, что если у вас есть удаленная ячейка, которая использовалась, она будет учитываться приведенным выше кодом, и все ячейки до тех пор, пока эта используемая ячейка не будет выбрана.
Выберите с помощью свойства конца
Теперь эта часть действительно полезна.
Свойство End позволяет выбрать последнюю заполненную ячейку. Это позволяет имитировать действие клавиш управления стрелками вниз / вверх или клавиш управления вправо / влево.
Попробуем разобраться в этом на примере.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро выбрать последние заполненные ячейки в столбце A.
Проблема здесь в том, что данные могут меняться, и вы не знаете, сколько ячеек заполнено. Если вам нужно сделать это с помощью клавиатуры, вы можете выбрать ячейку A1, а затем использовать клавиши Control + стрелка вниз, и она выберет последнюю заполненную ячейку в столбце.
Теперь давайте посмотрим, как это сделать с помощью VBA. Этот метод пригодится, когда вы хотите быстро перейти к последней заполненной ячейке в столбце переменного размера.
Sub GoToLastFilledCell () Диапазон ("A1"). End (xlDown). Выберите End Sub
Приведенный выше код перейдет к последней заполненной ячейке в столбце A.
Точно так же вы можете использовать End (xlToRight) для перехода к последней заполненной ячейке в строке.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight). Select End Sub
Теперь, что, если вы хотите выбрать весь столбец вместо перехода к последней заполненной ячейке.
Вы можете сделать это, используя приведенный ниже код:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Выберите End Sub
В приведенном выше коде мы использовали первую и последнюю ссылку на ячейку, которую нам нужно выбрать. Независимо от количества заполненных ячеек, приведенный выше код выберет все.
Вспомните приведенный выше пример, в котором мы выбрали диапазон A1: D20, используя следующую строку кода:
Диапазон («A1 ″,« D20 »)
Здесь A1 - это верхняя левая ячейка, а D20 - нижняя правая ячейка в диапазоне. Мы можем использовать ту же логику при выборе диапазонов переменного размера. Но поскольку мы не знаем точного адреса нижней правой ячейки, мы использовали свойство End, чтобы получить его.
В Range («A1», Range («A1»). End (xlDown)) «A1» относится к первой ячейке, а Range («A1»). End (xlDown) относится к последней ячейке. Поскольку мы предоставили обе ссылки, метод Select выбирает все ячейки между этими двумя ссылками.
Точно так же вы также можете выбрать весь набор данных, состоящий из нескольких строк и столбцов.
Приведенный ниже код выберет все заполненные строки / столбцы, начиная с ячейки A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Выберите End Sub
В приведенном выше коде мы использовали Range («A1»). End (xlDown) .End (xlToRight), чтобы получить ссылку на заполненную нижнюю правую ячейку набора данных.
Разница между использованием CurrentRegion и End
Если вам интересно, зачем использовать свойство End для выбора заполненного диапазона, когда у нас есть свойство CurrentRegion, позвольте мне рассказать вам разницу.
С помощью свойства End вы можете указать начальную ячейку. Например, если у вас есть данные в формате A1: D20, но первая строка - это заголовки, вы можете использовать свойство End для выбора данных без заголовков (используя приведенный ниже код).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Выберите End Sub
Но CurrentRegion автоматически выберет весь набор данных, включая заголовки.
До сих пор в этом руководстве мы видели, как по-разному ссылаться на диапазон ячеек.
Теперь давайте посмотрим, как мы можем использовать эти методы для выполнения некоторой работы.
Копирование ячеек / диапазонов с помощью VBA
Как я упоминал в начале этого урока, выбор ячейки не является необходимым для выполнения действий с ней. В этом разделе вы увидите, как копировать ячейки и диапазоны, даже не выбирая их.
Начнем с простого примера.
Копирование одной ячейки
Если вы хотите скопировать ячейку A1 и вставить ее в ячейку D1, приведенный ниже код сделает это.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Обратите внимание, что метод копирования объекта диапазона копирует ячейку (точно так же, как Control + C) и вставляет ее в указанное место назначения.
В приведенном выше примере кода место назначения указано в той же строке, где вы используете метод копирования. Если вы хотите сделать свой код еще более читаемым, вы можете использовать приведенный ниже код:
Sub CopyCell () Range ("A1"). Место назначения копирования: = Range ("D1") End Sub
Приведенные выше коды скопируют и вставят значение, а также форматирование / формулы в него.
Как вы уже могли заметить, приведенный выше код копирует ячейку, не выбирая ее. Независимо от того, где вы находитесь на листе, код скопирует ячейку A1 и вставит ее в D1.
Также обратите внимание, что приведенный выше код перезапишет любой существующий код в ячейке D2. Если вы хотите, чтобы Excel сообщал вам, есть ли что-то в ячейке D1, не перезаписывая это, вы можете использовать приведенный ниже код.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Вы хотите перезаписать существующие данные", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1" ") End If End Sub
Копирование диапазона фиксированного размера
Если вы хотите скопировать A1: D20 в J1: M20, вы можете использовать следующий код:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
В ячейке назначения вам просто нужно указать адрес верхней левой ячейки. Код автоматически скопирует точный скопированный диапазон в место назначения.
Вы можете использовать ту же конструкцию для копирования данных с одного листа на другой.
Приведенный ниже код скопирует A1: D20 с активного листа на Sheet2.
Sub CopyRange () Range ("A1: D20"). Копировать рабочие листы ("Sheet2"). Range ("A1") End Sub
Вышеупомянутое копирует данные с активного листа. Поэтому перед запуском кода убедитесь, что лист с данными является активным. На всякий случай вы также можете указать имя рабочего листа при копировании данных.
Sub CopyRange () Worksheets ("Sheet1"). Range ("A1: D20"). Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Преимущество приведенного выше кода заключается в том, что независимо от того, какой лист активен, он всегда копирует данные из Sheet1 и вставляет их в Sheet2.
Вы также можете скопировать именованный диапазон, используя его имя вместо ссылки.
Например, если у вас есть именованный диапазон под названием «SalesData», вы можете использовать приведенный ниже код, чтобы скопировать эти данные в Sheet2.
Sub CopyRange () Range ("SalesData"). Копировать рабочие листы ("Sheet2"). Range ("A1") End Sub
Если область действия именованного диапазона - это вся книга, вам не нужно находиться на листе с именованным диапазоном для запуска этого кода. Поскольку именованный диапазон ограничен рабочей книгой, вы можете получить к нему доступ с любого листа, используя этот код.
Если у вас есть таблица с именем Table1, вы можете использовать приведенный ниже код, чтобы скопировать ее на Sheet2.
Sub CopyTable () Range ("Table1 [#All]"). Копировать рабочие листы ("Sheet2"). Range ("A1") End Sub
Вы также можете скопировать диапазон в другую рабочую книгу.
В следующем примере я копирую таблицу Excel (Table1) в книгу Book2.
Подложка CopyCurrentRegion () Range ("Table1 [#All]"). Копировать книги ("Book2.xlsx"). Worksheets ("Sheet1"). Range ("A1") End Sub
Этот код будет работать, только если рабочая книга уже открыта.
Копирование диапазона переменного размера
Один из способов скопировать диапазоны переменного размера - преобразовать их в именованные диапазоны или таблицу Excel и использовать коды, как показано в предыдущем разделе.
Но если вы не можете этого сделать, вы можете использовать свойство CurrentRegion или End объекта диапазона.
Приведенный ниже код скопирует текущую область на активном листе и вставит ее в Sheet2.
Подложка CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Если вы хотите скопировать первый столбец вашего набора данных до последней заполненной ячейки и вставить его в Sheet2, вы можете использовать следующий код:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Копировать рабочие листы ("Sheet2"). Range ("A1") End Sub
Если вы хотите скопировать как строки, так и столбцы, вы можете использовать приведенный ниже код:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Копировать рабочие листы ("Sheet2"). Range ("A1") End Sub
Обратите внимание, что все эти коды не выделяют ячейки при выполнении. В общем, вы найдете только несколько случаев, когда вам действительно нужно выбрать ячейку / диапазон, прежде чем работать с ним.
Назначение диапазонов объектным переменным
До сих пор мы использовали полный адрес ячеек (например, Workbooks («Book2.xlsx»). Worksheets («Sheet1»). Range («A1»)).
Чтобы сделать ваш код более управляемым, вы можете назначить эти диапазоны объектным переменным, а затем использовать эти переменные.
Например, в приведенном ниже коде я назначил диапазон источника и назначения переменным объекта, а затем использовал эти переменные для копирования данных из одного диапазона в другой.
Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Конец подписки
Начнем с объявления переменных как объектов Range. Затем мы назначаем диапазон этим переменным с помощью оператора Set. После того, как диапазон был назначен переменной, вы можете просто использовать переменную.
Введите данные в следующую пустую ячейку (используя поле ввода)
Вы можете использовать поля ввода, чтобы пользователь мог вводить данные.
Например, предположим, что у вас есть набор данных ниже, и вы хотите ввести запись о продажах, вы можете использовать поле ввода в VBA. Используя код, мы можем убедиться, что он заполняет данные в следующей пустой строке.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Set Amount = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Категория продукта") Quantity.Value = InputBox ("Количество") Amount.Value = InputBox ("Amount") End Sub
Приведенный выше код использует поле ввода VBA для получения входных данных от пользователя, а затем вводит их в указанные ячейки.
Обратите внимание, что мы не использовали точные ссылки на ячейки. Вместо этого мы использовали свойства End и Offset, чтобы найти последнюю пустую ячейку и заполнить ее данными.
Этот код нельзя использовать. Например, если вы вводите текстовую строку, когда поле ввода запрашивает количество или сумму, вы заметите, что Excel позволяет это. Вы можете использовать условие If, чтобы проверить, является ли значение числовым или нет, и затем разрешить его соответственно.
Цикл по ячейкам / диапазонам
До сих пор мы видели, как выбирать, копировать и вводить данные в ячейки и диапазоны.
В этом разделе мы увидим, как перебирать набор ячеек / строк / столбцов в диапазоне. Это может быть полезно, когда вы хотите проанализировать каждую ячейку и выполнить какое-либо действие на ее основе.
Например, если вы хотите выделить каждую третью строку в выделенном фрагменте, вам нужно пройтись по ней и проверить номер строки. Точно так же, если вы хотите выделить все отрицательные ячейки, изменив цвет шрифта на красный, вам нужно пройти и проанализировать значение каждой ячейки.
Вот код, который будет перебирать строки в выбранных ячейках и выделять альтернативные строки.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows, если Myrow.Row Mod 2 = 0, то Myrow.Interior.Color = vbCyan End, если следующий Myrow End Sub
В приведенном выше коде функция MOD используется для проверки номера строки в выделенном фрагменте. Если номер строки четный, он выделяется голубым цветом.
Вот еще один пример, в котором код проходит через каждую ячейку и выделяет ячейки с отрицательными значениями.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Выбор для каждой ячейки Mycell в Myrange Если Mycell <0, то Mycell.Interior.Color = vbRed End, если следующий Mycell End Sub
Обратите внимание, что вы можете сделать то же самое, используя условное форматирование (которое является динамическим и лучший способ сделать это). Этот пример предназначен только для того, чтобы показать вам, как цикл работает с ячейками и диапазонами в VBA.
Куда поместить код VBA
Хотите знать, где находится код VBA в вашей книге Excel?
В Excel есть серверная часть VBA, называемая редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.
Вот как это сделать:
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код.Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
