Vba excel события листа

События в Excel

Термин “Событие Excel” используется для обозначения определённых действий, совершаемых пользователем в Excel. Например, когда пользователь переключает лист рабочей книги – это событие. Ввод данных в ячейку или сохранение рабочей книги – это тоже события Excel.

События могут быть связаны с рабочим листом Excel, с диаграммами, рабочей книгой или непосредственно с самим приложением Excel. Программисты могут создавать код VBA, который будет выполняться автоматически при наступлении того или иного события.

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

А если нужно, чтобы макрос запускался каждый раз при переходе на какой-то определённый рабочий лист (например, Лист1), то код VBA должен быть связан с событием Activate для этого листа.

Код VBA предназначенный для обработки событий Excel, должен быть помещён в соответствующем объекте рабочего листа или книги в окне редактора VBA (редактор можно открыть нажатием Alt+F11). Например, код, который должен выполняться каждый раз при возникновении определённого события на уровне рабочего листа, должен быть размещён в окне кода для этого рабочего листа. Это показано на рисунке:

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

Кликните по нужному событию в правом выпадающем меню, и в окно кода для этого объекта будет автоматически вставлена процедура Sub. В заголовке процедуры Sub Excel автоматически вставляет необходимые аргументы (если таковые имеются). Остаётся только добавить код VBA, чтобы определить, какие действия процедура должна выполнить, когда нужное событие будет обнаружено.

Пример

В следующем примере каждый раз при выборе ячейки B1 на рабочем листе Лист1 появляется окно с сообщением.

Чтобы выполнить это действие, нам нужно использовать событие рабочего листа Selection_Change, которое возникает каждый раз, когда изменяется выделение ячейки или диапазона ячеек. Функция Selection_Change получает в качестве аргумента Target объект Range. Так мы узнаём, какой диапазон ячеек был выделен.

Событие Selection_Change происходит при любом новом выделении. Но нам нужно, чтобы набор действий был выполнен только при выделении ячейки B1. Для этого будем отслеживать событие только в заданном диапазоне Target. Как это реализовано в программном коде, показанном ниже:

Как отследить событие(например выделение ячеек) в любой книге?

Иногда при разработке надстройки просто необходимо отследить какое-либо событие в книге. Но модуль ЭтаКнига и модули листов надстройки позволяют отследить лишь те события, которые происходят в той книге, в которой этот код прописан. А как же другие книги? Как, например, отследить событие открытия любой книги в Excel и сделать какое-то действие в зависимости от имени открытой книги? Или как отследить выделение ячейки в любой книге? Изменение значений ячеек?

Если не знаете что такое надстройка — Как создать свою надстройку?

На самом деле все до смешного просто:
В модуле ЭтаКнига главной книги(надстройка либо PERSONAL.XLS) необходимо создать переменную, которая будет ссылкой на все приложение Excel

Private WithEvents App As Application

На событие открытия главной книги (той, в которой пишется код и в которой объявили переменную App — опять же это надстройка либо PERSONAL.XLS) присваиваем этой переменной App значение запущенного приложения Excel:

Private Sub Workbook_Open() Set App = Application End Sub

Т.е. мы теперь имеем как бы свою локальную управляемую ссылку на Excel. Это позволит нам получить доступ к событиям приложения Excel из VBA и отследить их. И среди прочих событий есть такие, которые относятся ко всем открытым книгам. Т.е. то же выделение ячеек мы сможем обработать только внутри своей надстройки, но срабатывать оно будет при выделении ячеек в любой открытой книге.
Теперь создаем непосредственно событие — аналогично выбору других событий в книге в левом окне выбора объектов выбираем App. В правом появятся все доступные события для нашего объекта App:

в этом окне перечислены все события, которые могут быть «перехвачены» в любой открытой книге, а не только той, в которой этот код записан. Сразу после выбора какого-либо события из списка автоматически будет создана пустая процедура, в которую надо будет лишь добавить необходимый код.
Рассмотрим некоторые из этих событий.

Вот так, например, будет выглядеть код отслеживания открытия любой книги :

Private Sub App_WorkbookOpen(ByVal Wb As Workbook) MsgBox «Вы открыли книгу:» & Wb.Name End Sub

Теперь при открытии любой книги будет появляться сообщение с именем именно открытой книги.
Wb — это переменная событийной процедуры. Для каждой процедуры они могут отличаться или вовсе отсутствовать. Но если они есть — значит можно их использовать. Например, в этой процедуре( App_WorkbookOpen ) Wb это открываемая книга, т.е. объект типа Workbook. И к ней можно обращаться как к любой книге: перебрать листы, изменить какие-то свойства и т.п. Например, в коде выше я просто вывожу в информационное окно с именем открываемой книги.

Сам по себе код не заработает. Т.к. назначение значения переменной App происходит только при открытии самой книги(надстройки или PERSONAL), то после создания кодов надо будет сохранить эту книгу и открыть заново

А с помощью этого кода можно отследить создание новой книги :

Private Sub App_NewWorkbook(ByVal Wb As Workbook) MsgBox «Вы создали новую книгу» End Sub

Отслеживаем выделение ячеек во всех открытых книгах :

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) MsgBox «Вы выделили ячейку с адресом: » & Target.Address End Sub

Target — это объект Range(ячейка или диапазон ячеек), которые были выделены в книге.
Sh — это объект Worksheet, ячейки которого были выделены.
Таким образом у нас есть две переменные, которые мы можем использовать. Например, можно производить определенные действия только на листах с конкретным именем:

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = «Отчет» Then MsgBox «Вы выделили ячейку с адресом: » & Target.Address End If End Sub

А процедура ниже поможет отследить изменение значений ячеек во всех открытых книгах и отменить нежелательные изменения:

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim bUndo As Boolean If Sh.Name <> «Для изменений» Then If Sh.Name = «Описание» Then ‘для этого листа можно изменять только ячейки диапазона «A16:B20» If Intersect(Target, Sh.Range(«A16:B20»)) Is Nothing Then MsgBox «На этом листе изменять можно только ячейки в диапазоне ‘A16:B20’!», vbCritical, «www.excel-vba.ru» bUndo = True End If Else ‘для всех других листов, кроме листа «Для изменений» — изменять значения ячеек вообще нельзя MsgBox «Ячейки на этом листе нельзя изменять!», vbCritical, «www.excel-vba.ru» bUndo = True End If If bUndo Then With Application .EnableEvents = False .Undo .EnableEvents = True End With End If End If End Sub

В приложенном к статье файле будет чуть более понятно что делает эта процедура.

Естественно, в таких процедурах можно назначить выполнение и других(нужных) действий. Например, вызов макроса ( Call ИмяМакроса ). Макрос в таком случае должен быть размещен в стандартном модуле и иметь статус Public (или вовсе без статуса). Сам модуль должен тоже находится в той же книге.

Tips_Macro_How_Catch_Events.xls (60,5 KiB, 3 865 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Супер. очень доходчиво, понятно. То что искал. Ставлю +1 в гугл плюс

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox («Вы выбрали ячейку с адресом: » & Target.Row & » » & Target.Column) End Sub

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

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

Ну, и как же сделать РАБОЧЕЕ событие для отслеживания события при свёртывании приложения Excel?

Imort, для начала задать вопрос не «ну, и как же» — здесь Вам никто ничего не должен. После чего прочитать внимательно статью и сделать как написано. И просмотреть все доступные события. Может тогда найдете событие WindowResize. И найдете у передаваемого объекта Wn свойство WindowState.

Спасибо, отличный макрос

Ни auto_open на листе, ни Workbook_Open на книге не срабатывают. Как еще можно запустить макрос автоматически?

Игорь, давайте поменьше категоризма и побольше конкретики. Кто сказал, что auto_open должен быть на листе? Эта процедура размещается в стандартном модуле, т.к. она использовалась в те лихие времена, когда событийные процедуры листов и книг не встраивались в листы и книги.
Не работает только у Вас. Значит и дело в Вас или Вашем ПК, Excel. Опишите подробно свои действия — как что и куда записываете, как проверяете работу. Включены ли макросы вообще.

Дмитрий, прошу помощи)

У меня есть форма — нажимая на кнопку открывается нужный мне файл Excel, а форма сворачивается.
Как сделать так, чтобы после закрытия файла — форма опять отображалась?

Private WithEvents App_calc As Application
Sub Workbook_Open()
Set App_calc = Application
End Sub

Private Sub App_calc_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Wb.Name = «123_456_3.xlsm» Then
Application.EnableEvents = True
Cancel = False
F_Main_NEW.Show
End If
End Sub

Так не получается — форма открывается, а файл не закрывается, пока форму не закроешь 🙂

У Вас главная проблема в том, что форма показывается в модальном режиме. Это означает, что после показа формы дальнейшее выполнение кода в той процедуре, из которой форма вызвана, приостанавливается до тех пор, пока форма не будет закрыта.
Поэтому варианта два:
1. Отображать форму не модально: F_Main_NEW.Show 0
2. Отображать форму с запозданием. Создаете отдельную процедуру:

Sub ShowMainUF() F_Main_NEW.Show End Sub

а в процедуре WorkbookBeforeClose вызывать уже через Application.OnTime эту процедуру:

Private Sub App_calc_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) If Wb.Name = «123_456_3.xlsm» Then Application.OnTime Now+TimeValue(«00:00:01″),»ShowMainUF» End If End Sub

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

Дмитрий, добрый день!
Подскажите, пожалуйста.Хочу чтобы при сохранении файла в определенном листе фиксировалась дата сохранения(так сказать журнал изменений файла). Т.е. прописываю нужный мне код на Sub Workbook_AfterSave. Отрабатывает отлично. Но на случай сохранения файла через закрытие и появления диалогового окна о сохранении, я добавил этот же код на событие Workbook_BeforeClose. но к сожалению при нажатии пользователем на кнопку отмены — скрипт уже отработал. Есть ли возможность отловить эти кнопки на диалоговом окне сохранения? Заранее благодарю за помощь.
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
With Sheets(3)
lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(«A» & lLastRow).Value = Now
.Range(«B» & lLastRow).Value = Application.UserName
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=»999999″
With Sheets(3)
lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(«A» & lLastRow).Value = Now
.Range(«B» & lLastRow).Value = Application.UserName
End With
If Cancel Then Sheets(1).Unprotect Password:=»999999″
End Sub

Sub Workbook_Open()
Sheets(1).Unprotect Password:=»999999″
End Sub

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

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Программирование событий excel

Программирование событий excel

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

Выбор правильного объекта

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

Хотя некоторые события Excel распознают лишь определенные объекты, большинство из них образует иерархию от объекта Worksheet до объектов Workbook и Application. Например, изменения, внесенные в рабочий лист, инициируют событие Change (Изменение) объекта Worksheet, который в свою очередь инициирует событие Sheet Change (Изменение листа) для объектов Workbook и Application.

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

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

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

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

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

1. Откройте окно редактирования кода для объекта.

2. Выберите объект, выбрав его имя из раскрывающегося списка Объект в левом верхнем углу окна.

3. Выберите из раскрывающегося списка Процедура, в правом верхнем углу окна, событие, для которого должен быть написан код.

4. При этом в окне появляется каркас выбранной процедуры сообщения.

Кроме написания самого кода, единственным сложным моментом может быть вынос окна редактирования кода на первое место (п. 1 в приведенном выше списке). Для объектов Worksheet, Workbook и Chart, занимающих отдельный лист, никаких особенностей нет- необходимо просто выбрать объект в окне обозревателя и щелкнуть на кнопке View Code. Объекты представляются в алфавитном порядке, а их имена можно изменять в окне Options.

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

Если необходимо, чтобы код реагировал на действия, производимые пользователем с рабочим листом, потребуются такие инструменты, как свойства Change, Calculate и SelectionChange (для объектов Worksheet) и соответствующие события SheetCnange, Sheet Calculate и SheetselectionChange (для объектов Workbook и Application). Для запуска пользовательских процедур в то время, когда сами рабочие листы или диаграммы активизированы или не активизированы, используются события Activate и DeActivate.

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

Private Sub Worksheet_Change(ByVal Target As Range)

For Each oCell In Target

If oCell> 4 And oCell

Обратите внимание, что Target может быть не одной ячейкой, а диапазоном, благодаря тому, что операции заливки, удаления и вставки могут применяться одновременно к нескольким ячейкам. Для этого процедура сообщения Change должна включать в себя структуру

For Each . Next; в таком варианте процедура применима как отдельной ячейке, так и к нескольким ячейкам, что и демонстрирует приведенный выше пример.

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

Событие Calculate распознаваемое как объектом Worksheet, так и Chart, происходит при каждом обновлении программой Excel рабочего листа или диаграммы.

Событие Sheet Calculate для объектов Workbook и Application происходит в ответ. Если средство автоматического вычисления включено, данное событие активизируется, как только изменяется значение любой ячейки, т.е. происходит в тандеме с событием Change. Когда средство пересчета включено в ручном режиме, событие Calculate происходит лишь тогда, когда пользователь инициализирует пересчет нажатием клавиши .

Процедуры событий для событий Calculate и SheetCalculate используются для изменения рабочего листа в соответствии с результатами вычислений. Например, если известно, что пересчет может изменить элементы упорядоченного списка, целесообразно использовать процедуру события Worksheet _Calculate для упорядочения списка после проведения вычислений. Поскольку данные процедуры не сообщают, какая из ячеек изменялась в результате вычислений, необходимо поместить в код адреса ячеек, которые требуется изменить.

При каждом перемещении активной ячейки, а также при расширении или сжатии выделения Excel вызывает событие SelectionChange для Worksheet. Параллельно происходит событие Sheet SelectionChange для объектов Workbook и Application. Процедуры событий для данных событий можно использовать для обратной связи с текущим выделением. В приведенном ниже примере событие Selection Change используется для отображения в левой верхней ячейке текущего листа адреса активной ячейки, а также для помещения имени рабочего листа и адреса выделения в строку состояния. Обратите внимание на то, как аргумент Sh позволяет идентифицировать и вести работу с текущим листом:

Private Sub Workbook Sheet SelectionChange (ByVal Sh As Object, _

ByVal Tarqet As Excel.Range)

Application.StatusBar = Sh.Name & » : » & Target.Address

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

Private Sub Worksheet_SelectionCharge (ByVal Target As Excel.Range)

If Target.Address = «$3$2» Then

MsgBox «Вы нашли нужную ячейку!»

Поскольку объекты диаграмм Excel способны распознавать большое число событий, многие из которых связаны с мышкой, их можно считать большим элементом управления ActiveX, помещенным в рабочей книге. Диаграммы распознают события Activate, DeActivate и Calculate. Вот еще некоторые события, для которых возможно написание кодов.

* DragOver и DragPlot. Происходят, когда ячейки перемешаются над внедренной диаграммой или опускаются на нее соответственно.

* MouseDown. MouseUp и MouseMove. Происходят в ответ на действия мыши.

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

* Series Change. Происходит, когда пользователь изменяет значение в диаграмме через саму диаграмму (а не изменяя значения, содержащиеся в листе).

События листа

Задача примерно такая — имеется четыре ячейки, в которых находятся либо ноль, либо единица.
Для простоты, пусть это будет первая строка и столбцы с 2 по 5.
В первом столбце идентификатор объекта.
Ноль или единица задают некие свойства объекта, при этом единица в наборе может быть только одна.
Например, объекты это люди, а свойства это цвет волос.
Блондин, брюнет, рыжий, шатен.
Если запишем, что Джек рыжий, то остальные поля должны быть сброшены на ноль.
Допускаются все нули — Джек лысый
Формулами такое записать трудно, если вообще возможно.
Программно тоже не обработаешь, потому что если Джек и блондин и брюнет одновременно, то непонятно, что сбрасывать.

Здесь хорошо бы иметь обработчик события — вставил куда-то единичку, тогда понятно, что сохраняемся именно в этой ячейке, остальные сбрасываем.

Знает кто-нибудь про события листа?

Не работают события ЛИСТА
Всем привет! Пожалуйста, помогите решить проблему: делаю простую складскую программу в Excel. .

Макрос глючит из-за события листа
Добрый вечер дорогие форумяне! есть макрос, который глючит при выполнении копирования с одного.

Программное создание обработчика события для существующего листа
Доброе время суток! При программировании под Excel (2010) возникла проблемка: есть модуль, в.

Програмное копирование кода из стандартного модуля в модуль листа (код события)
После создания листа sheet(2) на нем програмно помещается кнопка "ПЕРЕСЧИТАТЬ под другую.

Что Вам рассказать?

Добавлено через 7 минут
Код в модуль листа:

Рассказать, как строится обработчик события.
И еще — как этот обработчик создать, когда страница создаётся методом :

Резонно!
Тем более что в моем случае вновь создаваемая страница заполняется по шаблону.
Правда, окончательная цель превращение книги в Хлам, а я не уверен, что можно скопировать страницу из Хлам»а.
Но все равно нормально!

Добавлено через 4 часа 5 минут
Не всё так однозначно.
Дело в том, что если на странице есть код, то Excel требует сохранить её (книгу) в формате Xlsm.

В принципе, ничего страшного нет, если документы будут сохраняться в в Xlsm.

Теперь надо проверить, копируются ли страницы из Хлам»а.

Добавлено через 38 минут
Надо добавлять функции активации/деактивации
Примерно вот так:

Соответственно, событие обрабатывается только при выставленном BitSetChanger, который выставляется при входе на страницу и сбрасывается при её покидании.

Наверное, можно как-то по другому включить/выключить обработчик.
=========================

А всё-таки, можно как-то задать обработку события на лету, как-то вставить код при входе на страницу?

Да, эта страница заполняется другим кодом, а указанная обработка нужна при ручном редактировании этой страницы.
Тут дело в особенностях задачи — страница заполняется в результате анализа первичных данных, в которых не содержится информация по цвету волос Джека. Примерно так — Джек может быть блондином, брюнетом, шатеном, рыжим иногда может быть, иногда не может, во всяком случае по первичным данным в некоторых случаях, но не всегда, можно вычислить, рыжий он или нет.
Цвет волос Джека может определить только пользователь, и задаёт вручную.

Добавлено через 26 минут
Если обработка будет происходить при неактивной странице, то начнутся хаотичные перевычисления, они ни к чему. Ну а за правильность заполнения отвечает код.
======================
Проверил по Хлам»у. Страница копируется нормально.
В общем, на данный момент только одно неудобство — надо сохранять документы в Xlsx
С этим, наверное, надо смириться.

Добавлено через 18 минут
Поправка:

Проверил по Хлам»у. Страница копируется нормально.
В общем, на данный момент только одно неудобство — надо сохранять документы в Xlsm
С этим, наверное, надо смириться.

Именно поэтому и хотелось вставлять код программно,

VBA в Excel Объект Excel.Worksheet и программная работа с листами Excel средствами VBA

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

Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В 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 — то же самое для предыдущего листа.
• PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл ->Параметры страницы.
• свойство 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(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.