Операции с объектами Microsoft Excel. Присваивание объекта переменной. Особенности программирования для MS Excel

В большинстве примеров вводной части раздела мы рассматривали возможности 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, которые описываются в этом разделе, относятся следующие: рабочая книга (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

Создание рабочего листа "Товары.xls"

Сначала рассмотрим, как можно выполнять операции с рабочим листом Microsoft Excel из программы Access. Для этого нам потребуется файл Товары.хls, содержащий рабочую книгу Excel с единственным рабочим листом "Товары", который представляет собой список товаров из демонстрационной базы данных "Борей". Вы можете использовать готовый файл, находящийся на сопровождающем книгу компакт-диске, либо создать его самим, экспортировав в Excel таблицу "Товары" с помощью команды меню Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel) (см. разд. "Быстрый экспорт данных в другие приложения Microsoft Office" гл. 3).

Для того чтобы подготовить файл Товары.xls к дальнейшим экспериментам, выполните следующие действия:

  1. Откройте файл Товары.xls, запустив Microsoft Excel.
  2. Выделите ячейки с А4 по D12 в рабочем листе. Выберите команду меню Вставка, Имя, Присвоить (Insert, Name, Define). В диалоговом окне Присвоение имени (Define Name) введите в текстовое поле Имя (Names in workbook) имя диапазона: workRange (рис. 15.25) и щелкните по кнопке ОК. Тем самым создается именованный диапазон, который будет использоваться в последующих примерах.

Рис. 15.25. Создание именованного диапазона в таблице "Товары.xls"

  1. Выберите в меню Сервис (Tools) команду Надстройки (Adds-Ins) и снимите все флажки в списке Список надстроек (Add-Ins Available) диалогового окна Надстройки (Add-Ins). Удаление надстроек уменьшает время, требующееся для запуска приложения Excel. Чтобы закрыть диалоговое окно, нажмите на кнопку ОК.
  2. Сохраните изменения, выполнив команду Файл, Сохранить (File, Save) или Файл, Сохранить как (File, Save As).
  3. Закройте Microsoft Excel.

Иерархия объектов VBA приложения Microsoft Excel

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

  • Объект Application является экземпляром собственно Microsoft Excel. Применяя к этому объекту различные методы, можно воспользоваться практически любыми командами меню Excel. Объект Application обладает свойствами ActiveWorkbook и

    ActiveSheet, которые указывают на текущие объекты Workbook (рабочая книга) и Worksheet (рабочий лист). Можно указать Excel.Application в качестве значения аргумента <класс> функций CreateObject () и GetObjectO, а также в операторе Dim objPlMH As New <класс>.

  • Основной объект Excel - Workbook (рабочая книга). С помощью этого объекта осуществляется доступ к свойствам и структуре рабочей книги Excel. Объекты workbook - это файлы, которые состоят из нескольких объектов, создаваемых Microsoft Excel: Worksheet (рабочий лист) и Chart (диаграмма). Объекты Worksheet и Chart содержатся В семействах Worksheets и Charts.
  • Объекты Worksheet являются подчиненными по отношению к объекту Workbook и обеспечивают доступ к свойствам и структуре рабочих листов книги Excel. Основным объектом взаимодействия приложений Microsoft Access 2002 и Microsoft Excel 2002 является именно объект Worksheet. Имеется возможность перенести информацию из строк и столбцов объекта Recordset приложения Microsoft Access в ячейки объекта Worksheet приложения Microsoft Excel и наоборот. Если в качестве значения аргумента <класс> функции GetObject() либо в операторе Dim оbj Имя As New <класс> указать Excel.Sheet, то по умолчанию откроется первый объект семейства Worksheets - первый рабочий лист (ActiveSheet) объекта Workbook.
  • Объекты sheet аналогичны объектам Worksheet, но они входят в семейство Sheets, которое включает в себя как объекты Worksheet, так и объекты Chart, представляющие листы диаграмм в рабочей книге.
  • Объект Range - диапазон ячеек рабочего листа Excel. Это могут быть отдельная ячейка, строка, столбец, прямоугольная область рабочего листа или несвязанный (произвольный набор ячеек) или трехмерный диапазон ячеек, включающий ячейки на нескольких рабочих листах. Можно получить либо установить значения диапазона ячеек, определив объект Range. Таких объектов, как ячейка, строка, столбец просто не существует. Именно объект Range со своими свойствами и методами обеспечивает работу как с отдельной ячейкой, так и с их диапазоном. Для ссылки на необходимую группу ячеек можно использовать имя диапазона, состоящего из этих ячеек. Если именованные диапазоны отсутствуют на рабочем листе, можно использовать метод Cells объекта worksheet, чтобы задать координаты ячейки или группы ячеек. Ниже приводятся два способа изменения значения ячейки А1:
ActiveSheet.Range ("Al").Value = 7 ActiveSheet.Cells (1,1).Value = 7

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

Открытие существующего рабочего листа Excel и работа с ним

Прежде чем работать с автоматизированными объектами Microsoft Excel, установим ссылку на библиотеку объектов Microsoft Excel. Для этого:

  1. Запустите Access и, если необходимо, откройте базу данных "Борей". Вызовите редактор VBA, открыв любой модуль.
  2. Выберите в окне редактора VBA команду меню Сервис, Ссылки (Tools, .References), чтобы открыть диалоговое окно References (Ссылки).
  3. Установите флажок у элемента Microsoft Excel 10.0 Object Library (рис. 15.26), затем нажмите на кнопку ОК, чтобы закрыть окно References.

Рис. 15.26.

Команды автоматизации удобно изучать при помощи окна отладки Immediate. Поэтому, выведите данное окно на экран, если оно не отображается. Для этого достаточно нажать соответствующую кнопку на панели инструментов или комбинацию клавиш +.

Чтобы программно открыть рабочий лист рабочей книги "Товары":

  1. Закройте Excel, если он запущен.
  2. Создайте новый модуль, выполнив команду меню Insert, Module (Вставка, Модуль).
  3. Добавьте в раздел описаний следующие описания переменных:
Private xlaProd As Excel.Application Private xlwProd As Excel.Workbook Private xlsProd As Excel.Worksheet
  1. Введите в окне отладки следующий оператор (рис. 15.27):
Set xlwProd = GetObject(CurDir & "\Товары.хls","Excel.Sheet")

При нажатии затем на клавишу приложение Microsoft Excel запускается в режиме /automation. Функция CurDir возвращает полное имя текущей папки. Если файл Товары.хls был сохранен где-нибудь в другом месте, измените в предыдущем операторе путь к этому файлу. В зависимости от скорости функционирования компьютера, запуск Excel может продолжаться достаточно долю. Загрузка приложения Excel завершена, когда в строке состояния окна отладки надпись Выполнение (Running) исчезает и появляется надпись Готово (Ready). В результате будет создан экземпляр класса Application Microsoft Excel и переменной xlwProd будет присвоена ссылка на объект Workbook. Обратите внимание, что функция Getobject () открывает скрытый экземпляр приложения Excel, значок Excel не появляется на панели задач и интерактивно обратиться к рабочей книге Excel нельзя.

Замечание

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

Рис. 15.27.

  1. Чтобы убедиться, что рабочая книга открыта и мы можем к ней обратиться, введите в окне отладки следующую команду?xlwProd.Name

Свойство Name созданного объекта workbook содержит имя файла Excel: Това-pbi.xls (рис. 15.28).

Рис. 15.28. Команды, позволяющие читать и устанавливать значения отдельных ячеек в рабочем листе "Товары"

  1. Файл Toвapы.xls содержит только один объект Worksheet, поэтому рабочий лист "Товары" является активным рабочим листом - объектом ActiveSheet. Чтобы убедиться в этом, введите в окно отладки команду:
?xlwProd. ActiveSheet.Name

Свойство Name этого объекта содержит имя рабочего листа: Товары.

  1. Теперь попробуйте обратиться к первой ячейке рабочего листа. Введите?xlwProd.ActiveSheet.Celled, 1). После короткой паузы появится ожидаемый результат - строка "Код товара". Это заголовок первого столбца таблицы.
  2. Метод Cells позволяет обратиться к любой ячейке рабочего листа. Введите?xlwProd. ActiveSheet. Cells (R, С), где R - номер строки, а С - номер столбца заданной ячейки, т. е. ее координаты (рис. 15.28).
  3. Можно изменить содержимое ячейки, если ввести выражение вида: xlwProd.ActiveSheet.Cells(2,2).Value = "brown rice". Подобно тому как многие объекты Access при введении имени объекта возвращают его значение, метод Cells не требует явного указания свойства Value по умолчанию. Чтобы убедиться в том, что содержимое ячейки изменилось, введите?xlwProd.ActiveSheet.Cells (3,2), не дописывая выражения.Value.

Для установления значения ячейки можно также использовать свойство Formula. Преимущество использования свойства Formula состоит в возможности его применения с целью введения формул с использованием "родного" синтаксиса Microsoft Excel, т. е. в виде ссылок на конкретные ячейки, например "=А2+С6".

Использование именованных диапазонов ячеек

Если в рабочем листе Excel создан именованный диапазон ячеек, то можно получить значения ячеек, содержащихся в этом диапазоне, если сослаться на свойство Range объекта Worksheet. Сначала посмотрим, какие именованные диапазоны присутствуют в открытом нами объекте. Введите в окно отладки команду (рис. 15.29)

XlwProd.Names(1).Name

Семейство Names представляет все имена, определенные в рабочей книге. В данном случае первый элемент этого семейства содержит имя диапазона: WorkRange.

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

XlwProd.Names(1).Value Результат будет: =Товары!$А$4:$D$12,

т. е. прямоугольная область А4-D12 на рабочем листе "Товары".

На рис. 15.29 приведены выражения для управления объектом Range.

Рис. 15.29.

Пусть переменная xlsProd ссылается на рабочий лист "Товары". Для этого введите команду:

Set xlsProd = xlwProd.ActiveSheet.

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

XlsProd.Range("WorkRange").Cells(1,1)

Здесь используется свойство Range объекта Worksheet для доступа к именованному диапазону, а затем свойство Cells объекта Range - для указания конкретной ячейки в диапазоне. Первая цифра указывает строку, а вторая - столбец.

Для того чтобы обратиться к объекту, который находится на уровень выше в иерархии объектов модели, можно воспользоваться свойством Parent. На рис. 15.29 представлено, как обратиться к рабочей книге Excel, содержащей текущий рабочий лист, и как установить объектную переменную xlaProd, которая должна ссылаться на объект Application Microsoft Excel:

Set хlwРабочаяКнига = хlsРабочийЛист.Parent.

Закрытие объектов Workbook и Application

Объект Microsoft Worksheet закрыть нельзя. Для закрытия объекта Excel Workbook может быть использован метод Close, а для выхода из приложения - метод Quit. Следующие операторы закрывают объект Workbook и затем осуществляют выход из приложения сервера автоматизации, освобождая системные ресурсы:

XlwProd.Close xlaProd.Quit Set xlsProd = Nothing Set xlwProd = Nothing Set xlaProd = Nothing

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

Замечание

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

Создание рабочего листа Excel с помощью кода автоматизации

Те же действия, что происходят при нажатии кнопки Анализ в MS Excel, можно осуществить при помощи кода автоматизации VBA. Преимуществом такого способа является возможность форматировать созданный объект специально под нужды конкретного приложения. Рассмотрим функцию CreateCustomSheet (), создающую новый объект Worksheet и заполняющую его данными из таблицы "Товары" базы данных Microsoft Access:

Function CreateCustomSheet() As Integer "Создание рабочего листа MS Excel из таблицы "Товары" "Описание локальных переменных "(Объектные переменные описаны на уровне модуля) Dim сйэБорей As Database "Текущая база данных Dim rstProd As Recordset "Объект Recordset Dim intRow As Integer "Счетчик строк Dim intCol As Integer "Счетчик столбцов "Открытие таблицы в текущей базе данных Set dbБорей = CurrentDb() Set rstProd = dbBopeu.OpenRecordset("Товары", dbdpenTable) DoCmd.Hourglass True "Создание нового объекта Excel Workbook Set xlwProd = CreateObject("Excel.Sheet") ""Создание объекта Application для применения метода Quit Set xlaProd = xlwProd.Parent intRow = 1 intCol = 1 rstProd.MoveFirst "Переход к первой записи Do Until rstProd.EOF "Цикл с шагом в одну запись For intCol = 1 То rstProd.Count "Цикл с шагом в одно поле If (Not IsNull(rstProd(intCol -1))) Then xlwProd.ActiveSheet.Cells(intRow, intCol).Value = CStr(rstProd(intCol -I}} End If Next intCol rstProd.MoveNext intRow = intRow + 1 Loop For intCol = 1 To xlwProd.ActiveSheet.Columns.Count "Форматирование каждого столбца рабочего листа xlwProd.ActiveSheet.Columns(intCol).Font.Size = 8 xlsCust.ActiveSheet.Columns(intCol).AutoFit If intCol = 8 Then "Выравнивание по левому краю числовых и "смешанных почтовых кодов xlwProd.ActiveSheet.Columns(intCol).HorizontalAlignment = _xlLeft End If Next intCol DoCmd.Hourglass False xlwProd.SaveAs (CurDir & "\Товары_2.xls") xlaProd.Quit End Function

Тип данных, возвращаемых выражением rstProd(intCol-l), следует специально изменить с variant на string при помощи функции cstr(), иначе Microsoft Excel вместо нужной величины отобразит в соответствующем столбце #н/д (#N/A#). Если объект Recordset содержит поля, типы которых отличны от Text, то для определения типа данных в столбце используйте соответствующую функцию СТуре().

Константа xlLeft, присвоенная в качестве значения свойству HorizontalAlignment восьмого столбца, представляет собой встроенную константу Excel, определяемую в тот момент, когда устанавливается ссылка на объектную библиотеку Microsoft Excel 10.0 Object Library. Выделение элемента Constants (Константы) в списке Модули/ Классы окна просмотра объектов при подключенной библиотеке Excel отображает константы xlConst. На рис. 15.30 приведено числовое значение константы xlLeft, которая является одной из констант для задания значения свойства HorizontalAlignment.

Рис. 15.30. Значения встроенных констант xlConst в окне Object Browser Access

Ввод оператора? CreateCustomSheet () в окне отладки запускает функцию, которую мы рассматривали выше. На рис. 15.31 приведена рабочая книга "ToBapы_2.xls" с рабочим листом, созданным при помощи функции CreateCustomSheet () и открытым в Microsoft Excel.

Рис. 15.31. Часть рабочего листа Excel, созданного из таблицы "Товары"

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

Объектов в этой иерархии очень много. Учитывая, что у каждого объекта, в свою очередь, большое количество свойств, методов и событий, изучать их достаточно сложно. Поэтому мы рассмотрим только основные объекты. На первых порах достаточно использовать Application (Приложение), Workbook (Рабочую книг}"), Worksheet (Рабочий лист), Range (Диапазон ячеек). Много в этой иерархии и коллекций: Workbooks – это коллекция рабочих книг, Worksheets – коллекция рабочих листов, Sheets – коллекция всех листов (рабочих листов и листов диаграмм), Charts – коллекция диаграмм.

Полное описание свойств, методов и событий каждого объекта можно найти в справочной системе. Для этого в редакторе Visual Basic откройте окно справки, в строке поиска наберите "Application Object Members" (для объекта Application) и перейдите но найденной гиперссылке. Для других объектов вместо Application используйте нужное имя класса.

Объект Application

На вершине объектной модели находится объект Application. Он представляет само приложение Excel. Через этот объект осуществляется доступ ко всем остальным объектам. Однако обычно свойства и методы этого объекта используют, не указывая слово Application. Например, вместо Application. ActiveCell.Value можно написать просто ActiveCell.Value. Основные свойства объекта приведены в табл. 12.21.

Таблица 12.21

Основные свойства объекта Application

Свойство

Описание

Active Workbook,

Активные объекты (рабочая книга, окно, лист, диаграмма, ячейка)

WorkSheetFunction

Представляет собой объект – контейнер, в котором находятся функции Excel. "Гак, например, мы можем обратиться в программе на VBA к функции Excel ФАКТР, вычисляющей факториал: WorksheetFunction.Fact(5). Имена функций в русскоязычной версии Excel не совпадают со свойствами объекта Worksheet Function. Чтобы узнать имя нужного свойства (имя функции в англоязычной версии), запишите макрос, использующий эту функцию на рабочем листе, и посмотрите имя функции в тексте макроса

Коллекция всех рабочих книг

Выделенный объект в активном окне. Тип объекта зависит от текущего выделения. Возвращается Nothing, если в активном окне нет выделенного объекта

Метод Quit объекта Application завершает работу Excel. Например, следующий код сохраняет все открытые книги Excel и завершает работу приложения:

For Each wb In Application.Workbooks

Application.Quit

Объект Workbook и коллекция Workbooks

Каждая рабочая книга представляется объектом Workbook, а их коллекция – объектом Workbooks. Методы коллекции Workbooks приведены в табл. 12.22.

Таблица 12.22

Методы коллекции Workbooks

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

Основные свойства и методы объекта Workbook приведены в табл. 12.23 и 12.24.

Таблица 12.23

Основные свойства объекта Workbook

Таблица 12.24

Основные методы объекта Workbook

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

Тип данных — это характеристика информации, хранимой на листе. На основе этой характеристики программа определяет, каким образом обрабатывать то или иное значение.

Типы данных делятся на две большие группы: константы и формулы. Отличие между ними состоит в том, что формулы выводят значение в ячейку, которое может изменяться в зависимости от того, как будут изменяться аргументы в других ячейках. Константы – это постоянные значения, которые не меняются.

В свою очередь константы делятся на пять групп:

  • Текст;
  • Числовые данные;
  • Дата и время;
  • Логические данные;
  • Ошибочные значения.

Выясним, что представляет каждый из этих типов данных подробнее.

Текстовые значения

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

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

Числовые данные

Для непосредственных вычислений используются числовые данные. Именно с ними Excel предпринимает различные математические операции (сложение, вычитание, умножение, деление, возведение в степень, извлечение корня и т.д.). Этот тип данных предназначен исключительно для записи чисел, но может содержать и вспомогательные символы (%, $ и др.). В отношении его можно использовать несколько видов форматов:

  • Собственно числовой;
  • Процентный;
  • Денежный;
  • Финансовый;
  • Дробный;
  • Экспоненциальный.

Кроме того, в Excel имеется возможность разбивать числа на разряды, и определять количество цифр после запятой (в дробных числах).

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

Дата и время

Ещё одним типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Он характеризуется тем, что с его помощью можно указывать на листе и проводить расчеты с датами и временем. Примечательно, что при вычислениях этот тип данных принимает сутки за единицу. Причем это касается не только дат, но и времени. Например, 12:30 рассматривается программой, как 0,52083 суток, а уже потом выводится в ячейку в привычном для пользователя виде.

Существует несколько видов форматирования для времени:

  • ч:мм:сс;
  • ч:мм;
  • ч:мм:сс AM/PM;
  • ч:мм AM/PM и др.

Аналогичная ситуация обстоит и с датами:

  • ДД.ММ.ГГГГ;
  • ДД.МММ
  • МММ.ГГ и др.

Есть и комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.

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

Логические данные

Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ» . Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.

Ошибочные значения

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

  • #ЗНАЧ! – применение неправильного вида аргумента для функции;
  • #ДЕЛ/О! – деление на 0;
  • #ЧИСЛО! – некорректные числовые данные;
  • #Н/Д – введено недоступное значение;
  • #ИМЯ? – ошибочное имя в формуле;
  • #ПУСТО! – некорректное введение адресов диапазонов;
  • #ССЫЛКА! – возникает при удалении ячеек, на которые ранее ссылалась формула.

Формулы

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

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

Отдельным видом формул являются функции. Это своеобразные подпрограммы, которые содержат установленный набор аргументов и обрабатывают их по определенному алгоритму. Функции можно вводить вручную в ячейку, поставив в ней предварительно знак «=» , а можно использовать для этих целей специальную графическую оболочку Мастер функций , который содержит весь перечень доступных в программе операторов, разбитых на категории.

С помощью Мастера функций можно совершить переход к окну аргумента конкретного оператора. В его поля вводятся данные или ссылки на ячейки, в которых эти данные содержатся. После нажатия на кнопку «OK» происходит выполнение заданной операции.

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

Похожие статьи