«Bill Jelen, Mr. Excel Tracy Syrstad 800 East 96th Street Indianapolis, Indiana 46240 ?изнес-решения Применение VBA и макросов в Microsoft® Excel Билл Джелен, “Мистер Excel” Трейси Сирстад Москва • Санкт-Петербург • Киев ...»

Изменение выражения в окне Immediate часто применяется при отладке проблемных участков кода. В подобных ситуациях может пригодиться самая различная информация имя активного рабочего листа (Print ActiveЗнакомство с Visual Basic for Applications Глава sheet.Name), адрес выбранного диапазона ячеек (Print Selection.

Address), адрес активной ячейки (Print ActiveCell.Address), формула активной ячейки (Print ActiveCell.Formula), значение активной ячейки (Print ActiveCell.Value или же просто Print ActiveCell, так как Value является стандартным свойством ячейки) и т.д.

Вычисление значения с помощью указателя мыши Чтобы узнать значение выражения, подведите к нему указатель мыши и за держите в таком положении пару секунд. На экране появится подсказка, со держащая текущее значение выражения. Как правило, этот прием оказывает ся наиболее полезным при отладке циклов (см. главу 5, ‘‘Циклы и управление выполнением кода’’). Пригодится он и при работе с автоматически сгенери рованным кодом. Заметьте, что выражение, значение которого вычисляется описанным выше способом, не обязано содержаться в только что выполнен ной строке кода. Как показано на рис. 2.33, макрос только выделил все ячейки (при этом текущей активной ячейкой является ячейка A1). Подведя указатель мыши к выражению ActiveCell.FormulaR1C1, можно узнать, что его зна чением является строка СчетДата.

Рис. 2.33. Чтобы узнать значение выражения, задержите над ним указатель мыши 82 Часть I Первые шаги Иногда окно просмотра кода редактора Visual Basic не реагирует на указа тель мыши. Поскольку некоторые выражения не имеют значения, назвать причину отсутствия подсказки удается не сразу. Подведите указатель мыши к выражению, которое всегда должно иметь значение, например, к переменной.

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

Вам все еще не нравится Visual Basic? Бьюсь об заклад, что после знакомства с его рабочей средой вы настроены гораздо менее категорично. Эти средства отладки просто потрясающи!

Окно Watches Окно Watches (Просмотр) позволяет отслеживать значение любого выра жения во время выполнения кода. Отследим текущий адрес выделенного диа пазона ячеек (Selection.Address).

Выберите команду меню редактора Visual Basic Debug Add Watch (Отладка Добавить в окно просмотра).

Введите Selection.Address в текстовом поле Expression (Выражение) диалогового окна Add Watch (Добавить в окно просмотра) и щелкните на кнопке OK (рис. 2.34).

Окно Watches обычно располагается под окном просмотра программного кода. Запустите макрос ИмпортСчета в режиме пошагового выполнения и остановитесь перед строкой Range("A14").Select. Текущее значение вы ражения Selection.Address будет равно $A$10 (рис. 2.35).

Рис. 2.34. Добавление в окно просмотра теку Рис. 2.35. Окно Watches позволяет от щего адреса выделенного диапазона ячеек слеживать текущее значение выраже Нажмите клавишу, чтобы выполнить строку Range("A14").Select.

В окне Watches будет отображен новый адрес выделенного диапазона ячеек $A$14 (рис. 2.36).

Рис. 2.36. Содержимое окна Watches обновляется после вы Установка точки прерывания с помощью окна Watches Щелкните правой кнопкой мыши на значке с изображением очков в окне Watches (Просмотр) и выберите команду контекстного меню Edit Watch (Изменить параметры просмотра). Установите переключатель Break When Value Changes (Приостановить при изменении значения) в группе переклю чателей Watch Type (Способ просмотра) диалогового окна Edit Watch (Изменить параметры просмотра) (рис. 2.37). Щелкните на кнопке OK.

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

Отслеживание состояния объекта с помощью окна Watches Ранее было рассмотрено отслеживание значения свойства Selection.

Address. Редактор Visual Basic позволяет также следить за состоянием целых объектов, таких как объект Selection (рис. 2.38).

84 Часть I Первые шаги Щелкните на значке с изображением знака ‘‘плюс’’, чтобы просмотреть все свойства объекта Selection (рис. 2.39). Существование некоторых из них окажется для вас настоящим сюрпризом. Кроме новых свойств наподобие.AddIndent (значение False) и.AllowEdit (значение True), вы увидите также уже знакомые свойства, такие как.Formula.

Рис. 2.39. Щелкните на значке с изображением знака “плюс”, чтобы просмотреть список свойств объекта и их текущих значений Возле некоторых свойств объекта Selection, таких как коллекция Borders, находится значок с изображением знака ‘‘плюс’’. Щелкните на нем, чтобы получить более детальную информацию об объекте.

Диспетчер объектов Чтобы открыть окно диспетчера объектов редактора Visual Basic, нажмите клавишу (рис. 2.40).

Рис. 2.40. Чтобы открыть окно диспетчера объектов, нажмите клавишу Диспетчер объектов позволяет просматривать библиотеку объектов Excel и проводить поиск в ней. Распечатка списка всех объектов из этой библиотеки занимает порядка 409 страниц текста, однако благодаря диспетчеру объектов работать с библиотекой совсем нетрудно.

Окно диспетчера объектов занимает пространство окна просмотра про граммного кода. С помощью верхнего раскрывающегося списка можно вы брать все подключенные библиотеки (All Libraries (Все библиотеки)), библио теку Excel, Office, VBA, библиотеку каждой открытой рабочей книги, а также все остальные библиотеки, указанные с помощью диалогового окна References (Ссылки) (чтобы открыть диалоговое окно References, выберите команду меню редактора Visual Basic Tools References (Сервис Ссылки)).

Раскройте список и выберите библиотеку Excel.

В левой части окна диспетчера объектов содержится список классов биб лиотеки Excel. Щелкните на имени класса Application. В правой части ок на диспетчера объектов появится список свойств и методов объекта Application (рис. 2.41).

86 Часть I Первые шаги Рис. 2.41. Выберите класс, а затем — метод или свойство. В нижней части окна диспетчера объектов появится краткое описание выбранного элемента. Рядом с именем метода в правой части окна диспетчера объектов находится значок с изображением зеленой книги, а рядом с именем свойства — изображение учетной карточки с указывающей на нее кистью руки Щелкните на имени свойства ActiveCell. В нижней части окна диспетчера объектов появится краткое описание свойства ActiveCell, из которого можно узнать тип возвращаемого этим свойством значения Range. Кроме того, свойство ActiveCell предназначено только для чтения, что делает невозмож ным присвоение ему значения с целью сдвинуть указатель активной ячейки.

Щелкните на ссылке Range в нижней части окна диспетчера объектов, чтобы увидеть список свойств и методов объекта Range, а значит и свойства ActiveCell. Щелкните на имени любого свойства или метода объекта Range, а затем на кнопке с изображением желтого вопросительного знака в верхней части диспетчера объектов. В результате откроется окно справочной системы с разделом, посвященным выбранному элементу.

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

Чтобы закрыть окно диспетчера объектов и вернуться к окну просмотра программного кода, щелкните на кнопке с изображением крестика в верхнем правом углу окна диспетчера объектов (рис. 2.42).

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

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

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

Автоматически сгенерированный код:

Rows("1:1").Select Selection.Font.Bold = True Оптимизированный код:

Rows("1:1").Font.Bold = True Подобное преобразование имеет несколько преимуществ. Во первых, ко личество строк кода уменьшается почти что вдвое. Во вторых, код выполняет ся быстрее.

Чтобы оптимизировать приведенный выше фрагмент кода, выделите фрагмент Select в верхней строке кода и фрагмент Selection. — в ниж ней, после чего щелкните на кнопке (рис. 2.43 и 2.44).

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

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

К примеру, на рис. 2.45 последовательное нажатие клавиш и при ведет к перемещению в ячейку A6, а не в ячейку A10.

(выражение End(xlDown) в VBA) срабатывает некорректно при Одним из возможных решений этой проблемы является перемещение в конец рабочего листа Excel и последовательное нажатие клавиш и.

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

Range("A65536").End(xlUp) Внимание Начиная с Excel 97 максимальное количество строк в Excel равно 65 536 (ранее оно равнялось 16 384). Чтобы обеспечить совместимость кода макроса с любой вер сией Excel, жестко закодированное значение 65 535 рекомендуется заменить вы ражением Rows.Count (максимальное число строк в текущей версии Excel).

Строка Cells(Row.Count, 1).End(xlUp) гарантирует правильность работы макроса как в будущих, так и в предыдущих версиях Excel.

Совет 3: используйте переменные Средство записи макросов никогда не создает переменные. О переменных речь пойдет далее в этой книге, а пока что можно отметить, что, как и в BASIC, переменные используются для хранения значений.

Создадим переменную для хранения номера последней строки данных. Пере менным рекомендуется давать информативные имена, например, FinalRow.

FinalRow = Range("A65536").End(xlUp).Row Зная номер последней строки данных, разместить в столбце A следующей строки слово ‘‘Всего’’ можно с помощью такого кода:

Range("A" & FinalRow + 1).Value = "Всего" См. также Более простой способ обращения к этой ячейке рассматривается в разделе “Обращение к диапазону ячеек с помощью свойства Cells” главы 3 на с. 99.

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

Range("E" & FinalRow + 1).Formula = "=SUM(E2:E" & FinalRow & ")" Совет 4: используйте одно выражение для копирования и вставки данных Автоматически сгенерированный код ‘‘славится’’ своей четырехшаговой процедурой копирования и вставки данных, подразумевающей выделение ис ходного диапазона ячеек, его копирование, выделение целевого диапазона 90 Часть I Первые шаги ячеек и, наконец, вызов метода ActiveSheet.Paste. Метод Copy, приме няемый к диапазону ячеек, обладает намного более широкой функционально стью, позволяя задать источник и назначение копируемых данных с помощью одного выражения.

Ниже приведен фрагмент автоматически сгенерированного кода:

Range("E14").Select Selection.Copy Range("F14:G14").Select ActiveSheet.Paste Range("E14").Copy Destination:=Range("F14:G14") Совет 5: используйте конструкцию With...End With Ниже приведен автоматически сгенерированный код, изменяющий раз личные параметры шрифта выделенного диапазона ячеек:

Range("A14:G14").Select Selection.Font.Bold = True Selection.Font.Size = Selection.Font.ColorIndex = Selection.Font.Underline = xlUnderlineStyleDoubleAccounting При выполнении этого кода макрос должен 4 раза подряд вычислить зна чение выражения Selection.Font. Поскольку каждый раз обращение про исходит к одному и тому же объекту, его имя рекомендуется указать в начале блока With. Чтобы сослаться на объект внутри блока With, соответствующие строки кода необходимо предварить символом точки, как показано ниже:

With Range("A14:G14").Font.Underline = xlUnderlineStyleDoubleAccounting Исправление и оптимизация автоматически сгенерированного кода Изменение автоматически сгенерированного кода Используя приведенные выше советы, превратим автоматически сгенерированный код макроса ИмпортСчета (см. ниже) в эффективный и профессиональный код.

Sub ИмпортСчета() ' ИмпортСчета Макрос ' Макрос записан 03.01.2005 (Александр Журавлев) ' Сочетание клавиш: Ctrl+и Workbooks.OpenText Filename:= _ "C:\Счет.txt", Origin:=1251, StartRow:=1, _ DataType:=xlDelimited, TextQualifier:= xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _ False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _ TrailingMinusNumbers:=True Selection.End(xlDown).Select Range("A14").Select ActiveCell.FormulaR1C1 = "Всего" Range("E14").Select Selection.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)" Selection.AutoFill Destination:=Range("E14:G14"), _ Type:=xlFillDefault Range("E14:G14").Select Rows("1:1").Select Selection.Font.Bold = True Rows("14:14").Select Selection.Font.Bold = True Cells.Select Selection.Columns.AutoFit End Sub Чтобы исправить и оптимизировать код макроса, выполните следующие действия.

1. Оставьте метод Workbook.OpenText без изменений.

2. В следующей строке кода осуществляется попытка перейти на последнюю стро ку с данными:

Selection.End(xlDown).Select Ничего не выделяйте. Кроме того, создайте две переменные — для номера по следней строки с данными и для номера итоговой строки. Чтобы избежать про блемы пустой ячейки, переместитесь на последнюю строку с данными с конца рабочего листа:

' Найти последнюю строку с данными FinalRow = Range("A65536").End(xlUp).Row TotalRow = FinalRow + 3. Следующие строки кода соответствуют вводу слова “Всего” в столбец A итого вой строки:

Range("A14").Select ActiveCell.FormulaR1C1 = "Всего" Воспользуйтесь созданной ранее переменной TotalRow и откажитесь от выде ления ячейки, как показано ниже:

' Создание итоговой строки Range("A" & TotalRow).Value = "Всего" 4. Приведенные ниже строки кода описывают ввод формулы суммы в столбец E и ее копирование в столбцы F и G:

Range("E14").Select Selection.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)" Selection.AutoFill Destination:=Range("E14:G14"), _ 92 Часть I Первые шаги Type:=xlFillDefault Range("E14:G14").Select Вы уже наверное догадались, что выделять здесь абсолютно нечего. Приведен ный ниже код помещает формулу суммы в требуемые ячейки итоговой строки (формат ссылок R1C1 рассматривается в главе 6, “Стиль записи ссылок R1C1”):

Range("E" & TotalRow).Resize(1, 3).FormulaR1C1 = _ "=SUM(R2C:R[-1]C)" 5. Ниже приведен код, сгенерированный средством записи макросов при фор матировании строки заголовков столбцов и итоговой строки:

Rows("1:1").Select Selection.Font.Bold = True Rows("14:14").Select Selection.Font.Bold = True А вот и его оптимизированная версия:

Rows("1:1").Font.Bold = True Rows(TotalRow & ":" & TotalRow).Font.Bold = True 6. Перед вызовом метода AutoFit средство записи макросов выделяет все ячей Selection.Columns.AutoFit Как вы уже догадались, это совершенно излишне:

Cells.Columns.AutoFit 7. Ниже приведен комментарий, добавляемый к каждому макросу при его создании:

' Макрос записан 03.01.2005 (Александр Журавлев) ' Сочетание клавиш: Ctrl+и Исправив и оптимизировав автоматически сгенерированный код, вы имеете полное право заменить слово “записан” на “создан”, как показано ниже:

' Макрос создан 03.01.2005 (Александр Журавлев) ' Сочетание клавиш: Ctrl+и Ниже приведен полный код исправленного и оптимизированного макроса.

Sub ИмпортСчетаИсправленный () ' ИмпортСчета Макрос ' Макрос создан 03.01.2005 (Александр Журавлев) ' Сочетание клавиш: Ctrl+и Workbooks.OpenText Filename:= _ "C:\Счет.txt", Origin:=1251, StartRow:=1, _ DataType:=xlDelimited, TextQualifier:= xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _ False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _ TrailingMinusNumbers:=True ' Найти последнюю строку с данными FinalRow = Range("A65536").End(xlUp).Row TotalRow = FinalRow + ' Создание итоговой строки Range("A" & TotalRow).Value = "Всего" Range("E" & TotalRow).Resize(1, 3).FormulaR1C1 = _ "=SUM(R2C:R[-1]C)" Rows("1:1").Font.Bold = True Rows(TotalRow & ":" & TotalRow).Font.Bold = True Cells.Columns.AutoFit End Sub Следующий шаг В этой главе были рассмотрены основы синтаксиса языка программирова ния Visual Basic for Applications, использование справочной системы, средств отладки, а также несколько советов по исправлению и оптимизации автома тически сгенерированного кода.

Следующая глава посвящена более детальному изучению диапазонов ячеек.

Диапазон ячеек представляет со бой любое их объединение в пределах одного рабочего листа. Примерами диапазона ячеек являются ячейка, строка, столбец и т.п. Объект Range ных объектов Excel VBA.

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

Рассмотрим следующую иерархию объектов Excel:

Application Workbook Worksheet свойства Resize

Range Объект Range является свойством с помощью свойств Columns и объекта Worksheet. Обе приведен ные ниже строки кода выполняют одно и то же действие, если рабочий лист Worksheets(1) является ак Worksheets(1).Range("A1") Таким образом, существует не Проверка пустых ячеек с сколько способов обращения к диа пазону ячеек. Range("A1") является наиболее распространенным из них в основном за счет того, что это стан дартный способ обращения к диапа зону ячеек, использующийся средст коллекции Areas

96 Часть I Первые шаги вом записи макросов. Приведенные ниже строки кода полностью эквива Range("D5") Range("B3").Range("C3") Cells(5, 4) Range("A1").Offset(4, 3) Range("МойДиапазон") 'при условии что МойДиапазон - имя ячейки D Более подробно различные способы обращения к диапазону ячеек рас сматриваются далее в этой главе.

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

Range("A1:B5").Select Согласно второму синтаксису обращение к диапазону ячеек осуществляет ся путем указания адреса его верхнего левого и нижнего правого угла, как по казано ниже:

Range("A1", "B5").Select Вместо адреса любого из углов можно подставить имя диапазона ячеек, функцию Cells, а также свойство ActiveCell. В следующей строке кода осуществляется выделение прямоугольного диапазона ячеек, в верхнем левом углу которого находится ячейка A1, а в нижнем правом углу активная ячейка:

Range("A1", ActiveCell).Select А вот как выделить диапазон ячеек, в верхнем левом углу которого нахо дится активная ячейка, а в нижнем правом углу ячейка, находящаяся на 5 строк ниже и на 2 столбца правее активной ячейки:

Range(ActiveCell, ActiveCell.Offset(5, 2)).Select Сокращенная форма обращения к диапазону ячеек Сокращенная форма обращения к диапазону ячеек предполагает использо вание квадратных скобок ([]), как показано в табл. 3.1.

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

Ниже приведен пример обращения к именованному диапазону ячеек МойДиапазон на рабочем листе Лист1:

Worksheets("Лист1").Range("МойДиапазон").Select Обратите внимание, что имя диапазона ячеек взято в кавычки. Это отличи тельная особенность использования именованных диапазонов в VBA. Без ка вычек Excel воспримет имя диапазона ячеек как объявленную в макросе пере менную. Единственное исключение касается сокращенной формы обращения к диапазону ячеек, которая не предусматривает заключение имени диапазона в кавычки.

Таблица 3.1. Сокращенная форма обращения к диапазону ячеек Range("A1:D5", "G6:I17") [A1:D5, G6:I17] Обращение к диапазону ячеек, расположенному на другом рабочем листе Переключение между рабочими листами может существенно замедлить выполнение кода макроса. Чтобы избежать этого, можно обратиться непо средственно к объекту Worksheet, как показано ниже:

Worksheets("Лист1").Range("A1") В приведенном выше коде происходит обращение к рабочему листу Лист1, даже если активным рабочим листом на данный момент является лист Лист2.

Чтобы обратиться к диапазону ячеек в другой рабочей книге, воспользуй тесь объектами Workbook, Worksheet и Range, как показано ниже:

Workbooks("Счета.xls").Worksheets("Лист1").Range("A1") Будьте внимательны, используя свойство Range в качестве аргумента дру гого свойства Range. В подобных случаях необходима полная идентификация диапазона ячеек. Предположим, что активным рабочим листом является лист Лист1, а суммирование данных производится на листе Лист2 так, как пока зано ниже:

WorksheetFunction.Sum(Worksheets("Лист2").Range(Range("A1"), _ Range("A7"))) Приведенная выше строка кода не будет выполняться, поскольку Excel не распространит ссылку на объект Worksheet на вложенные объекты Range.

Чтобы исправить ситуацию, можно поступить так:

WorksheetFunction.Sum(Worksheets("Лист2").Range(Worksheets( _ "Лист2").Range("A1"), Worksheets("Лист2").Range("A7"))) Однако еще лучше упростить эту достаточно длинную строку кода с помощью конструкции With...End, позволяющей заменить выражения 98 Часть I Первые шаги Worksheets("Лист2").Range более короткой формой.Range, как по казано ниже:

With Worksheets("Лист2") WorksheetFunction.Sum(.Range(.Range("A1"),.Range("A7"))) Обращение к диапазону ячеек с помощью указания его относительного адреса Обычно объект Range выступает в качестве свойства рабочего листа. Вме сте с тем, он может быть свойством другого объекта Range, внося неразбериху в и без того непростой программный код. Рассмотрим пример:

Range("B5").Range("C3").Select В результате выполнения приведенного выше кода выделяется ячейка D7.

Чтобы понять, почему так происходит, рассмотрим ячейку C3. Ячейка C3 рас положена на две строки ниже и на два столбца правее ячейки A1. Однако в указанном выше коде точкой отсчета является ячейка B5. Другими словами, VBA выделит ячейку, которая находится на том же смещении относительно ячейки B5, что и ячейка C3 относительно ячейки A1 (на две строки ниже и на два столбца правее), а именно D7.

Подобный стиль записи программного кода является весьма неинтуитив ным. На первый взгляд указанные в строке кода адреса ячеек не имеют ни ма лейшего отношения к адресу выделяемой ячейки!

Тем не менее, данный синтаксис может пригодиться при обращении к ячейке, расположенной на определенном смещении относительно активной ячейки. К примеру, в результате выполнения приведенной ниже строки кода выделяется ячейка, расположенная на 3 строки ниже и на 4 столбца правее те кущей активной ячейки:

Selection.Range("E4").Select Аналогичного результата (с применением куда более понятного синтакси са) можно добиться путем использования свойства Offset, которое рассмат ривается далее в этой главе.

Зачем же нужно знать о существовании такого неудобного способа обра щения к диапазону ячеек? Дело в том, что именно он пришелся ‘‘по душе’’ средству записи макросов. Ниже приведена одна из строк кода, сгенерирован ных при записи макроса импорта счета с использованием относительных ссы лок (см. главу 1, ‘‘Excel и VBA гремучая смесь’’):

ActiveCell.Offset(0, 4).Range("A1").Select Выполнение этого кода приведет к выделению ячейки, соответствующей ячейке A1 с учетом смещения относительно активной ячейки на 4 столбца Обращение к диапазону ячеек с помощью свойства Cells Свойство Cells используется для обращения ко всем ячейкам объекта Range, будь то целый рабочий лист или определенный диапазон ячеек.

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

Cells.Select Использование свойства Cells вместе с объектом Range выглядит избы точным:

Range("A1:D5").Cells Что делает объект Cells действительно полезным, так это его свойство Item, которое позволяет обратиться к любой ячейке диапазона.

Ниже приведен синтаксис использования свойства Item с объектом Cells:

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

Cells.Item(5, "C") Cells.Item(5, 3) Поскольку свойство Item является свойством по умолчанию объекта Range, справедлива следующая сокращенная запись:

Cells(5, "C") Cells(5, 3) Возможность использования числовых значений при указании параметров будет по достоинству оценена при создании циклов. Для выделения ячейки средство записи макросов применяет выражение наподобие Range("A1").Select, а для выделения диапазона ячеек Range("A1:C5").Select.

Следующие строки выдержаны в стиле автоматически сгенерированного кода:

FinalRow = Range("A65536").End(xlUp).Row For i = 1 To FinalRow Range("A" & i & ":E" & i).Font.Bold = True Next i В результате использования ‘‘недружелюбного’’ синтаксиса цикл, выде ляющий ячейки в столбцах A–E с помощью утолщения шрифта, оказался весьма сложным для восприятия. Попробуем записать его несколько иначе:

FinalRow = Cells(65536, 1).End(xlUp).Row For i = 1 To FinalRow Cells(i, "A").Resize(, 5).Font.Bold = True Next i Использование свойств Cells и Resize вместо адреса диапазона ячеек делает код цикла более наглядным.

100 Часть I Первые шаги Использование свойства Cells в качестве параметра свойства Range Свойство Cells можно использовать в качестве параметра свойства Range.

Приведенная ниже строка кода описывает диапазон ячеек A1:E5:

Range(Cells(1, 1), Cells(5, 5)) Применение подобного подхода оправдано в случае необходимости ис пользования переменных, как в предыдущем примере кода цикла.

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

Ниже приведен синтаксис использования свойства Offset:

Range.Offset(СмещениеПоСтрокам, СмещениеПоСтолбцам ) Чтобы обратиться к ячейке F5 при условии, что текущей активной ячейкой является ячейка A1, используйте выражение Range("A1").Offset(RowOffset:=4, ColumnOffset:=5) или его сокращенную форму Range("A1").Offset(4, 5) Отсчет адресов ячеек ведется с адреса ячейки A1. Сама ячейка A1 при этом не учитывается.

Одна из замечательных особенностей свойства Offset заключается в от сутствии необходимости указывать оба параметра одновременно. Чтобы обра титься к ячейке, расположенной на один столбец правее ячейки A1, исполь зуйте любое из следующих выражений:

Range("A1").Offset(ColumnOffset:=1) Range("A1").Offset(, 1) А вот как обратиться к ячейке, расположенной на одну строку выше ячейки B2:

Range("B2").Offset(RowOffset:=-1) Range("B2").Offset(-1) Рассмотрим таблицу с двумя столбцами, в одном из которых перечислены про дукты питания, а в другом их запасы. Чтобы найти продукт, запасы которого подошли к концу, и отметить это путем размещения в следующей ячейке слова ‘‘ПОПОЛНИТЬ’’, можно воспользоваться следующим макросом:

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _ LookIn:=xlValues) Rng.Offset(, 1).Value = "ПОПОЛНИТЬ" Результат выполнения макроса показан на рис. 3.1.

Свойство Offset позволяет смещать не только отдельные ячейки, но даже целые диапазоны. Приведенная ниже строка кода смещает диапазон ячеек A1:C3 на одну строку вниз и на один столбец правее так, что он переходит в диапазон ячеек B2:D4 (рис. 3.2).

Range("A1:C3").Offset(1, 1) Рис. 3.1. Результат выполнения мак Рис. 3.2. Сдвиг диапазона ячеек с помо роса, находящего продукты с ис щью команды Range("A1:C3").Offset текшими запасами Изменение размера диапазона ячеек с помощью свойства Resize Свойство Resize позволяет изменять размер диапазона ячеек, используя в качестве отправной точки текущую активную ячейку.

Ниже приведен синтаксис использования свойства Resize:

Range.Resize(КоличествоСтрок, КоличествоСтолбцов ) Чтобы создать диапазон ячеек B3:D13, используйте выражение Range("B3").Resize(RowSize:=11, ColumnSize:=3) или его сокращенную форму Range("B3").Resize(11, 3) Как и свойство Offset, свойство Resize не требует указания обоих пара метров одновременно. Чтобы увеличить размер диапазона ячеек до двух столбцов, используйте любое из следующих выражений:

Range("B3").Resize(ColumnSize:=2) Range("B3").Resize(, 2) А вот как увеличить размер диапазона ячеек до двух строк:

Range("B3").Resize(RowSize:=2) Range("B3").Resize(2) 102 Часть I Первые шаги Возвратимся к таблице с продуктами питания. Чтобы найти продукт, запа сы которого заканчиваются, и отметить это путем выделения цветом ячеек с названием продукта и его запасами, воспользуйтесь следующим макросом (рис. 3.3):

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _ LookIn:=xlValues) Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = Здесь свойство Offset используется для изменения активной ячейки, а свойство Resize — для увеличения размера диапазона до двух столбцов.

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

Range("Продукты").Resize(, 2) Помните, что параметры свойства Resize обозначают размер целевого диапазона ячеек, который необходимо создать.

Обращение к диапазону ячеек с помощью свойств Columns и Rows Свойства Columns и Rows используются для обращения к столбцам и строкам диапазона ячеек и возвращают соответствующий объект Range.

Ранее мы рассматривали следующую строку кода макроса:

FinalRow = Range("A65536").End(xlUp).Row В результате ее выполнения переменной FinalRow присваивается номер последней строки (объект Range), столбец A которой содержит какие либо данные. Зная номер последней строки с данными, можно создать цикл, по очередно обрабатывающий все значащие строки рабочего листа.

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

Range("A1:B9, C10:D19").Rows.Count Если же не группировать несмежные диапазоны ячеек (как показано ниже), то ре зультат подсчета количества строк будет равен 19:

Range("A1:B9", "C10:D19").Rows.Count Объединение диапазонов ячеек с помощью метода Union Метод Union позволяет объединить два или более несоприкасающихся диапазона ячеек. Он возвращает временный объект, предназначенный для манипулирования объединенным диапазоном:

Application.Union(аргумент1,аргумент2,...) В результате выполнения приведенного ниже кода два именованных диа пазона ячеек будут объединены, заполнены случайными числовыми значе ниями и выделены путем утолщения шрифта:

Set UnionRange = Union(Range("Диапазон1"), Range("Диапазон2")) With UnionRange ' В англоязычной версии Excel:

'.Formula = "=RAND()".FormulaLocal = "=СЛЧИС()".Font.Bold = True End With Создание нового диапазона ячеек из пересекающихся диапазонов с помощью метода Intersect Метод Intersect возвращает диапазон ячеек, полученный в результате пересечения нескольких диапазонов:

Application.Intersect(аргумент1,аргумент2,...) В результате выполнения приведенного ниже кода будет создан новый диапа зон ячеек, полученный в результате пересечения двух существующих диапазонов.

Ячейки нового диапазона выделены цветом, как показано на рис. 3.4.

Set IntersectRange = Intersect(Range("Диапазон1"), _ Range("Диапазон2")) IntersectRange.Interior.ColorIndex = 104 Часть I Первые шаги Рис. 3.4. Метод Intersect возвращает диапазон ячеек, по лученный в результате пересечения нескольких диапазонов Проверка пустых ячеек с помощью функции IsEmpty Функция IsEmpty возвращает булево значение, определяющее, является ячейка пустой (True) или нет (False). Ячейка является пустой, если она не содержит каких либо данных (даже символов пробела).

IsEmpty(Ячейка) На рис. 3.5 показана таблица с несколькими группами данных, разделен ными пустой строкой.

С помощью следующего кода проведем поиск пустых строк (точнее, пус тых ячеек в столбце A) и выделим цветом их первые 4 ячейки (рис. 3.6):

LastRow = Range("A65536").End(xlUp).Row If IsEmpty(Cells(i, 1)) Then Cells(i, 1).Resize(1, 4).Interior.ColorIndex = Next i Обращение к диапазону ячеек с помощью свойства CurrentRegion Свойство CurrentRegion возвращает объект, представляющий непре рывный диапазон ячеек. С помощью этого свойства можно обратиться к диа пазону ячеек, ограниченному по крайней мере одной пустой строкой или од ним пустым столбцом:

ДиапазонЯчеек.CurrentRegion В результате выполнения приведенной ниже строки кода будет выделен диапазон ячеек A1:D3 — непрерывный диапазон ячеек, включающий в себя ячейку A1 (рис. 3.7):

Range("A1").CurrentRegion.Select Рис. 3.7. Используйте свойство CurrentRegion для обращения к непрерывному диапа 106 Часть I Первые шаги Свойство CurrentRegion рекомендуется использовать для обращения к таблицам, размер которых постоянно меняется.

Выделение ячеек, соответствующих определенному критерию, с помощью метода SpecialCells Далеко не все пользователи Excel знают о существовании диалогового окна Выделение группы ячеек (Go To Special). Нажмите клавишу, чтобы открыть диалоговое окно Переход (Go To) (рис. 3.8).

Рис. 3.8. Чтобы открыть диалоговое окно Выделение группы ячеек, щелкните на кноп Щелкните на кнопке Выделить (Special) в левом нижнем углу диалогового окна Переход, чтобы открыть диалоговое окно Выделение группы ячеек (рис. 3.9).

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

Возможность выделения только видимых ячеек очень полезна при автоматиче ской фильтрации данных.

Возможности диалогового окна Выделение группы ячеек могут быть реализованы с помощью метода VBA SpecialCells. Этот метод позволяет работать с ячейка ми, соответствующими определенному критерию:

ДиапазонЯчеек.SpecialCells(Тип, Значение) Метод SpecialCells имеет два параметра: Тип и Значение (необязательный параметр). Тип ячейки может быть описан одной из констант xlCellType:

xlCellTypeAllFormatConditions xlCellTypeAllValidation xlCellTypeBlanks xlCellTypeComments xlCellTypeConstants xlCellTypeFormulas xlCellTypeLastCell xlCellTypeSameFormatConditions xlCellTypeSameValidation xlCellTypeVisible Рис. 3.9. Диалоговое окно Выделение группы ячеек предлагает широкие возможности по выделению ячеек Предусмотрено также 4 различных значения ячейки:

xlErrors xlLogical xlNumbers xlTextValues В результате выполнения приведенного ниже кода вокруг всех непрерывных диа пазонов ячеек с условным форматированием будет создана граница. При отсутст вии таких диапазонов будет выдано сообщение об ошибке:

Set rngCond = ActiveSheet.Cells.SpecialCells( _ xlCellTypeAllFormatConditions) If Not rngCond Is Nothing Then rngCond.BorderAround xlContinuous End If В таблице, показанной на рис. 3.10, отсутствуют данные в некоторых ячейках.

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

К счастью, метод SpecialCells позволяет выделить все пустые ячейки в диапа зоне и заполнить их нужными данными:

Sub FillIn() Range("A1").CurrentRegion.SpecialCells( _ xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" 108 Часть I Первые шаги Range("A1").CurrentRegion.Value = _ Range("A1").CurrentRegion.Value В приведенном выше коде выражение Range("A1").CurrentRegion соответст вует непрерывному диапазону ячеек рабочего листа. Свойство SpecialCells возвращает только пустые ячейки этого диапазона. Формула в стиле R1C1 (см. гла ву 6, “Стиль записи ссылок R1C1”) заполняет каждую пустую ячейку данными из ячейки, расположенной на одну строку выше. Вторая строка кода представляет собой быстрый способ выполнения команд Копирование (Copy) и Специальная вставка (Paste Special). Результат выполнения кода показан на рис. 3.11.

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

Рассмотрим задачу копирования данных о запасах продуктов (ячейки, вы деленные серым цветом) в другую часть рабочего листа (рис. 3.12).

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

Set NewDestination = ActiveSheet.Range("I1") For Each Rng In Cells.SpecialCells(xlCellTypeConstants, 1).Areas Rng.Copy Destination:=NewDestination Set NewDestination = NewDestination.Offset(Rng.Rows.Count) Next Rng Рис. 3.13. Коллекция Areas предоставляет возможность эффективного мани пулирования диапазонами несмежных ячеек Следующий шаг В следующей главе рассматриваются функции, определенные пользовате лем, а также наиболее распространенные задачи программирования в Excel.

Глава Создание функций, Иногда огромного количества встроенных функций Excel бывает недостаточно. В частности, Excel не содержит готового решения для зада чи суммирования значений в ячейках, выделенных определенным цветом.

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

VBA позволяет создавать функ ции, которые могут использоваться аналогично встроенным функциям Excel, таким как СУММ (SUM). Чтобы применить подобную функцию, не обходимо знать только ее имя и ар гументы.

112 Часть I Первые шаги Функции, определенные пользователем, должны храниться в стандартных моду лях. Модули рабочих листов и модуль ЭтаКнига (ThisWorkbook) являются специ альными модулями. Функция, размещенная в одном из таких модулей, не будет воспринята Excel как функция, определенная пользователем.

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

Add(Number1, Number2) Здесь Number1 — это первое слагаемое, а Number2 — второе:

Function Add(Number1, Number2) As Integer Add = Number1 + Number Попытаемся разобраться в приведенном выше коде:

аргументы функции Add — Number1 и Number2 — перечислены в скобках по возвращаемый функцией Add результат является целым числом (As Integer) и вычисляется по формуле Add = Number1 + Number2.

Чтобы применить функцию Add на рабочем листе, выполните следующие действия.

1. Введите любые два числа в ячейки A1 и A2.

2. Выделите ячейку A3.

3. Нажмите комбинацию клавиш или выберите команду меню Excel Вставка Функция (Insert Function), чтобы открыть диалоговое окно мастера 4. В раскрывающемся списке Категория (Or select a category) выберите значение Определенные пользователем (User Defined).

5. Выберите функцию Add и щелкните на кнопке OK.

6. В качестве первого аргумента укажите ячейку A1.

7. В качестве второго аргумента укажите ячейку A2.

8. Щелкните на кнопке OK.

Поздравляем! Вы только что создали собственную функцию и применили ее на Большинство функций, используемых на рабочих листах, могут с успехом применяться в VBA, и наоборот. Тем не менее, VBA требует, чтобы функция, определенная пользователем (Add), вызывалась из процедуры (Addition), как показано ниже:

Sub Addition () Dim Total as Integer Total = Add (1, 10) 'вызов функции, определенной пользователем MsgBox "Ответ: " & Total End Sub Наиболее распространенные задачи программирования в Excel В следующих разделах этой главы рассматриваются решения наиболее распространенных задач, встречающихся при повседневном программи ровании в Excel.

Вывод имени файла текущей рабочей книги в ячейке Предназначение следующей функции заключается в выводе имени файла активной рабочей книги в ячейке, как показано на рис. 4.1:

MyName() Рис. 4.1. Функции MyName и MyFullName используются для вывода в ячейке имени и полного имени файла активной рабочей книги, Функция MyName не имеет аргументов.

Function MyName() As String MyName = ThisWorkbook.Name End Function Вывод полного имени файла текущей рабочей книги в ячейке Предназначение следующей функции заключается в выводе имени файла активной рабочей книги в ячейке (см. рис. 4.1):

MyFullName() Функция MyFullName не имеет аргументов.

Function MyFullName() As String MyFullName = ThisWorkbook.FullName End Function 114 Часть I Первые шаги Как проверить, открыта ли рабочая книга Иногда требуется проверить, открыта ли определенная рабочая книга.

Следующая функция возвращает значение True, если рабочая книга открыта, и False в противном случае:

BookOpen(Bk) Функция BookOpen имеет один аргумент:

Function BookOpen(Bk As String) As Boolean 'Удалить информацию об ошибках.

'Если при выполнении кода возникнет ошибка, она будет пропущена.

Set T = Application.Workbooks(Bk) 'Если рабочая книга открыта, переменная T будет содержать 'объект рабочей книги и, таким образом, не будет пустой.

Ниже приведен пример использования функции BookOpen:

Sub OpenAWorkbook() BookName = "Chapter 4 samples.xls" 'Вызов функции BookOpen - не забудьте указать значение параметра.

IsOpen = BookOpen(BookName) Проверка существования рабочего листа в открытой книге Следующая функция возвращает значение True, если указанный рабочий лист существует, и False — в противном случае. Подобная проверка возмож на только при условии, что соответствующая рабочая книга открыта.

SheetExists(SName, WBName) Функция SheetExists имеет 2 аргумента:

WBName — имя рабочей книги (необязательный параметр).

Function SheetExists(SName As String, Optional WBName As _ String) As Boolean On Error Resume Next 'Проверить, задано ли имя файла рабочей книги.

If Len(WBName) > 0 Then Set WB = Workbooks(WBName) 'Завершить выполнение, если рабочая книга не открыта.

If WB Is Nothing Then Exit Function Set WB = ActiveWorkbook Set WS = WB.Sheets(SName) 'Если рабочий лист существует, переменная WS хранит 'соответствующий объект. Если рабочий лист отсутствует, 'переменная WS хранит значение Nothing.

'Если переменная WS НЕ хранит Nothing, значение 'выражения Not (WS Is Nothing) будет равно True.

SheetExists = Not (WS Is Nothing) Ниже приведен пример использования функции SheetExists:

Sub CheckForSheet() Dim ShtExists As Boolean ShtExists = SheetExists("Sheet9") 'Обратите внимание, что функции был передан только один параметр.

If ShtExists Then MsgBox "Рабочий лист существует!" MsgBox "Рабочий лист НЕ существует!" End Sub Подсчет количества файлов рабочих книг в папке Следующая функция просматривает папку (и при необходимости ее подпапки) и, в зависимости от переданных параметров, подсчитывает ли бо общее количество хранящихся в ней файлов рабочих книг Excel, либо количество файлов рабочих книг Excel, имена которых включают в себя заданную строку.

NumFilesInCurDir(LikeText, Subfolders) Функция NumFilesInCurDir имеет 2 аргумента:

LikeText — строка, которую должно включать в себя имя файла рабо чей книги (необязательный параметр);

Subfolders — булево значение, определяющее необходимость прове дения поиска в подпапках; по умолчанию поиск в подпапках не прово дится (False) (необязательный параметр).

Function NumFilesInCurDir(Optional LikeText As String, _ Optional Subfolders As Boolean = False) With Application.FileSearch 'Строка, которую должно включать в себя имя файла рабочей книги.

If Len(LikeText) > 0 Then 116 Часть I Первые шаги 'Выбрать тип файла - рабочие книги Excel.

.FileType = msoFileTypeExcelWorkbooks 'Указать на необходимость проведения поиска в текущей папке.

'Указать на необходимость проведения поиска в подпапках.

.SearchSubFolders = Subfolders NumFilesInCurDir =.FoundFiles.Count Ниже приведен пример использования функции NumFilesInCurDir:

Sub CountMyWkbks() MyFiles = NumFilesInCurDir("Глава*", True) MsgBox MyFiles & " файл(ов) найден(о)" Получение имени пользователя, зарегистрировавшегося в системе Следующая функция возвращает имя пользователя, зарегистрировавшего ся в системе. Вместе с функцией, возвращающей постоянное значение даты и времени (рассматривается далее в этой главе), она может быть применена для создания файла журнала. Кроме того, с ее помощью можно узнать имеющиеся у пользователя права на доступ к рабочей книге.

WinUsername() Функция WinUsername не имеет аргументов.

Функция WinUsername использует функции интерфейса прикладного програм мирования (API), который рассматривается в главе 22, “Интерфейс прикладного программирования (API) Windows”.

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

Private Declare Function WNetGetUser Lib "mpr.dll" Alias _ "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName _ As String, lpnLength As Long) As Long Private Const NO_ERROR = Private Const ERROR_NOT_CONNECTED = 2250& Private Const ERROR_MORE_DATA = Private Const ERROR_NO_NETWORK = 1222& Private Const ERROR_EXTENDED_ERROR = 1208& Private Const ERROR_NO_NET_OR_BAD_PATH = 1203& Текст функции WinUsername может быть помещен в любую часть модуля при условии, что он будет находиться ниже объявлений Private:

Function WinUsername() As String 'Переменные:

Dim strBuf As String, lngUser As Long, strUn As String 'Подготовка строковой переменной для использования в функции API.

strBuf = Space$(255) 'Использование функции WNetGetUser, возвращающей имя пользователя.

'Сохранение возвращенного функцией кода в переменной lngUser.

lngUser = WNetGetUser("", strBuf, 255) 'Если выполнение функции API прошло успешно, If lngUser = NO_ERROR Then 'убрать пробелы из переменной strBuf и возвратить результат 'выполнения функции WinUsername.

strUn = Left(strBuf, InStr(strBuf, vbNullChar) - 1) WinUsername = strUn 'Ошибка, завершение работы функции.

WinUsername = "Ошибка :" & lngUser End Function Ниже приведен пример использования функции WinUsername:

Sub CheckUserRights() Dim UserName As String UserName = WinUsername Select Case UserName Case "Administrator" MsgBox "Полные права" Case "Guest" MsgBox "Вы не можете вносить изменения в рабочую книгу" Case Else MsgBox "Ограниченные права" End Select End Sub Получение даты и времени последнего сохранения рабочей книги Следующая функция возвращает дату и время последнего сохранения ра бочей книги, как показано на рис. 4.2.

LastSaved(FullPath) Рис. 4.2. Функция LastSaved возвращает дату и время последнего сохранения рабочей книги Функция LastSaved имеет один аргумент:

FullPath — полный путь к файлу рабочей книги.

118 Часть I Первые шаги Function LastSaved(FullPath As String) As Date Получение постоянного значения даты и времени Поскольку значение, возвращаемое функцией Now, обновляется при каж дом открытии рабочей книги, его не рекомендуется использовать для указа ния даты и времени создания или изменения рабочей книги. Несмотря на то что следующая функция основана на функции Now, ее результат куда менее динамичен, так как он обновляется только при обновлении соответствующей ячейки (рис. 4.3).

DateTime() Рис. 4.3. Функция DateTime возвращает постоянное значение даты Функция DateTime не имеет аргументов.

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

Function DateTime() Проверка адреса электронной почты Следующая функция проверяет корректность написания адреса электрон ной почты (рис. 4.4).

IsEmailValid(StrEmail) Рис. 4.4. Проверка корректности написания адреса электронной почты Внимание Функция IsEmailValid проверяет только корректность написания адреса элек тронной почты, а не факт его существования.

Функция IsEmailValid имеет один аргумент:

StrEmail — адрес электронной почты.

Function IsEmailValid(strEmail As String) As Boolean Dim strArray As Variant Dim strItem As Variant Dim c As String Dim blnIsItValid As Boolean blnIsItValid = True 'Подсчет количества знаков @ в строке.

i = Len(strEmail) - Len(Application.Substitute(strEmail, _ "@", "")) 'Если знаков @ больше, чем 1, адрес электронной почты неверный.

If i 1 Then IsEmailValid = False: Exit Function ReDim strArray(1 To 2) 'Текст слева и справа от знака @ помещается в 2 разные переменные.

strArray(1) = Left(strEmail, InStr(1, strEmail, "@", 1) - 1) strArray(2) = Application.Substitute(Right(strEmail, _ Len(strEmail) - Len(strArray(1))), "@", "") For Each strItem In strArray 'Если хотя бы одна из переменных оказалась пустой, 'адрес электронной почты неверный.

If Len(strItem) 1 Then Exit Function arr1 = Application.Transpose(Rng) arr1 = UniqueValues(arr1) NoDupsArray = Application.Transpose(arr1) End Function Результат применения функции NoDupsArray на рабочем листе показан на рис. 4.8.

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

FirstNonZeroLength(Rng) Функция FirstNonZeroLength имеет один аргумент:

Function FirstNonZeroLength(Rng As Range) FirstNonZeroLength = 0# If Not IsNull(myCell) And myCell "" Then FirstNonZeroLength = myCell.Value End Function На рис. 4.9 показан пример использования функции FirstNonZeroLength на рабочем листе.

Рис. 4.9. Пример нахождения значения первой непустой ячейки в диапазоне с помощью функции FirstNonZeroLength Замена нескольких символов в строке Следующая функция используется для замены нескольких символов в строке (рис. 4.10):

MSubstitute(trStr, frStr, toStr) Рис. 4.10. Пример замены нескольких символов в строке с помощью Функция MSubstitute имеет 3 аргумента:

trStr — исходная строка;

frStr — символы строки, подлежащие замене;

toStr — символы заменители.

Внимание Функция MSubstitute предполагает, что длина строки toStr совпадает с дли ной строки frStr. Если длина строки toStr меньше длины строки frStr, не достающие символы считаются пустыми ("").Функция MSubstitute учитывает также регистр символов. Так, чтобы заменить все вхождения в строку буквы “А”, в строке frStr следует указать символы а и A. Замена одного символа двумя не поддерживается. Результатом выражения =MSubstitute("Тестовая строка"; "о"; "$@") будет Тест$вая стр$ка Ниже приведен текст функции MSubstitute:

Function MSubstitute(ByVal trStr As Variant, frStr As String, _ toStr As String) As Variant Dim iRow As Integer Dim iCol As Integer Dim j As Integer Dim Ar As Variant Dim vfr() As String Dim vto() As String ReDim vfr(1 To Len(frStr)) ReDim vto(1 To Len(frStr)) 'Помещение строк в массивы.

For j = 1 To Len(frStr) 'Сравнивание каждого символа и, при необходимости, его замена.

If IsArray(trStr) Then For iRow = LBound(Ar, 1) To UBound(Ar, 1) Ar(iRow, iCol), vfr(j), vto(j)) 130 Часть I Первые шаги Ar = Application.Substitute(Ar, vfr(j), vto(j)) End Function Извлечение чисел из смешанного текста Следующая функция извлекает числа из смешанного текста (текста, со держащего числа и буквы):

RetrieveNumbers(myString) Пример использования функции RetrieveNumbers на рабочем листе по казан на рис. 4.11.

Рис. 4.11. Пример извлечения чисел из смешанного текста с помощью функ Функция RetrieveNumbers имеет один аргумент:

myString — строка смешанного текста.

Function RetrieveNumbers(myString As String) 'Просмотр строки, начиная с ее конца (с шагом -1).

For i = Len(myString) To 1 Step - 'IsNumeric - это функция VBA, возвращающая True, 'если значение переменной является числом.

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

If IsNumeric(Mid(myString, i, 1)) Then If j = 1 Then OnlyNums = CInt(Mid(OnlyNums, 1, 1)) RetrieveNumbers = CLng(OnlyNums) End Function Преобразование номера недели в дату Следующая функция преобразовывает строку вида ‘‘Неделя НН ГГГГ’’ (где НН это номер недели, а ГГГГ номер года) в дату, соответствующую по недельнику этой недели:

Weekday(Str) На заметку Результат выполнения функции Weekday должен быть помещен в ячейку, отфор матированную для отображения даты.

Пример использования функции Weekday на рабочем листе показан на рис. 4.12.

Рис. 4.12. Пример преобразования номера недели в дату с помощью Функция Weekday имеет один аргумент:

Str — строка вида ‘‘Неделя НН ГГГГ’’.

Function ConvertWeekDay(str As String) As Date FirstMon = DateSerial(Right(str, 4), 1, 1) FirstMon = FirstMon - FirstMon Mod 7 + TStr = Right(str, Len(str) - 7) Week = Left(TStr, InStr(1, TStr, " ", 1)) + ConvertWeekDay = FirstMon + (Week - 1) * Разбор строки с символами разделителями Следующая функция извлекает элемент с заданным номером из строки с символами разделителями:

StringElement(str, chr, ind) Пример использования функции StringElement на рабочем листе пока зан на рис. 4.13.

Рис. 4.13. Пример извлечения элемента с заданным номером из стро ки с символами разделителями с помощью функции StringElement 132 Часть I Первые шаги Функция StringElement имеет 3 аргумента:

str — строка с символами разделителями;

ind — номер элемента, который нужно извлечь из строки.

Function StringElement(str As String, chr As String, Сортировка и конкатенация значений ячеек из заданного диапазона Следующая функция сортирует значения ячеек из заданного диапазона и проводит их конкатенацию с помощью символа разделителя,:

SortConcat(Rng) Пример использования функции SortConcat на рабочем листе показан на рис. 4.14.

Рис. 4.14. Пример сортировки и конкатенации значений ячеек из заданного диапазона с помощью функции SortConcat Функция SortConcat имеет один аргумент:

Для работы функции SortConcat используется процедура сортировки массива BubbleSort.

Function SortConcat(Rng As Range) As Variant Dim MySum As String, arr1() As String Dim j As Integer, i As Integer Dim cl As Range Dim concat As Variant On Error GoTo FuncFail:

'Инициализация результата функции.

SortConcat = 0# 'Завершить выполнение функции, если диапазон ячеек пуст.

If Rng.Count = 0 Then Exit Function 'Создать массив с размером, равным размеру диапазона ячеек.

ReDim arr1(1 To Rng.Count) 'Заполнить массив.

For Each cl In Rng 'Отсортировать элементы массива.

Call BubbleSort(arr1) 'Создать строку из элементов массива.

For j = UBound(arr1) To 1 Step - If Not IsEmpty(arr1(j)) Then 'Присвоить значение функции.

SortConcat = Left(MySum, Len(MySum) - 2) 'Точка выхода из функции SortConcat.

concat_exit:

Exit Function 'Вывести в ячейке номер ошибки и ее описание.

FuncFail:

SortConcat = Err.Number & "-" & Err.Description Resume concat_exit End Function Следующая процедура реализует один из наиболее популярных методов сор тировки массива, получившего название ‘‘метода пузырьковой сортировки’’:

Sub BubbleSort(List() As String) 'Данная процедура сортирует содержимое массива по возрастанию.

Dim First As Integer, Last As Integer Dim i As Integer, j As Integer First = LBound(List) Last = UBound(List) If UCase(List(i)) > UCase(List(j)) Then 134 Часть I Первые шаги Сортировка числовых и строковых значений Следующая функция сортирует значения ячеек из смешанного диапазона (диапазона, содержащего как числовые, так и строковые значения) сперва в числовом, а затем в алфавитном порядке. Результат помещается в массив, ко торый может быть отображен на рабочем листе с помощью формулы массива.

Sorter(Rng) Пример использования функции Sorter показан на рис. 4.15.

Рис. 4.15. Сортировка значений ячеек из смешанного диапазона с Функция Sorter имеет один аргумент:

Function Sorter(Rng As Range) As Variant If Rng.Columns.Count > 1 Then Exit Function arr1 = Application.Transpose(Rng) 'Возвратить массив.

Sorter = Application.Transpose(arr1) End Function Для сортировки значений ячеек в смешанном диапазоне функция Sorter использует две процедуры.

Public Sub QuickSort(ByRef vntArr As Variant, _ Optional ByVal lngLeft As Long = -2, _ Optional ByVal lngRight As Long = -2) Dim vntTestVal As Variant If lngLeft = -2 Then lngLeft = LBound(vntArr) If lngRight = -2 Then lngRight = UBound(vntArr) If lngLeft < lngRight Then lngMid = (lngLeft + lngRight) \ vntTestVal = vntArr(lngMid) 'Если строка найдена, добавить ее адрес к результату 'выполнения функции.

End Function Запись содержимого ячейки в обратном порядке Следующая функция записывает содержимое ячейки в обратном порядке:

ReverseContents(myCell, IsText) Функция ReverseContents имеет 2 аргумента:

IsText — необязательный булев параметр, определяющий, является значение ячейки текстом (True, используется по умолчанию) или чис Function ReverseContents(myCell As Range, Optional IsText _ As Boolean = True) Dim OrigString As String, NewString As String 'Удалить символы пробела в начале и конце строки.

OrigString = Trim(myCell) For i = 1 To Len(OrigString) 'Запись исходной строки в обратном порядке путем добавления 'строки NewString после символа исходной строки.

NewString = Mid(OrigString, i, 1) & NewString ReverseContents = CLng(NewString) End Function Поиск наибольших значений в диапазоне ячеек Следующая функция возвращает адреса ячеек диапазона, содержащих наи большее значение:

ReturnMaxs(Rng) Пример использования функции ReturnMaxs на рабочем листе показан на рис. 4.17.

Рис. 4.17. Пример нахождения адресов ячеек диапазона, содержа щих наибольшее значение, с помощью функции ReturnMaxs Функция ReturnMaxs имеет один аргумент:

Rng — адрес диапазона ячеек.

Function ReturnMaxs(Rng As Range) As String Dim Mx As Double Dim myCell As Range 'Если диапазон состоит из одной ячейки, вернуть ее адрес 'в качестве результата выполнения функции.

If Rng.Count = 1 Then ReturnMaxs = Rng.Address(False, _ False): Exit Function 'Использование встроенной функции Max для нахождения 'наибольшего значения в диапазоне ячеек.

Mx = Application.Max(Rng) 'Зная максимальное значение в диапазоне ячеек, найти все 'ячейки, содержащие это значение, и возвратить их адреса.

For Each myCell In Rng ReturnMaxs = myCell.Address(False, False) myCell.Address(False, False) 138 Часть I Первые шаги End Function Получение адреса гиперссылки Следующая функция возвращает адрес гиперссылки:

GetAddress(Hyperlink) Пример использования функции GetAddress на рабочем листе показан на рис. 4.18.

Рис. 4.18. Пример получения адреса гиперссылки с помощью функции Функция GetAddress имеет один аргумент:

Hyperlink — адрес ячейки, содержащей гиперссылку.

Function GetAddress(HyperlinkCell As Range) GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, _ "mailto:", "") End Function Получение адреса столбца ячейки Следующая функция возвращает адрес столбца ячейки:

ColName(Rng) Функция ColName имеет один аргумент:

Function ColName(Rng As Range) As String ColName = Left(Rng.Range("A1").Address(True, False), _ InStr(1, Rng.Range("A1").Address(True, False), End Function Генерация постоянных случайных чисел Следующая функция используется для помещения в ячейку случайного StaticRAND() В отличие от встроенной функции СЛЧИС (RAND), значение которой изме няется при каждом открытии рабочей книги, значение функции StaticRAND изменяется только при принудительном пересчете значения ячейки. Пример использования функции StaticRAND на рабочем листе показан на рис. 4.19.

Функция StaticRAND не имеет аргументов.

Function StaticRAND() As Double Randomize StaticRAND = Rnd End Function Рис. 4.19. Пример генерации постоянного случайного числа с по мощью функции StaticRAND Использование структуры Select...Case Следующая функция демонстрирует пример использования структуры Select...Case для замены вложенных выражений If...Then...Else (рис. 4.20).

Рис. 4.20. Пример замены вложенных выражений If...Then...Else с помощью структуры Select...Case Function state_period(mth As Integer, yr As Integer) Select Case mth 31 июля " & yr - 31 августа " & yr - 30 сентября " & yr - 140 Часть I Первые шаги End Function Следующий шаг В следующей главе будет рассмотрен один из фундаментальных компонентов любого языка программирования цикл. Помимо базовых циклов, присутст вующих практически в каждом языке программирования, будет рассмотрен цикл For Each...Next, являющийся исключительной особенностью VBA.

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

В этой главе рассматриваются сле Select Case

дующие базовые конструкции циклов: Следующий шаг

For...Next;

Do...While;

Do...Until;

While...Loop;

Until...Loop.

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

For Each...Next.

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

142 Часть I Первые шаги Рассмотрим следующий фрагмент кода:

Переменная счетчик носит имя I. При первом выполнении цикла значе ние переменной I равно 1. Это приводит к тому, что ячейке, расположенной в 1 й строке 1 го столбца, будет присвоено значение 1 (рис. 5.1).

Рассмотрим действия VBA при достижении строки Next I. Перед выпол нением этой строки значение переменной I равно 1. После выполнения стро ки Next I VBA необходимо принять решение. Если после добавления к пе ременной счетчику 1 ее значение не превысило максимально допустимое зна чение, заданное с помощью оператора To, цикл следует продолжить. В данном случае значение переменной I увеличится до 2 и выполнение кода будет про должено с первой строки после выражения For. Значение переменной I до и после выполнения строки Next показано на рис. 5.2 и 5.3, соответственно.

Рис. 5.1. При первом выполнении Рис. 5.2. Перед выполнением строки Next цикла ячейке, расположенной в 1 й I значение переменной I равно 1. Увеличе строке 1 го столбца, будет присвое ние переменной I на 1 не приведет к пре Во время второго выполнения цикла значение переменной I равно 2, в ре зультате чего ячейке, расположенной во 2 й строке 2 го столбца, будет при своено значение 2 (рис. 5.4).

Рис. 5.3. После выполнения строки Next I Рис. 5.4. Во время второго выпол значение переменной I равно 2. Выполне нения цикла ячейке, расположен ние кода будет продолжено с первой строки ной во 2 й строке 2 го столбца, бу При последующих выполнениях цикла значение переменной I будет уве личено до 3, 4 и т.д. На 10 м шаге ячейке, расположенной в 10 й строке 10 го столбца, будет присвоено значение 10.

Рассмотрим, что произойдет с переменной I после выполнения строки Next I в 10 й раз. Как показано на рис. 5.5, перед выполнением строки Next I в 10 й раз значение переменной I равно 10.

Как всегда, после увеличения значения переменной счетчика VBA пред стоит принять решение относительно дальнейшего выполнения цикла. Вы полнение строки Next I в 10 й раз приводит к увеличению значения пере менной I до 10, что больше, чем максимальное значение, заданное с помощью оператора To. VBA завершает цикл и переходит к выполнению первой строки кода после выражения Next (рис. 5.6).

Рис. 5.5. Перед выполнением строки Рис. 5.6. После увеличения значения пе Next I в 10 й раз значение переменной ременной I до 11 VBA выходит из цикла При намерении использовать переменную I после выполнения цикла сле дует помнить, что ее значение может превысить максимально допустимое зна чение, заданное с помощью оператора To.

Результат выполнения цикла после 10 итераций показан на рис. 5.7.

Рис. 5.7. Результат выполнения цикла после 10 итераций Наиболее распространенное применение цикла For...Next заключается в обработке строк заданного диапазона на основе некоторого критерия. При веденный ниже цикл используется для выделения всех строк, содержащих по ложительное число в столбце F:

For i = 2 To If Cells(i, 6).Value > 0 Then Cells(i, 8).Value = "Выручка от сервиса" Cells(i, 1).Resize(1, 8).Interior.ColorIndex = Next i 144 Часть I Первые шаги Данный цикл обрабатывает 2 10 строки рабочего листа. Если в столбце F строки находится положительное число, в столбец H помещается надпись ‘‘Выручка от сервиса’’, а все ячейки данной строки, расположенные в столб цах A–H, выделяются зеленым цветом (рис. 5.8).

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

FinalRow = Cells(65536, 1).End(xlUp).Row For i = 2 To FinalRow If Cells(i, 6).Value > 0 Then Cells(i, 8).Value = "Выручка от сервиса" Cells(i, 1).Resize(1, 8).Interior.ColorIndex = Использование переменных имеет определенные особенности, которые необходимо учитывать. Если импортированный файл счетов будет содержать только одну строку заголовка, значение переменной FinalRow окажется рав ным 1, а первая строка цикла примет вид For I = 2 to 1. Поскольку на чальное значение счетчика больше его максимально допустимого значения, цикл будет пропущен и выполнение кода начнется со строки, следующей за строкой Next I.

Изменение шага в цикле For...Next Цикл For...Next предусматривает возможность изменения значения пе ременной счетчика с шагом, отличным от 1. Рассмотрим задачу выделения цветом каждой второй строки в заданном диапазоне ячеек. Чтобы добиться этого, следует изменить шаг приращения значения переменной счетчика, воспользовавшись оператором Step:

FinalRow = Cells(65536, 1).End(xlUp).Row For I = 2 To FinalRow Step Cells(I, 1).Resize(1, 8).Interior.ColorIndex = В результате выполнения приведенного выше кода строки 2, 4, 6 и т.д. бу дут выделены бледно зеленым цветом (рис. 5.9).

Рис. 5.9. Пример изменения шага приращения значения переменной счетчика цикла For...Next с помощью оператора Step Значение переменной счетчика может изменяться практически с любым шагом. Ниже приведен пример извлечения каждой десятой строки (Step 10) из заданного диапазона ячеек:

FinalRow = Cells(65536, 1).End(xlUp).Row NextRow = FinalRow + Cells(NextRow - 1, 1).Value = "Выборка из приведенных выше данных" For I = 2 To FinalRow Step Cells(I, 1).Resize(1, 8).Copy Destination:=Cells(NextRow, 1) NextRow = NextRow + Next I Значение переменной счетчика может изменяться и в направлении от большего к меньшему. В частности, это может пригодиться при выборочном удалении строк, как показано ниже:

'Удаление строк со значением S54 в столбце C.

FinalRow = Cells(65536, 1).End(xlUp).Row For I = FinalRow To 2 Step - If Cells(I, 3).Value = "S54" Then Cells(I, 1).EntireRow.Delete Next I Досрочное завершение выполнения цикла Иногда выполнение цикла можно завершить досрочно. Рассмотрим задачу поиска строки, удовлетворяющей определенному критерию. Как только дан ная строка будет найдена, выполнение оставшейся части цикла теряет смысл.

Для досрочного выхода из цикла применяется выражение Exit For.

Следующий код используется для поиска строки с положительным числом в столбце F и нулем в столбце E. При нахождении такой строки выдается со 146 Часть I Первые шаги общение об ошибке, а указатель помещается в ячейку проблемной строки, расположенную в столбце F:

'Следующий код используется для поиска ошибок в исходных данных.

FinalRow = Cells(65536, 1).End(xlUp).Row ProblemFound = False For I = 2 To FinalRow If Cells(I, 6).Value > 0 Then If ProblemFound Then Вложение циклов Цикл может выполняться внутри другого цикла. Одним из наиболее нагляд ных примеров вложения циклов является цикл, обрабатывающий строки в за данном диапазоне ячеек, внутри которого выполняется цикл, обрабатывающий столбцы этих строк. Рассмотрим набор данных, представленный на рис. 5.10.

Рис. 5.10. Вложение циклов позволяет реализовать последовательную обработку всех ячеек этого диапазона FinalRow = Cells(65536, 1).End(xlUp).Row FinalCol = Cells(1, 255).End(xlToLeft).Column For I = 2 To FinalRow 'Если номер строки - четный, начать с 1-го столбца.

'Если номер строки - нечетный, начать со 2-го столбца.

For j = StartCol To FinalCol Step Cells(I, j).Interior.ColorIndex = Для обработки строк набора данных используется внешний цикл с пере менной счетчиком I, а для обработки столбцов этих строк внутренний цикл с переменной счетчиком J. Поскольку набор данных состоит из 7 строк (см. рис. 5.10), внешний цикл проходит 7 итераций. Каждой итерации внеш него цикла соответствует 6 или 7 итераций внутреннего цикла (это зависит от номера обрабатываемой строки). Результат выполнения приведенного выше кода показан на рис. 5.11.

Рис. 5.11. Результат выполнения вложенных циклов Циклы Do...Loop Существует несколько разновидностей цикла Do...Loop. Его наиболее про стой вариант используется для выполнения большого числа однообразных опера ций. Рассмотрим задачу преобразования списка адресов, показанного на рис. 5.12.

Рис. 5.12. Преобразование этого списка адресов в формат базы данных позволит автоматизировать процесс рассылки стандартных писем 148 Часть I Первые шаги Чтобы преобразовать подобный список адресов в формат базы данных (имя в столбце B, название улицы в столбце C, город и почтовый индекс в столбце D), можно записать макрос, включив режим относительных ссылок (см. главу 1, ‘‘Excel и VBA гремучая смесь’’). Предназначение макроса со стоит в преобразовании в формат базы данных одного адреса и установке ука зателя на ячейку, содержащую имя следующего адресата в списке:

Sub Macro3() ' Макрос3 Макрос ' Макрос записан 29.01.2005 (Александр Журавлев) ' Преобразовать в формат базы данных один адрес.

' Установить указатель на ячейку, содержащую имя следующего ' адресата в списке.

' Сочетание клавиш: Ctrl+Shift+A Selection.Copy ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(-1, 2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, -2).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(-2, 3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(4, -3).Range("A1").Select Об относительных ссылках рассказывается в разделе “Возможное решение:

использование относительных ссылок” главы 1 на с. 52.

Приведенный выше макрос не предназначен для профессионального применения и является примером решения единовременной задачи.

С помощью макроса преобразование в формат базы данных одного адреса сводится к установке указателя на ячейку, содержащую имя адресата, и нажа тию комбинации клавиш. После копирования составляющих адреса в столбцы B, C и D указатель устанавливается на ячейку, содержащую имя следующего адресата в списке (рис. 5.13).

Использование макроса позволяет преобразовывать список адресов в фор мат базы данных со скоростью 1 адрес в секунду. Однако эффективно ли дан ное решение при условии, что список состоит из 5000 адресов?

Рис. 5.13. После преобразования в формат базы данных одного адреса указатель устанавли вается на ячейку, содержащую имя следующего адресата Поместив код макроса между выражениями Do и Loop, его можно выпол нять бесконечно. Таким образом, часы монотонной работы можно свести к нескольким минутам наблюдения за ходом выполнения макроса.

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

Sub Macro3() ' Макрос3 Макрос ' Макрос записан 29.01.2005 (Александр Журавлев) ' Преобразовать в формат базы данных один адрес.

' Установить указатель на ячейку, содержащую имя следующего ' адресата в списке.

' Сочетание клавиш: Ctrl+Shift+A Selection.Copy ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(-1, 2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, -2).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(-2, 3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(4, -3).Range("A1").Select Loop End Sub Приведенный выше цикл представляет собой компромиссное решение, направленное на быстрое выполнение поставленной задачи. К счастью, цикл Do...Loop поддерживает возможность своего досрочного завершения.

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

150 Часть I Первые шаги Sub Macro3() ' Макрос3 Макрос ' Макрос записан 29.01.2005 (Александр Журавлев) ' Преобразовать в формат базы данных один адрес.

' Установить указатель на ячейку, содержащую имя следующего ' адресата в списке.

' Сочетание клавиш: Ctrl+Shift+A If Not Selection.Value > "" then Exit Do ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Application.CutCopyMode = False ActiveCell.Offset(-1, 2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, -2).Range("A1").Select Application.CutCopyMode = False ActiveCell.Offset(-2, 3).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(4, -3).Range("A1").Select Использование операторов While и Until Операторы While и Until могут использоваться как в выражении Do, так и в выражении Loop. Единственным обязательным условием является нали чие некоторого критерия, принимающего значение True или False.

При использовании конструкции Do While...Loop цикл не выполняется, если равен False. К примеру, при чтении со держимого текстового файла цикл не должен выполняться, если был достигнут конец файла (об этом свидетельствует значение функции EOF, равное True):

'Считать содержимое текстового файла, 'за исключением итоговых строк.

Open "C:\Счет.txt" For Input As # 'Импортировать строку.

В приведенном выше коде было использовано ключевое слово NOT. При дос тижении конца файла значение функции EOF(1) становится равным True. Не которые программисты считают, что частое использование ключевых слов NOT затрудняет восприятие кода. Чтобы избавиться от NOT, можно воспользоваться альтернативной конструкцией Do Until...Loop:

'Считать содержимое текстового файла, 'за исключением итоговых строк.

Open "C:\Счет.txt" For Input As # Do Until EOF(1) Line Input #FileNumber, Data If Not Left(Data, 5) = "ВСЕГО" Then 'Импортировать строку.

Иногда цикл необходимо выполнить хотя бы один раз. Для этого операто ры While и Until помещают в конец цикла, в выражение Loop. В результате выполнения приведенного ниже кода пользователю предлагается ввести неко торое число (сумму, указанную в счете) до тех пор, пока он не введет 0:

TotalSales = x = InputBox(Prompt:="Введите сумму следующего счета _ или 0 для завершения.") TotalSales = TotalSales + x Loop Until x = MsgBox "Общая сумма сегодняшних продаж составила $" & TotalSales В следующем примере пользователю предлагается ввести сумму, указан ную в чеке. Оплата нескольких счетов одним чеком — весьма распространен ная практика. Макрос последовательно ‘‘погашает’’ счета (начиная с самых ранних) до тех пор, пока не исчерпает сумму чека.

'Ввести сумму, указанную в чеке.

AmtToApply = InputBox("Введите сумму, указанную в чеке") 'Погасить счета, начиная с самых ранних, 'уменьшая при этом значение переменной AmtToApply.

NextRow = Do While AmtToApply > OpenAmt = Cells(NextRow, 3) If OpenAmt > AmtToApply Then 'Погасить счет с помощью чека.

Cells(NextRow, 4).Value = AmtToApply Cells(NextRow, 4).Value = OpenAmt AmtToApply = AmtToApply - OpenAmt NextRow = NextRow + Loop Возможность использования операторов While и Until как в начале, так и в конце конструкции Do...Loop, позволяет осуществлять тонкий контроль над ходом выполнения цикла.

152 Часть I Первые шаги Цикл While...Wend Цикл While...Wend был включен в VBA для обеспечения обратной со вместимости. В справочной системе VBA Microsoft рекомендует использо вать циклы Do...Loop как обладающие более широкими возможностями.

Чтобы объяснить принцип работы цикла While...Wend, приведем не большой пример.

'Считывание информации о счетах и вычисление общей суммы продаж.

Open "C:\Счет.txt" For Input As # MsgBox "Общая сумма продаж = " & TotalSales Первой строкой цикла While...Wend всегда является строка While, последней строка Wend. Возможность досрочного выхода из цикла не предусмотрена. За счет наличия операторов While и Until, кото рые могут применяться как в выражении Do, так и в выражении Loop, а также возможности досрочного выхода из цикла, конструкция Do...Loop заслуженно считается более надежной и гибкой, нежели конструкция While...Wend.

Цикл For Each...Next Цикл For Each...Next является одним из наиболее полезных циклов объектно ориентированного языка программирования. К сожалению, этот цикл не поддерживается средством записи макросов.

Рабочая книга Excel переполнена всевозможными коллекциями объек тов рабочие листы в рабочей книге, ячейки в диапазоне, сводные таблицы на рабочем листе, последовательности данных на диаграмме и т.п. Цикл For Each...Next предназначен для последовательной обработки элементов кол лекции. Прежде чем перейти к его более подробному изучению, рассмотрим понятие объектной переменной.

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

Существует мнение, согласно которому все переменные, используемые в процедуре, необходимо объявлять в ее начале с помощью ключевого слова Dim. Это позволяет указать тип переменной, например Integer или Double.

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

Sub Test() Dim WSD As Worksheet Dim MyCell As Range Dim PT As PivotTable Set WSD = ThisWorkbook.Worksheets("Данные") Set MyCell = WSD.Cells(65536, 1).End(xlUp).Offset(1, 0) Set PT = WSD.PivotTables(1) Чтобы присвоить значение объектной переменной, помимо знака равенст ва следует воспользоваться ключевым словом Set, как показано выше.

Одним из наиболее существенных преимуществ использования объектных переменных является возможность быстрого обращения к нужному объекту, например, WSD вместо ThisWorkbook.Worksheets("Данные").

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

Вместо переменной счетчика в цикле For Each...Next используется объектная переменная. В приведенном ниже коде такой переменной является переменная Cell:

For Each Cell In Range("A1").CurrentRegion.Resize(, 1) If Left(Cell.Value, 5) = "Всего" Then Cell.Resize(1, 8).Font.Bold = True Next Cell Свойство CurrentRegion используется для выделения непрерывного диа пазона ячеек, а свойство Resize — для ограничения диапазона столбцом A.

С помощью следующего кода производится поиск рабочего листа с задан ным именем в коллекции всех открытых рабочих книг:

For Each wb in Workbooks If wb.Worksheet(1).Name = "Menu" Then WBFound = True WBName = wb.Name End If Next wb В результате выполнения приведенного ниже кода с текущего рабочего листа будут удалены все находящиеся на нем фигуры:

For Each Sh In ActiveSheet.Shapes Sh.Delete Next Sh Выполнение следующего кода приведет к удалению с текущего рабочего листа всех сводных таблиц:

For Each PT In ActiveSheet.PivotTables PT.TableRange2.Clear Next PT 154 Часть I Первые шаги Обработка всех файлов в папке Рассмотрим несколько полезных процедур, построенных на применении циклов.

Первая процедура использует объект VBA FileSearch для нахождения всех JPG файлов в указанной папке и вывода их списка на рабочем листе Excel.

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

Sub ListJpgFiles() 'Этот макрос находит все JPG-файлы в заданной 'папке и выводит их список на рабочем листе Excel.

'Очистить все ячейки рабочего листа.

'Создать заголовки столбцов.

Range("A1:D1").Value = Array("FileName", "Path", _ "FileName", "NewPath") 'Поиск файлов осуществляется с помощью объекта FileSearch.

With Application.FileSearch FilesToProcess =.FoundFiles.Count 'Обработать список найденных файлов.

'Отделить путь к файлу от его имени.

Application.PathSeparator Then Приведенная выше процедура может быть использована для перемещения JPG файлов. Введите в столбце D полный путь к папке, в которую необходимо пере местить соответствующий файл. На каждой итерации приведенного ниже цикла For Each...Next объектная переменная Cell содержит ссылку на ячейку в столбце A (исходное имя файла), а выражение Cell.Offset(0, 3) — ссылку на соответствующую ячейку в столбце D (полный путь к папке, в которую необходимо переместить файл).

Sub CopyToNewFolder() FinalRow = Range("A65536").End(xlUp).Row For Each Cell In Range("A2:A" & FinalRow) OrigFile = Cell.Value If Cell.Offset(0, 3).Value > "" Then Application.PathSeparator & Cell.Offset(0, 2) End Sub Управление выполнением кода: использование конструкций If...Then...Else и Select Case Управление выполнением кода это еще один фундаментальный аспект программирования, игнорируемый средством записи макросов. VBA поддер живает две конструкции, реализующие концепцию управления выполнением кода, — If...Then...Else и Select Case.

Знакомство с конструкцией If...Then...Else Краеугольным камнем концепции управления выполнением кода является выражение If. Рассмотрим задачу копирования списка продуктов, показан ного на рис. 5.14, в два списка ‘‘Фрукты’’ и ‘‘Овощи’’.

156 Часть I Первые шаги Начинающему программисту может придти в голову идея создания двух циклов по одному для составления каждого списка. Тем не менее, данная задача решается с помощью всего лишь одного цикла и конструкции If...Then...Else.

Условие Обязательной частью выражения If является условие, имеющее значение True или False. Ниже приведены примеры простых и сложных условий:

If Range("A1").Value = "Товар" And Range("B1").Value If Range("A1").Value = "Товар" Or Range("B1").Value = Конструкция If...Then...End If Строки кода, размещенные после выражения If, будут выполнены только при соблюдении указанного условия. Чтобы завершить блок If, следует вос пользоваться выражением End If, как показано ниже:

Sub ColorFruitRedBold() FinalRow = Cells(65536, 1).End(xlUp).Row Cells(I, 1).Resize(1, 3).Font.Bold = True Cells(I, 1).Resize(1, 3).Font.ColorIndex = MsgBox "Все фрукты выделены красным цветом и утолщением _ шрифта" Конструкция If...Then...Else...End If Иногда необходимо выполнить один фрагмент кода, если условие равно True, и другой если оно равно False. В VBA для этого следует указать вто рой фрагмент кода после ключевого слова Else. Для завершения блока If...Then...Else используется выражение End If:

Sub FruitRedVegGreen() FinalRow = Cells(65536, 1).End(xlUp).Row Cells(I, 1).Resize(1, 3).Font.ColorIndex = Cells(I, 1).Resize(1, 3).Font.ColorIndex = MsgBox "Все фрукты выделены красным цветом, _ а все овощи - изумрудным" End Sub Конструкция If...ElseIf...End If Структура If...End If поддерживает возможность проверки нескольких условий с помощью ключевого слова ElseIf. Как показано на рис. 5.14, список продуктов содержит одно травянистое растение, что наводит на мысль о необходимости проверки трех условий — является ли элемент списка фруктом, овощем или травянистым растением? При негативном результате всех трех проверок можно сделать вывод, что элемент списка содержит ошибку:

Sub MultipleIf() FinalRow = Cells(65536, 1).End(xlUp).Row For I = 2 To FinalRow If Cells(I, 1).Value = "Фрукт" Then Cells(I, 1).Resize(1, 3).Font.ColorIndex = ElseIf Cells(I, 1).Value = "Овощ" Then Cells(I, 1).Resize(1, 3).Font.ColorIndex = ElseIf Cells(I, 1).Value = "Растение" Then Cells(I, 1).Resize(1, 3).Font.ColorIndex = 'Элемент списка содержит ошибку.

Cells(I, 1).Resize(1, 3).Interior.ColorIndex = MsgBox "Фрукты выделены красным цветом, овощи - изумрудным, _ а травянистые растения - синим" End Sub Конструкция Select Case...End Select Когда условий становится слишком много, использование структуры If...ElseIf теряет свою привлекательность. Для таких случаев VBA распо лагает конструкцией Select Case, первая строка которой содержит так на зываемое условное выражение:

Select Case Cells(I, 1).Value После строки с условным выражением перечислены его возможные значе ния, записанные после ключевого слова Case. Для каждого значения должен быть указан фрагмент кода, который будет выполнен, если условное выраже ние примет это значение.

Если необходимо предусмотреть возможность принятия условным вы ражением значения, отличного от всех перечисленных, воспользуйтесь ключевыми словами Case Else. Завершает блок Select Case выраже ние End Select.

158 Часть I Первые шаги Единственное отличие следующего кода от приведенного ранее заключает ся в использовании конструкции Case Select вместо конструкции If...ElseIf:

Sub SelectCase() FinalRow = Cells(65536, 1).End(xlUp).Row MsgBox "Фрукты выделены красным цветом, овощи - изумрудным, _ а травянистые растения - синим" Использование сложных выражений Case Выражения Case могут быть как простыми (рассматривались в преды дущем разделе), так и сложными. Следующее выражение определяет единое действие для ячеек, содержащих значения ‘‘Клубника’’, ‘‘Голубика’’ и ‘‘Малина’’:

Case "Клубника", "Голубика", "Малина" В качестве значения условного выражения можно указать диапазон, как показано ниже:

Кроме того, воспользовавшись ключевым словом Is и оператором сравне ния (например, > или Case Else Вложение выражений If Выражение If может находиться внутри другого выражения If. Вложение выражений If требует от программиста аккуратности при оформлении про граммного кода. Поскольку в конце подобных конструкций скапливается не сколько строк End If, соблюдение отступов поможет определить, к какому выражению If относится та или иная строка End If.

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

Если объем заказа фруктов составляет менее 5 ящиков, скидка не пре доставляется.

Если объем заказа фруктов составляет от 5 до 20 ящиков, предоставля ется скидка в размере 10%.

Если объем заказа фруктов составляет более 20 ящиков, предоставляет ся скидка в размере 15%.

Если объем заказа травянистых растений составляет менее 10 ящиков, скидка не предоставляется.

Если объем заказа травянистых растений составляет от 10 до 15 ящиков, предоставляется скидка в размере 3%.

Если объем заказа травянистых растений составляет более 15 ящиков, предоставляется скидка в размере 6%.

Если объем заказа овощей, за исключением спаржи, составляет 5 ящиков и более, предоставляется скидка в размере 12%.

Если объем заказа спаржи составляет 20 ящиков и более, предоставля ется скидка в размере 12%.

Во время распродажи продукта на него предоставляется скидка в раз мере 25%. Никакие другие скидки при этом не предоставляются.

На этой неделе проводится распродажа клубники, салата и помидоров.

Ниже приведен код, реализующий указанную политику скидок.

Sub ComplexIf() FinalRow = Cells(65536, 1).End(xlUp).Row For I = 2 To FinalRow ThisClass = Cells(I, 1).Value ThisProduct = Cells(I, 2).Value ThisQty = Cells(I, 3).Value 'Определение продуктов, находящихся на распродаже.

Select Case ThisProduct Case "Клубника", "Салат", "Помидоры" 'Расчет скидки.

160 Часть I Первые шаги 'Расчет скидки на спаржу.

End If 'Проводится ли распродажа продукта?

Range("D1").Value = "Скидка" MsgBox "Расчет скидок завершен" Следующий шаг Цикл это фундаментальный компонент любого языка программирования.

VBA поддерживает как традиционные циклы For...Next и Do...Loop, так и цикл For Each...Next, характерный для объектно ориентированных язы ков. В следующей главе будет рассмотрен очень важный для Excel VBA стиль записи ссылок с таинственным названием R1C1.

R1C Сравнение стилей записи ссылок A1 и R1C Стиль записи ссылок A1 берет свое начало от приложения VisiCalc. Использование стиля ссылок Для обращения к ячейке, располо R1C1 в Excel

женной в верхнем левом углу элек Чудесный мир формул Excel..... тронной таблицы, Дэн Бриклин (Dan Ссылки в стиле R1C1

Bricklin) и Боб Фрэнкстон (Bob Использование ссылок в стиле Frankston) предложили использовать R1C1 при условном запись ‘‘A1’’. Аналогичную адресную схему взял на вооружение Мич Кей пор (Mitch Kapor) в своем легендар ном продукте Lotus 1 2 3. Вскоре за конодательницей мод попыталась стать Microsoft, предложив рынку программу Multiplan и стиль записи ссылок R1C1. Согласно этому стилю для обращения к ячейке, располо женной в верхнем левом углу элек тронной таблицы, использовалась запись ‘‘R1C1’’, указывающая на то, что ячейка находится в 1 й строке (Row) 1 го столбца (Column).

Благодаря лидирующему положе нию, которое занимал на рынке про дукт Lotus 1 2 3 в 1980 х и начале 1990 х годов, стиль записи ссылок A стал общепризнанным стандартом.

Осознав всю невыгодность своей стратегии, Microsoft добавила в Excel поддержку адресации A1, сделав ее используемой по умолчанию. Следу ет отметить, что официально Micro soft поддерживает оба способа записи ссылок.

162 Часть I Первые шаги R1C1 — дела давно минувших дней?

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