Аннотация: Лекция посвящена описанию объектной модели MS Excel и подробному описанию методов, свойств и событий Application.
Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:
В общем виде программирование в Excel похоже на работу с Microsoft Word . Однако одно из главных отличий заключается в том, что в Excel рабочая область листа разбита на ячейки, каждая из которых имеет собственное имя. Имена ячеек могут быть двух видов.
Рис.
13.1.
Для выполнения большинства операций в MS Excel применяются следующие объекты.
Давайте начнем рассмотрение объектной модели MS Excel с объекта Application .
MsgBox Excel.Application.Name Листинг 13.1. Вывести имя приложения
После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel . Очевидно, что свойство Name объекта Application возвращает имя приложения.
Теперь рассмотрим наиболее важные методы и свойства Application . Некоторые из них похожи на таковые в MS Word . Например, метод Quit точно так же, как в Word , закрывает приложение , свойство Visible отвечает за видимость окна программы и т.д.
Этот метод, вызываемый для объекта Application , позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов (Range ). Например, код из листинга 13.2 . позволяет пересчитать все открытые книги.
Application.Calculate Листинг 13.2. Пересчитать все открытые книги
13-02-Excel GoTo.xlsm - пример к п. 13.3.2.
Позволяет выделить любой диапазон ячеек в любой книге, причем, если книга не активна - она будет активирована. Так же метод может запускать макросы Microsoft Excel.
Полный вызов метода выглядит так:
Goto(Reference, Scroll)
В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек , которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.
Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.
Например, такой вызов (листинг 13.3 .) позволяет выделить ячейку H500 на активном листе.
Application.Goto _ Reference:=ActiveSheet.Range("H500"), _ Scroll:=True Листинг 13.3. Выделить ячейку H500
Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application , то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo .
13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.
Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:
SendKeys(Keys, Wait)
Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш , для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.
Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS} . Для передачи нажатия кнопки Enter используйте значок ~ (
Sub MainProcedure()
Call GetProblemSize
Sub GetProblemSize()
ReDim Visited(Ncities)
ReDim Route(Ncities + 1)
Sub Initialize()
Dim I As Integer
Route(Ncities + 1) = 1
Visited(1) = True
For I = 2 To Ncities
Visited(I) = False
Синтаксис процедуры пользователя:
Sub ИмяПроцедуры (Параметры)
<Тело процедуры>
Элементы списка параметров имеют следующий вид: ИмяЭлемента As ТипДанных
7. Типы подпрограмм и их определения: определение и виды процедур. Примеры процедур разных видов. Логические фрагменты кода, которые выполняют определенную задачу, называются подпрограммами. В вба подпрограммами называют макросы, процедуры и даже функции. Функция – это подпрограмма, которая действует в пределах своего блока и возвращает только одно значение. Функции: 1)ф. пользователя 2) ф. модулей классов. Функция имеет следующий синтаксис:
Function Имя функции(список параметров) As ТипДанных
<тело функции>
Тип данных для функции предназначен для определения типа и возвращения функцией зн-я
Чтобы создать функцию пользователя, надо: 1. если нет модуля Insert/Module 2. Insert/Procedure 3. в открывшемся окне выбрать переключатель “Function”, в поле Name ввести имя функции («Доход») 4. в этом же окне установить переключатель «общие», чтобы был тип – Public 5. Ок. Откроется окно редактора. Ввести параметры функции и код.
Function Doxod (procent as double, platezh as variant, god as variant) as double
Dim i, j, n as integer, s as double
n=platezh.rows.count
s=s+platezh(i)/(1+procent)^((god(i)-god(1))/365)
Не закрывая окна View/Object Browser. Откроется окно просмотра объектов. В левом верхнем углу выбрать VBAProject и в окне Классы отобрать элементы текущего проекта. Выбрать модуль, в котором ваша функция. Затем в окне Компоненты отобрать все элементы, в том числе и функцию Доход. Щелкнуть правой кнопкой мыши на Доход и выбрать значение Свойства. Откроется окно «Параметры компонента», в нем ввести описание(назначение) функции. Закрыть окно и после этого ваша функция попадет в библиотеку стандартных функций Excel.
8. Объявление переменных. Объявление переменных в модулях и процедурах. Область действия переменных и процедур. Пример передачи в процедуру аргументов.
Dim I As Integer, j As Integer, k As Integer
Чтобы не забывать объявить переменные Tools/Options/ вкл Editor, где установить флажок Require Variable Declaration.
Option Explicit – общая область. Этот оператор будет контролировать описание переменных. Существует очень важный тип переменных, которого в VB не было. Это переменные объекта. Dim A As Object. Частный случай: Dim A As Range. Например, мы хотим обратиться к диапазону ячеек в течение процедуры. D
Dim SRange As Range
Set SRange=ActiveWorkBook.WorkSheets(“Продажи”).
Range(“SalesRange”)
SRange.Font.Size=14
Ключевое слово Set используется только при присвоении значения объектной переменной.
Для переменных определяется область видимости. Переменные могут быть: глобальные (для объявления используется Public) и локальные (используется оператор Private, Dim). Переменная типа Public – переменная уровня модуля, Dim – уровня процедуры. Переменная, определяемая в модуле оператором Dim, может быть переопределена этим же оператором внутри процедуры, принадлежащей этому модулю.
Передача в процедуру аргументов. Можно передавать аргументы (имя, фамилию) из процедуры Main в процедуру DisplayName. Тогда процедура Main называется главной, а DisplayName – вызываемой. В этом случае переменная FirstName и LastName не объявляются переменными уровня модуля, а объявляются как локальные переменные процедуры Main.
Sub Main ()
Dim FirstName As String, LastName As String, I As Integer
FirstName=Range(“Names”).cells(i, 1)
LastName= Range(“Names”).cells(i, 2)
Call DisplayName
Sub DisplayName
Dim FulllName As String
FulllName= FirstName+ LastName
MsgBox “Полное имя сотрудника”_&_ FulllName
9. Встроенное диалоговое окно сообщения. Пример функциии оператора MsgBox . Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно сообщений обозначается MessageBox (MsgBox) и окно ввода InputBox. Их можно рассматривать как фукнции и как оператор. Окно MsgBox- окно сообщения. Оно не требует проектирования и вызывается из программы командой MsgBoх и создается с помощью функции MsgBox(). Она имеет следующий синтаксис: MsgBox (Promt [, buttons] [,title,helpFile], ), где promt - обязательный параметр, эта строка в окне сообщения. Ее максимальная длина 1024 символа. Параметр Buttons не обязателен, его значение - целое число, равное сумме значений, определяющих наличие кнопок в окне сообщения. По умолчанию равно 0. Параметр title- заголовок окна сообщений. Help file – необязательный, ссылка на файл в справочной системе и на конкретное место в этом файле. пример
В зависимости от выбора кнопок окна MsgBox диалоговое окно возвращает одно из значений.
10. Встроенное диалоговое окно ввода. Пример функцииInputBox. Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно ввода информации InputBox требуется только как функция. Часто необходимо осуществить не только набор действия, но и ввести определенную информацию, которая будет восприниматься программой. Для этого используется функция InputBox. Синтаксис: InputBox (ptomt, , , , , context])
Promt – обязательная строка, которая выдается в окне сообщения, 1024 символа Title – необязательный, заголовок окна сообщения Default - строка в текстовом поле, если его нет, то строка пуста Xpos,Ypos- позиция левого верхнего угла ввода на экран Helpfile- ссылка на файл справочной информации. В этом окне всегда стоят по умолчанию две кнопки: ОК и Саncel. Пример:
InputBox("Введите цену для сравнения", "окно для ввода критерия")
13. Основные свойства и методы объекта Range. Примерыпрограммных кодов . Объект Range одновременно является и объектом и коллекцией. Рассмотрим наиболее важные свойства и методы объекта Range: 1. Свойство Address – возвращает адрес диапазона в виде строки. «В2:Р4» А=Range(“Sales”).Address “В2:Р4”
Range (“A1:A10”).Cells(3) ‘A3
Range (“A1:D10”).Cells(3, 4) ‘D3
Range (“A1”). Offset(3, 4) – E4
4. Font – выступает как в роли объекта, так и в роли свойства. В данный момент выступает как свойство, а как объект имеет свойства: Size, Name, Bold, Italic.
5. Horizontal Alignment – горизонтальное выравнивание ячеек в диапазоне. Свойства: xlCenter – по центру; xlLigh – по правому краю; xlLeft – по левому краю.
7. Name – возвращает имя диапазона. Это свойство позволяет задать имя диапазона в программном коде.
Range (“A1:D10”).Name=”Sales”
8. Value – возвращает значение в ячейку диапазона(используется относительно только одной ячейки диапазона).
Range (“A5”). Value=«Отчет о командировке»
Методы диапазона: Clear – удаляет содержимое и форматирование диапазона ячеек. ClearContents – удаляет только содержимое. Copy – копирует содержимое одного диапазона в другой. В этом случае он используется с единственным аргументом CopyDestination. C его помощью указывается, куда вы копируете.
Range(“B4:G25”). CopyDestination:=Range(“E4:F25”). Копируются формулы и содержимое. В случае, когда надо скопировать только числовые значения, а не формулы, используется метод PastSpecial. Сначала вы копируете в буфер обмена, затем переносите в диапазон. 1)Range(“B4:G25”).Copy 2)Range(“E4:F25”).PastSpecial Paste:=xlPasteValue
Select – выделение диапазона.
Sort – Применяется для сортировки диапазона ячеек. Key1 – по какому столбцу будет сортировка в диапазоне. Order1 – порядок сортировки(по возрастанию, по убыванию). Header – «да», «нет» (Если «да», то заголовок в сортировке участия не принимает, если «нет», участвует). Range(“A1:F25”).Sort
Key1:=Range(“C2”)
Order1:=xlAscending
Правила использования объектов Excel в програмном коде. Примеры программных кодов с их использованием.
14. Способы указания диапазонов в вба. Примеры программных кодов с указанием диапазонов. В процессе составления программного кода нужно уметь правильно использовать ссылку на диапазон. Рассмотрим следующие способы: 1. Использование адреса Range(“A1:B4”). 2. Применение имени диапазона Range(“Sales”). 3. Назначение переменной с именем диапазона NSales=Range(“Информация о продажах”).Name. 4.Использование свойства Cells Range(“A1:A10”).Cells(3) – A3
Range(“A1:D10”).Cells(3, 4) – D3
5.Настройка свойства Offset. Свойство имеет 2 аргумента. Когда указывается Offset для диапазона, то в роли диапазона выступает адрес только одной ячейки. Range(“A5”).Offset(3, 4) – E8
6. Указание левого верхнего и нижнего правого угла диапазона. Range(Range(“C1”), Range(“D10”)).
7.Применение свойства End. Для выделения диапазона относительно которого известен только левый верхний угол, можно воспользоваться свойством End. Оно указывает на нижний правый угол диапазона.
With Range(“A1”)
Range(.Cells(1, 1),.End(xlRight).End(xlDown)).Select
With Range(“A1”)
Range(.Offset(1, 1),.End(xlRight)).Name=”Sales”
Range(.Offset(2, 1),.End(xlDown)).Name=”Region”
Range(.Offset(2, 2),.End(xlRight)).End(xlDown).Name=”NameSales”
Работа с массивами. Оператор Option Base. Динамическая модель индексации и оператор ReDim. Примеры использования этих операторов. Функция управления массивами Array. Пример использования.
По аналогии с работой с информацией в списках Excel, массивы тоже представляют собой списки, в которых каждый элемент имеет свой индекс. Элементы массива воспринимаются программой в виде обычных переменных, но представленных в виде индексированного списка. В вба массивы используются для обработки списков, поскольку массивами управлять в программном режиме гораздо проще, чем списками. Правила работы с массивами. Option Base – определяет нижнюю границу изменения индекса в массиве. Например, Option Base1 – нижняя граница – 1. Эту строку в программном коде пишут в общей области модуля следом за оператором Option Explicit.
В большинстве экономических задач при написании программного кода невозможно заранее узнать количество элементов. Для этого существует возможность вначале программного кода не указывать при объявлении массива точного размера. (Dim ProdCode () As Integer, NProducts As
Integer.) Затем в теле процедуры, когда уже известно необходимое количество элементов массива, можно воспользоваться оператором ReDim, который выделяет для массива строго необходимый объем памяти. With ActiveWorkBook.WorkShits(«Продажи»).Range(“A3”)
NProducts=Range(.Offset(1,0),.End(xlDown)).
ReDim ProdCode(NProducts),UnitPrise(NProducts)
For i=1 to NProducts
ProdCode(i)=.Offset(i,0)
UnitPrise(i)=.Offset(i,1)
Оператор ReDim относительно конкретного массива может использоваться в программном коде столько раз, сколько необходимо. Единственной проблемой является то, что при таком использовании массива, все, что было в массиве, теряется. Чтобы этого избежать, нужно в записи оператора записать ключевое слово Preserve. (ReDim Preserve Sum(NS)). Обычно продолжением данного программного кода является размещение данных, взятых из ст. А и В списка в массивах ProdCode и UnitPrice. Для переноса данных из столбцов списка в массив, нужно организовать цикл. Если нужно перенести данные в столбцы списка, размещенного на рабочем листе:
(NFound – количество найденных записей)
For i=1 to NFound
With Range(“E3”)
Offset(j, 0)=ProdCodeFound(j) ‘код товара
Offset(j, 1)=Quontity(j) "количество товаров
Offset(j, 2)=DollarsTotal(j) ‘стоимость продукции
Рассмотрим конструкцию Array. Рассмотрим работу этой функции на примере. Данная функция используется для заполнения массива. Ее применении е рассмотрим в программе «Ипотечный кредит».
Sub Array Function ()
Dim Days As Variant
Days=Array(«Пн», «Вт», «Ср», «Чт», «Пт», «Сб», «Вс»)
MsgBox “Первый день недели: ” & Days(1)
Ключевое слово Array, после которого указывается список в скоках, используется для заполнения значениями переменной Days. Эта переменная – обычный массив, но в операторе Dim она указывается как обычная переменная типа Variant, а VBA самостоятельно с использованием функции Array определяет эту переменную как массив.
16. Модульная структура приложения на примере программного кода задачи, определяющий оптимальный маршрут перемещения дистрибьютора фирмы. Примериспользования переменных уровня модуля . При создании больших процедур возникают такие сложности, как сложность изучения, отладки, повторного использования. Намного предпочтительнее создавать модульные приложения, которые представляют собой набор относительно небольших процедур, каждая из которых выполняет небольшую подзадачу. Создается главная процедура MainProcedure, а из нее с помощью оператора Call вызываются другие процедуры.
Sub MainProcedure()
В качестве примера рассмотрим программный код задачи, определяющий оптимальный маршрут перемещения.
Dim Ncities As Integer, Visited() As Boolean, Route() As Integer, TotDist As Integer
Sub GenDistances()
Dim I As Integer, j As Integer, Ncities As Integer
With Range("DistMatrix")
Ncities = .Rows.Count
For I = 1 To Ncities - 1
For j = I + 1 To Ncities
Cells(I, j) = Int(Rnd * 100) + 1
For I = 2 To Ncities
For j = 1 To I - 1
Cells(I, j) = .Cells(j, I)
Sub MainProcedure()
Call GetProblemSize
Call PerformHeuristic
Call DisplayResults
Sub GetProblemSize()
Ncities = Range("DistMatrix").Rows.Count
ReDim Visited(Ncities)
ReDim Route(Ncities + 1)
Sub Initialize()
Dim I As Integer
Route(Ncities + 1) = 1
Visited(1) = True
For I = 2 To Ncities
Visited(I) = False
Sub PerformHeuristic()
Dim Step As Integer, I As Integer, NowAt As Integer, NextAt As Integer, MinDist As Integer
For Step = 2 To Ncities
For I = 2 To Ncities
If I <> NowAt And Visited(I) = False Then
If Range("DistMatrix").Cells(NowAt, I) < MinDist Then
MinDist = Range("DistMatrix").Cells(NowAt, NextAt)
Route(Step) = NextAt
Visited(NextAt) = True
TotDist = TotDist + MinDist
TotDist = TotDist + Range("DistMatrix"). Cells(NowAt, 1)
Sub DisplayResults()
Dim Step As Integer
For Step = 1 To Ncities + 1
Range("B19").Offset(Step, 0) = Route(Step)
MsgBox "Общее растояние:" & TotDist, vbInformation, "общее расстояние"
Использование переменных уровня модуля. Рассмотрим программу, которая иллюстрирует использование переменных уровня модуля.
На рабочем листе Excel создадим список и дадим ему имя – Name.
Dim FirstName As String, LastName As String
Dim I As Integer
FirstName=Range(“Name”).Cells(i, 1)
LastName=Range(“Name”).Cells(i, 2)
Call DisplayName
Sub DisplayName ()
Dim FullName As String
FullName=FirstName+LastName
MsgBox “Полное имя сотрудника: ” & FullName
17. Разраотка пользовательского интерфейса: форма как пользовательское окно диалога. Разработка формы на примере формы «Сведения о товарах». Отображение пользовательской формы. Пользовательское окно диалога – это форма, содержащая элементы управления, включая командные кнопки, переключатели, текстовые поля и другие. Оно обеспечивает пользователю ввод информации, требуемой для работы приложения.Рассмотрим типичный пример формы, которая часто встречается в приложениях.
В форме присутствуют 3 обработчика событий:
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumerList должен быть заполнен исходными данными. При работе со списком, его можно заполнить разными способами. Например, заполнить его данными, расположенными в ячейках рабочего листа. Для этого зоздать диапазон на рабочем листе, дать ему имя(Name - Costumer). При заполнении списка воспользоваться AddItem. Перед тем, как писать код для события Initialize, рассмотрим свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.
Dim cell As Variant
ProductBox = " "
MoscowOptions.Value = True
TrainOptions.Value = True
PerishBox = True
FragilBox = False
Существует другой способ заполнения списка. Свойство RowSource, откроется окно, из которого поочереди надо внести элементы.
Отображение формы. В отличии от VB в VBA форма в момент запуска приложения автоматически на экране не появляется. Для вызова формы на рабочий лист нужно применить метод Show. Для этого на рабочем листе создать кнопку, которая выводит форму на рабочий лист, и написать код:
Sub Кнопка1_Щелкнуть ()
Ипотека. Show
18. Обработка событий формы: создание кода обработки событий UserForm_Initialize, CanselButton_Click на примере обработки этих событий для пользовательской формы «Сведения о товарах».
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».
Код обработчика UserForm_Initialize.
В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumersList должен быть заполнен исходными данными. Для заполнения списка на рабочем листе создадим диапазон и дадим ему имя Costumers. В программном коде воспользуемся методом AddItem. Перед тем, как писать код для события Initialize, рассмотрим некоторые свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.
Private Sub UserForm_Initialize()
Dim cell As Variant
ProductBox = " "
MoscowOptions.Value = True
TrainOptions.Value = True
PerishBox = True
FragilBox = False
For Each cell In Range("Customers")
CustomersList.AddItem cell.Value
Для кнопки «отмена» запишем следующий код:
Private Sub CanselButton_Click ()
Unload.Me – этот метод выгружает форму из оперативной памяти и убирает ее с экрана.
19. Обработка событий формы: создание кода обработки события OkButton_Clickна примере обработки этого события для пользовательской формы «Сведения о товарах». Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.
Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:
1. UserForm_Initialize
2. OkButton_Click
3. CanselButton_Click
1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».
Код процедуры OkButton_Click. Обычно данный обработчик событий используется для сохранения данный, введенных в элементы управления. Как правило, введенная информация в элементы управления заносится в общедоступные переменные, которые определены в модуле, далее эти переменные используются в программных кодах модуля. ProductСode – код, введенный в поле; Region – пункт отправления; Shipping – транспорт; IsPerish – скоропортящийся; IsFragil – хрупкий; Customers – смисок.
Dim ProductCode As Integer, Region As String, Shipping As String, IsPerish As Boolean, IsFragile As Boolean, Customers As String
Private Sub OkButton_Click()
If .Value = " " or not IsNewmeric(.Value) Then
MsgBox "Код товара не введен или не числовой"
ProductCode=ProductBox. Value
If ProductCode < 1 Or ProductCode > 1000 Then
MsgBox "Код товара должен быть в диапазоне от 1 до 1000"
SetFocus ‘ставит курсор в поле
Exit Sub ‘выход из процедуры
If TrainOption.Value = True Then
Shipping = "Poezd"
Shipping = "Gruzovik"
If MoscowOption.Value = True Then
Region = "Moscow"
Region = "Voroneg"
IsPerish = PerishBox.Value
IsFragile = FragilBox.Value
With CostomerList ‘работа со списком
If .ListIndex <> -1 Then
Customers = CustomersList.Value
MsgBox "Элемент в списке не выбран"
20. Структура принятия решения If-Then-Else
Условный оператор If-Then-Else изменяет порядок выполнения про-граммы в зависимости от результатов проверки некоторого условия.
Sub LookUpPrice()
ReDim ProdCode(Nproducts)
ReDim UnitPrice(Nproducts)
For i = 1 To Nproducts
ProdCode(i) = .Offset(i, 0)
UnitPrice(i) = .Offset(i, 1)
ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")
Found = False
For i = 1 To Nproducts
If ProdCode(i) = ReguestedCode Then
Found = True
ReguestedPrice = UnitPrice(i)
Exit For
If Found Then
MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"
MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”
21. Дополнительное условие ElseIf
Средствами структуры принятия решенья If-Then-Else можно орга-низовать выполнение операторов в зависимости от соблюдения опреде-ленного условия. Рассмотрим другой вариант ее использования, на этот раз с ключевым словом ElseIf. Программа Proc31 отображает запрос на ввод пароля. Если пароль введен правильно, программа предоставляет пользо-вателю определенные возможности работы с рабочей книгой и сообщает ему об этом.
Sub Proc31_IfThenElseIf ()
Dim Password As String, ws As WorkSheet
Password = GetPassword
If Password = “level1” Then
For Each ws In ActiveWorkbook.WorkSheets
ws.Visible = True
MsgBox “Вы получили доступ ко всем листам книги.”
ElseIf Password = “level2” Then
ActiveWorkbook.Worksneets (1). Unprotect
MsgBox “Вы получили доступ только к первому листу рабочей книги.”
ElseIf Password = “level3” Then
ActiveWorkbook.Worksneets (1). Visible = True
MsgBox “Вы получили доступ только для чтения содержимого первого листа рабочей книги.”
MsgBox “Пароль не корректен. Повторите ввод пароля.”
Function GetPassword ()
В Proc31 ключевое слово Elself с последующим условием использу-ется дважды. Новое условие вносит в порядок выполнения команд допол-нительные изменения, если проверка первого условия закончилась неуда-чей. В Proc31 первое условие указано сразу за ключевым словом If. В нем проверяется равенство переменной Password строке ”levell”. Если пере-менная не равна строке, VBA переходит к первому ключевому слову Elself, где проверяет следующее условие – равенство переменной Password строке “level2”. Начиная с этого момента, программа использует только новое ус-ловие, “забывая” о старом. Если равенства опять нет, управление перехо-дит к следующему ключевому слову Elself и т. д.
В строке 3 процедуры Proc31 вызывается функция GetPassword, в ко-торой использованы новые элементы. Обратимся к ней еще раз.
Function GetPassword ()
GetPassword = Lcase (InputBox(“Enter Password:”.” Password”))
Здесь во второй строке заданы обращения к двум встроенным функ-циям VBA-Lcase и InputBox. Первая преобразует все буквы переданной в нее строки в строчные. Вторая выводит на экран диалоговое окно с запро-сом на ввод данных пользователем. У этой функции есть несколько необя-зательных аргументов, из которых нас интересуют только первые два. Один из них – строка, отображаемая в окне над полем для ввода. Второй содержит строку заголовка диалогового окна. При вызове функции Input-Box на экране появляется диалоговое окно с заданными заголовком и тек-стом приглашением, кроме того оно содержит пустое поле, в котором пользователь может ввести данные. Значение, возвращаемое InputBox, за-висит от того, какую кнопку щелкнет пользователь. Если это – ОК, воз-вращается введенная строка, если – Отмена (Cancel), то пустая. В нашем примере буквы этой строки передаются в функцию Lcase, которая преоб-разует их в строчные. В блоке, начинающемся сразу за ключевым словом If, помещен цикл For-Each-Next (он подробно описан ниже в разделе "Управляющая структура For-Each-Next”). В этом цикле перебираются все листы активной рабочей книги, с каждым из которых выполняются два действия – свойству Visible присваивается значение True и вызывается ме-тод Unprotect. Первое действие позволяет пользователю видеть лист, вто-рое – снимает с листа защиту паролем. У метода Unprotect есть один аргу-мент – строка с паролем. Но в данном случае пароль для защиты листов мы не использовали, поэтому Unprotect вызван без аргументов. В других бло-ках программы свойство Visible и метод Unprotect вызываются выборочно, в зависимости от введенного пароля.
22. Управляющая структура For-Next
Она позволяет выполнять несколько команд заданное число раз.
Sub LookUpPrice()
Dim ProdCode() As String, UnitPrice() As Currency, ReguestedPrice As Currency, Nproducts As Integer, i As Integer, Found As Boolean, ReguestedCode As String
With ActiveWorkbook.Worksheets("Коды товаров”).Range("A3")
Nproducts = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
ReDim ProdCode(Nproducts)
ReDim UnitPrice(Nproducts)
For i = 1 To Nproducts
ProdCode(i) = .Offset(i, 0)
UnitPrice(i) = .Offset(i, 1)
End With
ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")
For i = 1 To Nproducts
If ProdCode(i) = ReguestedCode Then
ReguestedPrice = UnitPrice(i)
MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"
MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”
23. Управляющая структура While-Wend
Действие ее подобно действию For-Next, но группа операторов вы-полняется не заданное число раз, а до соблюдения определенного условия. В программе Proc34 инструкция While-Wend использована для выделения определенного значения из последовательности случайных чисел.
Sub Proc34_WhileWend ()
Dim LotteryEntry As Integer
LotteryEntry = 0
While LotteryEntry <> 7
LotteryEntry = Int (10*Rnd ())
MsgBox “Ваше число равно “& LotteryEntry &”. Вы выиграли!!”
Программа Proc34 гарантирует, что в информационном окне всегда отображается заданное число. Цикл While-Wend выполняется, пока значе-ние переменной LotteryEntry не станет равным 7 (в условии использован оператор неравенства <>). При каждом выполнении цикла этой перемен-ной присваивается случайное значение от 1 до 9, а затем с помощью функ-ции VBA Beep, подается звуковой сигнал через внутренний динамик ком-пьютера. При запуске этой программы несколько раз, можно услышать разное количество сигналов, в зависимости от того, на каком шаге генера-тор случайных чисел вернет число 7.
Рассмотрим структуру цикла While-Wend подробнее.
While Ключевое слово, начало структуры While-Wend
LotteryEntry <> 7 Условие, определяющее, будет или нет вы-полнен цикл. Если оно соблюдено, цикл вы-полняется, если нет – управление передается оператору, стоящему перед ключевым сло-вом Wend
LotteryEntry=Int(10*Rnd()) Первый оператор тела
Beep Второй оператор тела
Wend Ключевое слово, отмечающее конец струк-туры While-Wend
Введение в систему программирования VBA. Объектная модель Excel, основные объекты Е. Краткая их характеристика.
VB и VBA не одно и то же. VB представляет собой язык для разработки программного обеспечения, которое используется отдельно от MS Office. VBA входит в состав пакета MS Office. Устанавливая его, вы получаете доступ к VBA. Он необходим для управления данными MS Office.
Язык VBA поддерживает все базовые элементы любого другого языка программ.: 1)переменные 2)массивы 3)подпрограммы 4)управляющие структуры 5)возможность создания пользоват. типов данных и т.д.
VBA явл. языком визуального и событийно управляемого программирования. В нем есть возможность создания форм с управляющими элементами, написания процедур, обрабатывающих события. VBA позволяет работать с огромным набором объектов. VBA поддерживается такими приложениями, как Access, PowerPoint, Word и др. В каждом из приложений свой набор объектов. В Access:формы, запросы, отчеты, таблицы; в Word:абзац, сноска; в PowerPoint слайд. Каждая программа поддерживает свою версию VBA.
VBA отличается от других языков программирования тем, что предоставляет возможность непосредственно работать со всеми объектами MS Office. Он имеет графическую инструментальную среду, позволяющую создать экранные формы и управляющие элементы.
Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны Range. Объект WorkSheets имеет свойство Name, только объект Range не имеет ярко выраженной коллекции. Выступает как коллекция и как объект. Чаще исп. В программном коде, обладает огромными возможностями.
2. Объектная модель Excel: понятие объекта, метода, свойства, события.
Объектная модель Excel является основой структуры программы. Без создания иерархической структуры объектов программы, нельзя использовать VBA в Еexcel.
Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны ячеек Range ckarts.
Свойства определяют внешний вид и состояние объекта. Для определения свойства его нужно указать справа от имени объекта через точку. Range(“A1”).Value – свойство описывает содержимое ячейки А1. Существует 2 вида опереций при работе со свойствами: 1)присваивание свойству значения. Объект.Свойство=<значение>
Range(“A1”).Value= «Отчет о командировке». 2)полученное свойство присваивается переменной. Переменная=Объект.Свойство
Var= Range(“A1”).Value
Методы объекта открывают задачи, которые может выполнять объект. Объект.Метод – когда у метода нет параметров. Range(“A1”).CleanContenst – очищает диапазон, но сохраняет форматирование. Если метод имеет аргументы, то они перечисляются через, после названия метода.
Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик. Суть программирования в VBA заключается в 2 понятиях: событие и отклик на него.
Свойства и методы могут быть связаны между собой: выполнение метода приводит к изменению свойства объекта, а изменение некоторого свойства может вызвать наступление события.
3. редактор VB. Окно проекта. Окно редактирования кода. Окно редактирования формы. Панель элементов. Окно свойств. Окно просмотра объектов. Окна Immediate, Watch. Редактор VB=Visual Basic Editor. Редактора VBA не бывает! Для запуска этого редактора нужно открыть приложение Excel и выполнить команду Сервис/Макрос/Редактор VB или Alt+F11. Чтобы вернуться в Excel, нужно повторно нажать Alt+F11. В открывшемся окне VBA: строка заголовка, меню, панели инструментов и несколько окон.
По умолчанию в верхнем левом углу находится окно проекта . Вывести его можно командой View/Project Explorer. Это окно содержит список всех открытых проектов и список всех их компонентов. В проекте всегда располагаются 3 модуля(на каждом листе) и модуль книги. Все модули делятся на: 1) стандартные – добавляются к проекту Insert/Module. Они содержат макросы, функции и процедуры. 2) модули объектов – модули, связанные с рабочими книгами, раблчими листами и формами. Проект VBA состоит из объектов, имеющих иерархическую структуру, и включает: объекты Excel, формы, стандартные модули и модули классов. Окно редактирования кода. В окне проекта выделяете объект, для которого будет введен программный код. Затем выполнить команду View/Code. Откроется окно, в которое нужно ввести программный код. Окно редактирования формы. Для вывода формы: Insert/User Form. На экране появится форма. Окно свойств. Для открытия: View/Properties Window. В левой части окна перечислены свойства объекта, в правой – значения свойств, которые можно изменять. Окно просмотра объектов. View/Object Browser или F2. В верхнем левом углу открывшегося окна – список библиотек объектной модели Excel. Оно дает спраку о всех объектах Excel, их свойствах и методах.
Панель элементов (View/Dwbug)в стандартном варианте включает различные классы управляющих элементов: CommandButton (командная кнопка), TextBox, Label и др. Существует возможность дополнить панель с помощью команды Tools/Additional Controls.
Окно Immediate – один из инструментов отладки. Предназначено для непосредственного ввода команд. Окно Watch – порядок выполнения программы и отслеживание ошибок.
4. Создание простой программы на примере приложения «Информация о продажах по регионам». Задача: создать программу, которая будет отслеживать продажи в каждом регионе. Для каждого региона отразить сообщение с количеством месяцев, когда продажи превышали заданный объем, введенный пользователем. Порядок выполнения: открыть Excel и на первом листе создать список следующего вида:
| А | В | С | … | G |
| месяц | Регион1 | Регион2 | Регион3 | |
| Янв2005 | ||||
| …. | ||||
| Дек2006 |
Отформатировать столбцы(1 – дата, остальные – формат денежный). Заполнить список. Затем выделить диапазон ячеек B2:G25 и присвоить ему имя SalesRange. Перейти в редактор вба: Alt+F11 или Сервис/Макрос/Редактор Вб. Если окна нет, выполнить команду View/Project Explorer. Далее выполнить Insort/Module, откроется окно редактора. Ввести следующий программный код:
Sub CountHighSales()
Dim i As Integer, j As Integer, ks As Integer, s As Currency
s = InputBox("Введите цену для сравнения", "окно для ввода критерия")
If Range("SalesRange").Cells(j, i) >= s Then
MsgBox "в регионе " & i & " объем продаж превышал " & s & " в " & ks & " месяцах"
Запустить программу на выполнение: F5 или Run/Run Sub. На экране появится окно InputBox. Запуск программы с помощью кнопки, расположенной на рабочем листе. Находясь на рабочем листе, выполнить команду Вид/Панели инструментов/Формы. В появившемся окне выбрать элемент Кнопка и нарисовать на рабочий лист. Откроется окно, в котором предлагается создать макрос, выбрать название CountHighSales и закрыть. Дать свое название кнопке. Для запуска программы щелкнуть на этой кнопке.
Создать программу можно и другим способом, начиная с размещения кнопки. Появляется окно «Назначить макрос объекту». Нажать на кнопку Создать. Запустится редактор VBE и откроется окно редактирования кода макроса. Ввести программный код.
5. Стандартные элементы управления VB: понятия, свойства, методы, события общие для этих элементов.Краткая характеристика элементов управления . Элементы управления - это объекты, которые можно разместить на форме. Как и все объекты, они имеют свойства, методы и события. Свойства определяют внешний вид и поведение.
Из всех офисных программ для создания пользовательских приложений чаще всего используется Excel. Этому есть два объяснения. Во-первых, данный пакет предназначен для широкого круга прикладных задач по обработке данных. Во-вторых, фактически именно с него началась история современного VBA. Всего несколько строчек кода, включенных в Excel, смогут создать программу для выполнения серьезных вычислений и оригинального анализа с использованием графики и выдачей отчетов. Однако для разработки собственных приложений необходимы следующие условия:
§ нужно научиться работать с Excel в его традиционном варианте, что позволит с помощью минимального программного кода использовать максимум встроенных возможностей пакета. Чем лучше вы знаете, сам продукт, тем проще создавать приложения;
§ требуется освоить иерархическую модель объектов Excel вместе с соответствующими свойствами, методами и событиями, через которые производится управление средой Excel при разработке пользовательского приложения;
§ следует изучить среду разработки VBA, где вы можете писать код программ, создавать пользовательские формы и отлаживать свое приложение.
Excel состоит из более чем 100 объектов - от ячеек и диаграмм до рабочих книг и электронных таблиц. Каждый объект имеет набор свойств, управляющих его внешним видом и поведением, а также содержит методы, обеспечивающие определенные действия с помощью этого объекта. Некоторые объекты имеют события.
Например, объект Workbook представляет собой конкретный файл Excel. Он имеет свойства Name (имя файла), Path (имя каталога) и Author (имя автора файла). Метод Save сохраняет файл. Вот как выглядит использование этих методов:
Существует два способа ссылок на объекты: можно сослаться непосредственно на имя одного из объектов или на индекс в коллекции. Согласно самому простому определению коллекция - это группа похожих объектов. Все объекты Excel разделяются на два класса: единичные объекты и объекты в коллекции. Для первых ссылка осуществляется непосредственно по их имени, для вторых - по индексу в данной группе. Коллекции предоставляют возможность иерархической организации объектов. Например, коллекция Workbooks содержит все объекты Workbook. Чтобы сослаться на конкретную рабочую книгу, можно указать имя:
Workbooks("Book1.xls")
или номер
Workbooks(1)
Четыре ключевых объекта
Ключевыми объектами в Excel являются Application, Workbook, Worksheet и Range, которые образуют иерархию:
Объект Application представляет собой саму программу Excel. Все приложения Excel/VBA реализуются в Excel, поэтому можно рассматривать этот объект в качестве среды, в которой они осуществляются. Любые установки свойств или вызовы методов, совершенные с объектом Application, воздействуют на весь Excel и соответственно на все приложения VBA, выполняемые в его среде. Например, используя объект Application, можно сделать невидимой строку состояния Excel и изменить его заголовок:
Application.DisplayStatusBar = False
Application.Caption = "Пользовательское приложение"
Объект Workbook является файлом рабочей книги Excel. В терминах разработки приложений его можно рассматривать в качестве механизма доставки или контейнера для любого приложения VBA, созданного при помощи Excel. Любые установки свойств или вызовы методов, совершенные с объектом Workbook, воздействуют на данное приложение. Например, для установки защиты и сохранения рабочей книги можно использовать такой код:
With Application.Workbooks(1)
Save
End With
Объект Worksheet, содержащийся в Workbook, служит нескольким целям в приложении Excel/VBA. Он содержит ячейки, куда пользователь может включать формулы для выполнения вычислений. При этом свойства и методы объекта Worksheet обрабатывают электронную таблицу как единое целое. Например, следующий код изменяет имя электронной таблицы(рабочий лист) и делает ее невидимой:
With Application.Workbooks(1).Worksheets(1)
Name = "Работа с ценными бумагами"
Visible = False
Объект Range представляет собой одну или несколько ячеек в электронной таблице. Он используется в основном для хранения и вывода фрагментов данных: чисел, строк или формул. Ячейки электронной таблицы, которые представлены в объекте Range, обладают широкими возможностями. Например, пользователь способен получить доступ из ячейки к более чем 400 встроенным функциям Excel и вызвать функции VBA. Можно также установить связи с другими ячейками в той же самой электронной таблице, других электронных таблицах или других рабочих книгах. Гибкость и мощность объекта Range позволяют совершенствовать встроенный вычислительный блок Excel и создавать более сложные приложения для анализа данных.
Объект Application
Объект Application –это главный (корневой) объект в иерархии объектов Excel, представляющий само приложение Excel.Он имеет большое число свойств и методов, позволяющих установить общие параметры приложения Excel.Перечислим основные его свойства и методы.
Основные свойства объекта Application
1 ActiveWorkbook, ActiveSheet, ActiveCell-возвращают активную,соответственно,рабочую книгу,рабочий лист и ячейку.В следующем примере в активной ячейке вводиться срока “Отчет”:
ActiveCell.Value=”Отчет”
2 Caption-возвращает или устанавливает текст из заголовка главного окна Excel.В следующем примере в качестве заголовка окна приложения устанавливается текст “Отчет за май”
Application.Caption=”Отчет за май”
3.DisplayFormulaBar-логическое свойство, регулирующее отображение строки формул.Например в следующем коде строка формул не отображается: Application.DisplayFormulaBar
4 DisplayStatusbar- логическое свойство, регулирующее отображение строки состояния
5 Path-возвращает путь к файлу Excel.exe
6 StatusBar- возвращает или устанавливает текст, выводимый в строке состояния
7 Version –возвращает номер текущей версии Excel.Используется для проверки применения корректной версии.Например:
If Application.Varsion <> “9.0” Then Exit Sub
Объект Workbook и семейство Workbooks
В иерархии Excel этот объект идет сразу после объекта Application и представляет файл рабочей книги.
Основные свойства объекта Workbook и семейства Workbooks
1 ActiveSheet – возвращает активный лист книги
2 Name –возвращает или устанавливает имя книги
З Path-возвращает полное имя папки, в которой находиться данная книга
4 Saved-логическое свойство, которое устанавливает, не производились ли изменения в книге со времени его последнего сохранения
Основные методы объекта Workbook и семейства Workbooks
1 Activate – активизирует рабочую книгу так, что ее первый рабочий лист становится активным.Например:
Workbooks(“Отдел кадров”).Activate
2 Protect- защищает рабочую книгу от внесения в нее изменений
3 Close –закрывает книгу
4 Open- открывает существующую книгу.Синтакис:
Open(FileName), где FileName –имя открываемого файла
Например:
Open(“Storage.xls”)
Основными событиями этого объекта являются Open (происходит при открытии книги).Например, напишем процедуру обработки события Open объекта Workbook при ее открытии.При этом мы выведем некоторое сообщение:
Sub Workbook_Open()
MsgBox(“Привет”)
Объект Worksheet и семейство Worksheets
В иерархии Excel объект WorkSheet идет сразу после объекта Workbook и представляет рабочий лист
Основные свойства объекта Worksheet и семейства Worksheets
1 Name- возвращает или устанавливает имя рабочего листа
2 Cells –Возвращает семейство всех ячеек рабочего листа или объект Range-конкретную ячейку, если в скобках указаны номер строки и столбца, на пересечении которых ячейка расположенна.Например:
Worksheets(1).Cells.Interior.ColorIndex=3 –окрашивает все ячейки первого рабочего листа в красный цвет.
Worksheets(1).Cells(1,2).Value=”Привет” –выводит строку “Привет” в ячейку,расположенную в первой строке во втором столбце
3 Columns, Rows- возвращают семейство всех столбцов и строк рабочего листа или конкретный столбец, если в скобках указан индекс.Например
WorkSheets(1).Columns(2)- возвращает второй столбец
Основные методы объекта Worksheet и семейства Worksheets
1 Activate- активизирует указанный рабочий лист.Например
Worksheets(1).Activate
2 Delete –удаляет указанный рабочий лист. Например
Worksheets(1).Delete
3 Protect - защищает рабочий лист от внесения в нее изменений
Основными событиями этого объекта являются Activate (происходит при активизации рабочего листа) и Deactivate (происходит при деактивизации рабочего листа).Например, напишем процедуру обработки события Activate объекта Worksheet при ее активизации.При этом мы выведем некоторое сообщение:
Sub Worksheet_Activate()
MsgBox(“Привет”)
Объект Range
В иерархии Excel объект Range (диапазон) , один из ключевых объектов VBA, следует сразу после объекта Worksheet. Объект Range не является элементом никакого семейства объектов.
Если в диапазоне указывается только имена столбцов и строк, то объект Range задает диапазон, состоящий из указанных столбцов и строк.Например, Range(“A:C”) задает диапазон состоящий из столбцов A,B,C. Range(“A:A”) задает диапазон состоящий из столбца А, Range(“2:2”) задает диапазон состоящий из второй строки.
Для того чтобы сослаться на конкретный диапазон ячеек достаточно указать адрес его верхней левой ячейки и адрес его нижней правой.Например, Range(“A1:B3”) задает диапазон состоящий из ячеек A1,A2,A3,B1,B2,B3.
Ячейка-это частный случай диапазона, который состоит из единственной ячейки.Например Range(“A1”) задает диапазон состоящий из ячейки A1.
Основные свойства объекта Range
1 Value- возвращает или устанавливает значение в ячейках диапазона.Например
x=Range(“C1”).Value - Некоторой переменной х присваивается значение из ячейки C1
2 Name- возвращает или устанавливает имя диапазана
3 Columns, Rows- возвращают все столбцы или строки в диапазоне.Например
Worksheets(1).Range("A1:B3").Columns.Value = 2 – во всех столбцах диапазона
A1:B3 выводится число 3
4 Interior- возвращает объект Interior, представляющий собой фон ячейки.Среди свойств объекта Interior
отметим только одно –ColorIndex-цвет фона, которое задается целыми числами от 1 до 56 .Например
Range(“A1”).Interior.ColorIndex=3 - Фон ячейки A1 заполняется красным цветом
5 Font –возвращает объект Font , представляющий собой шрифт.Объект Font имеет свойства Name(строка, указывающая имя шрифта) и т.д.
6 HorizontalAlignment- Горизонтальное выравнивание в ячейках диапазона.Допустимы значения xlCenter (Выравнивание по центру),xlRight(Выравнивание по правому краю) и т.д.
Основные методы объекта Range
1 ClearFormats и ClearContents- очищают, соотве-но, форматы и содержимое в ячейках диапазона.Например
Range(“A1:D2”).ClearContents
2 Select- выделение диапазона
©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-02-16
Простые и сложные переменные
По наличию внутренней структуры, переменные могут быть простыми или сложными (составными).
Простые переменные не имеют внутренней структуры, доступной для адресации. Последняя оговорка важна потому, что для компилятора или процессора переменная может быть сколь угодно сложной, но конкретная система (язык) программирования скрывает от программиста её внутреннюю структуру, позволяя адресоваться только «в целом».
Сложные переменные программист создаёт для хранения данных, имеющих внутреннюю структуру. Соответственно, есть возможность обратиться напрямую к любому элементу. Самыми характерными примерами сложных типов являются массив (все элементы однотипные) и запись (элементы могут иметь разный тип).
Следует подчеркнуть относительность такого деления: для разных программ одна и та же переменная может иметь разную структуру. Например, компилятор различает в переменной вещественного типа 4 поля: знаки мантиссы и порядка, плюс их значения, но для программиста, компилирующего свою программу, вещественная переменная - единая ячейка памяти, хранящая вещественное число.
Объектная модель MS Excel по общим принципам идентична объектной модели MS Word. Эта модель также имеет иерархическую структуру, в корне которой находится объект Application (Excel.Application), через который обеспечивается доступ к любой коллекции или внутреннему объекту приложения MS Excel или к компонентам открытых рабочих книг. Обшая структура объектной модели MS Excel представлена на рис. 1.4.
Рис. 1.4. Объектная модель MS Excel
Как уже сказано, вершиной объектной модели MS Excel является объект Application, непосредственно включающий такие объекты и коллекции, как Selection - текущий выделенный объект, WorkBooks - коллекция открытых рабочих книг, коллекции различных элементов управления, диалоговых окон и другие свойства приложения MS Excel. Объект Selection имеет свойства текущего выделенного объекта, поэтому нет смысла рассматривать здесь структуру модели этого объекта. Если выделена ячейка, то Selection = Ячейка, если диаграмма, то Selection = Диаграмма. Когда выделена область ячеек, Selection = Коллекции столбцов и строк. Коллекция рабочих книг представляет собой список открытых рабочих книг, доступ к которым осуществляется по индексу. Каждый элемент такой коллекции представляет собой объект "рабочая книга". Панели управления, меню, кнопки, диалоги и другие объекты, предназначенные для управления приложением, собраны в соответствующие коллекции элементов управления.
Основным элементом объекта "рабочая книга" является коллекция рабочих листов. Элементом коллекции является отдельный рабочий лист, который может представлять собой обычный лист (в виде таблицы) или быть в виде диаграммы. В последнем случае он ничего, кроме диаграммы, содержать не может. Рассмотрим вариант листа в виде таблицы (рис. 1.5).
Рис. 1.5. Объектная модель листа рабочей книги
Основной объект рабочего листа, с которым приходится работать, - ячейка. Ячейка как объект сама обладает множеством свойств и объектов, входящих в нее. Из них наиболее важными и часто используемыми являются: текст, шрифт, стиль текста, границы, заливка. Чтобы получить доступ к ним, необходимо получить доступ к самой ячейке, а затем изменять ее свойства. Ячейки объединены в области ячеек Range. Свойства области ячеек во многом совпадают со свойствами самой ячейки, но есть и отличия, состоящие в задании координат и размеров области. Ячейки объединены в строки и столбцы. Объединения строк и столбцов представляют собой коллекции, доступ к которым производится по числовому индексу или по буквенному обозначению столбца. На рабочем листе могуг располагаться внешние объекты: рисунки, фрагменты документов Word, звуки, видеозаписи и другие объекты, которые объединены в коллекцию внешних OLE-объектов. Прорисовка или, точнее, воспроизведение этих объектов полностью выполняется внешними программами, зарегистрированными в системе как OLE-серверы. Доступ к таким объектам производится через элементы коллекции OLEObjects, а доступ к их свойствам возможен только через эти OLE-серверы. Приложение Excel обладает большим набором собственных графических объектов, которые можно разместить на рабочем листе. Мы можем использовать рисунки, надписи, геометрические фигуры, диаграммы, которые обычно объединены в коллекции. Например, коллекция ChartObjects содержит набор диаграмм, которые располагаются на рабочем или на отдельном листе. Каждая диаграмма, в свою очередь, также содержит набор объектов и коллекций. Рассмотрение свойств, состава и содержания диаграмм - отдельная тема этой книги (см. главу 14).
Рис. 1.6. Объектные модели диалогов Найти в Excel (а) и Найти и заменить в Word (6)
Чтобы убедиться в гибкости, универсальности и больших возможностях для программирования объектов MS Office, рассмотрим еще одну коллекцию объектов, присутствующую как в Word, так и в Excel. Это коллекция диалогов (диалоговых окон), которые пользователь обычно открывает нажатием той или иной кнопки или выбором команды меню. Она принадлежит объекту Application. В объектной модели все диалоги представлены в виде элементов коллекции Dialogs, доступ к которым обеспечивается через числовой индекс. Посредством параметров метода Show элемента коллекции происходят передача параметров в диалог и его выполнение - такова модель диалогов для приложений Excel (рис. 1.6, а), для приложений Word модель диалога несколько отличается. Отличие заключается в том, что в диалог Word параметры передаются через свойства объекта-элемента коллекции (рис. 1.6, б).
У объекта Item() наряду с типичными свойствами и методами есть присущие только ему свойства и методы. Например, у диалога Найти и заменить есть свойство Find, определяющее текст для поиска - до запуска диалога.
В Excel объект коллекции Dialogs несколько отличается от диалогов Word. Здесь рассмотрим только объектную модель коллекции диалогов для Excel в целом (рис. 1.7).
Рис. 1.7. Объектная модель коллекции диалогов Excel
На этом рисунке представлена только малая часть всевозможных диалогов коллекции - показаны диалоги, наиболее часто используемые программистами и пользователями. Обычно в диалог передаются значения, которые устанавливают значения элементов управления, а возвращаются значения True или False, в зависимости от результата действия пользователя.
Можно подвести итог: приложения из состава MS Office, например Word и Excel, представляют собой взаимосвязанные объекты и коллекции объектов. Каждый объект или коллекция включает в себя множество параметров и других объектов, коллекций. В свою очередь, сами объекты и коллекции имеют хозяина (Parent), в состав которого они входят. На вершине объектной модели находятся объекты Word.Application для текстового процессора Word и Excel.Application - для табличного прцессора Excel. Через эти объекты, в основном, и осуществляется связь этих приложений с внешними программами. Как все это работает, вы узнаете из следующих глав книги.
Основой объектной модели Excel является объект Worksheet, представляющий один рабочий лист в файле. Каждый объект Worksheet является частью коллекции Worksheets, которая принадлежит объекту Workbook, представляющему файл рабочей книги Excel. Так как в приложении Excel одновременно может быть открыто несколько рабочих книг, существует также и коллекция Workbooks, содержащая по одному объекту Workbook на каждый открытый файл. Как и во всех приложениях пакета Office, объект Application находится в вершине иерархии объектов и доступен в качестве неявной ссылки при работе в Excel.
Существует еще один тип листов в рабочих книгах Excel - лист диаграммы (объект Chart) - особая таблица, которая содержит только диаграмму. Коллекция Charts объекта Application реализует доступ ко всем диаграммам всех открытых файлов, а коллекция charts объекта Workbook - только к диаграммам данной рабочей книги. Диаграммы также могут быть внедрены в рабочие листы вместо того, чтобы иметь собственный лист. Такие диаграммы не являются частью коллекции charts, а вместо этого доступны в составе коллекции Chartob-jects объекта Worksheet. Коллекция Sheets осуществляет доступ ко всем рабочим листам, как к таблицам, так и к диаграммам.
Большая часть того, что вы делаете в Excel, связана с объектом Range. Хотя имя этого объекта не отличается от своего двойника в приложении Word, их составы различны. Однако его функции практически те же: обрамление части документа, с которым производится работа. Диапазоном может выступать отдельная ячейка, двумерный блок ячеек на рабочем листе или трехмерный блок ячеек, охватывающий несколько рабочих листов.
Еще одной важной составной частью объектной модели Excel является объект Chart. Существует один объект chart, содержащийся в коллекции charts для каждой диаграммы в рабочей книге. Каждому объекту Chart подчинено множество объектов, представляющих составные части диаграммы, такие как оси и легенда.
Мы будем изменять наш макрос, зайдите в пункт меню "макросы", выберите наш и скажите "изменить":
Dim book As String
Dim sheet As String
Dim addr As String
book = Application.ActiveWorkbook.Name
sheet = Application.ActiveSheet.Name
Workbooks(book).Activate
Worksheets(sheet).Activate
Range("A1") = book
Range("B1") = sheet
Dim xList As Integer
xList = Application.Sheets.Count
For x = 1 To xList
s = addr + LTrim(Str(x))
Программирование на VBA можно рассматривать, как управление обьектами приложения. Вот именно обьектами и управляет наше приложение. В нашем случае, если упростить иерхическую архитектуру, то это выглядит так.
То есть главный обьект - приложение. В приложении могут быть несколько книг (Workbook), внутри которых находятся листы (Worksheets) и листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист. Вот я своим макросом и пытаюсь это выяснить. А заодно сколько листов в текущей книге.
DIM - обьявляет переменную с типом string. Используя обьект Application, мы получаем имена текущих книг и листа. С помощью Range("...") можно выделить ячейку и поместить значения в неё или считать. Вообщем обьекты имеют огромное количество свойств. Задача программиста на VBA знать эти свойства и методы.
