Макросы – эффективный способ автоматизации. Он позволяет запрограммировать конкретные операции. Например, они позволяют вывести определенную информацию в ячейке исходя из того, какое содержимое находится в другой. Конечно, подобный функционал имеется и в наборе стандартных функций Excel, но все зависит от сложности действий, которые следует выполнить.
И для реализации данного функционала, важно ознакомиться с понятием «объекты», являющееся ключевым при программировании электронных таблиц.
Под этим термином в Excel подразумеваются совокупность частей и элементов функционала, которые позволяют создать таблицу. В этот перечень входят листы, диапазоны как в целом, так и каждая строка или столбец по отдельности. Кроме этого, сам документ и даже приложение Windows, запущенное на компьютере пользователя, служит объектом.
Все они обладают набором характеристик, таких как название, защищенность, видимость, область прокрутки, а также многие другие. Они называются свойствами. С их помощью есть возможность менять характеристики объектов, что может быть очень полезным во время работы макроса.
Изменяя их, можно влиять на особенности взаимодействия пользователя с документом. Так, скорректировав свойство Visible, можно сделать таблицу невидимой.
Кроме этого, язык VBA предусматривает идею, каждый объект — это коллекция. Как догадливый читатель догадывается, это совокупность нескольких объектов. К примеру, Rows — это объект, контейнирующий все строчки документа, которые человек может как использовать, так и нет.
Получение доступа к объектам
Чтобы запрограммировать макрос на вызов требуемого объекта, важно сперва вызвать родительский. Например, для получения доступа к определенному рабочему документу, следует обратиться к коллекции Workbooks. В нее входит совокупность, как используемых пользователем в данный момент, так и нет.
Приведем таблицу с самыми часто встречаемыми объектами. С полным списком можно ознакомиться на официальном интернет-ресурсе разработчиков Microsoft Office Developer (информация предоставляется на английском языке).
|
Application |
Сама программа Excel, которую пользователь запустил на своем компьютере. |
| Workbooks |
Совокупность всех рабочих документов, входящих в состав объекта Application. Чтобы запросить требуемую книгу, необходимо воспользоваться этим объектом, а в скобках указать ее индекс или имя. Например, Workbooks(2) или Workbooks(«Книга2») |
|
Workbook |
Это один из рабочих документов. Чтобы с ним работать посредством макросов, необходимо сначала вызвать коллекцию Workbooks. Если требуется работать через макрос с данной книгой, можно воспользоваться ключевым словом ActiveWorkbook
Данный объект, прежде всего, предназначен для работы с объектом Sheets. |
| Sheets |
Это массив всех листов. Сюда входят не только те, которые с таблицей, но и графики, если кроме них на листе ничего нет. Чтобы работать с конкретным листом, необходимо в скобках написать его порядковый номер или название (Sheets(1) или Sheets(“Доказательства прекрасного мира»). |
|
Worksheets |
В состав этого контейнера входят все листы документа, открытого на компьютере польозвателя. При этом в его состав не входят диаграммы. Если необходимо воспользоваться графиком посредством макросов, следует применять объект Sheets. Аналогичным предыдущему примеру способом, чтобы начать работать с конкретным элементом этой коллекции (в данном случае, рабочим листом), необходимо в скобках указать его имя или индекс, как показано на примере.
Worksheets(1) или Worksheets(«Лист1»). |
| Worksheet |
Данный объект описывает определенный рабочий лист, к которому мы получили доступ с помощью коллекции Worksheet для работы непосредственно с ним. Чтобы вызвать объекты, включенные в состав этой коллекции, необходимо указать в скобках их порядковый номер или название. Кроме этого, можно использовать параметр ActiveSheet, чтобы работать с данным листом. Эта коллекция позволяет работать со строками и колонками, диапазоном. |
|
Rows, Columns |
Строки и колонки соответственно. Чтобы работать с конкретной строкой или колонкой, необходимо написать ее порядковое число в скобках. К примеру, Rows(1) или Columns(1). |
| Range |
Это перечень ячеек листа. Данный объект может содержать и одну ячейку, и несколько ячеек. Для получения доступа к одной из них, необходимо воспользоваться свойством Cells, и в скобках написать индекс строки и колонки. Например, Worksheet.Cells(1,1). Также можно указать диапазон значений. Для этого в диапазоне нужно поставить соответствующие координаты в скобках и в кавычках. Также есть еще два способа записи. Worksheet.Range(«A1:B10») или Worksheet.Range(«A1», «B10») или Worksheet.Range(Cells(1,1), Cells(10,2)) |
Важно отметить, что если в ссылке разработчик не указал вторую ячейку, то тогда будет считаться, что пользователь записал лишь одну.
В данной таблице видно, что нужно делать, чтобы получить доступ к определенному объекту. Для этого необходимо обращаться к нему через родительскую коллекцию. Так, чтобы записать диапазон, необходимо набрать следующий фрагмент кода.
Workbooks(«Книга1»).Worksheets(«Лист1»).Range(«A1:B10»)
Как указать объект, как значение переменной
Кроме всего прочего, есть возможность присвоить переменной определенный объект. Для этого необходимо использовать слово Set. Приводим пример для большего понимания.
Dim DataWb As Workbook
Set DataWb = Workbooks(«Data3.xlsx»)
Чем отличается активный объект от обычного?
Excel всегда считает один из документов активынм, поскольку в нем человек работает. Аналогично, есть активные объекты других типов. Следовательно, к какому-угодно из них можно обратиться через оператор ActiveWorkbook, ActiveSheet. Если же необходимо вызвать активный объект типа Range, необходимо указывать Selection.
Если в VBA необходимо сделать ссылку на объект без указания конкретной коллекции, в которую он входит, Excel автоматически применяет активную. Аналогично дела обстоят с отсутствием ссылок на конкретный документ или совокупность листов. При отсутствии точного указания, с каким объектом работать, Excel автоматически использует активный для выполнения своих операций.
Эту особенность макросов очень удобно использовать на практике. Достаточно точно описать лишь один объект, и Excel автоматически его станет искать в активных коллекциях. Например, так.
Range(«A1:B10»)
Изменение текущего активного объекта
Если во время исполнения кода необходимо изменить текущий активный объект, необходимо применять метод «Activate» или «Select», как показано на примере.
Workbooks(«Book123.xlsm»).Activate
Worksheets(«Data5»).Select
Range(«A10», «B20»).Select
Мы ниже более детально опишем, для чего эти методы используются.
Свойства
Каждая из коллекций в языке программирования VBA обладает определенным набором свойств. Так, книга может иметь имя, ячейки и другие. Чтобы изменить или получить значения определенных свойств, необходимо написать точку непосредственно после названия соответствующего объекта. Так, чтобы получить имя текущей активной книги, необходимо написать строчку кода ActiveWorkbook.Name. Следовательно, чтобы передать название книги, необходимо указать такой код:
Dim wbImya As String
wbImya = ActiveWorkbook.Name
Раньше мы уже демонстрировали метод работы с листом книги Excel через строку кода:
Workbooks(«WB1»).Worksheets(«WS1»)
Здесь никакого противоречия нет, поскольку объект Worksheet параллельно и являет собой свойство объекта Workbook.
Часть из свойств нельзя изменять, поскольку они имеют статус «только для чтения». Но некоторые из них могут быть изменены. Например, если необходимо изменить имя данного листа на «Лист1450», необходимо с помощью следующего кода присвоить это название данному свойству.
ActiveSheet.Name = «Лист 1450»
Методы: что это такое?
Еще одно понятие, характеризующее объекты – методы. Под этим термином подразумеваются действия, которые может приложение осуществлять. С точки зрения языка VBA, методы – процедуры, связанные с определенными коллекциями. Так, объект Workbook выполняет множество действий, среди которых – методы «Activate», «Close», «Save».
Чтобы выполнить любую из этих процедур, необходимо указать ее название через точку таким же образом, как со свойством. Например.
ActiveWorkbook.Save
Данный метод предоставляет возможность сохранить данную активную книгу Excel. Аналогично другим процедурам, они могут иметь аргументы, используемые при их вызове. Так, метод «Close» предусматриввает три дополнительных параметра, которые дают возможность передавать ему несколько видов информации, такой как путь сохранения книги при ее закрытии, а также другие.
Передача аргументов методу осуществляется путем указания необходимых параметров. Их разделение осуществляется с помощью запятых. Так, если поставлена задача сохранить данную актиуную книгу в файл с расширением .csv с названием «Очень важная книга», то это можно сделать с помощью использования метода SaveAs, в котором аргументы следующие — «Очень важная книга» и «xlCSV». При указании аргументов очень важно соблюдать правильную последовательность. В первую очередь необходимо прописывать название файла, а во вторую — его формат.
На практике это будет выглядеть следующим образом.
ActiveWorkbook.SaveAs «Очень важная книга», xlCSV
Для повышения читабельности кода важно применять именные аргументы каждый раз, когда требуется вызвать метод. Это, безусловно, неудобно, но даст возможность сберечь огромное количество времени в будущем. Если было принято решение использовать именные аргументы, необходимо сначала написать их название, потом значок :=, после чего уже прописать соответствующий параметр. И пример для того, чтобы наглядно продемонстрировать, как это работает.
ActiveWorkbook.SaveAs Filename:=»Book2″, [FileFormat]:=xlCSV
Для расширения своих знаний касаемо имеющихся в языке макросов объектов, свойств и методов, можно открыть специальное окно «Object browser» среды разработки. Для этого необходимо нажать функциональную клавишу F12 на клавиатуре в верхнем ряду. В некоторых ноутбуках, возможно, также потребуется нажать на клавишу Fn.
Примеры
Чтобы лучше понять принципы, о которых мы говорили выше, предлагаем рассмотреть 3 наглядных примера.
Пример 1
Сей фрагмент кода сперва применялся, чтобы показать новичкам, как работает цикл. Но чтобы продемонстрировать, как можно ссылаться на Worksheet, его также можно приводить. В данном случае код вызывает конкретный лист текущего активного документа.
Важно помнить, что свойство «Name» присуще всем листам, и рекомендуется его применять.
‘ Цикл, который проходит через все листы в активной книге
‘ и отображает его название в диалоговом окне
Dim wSheet1 As Worksheet
For Each wSheet1 in Worksheets
MsgBox «Found object: » & wSheet.Name
Next wSheet
Пример 2
Данный фрагмент наглядно показывает, что нужно делать для работы с информацией, содержащейся в различных документах. Кроме этого, он показывает, каким способом можно вызывать объект по умолчанию при отсутствии конкретного объекта, на который бы ссылался разработчик.
‘ Копирование набора ячеек с листа Sheet10 другого документа, названного «Info.xlsx»,
‘ и вставка одержанных итогов в лист «Vyvody» активного документа с названием
‘ «ActWb.xlsm»
Dim ActWb As Workbook
Set ActWb = Workbooks.Open(«C:\Info»)
‘ Не стоит забывать, что ActWb – это активная книга.
‘ Поэтому далее код вызывает объект ‘Sheets’ в этой активной книге.
Sheets(«Sheet10»).Range(«A10:B20»).Copy
‘ Результаты вставляются со скопированного диапазона в лист «Vyvody»
‘ активной книги. Важно, ActWb – это в данный момент не активная книга, поэтому ее нужно указать.
Workbooks(«ActWb»).Sheets(«Vyvody»).Range(«A1»).PasteSpecial Paste:=xlPasteValues
Пример 3
Следующий фрагмент кода демонстрирует способы работы с объектом Columns с текущего объекта Worksheet. Помимо всего прочего, он демонстрирует, как можно опустить ссылки на активный объект, если указать определенную ячейку или их набор внутри него.
Этот пример также демонстрирует применение ключевого слова Set, используемого здесь для присваивания диапазона переменной «Col».
Этот код помимо всего прочего показывает, как редактировать содержимое ячеек.
‘ Цикл через ячейки в колонке A листа «Sheet2»,
‘ выполняет арифметические операции для каждого значения и указывает результат в колонке А текущего активного листа(«Sheet1»)
Dim i As Integer
Dim Col As Range
Dim dVal As Double
‘ Присваивание переменной ‘Col’ колонки A листа 2
Set Col = Sheets(«Sheet2»).Columns(«A»)
i = 1
‘ Цикл через каждую ячейку колонки ‘Col’ до тех пор, пока
‘ не будет обнаружена пустая ячейка
Do Until IsEmpty(Col.Cells(i))
‘ Выполнение арифметических операций со значением в текущей ячейке
dVal = Col.Cells(i).Value * 3 — 1
‘ Эта команда копирует результат в колонку А
‘ текущего активного листа – нет необходимости уточнять, какого именно
Cells(i, 1).Value = dVal
i = i + 1
Loop
Таким образом, объекты дают возможность программным методом выполнять самые разнообразные действия с электронными таблицами. Это может использоваться в разных ситуациях. Например, когда необходимо адаптировать таблицу под нужды конкретной компании, а существующего функционала недостаточно. Также подобные макросы могут использоваться для автоматизации большого количества бухгалтерских операций, которые повторяются каждый период (месяц, квартал или год).
