Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
Name рабочего листа Excel, на которую ссылается объектная переменная aSheet, присваивается переменной AnyStr: MsgBox InstBook.FullName В приведенном примере InstBook — это переменная, заданная для ссыл- ки на объект открытой рабочей книги. Если InstBook ссылается на рабочую книгу с именем Sales.xls в папке My Documents, то окно сообщения, вызываемое приведенным выше оператором, отображает строку "C:\My Documents\SALES.XLS". Чтобы задать свойство объекта, надо присвоить свойству новое значение, используя следующий синтаксис: ИмяОбъекта.Свойство=Выражение Пример изменения свойств объекта InstSheet.Name = "Первый квартал" В данном примере изменяется имя рабочего листа, на который ссылается объектная переменная InstSheet, присваивая значение свойству Name листа: В таблице 2 перечислены некоторые из наиболее употребительных или полезных свойств объектов в Excel. В таблице представлено имя свойства, тип и значение, а также объекты, которые имеют это свойство. Таблица 2 Свойство Тип/Что означает Где найти ActiveCell Object: активная ячейка в рабочем листе Application, Window ActiveChart Object: активная диаграмма Application, Window, Workbook ActiveSheet Object: активный лист Application, Window, Workbook Address Возвращает координаты ячейки указанного объекта Range Cells Диапазон объекта Range Application, Range, Worksheet Count Integer: число объектов в коллекции Все объекты коллекции Formula String: формула для ячейки рабочего листа Диапазон Name String: имя объекта Application, Workbook и в других объектах Path String: драйвер и каталог, в Addln, Application, 87 котором сохранен объект Workbook Saved Boolean: сохранялась ли рабочая книга после последних изменений Workbook Selection Object: текущий выделенный фрагмент Application, Window StatusBar String: сообщение в статусной строке Application ThisWorkBook Object: рабочая книга, из которой выполняется текущая процедура Application Visible Boolean: отображается или нет объект на экране Application, Worksheet, Range и в других объектах Value (варьируется): действительное значение, отображаемое в ячейке Range Использование методов объекта Методы объекта используются в операторах VBA так, как использовались бы любые встроенные процедуры VBA. Метод объекта имеет следующий синтаксис: ИмяОбъекта.Метод Для методов объектов, имеющих обязательные и необязательные аргументы, используется такой синтаксис: ИмяОбъетк.Метод Аргумент1, Аргумент2,… Во второй строке синтаксиса Аргумент1, Аргумент2и так далее представляют аргументы в списке аргументов метода. Необходимо перечис- лять аргументы метода в определенном порядке, отделяя каждый аргумент в списке запятой и включая отмечающие запятые {place holding commas) для пропущенных необязательных аргументов. Метод может иметь один или не- сколько аргументов в своем списке или не иметь их совсем; аргументы метода могут быть обязательными или необязательными. Еще один способ вызова метода заключается в использовании именованных аргументов и является, пожалуй, наиболее простым и очевидным. При вызове метода указываются названия аргументов, за которыми следует оператор := и сами передаваемые значения: ИмяОбъекта.Метод Аргумент1:=Значение1, Аргумент2:=Значение2, … Вызов метода с использованием именованных аргументов имеет два преимущества. Прежде всего, это наглядность кодов программы. Второе преимущество – в его простоте. С этом случае указывать необходимо только те аргументы, значения которых определяются вызывающей программой. Примеры использования методов объекта InstBook.Activate 88 ActiveWorkbook.SaveAs Filename:="D:\VBA2000\NEWFILE.xls", FileFormat:=xlNormal Многие объекты имеют методы, которые возвращают значения так же, как это делает функция. Чтобы использовать значение, возвращаемое методом, необходимо поместить список аргументов метода в круглые скобки и включить вызов метода в оператор присваивания или другое выражение, точно так же, как при использовании функции. Можно также игнорировать результат, возвращаемый методом. Чтобы игнорировать результат метода (если он имеет результат), вызовите метод без круглых скобок вокруг списка аргументов, как если бы метод не возвращал результата. Примеры использования возвращаемых значений методов объекта MsgBox myRange.Address MsgBox myRange.Address(, , xlRlCl) Метод Addressв Excel (который принадлежит объекту Range) возвра- щает адрес диапазона ячеек в рабочем листе как строку. Если переменная myRangeссылается на первую ячейку в рабочем листе, то оператор MsgBox в приведенной выше строке примера отображает строку $А$1. Хотя в этом примере метод Addressвызывается без каких-либо аргументов, он, на самом деле, имеет несколько необязательных аргументов. Эти необязательные аргументы определяют стиль адреса ячеек рабочего листа, возвращаемого методом, а также, являются ли координаты ячеек абсолютными или относительными. Во втором примере показан вызов метода Addressс его третьим необязательным аргументом (который определяет стиль возвращаемых координат ячеек): В таблице 3 приведены некоторые из наиболее употребительных или полезных методов в Excel. В таблице представлено имя, краткое описание метода и объекты, имеющие этот метод. Таблица 3 Метод Назначение Имеется в объектах Activate Активизирует объект Window, Workbook, Worksheet, Range и в других объектах Calculate Выполняет вычисления в открытой рабочей книге, рабочем листе или диапазоне Application, Range, Worksheet Clear Удаляет данные, сохраненные в указанном объекте Range Close Закрывает указанный объект Window, Workbook, Workbooks Save Сохраняет файл рабочей книги Application, Workbook SaveAs Сохраняет указанный объект в другом файле Workbook, Worksheet Select Выбирает указанный объект Range, Sheets, 89 Worksheets Многие методы имеют большое количество аргументов. Чтобы получить список параметров метода, можно воспользоваться системой Auto Quick Info. Эта система работает следующим образом: как только вы наберете строку, например ActiveSheet.SaveAs( система подсказки выдаст на экран список всех параметров метода SaveAs, как это обычно делается для обычных функций. Согласно выданному системой Auto Quick Infoвсплывающему окну метод SaveAsимеет довольно много аргументов, хотя все они — необязательные. Если интуитивно вам непонятно назначение параметров какой-либо функции в окне Auto Quick Info,обратитесь за помощью к справочной системе. Для этого либо выделите, например, строку ActiveDocument.SaveAsи нажмите клавишу F1. В справочной системе очень подробно приведено описание каждого метода, и следует почаще прибегать к ее услугам. Впрочем, Редактор VBA постоянно предлагает эту помощь в процессе вашей работы с ним. Объектные переменные В дополнение к типам Byte, Integer, Long, Single, Double и String VBA также имеет тип Object.Переменные или выражения типа Object ссылаются на объект VBA или на объект, принадлежащий приложению, например Excel-объекты Workbook, Worksheet и Range. Как и в случае с другими типами VBA, можно объявлять переменные в модулях, процедурах и функциях с определенным типом Object,что показано в следующем операторе: Dim myObject As Object Можно задавать переменную myObject, создаваемую предшествующим оператором Dim, чтобы она содержала ссылку на любой объект VBA или объект приложения. Если вы собираетесь использовать переменную типа Objectдля некоторых специфических типов объектов, можно также объявлять объектную переменную для этого специфического типа объекта: Dim InstBook As Workbook Объектную переменную InstBook, создаваемую этим оператором Dim, можно использовать только для сохранения ссылок на объекты Workbook; при попытке присвоить переменной InstBookссылку на объект Range или WorksheetVBA отображает сообщение об ошибке несовпадения типов. Аналогично, следующее предложение объявляет объектную переменную, которая может сохранять только объекты Document: Dim InstDoc As Document Объектное выражение (object expression) — это любое выражение VBA, которое определяет отдельный объект. Все объектные выражения должны вычисляться до единственной объектной ссылки {ссылки на объект); 90 объектные выражения используются с единственной целью — создание ссылок на специфические объекты в ваших программах VBA. Объектное выражение может состоять из объектных переменных, объектных ссылок или объектного метода или свойства, которое возвращает объект. Нельзя использовать переменные типа Objectили объектные выражения в арифметических, логических или операциях сравнения. Объектная ссылка, созданная с помощью объектного выражения или сохраненная в объектной переменной, в действительности, является только адресом, указывающим место в памяти компьютера, где сохранен объект, на который выполняется ссылка. Поскольку объектная ссылка — это адрес памяти, арифметические, логические операторы и операторы сравнения не имеют смысла. Перед использованием объектной переменной для ссылки на объект необходимо задать эту переменную, чтобы она содержала ссылку на нужный объект. Присваивание объектной ссылки объектной переменной отличается от присваиваний других переменных; чтобы присвоить объектную ссылку объектной переменной, используйте оператор Set. Оператор Set имеет следующий синтаксис: Set Переменная=Объект Переменная— это любая объектная переменная или переменная типа Variant. Объект — любая допустимая объектная ссылка; это может быть другая объектная переменная или объектное выражение. Если Переменная — переменная, объявленная с каким-либо определенным типом (например, Rangeили Workbook), этот тип должен быть совместим с объектом, на который ссылается Объект. Пример использования объектных переменных Dim InstSheet As Worksheet Set InstSheet = Application.ActiveSheet Чтобы задать отдельный объект в выражении или объектную переменную для ссылки на этот объект, используйте методы и свойства, возвращающие объекты, такие как свойства ActiveWorkbook и ActiveSheet объекта Application или метод Cells объекта Worksheet (в Excel). Аналогичные принципы применимы в Word: используйте свойство ActiveDocument объекта Application для получения ссылки на текущий документ и так далее. Ссылка на объекты с помощью With...End With При написании кода программы можно встретить ссылку на один и тот же объект в нескольких операторах. VBA предоставляет особую структуру — структуру With...End With, позволяющую ссылаться.на свойства или методы, которые принадлежат одному и тому же объекту, без задания всей объектной ссылки каждый раз. Структура With...End Withимеет следующий синтаксис: With Object 91 ' операторы, использующие свойства и методы Object End With Object — это любая допустимая объектная ссылка. Пример использования структуры With...End With. Dim FName As String 'имя файла-копии With ActiveWorkbook ' Сформировать новое имя файла из исходного FName - Left(.Name, InStr(.Name, ".") - 1) & "_bp2.xls" FName = .Path & "\" & FName .SaveCopyAs Filename:=FName End With End Sub Сравните этот же код без использования структуры With...End With Dim FName As String ' имя файла-копии ' Сформировать новое имя файла из исходного FName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) _ & "_bp.xls" FName = ActiveWorkbook.Path & "\" & FName 22 ActiveWorkbook.SaveCopyAs Filename:=FName End Sub Работа с коллекциями объектов и контейнерами объектов Коллекция (collection) объектов — это группа связанных объектов, таких как все рабочие листы в рабочей книге или все символы в параграфе. Объект в коллекции называется элементом (element) этой коллекции. Сама коллекция является объектом; коллекции имеют собственные свойства и методы. Каждая коллекция, например, имеет свойство Count, которое возвращает число элементов в коллекции. Если в активной рабочей книге имеется 16 рабочих листов, то следующее выражение вычисляется до числа 16: Application.ActiveWorkbook.Worksheets.Count В этом выражении Worksheets— коллекция всех рабочих листов в рабочей книге, ActiveWorkbook — свойство Excel-объекта Application, возвращающее активную рабочую книгу, a Count— свойство коллекции Worksheets, возвращающее общее число рабочих листов в коллекции. Это простое выражение помогает проиллюстрировать то, что одни объекты содержат другие объекты.. Контейнер (container) — это любой объект, содержащий один или несколько других объектов. В данном примере Applicationсодержит объект, на который ссылается ActiveWorkbook, содержащий в свою очередь, коллекцию объектов Worksheets. Все контейнерные объектные ссылки соединяются вместе с помощью точки- разделителя (.) для образования одного объектного выражения. Объект Application включает в себя коллекцию (семейство) объектов Книга (Workbook). Семейство книг называется Workbooks (в конец добавляется буква ―s‖ в английском языке это обозначает множественное 92 число). Обратиться с конкретной книге в приложении можно указав название семейства – Workbooks и в скобках имя или номер книги. Например, Workbooks(―Книга.xls‖) или Workbooks(1). Объект Workbook является частью семейства Workbooks, хотя семейство Workbooks тоже является объектом. Кроме того, существует еще объект ActiveWorkbook – активная книга, который тоже является свойством объекта Application. Объекты и ActiveWorkbook являются одиночными объектами. Объект Workbooks - семейство. Объект Workbook содержит набор свойств объектов, которые будут перечислены ниже: объект семейство (коллекция) Worksheets – это рабочие листы конкретной рабочей книги. объект семейство Sheets – это рабочие листы в активной рабочей книге. объект семейство Windows – это все окна в конкретной рабочей книге. объект ActiveSheet - это активный рабочий лист в активной рабочей книге. Объект Worksheet является рабочим листом - элементом семейства Worksheets, но чтобы обратиться к конкретному листу из семейства надо указать имя или номер рабочего листа в коллекции. Например Worksheets(‖Лист1‖) или Worksheets(2). Объект APPLICATION Объект Application - это главный (корневой) объект в иерархии объектов Excel, представляющий само приложение Excel. Он имеет огромное число свойств и методов, позволяющих установить общие параметры приложения Excel. Кроме того, объект Application через свойство WorksheetFunction предоставляет возможность использовать в коде все встроенные функции рабочего листа. Это семейство возвращает WorksheetFunction объект, являющийся контейнером всех функций рабочего листа. Например, в следующем примере находится максимальное знчение из диапазона А1:А4: Макс=Application.WorksheetFunction. Max(Range("А1:А4")) Функции рабочего листа можно включать в код непосредственно через объект Application, опуская свойство WorksheetFunction. Например, в следующем примере переменной Pi присваивается значение Пи, а переменной Сумма присваивается значение суммы из диапазона А1:А4: Pi = Application.Pi() Сумма = Application.Sum(Range("A1:А4")) Свойства объекта APPLICATION Свойство Описание ActiveWorkbook, ActiveSheet, ActiveCell, Activechart Возвращают активную рабочую книгу, лист ячейку, диаграмму. ActiveCell содержится в ActiveSheet, a ActiveSheet и ActiveChart в ActiveWorkbook. В следующем примере в активной ячейке устанавливается 93 полужирный шрифт и в нее вводится строка текста "Отчет за май": With ActiveCell .Font Bold = True .Value = "Отчет за май" End With ThisWorkbook Возвращает рабочую книгу, содержащую выполняющийся в данный момент макрос Может возвращать рабочую книгу отличную от возвращаемой свойством ActiveWorkbook, т. к. выполняемый макрос может находиться в неактивной книге. Calculation Устанавливает режим вычислений. Допустимые значения: - xlCalculationAutomatic (автоматически); - xlCalculationManual (вручную); - xlCalculationSemiAutomatic (автоматически, кроме таблиц); Caption Возвращает или устанавливает текст из заголовка главного окна Excel. Установка значения свойства равным Empty возвращает заголовок, используемый по умолчанию. В следующем примере первая инструкция устанавливает в качестве заголовка окна приложения текст "Отчет за 2000 год", а вторая возвращает окну имя, используемое по умолчанию, т. е. Excel Application.Caption = "Отчет за 2000 год" Application.Caption = Empty DisplayScrollBars Логическое свойство, регулирующее отображение полос прокрутки. DisplayStatusBar Логическое свойство, регулирующее отображение строки состояния.. Height и Width Высота и ширина окна приложения в пунктах. Left и Тор Расстояние в пунктах от левой границы окна приложения до левого и верхнего края экрана. Right Расстояние в пунктах от правой границы окна приложения до правого края экрана. StatusBar Возвращает или устанавливает текст, выводимый в строке состояния. В данном примере в строке состояния выводится "Ввод данных..." Application.DisplayStatusBar = True Application.StatusBar = "Ввод данных..." WindowState Устанавливает размер окна. Допустимые значения: - xlMaximized (максимальный); 94 - xlMinimized, (минимальный); - xlNormal (нормальный). Методы объекта APPLICATION Метод Описание Calculate Вызывает принудительное вычисление во всех открытых рабочих книгах, или специфицированном рабочем листе или диапазоне. В следующем примере первая инструкция приводит к перерасчету во всех открытых книгах, вторая инструкция - к перерасчету на указанном рабочем листе активной рабочей книге, а третья - в указанном диапазоне: - Application.Calculate - Worksheets("Отчет").Calculate - Worksheets("Отчет ").Range("A1:C10").Calculate Help Отображает справку. Синтаксис: Help(HelpFile, HelpContextID) - HelpFile - имя HLP-файла. Если значение этого параметра не указано, то отображается файл справки Microsoft Excel; - HelpContextID - номер раздела справки. Если значе ние этого параметра не указано, то отображается оглав ление справки. Application.Help "Notepad.hip" Volatile Вызывает перевычисление функции пользователя при изменениях в ячейках рабочего листа. Например, Function Квадрат(х) Application.Volatile Квадрат = х^2 End Function OnKey Устанавливает выполнение специфицированной процедуры при нажатии заданной комбинации клавиш. |