Стив Каммингс vba для чайников

Определенные пользователем ссылки на объекты. Поскольку диапазон является объектом, можно установить именованную объектную ссылку на него, после чего доступ к диапазону осуществляется с помощью указания имени ссылки. Данную технику использовать проще и быстрее, чем многократное указание оригинального диапазона. После установки объектной ссылки RanGer, как показано в последующем примере, можно использовать его свойства в таких выражениях, как RanGer . Value:
Dim RanGer As Range Set RanGer = Worksheets("Лист1").Range("B12:H13" )

Использование свойств Cells для определения диапазона

При использовании без координат свойство Cells объекта Worksheets указывает на диапазон, включающий все ячейки данного рабочего листа. По аналогии, свойства Cells объекта Application ( Application. Cells ) ссылаются на все ячейки листа, активного в данный момент (свойство Cells может использоваться само по себе, без указания в явном виде объекта Application).

Если необходимо остановиться на более локализированном диапазоне, для свойства Cells требуется числовое указание координат строки и столбца (буквенное указание столбца не допускается). В следующем примере указан диапазон, заключающий ячейку ЕЗ:
Worksheets("Старые новости").Cells ( 3,5)
Весьма непривычно то, что сначала указывается координата строки, а затем столбца, в противоположность записи в А1-стиле. В предшествующем примере второе значение в ссылке на ячейку указывает на столбец Е, т.е. пятый столбец. Вследствие того, что данная система трудна для понимания, работать с ней следует лишь в случае особой необходимости. Поскольку обе координаты являются числами, можно очень просто указывать их через переменные. Переменные координаты позволяют программе (при работе) принимать решение о том, где находится требуемый диапазон на основе введенных пользователем данных, результатов вычислений и т.д.
В следующем примере строка выбирается в зависимости от текущего года и месяца: intMonth = Month (Now О)
aGoal = Worksheets("Monthly Projections").Cells ( intMont h, 8)
Способом чуть похитрее можно указать диапазон, покрывающий больше одной ячейки:
Range( Cells ( 3,5), Cells ( 4,6) )
В приведенном здесь примере указывается диапазон ячеек 2x2 на активном рабочем листе, включающий ячейки ЕЗ (строка 3, столбец 5) в верхнем левом и ячейку F4 в левом нижнем углах.
Для определения диапазона на неактивном листе необходимо использовать совместно свойства Range и Cells для требуемого листа. Следующее выражение выполняет данную работу, правда, имеет достаточно громоздкий вид:
Worksheets("2"). _
Range(Worksheets("2").Cells(3,5).
Worksheets("2">.Cells(4,6))
Оператор With позволяет избежать излишних ссылок на рабочий лист. В следующем примере к тексту всего диапазона применяется полужирное форматирование:
With Worksheets("Лист2")
.Range(.Cells ( 3,5), .Cells ( 4,6) ).Font.Bold = True
End With
Обратите внимание, что точки перед каждым использованием свойства Cells необходимы для связи любой ссылки с требуемым рабочим листом. В случае отсутствия точек каждое свойство Cells будет относится к активному листу, что приведет к возникновению конфликта со ссылкой свойства Range.

Выполнение совместных действий с ячейками

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

Worksheets ("Лист1").Range("B12:H13").Font.Size = 14
Безусловно, можно и даже предпочтительно использовать структуру With, когда необходимо работать с несколькими свойствами или методами целого диапазона, как показано здесь:

With someRange ' определенная ранее объектная ссылка

.Value = 20 ' Значение всех ячеек устанавливается равным 20
.Font.Name = "Garamond"
. Font.Italic = True
.Locked = True someRange = .Name ' Сохраняется имя диапазона
End With

Работа с отдельными ячейками диапазона

Хотя можно с помощью одного оператора назначить одно значение всем ячейкам диапазона, как показано в предыдущем примере, в Excel нет метода, позволяющего с помощью единственного действия изменять имеющиеся значения многоячеечного диапазона. Оператор вроде someRange .Value = someRange . Value + 10 не работает. Вместо этого необходимо осуществить циклический перебор всех ячеек диапазона с помощью цикла For Each. . , Next. При использовании данной техники не требуется знание количества ячеек, входящих в диапазон. Вот пример работоспособного кода:

For Each aCell In Selection

aCell.Value = aCell.Value + 10
Next

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

For Each aCell In Worksheets("2").Range("A5;B10")

If IsNumeric(aCell) Then
Select Case aCell
Case 5 to 10
aCell.Font.Underline = xlUnderlineStyleSingle
Case 10 to 20
aCell.Font.Italic = True
Case Is > 20
aCell.Font.Bold = True
End Select
End If
Next

Работа с выделениями

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

Во многих случаях пользовательский код должен воздействовать на выбранные ячейки, как это делают встроенные команды Excel. Для доступа к выделенному пользователем диапазону используется свойство Selection (Выделение) объекта Application или Window. Свойство Selection объекта Application возвращает диапазон, выделенный на рабочем листе, активном в данный момент. Следующие два оператора идентичны:

Application.Selection.Value = 20

Selection.Value =2 0

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

Dim Sel Range As Range

Set SelRange = Windows("Инвентаризация игрушек.xls">.Selection
With SelRange
.CheckSpelling
.AutoFit
.Copy
End With

Когда VBA-программа вносит изменения в рабочий лист и необходимо, чтобы пользователь их заметил, следует воспользоваться методом Select (Выделить) для перемещения выделения на нужный диапазон, диаграмму или что-либо другое. Метод Select можно применять фактически к любому существующему в Excel объекту, в том числе к объектам Chart (Диаграмма) и всем их компонентам (каждая часть диаграммы является отдельным VBA-объектом). к объектам Shape (Форма) и, конечно же, к объектам Range.

Для выделения диапазона необходимо сначала активизировать рабочий лист, на котором диапазон находится, а затем использовать метод Select для объекта Range, как показано в данном примере:

With Worksheets(" ")

.Activate .Range("Разбитые сердца").Select
End With

Кстати, метод Select для объектов Worksheet, очевидно, не выполняет ничего, кроме активизации указанного рабочего листа, и не изменяет в нем существующее выделение. Другими словами, он эквивалентен методу рабочего листа Activate. Подобным образом методы Activate и Select можно использовать для активизации листа диаграммы, но ни один из методов в действительности не выделяет диаграмму. Вот пример;

Chart s("Места жительства клиентов").Select
Для того чтобы выделить компонент диаграммы или внедренную диаграмму, используйте метод Select для интересующего вас объекта.
Чтобы сделать ячейку активной для ввода, к данной ячейке используется метод Activate (Активизировать) объекта Range. Если активизированная ячейка находится в пределах текущего выделения, весь диапазон остается выделенным. Именно таким образом работает следующий пример:
Worksheets("НичегоНеДелайДоМоегоУказания").Activate
Range("A1:E7").Select Range("C4").Activate
Если активизированная ячейка находится за пределами выделения, оно перемещается на активизированную ячейку.
Код для работы с выделенными ячейками, вероятно, приведет к возникновению ошибки, если его применить к диаграмме. Вот почему, прежде чем выполнять какие-либо операции с выделением, обычно следует убедиться, что выделение содержит тот тип объектов, который ожидается в коде. VBA-функция TypeName возвращает строку, содержащую тип объекта
выделения. С помощью структур If. . .Then или Case. . .Select можно определить, какое из возможных действий проводить над выделенным объектом.
В приведенном ниже примере выражение TypeName ( Selection) в операторе Select (вторая строка кода) возвращает строку, содержащую тип объекта текущего выделения. Данная строка затем проверяется рядом операторов Case, является ли выделение диапазоном (в таком случае его значение устанавливается равным 2 001), областью диаграммы (в таком случае устанавливается красный цвет выделения), когда же выделение не сделано, пользователь видит сообщение об отсутствии выделения. Также предусмотрен вывод соответствующего сообщения для случая, когда выделение не принадлежит ни одному из перечисленных типов:

With Selection

Select Case TypeName(Selection)
Case "Range"
.Value = 2001
Case "ChartArea"
.Interior.Colorlndex = 3 ‘ 3 = bright red
Case "Nothing"
MsgBox "Ничего не выделенно"
Case Else
MsgBox "Невозможно определить тип выделения!"
End Select
End With

Программирование пользовательских функций

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

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

Написание пользовательских функций рабочего листа

Пользовательские функции Excel- это просто обычные процедуры VBA-функций. Детально с процедурах функций и их синтаксисе можно узнать из главы 6. Если сказать коротко, процедура функции начинается с декларации ее имени и заканчивается оператором End Function. Иногда внутри может потребоваться оператор, присваивающий значение имени функции, данное значение как раз и возвращает функция. В простейшем примере, приведенном здесь, именно это и демонстрируется:

Function MemoryAvailable ()

MemoryAvailable = Application.MemoryFree
End Function

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

Function CheckForvalue(aRange,Value)

For Each objCell In aRange
CheckForValue = False 'по умолчанию возвращается значение
False
If objCell.Value = Value Then
CheckForValue = True
Exit For
End If
Next objCell
EndFunction

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

Запуск пользовательских функций

Один из способов запустить функцию, - это, конечно же, использовать стандартную процедуру запуска в VBA, т.е. вызвать ее внутри процедуры Sub. Детально данная техника описана в главе 6.

Для того чтобы вставить в рабочий лист возвращаемое функцией значение, следует использовать функцию таким же образом, как используется любая из встроенных функций: внести имя функции в ячейку после знака равенства. После имени печатаются круглые скобки с любым аргументом внутри. Скобки необходимы даже в случае, когда аргументы отсутствуют, как в следующем примере:
=MemoryAvailable()
Так же как и встроенные, пользовательские функции могут быть частью более сложной формулы ячейки, как в приведенном примере:
=MemoryAvailable() & ""сейчас доступно байт"
=IF(CheckForValue(Б8:В18,С8)),"Значение найдено","Значение не найдено")
Действительно замечательно то, что Панель формул распознает пользовательские функции. Если вы не помните, какие аргументы требуются для функции и принимает ли функция вообще какие-либо аргументы, не стоит беспокоится, Панель формул показывает рее, что необходимо для функции
Можно добавить к функции описание, которое будет появляться в диалоговом окне Вставка функции при выборе функции. Для создания описания выберите команду Сервис=Макрос=Макросы и затем в поле Имя макроса введите имя функции (функции не отображаются в списке макросов). Теперь щелкните на кнопке Параметры для ввода описания функции в соответствующее поле.
Если нужная функция хранится в текущей книге, достаточно просто написать ее имя г( ячейке формулы. Для того чтобы использовать функцию, хранящуюся в другой открытой книге, имени функции должно предшествовать имя рабочей книги и восклицательный знак, как показано в следующем примере:
=Примитивные фунции.xls!УмнаяФункция (C4:D6,M9)
Для доступа к функциям, хранящимся в книгах, не открытых в текущий момент или закрытых при возникновении доступа к ним в дальнейшем, необходимо настроить на них ссылку VBA. Выберите команду Tools=References. Если нужная рабочая книга не представлена в списке диалогового окна, щелкните на кнопке Browse для ее локализации и добавления в список. Если флажок рядом с книгой не установлен, сделайте это для активизации ссылки.
Теперь любую из содержащихся в книге функций можно использовать, просто указав ее имя, не указывая имени книги.
Если при выполнении пользовательской функции, помещенной в формулу рабочего листа, происходит ошибка, обычное сообщение об ошибке VBA-функции не выводится.
Все что можно увидеть вместо этого, - это неясное сообщение об ошибке вроде
#ЗНАЧ! в ячейке, содержащей формулу. При построении пользовательской функции можно воспользоваться следующими приемами, которые помогут в тестировании и отладке функции.
Прежде чем поместить функцию в рабочий лист, протестируйте ее, вызвав через процедуру Sub. Такой способ вызова позволяет получить сообщение об ошибке VBA и доступ к инструментарию отладки. Если функция требует ссылки на ячейки, необходимо при вызове функции использовать объект Range, как показано в данном примере:
Sub FxTester()
ReturnVal = CheckForValue(Range("B8:B13"),Range("C8"))
MsgBox ReturnVal
End Sub
В коде функции, записанной в формуле рабочего листа, в редакторе Visual Basic создайте точку разрыва. Функция будет запускаться при каждом пересчете Excel рабочего листа. Как только VBA достигнет строки, содержащей точку разрыва, вы автоматически попадете в редактор, готовый к выполнению отладки.
Профессиональное написание функций
Не стоит отказываться от возможности написания функции таким образом, чтобы выдаваемый се результат был именно таким, как вам того хотелось бы. Зачем поручать формуле выполнять то, что можно сделать в самой функции? Приведенная модификация функции CheckForValue (она приводилась в разделе "Написание пользовательских функций рабочего листа") вместо непонятных значений True и False возвращает строку поясняющего текста:
Function CheckForVaiue2(aRange, Value)
For Each objCell In aRange
CheckForValue2 = "искомое значение" 6 Value & _
"не найдено"
If objCell.Value = value Then
CheckForValue2 = "искомое значение" & Value & _
"находится в ячейке" & objCell.Address
Exit For
End If
Next objCell
End Function
Если модифицированной функции удается найти искомое значение где-либо внутри диапазона, она возвращает строчку вроде Искомое значение 3, 57 находится
Б ячейке SFS83; в противном случае возвращается строчка Искомое значение
3,57 не найдено.

Использование в scoqe встроенных функций

Независимо от того, пишете ли вы собственную функцию или процедуру Sub, не стесняйтесь призывать на помощь широчайшие возможности встроенных функций Excel по анализу данных и проведению вычислений. Использовать их в своем собственном коде очень просто: необходимо вызвать их как метод объекта Worksheet Functions. Предположим, что нужно написать процедуру, проводящую вычисления на основе среднего значения чисел определенного диапазона.

Дня получения среднего значения можно использовать код, подобный следующему:
OnAverage = Worksheet Function.Average ( Range ("В8:B13") )
Некоторые из встроенных функций Excel запрещены в VBA. Это касается функций, дублирующих встроенные VBA-функции, о которых шла речь в главе 11.

Программирование событий 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 < 11 Then
With oCell.Font
.Bold = True
.Size = 16
.Color = RGB(0, 255, 0) ‘ Зеленый
End With
End If
Next oCell
End Sub

Обратите внимание, что 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)
Sh.Range("Al") = ActiveCell.Address
Application.StatusBar = Sh.Name & " : " & Target.Address
End Sub

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

Private Sub Worksheet_SelectionCharge (ByVal Target As Excel.Range)

If Target.Address = "$3$2" Then
MsgBox "Вы нашли нужную ячейку!"
End If
End Sub

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

* DragOver и DragPlot. Происходят, когда ячейки перемешаются над внедренной диаграммой или опускаются на нее соответственно.
* MouseDown. MouseUp и MouseMove. Происходят в ответ на действия мыши.
* Select. Происходит при выделении какой-либо части диаграммы. Написанный код может определять тип и характеристики выделенного элемента и проводить соответствуюшие действия.
* Series Change. Происходит, когда пользователь изменяет значение в диаграмме через саму диаграмму (а не изменяя значения, содержащиеся в листе).