Поиск по сайту:
«BA Модель объектов Excel»
Файл: 4 КБ
Поделиться:
Необходимость применения Visual Basic для приложений в Excel продиктована тем, что в Excel имеется множество средств. Используя возможности Excel, например, по работе с диаграммами, финансовыми функциями и сводными таблицами, можно создать сложные приложения. VBA позволяет шить Excel новыми средствами и упростить его интерфейс.
Далее рассмотрим:
Модель объектов Excel Работа с основными объектами Excel Создание и использование формМодель объектов Excel
Из всех моделей объектов Microsoft Office модель объектов Excel является наиболее зрелой. Excel 5.0 был первым офисным приложением, в котором VBA использовался как язык разработки приложений. Модель объектов Excel совершенствуется в течение нескольких лет, а теперь она стала более удобной и полной. По сравнению с Excel 5.0 в Excel 97 добавлено несколько новых объектов.
Если вы работали с предыдущими версиями Excel, то некоторые объекты будут вам знакомы. Однако некоторые элементы, например, объекты WorksheetFunction, VBE и Assistant являются новыми.
В модели объектов Excel имеются более 100 объектов и семейств. Не следует пугаться этого. Вероятно окажется, что приблизительно 10 процентов объектов и семейств достаточно, чтобы выполнить 90 процентов всех возможных действий. Примерами наиболее часто используемых объектов и семейств объекты Application, Workbook и Workbooks, Worksheet Worksheets, а также объект Range. С помощью только этих объектов и семейств можно произвести огромное множество операций.
Использование объекта Application
Исходным элементом любой модели объектов Office является объект Application. Он представляет все приложение в целом. С помощью свойств и методов элемента Application, можно установить параметры работы программы, такие как задаваемые с помощью команды Сервис\Параметры (Tools\Options). В свойствах объекта Application, например, ActiveWorkbook, ActiveSheet и ActiveCell хранится информация об объекте, с которым в текущий момент работает пользователь. Кроме того, данный объект имеет полезные методы, например, метод Application.InputBox, который удобнее, чем стандартная функция VBA InputBox().
Использование свойств объекта Application
Как уже отмечалось выше, свойства объекта Application позволяют задавать общие параметры работы приложения Excel. Рассмотрим некоторые свойства элемента Application.
Свойства ActiveWorkbook, ActiveSheet и ActiveCell. В данных свойствах хранится информация об активном объекте. Активный объект - это объект, с которым в текущий момент взаимодействует пользователь. Значение свойства ActiveCell хранится в свойстве ActiveSheet, а величина свойства ActiveSheet - в свойстве ActiveWorkbook. Приведем пример использования свойства ActiveCell:
With ActiveCell .FontBold = True .Value = "Отчет о продажах" End With
Свойство Caption. В свойстве Caption объекта Application хранится имя, которое появляется в заголовке окна приложения Excel. Данное свойство доступно как для чтения, так и для установки. Если требуется скрыть, что приложение в действительности работает в Excel, то можно вместо значения "Microsoft Excel" указать другую строку:
Application.Caption = "Корпорация АБВ. Продажи"
Чтобы вернуть заголовок "Microsoft Excel", укажите ключевое слово Empty:
Application.Caption = Empty
Свойство Cursor. Свойство Cursor используется для задания внешнего вида указателя мыши во время выполнения макроса. При задании значения данного свойства можно указать одну из четырех констант Excel:
Константа XlDefault определяет использование указателя, заданного по умолчанию в приложении Excel. * Константа xlWait определяет использование указателя мыши в виде песочных часов. Рекомендуется присвоить свойству Cursor это значение в начале обработки данных (например, извлечения информации), чтобы сообщить пользователю, что ему требуется подождать завершения операции. Константа XlNorthwestArrow используется реже. Она определяет вывод указателя мыши в виде стрелки основного режима. Константа xlBeam используется также не очень часто. Она определяет вывод указателя мыши в виде стрелки выделения текста.Необходимо отметить, что после изменения свойства Cursor необходимо восстановить исходный вид мыши, присвоив свойству Cursor значение xlDefault, т. к. это не производится автоматически по завершении работы
Свойство DisplayAlerts. Если требуется отключить вывод встроенных подсказок и предупреждений при выполнении макроса, необходимо присвоить значение False свойству DisplayAlerts. В этом случае Excel производит стандартные действия, выбирая применяемый по умолчанию ответ на сообщения. Исходное значение свойства DisplayAlerts равно True.
Если макрос изменяет значение свойства DisplayAlerts, то перед окончанием его работы требуется присвоить данному свойству значение Тrue. В предыдущих версиях Excel это производилось автоматически, а в нас щей- необходимо делать вручную. Таким образом, чтобы использовать макросы предыдущих версий Excel в Excel 97, требуется во избежание нежелательного поведения приложения добавить в программу следующую строку кода:
Application.DisplayAlerts = True
Свойства DisplayFormulaBar, DisplayScrollBars и DisplayStatus:
Чтобы скрыть строку формулы, полосы прокрутки или строку состояния, требуется присвоить значение False свойству DisplayFormulaBar, DisplayScrollBars или DisplayStatusBar соответственно. И наоборот, для вывода требуемого элемента интерфейса, необходимо присвоить True соответствующему свойству. Значение по умолчанию для данных свойств равно True.
Свойство ЕпаblеСаnсelКеу. Обычно прерывание выполнения макроса осуществляется с помощью комбинации клавиш +. По умолчанию свойство EnableCancelKey равно значению xlInterrupt, которое допускает остановку процедуры пользователем для ее отладки или завершения. Если же требуется запретить прерывание выполнения, присвойте значение xlDisabled или xlErrorHandler свойству EnableCancelKey. Hеобходимо отметить, что константу xlDisabled следует использовать с особой осторожностью. Если свойство EnableCancelKey имеет данное значение, то выход из бесконечного цикла или прерывание макроса невозможно. Таким образом, перед установкой свойства требуется убедиться, что процедура полностью и тщательно отлажена.
Вместо константы xlDisable можно указать константу xlErrorHandler. Если свойство EnableCancelKey равно данному значению, то в выполняемой процедуре осуществляется переход к процедуре обработки ошибки. При использовании константы xlErrorHandler необходимо включить в макрос подпрограмму обработки ошибки, указав инструкцию On Error Goto. Номер ошибки, возникающей в результате нажатия комбинации клавиш <Ctrl>+<Break>, равен 18.
По завершении работы макроса свойству EnableCancelKey автоматически присваивается значение xlInterrupt.
Свойство ScreenUpdating. Одним из способов ускорить выполнение программы заключается в том, чтобы присвоить значение False свойству ScreenUpdating. Обычно этот метод применяется для макросов, которые отображают большое количество текста на рабочий лист. Если свойство ScreenUpdating равно False, то при выполнении макроса отключается на экран. В конце процедуры требуется присвоить значение True свойству ScreenUpdating.
В предыдущих версиях Excel свойству ScreenUpdating автоматически присваивалось значение True, а в настоящей версии это необходимо делать вручную. Таким образом, чтобы использовать макросы предыдущих версий в Excel 97, требуется добавить в конец макроса инструкцию, которая восстанавливает значение свойства ScreenUpdating.
Свойство StatusBar. Свойство statusBar позволяет задать текст строки состояния. Рекомендуется применять данное свойство совместно со свойством Cursor:
'Присвоить значение True свойству DisplayStatusBar, чтобы вывести строку состояния.
Application. DisplayStatusBar = True 'Установка текста строки состояния. Application.StatusBar = "Обработка, пожалуйста, подождите... " 'Замена обычного указателя мыши на песочные часы. Application.Cursor = xlWait 'Вызов процедуры GetSalesData 'Восстановление указателя мыши и строки состояния Application.Cursor = xlDefault Application.StatusBar = False
Свойтсво ThisWorkBook. В этом свойстве хранится ссылка на объект Workbook, являющийся рабочей книгой, в которой содержится выполняемый макрос. Значение этого свойства и свойства ActiveWorkBook не обязательно совпадают. Если в Excel открыто несколько рабочих книг, то возможно, что в свойстве ActiveWorkBook содерится ссылка на одну рабочую книгу, а в свойстве ThisWorkBook - на другую, которая и содержит выполняющийся макрос.
Использование методов объекта Application
В большинстве программ VBA, в которых используется объект Application, обычно изменяются свойства этого объекта. Однако также имеются несколько методов объекта Application, требующие детального рассмотрения.
Метод Calculate. Если в Excel установлен режим вычислений вручную, можно использовать метод Calculate объекта Application для принудительного выполнения вычислений во всех открытых рабочих книгах. Метод имеет следующий синтаксис:
Application.Calculate
Метод Goto. Метод Goto позволяет выбрать требуемый диапазон или процедуру VBA в любой рабочей книге. Отличие данного метода от рассматриваемого ниже метода Select заключается в том, что метод Goto активизирует связанную с диапазоном или процедурой рабочую книгу, если она не активна. Метод имеет следующий синтаксис:
Application.Goto Reference, Scroll
Параметр Reference - необязательный аргумент, который используется для указания требуемого диапазона в формате R1C1 или для задания имени процедуры VBA. Если данный параметр опущен, то подставляется последний диапазон, который указывался в методе Goto.
Параметp Scroll также не обязателен. Если он равен True, то после выполнения метода левый верхний угол заданного в методе диапазона выводится в левом верхнем углу окна.
Метод InputBox. Функция inputBox() рассмотрена выше. Метод ох хотя и кажется похожим, на самом деле имеет большие отличия. Функция InputBox() возвращает только строку. Метод InputBox имеет необязательный параметр type, который позволяет задать тип введенных пользователем данных. Метод имеет следующий синтаксис:
Application.InputBox(prompt, title, default, left, top, _ helpFile,helpContextID, type)
Параметр prompt - обязательный аргумент, определяющий сообщение, которое требуется отобразить в окне ввода. Параметр title - дополнительный аргумент, который задает заголовок окна. По умолчанию выводится заголовок "Ввод" (Input). Другим необязательным параметром является аргумент default. Он определяет значение по умолчанию, которое нужно вывести в окне. Параметры left и top - дополнительные параметры, которые определяют положение окна ввода по горизонтали и по вертикали относительно левого верхнего угла экрана. Параметры helpFile и helpContextID - имена файла справки и контекстного идентификатора в этом файле. Аргумент type определяет тип данных, возвращаемый методом. Если аргумент type не задан, то возвращается текст. В качестве значения данного apгумента можно использовать значения, перечисленные в таблице:
| Значение | Тип |
| 0 | Формула |
| 1 | Номер |
| 2 | Текст (строка) |
| 4 | Логическое значение (True или False) |
| 8 | Ссылка на ячейки, например, на объект Range |
| 16 | Значение ошибки, например, #Н/А |
| 64 | Массив |
Значения параметра type не являются последовательными, поскольку в программе можно использовать их комбинацию. Например, если необходимо задать ввод либо текста, либо чисел, следует присвоить данному параметру значение 1 + 2.
При нажатии кнопки ОК метод InputBox возвращает значение, введенное в окно, при нажатии кнопки Отмена (Cancel) - значение False. Приведем пример использования метода InputBox, в котором показано, как данный макрос реагирует на ввод текста или на нажатие кнопки Отмена:
Sub Input_example()
Dim hourly_wage As Currency
Dim num_of_hours As Single
Dim error_text As String
'Параметр Type получает значение 1 (число).
hourly_wage = Application.InputBox("Введите ставку почасовой_
оплаты:", "Почасовая _ оплата", 3.75, 1)
'Обратите внимание на использование значения False.
If hourly_wage = False Then
MsgBox "Операция отменена."
End
End If
num_of_hours = Application.InputBox("Введите количество_
отработанных часов:", _ "Отработанные часы", 40, 1)
If num_of_hours = False Then
MsgBox "Операция отменена."
End sub
Else
MsgBox "К оплате " & Format((num_of_hours *_ hourly_wage), "$##,##0.00")
End If
End Sub
Предупреждение
При проверке нажатия кнопки Отмена следует сравнить возвращаемый методом InputBox результат со значением False, а не со строкой "False".
Методы OnKey, OnRepeat, OnTime и OnUndo. В предыдущих версиях не поддерживались события. Чтобы устранить это ограничение, в объект Application были добавлены методы OnKey, OnRepeat, OnTime и OnUndo. Они обычно используются в процедуре Auto_Open, которая автоматически выполняется при открытии рабочей книги.
Метод OnKey выполняет заданную процедуру при нажатии определенной клавиши или комбинации клавиш. Метод имеет следующий синтаксис:
Application.OnKey Key, Procedure
Параметр Key - обязательный строковый аргумент, который определяет комбинацию клавиш или клавишу, которая назначена процедуре. В параметре Key можно указать также специальные клавиши:
Код клавиш для метода OnKey.
| Клавиша | Код |
| <Backspace> | {BACKSPACE} или {BS} |
| <Break> | {BREAK} |
| <Caps Lock> | {CAPSLOCK} |
| <Clear> | {CLEAR} |
| <Delete> | {DELETE} или {DEL} |
| <End> | {END} |
| <Enter> (цифровая клавиатура) | {ENTER} |
| <Enter> | ~ |
| <Escape> | {ESCAPE} или {ESC} |
| <F1>-<F15> | {F1}-{F15} |
| <Home> | {HOME} |
| <lnsert> | {INSERT} |
| < <-> | {LEFT} |
| <Num Lock> | {NUMLOCK} |
| <Page Down> | {PGDN} |
| <Page Up> | {PGUP} |
| < -> > | {RIGHT} |
| <Scroll Lock> | {SCROLLLOCK} |
| <Tab> | {TAB} |
Если требуется задать сочетание одновременно нажимаемых клавиш, используйте следующие:
Коды специальных клавиш для метода OnKey
| Специальная клавиша | Код |
| Alt или Option | % |
| Shift | + |
| Ctrl | ^ |
Параметр Procedure - необязательный строковый аргумент, который определяет имя процедуры, запускаемой при нажатии клавиши или комбинации клавиш. Если требуется отключить исполнение макроса при нажатии комбинации, присвойте данному параметру пустую строку (""). Если требуется восстановить исходную функцию сочетания клавиш, выполните OnKey, не указывая параметр Procedure. Приведем пример использования ОпКеу:
Назначение комбинации клавиш <Ctrl>+< > процедуре GetSalesData.
Application.OnKey "^{RIGHT}", "GetSalesData"
'Отмена назначения комбинации клавиш <Ctrl>+< >.
Application.ОпКеу "^{RIGHT}", ""
'Восстановление функции комбинации клавиш <Ctrl>+< >.
Application.OnKey "^{RIGHT}"
Метод OnRepeat определяет процедуру, которая выполняется при выборе команды Правка\Повторить (Edit\Repeat). Метод имеет следующий синтаксис:
Application.OnRepeat Text, Procedure
Параметр Text - обязательный аргумент, задающий текст команды Правка\Повторить. Параметр Procedure - обязательный аргумент, содержащий имя процедуры, которая выполняется при выборе команды Правка\Повторить.
Приведем пример использования данного метода. Следующая инструкция устанавливает текст команды Правка\Повторить равным "Повторить чтение данных по сбыту" и связывает процедуру GetSalesData с данной командой:
Application.OnRepeat "Повторить чтение данных по сбыту", "SaleData.xls!GetSalesData"
Метод OnTime позволяет назначить выполнение процедуры на заданное время. Метод имеет следующий синтаксис:
Application.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
Обязательный параметр EarliestTime - момент запуска процедуры. Параметр Procedure также обязателен. Он содержит имя выполняемой процедуры. Следует отметить, что для ввода значения параметра EarliestTime можно использовать функцию TimeValue().
Если на момент, заданный параметром EarliestTime, Excel не может запустить указанную процедуру из-за того, что выполняет другую операцию, то дополнительный параметр LatestTime определяет последний момент запуска этой процедуры. Если по прошествии указанного времени Excel так и не освободился, то процедура не исполняется.
Параметр Schedule - необязательный аргумент. Если он равен значению True (по умолчанию), то выполнение процедуры откладывается на сутки. Приведем пример использования метода OnTime:
'Выполнение процедуры GetSalesData по прошествии 1 минуты с текущего момента.
Application.OnTime Now + TimeValue ("00:01:00"), "GetSalesData"
'Выполнение процедуры UpdateSalesData в 14:00.
Application.OnTime TimeValue(" 14:00:00 "), "UpdateSalesData"
Метод OnUndo позволяет установить текст команды Правка\Отменить (Edit\Undo) и связать с этой командой процедуру. Синтаксис метода напоминает синтаксис метода OnRepeat:
Application.OnUndo(Text, Procedure)
Параметр Text устанавливает текст команды Правка\0тменить. Аргумент Procedure определяет процедуру, связанную с этой командой.
Метод Quit. Чтобы выйти из Excel, не запуская макросы Auto_Close, используется метод Quit объекта Application. При выходе выводится запрос на запись не сохраненных открытых рабочих книг. Метод имеет следующий синтаксис:
Application.Quit
Примечание.
Если требуется выйти из Excel, не отображая запрос на сохранение открытых рабочих книг, присвойте значение False свойству Application.DisplayAlerts. Однако при этом следует быть крайне осторожным. Не забудьте, что без сохранения закрываются все открытые рабочие книги, и не только открытые вашим приложением.
Использование событий объекта Application
В предыдущих версиях VBA у каждого объекта поддерживалось только одно событие, в большинстве случаев - событие Click. В VBA 5.0 объекты, включая элемент Application, имеют множество событий. Чтобы обработать событие с помощью объекта Application, необходимо создать новый модуль класса и объявить в нем переменную, используя ключевое слово WithEvents:
Public WithEvents oApp As Application
Ключевое слово WithEvents определяет, что переменная oApp используется для обработки события объекта. Данное ключевое слово допускается только в модулях класса и описывает только переменные уровня модуля. Теперь требуется связать описанный в модуле класса объект с элементом Application. Это можно сделать в любом модуле, указав следующий код:
'Инструкция Dim располагается в разделе описаний модуля.
'Имя EventClassModule - имя модуля класса.
Dim X As New EventClassModule
Sub InitializeAppEvents()
Set X.oApp = Application
End Sub
После выполнения процедуры initializeAppEvents объект оАрр в модуле пасса указывает на объект Excel Application, что позволяет использовать процедуры обработки в модуле класса при возникновении событий. Если необходимо включить или отключить обработку событий, в программе следует изменить значение свойства Application.EnableEvents. Если данное свойство имеет значение True, то процедуры обработки доступны.
Ниже описываются некоторые события объекта Application. Следует отметить, что именем объекта для события object_ИмяСобытия (например, object_NewWorkbook) - является имя переменной, которая описана с помощью ключевого слова WithEvents.
Private Sub object_NewWorkbook(ByVal Wb As Workbook)
Параметр Wb - ссылка на рабочую книгу, создание которой вызвало событие.
Событие WorkbookActivate. Событие WorkbookActivate возникает при активизации любой рабочей книги. Процедура обработки события имеет следующий синтаксис:
Private Sub object_WorkbookActivate (ByVal Wb As Workbook)
Параметр Wb - ссылка на рабочую книгу, активизация которой вызвало событие. Например, если требуется вручную произвести вычисления для всех открытых рабочих книг, то при активизации книги можно использовать следующую процедуру:
Private Sub oApp_WorkbookActivate(ByVal Wb As Workbook)
Application. Calculate
End Sub
Событие WorkbookBeforeClose. Событие WorkbookBeforeClose возникает непосредственно перед закрытием открытой рабочей книги. Синтаксис процедуры обработки является следующим:
Private Sub object_WorkbookBeforeClose(ByVal Wb As Workbook, _ ByVal Cancel As Boolean)
Параметр Wb - ссылка на рабочую книгу, которую требуется закрыть. Параметр Cancel имеет значение False при возникновении события. Если процедура обработки события присваивает значение True данному параметру, то рабочая книга не закрывается по завершении выполнения процедуры. Данное событие используется, например, для выполнения метода Calculate или для исполнения подпрограммы проверки условий на значение:
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, ByVal Cancel As Boolean) Application.Calculate
'Вызов внешней подпрограммы проверки условий на значение
ValidateEntries
End Sub
Событие WorkbookBeforePrint. Событие WorkbookBeforePrint возникает непосредственно перед печатью открытой рабочей книги. Синтаксис процедуры обработки является следующим:
Private Sub object_WorkbookBeforePrint(ByVal Wb As Workbook, _ ByVal Cancel As Boolean)
Параметр Wb - ссылка на рабочую книгу, которую требуется напечатать. Параметр Cancel имеет значение False при возникновении события. Если требуется отменить печать документа, присвойте данному параметру в процедуре обработки значение True.
Событие WorkbookBeforeSave. Событие WorkbookBeforeSave возникает непосредственно перед сохранением открытой рабочей книги. Синтаксис процедуры обработки является следующим:
Private Sub object_WorkbookBeforeSave{ByVal Wb As Workbook, _
ByVal SaveAsUi As Boolean, ByVal Cancel As Boolean)
Параметр Wb - ссылка на сохраняемую рабочую книгу. Если требуется отобразить диалоговое окно Сохранение документа (Save As), присвойте параметру SaveAsUi значение True. Параметр Cancel имеет значение False при возникновении события. Если требуется отменить сохранение документа, присвойте данному параметру значение True.
Событие WorkbookDeactivate. Событие WorkbookDeactivate возникает, когда открытая рабочая книга теряет фокус. Синтаксис процедуры обработки является следующим:
Private Sub object_WorkbookDeactivate (ByVal Wb As Workbook)
Параметр wb - ссылка на рабочую книгу, которая теряет фокус.
Событие WorkbookNewSheet. Событие WorkbookNewSheet возникает при добавлении нового листа в открытую рабочую книгу. Синтаксис процедуры обработки таков:
Private Sub object_WorkbookNewSheet(ByVal Wb As Workbook, _ ByVal Sh As Object)
Параметр wb - ссылка на рабочую книгу, в которую добавлен лист. Аргумент Sh - вновь созданный лист.
Данное событие можно использовать, чтобы удалить вновь созданный рабочий лист, если количество листов в рабочей книге больше заданного:
Private Sub object_WorkbookNewSheet (ByVal Wb As Workbook, _ ByVal Sh As Object)
If Wb.Worksheets.Count > 6 Then
MsgBox "Максимальное число листов в рабочей книге равно 5."
Wb.Sh.Delete
End If
End Sub
Событие WorkbookOpen. Событие WorkbookOpen возникает при открытии рабочей книги. Синтаксис процедуры обработки является следующим:
Private Sub object__WorkbookOpen (ByVal Wb As Workbook)
Параметр Wb - ссылка на открываемую рабочую книгу.