Основные принципы и концепции программирования на языке VBA в Excel

Объекты MS Excel

Разбить на страницы
Показывать лекцию целиком

Все офисные приложения можно рассматривать как совокупность объектов. Объектами являются сами приложения Application (центральный или корневой объект) и их компоненты (вложенные объекты).

Примеры объектов: рабочий лист Worksheet, рабочая книга Workbook, диаграмма Chart, рамка Border. Доступ к интервалам ячеек возможен только как к объектам Range, например, объект Range("A1") представляет ячейку A1. Каждый элемент меню, каждая командная кнопка, любой элемент рабочего листа являются объектами MS Excel.

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

В VBA возможны три типичные действия c объектами:

  • проверка свойств объекта;
  • изменение объекта посредством модификации его свойств;
  • выполнение методов объекта.
    •

    Подробно структура объектов, синтаксис свойств и методов, перечень событий рассмотрены в разделе Microsoft Excel Object Model книги под названием Microsoft Excel Visual Basic Reference справочника по VBA (Help).

    Свойства объектов

    Свойства объекта это атрибуты объекта. Каждый объект может иметь десятки свойств, например, объект Worksheet имеет 52 свойства.

    Свойства делятся на две группы:

  • свойства-участники ( accessors ), представляющие вложенные объекты;
  • терминальные свойства ( terminals ), задающие характеристики объекта или его состояние.
    •

    Свойства-участники позволяют добраться до объекта, находящегося на любом уровне вложенности. Например, в записи Application.ActiveWorkbook свойство ActiveWorkbook позволяет получить доступ к объекту приложения - активной рабочей книге, а в записи ActiveWorkbook.ActiveSheet свойство ActiveSheet означает доступ к объекту рабочей книги - активной странице этой книги.

    Изменение значений терминальных свойств - это один из способов изменить внешний объект.

    Свойства имеют статус:

  • Read-Write (далее R/W ) предполагает возможность изменения свойства;
  • Read-Only (далее R/O ) означает, что можно только протестировать значение свойства.
    •

    Некоторые свойства являются общими для многих объектов и для разных объектов могут иметь разный статус, например, Height, Width, являющиеся свойствами интервалов, окон и приложения. В дальнейшем указывается статус и тип значения свойства.

    В качестве значений свойств могут использоваться константы с префиксом xl, например, константа xlCalculationManual устанавливает ручной пересчет таблицы.

    Примеры часто используемых свойств объектов
    Свойство Объект Примеры Описание
    Bold, Italic (R/W Boolean) Font ActiveCell.Font.Bold=True

    ActiveCell.Font. Italic =False

    		 Устанавливает полужирный шрифт. Отменяет курсив.
    Column, Row (R/W Long) Range Debug.Print

    Range("B3:C5").Column,

    Range("B3:C5").Row

    		 В окне Immediate будут распечатаны номер первой колонки и номер первой строки интервала ячеек B3:C5 - "2 3"
    ColumnWidth (R/W Variant) Range Range("A1:B5").ColumnWidth=15 Ширина каждой колонки объекта Range 15 символов
    Height, Width (Double) Многие объекты Application.Width=200 (статус R/W)

    W=Range("A1:B5"). Height (статус R/O)

    		 Ширина окна приложения 200 пт.

    Возвращает суммарную высоту строк объекта Range в пунктах
    RowHeight (R/W Variant) Range Range("A1:B5").RowHeight=15 Устанавливает высоту каждой строки объекта Range в пунктах
    Formula (R/W Variant) Range Range("A2").Formula = "=pi()*A1^2" В ячейку А2 записывается формула
    Value (R/W Variant) Range Range("A3").Value=6.28 Значение ячейки устанавливается равным 6,28
    Count (R/O Long) Группа объектов N=Sheets.Count В переменную N записывается количество элементов коллекции объектов
    Name (String) Многие объекты ActiveSheet.Name="Nw_Sh"(статус R/W)

    Wb =ActiveWorkbook.Name(статус R/O)

    		 Активному листу присваивается новое имя.

    Переменной Wb присваивается имя активной рабочей книги.
    Parent (R/O Object) Многие объекты P_t= Range("A1:B5").Parent

    для объекта Range возвращает объект Sheet - рабочий лист, на котором объект Range расположен

    		 Возвращает объект обычно другого типа, который является объектом более высокого уровня по отношению к указанному объекту

    Свойства объектов изменяются при помощи оператора присваивания или под влиянием методов.

    Синтаксис операторов присваивания object.property=expression

  • object - ссылка на объект, над которым совершается действие;
  • property - название свойства, значение которого необходимо изменить;
  • expression - выражение, представляющее новое значение свойства объекта.
    •

    Важно

  • Каждое свойство может принимать значения только определенного типа.
  • Тип результата вычисления выражения должен соответствовать типу свойства, т.е, если свойство является числовым, то и результат вычисления выражения должен быть числом или должен преобразовываться в число.
    •

    Например, оператор ActiveCell.Font. Bold="b" является ошибочным, так как свойство Bold имеет тип Boolean и может принимать значения только True или False.

    Пример

    Процедура изменяет размеры активного окна приложения. Ширина и высота окна приложения вводятся в диалоге. Свойства Height и Width для объекта Window имеют статус R/W, но эти свойства нельзя изменять, если размер окна минимизирован или максимизирован. Поэтому первоначально в процедуре свойством WindowState устанавливается обычный размер окна

    (рис 8.1) Процедура изменяет размеры активного окна приложения

    При помощи оператора присваивания можно сохранить значение свойства в переменной. Значение свойства может использоваться как часть условного выражения. В таких случаях говорят о возврате значения свойства.

    Синтаксис оператора присваивания, возвращающего значение свойства

    variable=object.property
  • variable - переменная или свойство некоторого объекта;
  • object - ссылка на объект, свойство которого запоминается или тестируется;
  • property - название свойства, значение которого необходимо получить.
    •

    Важно

  • Тип переменной должен соответствовать типу значения свойства.
    •

    Примеры

  • Распечатать название рабочего листа c активной ячейкой.

    Свойство Parent возвращает рабочий лист, на котором расположена активная ячейка.

    (рис 8.2) Процедура распечатки названия рабочего листа c активной ячейкой
  • В зависимости от знака числа, хранящегося в ячейке, залить ячейку некоторым цветом.

    В процедуре тестируется свойство Value объекта Range - ячейки A1. В случае отрицательного числа цвет заливки ячейки - синий.

    (рис 8.3) Процедура тестировния свойства Value объекта RangeПри нулевом значении заливка ячейки отменяется (константа xlNone ). При положительном значении устанавливается цвет заливки, предусмотренный по умолчанию (константа xlAutomatic ).
    •

    Методы объектов

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

    Важно

  • Методы - это функции или подпрограммы.
  • Подобно процедурам методы могут принимать аргументы.
  • Функции VBA и методы Application могут иметь одинаковые имена, но различные аргументы, например, функция InputBox класса Interaction и метод InputBox класса Application.
    •

    Синтаксис вызова метода без аргументов

    object.method

    например, ActiveCell.Justify.

    Вызов метода с аргументами имеет две формы:

  • variable=object.method(arguments) - функциональная форма вызова (аргументы указываются в скобках после названия метода).
  • object.method arguments - операторная форма вызова (аргументы записываются через пробел после названия метода).
    •

    Если метод использует несколько аргументов, то они перечисляются через запятую.

    Аргументы можно задавать, используя позиционное или произвольное расположение.

    ЗАПОМНИТЕ

  • Функциональная форма вызова метода используется, если необходимо сохранять значение, возвращаемое методом.
  • Операторная форма вызова метода используется, если не нужно сохранять возвращаемое методом значение.
    •

    Каждый объект имеет свои собственные методы. Некоторые методы являются уникальными, а другие встречаются у нескольких объектов. Например, метод Delete может удалять графический объект и рабочий лист.

    Модель объектов

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

    (рис 8.4) Модель объектов MS Excel (фрагмент)

    Нажатие на выбранный объект отображает на экране статью, посвященную объекту, в которой ниже имени объекта, как правило, расположены три гиперссылки, позволяющие просмотреть свойства ( Properties ), методы ( Methods ) и события ( Events ) выбранного объекта с соответствующими примерами. В дополнение можно раскрыть список рекомендуемых для просмотра объектов ( See Also ). Нажатие на Multiple objects показывает перечень исходных объектов или перечень вложенных объектов.

    (рис 8.5) Фрагмент статьи, посвященной объекту Workbook

    Объекты приложения связаны между собой, и модель объектов отражает иерархические связи между объектами.

    Модель объектов содержит простые объекты и коллекции объектов. Коллекция объектов ( Collection ) объединяет группу подобных объектов.

    Коллекции объектов

    Коллекция объектов - это объект специального типа, существующий для управления объектами группы. Например, Workbooks является коллекцией всех открытых книг - объектов Workbook, а Worksheets - коллекцией рабочих листов некоторой рабочей книги - объектов Worksheet. Примерно половина всех объектов MS Excel - это коллекции объектов.

    Процедуры могут обращаться как к отдельному элементу коллекции (к объекту Workbook или к объекту Worksheet ), так и ко всем объектам коллекции одновременно (к объекту Workbooks или к объекту Worksheets ). Коллекция объектов и объекты этой коллекции обладают различными свойствами и методами.

    Коллекция объектов - это упорядоченная совокупность объектов. Для доступа к конкретному объекту в коллекции можно использовать его имя или порядковый номер в коллекции, например, Workbooks(1) указывает на первую рабочую книгу. Запись Worksheets("Sheet2") указывает на лист с именем Sheet2.

    Обращение к объекту

    Контейнеры

    Объекты приложения могут включать в себя объекты разных типов. Например, рабочая книга может содержать один или несколько рабочих листов и листов диаграмм. Рабочий лист, в свою очередь, содержит строки, колонки и интервалы ячеек. Когда объект одного типа находится внутри объекта другого типа, говорят о контейнере ( container ), в котором содержится объект.

    Самый старший контейнер объектов MS Excel - это приложение Application. Приложение - это контейнер для всех открытых рабочих книг, и в то же время приложение содержит такой глобальный объект, как строка меню, который доступен любой рабочей книге. Рабочий лист представляет пример того, что объект может быть частью нескольких контейнеров или коллекций одновременно: он входит в рабочую книгу, с одной стороны, а с другой стороны - является частью коллекции Sheets и коллекции Worksheets.

    Преимущества

  • Рассмотрение объекта в качестве контейнера позволяет уточнить, сославшись на контейнер, с каким именно объектом производится действие в процедуре.
    •

    Если в рабочей книге имеются два рабочих листа Sheet1 и Sheet2, то запись Worksheets("Sheet1").Range("A1") указывает на ячейку A1 рабочего листа Sheet1, а запись Worksheets("Sheet2").Range("A1") указывает на ячейку A1 рабочего листа Sheet2.

    Ссылка на объект

    Объект в VBA указывается при помощи ссылки. Запись Workbooks("cross").Worksheets("Sheet2") указывает на объект, являющийся листом с именем Sheet2 в рабочей книге cross, отличая его, таким образом, от листа с тем же именем, но в другой рабочей книге.

    Важно

  • Ссылка на объект состоит из имени контейнера и названия свойства-участника.
  • Ссылка на объект может состоять из нескольких спецификаторов (имен контейнеров), отделенных друг от друга точкой. Такая запись отражает иерархию объектов и позволяет точно указать конкретный объект.
  • Свойства и методы объекта указываются через точку после ссылки на объект.
    •

    Для объектов, относящихся к классу globals (например, активная рабочая книга, активная ячейка рабочего листа, константа), спецификатор Application можно опустить.

    Оператор With

    В VBA перед обращением к каждому из методов или свойств объекта требуется наличие ссылки на объект. Конструкция With…End With позволяет применить последовательность операторов к объекту, указав его имя только один раз в операторе With. Благодаря этому программа становится менее громоздкой, освобождаясь от повторений ссылки на объект.

    Синтаксис оператора

    With Object
[statements]
End With
  • Object - имя объекта;
  • statements - последовательность операторов.
    •

    Первая строка этой структуры идентифицирует объект, с которым будут производиться действия. В последующих операторах используются свойства и методы идентифицированного объекта. Оператор End With является закрывающей скобкой для оператора With. Часто подобная структура записывается при помощи макрорекордера.

    Внимание

  • Каждый оператор внутри блока statements начинается с точки.
    •

    Использование объектных переменных

    Чтобы получить доступ к свойствам или методам объекта, можно использовать два способа: прямое указание на объект и применение объектной переменной. Объектная переменная занимает 4 байта и хранит адрес, указывающий на объект.

    Например, установить полужирный шрифт для первой строки активного листа можно оператором ActiveSheet.Rows(1).Font.Bold = True или, используя объектную переменную, можно предложить два способа записи:

    Здесь оператор Set создает объект, тип которого установлен при описании объектной переменной. Далее можно обратиться к свойствам или методам созданного объекта. В первом случае это объект типа Range, во втором случае - тип объекта Font.

    Преимущества

  • Использование объектных переменных улучшает читабельность программы.
  • Использование объектных переменных уменьшает время выполнения программы, т.к. при каждом выполнении оператора для обращения к объекту не придется проходить полную цепочку иерархии объектов.
    •

    Объект Application

    При открытии MS Excel автоматически становится доступным объект Application с его свойствами и методами. Объект Application - корневой объект приложения. В него вложены остальные объекты приложения. Доступ к ним осуществляется посредством свойств-участников объекта Application.

    Для создания ссылки на объект Application используется свойство Application.

    Примеры операторов
    Application.Windows("AIR.XLS ").Activate Оператор активизирует рабочую книгу
    Application.Goto Range("B3:C5") Оператор выделяет интервал ячеек на активном рабочем листе

    Многие свойства объекта Application используются без ссылки на объект Application, так как они возвращают объекты, относящиеся к классу globals.

    Активные объекты

    Свойства, название которых начинается со слова Active, возвращают активный объект соответствующего типа. Для этих свойств необязательно указывать ссылку на объект Application, так как они входят в класс globals. Некоторые из этих свойств являются одновременно свойствами нескольких объектов.

    Свойства, возвращающие активный или выделенный объект
    Свойство Объект Действие Возвращаемый объект
    ActiveCell Application, Window Возвращает активную ячейку Range
    ActiveSheet Application, Window, Workbook Возвращает активный лист. Это может быть рабочий лист, лист диаграмм Sheet
    ActiveWorkbook Application Возвращает активную рабочую книгу Workbook
    ActiveWindow Application Возвращает активное окно Window
    Selection Application, Window Возвращает выделенный объект Различные типы объектов

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

    Примеры
    Оператор Комментарий
    ActiveCell.Font.Bold=True Устанавливает полужирный шрифт текста активной ячейки
    ActiveSheet.Name="Проба" Изменяет название активного листа рабочей книги
    MsgBox ActiveWorkbook.Fullname Высвечивает полное имя рабочей книги, включая путь и имя файла
    Selection.NumberFormat="0.00" Устанавливает числовой формат с двумя знаками после запятой для ячеек выделенного интервала
    ActiveCell.Value=10

    Application.ActiveCell.Value=10

    ActiveWindow.ActiveCell.Value=10

    Application.ActiveWindow.ActiveCell.Value=10

    		 Присваивают активной ячейке значение 10. Приведенные примеры доступа к активной ячейке равносильны
    Worksheets("Sheet1").Activate

    Selection.Clear

    		 Очищает предварительно выделенный на листе Sheet1 объект, например, интервал ячеек
    Если никакой объект не выделен, то свойство Selection возвращает значение Nothing и очистка не выполняется
    Worksheets("Sheet1").Activate

    MsgBox "Тип объекта " TypeName(Selection)

    		 Высвечивает тип предварительно выделенного на листе Sheet1 объекта

    Свойства, влияющие на высвечивание на экране

    Свойство DisplayAlerts (R/W Boolean)

    Во время выполнения программ возможно высвечивание сообщений и запросов MS Excel (встроенных диалоговых окон), на которые пользователь должен реагировать. Например, это может быть запрос на сохранение изменений при закрытии рабочей книги. Значение False свойства DisplayAlerts позволяет отключить высвечивание подобных сообщений. При отключении запросов MS Excel выбирает ответ, который установлен в диалоге по умолчанию.

    Важно

  • Не забывайте возвращать первоначальное значение этого свойства, равное True, так как MS Excel автоматически его не восстанавливает.
    •
    Пример
    Процедура закрывает рабочую книгу AIR.XLS без сохранения изменений, при этом запрос на сохранение изменений не возникает.

    Свойство ScreenUpdating (R/W Boolean)

    Это свойство обновления экрана разрешает (значение True ) или запрещает (значение False ) изменение экрана при выполнении команд MS Excel.

    Если процедура выполняет команды MS Excel, то на экране отображаются все действия с объектами MS Excel: выделение и копирование ячеек, перемещение экрана и т.д. Свойство ScreenUpdating позволяет избежать подобного "мелькания экрана".

    Важно

  • Выключение обновления экрана ускоряет выполнение макропроцедур.
  • Не забывайте возвращать первоначальное значение этого свойства, равное True, так как MS Excel автоматически его не восстанавливает.
    •

    Свойство Visible (R/W Boolean)

    Это свойство позволяет скрывать или показывать окно приложения. Свойство доступно для многих объектов, например, для объектов формы.

    Пример
    Процедура делает видимым окно приложения, если оно не видно или скрывает его, если окно высвечено.

    Другие свойства объекта Application

    Свойства Описание Примеры операторов
    Calculation (R/W) Возвращает или устанавливает параметры вычислений. Задается константами MS Excel Application.Calculation=xlCalculateManual задает ручной пересчет
    Application.CalculateBeforeSave=True задает автоматический пересчет перед сохранением файла
    Path (R/O String) Возвращает полный путь к объекту MsgBox "Путь к приложению MS Excel " Application.Path высвечивает путь к программе MS Excel
    SheetsInNewWorkbook (R/W Long) Возвращает или устанавливает количество рабочих листов во вновь создаваемой рабочей книге MsgBox "В новой рабочей книге " Application.SheetsInNewWorkbook " рабочих листов" высветит количество листов во вновь создаваемой рабочей книге
    ThisWorkbook Возвращает объект ThisWorkbook - рабочую книгу, в которой находится выполняемая процедура.

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

    		 ThisWorkbook.Close SaveChanges:=False закрывает рабочую книгу, содержащую исполняемый код
    ThisWorkbook.FullName возвращает полный путь к рабочей книге, содержащей исполняемый код 
    For Each w In Workbooks
  If w.Name <> ThisWorkbook.Name Then
    w.Close savechanges:=True
  End If
Next w
    Цикл закрывает с сохранением изменений все открытые рабочие книги за исключением той, в которой находится исполняемая процедура
    WorksheetFunction Возвращает одноименный объект-контейнер функций рабочего листа MsgBox "Десятичный логарифм 10^6 равен " WorksheetFunction.Log10(10^6) высвечивает 6

    Важно

  • Вызов функции рабочего листа производится со ссылкой на контейнер WorksheetFunction или на объект Application.
  • Названия функции VBA и функции рабочего листа, выполняющих одинаковые действия, могут не совпадать, например, функция Instr и функция Find.
    •

    Методы

    Метод OnTime

    Метод позволяет запустить некоторую процедуру в заданный момент времени.

    Синтаксис OnTime(EarliestTime, Procedure[,LatestTime])

  • EarliestTime - выражение, задающее время запуска процедуры;
  • Procedure - имя запускаемой процедуры;
  • LatestTime - самое позднее время, когда процедура может быть запущена, если невозможно было запустить ее точно в указанное время. Причиной невозможности запуска могло явиться выполнение диалога, который не был прерван ранее.
    •

    Пример

    (рис 8.6) Пример применения метода OnTime

    Процедура mes_time в 16:30 высвечивает сообщение "Пошли пить кофе".

    Внимание

  • При указании, например, LatestTime=EarliestTime + 30, MS Excel подождет 30 секунд и, если выполняемая процедура не завершится, то процедура, указанная в OnTime, не будет запущена вовсе.
  • Если параметр LatestTime не задавать, то MS Excel дождется завершения процедуры и запустит нужную процедуру.
  • Если в качестве EarliestTime использовать выражение Now()+интервал времени, то процедура запустится спустя указанный интервал от текущего времени.
    •

    Метод Wait

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

    Синтаксис expression.Wait(Time)

  • expression - возвращает объект Application ;
  • Time - время окончания паузы в выполнении процедуры в формате даты.
    •

    Пример

    Процедура устанавливает паузу примерно на 10 секунд.

    Используются функции даты Hour, Minute, Second, TimeSerial, Now. С их помощью из текущего времени выделяются час, минуты, секунды; секунды увеличиваются на 10, и составляется время окончания паузы.

    (рис 8.7) Пример применения метода Wait

    Коллекции объектов

    Ссылка на объект коллекции - это название коллекции, после которого в скобках указывается индекс объекта или его имя в кавычках. Например, ссылка Workbooks(1) выбирает первую из открытых рабочих книг, а Workbooks("budget") ссылается на рабочую книгу с именем "budget".

    Важно

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

    Объекты Workbooks и Workbook

    Документ MS Excel (рабочая книга) это объект Workbook. Можно одновременно работать с несколькими рабочими книгами. Открытые рабочие книги составляют коллекцию рабочих книг - Workbooks.

    Свойство Workbooks объекта Application возвращает объект Workbooks.

    При открытии или создании рабочей книги элемент Workbook автоматически добавляется в конец коллекции Workbooks, а при закрытии книги соответствующий элемент также автоматически удаляется из коллекции.

    Некоторые свойства и методы объектов Workbooks и Workbook
    Свойства и методы Примеры операторов и комментарии
    Объект Workbooks
    Свойство Count (R/O Long) MsgBox "Число открытых рабочих книг " Workbooks.Count высвечивает число рабочих книг в коллекции
    Метод Add Workbooks.Add добавляет новую рабочую книгу в коллекцию
    Метод Close Workbooks.Close используется без аргументов и закрывает все рабочие книги
    Объект Workbook
    Свойство Colors Свойство, заданное с индексом, указывает на конкретный элемент палитры. ActiveWorkbook.Colors(5) = RGB(255,0,0) заменяет пятый цвет палитры на красный
    Свойство без индекса возвращает палитру цветов в виде массива из 56 цветов.

    ActiveWorkbook.Colors = Workbooks("AIR.XLS").Colors заменяет палитру активной книги на палитру цветов книги AIR.XLS.
    Свойство Name (R/O String) MsgBox Workbooks(Workbooks.Count).Name высвечивает имя последней открытой книги
    Свойство FullName (R/O String) MsgBox ActiveWorkbook.FullName возвращает полное имя активной рабочей книги, включая путь к ней
    Свойство Sheets ThisWorkbook.Sheets.Count возвращает количество элементов в коллекции листов различных типов рабочей книги, содержащей выполняемый код
    Свойство Charts ActiveWorkbook.Charts(1).Name возвращает имя первого листа в коллекции диаграммных листов активной книги
    Свойство Worksheets Workbooks(1).Worksheets(1).Activate активизирует первый лист из коллекции рабочих листов
    Метод Open Workbooks.Open "AIR.xls" открывает существующую рабочую книгу AIR.xls
    Метод Close ActiveWorkbook.Close SaveChanges:=True, Filename="AIR" закрывает рабочую книгу. Книга удаляется из коллекции, и элементы коллекции Workbooks перенумеровываются.

    Параметр SaveChanges сохраняет или отменяет сделанные изменения. Параметр Filename задает название новой рабочей книги
    Метод Activate Workbooks("AIR.XLS").Activate активизирует указанную рабочую книгу
    Метод SaveAs ActiveWorkbook.SaveAs FileName:="d:\bel_acc\first_book" сохраняет рабочую книгу под именем Filename. Если в Filename папка не указана, то файл сохраняется в текущей папке

    Событийные процедуры

    Событийные процедуры записываются на процедурном листе, связанном с объектом. Каждый объект имеет свои собственные события.

    Чтобы вставить событийную процедуру для объекта Workbook

  • выделите объект ThisWorkbook (Эта книга) в окне проекта;
  • перейдите на лист процедур, нажав клавишу F7. Можно выполнить команду View Code или сделать двойной щелчок на объект ThisWorkbook ;
  • на процедурном листе в окне выбора объектов (вверху слева) выберите объект Workbook ;
  • в окне выбора событий (вверху справа) выберите событие. Автоматически вставляется процедура со стандартным именем, которое состоит из названия объекта и названия события, разделенных нижним подчеркиванием (_), например, для события Open событийная процедура имеет имя Workbook_Open ;
  • запишите текст процедуры.
    •

    Пример

    При вставке нового листа в рабочую книгу процедура запрашивает имя нового листа и вставляет лист в начало рабочей книги.

    При выборе события NewSheet автоматически появляется новая процедура Workbook_NewSheet с параметром Sh.

    Значение параметра, являющееся ссылкой на объект - новый лист, передается процедуре во время ее выполнения. Метод Move перемещает вставленный лист. Параметр before этого метода определяет новое месторасположение листа - начало рабочей книги.

    Объекты Sheets, WorkSheets и WorkSheet

    Коллекция Sheets представляет собой совокупность листов различных типов - рабочих листов (коллекция Worksheets ) и листов диаграмм (коллекция Charts ). Таким образом, каждый элемент коллекции Sheets является элементом коллекции WorkSheets или коллекции Charts и наоборот, любой элемент коллекции WorkSheets или коллекции Charts принадлежит коллекции Sheets.

    Некоторые свойства и методы объектов Sheets, WorkSheets и WorkSheet
    Свойства и методы Примеры и комментарии
    Объекты Sheets, WorkSheets
    Свойство Count (R/O Long) MsgBox "Количество рабочих листов в активной книге " ActiveWorkbook.WorkSheets.Count высвечивает количество рабочих листов в рабочей книге
    Метод Add Sheets.Add, WorkSheets.Add добавляет новый лист заданного типа в рабочую книгу
    Объекты Sheets, WorkSheets, Sheet, WorkSheet
    Методы Copy, Move Копирует, перемещает указанные листы или группу листов в новое место. Worksheets(1).Move after:=Worksheets(Worksheets.Count) перемещает первый лист в конец рабочей книги
    Объекты Sheet, WorkSheet
    Метод Activate WorkSheets("January").Activate активизирует указанный рабочий лист
    Метод Delete ActiveWorkbook.Worksheets(1).Delete удаляет первый рабочий лист
    Свойство Name (R/W String) Возвращает или устанавливает имя листа. WorkSheets(WorkSheets.Count).Name ="LastSheet" переименовывает последний рабочий лист
    Объекты WorkSheet
    Свойство Columns (R/O) Возвращает коллекцию столбцов. Worksheets(1).Columns(1).Font.Bold = True устанавливает полужирный шрифт для первой колонки первого рабочего листа
    Свойство ScrollArea (R/W String) Определяет границы интервала, внутри которого возможно перемещение по ячейкам. При установке значения "пустая строка" доступны все ячейки рабочего листа. Worksheets(1).ScrollArea = "A1:F10" разрешает доступ только к ячейкам A1:F10
    Свойство Shapes (R/O) Возвращает коллекцию Shapes - коллекцию графических объектов рабочего листа: рисунки, автофигуры и т.д. ActiveSheet.Shapes(1).AutoShapeType = 21 меняет тип первого графического объекта активного листа на "сердечко"
    Свойство Rows(R/O) Возвращает коллекцию строк. Worksheets("Sheet1").Rows(3).Delete удаляет третью строку
    Метод Calculate ActiveWorksheet.Calculate производит вычисления во всех ячейках указанного рабочего листа
    Метод CheckSpelling Используется для проверки правописания (с аргуменами и без аргументов). ActiveSheet.CheckSpelling ignoreUppercase:= True не проверяет слова, записанные только прописными буквами

    Методы

    Метод Add

    Добавляет новый лист в коллекцию Sheets, WorkSheets. При создании рабочей книги коллекция WorkSheets содержит столько рабочих листов, сколько определено свойством SheetsInNewWorkbook объекта Application.

    Внимание

  • Метод Add для объектов Workbooks и Sheets имеет различный синтаксис.
    •

    Cинтаксис метода для коллекций Sheets, WorkSheets

    expression.Add([Before] [,After] [,Count] [,Type])
  • expression - выражение, возвращающее коллекцию WorkSheets или Sheets. Указание обязательно;
  • Возможно задание только одного из двух параметров Before или After - специфицирует лист, перед которым вставляется новый лист;
  • Возможно задание только одного из двух параметров Before или After - специфицирует лист, после которого вставляется новый лист;
  • Count - количество вставляемых листов;
  • Type - тип вставляемого листа. Используются константы: xlWorksheet (по умолчанию), xlChart (только для объекта Sheets ), xlExcel4MacroSheet, xlExcel4IntlMacroSheet.
    •

    Важно

  • При отсутствии всех параметров один рабочий лист добавляется перед активным листом.
  • При задании параметров Before и After указывается ссылка на лист как индекс или имя в коллекции листов, например, Sheets(1) или Sheets("Лист1")
    •

    Методы Move и Select

    Метод Move используется для перемещения листов.

    Синтаксис expression.Move([Before] [,After])

  • expression - ссылка на объект, представляющий перемещаемый лист. Указание обязательно;
  • необязательные параметры before и after (ссылки на лист, см. описание метода Add ) определяют новое местоположение перемещаемого листа. Если не указан ни один из параметров, то лист перемещается во вновь создаваемую рабочую книгу.
    •

    Метод Select выделяет объект. При применении к одному листу методы Activate и Select активизируют указанный лист. Но метод Select используется для группировки листов, т.е. для расширения выделения.

    Синтаксис expression.Select([Replace])

  • expression - ссылка на объект, представляющий выделяемый лист. Указание обязательно;
  • Replace - для расширения выделения аргумент устанавливается в False. Если аргумент не задан или принимает значение True, то вместо старой области выделения создается новая область выделения. Необязательный параметр.
    •

    Замечание

  • Для выделения листов с конкретными именами используйте функцию Array. Например, Sheets(Array("Лист8", "Лист12")).Select.
    •

    Пример

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

    Событийные процедуры

    Чтобы вставить событийную процедуру для объекта WorkSheet:

  • выделите объект WorkSheet (например, Лист1 ) в окне проекта;
  • перейдите на лист процедур этого объекта;
  • на процедурном листе в окне объектов (вверху слева) выберите объект WorkSheet ;
  • в окне выбора событий (вверху справа) выберите событие;
  • запишите текст процедуры.
    •

    При выборе события автоматически вставляется процедура со стандартным именем, которое состоит из названия листа и названия события, разделенных нижним подчеркиванием (_).

    Пример

    При активизации листа Лист1 в ячейку A1 заноситcя название листа.

    (рис 8.8) Пример работы с событийной процедурой объекта WorkSheet

    Объект Range

    При работе в MS Excel чаще всего выполняются некоторые действия с группой ячеек рабочего листа. Объект Range - это отдельная ячейка, целиком строка или столбец рабочего листа, выделенный интервал ячеек, непрерывный интервал ячеек или интервал несмежных ячеек.

    Для задания объекта Range существуют различные возможности. Например, благодаря свойству ActiveCell, активная ячейка представляется в качестве объекта Range. Свойство Selection определяет выделенный интервал ячеек в качестве объекта Range.

    Свойства и методы, возвращающие объект Range
    Свойства и методы Применимы к объектам Примеры и комментарии
    Свойство ActiveCell Application Оператор ActiveCell.Value=10 устанавливает значение активной ячейки равным 10
    Свойство Areas Range Оператор Range("A1, B5:B10, C12:C20").Areas(3).Value = 10 устанавливает значение 10 для третьей области объекта Range - для ячеек интервала C12:C20
    Свойство Cells Application, Range, Worksheet Оператор Cells(7,3).Select активизирует ячейку C7 и равносилен оператору Range("C7").Select
    Свойство Columns Application, Range, Worksheet Оператор Columns("A:D").Select выделяет первые четыре столбца
    Свойство CurrentRegion Range Оператор ActiveCell.CurrentRegion.Count подсчитывает количество ячеек с данными в интервале, окружающем активную ячейку
    Свойство Offset Range Операторы Range ("A2:B10").Select, Selection.Offset(2,2).Value=10 устанавливают значение 10 каждой ячейки интервала C4:D12.

    Равносильно записи Range("C4:D12").Value=10
    Свойство Range Application, Range, Worksheet Операторы p=Range("A:B").Count, p=Range("налог").Count, p=ActiveSheet.Range("A1:A10").Count, p=Range("1:3").Count, p=Range("A1:C2, B10:D24").Count присваивают переменной p количество ячеек в заданных интервалах
    Свойство Rows Application, Range, Worksheet Оператор Rows("1:3").Select выделяет первые три строки
    Свойство Selection Application Оператор Selection.Clear очищает выделенный интервал ячеек
    Метод Union Range Union(Range("A1:C5"), Range("B10:D12") объединяет два несмежных интервала в один объект Range

    ЗАМЕЧАНИЯ

  • Все перечисленные свойства возвращают объект Range, не активизируя новую ячейку.
  • Ячейка остается активной до тех пор, пока методы Activate или Select не активизируют новую ячейку.
    •

    Свойства

    Cвойство Range

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

    Первый способ object.Range(Cell1)

    Второй способ object.Range(Cell1 [,Cell2])

  • object - ссылка на объект, например, на рабочий лист или на интервал ячеек. Ссылка необязательна. По умолчанию используется активный лист;
  • Cell1, Cell2 - аргументы для задания интервала ячеек. Cell1 - указание обязательно при обоих способах записи свойства Range.
    •

    Первый способ

    Аргумент Cell1 задает интервал ячеек произвольного размера.

    Важно

  • Могут использоваться имена, определенные в таблице, или координаты ячеек, столбцов, строк или интервалов.
  • Координаты задаются в стиле A1.
  • Координаты и имена заключаются в кавычки.
  • При задании интервалов координаты левого верхнего угла и правого нижнего угла интервала разделяются двоеточием.
  • Для задания несмежных интервалов используется запятая.
  • Для задания пересечения интервалов используется пробел.
    •
    Примеры записи оператора Range (1 способ)
    Запись Возвращаемый объект
    ActiveSheet.Range("A1:A10") интервал ячеек A1:A10 на активном листе
    Range("A:B") столбцы A:B
    Range("налог") интервал с именем налог
    Range("1:3") строки с первой по третью
    Range("A1:C2, B10:D24") объединение двух несмежных интервалов A1:C2 и B10:D24
    Range("A1:C10 B10:D24") пересечение двух интервалов A1:C10 и B10:D24, т.е. интервал B10:C10

    Второй способ

    Аргументы задают координаты интервала:

  • Cell1 - единственная ячейка (строка или столбец), задающая левый верхний угол интервала;
  • Cell2 - единственная ячейка (строка или столбец), задающая правый нижний угол интервала. Необязательный аргумент.
    •

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

    Примеры записи оператора Range (2 способ)
    Запись Возвращаемый объект
    Range("A5","D18") интервал A5:D18
    Range(Columns(1), Columns(5)) интервал, содержащий первые пять столбцов рабочего листа

    ЗАПОМНИТЕ

  • Если свойство Range применяется к объекту Range, то ссылка на интервал ячеек считается относительной и возвращается смещенный объект Range.
    •

    Например, если выделен интервал C1:D5, то запись Selection.Range("B2") возвратит ячейку D2.

    Свойство Cells

    Свойство Cells возвращает единственную ячейку рабочего листа, которая находится на пересечении строки и столбца, задаваемых целыми числами.

    Синтаксис object.Cells (RowIndex,ColumnIndex)

  • object - ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
  • RowIndex - индекс строки;
  • ColumnIndex - индекс столбца.
    •

    ЗАМЕЧАНИЯ

  • В свойстве Cells индекс строки является первым аргументом, а индекс столбца - вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка.
  • Понятие "индекс" ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число.
    •
    Примеры записи свойства Cells
    Запись Комментарий Возвращаемый объект
    ActiveSheet.Cells Свойство Cells без аргументов все ячейки активного рабочего листа
    Range("C5:C10").Cells(1,1) Свойство Cells применяется к объекту Range (относительная ссылка) ячейка C5
    Range(Cells(7,3),Cells(10,4)) Свойство Cells используется в качестве аргументов свойства Range интервал ячеек C7:D10

    Свойство Offset

    Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range("A5").Offset(-2,1) возвращает ячейку B3.

    Синтаксис object.Offset([RowOffset][,ColumnOffset])

  • object - ссылка на объект Range. Ссылка обязательна и определяет объект, относительно которого задается смещение;
  • RowOffset - смещение строки искомой ячейки относительно исходной ячейки;
  • ColumnOffset - смещение столбца искомой ячейки относительно исходной ячейки.
    •

    Необязательные аргументы RowOffset и ColumnOffset - числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю.

    Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7.

    Метод Union и свойство Areas

    Метод Union используется для объединения двух и более объектов Range, заданных ссылками на непересекающиеся интервалы, в один объект Range.

    Синтаксис Object.Union (arg1,arg2,...)

  • object - всегда объект Application. Ссылка необязательна;
  • arg1,arg2 - интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов.
    •

    Например, оператор Union(Range("A1:C5"),Range("B10:D12")).Select выделяет несмежные интервалы A1:C5 и B10:D12.

    Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range.

    Синтаксис Object.Areas(index)

  • object - ссылка на объект Range, состоящий из нескольких интервалов;
  • index - номер интервала в объекте. Аргумент необязателен.
    •
    Примеры
    Оператор Комментарий Результат
    p=Union (Range("A1:C5"), Range("B10:D12")).Areas(2).Count Если аргумент задан, то свойство Areas возвращает интервал - объект Range, определенный индексом интервала равен девяти, так как во втором интервале ровно 9 ячеек
    p=Union(Range("A1:C5"), Range("B10:D12")).Areas.Count Cвойство Areas без аргументов рассматривает каждый из несмежных интервалов как элемент коллекции объектов Range равен двум, так как объект, определенный методом Union, состоит из двух областей - коллекции из двух элементов
    p=Range("B10:D12").Areas.Count равен единице, так как объект Range представляет один элемент коллекции

    Свойства Column и Row (R/O Integer)

    Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств

    object.Column 
object Row
  • object - обязательная ссылка на объект Range.
    •

    Например, запись Range("C5").Column возвращает число 3, а запись Range("C5").Row возвращает число 5.

    Свойства Columns и Rows

    Свойство Columns (не путайте со свойством Column!) возвращает объект Range, представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено.

    Синтаксис Object.Columns(index)

  • object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист;
  • index - индекс колонки в объекте.
    •

    Например, запись Columns(1) возвращает колонку A активного рабочего листа, а запись Range("C1:D5").Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5.

    Важно

  • Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range.
  • Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns("B") указывают на одну и ту же колонку B.
    •

    Свойство Rows (не путайте со свойством Row!) возвращает объект Range, представляющий строку или коллекцию строк в объекте, к которому это свойство было применено.

    Синтаксис Object.Rows(index)

  • object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист;
  • index - индекс строки в объекте.
    •

    Важно

  • Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range.
    •

    Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек.

    Свойство CurrentRegion

    Свойство CurrentRegion определяет объект Range, который соответствует интервалу ячеек, включающему заданную ячейку.

    Пример

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

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

    (рис 8.9) Пример работы со свойством CurrentRegion
    Cвойства, связанные с шириной и высотой ячейки
    Свойства Примеры и комментарии
    ColumnWidth (R/W Variant) Возвращает или изменяет ширину колонки в единицах, эквивалентных одному символу в стиле Обычный ( Normal ). Шрифт стиля по умолчанию Arial Cyr и размер шрифта 10.

    Range("A1").ColumnWidth=15 устанавливает ширину колонки A в 15 символов
    Width (R/O Variant) Возвращает ширину интервала ячеек в пунктах.

    Range("A1").Width возвращает значение 93.75, если ширина колонки 15 символов, шрифт Times New Roman, размер шрифта 12 пунктов (72 пункта равны 1 дюйму или приблизительно 2,54 см).

    Debug.Print Range("A1:C3").ColumnWidth распечатает значение 8.43, а оператор Debug.Print Range("A1:C3").Width распечатает значение 144, если для колонок установлена стандартная ширина, шрифт Arial Cyr и размер шрифта 10
    RowHeight (R/W Variant) Возвращает или изменяет высоту строк интервала в пунктах.

    ActiveCell.RowHeight = 14 устанавливает высоту строки, в которой находится активная ячейка, в 14 пунктов
    Height (R/O Variant) Возвращает суммарную высоту интервала строк, зависящую от названия и размера шрифта. Если шрифт Arial Cyr и размер шрифта 10, то Debug.Print Range("A1").Height распечатает 12,75 и Debug.Print Range("A1:C3").Height распечатает 38,25
    WrapText (R/W Boolean) Range("A1").WrapText=True

    Значение True разбивает текст ячейки на несколько строк, если ширина столбца недостаточна для размещения текста целиком

    Замечание

  • Свойства Width и Height имеют статус Read-Only для объектов Range, но для других объектов, например, для объекта Window, они имеют статус Read-Write.
    •

    Методы

    Методы Select и Activate

    Метод Select выделяет интервал ячеек.

    Синтаксис object.Select(Replace)

  • object - выделяемый объект типа Range. Ссылка на объект обязательна;
  • Replace - для расширения выделения аргумент устанавливается в False. Если аргумент не задан или принимает значение True, то вместо старой области выделения создается новая область выделения. Необязательный параметр.
    •

    Метод Activate активизирует единственную ячейку.

    Синтаксис object.Activate

  • object - активизируемая ячейка. Ссылка на объект обязательна.
    •
    Примеры
    Оператор Активная ячейка
    Range("C7:E9").Select C7
    Range("C7:E9").Offset(1,1).Activate D8
    Range("C7:E9").Activate C7
    Range("C7:E9").Cells(2,1).Activate C8

    ЗАМЕЧАНИЯ

  • Активная ячейка выделяется фоном среди всех выделенных ячеек.
  • Метод Select выделяет интервал ячеек, тогда как метод Activate активизирует только одну ячейку.
  • При использовании метода Select первая ячейка интервала становится активной.
  • Если выделена только одна ячейка, то она является активной и свойства ActiveCell и Selection возвращают одну и ту же ячейку (объект Range ).
    •

    Метод Clear

    Очищает интервал ячеек, изменяя, таким образом, свойство Value каждой ячейки интервала.

    Пример

    Процедура очищает интервал ячеек или заполняет его единицами в зависимости от значений ячеек. Дополнительно изменяется шрифт и размер шрифта.

    (рис 8.10) Пример применения метода Clear

    Название шрифта является обязательным параметром вызываемой процедуры, а размер шрифта - необязательным параметром. Если он не задан, то размер шрифта принудительно меняется на 16.

    Вызывающая процедура проверяет, является ли интервал ячеек A1:B5 пустым. Если это не так, то интервал очищается и размер шрифта устанавливается в 16. Если же интервал ячеек пуст, то все ячейки интервала заполняются единицами и размер шрифта интервала ячеек равен 10.

    В обоих случаях шрифт ячеек интервала A1:B5 устанавливается в Times New Roman.

    Цветовое оформление объекта Range

    Свойство ColorIndex

    Свойство ColorIndex заливки (заливка - это объект Interior, который является вложенным для объекта Range ) рассматривает цвет как номер в палитре цветов рабочей книги. Всего в палитре 56 цветов.

    Пример

    В ячейках, начиная с активной, отображается палитра цветов рабочей книги.

    Переменные c и r содержат, соответственно, индекс столбца и индекс строки активной ячейки.

    Прямоугольный интервал из 56 ячеек (7 строк и 8 столбцов, начиная с активной ячейки) для отображения палитры задается переменной obj_range, содержащей ссылку на объект Range.

    Свойство Pattern (образец заливки) задается константой xlSolid, позволяющей установить заливку активных ячеек.

    (рис 8.11) Пример изменения свойства ColorIndex

    Свойство Color

    Свойство относится к объектам Border, Font или Interior (вложенные объекты для объекта Range ) и устанавливает цвет объекта в формате RGB. Свойство можно задать, используя функцию RGB, которая возвращает цвет в виде числа типа Long. Аргументы функции Red, Green, Blue определяют насыщенность соответствующей компоненты в устанавливаемом цвете и изменяются от 0 до 255.

    Например, оператор ActiveCell.Interior.Color=RGB(255, 0, 0) устанавливает красную заливку активной ячейки.

    Замечание

  • Не путайте свойство Color со свойством Colors! Последнее является свойством объекта Workbook и использует палитру цветов рабочей книги как массив значений цветов, например, оператор ActiveWorkbook.Colors(51) = RGB(255,0,0) меняет 51 цвет палитры активной рабочей книги на красный.
    •

    Чтобы использовать серый цвет разной интенсивности, установите равные аргументы функции RGB, например, выражение RGB(196,196,196) устанавливает 25% серую заливку. Чем больше значения аргументов, тем ближе серый цвет к белому.

    Страницы:

    Все офисные приложения можно рассматривать как совокупность объектов. Объектами являются сами приложения Application (центральный или корневой объект) и их компоненты (вложенные объекты).

    Примеры объектов: рабочий лист Worksheet, рабочая книга Workbook, диаграмма Chart, рамка Border. Доступ к интервалам ячеек возможен только как к объектам Range, например, объект Range("A1") представляет ячейку A1. Каждый элемент меню, каждая командная кнопка, любой элемент рабочего листа являются объектами MS Excel.

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

    В VBA возможны три типичные действия c объектами:

  • проверка свойств объекта;
  • изменение объекта посредством модификации его свойств;
  • выполнение методов объекта.
    •

    Подробно структура объектов, синтаксис свойств и методов, перечень событий рассмотрены в разделе Microsoft Excel Object Model книги под названием Microsoft Excel Visual Basic Reference справочника по VBA (Help).

    Свойства объектов

    Свойства объекта это атрибуты объекта. Каждый объект может иметь десятки свойств, например, объект Worksheet имеет 52 свойства.

    Свойства делятся на две группы:

  • свойства-участники ( accessors ), представляющие вложенные объекты;
  • терминальные свойства ( terminals ), задающие характеристики объекта или его состояние.
    •

    Свойства-участники позволяют добраться до объекта, находящегося на любом уровне вложенности. Например, в записи Application.ActiveWorkbook свойство ActiveWorkbook позволяет получить доступ к объекту приложения - активной рабочей книге, а в записи ActiveWorkbook.ActiveSheet свойство ActiveSheet означает доступ к объекту рабочей книги - активной странице этой книги.

    Изменение значений терминальных свойств - это один из способов изменить внешний объект.

    Свойства имеют статус:

  • Read-Write (далее R/W ) предполагает возможность изменения свойства;
  • Read-Only (далее R/O ) означает, что можно только протестировать значение свойства.
    •

    Некоторые свойства являются общими для многих объектов и для разных объектов могут иметь разный статус, например, Height, Width, являющиеся свойствами интервалов, окон и приложения. В дальнейшем указывается статус и тип значения свойства.

    В качестве значений свойств могут использоваться константы с префиксом xl, например, константа xlCalculationManual устанавливает ручной пересчет таблицы.

    Примеры часто используемых свойств объектов
    Свойство Объект Примеры Описание
    Bold, Italic (R/W Boolean) Font ActiveCell.Font.Bold=True

    ActiveCell.Font. Italic =False

    		 Устанавливает полужирный шрифт. Отменяет курсив.
    Column, Row (R/W Long) Range Debug.Print

    Range("B3:C5").Column,

    Range("B3:C5").Row

    		 В окне Immediate будут распечатаны номер первой колонки и номер первой строки интервала ячеек B3:C5 - "2 3"
    ColumnWidth (R/W Variant) Range Range("A1:B5").ColumnWidth=15 Ширина каждой колонки объекта Range 15 символов
    Height, Width (Double) Многие объекты Application.Width=200 (статус R/W)

    W=Range("A1:B5"). Height (статус R/O)

    		 Ширина окна приложения 200 пт.

    Возвращает суммарную высоту строк объекта Range в пунктах
    RowHeight (R/W Variant) Range Range("A1:B5").RowHeight=15 Устанавливает высоту каждой строки объекта Range в пунктах
    Formula (R/W Variant) Range Range("A2").Formula = "=pi()*A1^2" В ячейку А2 записывается формула
    Value (R/W Variant) Range Range("A3").Value=6.28 Значение ячейки устанавливается равным 6,28
    Count (R/O Long) Группа объектов N=Sheets.Count В переменную N записывается количество элементов коллекции объектов
    Name (String) Многие объекты ActiveSheet.Name="Nw_Sh"(статус R/W)

    Wb =ActiveWorkbook.Name(статус R/O)

    		 Активному листу присваивается новое имя.

    Переменной Wb присваивается имя активной рабочей книги.
    Parent (R/O Object) Многие объекты P_t= Range("A1:B5").Parent

    для объекта Range возвращает объект Sheet - рабочий лист, на котором объект Range расположен

    		 Возвращает объект обычно другого типа, который является объектом более высокого уровня по отношению к указанному объекту

    Свойства объектов изменяются при помощи оператора присваивания или под влиянием методов.

    Синтаксис операторов присваивания object.property=expression

  • object - ссылка на объект, над которым совершается действие;
  • property - название свойства, значение которого необходимо изменить;
  • expression - выражение, представляющее новое значение свойства объекта.
    •

    Важно

  • Каждое свойство может принимать значения только определенного типа.
  • Тип результата вычисления выражения должен соответствовать типу свойства, т.е, если свойство является числовым, то и результат вычисления выражения должен быть числом или должен преобразовываться в число.
    •

    Например, оператор ActiveCell.Font. Bold="b" является ошибочным, так как свойство Bold имеет тип Boolean и может принимать значения только True или False.

    Пример

    Процедура изменяет размеры активного окна приложения. Ширина и высота окна приложения вводятся в диалоге. Свойства Height и Width для объекта Window имеют статус R/W, но эти свойства нельзя изменять, если размер окна минимизирован или максимизирован. Поэтому первоначально в процедуре свойством WindowState устанавливается обычный размер окна

    (рис 8.1) Процедура изменяет размеры активного окна приложения

    При помощи оператора присваивания можно сохранить значение свойства в переменной. Значение свойства может использоваться как часть условного выражения. В таких случаях говорят о возврате значения свойства.

    Синтаксис оператора присваивания, возвращающего значение свойства

    variable=object.property
  • variable - переменная или свойство некоторого объекта;
  • object - ссылка на объект, свойство которого запоминается или тестируется;
  • property - название свойства, значение которого необходимо получить.
    •

    Важно

  • Тип переменной должен соответствовать типу значения свойства.
    •

    Примеры

  • Распечатать название рабочего листа c активной ячейкой.

    Свойство Parent возвращает рабочий лист, на котором расположена активная ячейка.

    (рис 8.2) Процедура распечатки названия рабочего листа c активной ячейкой
  • В зависимости от знака числа, хранящегося в ячейке, залить ячейку некоторым цветом.

    В процедуре тестируется свойство Value объекта Range - ячейки A1. В случае отрицательного числа цвет заливки ячейки - синий.

    (рис 8.3) Процедура тестировния свойства Value объекта RangeПри нулевом значении заливка ячейки отменяется (константа xlNone ). При положительном значении устанавливается цвет заливки, предусмотренный по умолчанию (константа xlAutomatic ).
    •

    Методы объектов

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

    Важно

  • Методы - это функции или подпрограммы.
  • Подобно процедурам методы могут принимать аргументы.
  • Функции VBA и методы Application могут иметь одинаковые имена, но различные аргументы, например, функция InputBox класса Interaction и метод InputBox класса Application.
    •

    Синтаксис вызова метода без аргументов

    object.method

    например, ActiveCell.Justify.

    Вызов метода с аргументами имеет две формы:

  • variable=object.method(arguments) - функциональная форма вызова (аргументы указываются в скобках после названия метода).
  • object.method arguments - операторная форма вызова (аргументы записываются через пробел после названия метода).
    •

    Если метод использует несколько аргументов, то они перечисляются через запятую.

    Аргументы можно задавать, используя позиционное или произвольное расположение.

    ЗАПОМНИТЕ

  • Функциональная форма вызова метода используется, если необходимо сохранять значение, возвращаемое методом.
  • Операторная форма вызова метода используется, если не нужно сохранять возвращаемое методом значение.
    •

    Каждый объект имеет свои собственные методы. Некоторые методы являются уникальными, а другие встречаются у нескольких объектов. Например, метод Delete может удалять графический объект и рабочий лист.

    Модель объектов

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

    (рис 8.4) Модель объектов MS Excel (фрагмент)

    Нажатие на выбранный объект отображает на экране статью, посвященную объекту, в которой ниже имени объекта, как правило, расположены три гиперссылки, позволяющие просмотреть свойства ( Properties ), методы ( Methods ) и события ( Events ) выбранного объекта с соответствующими примерами. В дополнение можно раскрыть список рекомендуемых для просмотра объектов ( See Also ). Нажатие на Multiple objects показывает перечень исходных объектов или перечень вложенных объектов.

    (рис 8.5) Фрагмент статьи, посвященной объекту Workbook

    Объекты приложения связаны между собой, и модель объектов отражает иерархические связи между объектами.

    Модель объектов содержит простые объекты и коллекции объектов. Коллекция объектов ( Collection ) объединяет группу подобных объектов.

    Коллекции объектов

    Коллекция объектов - это объект специального типа, существующий для управления объектами группы. Например, Workbooks является коллекцией всех открытых книг - объектов Workbook, а Worksheets - коллекцией рабочих листов некоторой рабочей книги - объектов Worksheet. Примерно половина всех объектов MS Excel - это коллекции объектов.

    Процедуры могут обращаться как к отдельному элементу коллекции (к объекту Workbook или к объекту Worksheet ), так и ко всем объектам коллекции одновременно (к объекту Workbooks или к объекту Worksheets ). Коллекция объектов и объекты этой коллекции обладают различными свойствами и методами.

    Коллекция объектов - это упорядоченная совокупность объектов. Для доступа к конкретному объекту в коллекции можно использовать его имя или порядковый номер в коллекции, например, Workbooks(1) указывает на первую рабочую книгу. Запись Worksheets("Sheet2") указывает на лист с именем Sheet2.

    Обращение к объекту

    Контейнеры

    Объекты приложения могут включать в себя объекты разных типов. Например, рабочая книга может содержать один или несколько рабочих листов и листов диаграмм. Рабочий лист, в свою очередь, содержит строки, колонки и интервалы ячеек. Когда объект одного типа находится внутри объекта другого типа, говорят о контейнере ( container ), в котором содержится объект.

    Самый старший контейнер объектов MS Excel - это приложение Application. Приложение - это контейнер для всех открытых рабочих книг, и в то же время приложение содержит такой глобальный объект, как строка меню, который доступен любой рабочей книге. Рабочий лист представляет пример того, что объект может быть частью нескольких контейнеров или коллекций одновременно: он входит в рабочую книгу, с одной стороны, а с другой стороны - является частью коллекции Sheets и коллекции Worksheets.

    Преимущества

  • Рассмотрение объекта в качестве контейнера позволяет уточнить, сославшись на контейнер, с каким именно объектом производится действие в процедуре.
    •

    Если в рабочей книге имеются два рабочих листа Sheet1 и Sheet2, то запись Worksheets("Sheet1").Range("A1") указывает на ячейку A1 рабочего листа Sheet1, а запись Worksheets("Sheet2").Range("A1") указывает на ячейку A1 рабочего листа Sheet2.

    Ссылка на объект

    Объект в VBA указывается при помощи ссылки. Запись Workbooks("cross").Worksheets("Sheet2") указывает на объект, являющийся листом с именем Sheet2 в рабочей книге cross, отличая его, таким образом, от листа с тем же именем, но в другой рабочей книге.

    Важно

  • Ссылка на объект состоит из имени контейнера и названия свойства-участника.
  • Ссылка на объект может состоять из нескольких спецификаторов (имен контейнеров), отделенных друг от друга точкой. Такая запись отражает иерархию объектов и позволяет точно указать конкретный объект.
  • Свойства и методы объекта указываются через точку после ссылки на объект.
    •

    Для объектов, относящихся к классу globals (например, активная рабочая книга, активная ячейка рабочего листа, константа), спецификатор Application можно опустить.

    Оператор With

    В VBA перед обращением к каждому из методов или свойств объекта требуется наличие ссылки на объект. Конструкция With…End With позволяет применить последовательность операторов к объекту, указав его имя только один раз в операторе With. Благодаря этому программа становится менее громоздкой, освобождаясь от повторений ссылки на объект.

    Синтаксис оператора

    With Object
[statements]
End With
  • Object - имя объекта;
  • statements - последовательность операторов.
    •

    Первая строка этой структуры идентифицирует объект, с которым будут производиться действия. В последующих операторах используются свойства и методы идентифицированного объекта. Оператор End With является закрывающей скобкой для оператора With. Часто подобная структура записывается при помощи макрорекордера.

    Внимание

  • Каждый оператор внутри блока statements начинается с точки.
    •

    Использование объектных переменных

    Чтобы получить доступ к свойствам или методам объекта, можно использовать два способа: прямое указание на объект и применение объектной переменной. Объектная переменная занимает 4 байта и хранит адрес, указывающий на объект.

    Например, установить полужирный шрифт для первой строки активного листа можно оператором ActiveSheet.Rows(1).Font.Bold = True или, используя объектную переменную, можно предложить два способа записи:

    Здесь оператор Set создает объект, тип которого установлен при описании объектной переменной. Далее можно обратиться к свойствам или методам созданного объекта. В первом случае это объект типа Range, во втором случае - тип объекта Font.

    Преимущества

  • Использование объектных переменных улучшает читабельность программы.
  • Использование объектных переменных уменьшает время выполнения программы, т.к. при каждом выполнении оператора для обращения к объекту не придется проходить полную цепочку иерархии объектов.
    •

    Объект Application

    При открытии MS Excel автоматически становится доступным объект Application с его свойствами и методами. Объект Application - корневой объект приложения. В него вложены остальные объекты приложения. Доступ к ним осуществляется посредством свойств-участников объекта Application.

    Для создания ссылки на объект Application используется свойство Application.

    Примеры операторов
    Application.Windows("AIR.XLS ").Activate Оператор активизирует рабочую книгу
    Application.Goto Range("B3:C5") Оператор выделяет интервал ячеек на активном рабочем листе

    Многие свойства объекта Application используются без ссылки на объект Application, так как они возвращают объекты, относящиеся к классу globals.

    Активные объекты

    Свойства, название которых начинается со слова Active, возвращают активный объект соответствующего типа. Для этих свойств необязательно указывать ссылку на объект Application, так как они входят в класс globals. Некоторые из этих свойств являются одновременно свойствами нескольких объектов.

    Свойства, возвращающие активный или выделенный объект
    Свойство Объект Действие Возвращаемый объект
    ActiveCell Application, Window Возвращает активную ячейку Range
    ActiveSheet Application, Window, Workbook Возвращает активный лист. Это может быть рабочий лист, лист диаграмм Sheet
    ActiveWorkbook Application Возвращает активную рабочую книгу Workbook
    ActiveWindow Application Возвращает активное окно Window
    Selection Application, Window Возвращает выделенный объект Различные типы объектов

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

    Примеры
    Оператор Комментарий
    ActiveCell.Font.Bold=True Устанавливает полужирный шрифт текста активной ячейки
    ActiveSheet.Name="Проба" Изменяет название активного листа рабочей книги
    MsgBox ActiveWorkbook.Fullname Высвечивает полное имя рабочей книги, включая путь и имя файла
    Selection.NumberFormat="0.00" Устанавливает числовой формат с двумя знаками после запятой для ячеек выделенного интервала
    ActiveCell.Value=10

    Application.ActiveCell.Value=10

    ActiveWindow.ActiveCell.Value=10

    Application.ActiveWindow.ActiveCell.Value=10

    		 Присваивают активной ячейке значение 10. Приведенные примеры доступа к активной ячейке равносильны
    Worksheets("Sheet1").Activate

    Selection.Clear

    		 Очищает предварительно выделенный на листе Sheet1 объект, например, интервал ячеек
    Если никакой объект не выделен, то свойство Selection возвращает значение Nothing и очистка не выполняется
    Worksheets("Sheet1").Activate

    MsgBox "Тип объекта " TypeName(Selection)

    		 Высвечивает тип предварительно выделенного на листе Sheet1 объекта

    Свойства, влияющие на высвечивание на экране

    Свойство DisplayAlerts (R/W Boolean)

    Во время выполнения программ возможно высвечивание сообщений и запросов MS Excel (встроенных диалоговых окон), на которые пользователь должен реагировать. Например, это может быть запрос на сохранение изменений при закрытии рабочей книги. Значение False свойства DisplayAlerts позволяет отключить высвечивание подобных сообщений. При отключении запросов MS Excel выбирает ответ, который установлен в диалоге по умолчанию.

    Важно

  • Не забывайте возвращать первоначальное значение этого свойства, равное True, так как MS Excel автоматически его не восстанавливает.
    •
    Пример
    Процедура закрывает рабочую книгу AIR.XLS без сохранения изменений, при этом запрос на сохранение изменений не возникает.

    Свойство ScreenUpdating (R/W Boolean)

    Это свойство обновления экрана разрешает (значение True ) или запрещает (значение False ) изменение экрана при выполнении команд MS Excel.

    Если процедура выполняет команды MS Excel, то на экране отображаются все действия с объектами MS Excel: выделение и копирование ячеек, перемещение экрана и т.д. Свойство ScreenUpdating позволяет избежать подобного "мелькания экрана".

    Важно

  • Выключение обновления экрана ускоряет выполнение макропроцедур.
  • Не забывайте возвращать первоначальное значение этого свойства, равное True, так как MS Excel автоматически его не восстанавливает.
    •

    Свойство Visible (R/W Boolean)

    Это свойство позволяет скрывать или показывать окно приложения. Свойство доступно для многих объектов, например, для объектов формы.

    Пример
    Процедура делает видимым окно приложения, если оно не видно или скрывает его, если окно высвечено.

    Другие свойства объекта Application

    Свойства Описание Примеры операторов
    Calculation (R/W) Возвращает или устанавливает параметры вычислений. Задается константами MS Excel Application.Calculation=xlCalculateManual задает ручной пересчет
    Application.CalculateBeforeSave=True задает автоматический пересчет перед сохранением файла
    Path (R/O String) Возвращает полный путь к объекту MsgBox "Путь к приложению MS Excel " Application.Path высвечивает путь к программе MS Excel
    SheetsInNewWorkbook (R/W Long) Возвращает или устанавливает количество рабочих листов во вновь создаваемой рабочей книге MsgBox "В новой рабочей книге " Application.SheetsInNewWorkbook " рабочих листов" высветит количество листов во вновь создаваемой рабочей книге
    ThisWorkbook Возвращает объект ThisWorkbook - рабочую книгу, в которой находится выполняемая процедура.

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

    		 ThisWorkbook.Close SaveChanges:=False закрывает рабочую книгу, содержащую исполняемый код
    ThisWorkbook.FullName возвращает полный путь к рабочей книге, содержащей исполняемый код 
    For Each w In Workbooks
  If w.Name <> ThisWorkbook.Name Then
    w.Close savechanges:=True
  End If
Next w
    Цикл закрывает с сохранением изменений все открытые рабочие книги за исключением той, в которой находится исполняемая процедура
    WorksheetFunction Возвращает одноименный объект-контейнер функций рабочего листа MsgBox "Десятичный логарифм 10^6 равен " WorksheetFunction.Log10(10^6) высвечивает 6

    Важно

  • Вызов функции рабочего листа производится со ссылкой на контейнер WorksheetFunction или на объект Application.
  • Названия функции VBA и функции рабочего листа, выполняющих одинаковые действия, могут не совпадать, например, функция Instr и функция Find.
    •

    Методы

    Метод OnTime

    Метод позволяет запустить некоторую процедуру в заданный момент времени.

    Синтаксис OnTime(EarliestTime, Procedure[,LatestTime])

  • EarliestTime - выражение, задающее время запуска процедуры;
  • Procedure - имя запускаемой процедуры;
  • LatestTime - самое позднее время, когда процедура может быть запущена, если невозможно было запустить ее точно в указанное время. Причиной невозможности запуска могло явиться выполнение диалога, который не был прерван ранее.
    •

    Пример

    (рис 8.6) Пример применения метода OnTime

    Процедура mes_time в 16:30 высвечивает сообщение "Пошли пить кофе".

    Внимание

  • При указании, например, LatestTime=EarliestTime + 30, MS Excel подождет 30 секунд и, если выполняемая процедура не завершится, то процедура, указанная в OnTime, не будет запущена вовсе.
  • Если параметр LatestTime не задавать, то MS Excel дождется завершения процедуры и запустит нужную процедуру.
  • Если в качестве EarliestTime использовать выражение Now()+интервал времени, то процедура запустится спустя указанный интервал от текущего времени.
    •

    Метод Wait

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

    Синтаксис expression.Wait(Time)

  • expression - возвращает объект Application ;
  • Time - время окончания паузы в выполнении процедуры в формате даты.
    •

    Пример

    Процедура устанавливает паузу примерно на 10 секунд.

    Используются функции даты Hour, Minute, Second, TimeSerial, Now. С их помощью из текущего времени выделяются час, минуты, секунды; секунды увеличиваются на 10, и составляется время окончания паузы.

    (рис 8.7) Пример применения метода Wait

    Коллекции объектов

    Ссылка на объект коллекции - это название коллекции, после которого в скобках указывается индекс объекта или его имя в кавычках. Например, ссылка Workbooks(1) выбирает первую из открытых рабочих книг, а Workbooks("budget") ссылается на рабочую книгу с именем "budget".

    Важно

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

    Объекты Workbooks и Workbook

    Документ MS Excel (рабочая книга) это объект Workbook. Можно одновременно работать с несколькими рабочими книгами. Открытые рабочие книги составляют коллекцию рабочих книг - Workbooks.

    Свойство Workbooks объекта Application возвращает объект Workbooks.

    При открытии или создании рабочей книги элемент Workbook автоматически добавляется в конец коллекции Workbooks, а при закрытии книги соответствующий элемент также автоматически удаляется из коллекции.

    Некоторые свойства и методы объектов Workbooks и Workbook
    Свойства и методы Примеры операторов и комментарии
    Объект Workbooks
    Свойство Count (R/O Long) MsgBox "Число открытых рабочих книг " Workbooks.Count высвечивает число рабочих книг в коллекции
    Метод Add Workbooks.Add добавляет новую рабочую книгу в коллекцию
    Метод Close Workbooks.Close используется без аргументов и закрывает все рабочие книги
    Объект Workbook
    Свойство Colors Свойство, заданное с индексом, указывает на конкретный элемент палитры. ActiveWorkbook.Colors(5) = RGB(255,0,0) заменяет пятый цвет палитры на красный
    Свойство без индекса возвращает палитру цветов в виде массива из 56 цветов.

    ActiveWorkbook.Colors = Workbooks("AIR.XLS").Colors заменяет палитру активной книги на палитру цветов книги AIR.XLS.
    Свойство Name (R/O String) MsgBox Workbooks(Workbooks.Count).Name высвечивает имя последней открытой книги
    Свойство FullName (R/O String) MsgBox ActiveWorkbook.FullName возвращает полное имя активной рабочей книги, включая путь к ней
    Свойство Sheets ThisWorkbook.Sheets.Count возвращает количество элементов в коллекции листов различных типов рабочей книги, содержащей выполняемый код
    Свойство Charts ActiveWorkbook.Charts(1).Name возвращает имя первого листа в коллекции диаграммных листов активной книги
    Свойство Worksheets Workbooks(1).Worksheets(1).Activate активизирует первый лист из коллекции рабочих листов
    Метод Open Workbooks.Open "AIR.xls" открывает существующую рабочую книгу AIR.xls
    Метод Close ActiveWorkbook.Close SaveChanges:=True, Filename="AIR" закрывает рабочую книгу. Книга удаляется из коллекции, и элементы коллекции Workbooks перенумеровываются.

    Параметр SaveChanges сохраняет или отменяет сделанные изменения. Параметр Filename задает название новой рабочей книги
    Метод Activate Workbooks("AIR.XLS").Activate активизирует указанную рабочую книгу
    Метод SaveAs ActiveWorkbook.SaveAs FileName:="d:\bel_acc\first_book" сохраняет рабочую книгу под именем Filename. Если в Filename папка не указана, то файл сохраняется в текущей папке

    Событийные процедуры

    Событийные процедуры записываются на процедурном листе, связанном с объектом. Каждый объект имеет свои собственные события.

    Чтобы вставить событийную процедуру для объекта Workbook

  • выделите объект ThisWorkbook (Эта книга) в окне проекта;
  • перейдите на лист процедур, нажав клавишу F7. Можно выполнить команду View Code или сделать двойной щелчок на объект ThisWorkbook ;
  • на процедурном листе в окне выбора объектов (вверху слева) выберите объект Workbook ;
  • в окне выбора событий (вверху справа) выберите событие. Автоматически вставляется процедура со стандартным именем, которое состоит из названия объекта и названия события, разделенных нижним подчеркиванием (_), например, для события Open событийная процедура имеет имя Workbook_Open ;
  • запишите текст процедуры.
    •

    Пример

    При вставке нового листа в рабочую книгу процедура запрашивает имя нового листа и вставляет лист в начало рабочей книги.

    При выборе события NewSheet автоматически появляется новая процедура Workbook_NewSheet с параметром Sh.

    Значение параметра, являющееся ссылкой на объект - новый лист, передается процедуре во время ее выполнения. Метод Move перемещает вставленный лист. Параметр before этого метода определяет новое месторасположение листа - начало рабочей книги.

    Объекты Sheets, WorkSheets и WorkSheet

    Коллекция Sheets представляет собой совокупность листов различных типов - рабочих листов (коллекция Worksheets ) и листов диаграмм (коллекция Charts ). Таким образом, каждый элемент коллекции Sheets является элементом коллекции WorkSheets или коллекции Charts и наоборот, любой элемент коллекции WorkSheets или коллекции Charts принадлежит коллекции Sheets.

    Некоторые свойства и методы объектов Sheets, WorkSheets и WorkSheet
    Свойства и методы Примеры и комментарии
    Объекты Sheets, WorkSheets
    Свойство Count (R/O Long) MsgBox "Количество рабочих листов в активной книге " ActiveWorkbook.WorkSheets.Count высвечивает количество рабочих листов в рабочей книге
    Метод Add Sheets.Add, WorkSheets.Add добавляет новый лист заданного типа в рабочую книгу
    Объекты Sheets, WorkSheets, Sheet, WorkSheet
    Методы Copy, Move Копирует, перемещает указанные листы или группу листов в новое место. Worksheets(1).Move after:=Worksheets(Worksheets.Count) перемещает первый лист в конец рабочей книги
    Объекты Sheet, WorkSheet
    Метод Activate WorkSheets("January").Activate активизирует указанный рабочий лист
    Метод Delete ActiveWorkbook.Worksheets(1).Delete удаляет первый рабочий лист
    Свойство Name (R/W String) Возвращает или устанавливает имя листа. WorkSheets(WorkSheets.Count).Name ="LastSheet" переименовывает последний рабочий лист
    Объекты WorkSheet
    Свойство Columns (R/O) Возвращает коллекцию столбцов. Worksheets(1).Columns(1).Font.Bold = True устанавливает полужирный шрифт для первой колонки первого рабочего листа
    Свойство ScrollArea (R/W String) Определяет границы интервала, внутри которого возможно перемещение по ячейкам. При установке значения "пустая строка" доступны все ячейки рабочего листа. Worksheets(1).ScrollArea = "A1:F10" разрешает доступ только к ячейкам A1:F10
    Свойство Shapes (R/O) Возвращает коллекцию Shapes - коллекцию графических объектов рабочего листа: рисунки, автофигуры и т.д. ActiveSheet.Shapes(1).AutoShapeType = 21 меняет тип первого графического объекта активного листа на "сердечко"
    Свойство Rows(R/O) Возвращает коллекцию строк. Worksheets("Sheet1").Rows(3).Delete удаляет третью строку
    Метод Calculate ActiveWorksheet.Calculate производит вычисления во всех ячейках указанного рабочего листа
    Метод CheckSpelling Используется для проверки правописания (с аргуменами и без аргументов). ActiveSheet.CheckSpelling ignoreUppercase:= True не проверяет слова, записанные только прописными буквами

    Методы

    Метод Add

    Добавляет новый лист в коллекцию Sheets, WorkSheets. При создании рабочей книги коллекция WorkSheets содержит столько рабочих листов, сколько определено свойством SheetsInNewWorkbook объекта Application.

    Внимание

  • Метод Add для объектов Workbooks и Sheets имеет различный синтаксис.
    •

    Cинтаксис метода для коллекций Sheets, WorkSheets

    expression.Add([Before] [,After] [,Count] [,Type])
  • expression - выражение, возвращающее коллекцию WorkSheets или Sheets. Указание обязательно;
  • Возможно задание только одного из двух параметров Before или After - специфицирует лист, перед которым вставляется новый лист;
  • Возможно задание только одного из двух параметров Before или After - специфицирует лист, после которого вставляется новый лист;
  • Count - количество вставляемых листов;
  • Type - тип вставляемого листа. Используются константы: xlWorksheet (по умолчанию), xlChart (только для объекта Sheets ), xlExcel4MacroSheet, xlExcel4IntlMacroSheet.
    •

    Важно

  • При отсутствии всех параметров один рабочий лист добавляется перед активным листом.
  • При задании параметров Before и After указывается ссылка на лист как индекс или имя в коллекции листов, например, Sheets(1) или Sheets("Лист1")
    •

    Методы Move и Select

    Метод Move используется для перемещения листов.

    Синтаксис expression.Move([Before] [,After])

  • expression - ссылка на объект, представляющий перемещаемый лист. Указание обязательно;
  • необязательные параметры before и after (ссылки на лист, см. описание метода Add ) определяют новое местоположение перемещаемого листа. Если не указан ни один из параметров, то лист перемещается во вновь создаваемую рабочую книгу.
    •

    Метод Select выделяет объект. При применении к одному листу методы Activate и Select активизируют указанный лист. Но метод Select используется для группировки листов, т.е. для расширения выделения.

    Синтаксис expression.Select([Replace])

  • expression - ссылка на объект, представляющий выделяемый лист. Указание обязательно;
  • Replace - для расширения выделения аргумент устанавливается в False. Если аргумент не задан или принимает значение True, то вместо старой области выделения создается новая область выделения. Необязательный параметр.
    •

    Замечание

  • Для выделения листов с конкретными именами используйте функцию Array. Например, Sheets(Array("Лист8", "Лист12")).Select.
    •

    Пример

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

    Событийные процедуры

    Чтобы вставить событийную процедуру для объекта WorkSheet:

  • выделите объект WorkSheet (например, Лист1 ) в окне проекта;
  • перейдите на лист процедур этого объекта;
  • на процедурном листе в окне объектов (вверху слева) выберите объект WorkSheet ;
  • в окне выбора событий (вверху справа) выберите событие;
  • запишите текст процедуры.
    •

    При выборе события автоматически вставляется процедура со стандартным именем, которое состоит из названия листа и названия события, разделенных нижним подчеркиванием (_).

    Пример

    При активизации листа Лист1 в ячейку A1 заноситcя название листа.

    (рис 8.8) Пример работы с событийной процедурой объекта WorkSheet

    Объект Range

    При работе в MS Excel чаще всего выполняются некоторые действия с группой ячеек рабочего листа. Объект Range - это отдельная ячейка, целиком строка или столбец рабочего листа, выделенный интервал ячеек, непрерывный интервал ячеек или интервал несмежных ячеек.

    Для задания объекта Range существуют различные возможности. Например, благодаря свойству ActiveCell, активная ячейка представляется в качестве объекта Range. Свойство Selection определяет выделенный интервал ячеек в качестве объекта Range.

    Свойства и методы, возвращающие объект Range
    Свойства и методы Применимы к объектам Примеры и комментарии
    Свойство ActiveCell Application Оператор ActiveCell.Value=10 устанавливает значение активной ячейки равным 10
    Свойство Areas Range Оператор Range("A1, B5:B10, C12:C20").Areas(3).Value = 10 устанавливает значение 10 для третьей области объекта Range - для ячеек интервала C12:C20
    Свойство Cells Application, Range, Worksheet Оператор Cells(7,3).Select активизирует ячейку C7 и равносилен оператору Range("C7").Select
    Свойство Columns Application, Range, Worksheet Оператор Columns("A:D").Select выделяет первые четыре столбца
    Свойство CurrentRegion Range Оператор ActiveCell.CurrentRegion.Count подсчитывает количество ячеек с данными в интервале, окружающем активную ячейку
    Свойство Offset Range Операторы Range ("A2:B10").Select, Selection.Offset(2,2).Value=10 устанавливают значение 10 каждой ячейки интервала C4:D12.

    Равносильно записи Range("C4:D12").Value=10
    Свойство Range Application, Range, Worksheet Операторы p=Range("A:B").Count, p=Range("налог").Count, p=ActiveSheet.Range("A1:A10").Count, p=Range("1:3").Count, p=Range("A1:C2, B10:D24").Count присваивают переменной p количество ячеек в заданных интервалах
    Свойство Rows Application, Range, Worksheet Оператор Rows("1:3").Select выделяет первые три строки
    Свойство Selection Application Оператор Selection.Clear очищает выделенный интервал ячеек
    Метод Union Range Union(Range("A1:C5"), Range("B10:D12") объединяет два несмежных интервала в один объект Range

    ЗАМЕЧАНИЯ

  • Все перечисленные свойства возвращают объект Range, не активизируя новую ячейку.
  • Ячейка остается активной до тех пор, пока методы Activate или Select не активизируют новую ячейку.
    •

    Свойства

    Cвойство Range

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

    Первый способ object.Range(Cell1)

    Второй способ object.Range(Cell1 [,Cell2])

  • object - ссылка на объект, например, на рабочий лист или на интервал ячеек. Ссылка необязательна. По умолчанию используется активный лист;
  • Cell1, Cell2 - аргументы для задания интервала ячеек. Cell1 - указание обязательно при обоих способах записи свойства Range.
    •

    Первый способ

    Аргумент Cell1 задает интервал ячеек произвольного размера.

    Важно

  • Могут использоваться имена, определенные в таблице, или координаты ячеек, столбцов, строк или интервалов.
  • Координаты задаются в стиле A1.
  • Координаты и имена заключаются в кавычки.
  • При задании интервалов координаты левого верхнего угла и правого нижнего угла интервала разделяются двоеточием.
  • Для задания несмежных интервалов используется запятая.
  • Для задания пересечения интервалов используется пробел.
    •
    Примеры записи оператора Range (1 способ)
    Запись Возвращаемый объект
    ActiveSheet.Range("A1:A10") интервал ячеек A1:A10 на активном листе
    Range("A:B") столбцы A:B
    Range("налог") интервал с именем налог
    Range("1:3") строки с первой по третью
    Range("A1:C2, B10:D24") объединение двух несмежных интервалов A1:C2 и B10:D24
    Range("A1:C10 B10:D24") пересечение двух интервалов A1:C10 и B10:D24, т.е. интервал B10:C10

    Второй способ

    Аргументы задают координаты интервала:

  • Cell1 - единственная ячейка (строка или столбец), задающая левый верхний угол интервала;
  • Cell2 - единственная ячейка (строка или столбец), задающая правый нижний угол интервала. Необязательный аргумент.
    •

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

    Примеры записи оператора Range (2 способ)
    Запись Возвращаемый объект
    Range("A5","D18") интервал A5:D18
    Range(Columns(1), Columns(5)) интервал, содержащий первые пять столбцов рабочего листа

    ЗАПОМНИТЕ

  • Если свойство Range применяется к объекту Range, то ссылка на интервал ячеек считается относительной и возвращается смещенный объект Range.
    •

    Например, если выделен интервал C1:D5, то запись Selection.Range("B2") возвратит ячейку D2.

    Свойство Cells

    Свойство Cells возвращает единственную ячейку рабочего листа, которая находится на пересечении строки и столбца, задаваемых целыми числами.

    Синтаксис object.Cells (RowIndex,ColumnIndex)

  • object - ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
  • RowIndex - индекс строки;
  • ColumnIndex - индекс столбца.
    •

    ЗАМЕЧАНИЯ

  • В свойстве Cells индекс строки является первым аргументом, а индекс столбца - вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка.
  • Понятие "индекс" ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число.
    •
    Примеры записи свойства Cells
    Запись Комментарий Возвращаемый объект
    ActiveSheet.Cells Свойство Cells без аргументов все ячейки активного рабочего листа
    Range("C5:C10").Cells(1,1) Свойство Cells применяется к объекту Range (относительная ссылка) ячейка C5
    Range(Cells(7,3),Cells(10,4)) Свойство Cells используется в качестве аргументов свойства Range интервал ячеек C7:D10

    Свойство Offset

    Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range("A5").Offset(-2,1) возвращает ячейку B3.

    Синтаксис object.Offset([RowOffset][,ColumnOffset])

  • object - ссылка на объект Range. Ссылка обязательна и определяет объект, относительно которого задается смещение;
  • RowOffset - смещение строки искомой ячейки относительно исходной ячейки;
  • ColumnOffset - смещение столбца искомой ячейки относительно исходной ячейки.
    •

    Необязательные аргументы RowOffset и ColumnOffset - числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю.

    Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7.

    Метод Union и свойство Areas

    Метод Union используется для объединения двух и более объектов Range, заданных ссылками на непересекающиеся интервалы, в один объект Range.

    Синтаксис Object.Union (arg1,arg2,...)

  • object - всегда объект Application. Ссылка необязательна;
  • arg1,arg2 - интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов.
    •

    Например, оператор Union(Range("A1:C5"),Range("B10:D12")).Select выделяет несмежные интервалы A1:C5 и B10:D12.

    Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range.

    Синтаксис Object.Areas(index)

  • object - ссылка на объект Range, состоящий из нескольких интервалов;
  • index - номер интервала в объекте. Аргумент необязателен.
    •
    Примеры
    Оператор Комментарий Результат
    p=Union (Range("A1:C5"), Range("B10:D12")).Areas(2).Count Если аргумент задан, то свойство Areas возвращает интервал - объект Range, определенный индексом интервала равен девяти, так как во втором интервале ровно 9 ячеек
    p=Union(Range("A1:C5"), Range("B10:D12")).Areas.Count Cвойство Areas без аргументов рассматривает каждый из несмежных интервалов как элемент коллекции объектов Range равен двум, так как объект, определенный методом Union, состоит из двух областей - коллекции из двух элементов
    p=Range("B10:D12").Areas.Count равен единице, так как объект Range представляет один элемент коллекции

    Свойства Column и Row (R/O Integer)

    Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств

    object.Column 
object Row
  • object - обязательная ссылка на объект Range.
    •

    Например, запись Range("C5").Column возвращает число 3, а запись Range("C5").Row возвращает число 5.

    Свойства Columns и Rows

    Свойство Columns (не путайте со свойством Column!) возвращает объект Range, представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено.

    Синтаксис Object.Columns(index)

  • object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист;
  • index - индекс колонки в объекте.
    •

    Например, запись Columns(1) возвращает колонку A активного рабочего листа, а запись Range("C1:D5").Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5.

    Важно

  • Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range.
  • Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns("B") указывают на одну и ту же колонку B.
    •

    Свойство Rows (не путайте со свойством Row!) возвращает объект Range, представляющий строку или коллекцию строк в объекте, к которому это свойство было применено.

    Синтаксис Object.Rows(index)

  • object - ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист;
  • index - индекс строки в объекте.
    •

    Важно

  • Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range.
    •

    Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек.

    Свойство CurrentRegion

    Свойство CurrentRegion определяет объект Range, который соответствует интервалу ячеек, включающему заданную ячейку.

    Пример

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

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

    (рис 8.9) Пример работы со свойством CurrentRegion
    Cвойства, связанные с шириной и высотой ячейки
    Свойства Примеры и комментарии
    ColumnWidth (R/W Variant) Возвращает или изменяет ширину колонки в единицах, эквивалентных одному символу в стиле Обычный ( Normal ). Шрифт стиля по умолчанию Arial Cyr и размер шрифта 10.

    Range("A1").ColumnWidth=15 устанавливает ширину колонки A в 15 символов
    Width (R/O Variant) Возвращает ширину интервала ячеек в пунктах.

    Range("A1").Width возвращает значение 93.75, если ширина колонки 15 символов, шрифт Times New Roman, размер шрифта 12 пунктов (72 пункта равны 1 дюйму или приблизительно 2,54 см).

    Debug.Print Range("A1:C3").ColumnWidth распечатает значение 8.43, а оператор Debug.Print Range("A1:C3").Width распечатает значение 144, если для колонок установлена стандартная ширина, шрифт Arial Cyr и размер шрифта 10
    RowHeight (R/W Variant) Возвращает или изменяет высоту строк интервала в пунктах.

    ActiveCell.RowHeight = 14 устанавливает высоту строки, в которой находится активная ячейка, в 14 пунктов
    Height (R/O Variant) Возвращает суммарную высоту интервала строк, зависящую от названия и размера шрифта. Если шрифт Arial Cyr и размер шрифта 10, то Debug.Print Range("A1").Height распечатает 12,75 и Debug.Print Range("A1:C3").Height распечатает 38,25
    WrapText (R/W Boolean) Range("A1").WrapText=True

    Значение True разбивает текст ячейки на несколько строк, если ширина столбца недостаточна для размещения текста целиком

    Замечание

  • Свойства Width и Height имеют статус Read-Only для объектов Range, но для других объектов, например, для объекта Window, они имеют статус Read-Write.
    •

    Методы

    Методы Select и Activate

    Метод Select выделяет интервал ячеек.

    Синтаксис object.Select(Replace)

  • object - выделяемый объект типа Range. Ссылка на объект обязательна;
  • Replace - для расширения выделения аргумент устанавливается в False. Если аргумент не задан или принимает значение True, то вместо старой области выделения создается новая область выделения. Необязательный параметр.
    •

    Метод Activate активизирует единственную ячейку.

    Синтаксис object.Activate

  • object - активизируемая ячейка. Ссылка на объект обязательна.
    •
    Примеры
    Оператор Активная ячейка
    Range("C7:E9").Select C7
    Range("C7:E9").Offset(1,1).Activate D8
    Range("C7:E9").Activate C7
    Range("C7:E9").Cells(2,1).Activate C8

    ЗАМЕЧАНИЯ

  • Активная ячейка выделяется фоном среди всех выделенных ячеек.
  • Метод Select выделяет интервал ячеек, тогда как метод Activate активизирует только одну ячейку.
  • При использовании метода Select первая ячейка интервала становится активной.
  • Если выделена только одна ячейка, то она является активной и свойства ActiveCell и Selection возвращают одну и ту же ячейку (объект Range ).
    •

    Метод Clear

    Очищает интервал ячеек, изменяя, таким образом, свойство Value каждой ячейки интервала.

    Пример

    Процедура очищает интервал ячеек или заполняет его единицами в зависимости от значений ячеек. Дополнительно изменяется шрифт и размер шрифта.

    (рис 8.10) Пример применения метода Clear

    Название шрифта является обязательным параметром вызываемой процедуры, а размер шрифта - необязательным параметром. Если он не задан, то размер шрифта принудительно меняется на 16.

    Вызывающая процедура проверяет, является ли интервал ячеек A1:B5 пустым. Если это не так, то интервал очищается и размер шрифта устанавливается в 16. Если же интервал ячеек пуст, то все ячейки интервала заполняются единицами и размер шрифта интервала ячеек равен 10.

    В обоих случаях шрифт ячеек интервала A1:B5 устанавливается в Times New Roman.

    Цветовое оформление объекта Range

    Свойство ColorIndex

    Свойство ColorIndex заливки (заливка - это объект Interior, который является вложенным для объекта Range ) рассматривает цвет как номер в палитре цветов рабочей книги. Всего в палитре 56 цветов.

    Пример

    В ячейках, начиная с активной, отображается палитра цветов рабочей книги.

    Переменные c и r содержат, соответственно, индекс столбца и индекс строки активной ячейки.

    Прямоугольный интервал из 56 ячеек (7 строк и 8 столбцов, начиная с активной ячейки) для отображения палитры задается переменной obj_range, содержащей ссылку на объект Range.

    Свойство Pattern (образец заливки) задается константой xlSolid, позволяющей установить заливку активных ячеек.

    (рис 8.11) Пример изменения свойства ColorIndex

    Свойство Color

    Свойство относится к объектам Border, Font или Interior (вложенные объекты для объекта Range ) и устанавливает цвет объекта в формате RGB. Свойство можно задать, используя функцию RGB, которая возвращает цвет в виде числа типа Long. Аргументы функции Red, Green, Blue определяют насыщенность соответствующей компоненты в устанавливаемом цвете и изменяются от 0 до 255.

    Например, оператор ActiveCell.Interior.Color=RGB(255, 0, 0) устанавливает красную заливку активной ячейки.

    Замечание

  • Не путайте свойство Color со свойством Colors! Последнее является свойством объекта Workbook и использует палитру цветов рабочей книги как массив значений цветов, например, оператор ActiveWorkbook.Colors(51) = RGB(255,0,0) меняет 51 цвет палитры активной рабочей книги на красный.
    •

    Чтобы использовать серый цвет разной интенсивности, установите равные аргументы функции RGB, например, выражение RGB(196,196,196) устанавливает 25% серую заливку. Чем больше значения аргументов, тем ближе серый цвет к белому.

    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^