WWW.NEW.Z-PDF.RU
БИБЛИОТЕКА  БЕСПЛАТНЫХ  МАТЕРИАЛОВ - Онлайн ресурсы
 

Pages:   || 2 | 3 | 4 | 5 |

«и макросов в Microsoft® Excel ?usiness solutions VBA and Macros for Microsoft® Excel Bill Jelen, Mr. Excel Tracy Syrstad 800 East 96th Street Indianapolis, ...»

-- [ Страница 1 ] --

?изнес-решения

Применение VBA

и макросов в

Microsoft® Excel

?usiness solutions

VBA and Macros

for

Microsoft® Excel

Bill Jelen, Mr. Excel

Tracy Syrstad

800 East 96th Street

Indianapolis, Indiana 46240

?изнес-решения

Применение VBA

и макросов в

Microsoft® Excel

Билл Джелен, “Мистер Excel”

Трейси Сирстад

Москва • Санкт-Петербург • Киев

ББК 32.973.26 018.2.75

Д40

УДК 681.3.07

Издательский дом ‘‘Вильямс”

Главный редактор С.Н. Тригуб

Зав. редакцией В.Р. Гинзбург

Перевод с английского и редакция А.В. Журавлева

По общим вопросам обращайтесь в Издательский дом ‘‘Вильямс’’ по адресу:

info@williamspublishing.com, http://www.williamspublishing.com 115419, Москва, а/я 783; 03150, Киев, а/я 152 Джелен, Билл, Сирстад, Трейси .

Д40 Применение VBA и макросов в Microsoft Excel. : Пер. с англ. М. : Из дательский дом ‘‘Вильямс’’, 2006. 624 с. : ил. Парал. тит. англ .

ISBN 5 8459 0882 5 (рус.) В этой книге рассматривается автоматизация выполнения всевозможных задач с помощью Excel VBA от создания простого отчета до разработки полноценного приложения Excel ‘‘с нуля’’. Авторы книги полагаются на достаточно высокий уро вень подготовки читателя, однако допускают, что материал каждой главы не зна ком ему в полном объеме. Особое внимание при изложении материала уделяется таким высокоэффективным средствам Excel, как диаграмма, расширенный фильтр и сводная таблица. Прежде чем продемонстрировать решение той или иной задачи с помощью VBA, авторы кратко останавливаются на ее выполнении с помощью пользовательского интерфейса Excel. Прочитав книгу, читатель получит знания, необходимые для автоматизации выполнения повседневных задач и создания соб ственных решений в Excel с помощью VBA .

Книга предназначена для опытных пользователей Excel .

ББК 32.973.26 018.2.75 Все названия программных продуктов являются зарегистрированными торговыми марками соответствующих фирм .

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

Authorized translation from the English language edition published by Sams Publishing, Copyright © 2004 .

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage re trieval system, without permission from the publisher .

Russian language e

–  –  –

Благодарности Спасибо Мале Сингху (Mala Singh) из XLSoft Consulting за помощь в написа нии главы 10; Тому Уртису (Tom Urtis) за техническое редактирование; Джерри Колю (Jerry Kohl) за его великолепные идеи; Джанет Гарсиа (Jeanette Garcia), Барбе Джелен (Barb Jelen), Дагу и Стейси Джеффериз за техническую помощь;

Зику, Джошу и Мэри Эллен Джелен за их терпение; Тому Мацешеку (Tom Ma cioszek) за ‘‘дружескую’’ проверку; Чеду Ротшиллеру (Chad Rothschiller) из Mi crosoft за неоценимую помощь в изучении Excel XML; Дейву Гейнеру (Dave Gainer), Стиву Заске (Steve Zaske), Эрику Паттерсону (Eric Patterson) и Джо Чи рилову (Joe Chirilov) из Microsoft; Лоретте Йейтс (Loretta Yates), Шону Диксону (Sean Dixon), Марго Кэттс (Margo Catts), Энди Бистеру (Andy Beaster), Грегу Виганду (Greg Wiegand), Эми Сорокас (Amy Sorokas), Ким Спилкер (Kim Spilker), Эрике Миллен (Erika Millen), Кэти Бидуэл (Kathy Bidwell), Синди Ти терс (Cindy Teeters), Мишель Митчелл (Michelle Mitchell) и Гэри Адэру (Gary Adair) из Pearson; Иване Тейлор (Ivana Taylor) за блестящий маркетинг; читате лям MrExcel .

com, нашим клиентам и всем MVP; Дэну Бриклину (Dan Bricklin), Бобу Фрэнкстону (Bob Frankston) и Митчу Кейпору (Mitch Kapor) за создание электронных таблиц; Уильяму Брауну (William Brown) из Waterside; Пэм Гензель 24 Благодарности

–  –  –

Спасибо Корту Чиллдону Хоффу (Cort Chilldon Hoff) за поддержку в труд ные минуты; Хуану Пабло Гонсалесу Руизу (Juan Pablo Gonzales Ruiz) за его советы (в частности, касающиеся функций из главы 4); Даниелю Клэнну (Daniel Klann), Деннису Валентайну (Dennis Wallentin), Ивану Ф. Моале (Ivan F. Moala), Хуану Пабло Гонсалесу (Juan Pablo Gonzales), Масаре Каджи (Masaru Kaji), Натану П. Оливеру (Nathan P. Oliver), Ричи Силлсу (Richie Sills), Расселу Гауфу (Russell Hauf), Суату Мехмету Озгуру (Suat Mehmet Ozgur), Тому Уртису (Tom Urtis), Томми Майлзу (Tommy Miles) и Вэю Цзянгу (Wei Jiang) за их вклад в написание главы 13; Крису Лемэру (Chris Lemair) за то, что он открыл для меня удивительный мир Excel и макросов, а также Энн Трой (Anne Troy) за то, что она познакомила меня с Биллом .

Трейси VBA — работа.. .

на результат Язык программирования Visual Basic for Applications (VBA) позво VBA — работа на результат......... 25 ляет существенно повысить произ Как организована эта книга........27 Для кого предназначена эта водительность труда пользователей книга

Microsoft Excel .

История развития Не дожидаясь помощи от отдела электронных таблиц информационных технологий, поль и макросов

зователи Excel могут самостоятельно Будущее Excel и VBA

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

обязанностей. Это сулит как пре Рассматриваемые имущества, так и недостатки. С од версии Excel

ной стороны, пользователи Excel Программный код

Следующий шаг

смогут повысить эффективность свое го труда. С другой, им придется ра зобраться со всеми тонкостями ис кусства создания макросов с помо щью Excel VBA .

Уверен, что в этот самый момент вы или кто либо из ваших коллег все еще выполняете в Excel рутин ные операции, которые могут быть автоматизированы с помощью VBA .

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

26 Введение

Практикум

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

Прежде всего, Валери экспортировала главную бухгалтерскую книгу из ERP системы в текстовый файл с разделителями запятыми (CSV). Затем полученный CSV файл был импортирован в Excel .

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

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

Звездный час Валери передала отчет своему начальнику, который уже отчаялся получить его в срок. Она тут же стала “героем дня” и пребывала на седьмом небе от счастья .

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

Тяжкое бремя славы Корпорация, в которой работает Валери, насчитывает 46 отделов. Подготовка фи нансового отчета для каждого отдела подразумевает импортирование данных из ERP системы, удаление определенных счетов, создание нескольких сводных таб лиц и комбинацию полученных итоговых результатов. На подготовку первого от чета Валери потратила 3 часа. Она подсчитала, что с учетом полученного опыта сможет создать 46 отчетов не менее чем за 40 часов. Валери пришла в отчаяние .

–  –  –

Как организована эта книга Эта книга состоит из трех частей. Ее цель — научить читателя создавать макросы VBA для автоматизации выполнения рутинных задач в Excel .

Часть I, “Первые шаги” Глава 1, ‘‘Excel и VBA гремучая смесь’’, акцентирует внимание на фундамен тальной проблеме средства записи макросов Excel — средство записи макросов не работает. В главе 2, ‘‘Знакомство с Visual Basic for Applications’’, рассматриваются основы синтаксиса языка программирования Visual Basic for Applications. Глава 3, ‘‘Работа с диапазоном ячеек’’, посвящена работе с диапазонами ячеек .

В главе 4, ‘‘Функции, определенные пользователем’’, рассматривается соз дание функций, определенных пользователем, а также приводятся примеры решения 25 наиболее распространенных задач, встречающихся при повсе дневном программировании в Excel .

Глава 5, ‘‘Циклы и управление выполнением кода’’, посвящена циклам — фундаментальному компоненту любого языка программирования. Разрабаты вая решение для Валери, мы создали код, подготавливающий отчет для одного отдела, а затем поместили его в цикл с 46 итерациями .

В главе 6, ‘‘Стиль записи ссылок R1C1’’, описывается стиль записи ссылок R1C1, а в главе 7, ‘‘Имена’’, — использование имен. Глава 8, ‘‘События’’, по священа событиям, а глава 9, ‘‘Введение в пользовательские формы’’, — поль зовательским формам .

Часть II, “Автоматизация Excel” В главе 10, ‘‘Диаграммы’’, рассматривается использование VBA при работе с диаграммами. Глава 11, ‘‘Анализ данных с помощью расширенного фильт ра’’, посвящена анализу данных с помощью расширенного фильтра, а гла ва 12, ‘‘Сводные таблицы’’, — работе со сводными таблицами. В комбинации с VBA диаграммы, расширенный фильтр и сводные таблицы образуют мощ ную основу для создания всевозможных отчетов .

В главе 13, ‘‘Excel всемогущий’’, рассматриваются распространенные зада чи, встречающиеся при работе с Excel, и их решения с помощью VBA, пред лагаемые опытными программистами со всех уголков мира .

Глава 14, ‘‘Взаимодействие с Internet’’, посвящена автоматизации Web за просов, глава 15, ‘‘Поддержка XML в профессиональном выпуске Excel 2003’’, — работе с данными в формате XML, глава 16, ‘‘Автоматизация Word’’, — автомати зации Microsoft Word .

Часть III, “Удивительные возможности Visual Basic for Applications” В главе 17, ‘‘Массивы’’, рассматриваются массивы. Основное предназна чение массива заключается в упрощении обработки данных и повышении 28 Введение скорости выполнения программного кода. Глава 18, ‘‘Работа с текстовыми фай лами’’, посвящена работе с текстовыми файлами, а глава 19, ‘‘Использование Microsoft Access’’, — использованию баз данных Microsoft Access. Применение Excel в качестве пользовательского интерфейса, а MDB файла — в качестве базы данных позволяет добиться оптимального использования возможностей обеих программ .

В главе 20, ‘‘Создание пользовательских объектов, типов и коллекций’’, рас сматривается создание модулей классов, предназначенных для размещения поль зовательских объектов VBA. Глава 21, ‘‘Пользовательские формы профессио нальный подход’’, посвящена сложным элементам управления, а также различ ным приемам программирования пользовательских форм. В главе 22, ‘‘Интерфейс прикладного программирования (API) Windows’’, рассматриваются основы ис пользования функций интерфейса прикладного программирования (API) Win dows. Глава 23, ‘‘Обработка ошибок’’, посвящена обработке ошибок, глава 24, ‘‘Создание пользовательских меню и панелей инструментов’’, — созданию поль зовательских меню и панелей инструментов, глава 25, ‘‘Надстройки’’, — примене нию надстроек. Наконец, глава 26, ‘‘Практикум: создание приложения Excel ‘‘с нуля’’, представляет собой практикум, демонстрирующий процесс создания приложения Excel ‘‘с нуля’’ .

Для кого предназначена эта книга На мероприятии, посвященном выходу на рынок пакета приложений Mi crosoft Office 2003, корпорация Microsoft огласила результаты исследования, согласно которым среднестатистический пользователь применяет только 10% заложенных в Office возможностей. Эта книга предназначена для опытных пользователей Excel. Опрос, проведенный среди 2000 посетителей Web сайта MrExcel.com, показал, что 42% опытных пользователей Excel применяют в по вседневной работе все наиболее эффективные средства этого приложения .

Компания MrExcel Consulting часто устраивает семинары для бухгалтеров. Как правило, все они работают с Excel по 30 40 часов в неделю. Практически на каждом семинаре я демонстрирую слушателям возможности Excel, о которых они ранее и не подозревали, и тем не менее практически на каждом семинаре находится слушатель, который превосходит меня в знании того или иного средства Excel. Что я хочу этим сказать? Вероятно, читатель этой книги вели колепно разбирается в Excel. Несмотря на это, я предполагаю, что материал каждой главы незнаком в полном объеме для 58% опытных пользователей Ex cel. Прежде чем продемонстрировать решение той или иной задачи с помо щью VBA, я кратко остановлюсь на ее выполнении с помощью пользователь ского интерфейса Excel .

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

В 1979 году Дэн Бриклин (Dan Bricklin) и Боб Фрэнкстон (Bob Frankston) (рис. 1) в буквальном смысле изменили мир. Они создали первую электрон ную таблицу, предназначенную для выполнения на компьютерах Apple II, и назвали ее VisiCalc (сокращение от англ. ‘‘visual calculator’’ — визуальный калькулятор). Вскоре программа VisiCalc была перенесена на несколько раз личных платформ, включая IBM PC. В 1981 году была выпущена расширенная версия VisiCalc, предназначенная для выполнения на компьютерах Apple III и поддерживающая макросы командной строки. Проект VisiCalc прекратил свое существование в 1985 году в результате судебной тяжбы .

Рис. 1. Дэн Бриклин и Боб Фрэнкстон

В 1983 году Митч Кейпор (Mitch Kapor) создал программу Lotus 1 2 3 .

По своим функциональным возможностям Lotus 1 2 3, изначально разработан ная для выполнения под управлением операционной системы DOS, намного превзошла VisiCalc. В первый год объем продаж Lotus 1 2 3 достиг впечатляю щей цифры в 53 млн долларов. Вплоть до середины 90 х годов Lotus 1 2 3 зани мала лидирующее положение на рынке программ для работы с электронными 30 Введение таблицами. Несмотря на наличие конкурентов (Quattro, Multiplan и др.), Lotus 1 2 3 ‘‘де факто’’ оставалась стандартным инструментом бухгалтерского учета .

1985 год был ознаменован появлением на свет второго выпуска Lotus 1 2 3, поддерживающего 8192 строки и 256 столбцов — более 2 млн ячеек! Кроме того, пользователю была предоставлена возможность записывать простые макросы .

В 1990 году я был на 100% уверен в незыблемости позиций Lotus 1 2 3 на рынке программного обеспечения для работы с электронными таблицами .

В начале 90 х годов была выпущена версия Lotus 1 2 3 для операционной системы CP/M. В то же самое время Microsoft направила усилия на улучшение собственного продукта для работы с электронными таблицами — Excel. Про грамма Excel 3.0, выпущенная в 1990 году, существенно проигрывала Lotus

1 2 3. Тем не менее, Microsoft продолжала упорствовать, выпуская новую, улучшенную версию Excel каждые 1 2 года. Excel 4, выпущенная в 1992 году, уже пользовалась популярностью и предлагала возможность создавать макро сы с помощью языка XLM. Excel 5, выпущенная в 1993 году, поддерживала создание нескольких рабочих листов в пределах одной рабочей книги, а также запись макросов с помощью нового языка программирования — VBA. Благодаря наличию обратной совместимости с Lotus 1 2 3 продажи Excel стали стремительно возрастать. Середину 90 х годов прошлого столе тия можно без преувеличения назвать ‘‘золотой эрой’’ в развитии Excel .

В Excel 95 и Excel 97 были представлены новые функциональные средства, такие как сводные таблицы, автофильтр и автоматическое вычисление про межуточных итогов. Кроме того, в Excel 97 появилась новая среда разработ ки VBA. Доминированию Lotus 1 2 3 на рынке программ для работы с элек тронными таблицами был положен конец. На момент написания этой книги гремучая смесь в виде Excel и VBA прочно завоевала сердца более чем 400 млн пользователей по всему миру .

Будущее Excel и VBA С каждым новым выпуском программа для работы с электронными табли цами StarOffice Calc приближается по предлагаемым возможностям к Excel .

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

Наверняка многие слышали о том, что Microsoft собирается отказаться от VBA. На самом деле это маловероятно. По прошествии более чем 10 лет с мо мента появления VBA Microsoft все еще поддерживает макросы XLM (язык макросов, появившийся в Excel 4). К тому же Microsoft официально заявила о поддержке VBA в следующей версии Excel. Учитывая, что большинство поль зователей приобретают каждую вторую версию Office, VBA будет актуален по меньшей мере до 2009 года .

Введение Наконец, с точки зрения маркетинга было бы нелепо отказываться от VBA как от ключевого компонента, обеспечивающего Excel тотальное преимущест во над StarOffice Calc .

В октябре 2003 года корпорация Microsoft официально объявила о новой инициативе, направленной на повышение безопасности предлагаемых Mi crosoft решений. Это заявление имеет весьма серьезное значение, поскольку печально известный вирус Melissa использовал для своего распространения макросы VBA текстового редактора Word. Пресса и власть имущие отреагиро вали мгновенно, поместив VBA в список ‘‘вымирающих’’ технологий. Если безопасность приложений пакета Office будет оставаться под угрозой, Micro soft может быть вынуждена отказаться от VBA .

Одной из отличительных особенностей пакета Office 2003 является под держка языка программирования Visual Basic.NET. Язык Visual Basic 6 позво лял автоматизировать любую задачу в Excel XP с помощью VBA. В Excel 2003 некоторые задачи, такие как создание смарт документа или размещение со держимого на панели Справочные материалы (Research Pane), можно авто матизировать только из среды.NET .

Учитывая вопросы безопасности, возникающие при использовании VBA, Microsoft может заменить его набором инструментов.NET Tools for Office. Это было бы роковой ошибкой. На текущий момент свыше 400 млн пользователей Office могут приобрести книгу, подобную этой, в течении недели изучить осно вы создания макросов и начать разрабатывать собственные решения с помощью VBA. Производительность труда ‘‘белых воротничков’’ может существенно воз расти, а их зависимость от отдела информационных технологий — уменьшить ся. Если Microsoft заменит VBA набором инструментов.NET Tools for Office, ко нечный пользователь будет лишен возможности создавать макросы с помощью Excel. Кроме того, это нивелирует преимущество Excel перед конкурирующим продуктом StarOffice Calc, поддержка языка создания макросов в котором долж на появиться к 2007 году. Таким образом, инициатива по повышению безопас ности приложений пакета Office может обернуться для Microsoft утратой лиди рующих позиций на рынке программ для работы с электронными таблицами .

Тем не менее, я уверен, что VBA останется с нами по крайней мере до кон ца этого десятилетия. К тому же навыки создания макросов с помощью Excel VBA не утратят своей актуальности в результате перехода на среду.NET, а синтаксис VBA не так уж сильно отличается от синтаксиса Visual Basic.NET .

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

Курсив используется для выделения терминов, названий Web сайтов, а также акцентирования внимания читателя .

Моноширинным шрифтом выделяется код VBA, заголовки столбцов, ссылки, формулы, имена макросов, модулей, функций, процедур, 32 Введение переменных, констант, объектов, методов, свойств, файлов, адре сов URL и пр .

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

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

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

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

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

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

Рассматриваемые версии Excel На момент написания этой книги текущей версией Excel была версия Ex cel 2003. За исключением главы 15, ‘‘Поддержка XML в профессиональном выпуске Excel 2003’’, большая часть приведенного в книге программного кода совместима с Excel 2002 и Excel 2000. Более подробно о совместимости раз личных версий Excel рассказывается в главе 23, ‘‘Обработка ошибок’’ .

Программный код Прилагаемый к книге программный код включает все рассматриваемые в ней примеры.

Его можно загрузить по адресу:

http://www.williamspublishing.com/Books/5-8459-0882-5.html .

(Чтобы загрузить код, прилагающийся к англоязычному изданию этой книги, по сетите Web страницу по адресу: http://www.mrexcel.com/getcode.html.)

–  –  –

Ждем ваших отзывов!

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

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

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

Наши координаты:

info@williamspublishing.com

E mail:

http://www.williamspublishing.com

WWW:

Адреса для писем:

из России: 115419, Москва, а/я 783 из Украины: 03150, Киев, а/я 152 Часть I I Первые шаги

1. Excel и VBA — гремучая смесь

2. Знакомство с Visual Basic for Applications

3. Работа с диапазоном ячеек

4. Функции, определенные пользователем

5. Циклы и управление выполнением кода

6. Стиль записи ссылок R1C1

7. Имена

8. События

9. Введение в пользовательские формы

Глава 1 Excel VBA —

–  –  –

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

Средство записи макросов не работает!

С середины 90 х годов прошлого столетия корпорация Microsoft нача ла доминировать на рынке программ для создания электронных таблиц .

Несмотря на то что в части работы с электронными таблицами продукт Microsoft оказался действительно очень удачным (приверженцы Lotus 38 Часть I Первые шаги 1 2 3 быстро научились с ним работать), записать корректно работающий макрос в Excel не удавалось практически никому. Неоспоримое преимущество языка программирования Microsoft VBA перед языком макросов Lotus 1 2 3 нивелировалось низким качеством средства записи макросов .

Макрос, созданный накануне с помощью Lotus 1 2 3, прекрасно выполня ется и сегодня. Аналогичный макрос, созданный с помощью Microsoft Excel, мог преподнести неприятный сюрприз. Многие из тех, кто пытался создать свой первый макрос в Excel, приходили в отчаяние .

Visual Basic — это не BASIC Код, сгенерированный в результате создания моего первого макроса, не был похож ни на что, виденное мною ранее. Несмотря на то что я знал с пол дюжины различных языков программирования, так называемый ‘‘Visual Basic’’ оказался абсолютно неинтуитивным и даже приблизительно не напо минал тот BASIC, который я изучал в школе .

В 1995 году я уже в совершенстве освоил создание электронных таблиц .

И вот компания, в которой я работал, приняла решение о переходе с Lotus 1 2 3 на Excel. Без преувеличения, я оказался в сложном положении: с одной стороны — средство записи макросов, которое не работает так, как надо, с другой — язык программирования, в котором я ничего не понимал .

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

Нас должно объединять следующее: мы все пытались создать макрос в Excel и остались недовольны полученным результатом .

Хорошие новости Многочисленные недостатки средства записи макросов не являются не преодолимым препятствием на пути к постижению искусства программиро вания в Excel. Далее в книге будет рассказано, как исправить ошибки автома тически сгенерированного кода, а также как прочитать загадочный ману скрипт, написанный на языке Visual Basic .

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

Авторы книги работают в компании MrExcel Consulting, предлагающей ус луги по автоматизации процесса создания отчетов в Excel для огромного числа клиентов. В ходе своей работы мы часто сталкиваемся с очень похожими зада Excel и VBA — гремучая смесь Глава 1 чами: успешно импортировав данные в Excel, наши клиенты хотели бы упро стить долгий и утомительный процесс создания одних и тех же еженедельных, ежемесячных или ежеквартальных отчетов .

Именно это и предлагает VBA. Часы, потраченные на программирование макросов, сводят создание отчетов к нескольким щелчкам мыши. Поистине царская награда!

В этой главе будут рассмотрены причины некорректной работы средства за писи макросов. В частности, будет рассмотрен макрос, который начинает сбо ить на следующий день после своего создания. Не обращайте внимания на не понятный вам код. Цель этой главы — показать фундаментальную проблему средства создания макросов Excel и познакомить вас со средой разработки VBA .

–  –  –

Панель инструментов Visual Basic содержит несколько кнопок .

Выполнить макрос (Run Macro). Отображает список доступных макросов .

Записать макрос (Record Macro). Начинает процесс записи макроса и отображает панель инструментов Остановить запись (Stop Recording) (рис. 1.2) .

Рис. 1.2. Одна из самых маленьких панелей инструментов в Excel содержит одну из самых важных для записи работоспособного макроса кнопок (Относительная ссылка) Безопасность (Security). Отображает диалоговое окно Безопасность (Security) (см. раздел ‘‘Безопасность макросов’’, далее в этой главе) .

Редактор Visual Basic (Visual Basic Editor). Открывает редактор Visual Basic .

40 Часть I Первые шаги Элементы управления (Control Toolbox). Отображает панель инструмен тов с элементами управления, которые можно добавить на рабочий лист .

Режим конструктора (Design Mode). Режим конструктора позволяет редактировать элементы управления, размещенные на рабочем листе .

Редактор сценариев (Microsoft Script Editor). Открывает редактор Web сценариев. Поскольку эта тема не имеет прямого отношения к VBA, она не будет рассматриваться в этой книге .

Панель инструментов Остановить запись (см. рис. 1.2), которая отобража ется на экране в результате щелчка на кнопке Записать макрос, содержит всего лишь 2 кнопки .

Остановить запись (Stop Recording). Останавливает текущий процесс записи макроса .

Относительная ссылка (Relative Reference). Указывает Excel на необ ходимость использования относительных ссылок вместо абсолютных .

Безопасность макросов После того как макросы VBA стали использоваться злоумышленниками для распространения вирусов, Microsoft разработала новую политику безопас ности, по умолчанию запрещающую выполнение макросов. Чтобы продол жить изучение материала, нам потребуется изменить стандартную политику .

Откройте диалоговое окно Безопасность (Security) (рис. 1.3), выбрав ко манду главного меню Excel Сервис Макрос Безопасность (Tools Macro Security). Microsoft различает 4 уровня безопасности: Очень высокая (Very High), Высокая (High) (используется по умолчанию), Средняя (Medium) и Низкая (Low). При выборе уровня безопасности Высокая запрещается выпол нение или редактирование всех неподписанных макросов. Чтобы начать созда ние собственных макросов, выберите уровень безопасности Средняя .

Уровень безопасности “Очень высокая” В соответствии с парадигмой безопасности Microsoft системный админи стратор создает высокозащищенный сетевой каталог (так называемую песочницу (sandbox)) и определяет его как доверенное размещение. Все макро сы, находящиеся в песочнице, считаются безопасными (их разрешается вы полнять), остальные макросы таят в себе потенциальную угрозу. Ключевым моментом этой парадигмы является предположение о невозможности ком прометации доверенного размещения .

–  –  –

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

При открытии рабочей книги все находящиеся в ней неподписанные макросы будут попросту отключены .

Рис. 1.3. Уровень безопасности Высокая выбран по умолчанию

Уровень безопасности “Средняя” На уровне безопасности Средняя (Medium) решение об отключении потенциально опасных макросов принимается пользователем. Именно этот уровень безопасности рекомендуется применять при разработке соб ственных макросов. Конечно же, необходимость включать макросы при каждом открытии рабочей книги может несколько раздражать. С другой стороны, это последняя возможность защититься от разрушительного ви руса, который таится в ничем не приметной рабочей книге, присланной вам по электронной почте .

Уровень безопасности “Низкая” На этом уровне безопасности защита от потенциально опасных макро сов отсутствует. Теперь уже ничто не защитит вас от вируса, хранящегося в рабочей книге. Применение уровня безопасности Низкая (Low) крайне не рекомендуется .

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

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

Чтобы начать запись макроса, выберите команду главного меню Excel Сервис Макрос Начать запись (Tools Macro Record New Macro) или щелкните на кнопке Записать макрос (Record Macro) панели инструментов Visual Basic. Перед тем как начать запись макроса, Excel отобразит диалоговое окно Запись макроса (Record Macro), показанное на рис. 1.4 .

Рис. 1.4. Задайте имя и сочетание клавиш для будущего макроса Диалоговое окно “Запись макроса” Введите имя макроса в поле Имя макроса (Macro name). В имени макроса не допускается использование пробела (таким образом, имя Макрос1 являет ся допустимым, а имя Макрос 1 — нет). Старайтесь давать макросам значи мые имена, например, КвартальныйОтчет. Имена наподобие Макрос1 яв ляются не слишком информативными .

Задайте сочетание клавиш с помощью одноименного поля. К примеру, если ввести в поле Сочетание клавиш (Shortcut key) букву ‘‘п’’, записанный макрос можно будет выполнить путем нажатия комбинации клавиш Ctrl+п .

С помощью раскрывающегося списка Сохранить в (Store macro in) выберите место хранения записываемого макроса: Личная книга макросов (Personal Macro Workbook), Новая книга (New Workbook) или Эта книга (This Workbook) .

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

Личная книга макросов (PERSONAL.XLS) создается при первом сохранении макроса в одноименном размещении. Это скрытая рабочая книга, которая загружается автоматически при каждом запуске Excel. Чтобы отобразить лич Excel и VBA — гремучая смесь Глава 1 ную книгу макросов на экране, выберите команду главного меню Excel Окно Отобразить (Window Unhide) .

Личная книга макросов подходит для хранения далеко не каждого макроса .

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

После выбора места хранения макроса щелкните на кнопке OK. Чтобы за кончить запись макроса, щелкните на кнопке Остановить запись (Stop Re cording) одноименной панели инструментов .

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

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

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

1. Щелкните на панели инструментов правой кнопкой мыши и выберите команду контекстного меню Настройка (Customize) .

2. Перейдите во вкладку Команды (Commands) (рис. 1.5) .

Рис. 1.5. Чтобы добавить кнопку выполнения макроса на панель инструментов Excel, восполь зуйтесь диалоговым окном Настройка 44 Часть I Первые шаги

3. Выберите категорию Макросы (Macros) .

4. Выберите команду Настраиваемая кнопка (Custom Button) (со значком улыбающейся рожицы) и перетащите ее на панель инструментов .

5. Щелкните на помещенном на панель инструментов значке с улыбаю щейся рожицей правой кнопкой мыши (не закрывайте диалоговое окно Настройка (Customize)) .

6. Выберите команду контекстного меню Назначить макрос (Assign Macro), выберите макрос и щелкните на кнопке OK .

7. Закройте диалоговое окно Настройка .

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

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

1. Отобразите панель инструментов Формы (Forms), выбрав команду главного меню Excel Вид Панели инструментов Формы (View Toolbars Forms) .

2. Щелкните на кнопке Кнопка (Button) .

3. Щелкните на рабочем листе левой кнопкой мыши и, удерживая ее (кнопку) нажатой, нарисуйте контур кнопки. Отпустите левую кнопку мыши .

4. Выберите требуемый макрос в диалоговом окне Назначить макрос объекту (Assign Macro) (рис. 1.6) и щелкните на кнопке OK .

–  –  –

Редактор Visual Basic На рис. 1.7 показано типичное окно редактора Visual Basic, которое состоит из трех основных частей. Не беспокойтесь, если ваше окно редактора Visual Basic отличается от показанного на рисунке. Более подробно редактор Visual Basic рассматривается в следующих разделах главы .

Рис. 1.7. Окно редактора Visual Basic

Параметры редактора Visual Basic Редактор Visual Basic имеет несколько настраиваемых параметров. Рас смотрим те из них, которые относятся непосредственно к написанию кода .

Настройка параметров редактора Visual Basic Чтобы настроить параметры редактора Visual Basic, выберите команду ме ню Tools Options (Сервис Параметры)1 и перейдите во вкладку Editor (Редактор). Из всех параметров, размещенных на этой вкладке, внимания за служивает только один — Require Variable Declaration (Требовать объявления переменной). По умолчанию Excel не требует объявлять переменные, что спо 1 Примерный перевод. Редактор Visual Basic не русифицирован. Прим. ред .

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

Использование цифровых подписей Если вам надоело постоянно подтверждать безопасность собственных мак росов, воспользуйтесь цифровой подписью, выбрав команду меню Tools Digital Signature (Сервис Цифровая подпись) .

Диспетчер проектов Диспетчер проектов содержит список всех открытых рабочих книг и загру женных дополнительных модулей. Щелкнув на значке ‘‘плюс’’ рядом с узлом VBAProject (Проект VBA), можно увидеть папки Microsoft Excel Objects (Объекты Microsoft Excel), Forms (Формы), Class Modules (Модули классов) и Modules (Модули) (присутствует по умолчанию). Каждая папка содержит один или несколько компонентов .

Чтобы просмотреть код компонента, щелкните на нем правой кнопкой мыши и выберите команду контекстного меню View Code (Просмотр кода) .

Такого же результата можно достичь путем двойного щелчка на названии компонента (за исключением форм, двойной щелчок на названии которых приводит к открытию формы в режиме конструктора) .

Чтобы отобразить окно диспетчера проектов, выберите команду меню Tools Project Explorer (Сервис Диспетчер проектов), нажмите ком бинацию клавиш Ctrl+R или щелкните на кнопке Project Explorer (Диспетчер проектов), расположенной на панели инструментов .

Окно диспетчера проектов показано на рис. 1.8. Чтобы добавить к проекту модуль, щелкните на названии проекта правой кнопкой мыши, выберите ко манду контекстного меню Insert (Вставить), а затем — тип добавляемого модуля .

Объекты Microsoft Excel По умолчанию проект состоит из модулей рабочих листов и модуля ЭтаКнига (ThisWorkbook). Код, имеющий непосредственное отношение к рабочему листу (например, код обработки событий листа), помещается в соответствую щий этому листу модуль. Модуль ЭтаКнига содержит код обработки событий рабочей книги. Об обработке событий речь идет в главе 8, ‘‘События’’ .

Формы Excel позволяет создавать формы для взаимодействия с пользователем .

О формах речь идет в главе 9, ‘‘Введение в пользовательские формы’’ .

Модули При записи макроса Excel автоматически создает модуль, куда помещает код макроса. Именно в таких модулях хранится большая часть создаваемого вами кода .

Excel и VBA — гремучая смесь Глава 1 Рис. 1.8. Диспетчер проектов содер жит список всех модулей проекта Модули классов Модули классов Excel предназначены для создания пользовательских объ ектов. Помимо этого, модули классов позволяют программистам обменивать ся фрагментами кода, не вдаваясь в подробности работы последнего. О моду лях классов речь идет в главе 20, ‘‘Создание пользовательских объектов, типов и коллекций’’ .

Окно свойств Окно свойств предназначено для редактирования параметров различных компонентов — рабочих листов, книг, модулей или элементов управления форм. Список параметров компонента зависит от его типа .

Чтобы открыть окно свойств, выберите команду меню View Properties Window (Вид Окно свойств), нажмите клавишу F4 или щелкните на кнопке Project Properties (Свойства проекта), расположенной на панели инструментов .

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

48 Часть I Первые шаги

–  –  –

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

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

1. Выберите команду главного меню Excel Файл Открыть (File Open) .

2. Отобразите содержимое папки, в которой хранится файл Счет.txt .

3. Выберите значение Все файлы (All Files) из раскрывающегося списка Тип файлов (Files of type) .

4. Выберите файл Счет.txt .

5. Щелкните на кнопке Открыть (Open) .

6. В группе Формат исходных данных (Original data type) диалогового окна Мастер текстов (импорт) — шаг 1 из 3 (Text Import Wizard — Step 1 of 3) устано вите переключатель С разделителями (Delimited) .

7. Щелкните на кнопке Далее (Next) .

8. В группе Символом-разделителем является (Delimiters) диалогового окна Мастер текстов (импорт) — шаг 2 из 3 (Text Import Wizard — Step 2 of 3) сбросьте флажок Знак табуляции (Tab) и установите флажок Запятая (Comma) .

9. Щелкните на кнопке Далее .

10.В группе Формат данных столбца (Column data format) диалогового окна Мастер текстов (импорт) — шаг 3 из 3 (Text Import Wizard — Step 3 of 3) уста новите переключатель Дата (Date) и выберите из раскрывающегося списка значение ДМГ (DMY) .

11. Щелкните на кнопке Готово (Finish) для импортирования файла .

12.Нажмите клавишу End, а затем — клавишу, чтобы переместиться на по следнюю строку импортированных данных .

Excel и VBA — гремучая смесь Глава 1

13.Нажмите клавишу, чтобы переместиться на итоговую строку .

14.Введите слово “Всего” .

15. Нажмите клавишу 4 раза, чтобы переместиться в столбец E итоговой строки .

16.Щелкните на кнопке Автосумма (AutoSum) и нажмите комбинацию клавиш Ctrl+Enter, чтобы суммировать значения столбца ПродуктВыручка, остава ясь при этом в той же ячейке .

17. Перетащите маркер заполнения по столбцам F и G, чтобы скопировать в них формулу суммирования .

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

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

20.Нажмите комбинацию клавиш Ctrl+A, чтобы выделить все ячейки рабочего листа .

21.Выберите команду Формат Столбец Автоподбор ширины (Format Column AutoFit Selection) .

Теперь вы готовы к записи своего первого макроса. Создайте пустую рабочую книгу и сохраните ее под каким нибудь описательным именем, например МакросИмпортаСчетов.xls. Щелкните в панели инструментов Visual Basic на кнопке Записать макрос (Record Macro) или выберите команду меню Сервис Макрос Начать запись (Tools Macro Record New Macro) .

Измените предлагаемое по умолчанию имя макроса Макрос1 на более инфор мативное, например ИмпортСчета. Убедитесь, что макрос будет сохранен в раз мещении Эта книга (This Workbook) и задайте сочетание клавиш для выполнения макроса, к примеру Ctrl+и. По умолчанию в поле Описание (Description) зано сится ваше имя и дата создания макроса. Добавьте сюда текст, кратко описываю щий предназначение макроса (рис. 1.10), и щелкните на кнопке OK .

–  –  –

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

Внимание Сопротивляйтесь желанию убрать из виду панель инструментов Остановить запись (Stop Recording). Если она будет вам мешать, перетащите ее в безо пасное место (действие по перетаскиванию панели Остановить запись не вклю чается в записываемый макрос). Если вы все же закроете панель инструментов Остановить запись, то для того чтобы завершить запись макроса, вам потребует ся выбрать команду меню Сервис Макрос Остановить запись (Tools Macro Stop Recording) .

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

Внимание Закрытие панели инструментов Остановить запись не приводит к остановке записи макроса. Этим вы только усложните себе жизнь, так как теперь для завершения записи макроса вам потребуется выбрать команду меню Сервис Макрос Остановить запись .

Пришло время взглянуть на сгенерированный код макроса. Для этого от кройте окно редактора Visual Basic, выбрав команду меню Сервис Макрос Редактор Visual Basic (Tools Macro Visual Basic Editor) или воспользовав шись комбинацией клавиш Alt+F11 .

Изучение кода макроса Рассмотрим код, сгенерированный Excel в результате записи макроса. От кройте редактор Visual Basic, воспользовавшись комбинацией клавиш Alt+F11. Щелкните на названии модуля Module1 проекта МакросИмпортаСчетов.xls правой кнопкой мыши и выберите команду контекстного ме ню View Code (Просмотр кода). Строки кода, начинающиеся со знака апост рофа, являются комментариями и игнорируются Excel. Комментарии созда ются на основе информации, введенной в окне Запись макроса (Record Macro) (сюда, в частности, относится сочетание клавиш, использующееся для вызова макроса) .

Excel и VBA — гремучая смесь Глава 1 Внимание Комментарий не определяет сочетание клавиш. Другими словами, изменив в комментарии сочетание клавиш Ctrl+и на Ctrl+с, вы ничего не добьетесь .

Изменить сочетание клавиш можно только с помощью диалогового окна Макрос (Macro) .

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

Рис. 1.11. Сгенерированный код макроса выглядит очень аккуратно

Приведенные ниже 8 строк кода представляют собой 1 строку, разбитую на несколько фрагментов для удобочитаемости .

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), _ 52 Часть I Первые шаги Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _ TrailingMinusNumbers:=True Учитывая сказанное выше, средство записи макросов превратило 21 шаговую процедуру создания отчета в 14 строк кода. Весьма неплохо!

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

А теперь протестируем созданный макрос. Вернитесь к интерфейсу Excel, воспользовавшись комбинацией клавиш Alt+F11. Закройте файл Счет.txt, не сохранив внесенных в него изменений. При этом у вас должна остаться от крытой рабочая книга МакросИмпортаСчетов.xls .

Выполните сохраненный макрос, нажав комбинацию клавиш Ctrl+и .

Он должен сработать безукоризненно (рис. 1.12) .

Рис. 1.12. Создание отчета прошло без сучка и задоринки

Непредвиденные результаты Предположим, что утром следующего дня вы получили по электронной почте новый файл Счет.txt. Запустив созданный накануне макрос с помо щью сочетания клавиш Ctrl+и, вы были неприятно удивлены. Файл Счет.txt от 5 июня содержал сведения о 12 ти счетах, а файл Счет.txt от 6 июня — о 16 ти. Тем не менее, макрос поместил итоговую информацию в 14 ю строку, тем самым в точности воспроизведя действия, выполненные при его записи (рис. 1.13) .

–  –  –

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

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

Абсолютные ссылки основаны на действительных адресах ячеек, например A1. Относительные ссылки основаны на позиции ячейки относительно другой ячейки. Например, ссылка R[16]C[-1] указывает на ячейку, которая нахо дится на 16 строк ниже и на 1 столбец левее текущей ячейки .

Практикум Запишем тот же макрос с использованием относительных ссылок. Закройте файл Счет.txt без сохранения изменений. В рабочей книге МакросИмпортаСчетов.xls создайте новый макрос, выбрав команду меню Сервис Макрос Начать запись (Tools Macro Record New Macro). Присвойте новому макросу имя ИмпортСчетаОтносительно и назначьте другое сочетание клавиш, например Ctrl+т (рис. 1.14) .

Импортируйте данные из файла Счет.txt. Прежде чем переходить к последней строке данных с помощью последовательного нажатия клавиш End и, щелкните на кнопке Относительная ссылка (Relative Reference) в панели инстру ментов Остановить запись (Stop Recording) (см. рис. 1.2) .

Выполните следующие действия .

1. Нажмите клавишу End, а затем — клавишу, чтобы переместиться на по следнюю строку импортированных данных .

54 Часть I Первые шаги

2. Нажмите клавишу, чтобы переместиться на итоговую строку .

–  –  –

3. Введите слово “Всего” .

4. Нажмите клавишу 4 раза, чтобы переместиться в столбец E итоговой строки .

5. Щелкните на кнопке Автосумма (AutoSum) и нажмите комбинацию клавиш Ctrl+Enter, чтобы суммировать значения столбца ПродуктВыручка, остава ясь при этом в той же ячейке .

6. Перетащите маркер заполнения по столбцам F и G, чтобы скопировать в них формулу суммирования .

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

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

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

9. Нажмите комбинацию клавиш Ctrl+A, чтобы выделить все ячейки рабочего листа .

10.Выберите команду Формат Столбец Автоподбор ширины (Format Column AutoFit Selection) .

11. Остановите запись макроса .

Нажмите комбинацию клавиш Alt+F11, чтобы вернуться в окно редактора Visual Basic и просмотреть полученный на этот раз код. Текст макроса ИмпортСчетаОтносительно будет помещен в модуль Module1 сразу же после текста макроса ИмпортСчета .

Excel и VBA — гремучая смесь Глава 1 Внимание Если между созданием первого и второго макроса вы завершали работу с Ex cel, новый макрос будет помещен в модуль Module2 .

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

Рис. 1.15. Код макроса, записанного с использованием режима относительных ссылок

Чтобы протестировать макрос, закройте файл Счет.txt без сохранения изменений и нажмите комбинацию клавиш Ctrl+т. На этот раз работа мак роса не должна вызывать каких либо нареканий .

Предположим, что файл Счет.txt от 7 го июня содержит сведения о 21 счете (рис. 1.16) .

Откройте рабочую книгу МакросИмпортаСчетов.xls и выполните но вый макрос, нажав комбинацию клавиш Ctrl+т. На первый взгляд макрос справился с поставленной перед ним задачей. Но взгляните на рис. 1.17 — не кажется ли вам, что здесь что то не так?

Передав подобный отчет менеджеру, вы, несомненно, навредили бы своей репутации. Присмотритесь к ячейке E23. В левом верхнем углу ячейки нахо 56 Часть I Первые шаги дится маленький зеленый треугольник — верный признак ошибки. Следует отметить, что возможность предупреждения ошибок появилась благодаря смарт тегам — средству, недоступному в Excel 95 или Excel 97 .

Рис. 1.16. Сможет ли новый макрос справиться с этими данными?

Рис. 1.17. Результат выполнения макроса, использующего относительные ссылки

–  –  –

ко с 7 по 22 строку. К сожалению, логику функции автоматического суммирова ния не может воспроизвести ни один автоматически созданный макрос .

Если же файл Счет.txt от 7 го июня содержит сведения о меньшем коли честве счетов, чем 6 го июня, Excel ‘‘наградит’’ вас аналогичной формулой =СУММ(E10:E65531) (=SUM(E10:E65531)) и сообщением о наличии цик лических ссылок (рис. 1.18) .

Рис. 1.18. Результат выполнения макроса, использующего относительные ссылки, при меньшем количестве счетов Отчаяние Дочитав книгу до этого места, вы, вероятно, уже проклинаете Microsoft .

Представьте себе мое состояние после нескольких дней безуспешных попыток написать хотя бы один работающий макрос. Ситуацию усугубляло знание то го, что подобные макросы без проблем генерировались средством записи мак росов Lotus 1 2 3, созданным в далеком 1983 году. То, что получилось у Мича Кейпора (Mitch Kapor) 21 год назад, Microsoft не может повторить до сих пор .

Известно ли вам, что все ранние версии Excel вплоть до Excel 97 поддержи вали выполнение макросов командной строки Lotus? Этот факт стал известен мне только после того, как Microsoft объявила об окончании поддержки Excel 97. Многие компании, перешедшие на Excel XP (который уже не под держивал выполнение макросов Lotus 1 2 3), обратились к нам с просьбой пе реписать старые макросы Lotus на Excel VBA. Я не могу смириться с мыслью, что начиная с Excel 5, Excel 95 и Excel 97 интерпретатор Microsoft мог выпол нить макрос, корректно решавший поставленную нами задачу, однако средст во записи макросов было не в состоянии его создать .

Следующий шаг Единственно правильное решение рассмотренной задачи заключается в применении языка программирования Visual Basic. Первым приближением 58 Часть I Первые шаги к цели можно считать автоматически сгенерированный макрос. Немного здравого смысла, и он станет реальным подспорьем в решении повседневных задач. В главе 2, ‘‘Знакомство с Visual Basic for Applications” мы попробуем применить этот подход к двум записанным нами макросам. Научившись ‘‘читать’’ код VBA, вы с легкостью сможете подправить автоматически сгене рированный код и даже написать макрос ‘‘с нуля’’ .

Глава 2 Visual Basic for Applications

–  –  –

Учимся понимать “речь” VBA Попробуем сыграть в футбол на языке BASIC.

Команда ‘‘ударить по мячу’’ будет выглядеть примерно следующим образом:

Kick the Ball Именно так мы и говорим в повседневной жизни. Глагол ‘‘ударить’’ (kick) следует перед существительным ‘‘мяч’’ (the ball). Аналогично, в приведенном выше примере глагол Print следует перед существительным * (звездочка) .

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

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

Ball.Kick В VBA существительное (объект) записывается перед глаголом (методом) .

Базовая структура большинства строк VBA выглядит так:

Объект.Метод К сожалению, это не очень похоже на повседневную речь. Никто не гово рит ‘‘Вода.Пить’’, ‘‘Мяч.Ударить’’ или ‘‘Девушка.Целовать’’. Именно поэтому VBA кажется очень сложным по сравнению с процедурными языками про граммирования .

Продолжим аналогию. Представьте, что вы стоите на зеленом газоне перед тремя мячами: футбольным, баскетбольным и бейсбольным. Как сказать на VBA ‘‘ударить футбольный мяч’’ члену школьной футбольной команды?

Выше была приведена команда ‘‘ударить по мячу’’ (Ball.Kick), однако в данном случае этого недостаточно. Возможно, ребенок ударит мяч, который находится ближе всех к нему (например, бейсбольный) .

В VBA практически для каждого объекта (существительного) определяется коллекция этих объектов. Рассмотрим электронную таблицу Excel. Строке со Знакомство с Visual Basic for Applications Глава 2 ответствует набор строк, столбцу набор столбцов, рабочему листу набор рабочих листов.

С точки зрения синтаксиса имя коллекции объектов состав ляется из имени объекта и суффикса ‘‘s’’, например:

Row Rows, Cell Cells, Ball Balls .

Существует несколько способов обращения к элементу коллекции.

Пер вый из них состоит в использовании порядкового номера элемента, например:

Balls(2).Kick Несмотря на то что приведенная выше запись вполне корректна, пере упорядочивание мячей в коллекции может привести к весьма плачевному результату .

Второй способ обращения к элементу коллекции является более безопас ным и состоит в использовании имени элемента, например:

Balls("Soccer").Kick Теперь можно быть уверенным, что ребенок ударит именно по футбольно му мячу .

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

Ниже приве дена команда ‘‘сильно ударить футбольный мяч так, чтобы он полетел влево’’:

Balls("Soccer").Kick Direction:=Left, Force:=Hard Комбинации двоеточия и знака равенства в коде VBA всегда указывают на параметр метода .

Методы могут иметь много параметров, как обязательных, так и нет. Пред положим, что у метода Kick есть параметр Elevation (‘‘поднятие’’).

Ниже приведена команда ‘‘сильно ударить футбольный мяч так, чтобы он полетел высоко влево’’:

Balls("Soccer").Kick Direction:=Left, Force:=Hard, Elevation:=High Для каждого метода существует определенный порядок следования его пара метров. Некоторые программисты пропускают имена параметров, указывая толь ко их значения.

Следующая строка кода полностью эквивалентна предыдущей:

Balls("Soccer").Kick Left, Hard, High Практика пропуска имен параметров не вносит ясности в код, так как не зная точного порядка следования параметров, сложно судить о предназначе нии той или иной строки. Значения параметров Left, Hard и High сами по себе информативны, однако так бывает далеко не всегда.

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

WordArt.Add Left:=10, Top:=20, Width:=100, Height:=200

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

WordArt.Add 10, 20, 100, 200 Несмотря на то что приведенная выше строка кода вполне корректна, от сутствие имен параметров серьезно затрудняет восприятие ее смысла. Точный 62 Часть I Первые шаги порядок следования параметров метода можно узнать, обратившись к разделу справочной системы, посвященному этому методу .

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

Ниже приве дены две эквивалентных строки кода, соответствующих команде ‘‘ударить футбольный мяч так, чтобы он полетел высоко влево’’ (не важно, насколько сильным будет сам удар):

Balls("Soccer").Kick Direction:=Left, Elevation:=High Balls("Soccer").Kick Left, Elevation:=High Указав имя одного параметра, следует указать также имена всех парамет ров, которые последуют за ним в этой строке кода .

Некоторые методы не имеют параметров.

Ниже приведен код, имитирую щий нажатие клавиши F9:

Application.Calculate Другие методы выполняют действие и возвращают его результат.

Ниже приведен код, добавляющий рабочий лист:

Worksheet.Add Before:=Worksheets(1) Поскольку метод Worksheet.Add создает новый объект, результат его выполнения может быть присвоен переменной (параметры метода при этом следует взять в скобки):

Set MyWorksheet = Worksheet.Add (Before:=Worksheets(1)) Напоследок рассмотрим еще одну важную составляющую языка VBA — свойства. Свойства описывают объект наподобие того, как прилагательное описывает существительное .

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

Изменение цвета ячейки на желтый описывается следующей строкой кода:

ActiveCell.Interior.ColorIndex = 6 Обратите внимание, что в приведенном выше коде используется конструк ция Объект.Свойство, похожая на уже рассмотренную нами конструкцию Объект.Метод. На первый взгляд, их невозможно отличить друг от друга. Если же присмотреться повнимательнее, то можно заметить отсутствие двоеточия перед знаком равенства в строке с конструкцией Объект.Свойство. Обычно свойство всегда присутствует в левой или правой части выражений, связанных с присвоением значения .

Ниже приведена команда, изменяющая цвет текущей ячейки на цвет ячейки A1:

ActiveCell.Interior.ColorIndex = Range("A1").Interior.ColorIndex Итак, изменение значения свойства Interior.ColorIndex приводит к изменению цвета ячейки. Сравнивая свойство с прилагательным, получаем Знакомство с Visual Basic for Applications Глава 2 достаточно странный результат изменение прилагательного влечет за собой выполнение действия .

В табл. 2.1 приведен краткий ‘‘словарь’’ терминов VBA .

–  –  –

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

Спасительная клавиша F1 Приступая к написанию макросов, обязательно убедитесь в наличии на вашем компьютере справочной системы VBA. К сожалению, она не входит в стандартную установку Microsoft Office. Чтобы проверить наличие справочной системы VBA, выполните следующие действия .

1. Запустите Excel и откройте окно редактора Visual Basic, воспользовав шись комбинацией клавиш Alt+F11. Выберите команду меню Insert Module (Вставить Модуль) (рис. 2.1) .

2. Введите 3 строки кода, как показано на рис. 2.2, и установите курсор посредине слова MsgBox .

3. Нажмите клавишу F1. Если справочная система VBA установлена, откроется окно, показанное на рис. 2.3 .

64 Часть I Первые шаги

–  –  –

Если справочная система VBA не установлена, Excel выдаст сообщение об ошибке. Установите справочную систему VBA, воспользовавшись установоч ными компакт дисками Microsoft Office (при необходимости обратитесь за помощью к системному администратору) .

Знакомство с Visual Basic for Applications Глава 2 Просмотр разделов справочной системы Раздел справочной системы, посвященный тому или иному методу, содер жит подробное описание всех его параметров. Под именем метода или функ ции расположены три ссылки: See Also (См. также), Example (Пример) и Specifics (Особенности). Одной из наиболее полезных является ссылка Example, ведущая на страницу с примером использования метода или функ ции (рис. 2.4) .

Рис. 2.4. Большинство разделов справочной системы VBA со держат ссылку на страницу с примерами Код примера можно выделить (рис. 2.5), скопировать в буфер обмена с по мощью комбинации клавиш Ctrl+C, а затем вставить в модуль с помощью комбинации клавиш Ctrl+V .

Код записанных макросов наверняка содержит много незнакомых объек тов и методов. Установите курсор посредине интересующего вас ключевого 66 Часть I Первые шаги слова и нажмите клавишу F1, чтобы отобразить соответствующий раздел справочной системы VBA .

–  –  –

Изучение кода записанного макроса Рассмотрим код первого макроса, записанного в главе 1, “Excel и VBA — гремучая смесь”, и попытаемся понять его смысл в контексте объектов, свойств и методов (рис. 2.6) .

Согласно концепции Объект.Метод (или, что то же самое, Существитель ное.Глагол) в 1 й строке кода Workbooks является объектом, а OpenText — методом. Установите курсор внутри слова OpenText и нажмите клавишу F1, чтобы открыть раздел справочной системы VBA, посвященный этому методу (рис. 2.7) .

В справочной системе указано, что OpenText — это метод. Его парамет ры перечислены в стандартном порядке следования в области, выделенной серым цветом. Обратите внимание, что метод OpenText имеет всего лишь один обязательный аргумент FileName. Все остальные параметры могут быть пропущены .

Знакомство с Visual Basic for Applications Глава 2 Рис. 2.6. Код записанного макроса Рис. 2.7. Раздел справочной системы, посвященный методу OpenText. Ссылка Applies To (Применяется к) позволяет просмотреть список объектов, к которым может быть применен этот метод 68 Часть I Первые шаги Необязательные параметры В справочной системе VBA можно найти информацию о стандартных зна чениях необязательных параметров. К примеру, стандартным значением пара метра StartRow является 1, что весьма приемлемо. А вот пропустив параметр Origin, вы рискуете попасть впросак. Дело в том, что по умолчанию Excel ис пользует текущее значение этого параметра. Другими словами, если вы выпол ните свой макрос после того, как кто то импортирует в Excel файл с китайскими иероглифами, Excel предположит, что вы хотите сделать то же самое .

Предопределенные константы Согласно разделу справочной системы VBA, посвященному методу OpenText (см. рис. 2.7), DataType — это свойство, которое может иметь значение xlDelimited или xlFixedWidth (предопределенные константы Excel VBA типа XlTextParsingType). В редакторе Visual Basic нажмите комбинацию клавиш Ctrl+G, чтобы открыть окно Immediate (Быстрое выполнение) .

В окне Immediate введите следующую строку и нажмите клавишу Enter:

Print xlFixedWidth Как показано на рис. 2.8, значением константы xlFixedWidth является 2 .

Аналогичным образом можно узнать значение константы xlDelimited, которое равно 1. Использование предопределенных констант с информа тивными именами вместо чисел значительно повышает удобочитаемость программного кода .

Рис. 2.8. Воспользуйтесь окном Immediate, чтобы узнать значения предопределенных констант VBA, таких как xlFixedWidth В большинстве случаев раздел справочной системы либо содержит допус тимые константы непосредственно в тексте справки, либо предлагает ссылку, щелчок на которой приводит к их отображению (рис. 2.9) .

К справочной системе VBA можно предъявить только одну претензию она не позволяет узнать, является ли конкретный параметр нововведением те кущей версии Excel. К примеру, параметр TrailingMinusNumbers был впервые представлен в Excel 2002. Попытка выполнения макроса, содержа щего этот параметр, в Excel 2000 завершится весьма плачевно. К сожалению, эта проблема достаточно серьезна, поскольку решить ее можно только мето дом проб и ошибок .

Знакомство с Visual Basic for Applications Глава 2

Рис. 2.9. Щелкните на ссылке, чтобы увидеть все допустимые константы

Изучив раздел справочной системы, посвященный методу OpenText, можно заметить, что этот метод является в некотором смысле эквивалентом мастера импорта текстов. Так, на первом шаге мастера необходимо выбрать С разделителями (Delimited) или Фиксированной формат исходных данных ширины (Fixed width), а также формат файла и строку, с которой необходимо начать импорт (рис. 2.10) .

Другими словами, первый шаг мастера импорта текстов можно описать тремя параметрами метода OpenText:

Origin:=1251 StartRow:=1 DataType:=xlDelimited 70 Часть I Первые шаги Рис. 2.10. Первый шаг мастера импорта текстов описывается тремя па раметрами метода OpenText На втором шаге мастера импорта текстов производится выбор разделителя для текстовых данных. Чтобы Excel не считал две последовательные запятые одной, флажок Считать последовательные разделители одним (Treat con secutive delimiters as one) снят. Поля, содержащие запятую как часть данных (например, ‘‘XYZ, Inc.’’), должны быть ограничены символом, выбранным в раскрывающемся списке Ограничитель строк (Text qualifier) (рис. 2.11) .

–  –  –

TextQualifier:=xlDoubleQuote ConsecutiveDelimiter:=False Tab:=False Semicolon:=False Comma:=True Space:=False Other:=False На третьем шаге мастера импорта текстов определяется формат столбцов данных. В рассмотренном примере мы оставили стандартный формат Общий (General) для всех столбцов, кроме первого, для которого был выбран формат даты ДМГ (DMY) (рис. 2.12) .

Рис. 2.12. Третий шаг мастера импорта текстов описывается всего лишь одним параметром метода OpenText Третий шаг мастера импорта текстов полностью описывается параметром FieldInfo метода OpenText .

Щелкнув на кнопке Подробнее (Advanced) диалогового окна Мастер текстов (импорт) — шаг 3 из 3 (Text Import Wizard — Step 3 of 3), можно вы брать разделитель целой и дробной части, разделитель разрядов, а также ука зать на необходимость отображения знака ‘‘минус’’ в конце отрицательных чисел (рис. 2.13) .

Следует отметить, что средство записи макросов не генерирует код для па раметров DecimalSeparator и ThousandsSeparator до тех пор, пока не будет выбран отличный от стандартного разделитель целой и дробной части и разделитель разрядов, соответственно. В то же время, средство записи макро сов всегда генерирует код для параметра TrailingMinusNumbers .

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

72 Часть I Первые шаги

–  –  –

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

Selection.End(xlDown).Select Щелкните на слове End и нажмите клавишу F1. На экране появится диалоговое окне Context Help (Контекстная справка), предлагающее выбрать один из двух разделов справочной системы, посвященный слову End. Один из них находится в библиотеке Excel, а другой в библиотеке VBA (рис. 2.14) .

Рис. 2.14. Иногда одному ключевому слову соответст вует несколько разделов справочной системы Чтобы не гадать, какой из двух разделов справочной системы вам нужен, щелкните на кнопке Help (Справка). Как показано на рис. 2.15, раздел спра вочной системы из библиотеки VBA содержит сведения о выражении End. Это не то, что нам нужно .

Закройте окно справочной системы, снова нажмите клавишу F1 и выбе рите раздел, посвященный слову End, из библиотеки Excel. Свойство End воз вращает объект Range, что эквивалентно последовательному нажатию кла виш End и или End и в пользовательском интерфейсе Excel .

Щелкнув на ссылке XlDirection, можно увидеть список параметров, допус тимых для передачи функции End (рис. 2.16) .

Знакомство с Visual Basic for Applications Глава 2 Рис. 2.15. Поиск нужного раздела справочной системы можно проводить методом проб и ошибок Возврат объектов свойством Ранее неоднократно упоминалось, что базовый синтаксис языка VBA пред ставлен конструкцией Объект.Метод. В рассмотренной выше строке кода ме тодом, очевидно, является метод.Select. Несмотря на то, что End — это свойство, оно возвращает объект Range, а метод, таким образом, применяется непосредственно к свойству .

Открыв раздел справочной системы, посвященный слову Selection, можно обнаружить, что это также свойство, а не объект. Полное обращение к свойству Selection выглядит как Application.Selection, однако в кон тексте использования объектной модели Excel префикс Application можно опустить. Если бы данный макрос выполнялся в текстовом редакторе Word, нам обязательно потребовалось бы указать перед свойством.Selection пе ременную объекта для идентификации вызываемого приложения .

74 Часть I Первые шаги Рис. 2.16. Нужный раздел справочной системы, посвященный свойству End Тип возвращаемого свойством Application.Selection объекта зависит от текущего выделенного элемента. Если это ячейка, свойство Application.Selection возвращает объект Range .

Использование отладчика кода Редактор Visual Basic содержит великолепный отладчик, предназначенный для поиска и устранения недостатков программного кода .

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

К счастью, отладчик Excel поддерживает пошаговое выполнение кода .

Разместите курсор посредине имени процедуры ИмпортСчета и выберите команду меню Debug Step Into (Отладка Пошаговое выполнение) (или на жмите клавишу F8) (рис. 2.17) .

Сейчас редактор Visual Basic находится в режиме пошагового выполне ния кода. Строка, которая будет выполнена следующей, выделена желтым цветом. Кроме того, на нее указывает желтая стрелка, расположенная сле ва (рис. 2.18) .

Знакомство с Visual Basic for Applications Глава 2

–  –  –

Рис. 2.18. Отладчик готов выполнить первую строку кода макроса Выполнение строки Sub ИмпортСчета() приводит к входу в процедуру ИмпортСчета(). Нажмите клавишу F8, чтобы выполнить эту строку и пе рейти к следующей. Редактор Visual Basic выделит желтым цветом фрагмент кода, соответствующий методу OpenText. Нажмите клавишу F8. После выполнения метода OpenText переключитесь в Excel с помощью комбина ции клавиш Alt+Tab и убедитесь в успешном импорте файла Счет.txt .

Обратите внимание, что текущей выделенной ячейкой является ячейка A1 (рис. 2.19) .

76 Часть I Первые шаги

Рис. 2.19. Файл Счет.txt успешно импортирован в Excel

Переключитесь в редактор Visual Basic, воспользовавшись комбинацией клавиш Alt+Tab. Нажмите клавишу F8, чтобы выполнить строку кода макроса Selection.End(xlDown).Select. Переключившись в Excel, можно увидеть, что теперь текущей выделенной ячейкой является ячейка A10 (рис. 2.20) .

–  –  –

Переключившись в редактор Visual Basic, нажмите клавишу F8, чтобы выполнить команду Range("A14").Select. Вместо того чтобы выделить ячейку в первой свободной строке после импортированных данных (A11), макрос выделил ячейку A14, как показано на рис. 2.21 .

–  –  –

Обнаружив проблемный участок кода, остановите выполнение макроса, выбрав команду меню Run Reset (Выполнить Сброс) или щелкнув на кнопке панели инструментов Reset (Сброс) (рис. 2.22). Вернитесь в Excel и отмените все действия, которые успел выполнить макрос. В данном случае за кройте файл Счет.txt без сохранения изменений .

Рис. 2.22. Щелчок на кнопке Reset приводит к остановке выполнения макроса

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

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

Рис. 2.23. Красно коричневый маркер слева от строки кода свидетельствует о наличии точки прерывания Выберите команду Run Run Sub/UserForm (Выполнить Выполнить под программу/Пользовательскую форму) или нажмите клавишу F5. Выполне ние макроса остановится на границе точки прерывания, а соответствующая 78 Часть I Первые шаги строка кода будет выделена желтым цветом. Нажмите клавишу F8, чтобы продолжить выполнение макроса в пошаговом режиме (рис. 2.24) .

Рис. 2.24. Строка кода, на которой установлена точка прерывания, выделена желтым цветом Завершив отладку кода, следует удалить все точки прерывания. Чтобы уда лить точку прерывания, щелкните на соответствующей ей точке на полосе слева от строки кода. Чтобы удалить все точки прерывания в проекте, выбери те команду меню Debug Clear All Breakpoints (Отладка Удалить все точки прерывания) или воспользуйтесь комбинацией клавиш Ctrl+Shift+F9 .

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

При подведении указателя мыши к стрелке он меняет свою форму, как показано на рис. 2.25. Перетащите желтую стрелку на строку кода, которая должна быть выполнена следующей, или разместите на этой строке курсор и выберите ко манду меню Debug Set Next Statement (Отладка Выполнить следующей) .

–  –  –

Выполнение фрагмента кода Иногда возникает необходимость в выполнении целого фрагмента кода, на пример, цикла. Вместо того чтобы возвращаться к одним и тем же строкам не сколько раз подряд, можно указать отладчику на необходимость выполнения всего участка кода до указанной вами строки. Для этого разместите курсор на требуемой строке и воспользуйтесь комбинацией клавиш Ctrl+F8 или коман дой меню Debug Run To Cursor (Отладка Выполнить до указанной строки) .

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

Окно Immediate Чтобы открыть окно Immediate (Быстрое выполнение) в редакторе Visual Basic, нажмите комбинацию клавиш Ctrl+G. На рис. 2.26 приведен пример вычисления различных выражений, таких как адрес текущей выделенной ячейки, ее значение, а также имя активного рабочего листа .

Окно Immediate обычно располагается под окном просмотра программ ного кода. Размер окна Immediate можно изменить, воспользовавшись марке ром изменения размера окна (рис. 2.27) .

Рис. 2.26. Пауза после выполнения каждой Рис. 2.27. Изменение размера окна строки кода позволяет узнать текущие значе Immediate ния переменных или выражений Если содержимое окна Immediate не умещается на экране, его можно про смотреть с помощью полосы прокрутки, расположенной в правой части окна .

Выражение, значение которого необходимо вычислить с помощью окна Immediate, не обязательно набирать каждый раз заново. К примеру, вычислим значение выражения Selection.Address после выполнения нескольких строк кода макроса (рис. 2.28) .

Нажмите клавишу F8, чтобы выполнить следующую строку кода. Вме сто повторного ввода выражения, установите курсор в конец содержащей это выражение строки (рис. 2.29) .

80 Часть I Первые шаги

–  –  –

Чтобы повторно вычислить результат выражения, нажмите клавишу Enter .

Новый результат (в данном случае $1:$1) ‘‘сдвинет’’ старый ($E$14:$G$14) на одну строку вниз (рис. 2.30) .

Нажмите клавишу F8 четыре раза, чтобы выполнить строку Cells.Select. Снова расположите курсор в конце строки Print Selection.Address в окне Immediate и нажмите клавишу Enter. Новый ре зультат выражения Selection.Address сдвинет на одну строку вниз два предыдущих (рис. 2.31) .

–  –  –

Выражение, указанное в окне Immediate, можно изменить. Установите курсор справа от слова Address и удалите его с помощью клавиши Backspace. Введите выражение Rows.Count и нажмите клавишу Enter. В окне Immediate появится значение, равное числу выделенных строк (рис. 2.32) .

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

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

Рис. 2.32. Измените выражение, ука занное в окне Immediate, и нажмите клавишу Enter Вычисление значения с помощью указателя мыши Чтобы узнать значение выражения, подведите к нему указатель мыши и за держите в таком положении пару секунд. На экране появится подсказка, со держащая текущее значение выражения. Как правило, этот прием оказывает ся наиболее полезным при отладке циклов (см. главу 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) .

–  –  –

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

Рис. 2.37. Установите переключатель Break When Value Changes

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

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

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

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

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

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

Рис. 2.40. Чтобы открыть окно диспетчера объектов, нажмите клавишу F2 Диспетчер объектов позволяет просматривать библиотеку объектов 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) .

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

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

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

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

Rows("1:1").Select Selection.Font.Bold = True

Оптимизированный код:

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

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

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

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

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

–  –  –

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

В контексте пользовательского интерфейса 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 .

Переменные можно использовать и при построении формулы.

К примеру, приведенная ниже формула суммирует все значения, начиная с ячейки E2 и заканчивая ячейкой, находящейся на пересечении последней строки данных и столбца 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 = 12 Selection.Font.ColorIndex = 5 Selection.Font.Underline = xlUnderlineStyleDoubleAccounting При выполнении этого кода макрос должен 4 раза подряд вычислить зна чение выражения Selection.Font. Поскольку каждый раз обращение про исходит к одному и тому же объекту, его имя рекомендуется указать в начале блока With.

Чтобы сослаться на объект внутри блока With, соответствующие строки кода необходимо предварить символом точки, как показано ниже:

With Range("A14:G14").Font.Bold = True.Size = 12.ColorIndex = 5.Underline = xlUnderlineStyleDoubleAccounting End With Исправление и оптимизация автоматически сгенерированного кода

–  –  –

Изменение автоматически сгенерированного кода Используя приведенные выше советы, превратим автоматически сгенерированный код макроса ИмпортСчета (см. ниже) в эффективный и профессиональный код .

Sub ИмпортСчета() ' ' ИмпортСчета Макрос ' Макрос записан 03.01.2005 (Александр Журавлев) Знакомство с Visual Basic for Applications Глава 2 ' ' Сочетание клавиш: 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 + 1

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 средство записи макросов выделяет все ячей ки рабочего листа:

Cells.Select 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 ' Найти последнюю строку с данными Знакомство с Visual Basic for Applications Глава 2 FinalRow = Range("A65536").End(xlUp).Row TotalRow = FinalRow + 1 ' Создание итоговой строки 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, использование справочной системы, средств отладки, а также несколько советов по исправлению и оптимизации автома тически сгенерированного кода .

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

Глава 3

–  –  –

вом записи макросов. Приведенные ниже строки кода полностью эквива лентны:

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

Обращение к диапазону ячеек с помощью указания адреса его верхнего левого и нижнего правого угла Существует два различных синтаксиса команды 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 Работа с диапазоном ячеек Глава 3 Обратите внимание, что имя диапазона ячеек взято в кавычки. Это отличи тельная особенность использования именованных диапазонов в VBA. Без ка вычек Excel воспримет имя диапазона ячеек как объявленную в макросе пере менную. Единственное исключение касается сокращенной формы обращения к диапазону ячеек, которая не предусматривает заключение имени диапазона в кавычки .

–  –  –

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

Чтобы избежать этого, можно обратиться непо средственно к объекту 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"))) End With Обращение к диапазону ячеек с помощью указания его относительного адреса Обычно объект 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 столбца вправо .

Работа с диапазоном ячеек Глава 3 Обращение к диапазону ячеек с помощью свойства 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 Результат выполнения макроса показан на рис. 3.1 .

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

Range("A1:C3").Offset(1, 1)

–  –  –

Изменение размера диапазона ячеек с помощью свойства 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 = 15

Рис. 3.3. Изменение размера диапазона ячеек в действии

Здесь свойство Offset используется для изменения активной ячейки, а свойство Resize — для увеличения размера диапазона до двух столбцов .

Точно так же можно изменить и размер диапазона, состоящего из несколь ких ячеек.

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

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

–  –  –

Внимание Для корректного использования некоторых свойств объектов 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 = 6 104 Часть I Первые шаги Рис. 3.4. Метод Intersect возвращает диапазон ячеек, по лученный в результате пересечения нескольких диапазонов Проверка пустых ячеек с помощью функции IsEmpty Функция IsEmpty возвращает булево значение, определяющее, является ячейка пустой (True) или нет (False). Ячейка является пустой, если она не содержит каких либо данных (даже символов пробела) .

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

–  –  –

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

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

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

3.7):

Range("A1").CurrentRegion.Select

–  –  –

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

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

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

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

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

Этот метод позволяет работать с ячейка ми, соответствующими определенному критерию:

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

Тип ячейки может быть описан одной из констант xlCellType:

xlCellTypeAllFormatConditions xlCellTypeAllValidation xlCellTypeBlanks xlCellTypeComments Глава 3 107 Работа с диапазоном ячеек 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 End Sub

–  –  –

В приведенном выше коде выражение Range("A1").CurrentRegion соответст вует непрерывному диапазону ячеек рабочего листа. Свойство SpecialCells возвращает только пустые ячейки этого диапазона. Формула в стиле R1C1 (см. гла ву 6, “Стиль записи ссылок R1C1”) заполняет каждую пустую ячейку данными из ячейки, расположенной на одну строку выше. Вторая строка кода представляет собой быстрый способ выполнения команд Копирование (Copy) и Специальная вставка (Paste Special). Результат выполнения кода показан на рис. 3.11 .

–  –  –

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

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 .

Глава 4, Создание функций, Создание функций, определенных определенных пользователем.. 111 Наиболее распространенные пользователем задачи программирования в Excel

Иногда огромного количества Следующий шаг

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

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

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

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

Практикум

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

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

Эта функ ция принимает два аргумента:

Add(Number1, Number2)

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

Function Add(Number1, Number2) As Integer Add = Number1 + Number2 End Function

Попытаемся разобраться в приведенном выше коде:

имя функции — Add;

аргументы функции Add — Number1 и Number2 — перечислены в скобках по сле ее имени;

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

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

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

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

3. Нажмите комбинацию клавиш Shift+F3 или выберите команду меню Excel Вставка Функция (Insert Function), чтобы открыть диалоговое окно мастера функций .

4. В раскрывающемся списке Категория (Or select a category) выберите значение Определенные пользователем (User Defined) .

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

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

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

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

Поздравляем! Вы только что создали собственную функцию и применили ее на рабочем листе .

Функции, определенные пользователем Глава 4 Большинство функций, используемых на рабочих листах, могут с успехом применяться в VBA, и наоборот.

Тем не менее, VBA требует, чтобы функция, определенная пользователем (Add), вызывалась из процедуры (Addition), как показано ниже:

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

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

4.1:

MyName()

–  –  –

Функция 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 — в противном случае. Подобная проверка возмож на только при условии, что соответствующая рабочая книга открыта .

SheetExists(SName, WBName)

Функция SheetExists имеет 2 аргумента:

SName — имя рабочего листа;

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

Function SheetExists(SName As String, Optional WBName As _ String) As Boolean Dim WS As Worksheet Dim WB As Workbook Функции, определенные пользователем Глава 4 On Error Resume Next 'Проверить, задано ли имя файла рабочей книги .

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

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

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

SheetExists = Not (WS Is Nothing) End Function

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

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

If ShtExists Then MsgBox "Рабочий лист существует!" Else MsgBox "Рабочий лист НЕ существует!" End If 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.NewSearch 'Строка, которую должно включать в себя имя файла рабочей книги .

If Len(LikeText) 0 Then.Filename = LikeText 116 Часть I Первые шаги

–  –  –

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

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 = 0 Private Const ERROR_NOT_CONNECTED = 2250& Private Const ERROR_MORE_DATA = 234 Private Const ERROR_NO_NETWORK = 1222& Private Const ERROR_EXTENDED_ERROR = 1208& Private Const ERROR_NO_NET_OR_BAD_PATH = 1203& Текст функции WinUsername может быть помещен в любую часть модуля при условии, что он будет находиться ниже объявлений Private:

Функции, определенные пользователем Глава 4 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 Else 'Ошибка, завершение работы функции .

WinUsername = "Ошибка :" & lngUser End If 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 Первые шаги

–  –  –

Получение постоянного значения даты и времени Поскольку значение, возвращаемое функцией Now, обновляется при каж дом открытии рабочей книги, его не рекомендуется использовать для указа ния даты и времени создания или изменения рабочей книги. Несмотря на то что следующая функция основана на функции Now, ее результат куда менее динамичен, так как он обновляется только при обновлении соответствующей ячейки (рис. 4.3) .

DateTime()

–  –  –

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

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

–  –  –

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

IsEmailValid(StrEmail)

–  –  –

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

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

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

Function IsEmailValid(strEmail As String) As Boolean Dim strArray As Variant Dim strItem As Variant Dim i As Long 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) = 0 Then blnIsItValid = False IsEmailValid = blnIsItValid Exit Function End If 'Проверка использования только допустимых символов .

For i = 1 To Len(strItem) 'Чтобы упростить проверку, все символы переводятся в нижний регистр .

c = LCase(Mid(strItem, i, 1)) If InStr("abcdefghijklmnopqrstuvwxyz_-.", c) = 0 _ And Not IsNumeric(c) Then blnIsItValid = False IsEmailValid = blnIsItValid Exit Function End If Next i 'Проверка, что первым символом строк слева и справа от @ 'не является символ точки (.) .

If Left(strItem, 1) = "." Or Right(strItem, 1) = "." Then blnIsItValid = False IsEmailValid = blnIsItValid Exit Function End If Next strItem 120 Часть I Первые шаги

–  –  –

IsEmailValid = blnIsItValid End Function Суммирование значений ячеек на основе цвета заливки Следующая функция суммирует значения ячеек на основе цвета заливки .

SumByColor(CellColor, SumRange) На заметку Функция SumByColor не поддерживает ячейки с условным форматированием .

Таким образом, наличие заливки является ключевым условием, необходимым для выполнения функции .

–  –  –

SumByColor = myTotal End Function Пример использования функции SumByColor на рабочем листе можно увидеть на рис. 4.5 .

Рис. 4.5. Пример суммирования значений ячеек на основе цвета заливки Получение имени и номера цвета заливки ячейки

Следующая функция возвращает имя и номер цвета заливки ячейки:

CellColor(myCell, ColorIndex) На заметку Функция CellColor не поддерживает ячейки с условным форматированием. На личие заливки является ключевым условием, необходимым для выполнения функции .

Функция CellColor имеет 2 аргумента:

myCell — адрес ячейки;

ColorIndex — если данный необязательный параметр имеет значение True, функция CellColor возвратит номер цвета заливки ячейки (по умолчанию функция CellColor возвращает имя цвета заливки) .

Function CellColor(myCell As Range, Optional ColorIndex As Boolean) Dim myColor As String, IndexNum As Integer Select Case myCell.Interior.ColorIndex Case 1 myColor = "Черный" IndexNum = 1 Case 2 myColor = "Белый" 122 Часть I Первые шаги

–  –  –

IndexNum = 37 Case 38 myColor = "Розовый" IndexNum = 38 Case 39 myColor = "Сиреневый" IndexNum = 39 Case 40 myColor = "Светло-коричневый" IndexNum = 40 Case 41 myColor = "Темно-голубой" IndexNum = 41 Case 42 myColor = "Темно-бирюзовый" IndexNum = 42 Case 43 myColor = "Травяной" IndexNum = 43 Case 44 myColor = "Золотистый" IndexNum = 44 Case 45 myColor = "Светло-оранжевый" IndexNum = 45 Case 46 myColor = "Оранжевый" IndexNum = 46 Case 47 myColor = "Сизый" IndexNum = 47 Case 48 myColor = "Серый 40%" IndexNum = 48 Case 49 myColor = "Светло-сизый" IndexNum = 49 Case 50 myColor = "Изумрудный" IndexNum = 50 Case 51 myColor = "Темно-зеленый" IndexNum = 51 Case 52 myColor = "Оливковый" IndexNum = 52 Case 53 myColor = "Коричневый" IndexNum = 53 Case 54 myColor = "Вишневый" IndexNum = 54 Case 55 myColor = "Индиго" IndexNum = 55 Case 56 myColor = "Серый 80%" 124 Часть I Первые шаги

–  –  –

Получение номера цвета текста в ячейке

Следующая функция возвращает номер цвета текста в ячейке:

TextColor(Rng) На заметку Функция TextColor не поддерживает ячейки с условным или автоматическим форматированием текста (примером последнего является выделение отрицатель ных числовых значений красным цветом) .

Домашнее задание Изучив функцию TextColor, измените рассматривавшуюся в предыдущем раз деле функцию CellColor таким образом, чтобы она возвращала имя цвета текста в ячейке .

–  –  –

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

4.7):

NumUniqueValues(Rng) Рис. 4.7. Пример подсчета количества уникальных значений в диапазоне ячеек с помощью функции NumUniqueValues

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

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

Function NumUniqueValues(Rng As Range) As Long Dim myCell As Range, UniqueVals As New Collection 'Произвести пересчет результата при изменении диапазона ячеек .

Application.Volatile 'Поместить значения всех ячеек диапазона в коллекцию '(в коллекции могут находиться только уникальные значения) .

'Продолжить выполнение функции при возникновении ошибки '(помещение в коллекцию двух одинаковых элементов) .

On Error Resume Next For Each myCell In Rng UniqueVals.Add myCell.Value, CStr(myCell.Value) Next myCell 'Вернуться к стандартному режиму обработки ошибок .

On Error GoTo 0 'Возвратить количество элементов в коллекции .

NumUniqueValues = UniqueVals.Count End Function Удаление повторяющихся значений из диапазона ячеек Следующая функция удаляет повторяющиеся значения из указанного диа пазона ячеек:

UniqueValues(OrigArray) 126 Часть I Первые шаги

–  –  –

'Завершить выполнение функции, если массив содержит 'элемент одного из следующих типов .

iBadVarTypes(0) = vbObject iBadVarTypes(1) = vbError iBadVarTypes(2) = vbDataObject iBadVarTypes(3) = vbUserDefinedType iBadVarTypes(4) = vbArray 'Проверить, является ли переданное функции значение массивом .

If Not IsArray(OrigArray) Then Err.Raise ERR_BP_NUMBER,, ERR_BAD_PARAMETER Exit Function End If

–  –  –

UniqueValues = vAns End Function

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

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

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

–  –  –

Замена нескольких символов в строке Следующая функция используется для замены нескольких символов в строке (рис.

4.10):

MSubstitute(trStr, frStr, toStr)

–  –  –

Функция 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))

–  –  –

Извлечение чисел из смешанного текста Следующая функция извлекает числа из смешанного текста (текста, со держащего числа и буквы):

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

–  –  –

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

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

–  –  –

Разбор строки с символами разделителями Следующая функция извлекает элемент с заданным номером из строки с символами разделителями:

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

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

–  –  –

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

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

–  –  –

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) 'Заполнить массив .

i=1 For Each cl In Rng arr1(i) = cl.Value i=i+1 Next 'Отсортировать элементы массива .

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

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

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 Dim Temp

–  –  –

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

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

–  –  –

Поиск строки в диапазоне ячеек Следующая функция проводит поиск строки в указанном диапазоне ячеек .

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

ContainsText(Rng, Text) Пример использования функции ContainsText показан на рис. 4.16 .

Рис. 4.16. Пример поиска строки в диапазоне ячеек с помощью функции ContainsText 136 Часть I Первые шаги

Функция ContainsText имеет 2 аргумента:

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

Text — строка, которую необходимо найти .

Function ContainsText(Rng As Range, Text As String) As String

–  –  –

Запись содержимого ячейки в обратном порядке

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



Pages:   || 2 | 3 | 4 | 5 |
Похожие работы:

«"Думай медленно. решай быстро / Даниэль Канеман": АСТ; Москва; 2014 ISBN 978-5-17-080053-7 Наши действия и поступки определены нашими мыслями. Но всегда ли мы контролируем наше мышление? Нобелевский лауреат Даниэль Канеман объясняет, почему мы подчас...»

«Утверждаю Руководитель программы профессор _ Е.С. Рольбина "_"_2012 г. Учебная программа по дисциплине "Маркетинг" Составитель: профессор кафедры маркетинга КГФЭИ, к.э.н. Е.С. Рольбина Казань 2012 ВВЕДЕНИЕ В условия...»

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

«И. А. Дмитрик УКраинизаЦиЯ донБаССа В 1923–1938 гг.: оБЩеСТВенное Мнение Изучение процесса украинизации включает выявление отношения к данной проблеме как руководителей разного уровня, так и населения. Два фактора изначально осложняли восприятие...»

«Содержание Часть первая. ЭТАПЫ ЭВОЛЮЦИИ ОРГАНИЧЕСКИХ СОЕДИНЕНИЙ.САМООБРАЗОВАНИЕ КЛЕТКИ Глава I. СТАДИИ РАЗВИТИЯ Образование Вселенной (выход из равновесия и возврат к нему)....»

«СЧЕТНАЯ ПАЛАТА РОССИЙСКОЙ ФЕДЕРАЦИИ ОТЧЕТ О РЕЗУЛЬТАТАХ КОНТРОЛЬНОГО МЕРОПРИЯТИЯ "Проверка эффективности использования открытым акционерным обществом "РОСНАНО" (государственной корпорацией "Российская корпорация нанотехнологий") средств федерального бюджета, полученных в 2007-2012 годах, и соо...»

«"Р. ПРОТИВ РОССИИ" (ПОСТАНОВЛЕНИЕ) 1 ЕВРОПЕЙСКИЙ СУД ПО ПРАВАМ ЧЕЛОВЕКА ТРЕТЬЯ СЕКЦИЯ ДЕЛО "Р. ПРОТИВ РОССИИ" (Жалоба № 11916/15) ПОСТАНОВЛЕНИЕ СТРАСБУРГ 26 января 2016 г. Данное решени...»

«Корнелия Квин ПОСЛЕДНЯЯ ЖЕРТВА Трагедия в 15 сценах Моему Мертвому Ангелу – безнадежно далекому и бесконечно любимому Август, 2015. Г . Череповец ДЕЙСТВУЮЩИЕ ЛИЦА Вероника, молодая девушка Арчибальд, красивый молодой аристократ, ее возлюбленный Миссис Гамильтон, почтенная вдова, мать Вероники Джеймс, слуга Арчибальда София, по...»

«МУНИЦИПАЛЬНОЕ БЮДЖЕТНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ СРЕДНЯЯ ОБЩЕОБРАЗОВАТЕЛЬНАЯ ШКОЛА с. ИШКАРОВО МУНИЦИПАЛЬНОГО РАЙОНА ИЛИШЕВСКИЙ РАЙОН РЕСПУБЛИКИ БАШКОРТОСТАН РАССМОТРЕНО СОГЛАСОВАНО УТВЕРЖДАЮ на заседании ШМО Зам. директора по УВР Директ...»

«ОТЧЕТ Вилейского опытного лесхоза для общественности по результатам деятельности за 2015 год и плана на 2016 год 1. Контактная информация Наименование ГОЛХУ "Вилейский опытный лесхоз" организации Адрес: 222410 Минская обл. г. Вилейка, ул. Стахановская, 221 Телефон: Факс: (01771) 2-22-50 (01771) 2-22-50...»

«Политическая социология. Бюрократия © 1999 г. В.П. МАКАРЕНКО ПРАВИТЕЛЬСТВО И БЮРОКРАТИЯ МАКАРЕНКО Виктор Павлович доктор философских и политических наук, профессор, заведующий лабораторий философских проблем политики Ростовского государственного университета. Со...»

«Праздничные, знаменательные и памятные даты в ноябре 2013 года 1 ноября День судебного пристава 4 ноября День народного единства 6 ноября День Конституции Республики Татарстан 10 ноября День сотрудника органов внутренних дел РФ 17...»

«"УТВЕРЖДАЮ" Председатель закупочной комиссии В.В. Соколов " 29" мая 2015 года ДОКУМЕНТАЦИЯ открытого запроса предложений на поставку трубки латунной для нужд филиалов ОАО "ТГК-14" Город Чита 2015 год Страница 1 из 32 Оглавление 1. Общие положения 2. Тре...»

«ВУЛКАНОЛОГИЯ И СЕЙСМОЛОГИЯ 1997 №4 У Д К 550.4+551.217(571.66) © 1997 г. ПЕВЗНЕР М.М., ПОНОМАРЕВА В.В., МЕЛЕКЕСЦЕВ И.В. ЧЕРНЫЙ ЯР-РЕПЕРНЫЙ РАЗРЕЗ ГОЛОЦЕНОВЫХ МАРКИРУЮЩИХ ПЕПЛОВ СЕВЕРО-ВОСТОЧНОГО ПОБЕРЕЖЬЯ КАМЧАТКИ В результате тефрохронологических и радиоуглеродных исследований почвенно-пирокл...»

«1|Страница Открытый тендер с предварительным квалификационным отбором № ОТ 16-04-22 на выполнение работ по подготовке аналитических отчетов в области выявления инцидентов, методов совершения атак, престу...»

«СОДЕРЖАНИЕ 1. Введение 2. Аэронавигационная информация 3. Схемы аэродрома и сценарии 4. Основные процедуры для пилотов 4.1. Вылет 4.2. Прилет ВВЕДЕНИЕ ДОМОДЕДОВО Московский аэропорт Домодедово – крупнейший аэропорт России по объему пассажирских перевозок и занимает одно из лидирующих мест в Европе согласно классификации Международного совет...»

«www.koralinisklubas.weebly.com E-mail: koraluklubui@gmail.com О.А.Бутакова Нужная информация о здоровье 2. Содержание Частная патология. Тест на состояние систем организма. Причины заболеваний. Вирусы. Бактерии. Грибк...»

«Е. В. Пилипко. Структура сознания личности Е. В. Пилипко (г. Харьков, Украина) Структура сознания личности Сегодня в стремительно изменяющемся мире синергетика предлагает изучать динамичные системы с позиции самоорганизации, в том числе и в рамках феноменологии. Динамичность же сознания, как...»

«Зарегистрировано Министерством юстиции Российской Федерации 17 октября 2008 года Регистрационный № 12478 16 октября 2008 года № 323-П ПОЛОЖЕНИЕ О предоставлении Банком России российским кредитным организациям кредитов без обе...»

«© Современные исследования социальных проблем (электронный научный журнал), Modern Research of Social Problems, №2(34), 2014 www.sisp.nkras.ru DOI: 10.12731/2218-7405-2014-2-3 УДК 378 ДЕЯТЕЛЬНОСТНЫЙ ПОДХОД КАК ТЕОРЕТИКОМЕТОДОЛОГИЧЕСКАЯ СТРАТЕГИЯ ФОРМИР...»

«И.Ю.Анкудинов "Синь горюч камень" былины о Садко В 1871 г. А.Ф.Гильфердинг записал в Пудожье от Андрея Пантелеевича Сорокина текст былины о Садко. В начальной части этой записи содержится...»

«Фонд имени Генриха БЁллЯ Гендер для "чайников"-2 Москва "Звенья" ББК 60.54:71.4 Г34 Научный редактор Ирина Тартаковская Иллюстрации Адгура Дзидзария ISBN 978–5–7870–0110–5 © Фонд имени Генриха Бёлля, состав, 2009 © Коллектив авторов, 2009 © А.Г.Дзидзария, рисунки, 2009 © А.А.Кулаков, оформление, 2...»

«Автоматизированная копия 586_261966 ВЫСШИЙ АРБИТРАЖНЫЙ СУД РОССИЙСКОЙ ФЕДЕРАЦИИ ПОСТАНОВЛЕНИЕ Президиума Высшего Арбитражного Суда Российской Федерации № 268/11 Москва 14 июня 2011 г. Президиум Высшего Арбитражного Суда Российской Федерации в составе: председательствующего – Председ...»

«НАУКИ О ЗЕМЛЕ УДК 551.582.2; 551.583.14 С.А. Тобратов ЗАКОНОМЕРНОСТИ И МАСШТАБЫ ВЕКОВЫХ КОЛЕБАНИЙ КЛИМАТА ЦЕНТРА РУССКОЙ РАВНИНЫ (ПО ДАННЫМ МЕТЕОСТАНЦИИ РЯЗАНЬ) Приведено обоснование циклической составляющей современных изменений климата Центра России с характерными временами от 70 лет до 1,5 недель. Рассмотрены масштабы измен...»

«ИНТЕРПРЕТАЦИЯ И ВОСПРИЯТИЕ НАСЛЕДИЯ СВЯТИТЕЛЯ ФИЛАРЕТА. 111 О. Л. Фетисенко (ИРЛИ, Санкт-Петербург) ПРОТОИЕРЕЙ ИОСИФ ФУДЕЛЬ О МИТРОПОЛИТЕ ФИЛАРЕТЕ Протоиерей Иосиф Иванович Фудель (1864/1865–1918), известный церковный деятель и публицист, настоятель арбатской церкви Николы в Плотниках, ближайший из младш...»

«Б. И. Макаренко Партийная система России в 2008 году Партийная система России в 2008 году Б. И. Макаренко 1. Партийная система в России: институциональные и политические факторы Сравнительный контекст: партийная система...»

«1 ВСЕРОССИЙСКАЯ ОЛИМПИАДА ШКОЛЬНИКОВ ПО ОБЩЕСТВОЗНАНИЮ 2014–2015 г. МУНИЦИПАЛЬНЫЙ ЭТАП. 9 КЛАСС Критерии оценивания Установите истинность или ложность суждения. Обозначьте "ДА" истинные суждения; "НЕТ" — ложные суждения. Ответы внесите в таблицу.1. Термин "социальное государство" впервые был употреблён в трудах Платон...»

«ИНСТРУКЦИЯ ПО ЭКСПЛУАТАЦИИ ПРИВОДА ДЛЯ СЕКЦИОННЫХ ВОРОТ FAAC 540BPR Внимательно прочитайте инструкцию перед использованием привода и не выбрасывайте ее. Основные правила безопасности Автоматический привод 540BPR обеспечивает высокую степень безопасности, если установл...»

«Сообщение о раскрытии акционерным обществом на странице в сети Интернет списка аффилированных лиц 1. Общие сведения 1.1 . Полное фирменное наименование эмитента Открытое акционерное общество энергетики и электрификации "Мосэнерго"1.2. Сокращенное фирменное наименование ОАО "Мосэнерго" эмитента 1.3. Место нах...»

«Гастон Дюрвиль, Андре Дюрвиль в сотрудничестве с художником Эмилем Байи. Чтение по лицу характера, темперамента и болезненных предрасположений. Оглавление Общие принципы. Морфология человека. Классификация. Квадратные...»

















 
2018 www.new.z-pdf.ru - «Библиотека бесплатных материалов - онлайн ресурсы»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 2-3 рабочих дней удалим его.