Научная работа

Visual Basic for Applications
Автор не известен - авторские права кафедра информатики ВГПУ

 

Введние.

Visual Basic for Applications (в дальнейшем просто VBA) — это объектно-ориентированный язык программирования, специально разработанный в свое время для записи макросов в приложениях. Впервые он появился в Microsoft Excel 5.0, а затем и другие приложения Microsoft Office, такие, как и PowerPoint, перешли на его использование. Идут разговоры и о том, что Microsoft лицензирует эту технологию другим компаниям так, чтобы они смогли включить VBA в свои приложения.

Таким образом VBA стал фактически стандартом языка макропрограммирования. Выгоды такого подхода очевидны: появление стандартного языка для макропрограммирования означает, что независимо от того, каким приложением вы пользуетесь, достаточно знать единый набор операторов и приемов программирования. Кроме того, это также способствует более тесному взаимодействию различных приложений, поскольку VBA "знает" команды и объекты, используемые каждым из приложений. С помощью VBA можно разрабатывать комплексные приложения, одновременно использующие те или иные компоненты нескольких приложений.

При этом одним из самых существенных преимуществ VBA является простота его использования. На первых порах удается обойтись вообще без всякого программирования: достаточно включить автоматическую запись выполняемых пользователем действий и в результате получить готовый макрос, а затем сопоставить ему кнопку на панели инструментов или новую команду меню, которые будут служить для вызова этого макроса. Не даром этот язык носит титул "Visual" — с его помощью можно создавать собственные диалоговые окна, просто размещая с помощью мыши соответствующие элементы управления (кнопки, поля ввода, раскрывающиеся списки, флажки или переключатели) в диалоговом окне. Точно так же можно дополнять такими элементами управления документы Word, рабочие листы Excel, формы Access, презентации PowerPoint.

VBA является полноценным языком программирования, позволяющим записать не только последовательно выполняемые пользователем действия, но и содержащим все необходимые конструкции языка программирования высокого уровня, включая разнообразные средства организации ветвлений, циклов и ведения диалога с пользователем. Весьма удобный редактор VBA позволяет не только писать и редактировать программы, но и вести их отладку. Короче говоря, в Microsoft Office имеется все необходимое для создания профессиональных приложений.

ОБЪЕКТНО-ОРИЕНТИРОВАННОЕ ПРОГРАММИРОВАНИЕ.

Объектно-ориентированное программирование(ООП) представляет собой новый этап развития современных концепций построения языков программирования.Здесь получили дальнейшее развитие принципы структурного программирования-структуризация программ и данных,модульность и т.д.

В основе ООП лежит понятие объекта(object),сочетающего в себе данные и действия над ними.Объект в некотором роде похож на стандартный тип –запись(record), но включает в себя не только поля данных, но также и подпрограммы для обработки этих данных, называемые методами. Таким образом, в объекте сосредоточены его свойства и поведение.

ООП характеризуется тремя основными свойствами: инкапсуляция, наследование, полиморфизм.

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

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

ООП обладает рядом преимуществ при создании больших программ.В частности, к ним можно отнести:

- Использование более естественных с точки зрения повседневной практики понятий, простота введения новых понятий;

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

- Возможность создания библиотеки объектов ;

- Возможность написания подпрограмм с различными наборами формальных параметров , но имеющих одно и то же имя ;

- Возможность разделения доступа к различным объектам программы и т.д.

Однако следует иметь в виду ,что ООП обладает и рядом недостатков и эффективно не во всех случаях. Неэффективно ООП применительно к небольшим программам ,поэтому его можно рекомендовать при создании больших программ ,а лучше даже класса программ. Можно сказать ,что ООП скорее не упрощает саму программу,а упрощает технологию ее создания .

ВВЕДЕНИЕ В МОДЕЛИ ОБЪЕКТОВ
Что такое объекты и модели объектов?

Объекты

Некоторые макросы или написанные пользователем процедуры могут просто манипулировать обычными переменными, например, функции могут выполнять вычисления, используя переданные значения аргументов, вспомогательные переменные и встроенные функции, а затем возвращать вычисленное значение в точку вызова. Однако чаще всего процедуры пытаются воздействовать на данные, хранящиеся в документе, рабочей книге или базе данных, изменяют текст, содержимое ячеек, графические объекты, диаграммы, или иным образом воздействуют на приложение, например, открывают или сохраняют документы, скрывают или показывают рабочие листы в книге Excel, и тому подобное. Такие изменяемые элементы приложения или документа (например, слова, абзацы, сноски или колонтитулы документа Word, ячейки, рабочие листы, диаграммы рабочей книги Excel, а также и сами приложения Office) называются в VBA объектами (Objects). Это, пожалуй, одно из ключевых понятий в VBA, и без его понимания обойтись просто невозможно. Остановимся на нем подробнее.

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

Объектом VBA считается некоторый элемент, который можно отобразить в окне приложения и, главное, на который можно воздействовать некоторым образом, изменяя его состояние. Например, диапазон ячеек рабочего листа можно увидеть в окне, и можно изменить его состояние, введя в ячейки этого диапазона данные, сменив цвет ячеек, используемый шрифт или иные характеристики. Таким образом, диапазон ячеек — это объект. Может возникнуть вопрос — что же из видимых в окне элементов не является объектом? Ведь приложения Office в такой большой степени поддаются настройке пользователем, что практически все, что вы можете увидеть в окне приложения, можно тем или иным образом изменить. Тем не менее существуют элементы окна приложения, не являющиеся объектами. Например, кнопки Свернуть окно и Развернуть окно не являются объектами. Вы можете пользоваться этими кнопками, но не можете изменить их. Напротив, само окно рабочей книги является объектом, поскольку оно может быть свернуто или развернуто с помощью этих кнопок.

Изменить состояние объекта в VBA можно одним из двух способов:

· изменив одно из свойств (Properties) объекта;

· выполнить некоторые действия, применив один из методов (Methods), ассоциированных с этим объектом

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

Объекты Excel

Объект

Описание

Application ( объект "Приложение") Этот объект представляет собой само приложение Excel в целом. Он включает в себя глобальные устанавливаемые параметры, такие, например, как используемый стиль ссылок на ячейки или режим проведения вычислений. Кроме того, он включает в себя встроенные функции Excel.
Windows
( семейство "Окна")
Объекты этого семейства используются при свертке или развертке окна, разбиении его на части и фиксировании подокон. Принадлежащий к этому семейству объект ActiveWindow представляет собой активное в настоящий момент окно.
Workbooks ( семейство
"Рабочие книги")
Объекты этого семейства определяют состояние рабочей книги, например: не является ли она доступной только для чтения; или какой из листов рабочей книги активен в настоящий момент. Принадлежащий к этому семейству объект ActiveWorkbook — это объект, который представляет собой активную в настоящий момент рабочую книгу.

Worksheets

(семейство
"Рабочие листы")

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

ActiveWorksheet - это объект, который представляет собой активный в настоящий момент рабочий лист.

Range


(объект "Диапазон")

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

Excel

объект. Принадлежащий к этому же классу объектов объект

ActiveCell - представляет собой активную в настоящий момент ячейку. Обратите внимание на то, что не существует такого объекта, как

Cell отдельно взятая ячейка представляет собой частный случай объекта Range.

При ссылке на объект в программе иногда приходится для уточнения задавать все объекты, лежащие на иерархическом пути к нужному объекту, при этом имена объектов отделяются друг от друга точкой. Например, чтобы присвоить значение 1 первой ячейке первого листа первой рабочей книги Excel, можно использовать следующий оператор VBA:

Application.Workbooks(1).Worksheets(1).Range("A1").Value = 1

В этом примере используются не только простые объекты, а также объекты, являющиеся семействами (Collections) — Workbooks и Worksheets (во встроенной справке на иерархическом дереве объектов простые объекты окрашены бирюзовым цветом, а объекты, являющиеся также и семействами — желтым). В таком случае в скобках после имени семейства указывается имя или индекс конкретного элемента семейства.

В любом приложении Office две основные составляющие:содержимое (content) и функциональные возможности(functionality).Содержимое-это документы,обрабатываемые приложением,и их элементы:слова,числа,графика;сюда же относится и информация об атрибутах отдельных элементов (размер окна документа,цвет изображения или размер шрифта того или иного слова).Под функциональными возможностями подразумеваются способы работы с содержимым-например:открытие,закрытие,добавление,удаление,копирование,вставка,редактирование или форматирование его элементов.

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

Объектом приложения верхнего уровня обычно служит объект APPLICATION,представляющий само приложение.Например,в модели объектов Microsoft Excel таковым является сам Microsoft Excel .Объект APPLICATION содержит другие объекты ,доступ к которым возможен только в тот период,когда существует объект-приложение(т.е.при выполнении приложения).Так,объект APPLICATION Microsoft Excel содержит объекты WORKBOOK.Поскольку существование объекта WORKBOOKS зависит от существования объекта APPLICATION,принято говорить,что объект WORKBOOKS-потомок объекта APPLICATION,и наоборот,объект APPLICATION-предок объекта WORKBOOKS.

У многих объектов-потомков у самих есть потомки.Например ,объект WORKBOOK Microsoft Excel содержит набор объектов WORKSHEET (или является предком );этот набор представляет все листы рабочей книги .У объекта-предка может быть несколько потомков .Например,у объекта WORKSHEET есть объекты-потомки: RANGE и др.По аналогии и у потомков может быть несколько предков .Например , объект WORKSHEET является потомком как объекта APPLICATION ,так и объекта WORKBOOK.

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

Каждый объект в иерархии включает в себя не только объекты более низких уровней ,но и их содержимое и функциональность .Чем выше объект в иерархии, тем больше его содержимое и шире функциональные возможности . Например,объект APPLICATION содержит размер окна приложения и позволяет завершить работу приложения,объект WORKBOOK содержит имя файла и формат рабочей книги и позволяет сохранить ее,а объект WORKSHEET содержит имя рабочего листа и позволяет удалить рабочий лист .

Зачастую невозможно добраться до того,что кажется содержимым файла. Например, до значений на рабочем листе Microsoft Excel,пока не пройдешь несколько уровней иерархии объектов ; все дело в том , что это специфическая информация относится к конкретной части приложения .Иначе говоря ,значение в какой-либо ячейке рабочего листа относится только к этой ячейке ,а не ко всем ячейкам рабочего листа ,и поэтому его нельзя хранить непосредственно в объекте WORKSHEET .Таким образом,содержимое и функциональность ,заключенные в объекте ,ограничиваются сферой его действия .

Объекты-наборы.

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

Можно также создавать и добавлять к набору новые элементы - обычно методом Add этого набора .

Число объектов в наборе можно определить через свойство Count. В следующем примере Microsoft Excel выводит сообщение ,если открыто более 3-х рабочих книг :

If Workbooks.Count >3 then MsgBox"More than 3 Workbooks are open"

Наборы полезны и для других целей: они позволяют выполнять операции над всеми объектами набора.

 

Свойства

В VBA, как и в реальной жизни, всякий объект обладает определенными свойствами (Properties), описывающими этот объект или его состояние.

Свойства и методы предназначены для доступа к содержимому и функциональности объектов.Например ,свойство Value объекта Range используется для просмотра значения в ячейке B3 на рабочем листе Sales из рабочей книги Current.xls :

Workbooks("current.xls").Worksheet("sales").Range("B3").Value=3

Свойство Bold объекта Font позволяет изменить начертания шрифта в ячейке B3 рабочего листа Sales на полужирное:

Workbooks("current.xls").Worksheet("sales").Range("B3").Font.Bold =true

Приведем еще один пример — в приложении Excel объект Range (диапазон ячеек рабочего листа) имеет такие свойства, как:

Font — используемый шрифт;

Formula — определяет содержимое ячейки или диапазона ячеек;

Value — определяет значение ячейки;

и многие, многие другие свойства.

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

объект.свойство

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

ActiveCell.Value

Более всего новичка может запутать тот факт, что некоторые свойства объектов сами в свою очередь могут выступать в качестве объектов. Так, например, объект Application имеет свойство ActiveWindow, которое показывает, какое окно является активным в настоящий момент. В то же время ActiveWindow, как уже упоминалось, является объектом семейства Windows, который может иметь свойство ActiveCell, в свою очередь являющееся объектом класса Range. Далее, ActiveCell имеет свойство Font, также являющееся объектом, имеющим такие свойства, как Name,

Size или Bold. Таким образом, возникает иерархия свойств-объектов, аналогичная иерархии "чистых" объектов.

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

ActiveWindow.ActiveCell.Font.Size

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

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

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

Объект Application

ActiveWindow

Активное окно

ActiveWorkbook

Активная рабочая книга

ScreenUpdating

Режим обновления экрана (при выполнении процедуры для ускорения работы можно приостановить обновление отображаемых данных, и снова включить обновление перед выходом из процедуры)

StandardFont

Имя стандартного шрифта для новых рабочих листов

Объект Workbook (элемент из семейства Workbooks)

ActiveSheet

Активный рабочий лист

FullName

Полное имя рабочей книги, включая путь

Name

Имя рабочей книги

Saved

Признак того, что состояние рабочей книги сохранено на диске (имеет логическое значение

False

,

если в книге были сделаны изменения)

Объект Worksheet

Name

Имя рабочего листа

OnSheetActivate

Имя процедуры, вызываемой при активизации рабочего листа пользователем

Previous

Предыдущий рабочий лист

ProtectContents

Режим защиты содержимого ячеек рабочего листа

Visible

Режим видимости рабочего листа (скрыт или показан)

 

Есть два способа использования свойств объектов: можно либо получить текущее значение свойства, либо изменить свойство (то есть задать новое значение для этого свойства). При изменении свойства необходимо указать сначала имя объекта, а затем имя свойства, использовав в качестве разделителя точку, затем следует оператор присваивания ( = ) и новое значение:

Объект.Свойство = новоеЗначение

В этом выражении новое значение может быть константой или формулой, возвращающей нужное значение, и принадлежать к одному из трех типов:

Числовое значение. Например, при установлении размера шрифта может использоваться следующий оператор:

 

ActiveCell.Font.Size = 14

Строка символов. Строковые значения заключаются в двойные кавычки. Вот пример изменения используемого шрифта:

ActiveCell.Font.Name = "Courier New Cyr"

Логическое значение. Обратите внимание, что в VBA используется обозначение True и False, в отличие от функций рабочего листа, где используется обозначение ИСТИНА и ЛОЖЬ. Вот пример изменения начертания шрифта (применение курсива):

ActiveCell.Font.Italic = True

Иногда бывает нужно проверить или сохранить текущее состояние свойства перед тем, как изменить его или выполнить какое-либо другое действие. Для этого используется следующий синтаксис:

переменная = Объект.Свойство

Например, следующий оператор запоминает состояние свойства защиты содержимого ячеек рабочего листа в переменной bPC:

bPC = ActiveSheet.ProtectContents

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

Методы

Кроме свойств (Properties), объект имеет еще и присущие ему методы (Methods). Если свойство объекта описывает некоторое состояние этого объекта, то метод описывает действия, которые над ним можно выполнить. Например, в рабочем листе — объекте Worksheet — можно произвести перевычисление всех содержащихся на нем формул с помощью метода Calculate.

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

Если аргументы вообще отсутствуют, то синтаксис вызова метода совпадает с ссылкой на свойство:

Объект.Метод

Например, для сохранения рабочей книги может использоваться следующий оператор:

ActiveWorkbook.Save

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

Объект.Метод(аргумент1, аргумент2, ...)

или

Объект.Метод аргумент1, аргумент2, ...

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

диапазон.Offset(rowOffset,columnOffset)

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

ActiveCell.Offset(0,1).Value

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

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

ActiveCell.Offset(columnOffset:=1).Value

Обратите внимание на то, что при этом для указания фактического значения аргумента используется оператор := , а не просто знак равенства.

Объект Application (Приложение)

Quit

Завершает Excel

Undo

Отменяет последнее выполненное действие

Объект Workbook (Рабочая книга)

Activate

Активизирует рабочую книгу

Close

Закрывает рабочую книгу

Save

Сохраняет рабочую книгу

Save As

Сохраняет рабочую книгу под другим именем

Объект Worksheet (Рабочий лист)

Activate

Активизирует рабочий лист

Calculate

Заново вычисляет значения в ячейках рабочего листа

Delete

Удаляет рабочий лист

Protect

Защищает рабочий лист

Unprotect

Отменяет защиту рабочего листа

Объект Window (Окно)

Activate

Активизирует окно

Close

Закрывает окно

Объект Range (Диапазон)

Clear

Полностью очищает диапазон (в том числе и форматирование ячеек)

ClearContents

Очищает содержимое ячеек диапазона

ClearFormats

Очищает форматирование ячеек диапазона

Copy

Копирует диапазон в буфер обмена

Offset

Возвращает диапазон с указанным смещением относительно первоначального диапазона

Paste

Вставляет содержимое буфера обмена в диапазон

Select

Выделяет диапазон

Sort

Сортирует значения в диапазоне

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

Семейством (Collection) в VBA называется совокупность однотипных объектов. Например, в Excel семейство Worksheets является совокупностью всех рабочих листов — объектов Worksheet — в данной рабочей книге, а семейство Lines — совокупностью прямых линий, нарисованных на данном рабочем листе.

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

Workbooks("ПОДПИСКА.xls").Close

закрывает рабочую книгу под именем ПОДПИСКА.xls.

А вот пример использования индекса элемента семейства. Оператор

ActiveSheet.Lines(1).Select

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

Замечание Нумерация элементов в семействе при использовании индексов начинается с единицы.

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

ActiveSheet.Lines.Delete

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

Как правило, индивидуальные объекты, являющиеся элементами семейств, имеют гораздо больше свойств и методов, чем соответствующий собирательный объект-семейство. Например, объект-семейство Workbooks в Excel имеет всего пять свойств (Application, Count, Creator, Item, Parent) и четыре метода (Add, Close, Open, OpenText), то время как объект Workbook имеет 59 свойств и 42 метода.

Не все объекты приложений могут группироваться в семейства — для некоторых индивидуальных объектов не существует соответствующих семейств.

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

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

Семейство

Описание

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

Workbooks

Все открытые в настоящий момент рабочие книги. С помощью метода

Open

можно открыть еще одну рабочую книгу. Метод

Add

создает новую рабочую книгу.

Sheets

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

Add

Copy,

Delete,

Select.

Worksheets

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

Sheets.

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

СВЯЗЬ МОДЕЛИ ОБЪЕКТОВ СПОЛЬЗОВАТЕЛЬСКИМ ИНТЕРФЕЙСОМ.

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

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

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

В Microsoft Office 97 предусмотрен, пожалуй, самый гибкий в настоящий момент интерфейс пользователя, и имеются разнообразные и мощные способы дополнения, изменения и настройки пользовательской среды. Можно автоматически записывать выполняемые пользователем действия и сохранять их в виде макросов на языке программирования VBA, а также изменять полученный код макросов или писать их самому с помощью редактора Visual Basic. Можно изменять систему раскрывающихся меню, дополнять имеющиеся меню новыми меню и командами, например, командами, предназначенными для выполнения созданных вами макросов. Можно изменять панели инструментов, перетаскивая кнопки с помощью мыши с одной панели на другую, создавать новые кнопки инструментов и назначать им макросы, а также изменять и создавать новые рисунки на кнопках. Наконец, можно изменять стандартные назначения комбинаций клавиш быстрого вызова для исполнения различных команд, а также создавать собственные комбинации клавиш быстрого вызова и назначать им созданные ранее макросы. Макросы

Что же такое макрос? Макрос — это программа, состоящая из списка команд, которые должны быть выполнены приложением. Как и командные файлы DOS, макросслужит для объединения нескольких различных действий в одну процедуру, которую вы можете легко вызвать. Этот список команд состоит в основном из макрооператоров, которые тесно связаны с командами приложения, в котором вы создаете макрос — т. е. с командами Word, Excel или других приложений Microsoft Office. Некоторые из этих макрооператоров выполняют специфические для макросов действия, но большая часть соответствует командам меню или параметрам, задаваемым в диалоговых окнах. Например, ActiveWorkbook.Save в Excel работает в точности как команда меню Файл /Сохранить

Автоматическая запись макросов

Вернемся к созданию макросов. Можно выделить три основных разновидности макросов.

Командные макросы — это наиболее распространенные макросы, обычно состоящие из операторов, эквивалентных тем или иным командам меню или параметрам диалоговых окон. Основным предназначением такого макроса является выполнение действий, аналогичных командам меню — т. е. изменение окружения и основных объектов приложения. Например, вставка или форматирование фрагмента текста в Word, изменение рабочего листа или рабочего пространства в Excel, сохранение или вывод на печать, и т. п. Короче, в результате выполнения такого макроса вносятся изменения либо в обрабатываемый документ, либо в общую среду приложения.

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

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

Типичный командный макрос имеет следующие элементы:

Операторы Sub и End Sub. Эти ключевые слова располагаются в начале и в конце макроса, как и все ключевые слова, выделяются обычно синим цветом.

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

Тело макроса. Часть макроса, заключенная между операторами Sub и End Sub, состоит из последовательности операторов, каждый из которых соответствует выполненному вами во время записи макроса действию.

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

ОБЪЕКТ APPLICATION.

Большинство свойств объекта APPLICATION управляет внешним видом окна Microsoft Excel или глобальными аспектами поведения этого приложения.Например, если видима строка формул, значение свойства DisplayFormulaBar равна True, а если отключено обновление экрана, то значение свойства ScreenUpdating равно False.

Свойства объекта APPLICATION обеспечивают доступ к "нижестоящим" объектам иерархии (к WINDOWS,WORKBOOKS ).Такие свойства называются аксессорами , они позволяют проходить по иерархии объектов от вершины (APPLICATION ) до объектов более низких уровней (WORKBOOKS и др.).

Некоторые свойства и методы, применимые к объекту APPLICATION, применимы и к объектам,расположенным на более низких уровнях иерархии.Использование подобных свойств или методов на уровне APPLICATION приводит к изменению всех открытых рабочих книг и листов.Например, метод Calculate применим к объектам APPLICATION,WORKBOOK и WORKSHEET. Выражение Application.Calculate приводит к пересчету данных на всех рабочих листах во всех открытых рабочих книгах, а применение этого метода к объектам WORKBOOK или WORCSHEET позволяет соответственно сузить масштабы пересчета.ОБЪЕКТ WORKBOOK.

Открывая или сохраняя файл в Microsoft Excel, мы фактически открываем или сохраняем рабочую книгу. Поэтому для работы с файлами в Visual Basic используются методы объекта WORKBOOK или набора WORKBOOKS.

ОТКРЫТИЕ РАБОЧЕЙ КНИГИ .

Рабочая книга открывается методом Open. Он всегда применяется для набора WORKBOOKS, ссылку на который можно получить через свойство WORKBOOKS. Вот как выглядит код, открывающий файл Book1.xls(в текущем каталоге) и показывающий значение из ячейки A1 на первом листе данной книги:

 

Sub OpenBook1()

Set myBook=Workbooks.Open(Filename:="Book1.xls")

MsgBox myBook.Worksheets(1).Range("A1").Value

End Sub

Заметим, что метод Open возвращает объект WORKBOOK, соответствующий только что открытой книге.

Есть два сравнительно безопасных места хранения рабочих книг, которые можно открыть программно.Первое-папка, где хранится исполняемый файл Microsoft Excel.Второе-папка Library, создаваемая при установке автоматически; она находится на один уровень ниже той, где хранится исполняемый файл Microsoft Excel. Чтобы открыть книгу, хранящуюся в папке, где расположен исполняемый файл, можно использовать свойство Path-оно возвращает строку, определяющую путь к данной папке.

Другое сравнительно безопасное место хранения рабочей книги-папка Library.Чтобы получить строку с путем к этой папке, используйте вместо свойства Path свойства LibraryPath.

СОЗДАНИЕ И СОХРАНЕНИЕ РАБОЧЕЙ КНИГИ.

Создать книгу позволяет метод Add набора WORKBOOKS. Значение, возвращаемое этим методом, присваивают объектной переменной, и тогда мы сможем ссылаться на созданную рабочую книгу в своей программе. При первом сохранении новой книги применяют метод SaveAs, а последующем-метод Save.

ЗАКРЫТИЕ РАБОЧЕЙ КНИГИ.

Для закрытия книги предназначен метод Close объекта WORKBOOK.

ОБЪЕКТ RANGE.

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

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

объект.Range(интервал)

где:

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

Range("A1:B10").ClearContents

Строка, описывающая диапазон, может содержать символы $, задающие абсолютный стиль ссылок, но эти символы игнорируются и никак не влияют на определение диапазона. Если метод применяется к объекту типа Worksheet, то получается абсолютная ссылка на указанный диапазон разделитель списка — запятая ","). Например, следующий оператор ссылается на диапазон A1:B10 активного рабочего листа и очищает его рабочего листа; если же метод применяется к объекту типа Range, то ссылка считается относительной. Например, если текущая активная ячейка — это ячейка B3, то оператор

Selection.Range("B1")

возвращает ячейку C3, поскольку ссылка B1 считается относительной для объекта Range (активной ячейки B3), возвращаемого свойством Selection. В то же время оператор

ActiveSheet.Range("B1")

всегда возвращает ячейку B1.

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

Range("Условия").Copy

копирует поименованный диапазон "Условия" в буфер обмена.

Другой синтаксис для метода Range использует два аргумента:

объект.Range(ячейка1,ячейка2)

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

МЕТОД CELLS.

Хотя метод Range и позволяет получить отдельную ячейку рабочего листа, однако чаще всего для этого используется другой, более удобный для этого конкретного случая метод — метод Cells. Этот метод возвращает в качестве объекта Range одиночную ячейку или семейство одиночных ячеек, а его особенное удобство заключается в том, что он использует стиль ссылок R1C1, т. е. числовые значения, определяющие номер строки и столбца, на пересечении которых находится интересующая вас ячейка, или же просто порядковый номер ячейки на рабочем листе (напомним, что в одной строке рабочего листа Excel располагается 256 ячеек). Использование числовых аргументов особенно удобно при организации циклической обработки ячеек некоторого диапазона, что позволяет использовать в качестве счетчика цикла и аргумента метода Cells одну и ту же переменную.

Итак, метод Cells имеет три вида синтаксиса:

объект.Cells(номерСтроки,номерСтолбца)

объект.Cells(номерЯчейки)

объект.Cells

Нумерация строк, столбцов и ячеек начинается с единицы, так что Cells(2,3) возвращает ячейку C2, а Cells(257) — ячейку A2. Третий вид обращения к методу Cells, без указания аргументов, возвращает семейство всех ячеек объекта.

Вот пример использования метода Cells:

For номерСтолбца = 1 To 4

Cells(1,номерСтолбца).Value = "Квартал " & номерСтолбца

Next

Этот цикл записывает в первые четыре ячейки первой строки текстовые значения "Квартал 1", "Квартал 2", "Квартал 3" и "Квартал 4".

СВОЙСТВО OFFSET.

При использовании метода Cells вы применяете абсолютные номера строк и столбцов. Однако иногда бывает нужно обратиться к ячейке, абсолютные номера строки и столбца которой не известны — нужно сделать относительную ссылку, например, обратиться к ячейке, расположенной на два столбца правее и одну строку ниже активной ячейки. Конечно, можно выяснить абсолютный адрес активной ячейки, а затем вычислить адрес интересующей вас ячейки, но для этого существует другой, более удобный способ: использовать еще один метод — Offset (смещение). Вот синтаксис, используемый для этого метода:

объект.Offset(смещениеСтроки,смещениеСтолбца)

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

Используемый объект должен принадлежать к классу Range, т. е. может быть ячейкой или диапазоном ячеек. Если это диапазон ячеек, то и результат применения метода Offset также будет являться диапазоном ячеек такого же размера, смещенного на указанное количество строк и столбцов. Например, оператор

Range("A1:C2").Offset(1,1).ClearContents

очищает содержимое диапазона ячеек B2:D3.

СВОЙСТВА CurrentRegion и UsedRange.

Эти два свойства очень полезны, когда программа работает с диапазонами, размерами которых Вы можете управлять. Текущий регион (CurrentRegion ) - это диапазон ячеек, ограниченный пустыми строками и колонками или сочетанием пустых строк, колонок и границ рабочего листа.

CurrentRegion - свойство объекта RANGE. На листе может быть несколько текущих регионов - все зависит от того, к какому объекту RANGE Вы обращаетесь.

Использованный диапазон (UsedRange ) ограничен левой верхней и правой нижней заполненными ячейками, наиболее удаленными друг от друга. В этом диапазоне содержатся все заполненные ячейки листа, а также расположенные между ними пустые ячейки. На листе может быть только один такой диапазон, и UsedRange является свойством объекта WORKSHEET, а не RANGE.

СВОЙСТВО Address.

У любого объекта RANGE есть свойство Address, которое возвращает адрес ячеек диапазона в виде строки.


ПРИМЕРЫ.

1.Создание и сохранение рабочей книги.

Sub CreoteAndSave ( )

Set newBook=Workbooks.Add

Do

fName=Application.GetSaveAsFilename

Loop Until fName<>False

NewBook.SaveAsFilename:=fName

End Sub


2.Закрытие рабочей книги.

Sub OpenChangeClose ( )

Do

FName=Application.GetOpenFilename

Loop Until fName<>False

Set myBook=Workbooks.Open(Filename:=fName)

‘здесь вносим какие-то измененияв myBook

myBook.Close Savechanges:=False

End Sub


 

3.Если ячейка содержит значение 0.01,то оно заменяется нулем.

Sub RoundToZero( )

For rwIndex=1 To 4

For colIndex=1 To 10

If Worksheets(“sheet1”).Cells(rwIndex,colIndex).Value=0.01 Then

Worksheets(“sheet1”).Cells(rwIndex,colIndex).Value=0

End If

Next colIndex

Next rwIndex

End Sub


4.Отформатировать список через свойство CurrentRegion.

Sub FormatRange( )

Set myRange=Worksheets(“sheet1”).Range(“A1”).CurrentRegion

MyRange.NumberFormat=”0.0”

End Sub


5.Скрыть каждую вторую колонку в использованном диапазоне на листе sheet1.

Sub HideColumns( )

Set r=Worksheets(“sheet1”).UsedRange

For Each col In r.Columns

If col.Columns Mod 2=0 Then

Col.Hidden=True

End If

Next col

End Sub


6.Макрос,который сортирует колонки и удаляет строки с одинаковымы данными.

Sub GoodRemoveDuplicaties( )

Worksheets(“sheet1”).Range(“A1”).Sort_

Key1:=Worksheet1(”sheet1”).Range(A1”)

Do While Not IsEmpty(CurrentCell)

Set nextCell=currentCell.Offset(1,0)

If nextCell.Value=currentCell.Value Then

CurrentCell.EntireRow.Delete

End If

Set currentCell=nextCell

Loop

End Sub.