5 что является объектами ms excel. Работа с типами данных в Microsoft Excel

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

Разговор об объектах Excel целесообразно начать с рассмотрения каркаса документа Excel . О важном понятии каркаса документа я много писал в предыдущих книгах . Многочисленные библиотеки объектов Office 2000, совокупность которых для программиста и представляют Office 2000, задают каркас всех документов, которые можно построить в этой среде. Когда создается новый документ, например, рабочая книга Excel , то по умолчанию из всей совокупности библиотек выбирается несколько, объекты которых и составляют каркас документа. Эти объекты доступны программисту, без каких либо дополнительных усилий. Центральную роль в каркасе документов Excel играют, конечно же, объекты библиотеки Excel . Но знание и всех других объектов, входящих в каркас, необходимо. Например, при программном создании интерфейса необходимо знание общих объектов библиотеки Office. Отмечу еще, что при желании программист всегда может расширить каркас документа, добавив в него те или иные библиотеки. Каркас, создаваемый по умолчанию в тот момент, когда открывается новая рабочая книга , состоит из объектов, входящих в состав следующих библиотек:

• Excel - библиотека, задающая основу документов Excel. Здесь хранится класс, задающий корневой объект Excel.Application, и все классы объектов, вложенных в корневой объект.
• Office - библиотека объектов, общих для всех приложений Office 2000. Здесь находятся классы, определяющие инструментальные панели - CommandBar и классы других общих объектов. Здесь же находятся классы, задающие Помощника (объект Assistant и все классы, связанные с ним). В частности, появился новый объект, которого не было в предыдущей версии - Мастер Ответов (Answer Wizard).
• Stdole - библиотека классов, позволяющая работать с OLE - объектами и реализовать Автоматизацию.
• VBA - библиотека классов, связанных с языком VBA. Здесь хранятся все стандартные функции и константы, встроенные в язык, классы Collection и ErrObject.
• VBAProject - проект по умолчанию, связанный с документом. Классы, которые могут создаваться программистом в этом проекте, методы, свойства, - все это доступно для просмотра, так же, как и объекты классов, встроенных в стандартные библиотеки.

Если сравнить каркас рабочей книги Excel , например, с каркасом документа Word , то они отличаются тем, что в основе одного лежит библиотека Excel , в основе другого - библиотека Word . Эти библиотеки содержат специфические для данных приложений объекты. Что же касается интерфейсных объектов, объектов определяющих среду редактора VBA , автоматизацию, то здесь используются общие объекты. Библиотеки Office, Stdole, VBA - это общие для всех приложений Office 2000 библиотеки. Об объектах этих библиотек я подробно рассказывал в .

Замечу, что хотя каркас документа Excel не изменился в Office 2000 в сравнении с предыдущей версией, вместе с тем в объектной модели произошли довольно существенные изменения, появились новые объекты, новые свойства и методы у ранее существовавших объектов.

Объектная модель Excel

Прежде всего, несколько слов о том, как устроена объектная модель Excel и других приложений Office 2000. В этой модели объекты связаны между собой отношением встраивания . На нулевом уровне иерархии существует некоторый центральный объект , в который встроены другие объекты, составляющие первый уровень иерархии. В каждый из объектов первого и последующих уровней могут быть встроены объекты следующего уровня. Так этот процесс продолжается. Таким образом, объекты в этой модели "толстые", поскольку в них встроено большое число других объектов. В особенности это касается объектов, стоящих на верхних уровнях иерархии.

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

Давайте перейдем к рассмотрению библиотеки объектов Excel 9.0 и начнем с центрального объекта этой библиотеки - Excel.Application .

Объект Excel Application

Объект Excel.Application задает приложение Excel. А посему свойства, методы и события этого объекта должны характеризовать приложение в целом. Понятно, что у этого объекта должно быть свойство Workbooks , возвращающее все открытые в приложении рабочие книги, свойство Windows , возвращающее открытые окна, свойства, такие как CommandBars , возвращающие объекты интерфейса, и другие подобные свойства. Методов и событий, характерных для всего приложения в целом, по-видимому, не так уж и много. Так что, казалось бы, структура этого объекта должна быть достаточно простой. Однако реально это не так, - у объекта Excel.Application очень большое число свойств, методов и событий, что не позволяет мне описать их полностью, да и нет в этом особого смысла. Объект Excel.Application , на мой взгляд, явно перегружен, многие его свойства и методы без всякого ущерба можно было бы исключить, поскольку они оперируют с объектами, стоящими на более низких уровнях иерархии и не имеют прямого отношения ко всему приложению в целом. Приведу лишь один пример. Первое по алфавиту свойство ActiveCell возвращает объект, задающий активную ячейку . Понятно, что речь идет об активной ячейке активной страницы активной рабочей книги. Непонятно только, зачем нужно было добавлять это свойство самому приложению. Вполне достаточно, чтобы им обладал объект WorkSheet , задающий страницу книги. Более того, если в момент вызова свойства ActiveCell нет активной страницы с ячейками, то возникнет ошибка, чего не происходит, если активную ячейку вызывает объект WorkSheet . Примеров подобной перегруженности объекта Application можно привести много. Я в своем описании объектов верхнего уровня не всегда буду упоминать такие свойства, полагая, что лучше рассказать о них там, где они необходимы по существу.

Общие объекты и Excel.Application

Давайте начнем рассмотрение со свойств объекта Excel.Application, возвращающих уже знакомые нам общие объекты:

Таблица 3.1. Общие объекты, доступные в Excel.Application

	Назначение объекта	Библиотека
Assistant	Помощник, позволяющий организовать собственную диалоговую систему.	Office
Answer Wizard	Мастер Ответов, стоящий за спиной Помощника. Может использоваться при создании собственной справочной системы.	Office
Com AddIns	Коллекция компонент, общих для приложений Office 2000.	Office
CommandBars	Коллекция инструментальных панелей, без работы с которой не обойтись при создании собственного интерфейса документа Excel.	Office
FileSearch	Объект, используемый при поиске файлов.	Office
Language Settings	Объект, задающий языковые предпочтения, общие для приложений Office 2000.	Office
Debug	Объект, используемый при отладке программных проектов.	VBA
VBE	Корневой объект при работе с программными проектами.	VBA

Все объекты, приведенные в этой таблице, играют важную роль при программной работе с документами Excel, как, впрочем, и с другими документами Office 2000.

Свойства - участники объекта

Рассмотрим теперь свойства - участники объекта Excel.Application , возвращающие объекты, специфические для Excel, Как я и предупреждал, я рассмотрю лишь основные свойства, которые действительно необходимы при работе с объектом Excel.Application .

Таблица 3.2. Основные свойства - участники

Свойство, возвращающее объект	Назначение объекта
WorkBooks	Коллекция открытых в Excel документов - рабочих книг. Основной объект, благодаря которому можно получить доступ к любому документу Excel и далее работать с объектами этой рабочей книги.
Windows	Коллекция открытых окон во всех рабочих книгах. Дело в том, что одну и ту же рабочую книгу часто полезно открывать в нескольких окнах, что позволяет видеть разные участки рабочей книги. Коллекция Windows позволяет получить доступ к каждому такому окну. Чаще всего, свойство Windows используется при работе с объектом WorkBook , для объекта Application это один из примеров той перегрузки, о которой я упоминал выше.
WorkSheetFunction	Объект - контейнер, в котором находятся многочисленные функции Excel, начиная от обычных математических функций и кончая функциями, применяемыми для решения задач статистики, прогноза, работы с датами и прочими.
AddIns	Коллекция компонент, расширяющих возможности решения специальных задач в Excel.
AutoCorrect	Знакомый по приложению Word объект, позволяющий задавать автоматическую корректировку набираемых текстов в ячейках Excel.
DefaultWebOptions	Объект, позволяющий устанавливать параметры для документов Excel, сохраненных в виде Web-страниц. Схож с аналогичным объектом Word.Application , но имеет свою специфику.
Dialogs	Объект Dialogs также как и три предыдущих объекта - AddIns, AutoCorrect , DefaultWebOptions относится к группе схожих объектов, встречающихся в каждом из приложений Office 2000, имеющих много общего, но имеющих и отличия, связанные со спецификой приложения. Также как и в Word, объект Dialogs задает коллекцию стандартных диалоговых окон, которые могут открываться в Excel, позволяя организовать диалог с пользователем.
Names	Одно из перегруженных свойств, возвращающее коллекцию всех имен, используемых для отдельных ячеек и областей всех открытых документов Excel. Чаще всего, это свойство используется при работе с отдельной рабочей книгой или отдельной страницей.
ODBCErrors	Коллекция объектов класса ODBCError . Элементы этой коллекции создаются автоматически источником ODBC-данных, если при выполнении запроса на получение данных возникли ошибки. Если ошибок не было, то и коллекция будет пустой.
OLEDBErrors	Коллекция объектов класса OLEDBError . Аналогично предыдущей коллекции, ее элементы появляются при наличии ошибок в процессе работы с базой данных, когда используется интерфейс OLE DB.
RecentFiles	Объект, относящийся к группе схожих объектов семейства Office 2000. Он задает коллекцию файлов, хранящих документы Excel последнего использования.

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

• Группа активных объектов - ActiveWorkbook, ActiveWindow, ActiveSheet, ActiveChart, ActiveCell, ActivePrinter , - возвращающих активную рабочую книгу, окно, активную рабочую страницу, диаграмму или ячейку, если таковые существуют в момент вызова соответствующего свойства. При отсутствии запрашиваемого активного объекта возникнет ошибка. Все эти объекты будут подробно рассмотрены, но чуть позже, когда мы спустимся вниз по иерархии объектов. Особняком стоит свойство, возвращающее активный принтер. Это свойство действительно имеет смысл связать с приложением. Заметьте, что объекты, стоящие на нижних уровнях иерархии, например, Workbook , этим свойством не обладают, так что добраться до принтера можно только через объект Application.
• Группа коллекций и объектов Range, входящих в состав соответствующего активного объекта - Sheets, Charts, Rows, Columns, Cells, Range - возвращающие соответственно коллекции рабочих страниц, страниц диаграмм активной рабочей книги, объект Range, содержащий все строки, столбцы, ячейки или заданную область активной рабочей страницы. Также как и в случае вызова объектов предыдущей группы, при вызове этих свойств следует быть осторожным, поскольку возникает ошибка, если нет соответствующего активного объекта.
• Свойство Selection возвращает выделенный объект в активном окне. Тип возвращаемого объекта зависит, от текущего выделения. Возвращается Nothing, если в активном окне нет выделенного объекта.
• Свойство ThisWorkbook возвращает текущую рабочую книгу, содержащую выполняемый макрос, один из операторов которого и вызвал это свойство. Это свойство представляет единственный способ добраться до рабочей книги, содержащей компонент AddIn, изнутри макросов, составляющих этот компонент.

Терминальные свойства

Терминальных свойств много, и понятно почему. Приложение Excel, как и другие приложения Office 2000, могут быть настроены пользователем по своему усмотрению. Эту настройку можно выполнять вручную, а можно и программно. Настройка вручную большей частью проводится из меню Сервис | Параметры, используя возможности, предоставляемые различными вкладками в открывающемся окне параметров. Для программной настройки используются терминальные свойства, - в этом их основное назначение. Естественно, я не буду останавливаться на всех свойствах, - они просты. В ниже приведенном обзоре представлено выборочное описание некоторых групп терминальных свойств:

• Группа свойств, задающих свойства приложения по умолчанию, - DefaultFilePath, DefaultSaveFormat, DefaultSheetDirection , - путь по умолчанию, формат по умолчанию, направление просмотра текста (слева направо или справа налево), задаваемое для некоторых языков. К этим же свойствам примыкает и ранее упоминавшееся свойство DefaultWebOptions .
• Группа булевых свойств, позволяющих включить или выключить отображение на экране тех или иных элементов приложения - DisplayAlerts, DisplayCommentIndicator, DisplayFormulaBar, DisplayStatusBar и другие Display-свойства. Первое из этих свойств позволяет управлять выдачей на экран некоторых сообщений в процессе работы макросов, второе - отображать специальный индикатор при показе комментариев. Более часто приходится использовать управление показом панелей формул и статуса. Особенно часто приходится использовать эти свойства, когда документ Excel используется в специальных целях, например, при отображении различных бланков, когда внешний вид документа ничем не напоминает привычную электронную таблицу. Замечу, что используемое в этих случаях свойство DisplayGridLines , позволяющее отключать сетку, принадлежит объекту Windows, а не объекту Application.
• Группа булевых свойств, позволяющих включить или выключить те или иные свойства - EnableAnimations, EnableAutoComplete, EnableCancelKey, EnableEvents, EnableSound . Первое из этих свойств позволяет управлять анимацией при добавлении или удалении строк и столбцов рабочего листа, второе - автозаполнением ячеек таблицы. Свойство EnableCancelKey не является булевым, оно принимает значения, заданные соответствующим перечислением, и позволяет управлять процессом прерывания программы при нажатии комбинации клавиш Ctrl+Break. Значение xlInterrupt, принятое по умолчанию, позволяет прервать выполнение макроса и перейти в режим отладки, где возможно пошаговое выполнение. Однако с помощью этого свойства можно задать разные режимы, как, например, передачу управления обработчику ошибок в момент прерывания. Пользоваться этим свойством следует осторожно, поскольку при зацикливании может возникнуть ситуация, когда нельзя будет прервать программу, не применяя грубых способов. Свойство EnableEvents позвол яет управлять включением событий объекта Application, а свойство EnableSound управляет включением звука в процессе работы приложений Office 2000.
• Группа свойств, управляющих размерами главного окна приложения Excel - Height, Width, Left, Top , задающие высоту, ширину окна и координаты верхнего левого угла окна.
• Многие другие свойства, позволяющие управлять курсором, скроллингом, характеристиками пользователя и многими другими параметрами так или иначе, характеризующими приложение Excel.

Методы объекта Application

Методов у объекта Excel.Application меньше, чем свойств, но и их около полусотни. Дадим краткий обзор, опять-таки, объединяя их по возможности в группы:

Public Sub RepeatAndUndo() "Создание пунктов Повторить и Отменить в меню Правка Call Application.OnRepeat("Hello", "Test") Call Application.OnUndo("7 to A1", "Write7") End Sub Public Sub Test() MsgBox ("Hi!") End Sub Public Sub Write7() Range("A1") = 7 End Sub

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

• Методы Repeat и Undo близки по духу к рассмотренным только что методам. Они позволяют повторить или отменить последнее действие пользователя при его работе вручную.
• Еще одним важным методом, позволяющим запускать макрос на выполнение, является метод Run(Macro, Arg1, Arg2, …) . Метод Run позволяет выполнить макрос (процедуру или функцию) проекта рабочей книги или функцию из DLL или XLL. Макрос, запускаемый на выполнение, может находиться в той же рабочей книге, что и макрос, вызвавший метод Run , но может принадлежать и другой рабочей книге. В этом случае, естественно, проекты должны быть связаны по ссылке и в проекте, который вызывает макрос другого проекта, должна быть установлена ссылка на вызываемый проект. При вызове макросу могут быть передано произвольное число аргументов, все они передаются по значению, так что, заметьте, нельзя передать макросу сам объект, а только его значение, задаваемое свойством Value . Метод Run в свою очередь возвращает значение, являющееся результатом выполнения макроса. Приведу простой пример, демонстрирующий все особенности вызова метода Run :

Проекту документа BookOne я дал имя BookOneProject . В этом проекте объявлена глобальная переменная

Option Explicit Public GlobalZ As Variant

В модуль с именем ModuleOne этого проекта я поместил описание процедуры PlusXY и функции Plus1 . Они выполняют простые и понятные без комментариев действия.

Public Function Plus1(ByVal X As Integer) As Integer Plus1 = X + 1 End Function Public Sub PlusXY(ByVal X As Integer, Y As Integer) GlobalZ = X + Y End Sub

В этом же модуле находится и процедура testrun , демонстрирующая вызовы метода Run .

Public Sub testrun() "Запуск на выполнение функции и процедуры, "находящихся в том же проекте Dim z As Integer z = Application.Run("Plus1", 7) Debug.Print "z = ", z z = Application.Run("PlusXY", 5, 7) Debug.Print "GlobalZ = ", GlobalZ, "z = ", z End Sub

Вот результаты ее выполнения:

z = 8 GlobalZ = 12 z = 0

В проекте другой рабочей книги Excel с именем BookTwo я установил ссылку на проект BookOneProject и в один из модулей поместил процедуру testrun1 , вызывающую макросы проекта BookOneProject :

Public Sub testrun1() "Запуск на выполнение функции и процедуры, "находящихся в другом проекте BookOneProject, "на который установлена ссылка. Dim z As Integer z = Application.Run("BookOneProject.Module1.plus1", 7) MsgBox ("z= " & z) Call Application.Run("BookOneProject.Module1.plusXY", 5, 7) MsgBox ("GlobalZ = " & BookOneProject.GlobalZ) End Sub

И в этом варианте метод Run успешно справляется с вызовом макросов другого проекта. Конечно, в данном примере вместо того, чтобы применять метод Run , можно было бы непосредственно вызвать ту же функцию Plus1 . Но, надеюсь, Вы понимаете, что истинная ценность метода Run в том, что имя выполняемого макроса может быть передано ему в качестве параметра, так что в зависимости от ситуации он может запускать разные макросы. Но давайте закончим с примером и вернемся к рассмотрению других методов объекта Excel.Application .

• Метод Goto(, ) не выполняя макроса, позволяет перейти к его рассмотрению. Другое, может быть, основное назначение метода состоит в том, чтобы перейти в заданную точку рабочей книги Excel. Чтобы перейти к рассмотрению макроса, параметр Reference должен быть строкой, задающей имя макроса. Для перехода в заданную область документа параметр Reference задается объектом Range. Булев параметр Scroll , имеющий значение true , обеспечивает прокрутку области так, чтобы заданная точка находилась в левом верхнем углу области просмотра. Главное, на что стоит обратить внимание, - метод Goto позволяет осуществлять переходы между документами. Вот пример макросов из документа BookTwo , осуществляющих соответственно переходы к заданной области и макросу документа BookOne .

  Public Sub GotoRange() "Переход к заданной области другого документа Application.Goto Workbooks("BookOne.xls").Worksheets("Лист1").Range("A20"), True End Sub Public Sub GotoMacro() "Переход к заданному макросу в другом проекте Application.Goto "BookOneProject.Module1.testrun" End Sub

• Метод MacroOptions (, , , , , , , , , ) - это еще один метод, связанный с макросами. Он позволяет задать для макроса, указанного первым параметром, различные характеристики - описание, горячие клавиши, раздел справки, связанный с данным макросом, и другие свойства.
• Метод RecordMacro(, ) - также предназначен для работы с макросами. Он позволяет добавить некоторый программный код в макрос, создаваемый инструментом MacroRecorder. В момент вызова метода MacroRecorder должен быть включен и записывать макрос в модуль, не являющийся активным, другими словами, нельзя произвести запись в тот модуль, макрос которого вызвал метод RecordMacro .
• Метод Wait(Time) As Boolean - это последний из описываемых мной методов объекта Excel.Application , входящих в большую группу методов, предназначенных для работы с макросами. Он позволяет организовать задержку вычислений на заданное время, указанное параметром метода. В приведенном ниже примере метод используется, чтобы открыть и показать пользователю некоторую форму, а затем закрыть ее по истечении заданного времени. Этот прием можно использовать в играх, целью которых является проверка внимательности. Вот текст соответствующего макроса:

  Public Sub WaitSomeTime() "Открывает форму на ограниченное время MsgBox ("Форма будет показана на 10 секунд!") FlyForm.Show Application.Wait (Now + TimeValue("0:00:10")) FlyForm.Hide End Sub

Взгляните, как выглядит сама форма.

Рис. 3.1. Форма FlyForm, открытая на "мгновение"

Привожу рисунок этой формы только для того, чтобы пояснить, какая цель преследуется в этом примере. Я предполагал, что при открытии формы пользователь должен успеть в предоставленное ему время ввести два числа в поля X и Y, нажать кнопку, производящую вычисления и запомнить полученный результат. Однако мои намерения не осуществились, и вот по каким причинам. Если форма имеет статус модальной формы , то выполнение макроса приостанавливается до той поры, пока пользователь не закроет форму. Так что в этом случае у пользователя время на работу с формой не ограничено. Это я понимал. Если же форма имеет статус немодальной формы (свойство ShowModal = False ), то форма действительно будет открыта в течение 10 секунд. Но в этом случае пользователь не сможет работать с этой формой, вводить значения в поля ввода и нажимать командную кнопку. Хуже всего то, что при попытке ввода значений в поля формы они фактически будут попадать в произвольное место программного текста и порти ть сам проект. Так что следует быть осторожным в подобной ситуации.

• Метод Help(, ) позволяет вызвать справочное руководство, указав при необходимости и соответствующий раздел в этом руководстве. Можно вызывать как стандартную справочную систему, - в этом случае не нужно задавать аргументы при вызове метода, либо, что чаще бывает, собственную справочную систему. Первый параметр метода задает имя файла, хранящего справочное руководство. Этот файл может иметь уточнение "chm" , если руководство подготовлено с помощью инструментария HTML Help Workshop , или иметь уточнение "htm", если справочная система создана с помощью инструментария Microsoft WinHelp.
• Методы Intersect(Arg1 As Range, Arg2 As Range, …)As Range и Union(Arg1 As Range, Arg2 As Range, …)As Range возвращают в качестве результата объект Range , задающий прямоугольную область, представляющую соответственно пересечение или объединение областей аргументов, которых должно быть не менее двух и не более 30.
• Метод InputBox , по существу, эквивалентен одноименной функции из библиотеки VBA и позволяет организовать диалог с пользователем и принять введенное им значение. Функция InputBox является одной из наиболее широко применяемых функций, и примеров ее вызова приводилось достаточно много. Не обойтись без нее и в примерах этой книги. Что вызывать метод InputBox объекта Application или функцию InputBox библиотеки VBA - дело вкуса.
• Метод Volatile() позволяет включить или выключить принудительное вычисление для функций, вызываемых в формулах рабочего листа. Метод вызывается непосредственно в функции, которую предполагается пометить. Булев параметр Volatile помечает функцию, как принудительно вычисляемую, если он имеет значение true . Это значение является значением параметра по умолчанию.

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

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

Файл рабочей книги Excel представлен в объекте Workbook, который имеет большое количество свойств и методов. Справочная информация по ним присутствует как в электронной справке по VBA, так и в . Мы не будем углубляться в чисто справочную информацию и во вводной части рассмотрим только те сведения, с которыми далее встретимся в приведенных примерах.

Так, свойство Worksheets объекта Workbook представляет семейство всех рабочих листов книги. И для обращения к конкретному листу книги с помощью этого свойства следует просто указать в качестве параметра номер листа, что выглядит так: Worksheets(номер листа) . В качестве номера листа выступает просто его порядковый номер в книге Microsoft Excel. Другой вариант заключается в указании в качестве параметра названия листа: Worksheets ("Название листа") . Одним из наиболее часто программируемых событий, связанных с книгой в целом, является событие Open , которое происходит при открытии рабочей книги. Так, если мы хотим, чтобы при открытии книги выполнялись определенные действия, следует расположить необходимый программный фрагмент внутри процедуры Workbook_Open . Программирование этого события будет рассматриваться в части приводимых далее примеров.

Следующим объектом в порядке иерархии после Workbook является объект Worksheet , представляющий рабочий лист. Из многообразия методов этого объекта широко используется Activate , который существует и для семейства листов Worksheets, о котором мы уже сказали выше. Например, если при работе на первом листе книги Microsoft Excel требуется активизировать второй лист, то синтаксис программной строки в процедуре (выполняться она может, например, по щелчку на кнопке) должен выглядеть следующим образом: Worksheets(2).Activate . Если же требуется активизировать лист, называемый Информация о фирмах , то следует написать в процедуре такую конструкцию: Worksheets("Информация о фирмах").Activate .

Пользователи Microsoft Excel знают, что это приложение предлагает сервис, связанный с защитой рабочих книг и составляющих их листов. Так, метод Protect (семейства Worksheets) обеспечивает защиту рабочего листа от внесения в него изменений. Для программной установки защиты с паролем (пароль указывается в параметре Password этого метода) листа с названием Сотрудники можно поступить следующим образом:

1 2	Worksheets("Сотрудники" ).Protect Password:="zv2345" , _ DrawingObjects:=True , Contents:=True , Scenarios:=True

Worksheets("Сотрудники").Protect Password:="zv2345", _ DrawingObjects:=True, Contents:=True, Scenarios:=True

Существует и соответствующий метод Unprotect , позволяющий снять защиту с листа. Для только что установленной защиты метод ее снятия: Worksheets("Сотрудники").Unprotect Password:="zv2345" . Любая практическая работа в Microsoft Excel, так или иначе, касается информации в ячейках. Для работы с ячейками в VBA существует объект Range (в переводе - диапазон ячеек). Использование этого объекта требует задания параметра диапазона ячеек, которые нас интересуют. Это может быть одна ячейка или группа ячеек. Так, если мы напишем Worksheets(1).Range ("С5").Value = 7 , то это означает, что в ячейку С5 первого листа мы программно записываем число 7. Здесь используется основное свойство объекта Range - Value . Буквально оно означает значение или содержимое ячейки (группы ячеек). В следующей конструкции в совокупность ячеек программно вводится одинаковый набор букв АБСДЕ: Worksheets(1).Range("A1:А3").Value = "АБСДЕ" .

Другой способ работы с ячейками реализуется с помощью объекта Cells , и синтаксис его использования выглядит следующим образом: Cells (номер строки, номер столбца) . Фактически с точки зрения их использования рассматриваемые объекты похожи. Например, получить в переменной Z значение ячейки D5 можно двумя разными способами: Z = Range("D5").Value или Z = Cells(5,4).Value . В качестве примера программной конструкции на данную тему можно привести следующее присвоение:

1 2	Worksheets(2).Range("C5" ).Value = _ Worksheets(3).Cells(5, 1).Value.

Worksheets(2).Range("C5").Value = _ Worksheets(3).Cells(5, 1).Value.

Лабораторная работа

ИЗУЧЕНИЕ ИНТЕРФЕЙСА ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL 2010. ПРОСТЫЕ ВЫЧИСЛЕНИЯ

Цели работы:

1)ознакомиться с возможностями табличного процессора;

2)ознакомиться с пользовательским интерфейсом программы MS Excel 2010;

3) ознакомиться с основными приемами работы в MS Excel 2010.

Задание:

1) изучите п. 1 «Учебный материал»;

2) выполните задания, приведенные в п. 2;

3) ответьте на контрольные вопросы (п. 3).

УЧЕБНЫЙ МАТЕРИАЛ

Основные понятия табличного процессора

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

Функции табличного процессора весьма разнообразны:

§ создание и редактирование таблиц;

§ оформление и печать таблиц;

§ создание многотабличных документов, объединенных формулами;

§ построение диаграмм;

§ работа с ЭТ как с базой данных; выборка данных по запросам;

§ создание итоговых и сводных таблиц;

§ решение задач типа «что-если» путем подбора параметров;

§ решение оптимизационных задач;

§ создание программ-макросов с помощью встроенного языка программирования Visual Basic for Applications (VBA).

Табличный процессор Excel является составной частью интегрированного программного пакета Microsoft Office.

После запуска Excel 2010 на экране появляется окно следующей структуры:

1. Заголовок программы .

2. Панель быстрого доступа с кнопкой настройки панели, по умолчанию расположенная в верхней части окна приложения Excel (рис. 1). Предназначена для быстрого доступа к наиболее часто используемым функциям. По умолчанию панель содержит всего три кнопки: Сохранить, Отменить, Вернуть (Повторить) . Панель быстрого доступа можно настраивать, добавляя в нее новые команды.

Рис. 1. Панель быстрого доступа

3. Лента. В Excel 2010 меню и панели инструментов выполнены в виде ленты. Команды упорядочены в логические группы, собранные на вкладках. Вкладки ориентированы на выполнение задач. Группы на каждой вкладке разбивают задачу на ее составляющие. Кнопки команд в каждой группе служат для выполнения команд или отображения меню команд. Значок (кнопка) группы – маленький квадрат в правом нижнем углу группы элементов во вкладке (рис. 2). Щелчок по значку открывает соответствующее этой группе диалоговое окно или область задач для расширения функциональных возможностей. Например, значок группы Шрифт вкладки Главная открывает диалоговое окно Формат ячейки . А значок группы Буфер обмена отображает область задач Буфер обмена .Не каждая группа имеет значок.

По умолчанию в окне отображается семь постоянных вкладок: Главная , Вставка , Разметка страницы , Формулы , Данные , Рецензирование , Вид .Вкладка Главная открывается по умолчанию после запуска программы.

Рис. 2. Кнопки групп

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

Перемещаться по ленте можно с помощью клавиатуры: если нажать клавишу , на ленте появятся подсказки для перехода к соответствующей вкладке (рис. 3).

Рис. 3. Клавиши перемещения по ленте

Перейдя на вкладку, можно увидеть подсказки по клавишам перехода по текущей вкладке (рис. 4).

Рис. 4. Клавиши перехода вкладки Главная

Заменить ленту панелями инструментов или меню предыдущих версий приложения Microsoft Excel нельзя. Удалить ленту также нельзя. Однако, чтобы увеличить рабочую область, ленту можно скрыть (свернуть). Способы:

1. Нажмите кнопку Свернуть ленту , расположенную в правой части линии названий вкладок. Для возврата нажать на кнопку Развернуть ленту .

2. Для быстрого скрытия/отображения ленты достаточно двойного щелчка по заголовку любой вкладки.

3. Ленту можно свернуть и иначе:

a) щелкните правой кнопкой мыши в любом месте ленты;

b) в контекстном меню выберите команду Свернуть ленту .

4. Чтобы свернуть или восстановить ленту, можно также нажать комбинацию клавиш .

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

1) щелкните правой кнопкой мыши в любом месте ленты;

2) в контекстном меню выберите команду Настройка ленты .

Также настройка ленты производится в окне ПараметрыExcel в категории Настройка ленты меню Файл .

4. Вкладка (меню) «Файл» . Вкладка Файл всегда расположена в ленте первой слева. Меню содержит команды для работы с файлами (Сохранить , Сохранить как , Открыть , Закрыть , Последние , Создать ), для работы с текущим документом (Сведения , Печать, Доступ ), а также для настройки Excel (Справка , Параметры ). Кнопка Выход завершает работу
с приложением.

5. Мини-панели инструментов. Мини-панели инструментов (рис. 5) содержат основные наиболее часто используемые элементы для оформления текста документа, рисунков, диаграмм и других объектов. В отличие от других приложений Office 2010 (Word, PowerPoint и др.) в Excel 2010 мини-панель не отображается автоматически при выделении фрагмента листа. Для ее отображения щелкните правой кнопкой по выделенной области. Состав элементов мини-панелей постоянный и неизменный.

Рис. 5. Мини-панель инструментов и контекстное меню

6. Строка формул , состоящая из трех частей:

a) адреса активной ячейки ;

b) кнопок Отмена , Ввод , которые появляются в режиме ввода или правки, и кнопки Вставка функции f x ;

c) содержимого активной ячейки.

Для просмотра и редактирования содержимого выделенной ячейки можно увеличить высоту строки формул. Для этого щелкните по кнопке Развернуть строку формул . Если и в этом случае отображается не все содержимое ячейки, можно воспользоваться полосой прокрутки. Для того чтобы привести строку формул в исходное состояние, щелкните по кнопке Свернутьстроку формул. Убрать/отобразить строку формул можно следующим образом: Файл ® Параметры ® Дополнительно ® категория Экран ® Показывать строку формул .

7. Координатная строка – содержит имена столбцов.

8. Координатный столбец – содержит номера строк.

9. Рабочее поле.

10. Горизонтальная и вертикальная полосы прокрутки .

11. Ярлычки листов с кнопками перехода по рабочим листам.

12. Строка состояния , в которой указываются режимы работы Готово , Ввод , Правка ; режимы просмотра книги Обычный, Разметка страницы, Страничный ; масштабная линейка.

Основные объекты Excel

1. Столбец . Таблица Excel содержит 16384 столбца(2 14). Столбцыидентифицированы латинскими буквами. Так как их только 26, то после Z обозначение столбцов идет сдвоенными буквами AA, AB, AC, …, GA, GB, GC, …, HX, HY, HZ, а после столбца ZZ – строенными ААА, ААВ, ААС, …, AAZ, ABA , … Заканчивается нумерация на столбце XFD. Чтобы быстро перейти к первому или последнему столбцу (строке) рабочего листа, нужно нажать клавишу <Ctrl> и соответствующую клавишу управления курсором.

2. Строка – их в таблице 1048576 (2 20).

3. Ячейка – место пересечения строки и столбца. Каждая ячейка имеет уникальный адрес, в котором указывается имя столбца и номер строки, на пересечении которых она расположена. Примеры использования адресов ячеек в стиле A1 показаны в таблице 1.

Таблица 1

Стиль ячеек A1

Excel поддерживает альтернативную систему указания ячеек, называемую R1C1 (от англ. слов Row – строка и Column – колонка). В этой системе и столбцы, и строки таблицы пронумерованы, а номер строки предшествует номеру столбца. Например, ячейка А1 называется R1C1 (строка 1, столбец 1). Ячейка В1 – это R1C2 (строка 1, столбец 2). Перейти к альтернативному стилю и обратно можно, зайдя в меню Файл ® Параметры ® Формулы ® категория Работа с формулами ® стиль ссылок R1C1 .

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

4. Блок ячеек – это прямоугольник, в котором указываются адреса ячеек левого верхнего и нижнего правого углов, разделенных двоеточием, например А1:С5.Если в выполняемом действии указан блок ячеек, то задействованы все его ячейки.

5. Рабочий лист – это созданная таблица для решения задачи, диаграмма, макрос, рисунок. Стандартное имя листа – Лист1, Лист2, …. С рабочими листами можно выполнять следующие действия :

§ переименование;

§ удаление;

§ вставка;

§ перемещение;

§ копирование.

Эти действия выполняются с помощью контекстного меню при установленном указателе мыши на ярлычке листов или в группе Ячейки вкладки Главная (рис. 6).

Рис. 6. Группа Ячейки вкладки Главная

Меню Файл ® Параметры ® Дополнительно ® категория Показать параметры для следующей книги ® Показывать ярлычки листов позволяет убрать/отобразить ярлычки листов.

6. Рабочая книга – это файл, который хранится на диске и содержит один или несколько листов. По умолчанию рабочая книга имеет имя Книга1, Книга 2 , …. Сохранить файл можно с помощью пункта меню Файл ® Сохранить . Команда Сохранить как выбирается, если нужно сохранить уже записанный на диск файл под новым именем. Файлам Excel 2010 по умолчанию присваивается расширение .xlsx .

7. Диаграмма – это графическое отображение данных таблицы. Может храниться на отдельном листе, а может сопровождаться текстом или таблицей.

8. Рисунок – создается с помощью группы Иллюстрации вкладки Вставка в самой среде Excel или может быть вставлен из другого графического редактора.

9. Модули Visual Basic – программы, называемые макросами и созданные на языке программирования Visual Basic.

Типы данных

Как правило, в ЭТ используются следующие типы данных:

1. Текст – любая последовательность символов, используемая в основном для заголовков таблиц, строк, столбцов и комментариев.

2. Число . В ячейке Excel можно отобразить три типа числовых данных (констант):

a) целые числа – это последовательность цифр от 0 до 9 со знаком или без него: +25; –100.

b) вещественные числа с фиксированной запятой – это десятичные дроби, в которых целая часть отделяется от дробной запятой: 28,25; –3,765.

c) вещественные числа с плавающей запятой – это числа, записанные в следующей форме: 1,5Е+03 или 2Е-08 . Такую запись еще называют экспоненциальной формой записи числа (научный формат).

По умолчанию правильно введенное число выравнивается по правому краю ячейки. Неправильно введенное число считается текстом и выравнивается по левому краю. Если число не поместилось по ширине ячейки, то вся ячейка заполняется символом # (рис. 7).

Рис. 7. Вид чисел на рабочем листе

3. Формула – это выражение, состоящее из чисел, адресов ячеек, функций и знаков операций и начинающееся со знака = . Например:

А1*20%+12*A1*D12.

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

4. Функция – это запрограммированные формулы, позволяющие производить часто встречающиеся последовательности вычислений (стандартные функции разного назначения).

5.Дата – это дата, представленная в различных форматах, с которой можно производить арифметические и логические операции. Например, 21.10.2011; 21 октября 2011 г. и др.

Копирование ячеек

Копирование можно осуществить следующими способами:

1) вкладка Главная ® группа Буфер обмена ® команда Копировать ;

2) с помощью команды Копировать контекстного меню;

3) с помощью комбинации клавиш ;

4) с помощью техники «перетащить-и-оставить» при нажатой клавише . В этом случае рядом с указателем мыши появляется знак + (признак копирования);

5) с использованием маркера в правом нижнем углу копируемой ячейки. При отсутствии этого маркера необходимо воспользоваться командой Файл ® Параметры ® Дополнительно ® категория Параметры правки ® Разрешить маркеры заполнения и перетаскивания ячеек .

К числу основных объектов MS Excel, которые описываются в этом разделе, относятся следующие: рабочая книга (Workbook ) и

семейство рабочих книг (Workbooks ), рабочий лист (Worksheet ) и семейство рабочих листов (Worksheets ), диапазон ячеек или ячейка (Range ).

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

Пример :

Workbooks(“Книга1.xls”).Worksheets(“Лист1”).Activate

Семейство WorkBooks(“Книга1”) обеспечивает доступ к рабочей книге. В скобках указывается имя книги.

Семейство WorkSheets(“Лист1”) обеспечивает доступ к рабочему листу. В скобках указывается имя листа.

Семейство Range(“диапазон”) обеспечивает доступ к диапазону ячеек или к ячейке. В скобках указывается диапазон ячеек или имя ячейки.

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

Примеры :

WorkBooks(“Книга1”)

WorkSheets(“Лист1”) Range(“A1”) Range(“A1:В10”) Cells(2,3) Cells(k,i+1)

5.4.4.1. Объект Workbook и семействоWorkbooks

В иерархии MS Excel объект Workbook (рабочая книга) идет сразу после объектаApplication и представляет собой файл рабочей книги. Рабочая книга хранится либо в файлах форматаXLS (стандартная рабочая книга) илиXLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют

работать с файлами. Этот объект входит в семейство (набор)

Workbooks(<Индекс>) возвращает объект по индексу в наборе.

Workbooks(“<Имя>”) возвращает объект по имени в наборе.ActiveWorkbook возвращает ссылку на активную книгу в момент выполнения команды.

5.4.4.1.1. Свойства

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

Примеры :

MsgBox(“Имя активного листа “ & ActiveSheet.Name)

MsgBox(ActiveWorkbook.ActiveSheet.Name) – выводит имя активного рабочего листа в диалоговом окне. ActiveDialog– возвращает активное диалоговое окно. ActiveChart– возвращает активную диаграмму.

Sheets – возвращает семейство всех листов книги.

Worksheets – возвращает семейство всех рабочих листов книги.

Пример :

For Each s In ActiveWorkbook.Sheets MsgBox (s.Name)

Charts – возвращает семейство всех диаграмм книги, которые не внедрены в рабочие листы.

Count – возвращает число объектов семействаWorkbooks (количество открытых приложением книг).

FullName – возвращает полное имя рабочей книги.

Пример :

MsgBox (ActiveWorkbook.FullName)

Name – возвращает имя активной рабочей книги. 245

Пример :

MsgBox (ActiveWorkbook.Name)

Path – возвращает путь к файлу рабочей книги.

Пример :

MsgBox (ActiveWorkbook.Path)

5.4.4.1.2. Методы

Метод Activate активизирует рабочую книгу так, что ее первый рабочий лист становится текущим (доступным для работы).

Пример :

WorkBooks(“Книга1”).Activate

Workbook.Activate

Метод Close обеспечивает закрытие рабочей книги.Close SaveChanges FileName – закрывает книгу. ПараметрSaveChanges позволяет управлять сохранением изменений в рабочей книге. Если его значение равноTrue , то изменения сохраняются, если –False , то не сохраняются. ПараметрFileName – строка, содержащая имя файла рабочей книги.

Пример :

WorkBooks(“Книга1”).Close WorkBooks(“Книга1”).Close SaveChanges:=True Filename:=“Книга2”

New Window предназначен для открытия указанной книги в новом окне.

Пример :

WorkBooks(“Книга1”).NewWindow

Save служит для сохранения изменений в рабочей книге.

Пример :

WorkBooks (“Книга1”).Save

SaveAs Filename – используется для сохранения книги под другим именем (в другом файле).

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

Примеры :

WorkBooks (“Книга1”).SaveAs FileName:=“kdjf.xls”

ActiveBook.SaveAsCopy FileName:=”Моя книга”

Open Filename – позволяет открыть рабочую книгу с именем, указанным в параметреFileName .

Пример :

Workbooks.Open FileName:=“Книга1.xls”

5.4.4.1.3. События

Основные события объекта Workbook приведены в табл. 5.13.

	Таблица 5.13
	Когда возникает событие
	При закрытии рабочей книги
	Перед печатью рабочей книги
	Перед сохранением рабочей книги
	При добавлении нового листа
	При открытии рабочей книги
	При активизации рабочего листа

5.4.4.2. Объект Worksheetи семейство WorkSheets

В иерархии MS Excel объект Worksheet идет сразу после

объекта Workbook , представляет рабочий лист книги и входит в семейство (набор)Worksheets .

Примеры :

Worksheets(“Лист1”).Activate Worksheets(1).Activate

Пример :

Activesheet.Range (“a1”)=1

5.4.4.2.1. Свойства

Свойство Name позволяет работать с именем рабочего листа.

Пример :

Worksheets(1).Name=”Итоги”

ActiveCell – возвращает активную ячейку активного рабочего листа.

ячейку с указанными координатами.

Columns(<столбец>) – возвращает ссылку на столбец. В качестве параметра могут быть заданы имя или номер столбца.

Пример :

Worksheets(1).Columns(“a”)=1

Worksheets(1).Columns(1)=1

Rows(<строка>) –в озвращает ссылку на строку. В качестве параметра может быть использован номер строки.

Пример :

Worksheets(1).Rows(1)=1

Range(<Диапазон ячеек>) – возвращает ссылку на указанный диапазон ячеек.

Пример :

Worksheets(“Лист1”).UsedRange.Value=1 Count – возвращает количество листов в книге.

Visible – определяет отображение рабочего листа в книге. Его допустимые значения:True – рабочий лист выводится на экран;False – рабочий лист невидим (скрыт), но его можно отобразить на экране с помощью последовательности командФормат, Лист,

Отобразить (Format, Sheet, Show); xlVeryHidden – рабочий лист скрыт и его можно отобразить на экране только программно.

Примеры :

Sub Пусто() Worksheets(“Лист3”).Visible=False

Sub Открыто()

Worksheets(“Лист3”).Visible=True

Sub Пусто1() Worksheets(“Лист4”).Visible=xlVeryHidden

Excel позволяет размещать довольно много различных типов объектов на листе: диаграммы, фигуры, картинки и SmartArt, например. Для работы с объектом вы должны его выбрать. Самый простой способ выбрать отдельный объект - щелкнуть на нем кнопкой мыши.

А что делать, если вы хотите выбрать несколько объектов? Например, вам может потребоваться переместить несколько объектов, удалить их или применить форматирование. Excel предоставляет различные способы выбора нескольких объектов.

Ctrl+щелчок

Удерживание нажатой клавиши Ctrl при щелчках на объектах - один из способов выбрать несколько объектов.

Панель Выделение и видимость

Панель Выделение и видимость , показанная на рис. 151.1, обеспечивает быстрый способ выбора (и скрытия) объектов. Для открытия этой панели выберите Главная Редактирование Найти и выделить Область выделения . Панель содержит имя каждого объекта на активном листе. Щелкните на имени объекта, и он будет выбран. Нажмите Ctrl для выбора нескольких объектов. Вы также можете воспользоваться этой панелью для того, чтобы скрыть объекты (щелкните на маленьком значке с изображением глаза) и изменить их порядок (используйте две соответствующие кнопки внизу).

Диалоговое окно Выделение группы ячеек

Для выбора всех объектов на листе выполните команду Главная Редактирование Найти и выделить Выделение группы ячеек . Далее установите переключатель в соответствующее положение и нажмите ОК .

Инструмент выбора объектов

Другой способ выбора нескольких объектов состоит в использовании инструмента выбора объектов (выберите Главная Редактирование Найти и выделить Выбор объектов ). Когда вы выполняете эту команду, Excel переходит в специальный режим, а указатель мыши принимает вид стрелки. Щелкните кнопкой мыши на листе и перетаскивайте, чтобы выбрать все объекты в прямоугольной области. Чтобы вернуться в нормальный режим, нажмите клавишу Esc .