В модели объектов Excel имеется более 100 объектов. Наиболее часто используемыми объектами Excel являются объекты Application, Workbooks и Workbook, Worksheets и Worksheet, Range, Selection.
Коллекция представляет собой объект, содержащий несколько других объектов, как правило, одного и того же типа.
Объект Workbooks содержит все открытые объекты Workbook (рабочая книга).
Доступ к заданному элементу коллекции осуществляется либо по номеру, либо по имени.
Например, Worksheets(1) обозначает первый рабочий лист активной книги, а Worksheets(“Лист1”) — рабочий лист с именем Лист1.
Методы и Свойства
Примеры: Application.Caption = “Пример”
Полная ссылка на объект состоит из ряда имен вложенных последовательно друг в друга объектов. Их имена в этом ряду разделяются точками. Ряд начинается с объекта Application и заканчивается именем самого объекта.
В неявной ссылке, в отличие от полной, активный в данный момент объект, как правило, можно опускать.
Раздел: табличные процессоры. Заметка 1. Работа с ячейками в MS Excel.
• Если программа выполняется в Excel, то Workbooks(“Архив”).Worksheets(“Продажа”).Range(“A1”)
• Если рабочая книга Архив является активной, то ссылку можно сократить:
Worksheets(“Продажа”).Range(“A1”)
• Если рабочий лист Продажа активен, то Range(“A1”)
Объект Application – это главный (корневой) объект в иерархии объектов Excel, представляет само приложение Excel. Он имеет огромное число свойств и методов, позволяющих установить общие параметры приложения Excel.
Свойства объекта Application
• ActiveWorkbook (активная рабочая книга)
• ActiveSheet (активный рабочий лист)
• ActiveCell (активная ячейка)
• ActiveChart (активная диаграмма)
.Value = “Отчет за Май”
Caption – Возвращает или устанавливает текст из заголовка главного окна Excel. Установка значения свойства равным Empty возвращает заголовок, используемый по умолчанию.
- Application.Caption = “Отчет за 2000 год”
- Application.Caption = Empty
Метод Quit – Закрывает приложение.
После объекта Application в иерархии объектов Excel следует коллекция Workbooks, которая содержит объекты Workbook. Объект Workbook представляет собой рабочую книгу Excel. Все открытые в Excel рабочие книги включены в коллекцию Workbooks.
Свойства коллекции Workbooks и объекта Workbook
• Count — возвращает число объектов коллекции Workbooks.
• Worksheets – возвращает коллекцию всех рабочих листов книги.
• Charts – возвращает коллекцию всех диаграмм книги (которые не внедрены в рабочие листы)
Методы коллекции Workbooks и объекта Workbook
• Activate — активизирует рабочую книгу.
• Add – создает новый объект в коллекции Workbooks.
• Close –закрывает книги.
Обработка событий объекта Workbook
- Перейти в редактор VBA;
- В окошке Project Explorer выбрать объект ЭтаКнига и перейти в окошко кода для этого объекта;
- В верхнем левом списке выбрать объект Workbook;
- В верхнем правом списке выбрать необходимое событие.
Коллекция Worksheets включает в себя множество всех объектов Worksheet (рабочий лист) в рабочей книге. В иерархии Excel объект Worksheets идет сразу после объекта Workbook.
VBA (Использование классов и объектов) ч.1
Свойства коллекции Worksheets и объекта Worksheet
• Name – возвращает или устанавливает имя рабочего листа.
• Visible – возвращает True, если объект – видимый.
• Cells – возвращает коллекцию всех ячеек рабочего листа.
• Columns, Rows – возвращает коллекцию всех столбцов и строк рабочего листа.
Методы коллекции Worksheets и объекта Worksheet
• Activate – активизирует рабочий лист.
• Add – создает новый рабочий лист.
• Delete – удаляет рабочий лист.
• Activate – активизирует рабочий лист.
• Add – создает новый рабочий лист.
• Delete – удаляет рабочий лист.
Обработка событий объекта Worksheet
- Перейти в редактор VBA;
- В окошке Project Explorer выбрать объект Лист1 (или другой лист) и перейти в окошко кода для этого объекта;
- В верхнем левом списке выбрать объект Worksheet;
- В верхнем правом списке выбрать необходимое событие.
События объекта Worksheet
• Activate – при активизации рабочего листа.
• BeforeDoubleClick – при двойном щелчке по рабочему листу.
• Calculate – при пересчете рабочего листа.
• Change – при изменении содержимого ячейки пользователем.
• Deactivate – когда рабочий лист теряет фокус.
• SelectionChange – при изменении выделенного диапазона ячеек.
В иерархии Excel объект Range (диапазон), один из ключевых объектов VBA,следует сразу после объекта Worksheet. Объект Range может представлять собой ячейку, строку, столбец или диапазон ячеек.
Объект Selection – это любые выделенные ячейки на рабочем листе. При работе с объектом Selection можно использовать свойства и методы объекта Range. Для определения объекта Selection можно использовать метод Select.
Задание группы строк и столбцов
- Range(“A:C”) – задает диапазон, состоящий из столбцов A, B и C.
- Range(“2:2”) – задает диапазон состоящий из второй строки.
- Range(“2:5”) – задает диапазон состоящий из 2, 3, 4, 5 строки.
- Rows(2) – задает вторую строку.
- Columns (1) – задает столбец А.
Связь объекта Range и свойства Cells объекта Worksheet
- Range(“A2”) – задает ячейку A2.
- Cells(1,2) – задает ячейку B1.
- Range(“A2:C3”) – задает диапазон ячеек A2:C3.
- Range(“A2:C3, A5:C6”) – задает диапазон ячеек A2:C3 и A5:C6.
- Range(Cells(1,2), Cells(3,3)) – задает диапазон ячеек B1:C3.
- Range(“B2:D4”).Select
Selection.Cells(2,2).Value = 2 – значение 2 вводится в ячейку C3.
Свойства объекта Range
• Value – возвращает или устанавливает значение в ячейках диапазона.
• Interior – возвращает объект, представляющий собой фон ячейки. Свойство ColorIndex этого объекта задает цвет фона (от 1 до 56: 1-черный, 2-белый, 3-красный, 4-зеленый, 5-синий, 6-желтый, 7-фиолетовый).
• Font – возвращает объект, представляющий собой шрифт. Свойства: Name, FontStyle (Regular (обычный), Bold (жирный), Italic (курсив)), Size, ColorIndex.
• Formula – возвращает или устанавливает формулу в формате A1.
• Address – возвращает адрес ячейки.
X = Range(“C1”).Value ‘X — переменная
With Range (“A1:B2”).Font
.Size = 14:.FontStyle = “Bold”:.ColorIndex = 3
End With
• Range(“A1”).Formula = “=$A$4 + $A$10”
Методы объекта Range
• Activate – активизирует ячейку.
• Clear – очищает диапазон.
• Copy – копирует диапазон в другой диапазон или в буфер обмена.
• Delete – удаляет диапазон.
• Select – выделяет диапазон.
Range (“A1:D4”).Copy Worksheets(“Лист2”).Range(“E5”)
Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:
Источник: studopedia.ru
06_Лекция_Основные объекты VBA в Excel
Методы объекта Range, использующие команды Excel Встроенные в Excel команды и методы позволяют эффективно работать с диапазоном: заполнять его элементами по образцу, сортировать, фильтровать и консолидировать данные, строить итоговую таблицу и создавать сценарии, решать нелинейное уравнение с одной переменной. Метод AutoFill Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и
протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность. Синтаксис: объект. AutoFill(диапазон, тип) Аргументы: Диапазон Диапазон, с которого начинается заполнение тип Допустимые
| значения: | xlFillDefault, | xlFillSeries, | xlFillCopy, | xlFillFormats, |
| xlFillValues,xlFillDays, | xlFillWeekdays, | xlFillMonths, | xlFillYears, | |
| xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault | ||||
Метод AutoFilter Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком.
Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks).
Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter). При применении метода AutoFilter допустимы два синтаксиса. Синтаксис 1: Объект. AutoFilter В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект. Синтаксис 2: Объект.
AutoFilter (field, criteria1, operator, criteria2) В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе. Аргументы: field Целое, указывающее поле, в котором производится фильтрация данных Criteria1 Задают два возможных условия фильтрации и criteria2 поля.
Допускается использование строковой постоянной, например 101, и знаков отношений >, =, operator Допустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)
При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.
Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае) Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае) Метод GoalSeek Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения.
Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения. Синтаксис: Объект. GoalSeek(Goal, ChangingCell) Аргументы: Объект Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell
| Goal Значение левой | части | решаемого уравнения, | не | содержащей |
| параметра | ||||
| ChangingCell Ссылка | на | ячейку, отведенную | под | параметр |
(неизвестную величину). Значение, введенное в данную ячейку до активизации метода Goalseek, рассматривается как начальное приближение к искомому корню Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application.
Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией: With Application Maxiterations = 1000 MaxChange = 0.0001 End With
Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).
Метод Sort Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort). Синтаксис: Объект.
Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn) Аргументы: Объект Диапазон, который будет сортироваться Key1 Ссылка на первое упорядочиваемое поле Order1 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок) key2 Ссылка на второе упорядочиваемое поле order2 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок) header Допустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок) orderCustom Пользовательский порядок сортировки. По умолчанию используется Normal matchCase Допустимые значения: True (учитываются регистры) и False
| (регистры не учитываются) | |||
| orientation Допустимые | значения: | xlTopToBottom | (сортировка |
осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам) Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму: Worksheets(«Лист»).Range(«A1: C20»).Sort _ key1:=Worksheets(«Sheet1»).Range(«A1»), _ key2:=Worksheets («Sheet1»).Range («B1»)
Округление чисел Округлять десятичные числа приходится часто, особенно при работе с денежными значениями. VBA не предлагает прямого решения таких задач, но обсуждаемые ниже приемы помогут решить эти проблемы. 1 способ Функция Round Пример: X= round(2.505, 2) Значение х будет 2,5, а не 2,51. Поэтому часто не используется.
2 способ Функция Format Пример: sngОкругление=Format(SngНеокругленное, “#, 0.00”) 3 способ Функция FormatNumber SngОкругление= FormatNumber(sbgНеокругленное, 2) Для изменения знаков после запятой измените число нулей после десятичной точки в аргументе Format, либо измените число, задающее значение второго аргумента, на нужное. Примечание.
Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long. Приведение данных Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых – CDBL. Синтаксис: CDbl(выражение) Обязательный аргумент выражение является любым строковым или числовым выражением.
Для считывания информации, введенной в текстовое поле в созданной форме, вводят переменную и прописывают выражение: А = Cdbl(textBoxN.text) После чего с данной переменной можно работать. Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range: range(«A5»).value = a Функцией, обратной по действию к CDbl, является функция CStr – она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно. TextBoxN.text = CStr(.Range(«A8»).value) – считывание значения с ячейки и вывод его в текстовое окно. Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.
Источник: studfile.net
Объекты Excel
Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.
Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.
В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.
Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.
В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).
Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).
По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(«A1:B10») или Worksheet.Range(«A1», «B10») или Worksheet.Range(Cells(1,1), Cells(10,2)).
Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:
Workbooks(«Книга1»).Worksheets(«Лист1»).Range(«A1:B10»)
Присваивание объекта переменной
В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:
Активный объект
В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.
Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.
Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.
Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:
Range(«A1:B10»)
Смена активного объекта
Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:
Sub ActivateAndSelect() Workbooks(«Книга2»).Activate Worksheets(«Лист2»).Select Worksheets(«Лист2»).Range(«A1:B10»).Select Worksheets(«Лист2»).Range(«A5»).Activate End Sub
Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.
Свойства объектов
Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:
Dim wbName As String wbName = ActiveWorkbook.Name
Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:
Workbooks(«Книга1»).Worksheets(«Лист1»)
Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.
Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на «Мой рабочий лист«, достаточно присвоить это имя свойству Name активного листа, вот так:
ActiveSheet.Name = «Мой рабочий лист»
Методы объектов
Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.
Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:
ActiveWorkbook.Save
Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.
Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем «Книга2», то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:
ActiveWorkbook.SaveAs «Книга2», xlCSV
Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания «:=» и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:
ActiveWorkbook.SaveAs Filename:=»Книга2″, [FileFormat]:=xlCSV
В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.
Рассмотрим несколько примеров
Пример 1
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.
‘Пролистываем поочерёдно все рабочие листы активной рабочей книги ‘и выводим окно сообщения с именем каждого рабочего листа Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox «Найден рабочий лист: » Лист2″, ‘выполняем с каждым из них арифметические операции и записываем результат ‘в столбец A активного рабочего листа (Лист1) Dim i As Integer Dim Col As Range Dim dVal As Double ‘Присваиваем переменной Col столбец A рабочего листа «Лист2» Set Col = Sheets(«Лист2»).Columns(«A») i = 1 ‘Просматриваем последовательно все ячейки столбца Col до тех пор ‘пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) ‘Выполняем арифметические операции со значением текущей ячейки dVal = Col.Cells(i).Value * 3 — 1 ‘Следующая команда записывает результат в столбец A ‘активного листа. Нет необходимости указывать в ссылке имя листа, ‘так как это активный лист рабочей книги. Cells(i, 1).Value = dVal i = i + 1 Loop
Источник: office-guru.ru