Применение Excel для анализа информации из базы данных

МИНИСТЕРСТВО  ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

Учреждение  образования 
«Брестский государственный университет имени А. С. Пушкина»

Математический  факультет 
Кафедра математического моделирования

Курсовая работа

Применение Excel для анализа информации из базы данных

Волк  Татьяна Владимировна, 
студентка 3 курса специальности «Экономическая кибернетика»

Будько  Дмитрий Александрович – ассистент кафедры математического моделирования

Брест 2011

содержание

ВВЕДЕНИЕ 3

1 ПРЕИМУЩЕСТВА И ВОЗМОЖНОСТИ ПРИМЕНЕНИЯ VBA 4

1.1 Что такое язык VBA 4

1.2 Создание приложений VBA в Excel 4

2 ИЕРАРХИЯ СТАНДАРТНЫХ ОБЪЕКТОВ В EXCEL 6

2.1 Объект Application. Свойства и методы объекта Application 6

2.2 Коллекция Workbooks и объект Workbook 10

2.3 Коллекция Sheets и объект Worksheet, их свойства и методы 11

2.4 Программная работа с ячейками и диапазонами ячеек средствами VBA 14

3 ПРИМЕРЫ ВОЗМОЖНОСТЕЙ ЯЗЫКА VBA 19

3.1 Задание к курсовой работе 19

3.2 Описание алгоритма 19

3.3 Листинг программы 22

ЗАКЛЮЧЕНИЕ 24

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 25

ВВЕДЕНИЕ

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

Так зачем усложнять себе жизнь?

Тема моей курсовой работы актуальна в наше время, т.к. VBA имеет огромную область применения. Язык Vba очень прост, потому что изначально был ориентирован на пользователей. Применение встроенного языка программирования Visual Basic for Applications (VBA) существенно расширяет возможности электронных таблиц MS Excel при обработке и анализе корпоративных данных.

Целью моей работы является изучение языка программирования Visual Basic for Applications (VBA), применение этого языка в одном из приложений Office и создание приложений VBA в Excel. Задачи курсовой работы:

• научиться использовать интегрированную среду разработки — редактор VBA для просмотра, редактирования и ввода программного кода;
• применять в своих рабочих книгах процедуры и формы VBA;
• использовать объектную модель Excel, свойства, методы и события объектов (рабочих книг, листов, диапазонов ячеек и т.д.).

1 ПРЕИМУЩЕСТВА И ВОЗМОЖНОСТИ ПРИМЕНЕНИЯ VBA

1.1 Что такое язык VBA

VBA (Visual Basic for Applications) — это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.

Главная причина проста — язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office — документах Word, книгах Excel, презентациях PowerPoint и т.п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге — хост) встроена внутрь этих приложений.

VBA изначально был ориентирован  на пользователей, а не на  профессиональных программистов  (хотя профессионалы пользуются  им очень активно), поэтому создавать  программы на нем можно очень  быстро и легко. Кроме того, в Office встроены мощные средства, облегчающие работу пользователя: подсказки по объектам и по синтаксису, макрорекордер и т.п.

При создании приложений на VBA вам, скорее всего, не придется заботиться о установке и настройке специальной  среды программирования и наличии  нужных библиотек на компьютере пользователя — MS Office есть практически на любом компьютере.

Несмотря на то, что часто  приложения VBA выполняются медленнее, чем бы вам хотелось, они нересурсоемки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований по производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. Чаще всего проблемы с производительностью VBA-приложений — это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т.п.

Программы на VBA по умолчанию  не компилируются и поэтому вносить  в них исправления очень удобно. Не нужно разыскивать исходные коды и перекомпилировать программы.

1.2 Создание приложений VBA в Excel

Excel — это наиболее часто используемое с точки зрения программирования приложение Office. В подавляющем большинстве случаев сотрудников предприятий интересует, как автоматизировать выполнение операций именно в Excel. Чаще всего на предприятиях встречаются следующие ситуации:

• необходимо автоматизировать загрузку данных в таблицу Excel из базы данных, а затем в автоматическом режиме произвести обработку этой таблицы (расчеты, моделирование и т.п.), и представить эту информацию в стандартном виде. На практике, конечно, намного правильнее постараться перенести выполнение расчетов  на сервер баз данных, но обычно у пользователей для этого нет ни необходимых знаний, ни прав для работы с сервером баз данных. Поэтому Excel в таких ситуациях остается незаменимым средством;
• приложение, работающее с базой данных, уже умеет генерировать отчеты в формате файлов Excel. Но со временем потребности в отчетах изменяются, появляется необходимость в новых отчетах или в изменении старых. Чаще всего в этом случае пользователи самостоятельно создают новые отчеты, используя данные из старых. Повторяющейся работы очень много, поэтому автоматизация таких операций бывает просто необходимой;
• очень часто пользователи, не имея возможности обратиться к профессиональным программистам, самостоятельно реализуют нужные им приложения в таблицах Excel;
• формат файлов Excel удобен не только для вывода информации из базы данных, но и для загрузки введенной вручную информацию в базу данных. Часто на предприятиях информация из филиалов, подразделений, сотрудников и т.п. собирается в формате Excel. В результате со временем возникает вопрос — как автоматизировать процесс загрузки информации из Excel в базу данных.

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

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

Иерархия стандартных  объектов в Excel немного больше, чем в Word, т.к. появляется новый элемент — лист, поэтому главная его иерархия выглядит следующим образом: Application — Workbook (книга) — Worksheet (лист) — Range (диапазон).

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

2 ИЕРАРХИЯ СТАНДАРТНЫХ ОБЪЕКТОВ В EXCEL

2.1 Объект Application. Свойства и методы объекта Application

Объект Application в Microsoft Excel представляет все приложение Excel и находится на самом верхнем уровне объектной модели Excel. Если вам потребуется вызвать Excel из другого приложения, вам потребуется создать объект Excel.Application (не забудьте при этом при помощи меню Tools->References добавить ссылку на библиотеку Microsoft Excel 11.0 Object Library).

Если вы работаете из уже запущенного Excel, создавать объект Application вам не потребуется. Он будет доступен всегда. Если вы обращаетесь к какому-либо свойству без указания вышестоящего объекта, то редактор Visual Basic в Excel будет считать, что вы обращаетесь к свойству объекта Application.

Вообще объекты Application в большинстве приложений Office очень похожи, и к ним применяются те же соображения. Точно так же многие разработчики считают, что удобнее и надежнее работать со скрытым окном Excel, точно так же открывать новый экземпляр Excel удобнее, чем разыскивать экземпляр, уже открытый пользователем. Для того чтобы в окне редактора кода для форм появился объект Application, точно так же необходимо в разделе Declarations кода формы объявить объект Application с ключевым словом WithEvents.

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

Свойства объекта Application:

• свойства Active возвращают соответственно активную ячейку (ту, на которую указывает курсор ввода данных), активную диаграмму, активный лист, активную книгу, активное окно. Все эти свойства доступны только для чтения. Собственно говоря, использовать их для создания объектов совсем не обязательно — объекты ActiveCell, ActiveSheet и т.п. создаются автоматически во время работы приложения и доступны всегда. Немного отличается свойство ActivePrinter — оно позволяет не только вернуть, но и установить активный принтер.
• AutoRecover — возвращает одноименный объект, который позволяет определить параметры автосохранения Excel. Например, чтобы открытые документы Excel автосохранялись каждые 5 минут, можно использовать код. Время указывается в минутах, можно использовать значения в интервале от 1 до 120.
• свойство Calculation позволяет определить или узнать режим пересчета рабочей книги (по умолчанию — автоматически, можно также использовать ручной пересчет или полуавтоматический, когда автоматически пересчитывается все, кроме таблиц). Есть смысл отключать автоматический пересчет тогда, когда пересчет значений после каждого изменения ячейки занимает много времени и мешает вводу данных
• свойство CalculationState позволяет проверять, занимается ли Excel пересчетом данных или пересчет уже завершен.
• Cells — одно из самых важных свойств объекта Application. Оно возвращает объект Range, представляющий все ячейки в активном листе активной книги.
• Очень похоже действуют свойства Columns и Rows.
• Еще раз отметим, что свойства Cells, Columns и Rows возвращают вовсе не наборы объектов Cell, Column и Row, как считают многие пользователи, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями.
• Cursor — это свойство позволяет поменять внешний вид указателя мыши в Excel
• DataEntryMode — очень интересное свойство, которое может уберечь пользователя от множества ошибок. Оно позволяет перейти в режим ввода данных — data entry mode, когда пользователю разрешается только вводить данные в разблокированные ячейки выбранного диапазона. Всего в вашем распоряжении три варианта: xlOn — включить этот режим, xlStrict — включить плюс сделать так, чтобы пользователь не мог из него выйти при помощи клавиши <Escape>, xlOff — режим отключить.
• DisplayAlerts — позволяет отключить показ различных предупреждений, которые пользователю обычно в ходе работы приложения показывать не надо (например, подавить предупреждение при закрытии ненужного файла, в котором не были сохранены изменения).
• FileSearch — это свойство позволяет провести поиск по указанному вами каталогу и вывести результаты.
• Interactive — это свойство позволяет полностью заблокировать ввод в приложение Excel со стороны пользователя (как клавиатуру, так и мышь).
• свойство LibraryPath возвращает путь к каталогу, где лежат файлы надстроек Excel — XLA. По умолчанию — \Office11\Library.
• свойство MoveAfterReturn позволяет включить/отключить переход на следующую ячейку после завершения ввода данных и нажатия на <Enter> (по умолчанию — включено), а свойство MoveAfterReturnDirection позволяет определить направление перехода. В некоторых ситуациях это может сильно упростить ввод данных пользователем.
• Range — очень важное свойство. Возвращает объект Range, который представляет собой диапазон ячеек и используется в Excel практически для любых операций с ячейками.
• ReferenceStyle — возможность переключить режим отображения ячеек между A1 (буквы — столбцы, цифры — строки) и R1C1 (когда и строки, и столбцы указываются цифрами).
• Selection — как несложно догадаться, это свойство возвращает то, что в настоящий момент выбрал пользователь. Если он выбрал обычные ячейки в таблице, то вернется объект Range. Если же пользователь выбрал что-то на диаграмме, то может вернуться объект осей, легенды и т.п. — в зависимости от того, что было выбрано.
• Sheets — возвращает коллекцию Sheets — набор листов книги (если вызывается для приложения, то для активной книги) и набор диаграмм, которые находятся на отдельных листах. Если используется свойство Worksheets, то вернется та же коллекция Sheets, но уже состоящая только из объектов Worksheet — обычных листов (без диаграмм).
• ThisCell и ThisWorkbook — очень удобные свойства, которые позволяют обращаться к текущей ячейке и текущей книге, не обременяя себя созданием объектных переменных. Эти объекты создавать не нужно — они и так изначально существуют в работающем Excel.
• Свойства Windows, Workbooks и Sheets возвращают, соответственно, все открытые окна, книги и листы Excel.
• Свойство WorkSheetFunction позволяет использовать в программе на VBA функции Excel напрямую, без необходимости прописывать их в какую-либо ячейку.

Самые важные методы объекта Excel.Application перечислены ниже:

• ActivateMicrosoftApp() — специальный метод, который предназначен для запуска и активизации (или просто активизации, если приложение уже запущено) приложений Office (Word, Access, PowerPoint).
• AddCustomList() — возможность создать новый пользовательский список. В качестве параметра принимает либо объект Range (элементами списка станут те значения, которые есть в диапазоне), либо просто стандартный объект Array. Удалить можно при помощи метода DeleteCustomList().
• методы, которые начинаются на Calculate…, позволяют пересчитать значения в ячейках. Просто Calculate() — обычный пересчет значений (чаще всего нужен, если автопересчет отключен), CalculateFull() — пересчитать значения во всех открытых книга, CalculateFullRebuild() — еще и произвести перестроение формул (аналогично занесению всех формул заново). Остановить пересчет можно при помощи метода CheckAbort().
• ConvertFormula() — возможность преобразовать формулу двумя способами: либо перевести адресацию ячеек в другой режим (например, вместо A1 в R1C1), либо поменять абсолютные ссылки на относительные и наоборот. В качестве параметра принимает строковую переменную с текстом формулы (должна начинаться с символа =) и флаги конвертации.
• DoubleClick() — эквивалентно двойному щелчку мышью поактивной ячейки, то есть переход в режим ввода данных в эту ячейку.
• Evaluate() — очень полезный и часто используемый метод. Позволяет по имени найти объект книги Excel и преобразовать его в объект или значения для дальнейшего использования. В качестве имен этот метод принимает:

• имена ячеек в стиле A1 (возвращается объект Cell)
• имена диапазонов (возвращается объект Range)
• имена, определенные в макросе (чаще всего — названия переменных)
• ссылки на внешние книги (например, Evaluate("[Book1.xls]Sheet1!A5"))

• GetSaveAsFilename() — такой же по функциональности способ, который работает с окном Save As.

• GoTo() — важный и часто используемый метод. Принимает в качестве параметра объект Range, строку со ссылкой на ячейку (в формате R1C1) или имя процедуры VBA и выделяет и активизирует диапазон/ячейку или запускает на выполнение (если передается имя процедуры). В отличие от метода Select(), этот метод еще и автоматически активизирует лист, на котором расположен диапазон или ячейка.
• Help() — этот метод позволяет открыть и показать информацию из файла справки (в том числе пользовательского). В качестве параметра принимает имя файла справки и метку темы в нем.
• метод Intersect() возвращает диапазон, который представляет область пересечения двух других (или более) диапазонов. Если передаваемые в качестве параметров диапазоны не пересекаются, возвращается Nothing.
• OnKey() — этот метод позволяет "посадить" процедуру VBA на определенную клавиатурную комбинацию. Например, чтобы назначить процедуру Msg() из модуля Лист1 клавиатурной комбинации <Alt>+<M>, можно воспользоваться командой:

Application.OnKey "%{m}", "Лист1.Msg"

• метод Repeat() позволяет просто повторить действие, которое было выполнено последним пользователем (не программным способом). Отменить действие пользователя можно при помощи метода Undo().
• метод Run() позволяет выполнить: процедуру или функцию VBA, макрос Excel или процедуру или функцию в модуле XLL (и передать ей до 30 параметров).
• Union() — это важный метод позволяет объединить два или более диапазонов.
• Wait() — это метод позволяет приостановить работу Excel на указанное вами время, сняв нагрузку с процессора. Используется для демонстраций — чтобы пользователь успел увидеть, что происходит, для ожидания завершения выполнения какой-либо внешней операции и т.п. При этом ввод пользователя блокируется, а указатель мыши приобретает вид песочных часов.

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

Следующий по иерархии после  Application объект в объектной модели Excel — это объект Workbook, который представляет книгу Excel. Он нужен для получения ссылки на нужную нам книгу в наборе открытых книг Excel, а также для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:

• первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна.
• второй способ — использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге.
• третий способ — использовать свойство Application.ThisWorkbook. При этом мы обращаемся к той книге, которой принадлежит данный программный модуль.

На практике чаще всего  нам нужно либо создать в Excel новую книгу, либо открыть существующую книгу. Для этой цели используются методы Add() и Open() соответственно.

Помимо стандартных метода в коллекции Workbooks предусмотрено также три специальных:

• OpenDatabase() — открыть базу данных, выполнить к ней запрос;
• OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл.
• OpenXML() — в качестве источника данных будет выступать файл в формате XML.

Теперь о самых  важных свойствах объекта Workbook — самой рабочей книги:

• Name, CodeName, FullName — разные имена этой книги. Самое простое имя — Name, это имя совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга будет называться в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства книги в окне Properties, кодовое имя книги будет представлено в строке (Name). Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или прямо в окне Properties).
• Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions возвращают одноименные коллекции соответствующих объектов
• ConflictResolution — как будут разрешаться конфликты изменения данных, если книга открыта несколькими пользователями сразу (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:

• запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
• по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
• получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.

• FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.

• свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге.

Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны.

2.3 Коллекция Sheets и объект Worksheet, их свойства и методы

В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

Чаще всего для ввода  данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать. Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ' Создаемновыйлист

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ' НаходимЛист1

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Обратите внимание, что  в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются "Sheet1", "Sheet2" и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств:

• Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
• EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
• EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
• Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
• свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
• QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
• Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
• Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
• UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
• Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

• Методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect() намужезнакомы .Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
• метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица);
• метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
• SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
• ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта  Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.

2.4 Программная работа с ячейками и диапазонами ячеек средствами VBA

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

• самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего).
• второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.).
• третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много. Ниже представлены некоторые самые употребимые свойства:

• Address — позволяет вернуть адрес текущего диапазона. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

На практике встречается  множество ситуаций, когда адрес  ячейки нужно разобрать на части  и вернуть из него имя столбца  или номер строки. Это очень  просто сделать при помощи строковых  функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так: sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

На первый взгляд кажется  сложным, но на самом деле все очень  просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().

• AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
• Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона.
• Точно такие же особенности у свойств Row и Rows, Column и Columns.
• Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. .
• Count — возвращает количество ячеек в диапазоне.
• CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника. Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка).
• End — еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В какой стороне будет считаться последняя ячейка, можно определить при помощи передаваемого параметра.
• Errors — свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.
• Font — это свойство позволяет получить доступ к объекту Font, при помощи которого можно настроить особенности оформления текста в ячейке (цвет, шрифт, размер букв и т.п.)
• FormatConditions — возможность создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.
• Formula — одно из самых важных свойств объекта Range. Доступно и на чтение, и на запись. Если используется на чтение, то возвращает текст формулы, прописанной в ячейку (а не вычисленное значение), если используется на запись, то позволяет записать формулу в ячейку. Если применить это свойство для диапазона, в который входит несколько ячеек, то формула будет прописана во все ячейки диапазона.
• Свойство FormulaLocal, как и AddressLocal, позволяет внести поправки на особенности нумерации ячеек в локализованной версии Excel (для русских версий Excel в нем нет необходимости).
• FormulaHidden — возможность спрятать формулы в диапазоне от пользователя. Работает только на защищенных листах.
• Interior — еще одно свойство, связанное с форматированием. В основном позволяет покрасить ячейки диапазона.
• Locked — это свойство позволяет заблокировать ячейки диапазона при защите листа.
• Next — перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если защищен — то следующая незаблокированная ячейка.
• Offset — это свойство позволяет получить новый объект Range с определенным смещением от исходного.
• свойство Orientation позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах.
• PageBreak — это свойство обычно используется для программной вставки разрывов страницы.
• Range — это свойство, как уже говорилось выше, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.
• Resize — возможность изменить текущий диапазон.
• ShrinkToFit — это свойство позволяет автоматически настроить размер текста в диапазоне таким образом, чтобы текст умещался в ширину столбца.
• Style — это свойство позволяет вернуть объект Style, представляющий стиль для указанного диапазона.
• Value — наиболее часто используемое свойство объекта Range. Позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона. Точно для той же цели используется свойство Value2, единственное отличие — это свойство не поддерживает типы данных Currency и Date.

Теперь — о методах объекта Range:

• Activate() — выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку.
• AddComment() — возможность добавить комментарий к ячейке. Ячейка будет помечена красным уголком, а текст комментария будет показан в виде всплывающей подсказки.
• AutoFill() — возможность использовать автозаполнение для диапазона (например, если первые две ячейки будут заполнены как 1 и 2, то дальше в автоматическом режиме будет продолжено: 3, 4, 5 и т.п.)
• AutoFit() — автоматически поменять ширину всех столбцов и высоту всех строк в диапазоне, чтобы туда уместился текст ячеек.
• BorderAround() — возможность поместить диапазон в рамку с выбранными вами параметрами.
• методы Clear… позволяют очистить содержимое диапазона — от значений, форматирования, комментарий и т.п.
• Copy() — возможность скопировать диапазон в другое место. Если место назначения не указано, он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона вырезаются.
• CopyFromRecordset() — очень удобный метод, который позволяет вставить данные из объекта ADO Recordset на лист Excel, начиная с верхнего левого угла указанного диапазона.
• DataSeries() — метод, который может сэкономить множество времени и избежать возни с функциями даты и времени. Этот метод позволяет увеличить вами значения даты в диапазоне на указанный вами временной интервал. Например, если у вас в диапазоне стоит первое января, то при помощи этого метода можно сгенерировать первое число любого другого месяца.
• Delete() — удаляет данные текущего диапазона. В качестве необязательно параметра можно определить, с какой стороны будут сдвигаться ячейки на место удаленных.
• Dirty() — пометить ячейки диапазона как "грязные". Такие ячейки будут пересчитаны при следующем же пересчете. Пересчитать ячейки диапазона можно и принудительно — при помощи метода Calculate().
• методы Fill… (FillDown(), FillUp(), FillLeft(), FillRight()) позволяют размножить одно и то же значение по ячейкам диапазона в указанном вами направлении.
• метод Find() позволяет произвести поиск по ячейкам диапазона и вернуть новый объект Range, который представляет первую ячейку, в котором было найдено нужное значение. Методы FindNext() и FindPrevious() позволяют продолжить поиск, начатый методом Find(), в разных направлениях.
• метод Insert() позволяет вставить ячейки в диапазон, сдвинув остальные (можно выбрать — вправо или вниз).
• метод Justify() позволяет равномерно распределить текст по диапазону. Если в данный диапазон он не помещается, он будет распространен на соседние ячейки (с перезаписью их значений).
• Parse() — позволяет разбить одну ячейку на несколько по указанному вами шаблону.
• PasteSpecial() — операция, дополняющая Copy() и Cut(). Она позволяет вставить то, что лежит в буфере обмена, с указанием специальных параметров вставки (вставлять с добавлением к существующим данным, с умножением, вычитанием, делением и т.п.).
• PrintOut() и PrintPreview() — позволяют вывести диапазон на печать или открыть режим просмотра перед печатью.
• Replace() — метод, дополняющий метод Find(). Позволяет проводить поиск и замену значений в диапазоне.
• Select() — возможность выделить указанный диапазон. Объекта Selection в Excel нет — вместо него есть возможность получить объект Range, представляющий выделенную область.
• Show() — экран будет проскроллирован таким образом, чтобы показать указанный диапазон.
• ShowErrors() — показать источник ошибки для указанной ячейки.
• Sort() — возможность произвести сортировку ячеек в диапазоне. Можно использовать большое количество необязательных параметров для настройки сортировки.
• метод Table() позволяет создать таблицу на основе передаваемого столбца, строки и функции, которую нужно использовать для вычисления ячеек таблицы.
• TextToColumns() — сложный метод, который позволяет разбить столбцы в диапазоне на несколько столбцов в соответствии с определенным алгоритмом.