Vba excel управление другими программами
Иногда бывает необходимо перенести что-то из Excel в другое приложение. Я возьму для примера Word. Например скопировать ячейки и вставить. Обычно мы это так и делаем - скопировали в Excel, открыли Word - вставили. Но сделать это при помощи кода чуть сложнее, хотя если разобраться никаких сложностей нет. Ниже приведен пример кода, который открывает Word, открывает в нем определенный документ, копирует данные из Excel и вставляет в открытый документ Word.
Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Excel диапазон "A1:A10" Range("A1:A10").Copy 'вставляем скопированные ячейки в Word - в начала документа objWrdDoc.Range(0).Paste 'закрываем документ Word с сохранением objWrdDoc.Close True ' False - без сохранения 'закрываем приложение Word - обязательно! objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
Tips_Macro_OpenWord.xls (49,5 KiB, 5 922 скачиваний)
В файле-примере, приложенном к данной статье, в комментариях к коду есть несколько добавлений. Например, как вставить текст из ячеек в определенные закладки Word-а и как добавить новый документ, а не открывать уже имеющийся. Так же так есть код проверки - открыто ли приложение Word в данный момент. Порой это тоже может пригодиться, чтобы работать с запущенным приложением Word, а не создавать новое:
В принципе, активировать или вызвать(если закрыто) другое приложение Офиса можно одной строкой:
Sub Open_AnotherApp() Application.ActivateMicrosoftApp xlMicrosoftWord End Sub
но данный метод может пригодиться только в том случае, если Вам необходимо действительно лишь активировать другое приложение, но дальше обращаться к этому приложению Вы уже не сможете.
По сути, методами CreateObject и GetObject можно обратиться к любому стороннему приложению(например Internet Explorer). Куда важнее при обращении к этим объектам знать объектную модель того приложения, к которому обращаетесь. Чтобы увидеть свойства и методы объектной модели приложения, можно в редакторе VBA подключить необходимую библиотеку, объявить переменную, назначив ей тип приложения. Покажу на примере того же Word-а.
Для начала открываем меню Tools - References :
Подключаем библиотеку:
Затем объявляем переменную и присваиваем ей тип нужного приложения:
Sub OpenWord() Dim objWrdApp As Word.Application Set objWrdApp = New Word.Application objWrdApp.Visible = True End Sub
Если теперь в редакторе, внутри этой процедуры в любом месте ниже объявления переменной набрать objWrdApp и точку, то сразу после ввода точки выпадет меню, в котором будут перечислены все доступные методы и свойства этого приложения.
Так же можно нажать F2 и через поиск найти Word и просмотреть все методы и свойства данного приложения.
Метод установки ссылки на библиотеку приложения через Tools-References называют еще ранним связыванием. Подобный метод позволяет создать ссылку на приложение быстрее и, как описано выше, предоставляет разработчику доступ к визуальному отображению свойств и методов объекта. Но есть существенный минус: если в своем коде Вы установите ссылку на Word 12 Object Libbary(Word 2007), то на ПК с установленным Word 2003 получите ошибку MISSING, т.к. Word 2003 относится к библиотеке Word 11 Object Libbary. Подробнее можно прочитать в статье Ошибка — Cant find project or library.
Метод же CreateObject еще называется методом позднего связывания. Применяя его не возникнет проблем с MISSING, очень часто возникающих при раннем связывании. Поэтому я рекомендовал бы при разработке использовать раннее связывание для удобства использования свойств и методов(если Вы их не знаете), а перед распространением приложения в коде заменить все именованные константы(типа wdLine) на числовые константы(для wdLine это 5) и применить позднее связывание. Посмотреть числовое значение константы можно просто записав её в коде, начать выполнение кода через F8 и навести курсор мыши на эту константу. Всплывающая подсказка покажет числовое значение. Так же можно отобразить окно Immediate(View -Immediate Window или сочетание клавиш Ctrl + G ), записать вопросительный знак и вставить эту константу и нажать Enter :
?wdLine
ниже будет выведено числовое представление этой константы.
А заменять эти константы их числовыми значениями в случае с поздним связыванием необходимо, т.к. Excel не знает их значений.
Попробую пояснить поподробнее про эти константы и почему их надо заменять какими-то числами: при подключении библиотеки Wordа(Word 12 Object Libbary) мы так же подключаем и все свойства, методы и константы, которые доступны из Wordа. И их использование напрямую становится доступно из Excel и мы можем смело написать что-то вроде wbLine и Excel поймет эту константу. При позднем же связывании мы уже не подключаем библиотеки Word(во избежание ошибок совместимости) и как следствие - методы, свойства и константы Wordа для Excel становятся чем-то неизвестным и не документированным и мы получим ошибку "Variable not defined"(если включена директива Option Explicit) при попытке назначить свойство через wdLine. Если же Option Explicit не включена - то хоть ошибки не будет, но и код будет работать неверно, т.к. для неизвестной для Excel переменной wbLine будет назначено значение 0(Empty). Поэтому и надо все константы другого приложения заменять их числовыми значениями.
Главная ошибка новичка
И хочу так же упомянуть про ошибку, которую очень часто совершают при обращении к одному приложению из другого. Допустим, необходимо скопировать из Word все данные в Excel. Часто начинающие делают это так:
Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа Range.Copy 'вставляем скопированное в ячейку А1 активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
На строке Range.Copy обязательно получите ошибку от VBA, указывающую, что нужен аргумент для объекта. Можно попробовать добавить этот аргумент: Range(1).Copy. Но все равно получим ошибку. Можно, конечно, указать даже ячейки: Range("A1").Copy. Но это приведет к тому, что скопирована будет ячейка А1 активного листа Excel.
Все дело в том, что мы хотим скопировать данные из Word-а, выполняя при этом код из Excel. А у Excel тоже есть объект Range с другими аргументами. И если не указать какому приложению, листу или документу принадлежит Range, то по умолчанию он будет отнесен к тому приложению, из которого выполняется код. Т.е. к Excel. Если совсем кратко об этом - всегда надо указывать какому приложению или объекту принадлежит используемый объект или свойство. Правильно код должен выглядеть так:
Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа 'при этом перед Range явно указываем откуда его брать - из документа Word -objWrdDoc("C:\Doc1.doc") objWrdDoc.Range.Copy 'вставляем скопированное из Word в активную ячейку активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
Вместо Range ту же ошибку делают и с Selection(потому что Selection часто присутствует в записанных макрорекордером макросах), т.к. этот объект есть и в Excel и в Word и без явного указания приложения будет относится к приложению, в котором записано.
Tips_Macro_OpenWord.xls (49,5 KiB, 5 922 скачиваний)
А в архиве ниже - практически готовое решение заполнения всевозможных бланков Word из Excel. Как это работает. У нас есть таблица Excel с данными для заполнения бланков заявлений на пособия:
Обращаю внимание, что в первой строке расположены метки. Они нужны для того, чтобы код мог понять значения какого столбца в какое место шаблона Word должны попасть. А в самом шаблоне Word мы должны проставить эти самые метки:
Фигурные скобки сделаны для того, чтобы код 100% искал и заменял только метку в шаблоне, исключая при этом замену случайного текста вне скобок(ведь слово "Должность" может встречаться и само по себе).
А здесь я схематично привел то, как будут происходить замены:
Сначала программа создаст новую папку, в которую и будет сохранять создаваемые файлы(имя папки состоит из даты и времени запуска кода). Далее программа циклом пройдется по каждой строке таблицы, создаст на основании шаблона Word( "Шаблон.doc" ) новый файл для этой строки, заполнит этот шаблона данными на основании меток, и сохранит созданный файл под новым именем. Сам файл шаблона при этом не изменяется - все метки в нем сохраняются как были настроены до запуска кода. Конкретно в приложенном коде значение для имени нового файла берется из первого столбца "ФИО с инициалами". Но это можно изменить в коде при необходимости. Делается это в этой строке:
'считываем фамилию с инициалами sWDDocName = .Cells(lr, 1).Value
Что еще важно: файл шаблона Word должен находиться в той же папке, что и файл с кодом. Название файла в приложенном к статье файле должно быть "Шаблон.doc". Но его так же можно изменить, не забыв изменив его в коде в этой строке:
'имя шаблона Word с основным текстом и метками Const sWDTmpl As String = "Шаблон.doc"
If you want to run code in one Microsoft Office application that works with the objects in another application, follow these steps.
To run the code
Set a reference to the other application's type library in the References dialog box (Tools menu). Then, the objects, properties, and methods will appear in the Object Browser and the syntax will be checked at compile time. You can also get context-sensitive Help on them.
Declare object variables that will refer to the objects in the other application as specific types. Qualify each type with the name of the application that is supplying the object. For example, the following statement declares a variable that points to a Microsoft Word document and another that refers to a Microsoft Excel workbook.
Note You must follow the preceding steps if you want your code to be early bound.
Use the CreateObject function with the OLE Programmatic Identifiers of the object you want to work with in the other application, as shown in the following example. To see the session of the other application, set the Visible property to True.
Apply properties and methods to the object contained in the variable. For example, the following instruction creates a new Word document.
When finished working with the other application, use the Quit method to close it, and then set its object variable to Nothing to free any memory it is using, as shown in the following example.
About the Contributors
Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Dennis has been developing Excel solutions for over 20 years and is also the coauthor of "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition)."
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Помимо работы с данными Word приложение может обмениваться данными с другими приложениями, такими как Excel, PowerPoint или Access. Вы можете общаться с другими приложениями с помощью автоматизации (ранее OLE Automation) или динамического обмена данными (DDE).
Автоматизация Word из другого приложения
Автоматизация позволяет возвращать, редактировать и экспортировать данные, ссылаясь на объекты, свойства и методы другого приложения. Объекты приложений, на которые может ссылаться другое приложение, называются объектами автоматизации.
Первым шагом на пути к предоставлению Word другому приложению для автоматизации является ссылка на объект Word Application . В Visual Basic вы используете функцию Visual Basic CreateObject или GetObject, чтобы вернуть ссылку на объект Word Application. Например, в процедуре Excel можно использовать следующие инструкции.
Это инструкция делает объект Application in Word доступным для автоматизации. С помощью объектов, свойств и методов объекта Word Application можно управлять Word. Например, в следующей инструкции создается новый документ Word.
Используйте свойство Видимый , чтобы сделать новый документ видимым после его создания.
Функция CreateObject запускает сеанс Word, который автоматизация не закрывается, когда истекает срок действия объектной переменной, ссылаемой на объект Application . Настройка ссылки объекта на ключевое слово Visual Basic Ничего не закроет Word. Вместо этого используйте метод Quit для закрытия приложения Word. В следующем Excel показан путь запуска Word. Метод Quit используется для закрытия нового экземпляра Word после отображения пути запуска.
Автоматизация другого приложения из Word
Чтобы обмениваться данными с другим приложением с помощью автоматизации из Word, сначала вы получите ссылку на приложение с помощью функции CreateObject или GetObject . Затем, используя объекты, свойства и методы другого приложения, вы добавляете, меняете или удаляете сведения. После внесения изменений закройте приложение. В следующем примере Word отображается путь Excel запуска. Используйте заявление Visual Basic set с ключевым словом Nothing для очистки переменной объекта, которая имеет тот же эффект, что и закрытие приложения.
Использование динамического обмена данными (DDE)
Заметка о безопасности
Задача | Способ |
---|---|
Запуск DDE | DDEInitiate |
Получение текста из другого приложения | DDERequest |
Отправка текста в другое приложение | DDEPoke |
Проведение команды в другом приложении | DDEExecute |
Закрыть канал DDE | DDETerminate |
Закрыть все каналы DDE | DDETerminateAll |
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Если вы произносите слово "макрОсы" с придыханием от ужаса и ударением на втором слоге, а фраза "Visual Basic for Applications" для вас звучит как заклинание, то эта статья - не для вас. Во всяком случае - пока :)
Если же у вас за плечами есть хоть какой-то опыт программирования макросов на VBA в Excel, и вы не планируете останавливаться, то приведенная ниже подборка полезных надстроек и программ должна вам (хотя бы частично) пригодиться.
MZ-Tools - "швейцарский нож" для программиста
После установки в редакторе VBE в меню Tools появится подменю MZ-Tools и новая панель инструментов для быстрого вызова тех же функций:
Умеет очень и очень много. Из самого ценного, на мой взгляд:
- Автоматически добавлять "рыбы-заготовки" для создания процедур, функций, обработчиков событий и ошибок с правильным наименованием переменных по венгерской системе.
- Копировать элементы управления на пользовательских формах вместе с их кодом.
- Делать закладки (Favorites) на процедуры и быстро к ним переходить в большом проекте.
- Разбивать длинные строки кода на несколько и собирать обратно (split и combine lines).
- Выдавать подробную статистику по проекту (кол-во строк кода, процедур, элементов на формах и т.д.)
- Проверять проект на наличие неиспользуемых переменных и процедур (Review Source)
- Создать свою базу заготовок кода (Code Templates) для типичных случаев и быстро вставлять их потом в новые макросы.
- Автоматически создавать длинную и страшную строку для подключения к внешним источникам данных по ADO.
- Вешать горячие клавиши на любую функцию из надстройки.
Однозначный мастхэв для программиста любого уровня. Если у вас последняя версия Office, то скачивайте обязательно свежую версию MZ-Tools 3.00.1218 от 1 марта, т.к. в ней исправлен баг, при работе с Excel 2013.
Smart Indenter - автоматическая расстановка отступов в коде
Хорошо делает одну простую, но очень нужную операцию - автоматически расставляет отступы табуляции в коде VBA, наглядно выделяя вложенные циклы, проверки условий и т.д.
Очень удобно повесить это действие на любое удобное сочетание клавиш в разделе Indenting Options и делать в одно касание.
К сожалению, автор программы забросил ее в 2005 году (почему, Карл!?) и последняя версия на сайте предназначена для Excel 97-2003. Тем не менее, программа вполне успешно работает и с более новыми версиями. Единственный нюанс: если у вас Excel 2013, то перед установкой Smart Indenter нужно установить сначала MZ-Tools последней версии, т.к. она содержит нужную для работы Indenter'а динамическую библиотеку.
VBE Tools - микроподстройка элементов в формах
Выравнивание элементов управления (кнопок, полей ввода, текстовых надписей и т.д.) на сложной форме может быть весьма мучительным процессом. Стандартная привязка к сетке редактора через меню Tools - Options - General - Align Controls to Grid иногда не очень помогает и даже начинает мешать, особенно если нужно сдвинуть, например, кнопку совсем на чуть-чуть. В этом деле поможет надстройка VBE Tools, которая после установки отобразить простую панельку, где можно для выбранного элемента произвести микроподстройку размеров и положения на форме:
Сдвиг положения также можно будет делать с помощью сочетаний клавиш Alt+стрелки, а изменение размеров с помощью Shift+Alt+стрелки и Ctrl+Alt+стрелки.
Также по щелчку правой кнопки мыши по элементу можно его переименовать сразу вместе с кодом.
VBA Diff - поиск отличий в коде
Этот инструмент пригодится, пожалуй, больше профессиональным программистам на VBA при создании больших и сложных проектов или совместной разработке. Его основная функция - сравнивать два проекта и наглядно отображать разницу в коде между ними:
Есть 30-дневный бесплатный период, а потом надстройка попросит заплатить за нее 39 фунтов (около 3.5 тыс.руб по текущему курсу).
Говоря откровенно, мне она пригодилась в этой жизни всего раза 3-4 на супербольших проектах, но сэкономила мне тогда несколько дней и много-много нервных клеток :) Ну и всегда есть, конечно, бесплатная альтернатива: экспортировать код в текстовый файл (правой кнопкой мыши по модулю - Export) и сравнивать их потом в Microsoft Word с помощью команды Рецензирование - Сравнить документы, но с помощью VBA Diff это на порядок удобнее.
Moqups и Wireframe Sketcher - прототипирование интерфейса
При создании сложных интерфейсов для взаимодействия с пользователем очень удобно бывает заранее спроектировать примерный внешний вид диалоговых окон, т.е. выполнить прототипирование. По факту, это оказывается гораздо проще, чем переделывать потом готовые формы и их код. Помню, как-то раз в одном из проектов заказчик просил сделать "меню", имея ввиду "вкладки". Полдня работы псу под хвост :(
Существует огромное количество платных и бесплатных программ разного уровня сложности и мощи для этих задач. Я таких программ и сервисов перепробовал с десяток, и в последнее время чаще всего использую Moqups:
Это онлайновый редактор, который:
В общем и целом, для задач разработчика на VBA - более, чем достаточно, я считаю.
Если для кого принципиально нужен офлайновый вариант (для работы без доступа к интернету на берегу моря, например), то рекомендую Wireframe Sketcher:
После бесплатного демо-периода на 2 недели попросит купить за те же 99$.
Invisible Basic - обфускатор кода
Надежно закрыть исходный код ваших макросов паролем в Microsoft Excel, к сожалению, невозможно. Однако, существует целый класс программ, называемых обфускаторами (от англ. obfuscate - сбивать с толку, запутывать), которые так меняют внешний вид VBA-кода, что прочитать и понять его будет крайне сложно, а именно:
- имена переменных, процедур и функций заменяются на длинные бессмысленные наборы символов или, наоборот, на короткие буквенные непонятные обозначения
- убираются наглядные отступы табуляции
- убираются или, наоборот, хаотично ставятся переносы строк и т.д.
Откровенно говоря, я не фанат использования подобных методов. В частности, с PLEX я решил, что лучше буду отдавать покупателям полной версии открытый, понятный и прокомментированный исходный код - это мне кажется более правильным. Тем не менее, у моих знакомых программистов неоднократно были случаи, когда подобная программка очень пригодилась бы (программист выполнил работу, а клиент не заплатил и т.д.) Так что если понадобится - знайте, где взять. "Мы мирные люди, но наш бронепоезд. " и все такое.
Code Cleaner - очистка кода
В процессе работы над проектом (особенно, если он большой и долгий) в модулях кода и формах начинает накапливаться "мусор" - обрывки служебной информации редактора VBE, которые могут привести к неожиданным и нежелательным глюкам. Утилита Code Cleaner чистит эту гадость простым, но надежным способом: экспортирует код из модулей в текстовые файлы, а потом импортирует его чистеньким обратно. Очень рекомендую при работе над большими проектами периодически проводить такую "уборку".
Ribbon XML Editor
Если для запуска ваших макросов хочется создать на ленте Excel собственную вкладку с красивыми кнопками, то вам не обойтись без редактора XML-файлов интерфейса. Однозначно, самым удобным и мощным на сегодняшний день является в этом плане отечественная программа Ribbon XML Editor, созданная Максимом Новиковым.
Совершенно замечательный софт, который:
- позволит легко добавлять на ленту собственные вкладки, кнопки, выпадающие списки и другие элементы нового интерфейса Office
- полностью поддерживает русский язык
- помогает при редактировании, отображая контекстные подсказки
- можно легко освоить по урокам
- полностью бесплатен
Долгие годы Microsoft упорно в лоб игнорирует разработчиков на VBA, считая его, судя по всему, неполноценным языком программирования. Периодически проскальзывают слухи, что в следующей версии Office Visual Basic уже не будет или его заменят на JavaScript. Регулярно выходят новые версии Visual Studio с новыми плюшками, а редактор VBE так и застрял в 1997 году, до сих пор не умея стандартными средствами расставить отступы в коде.
В реальности же, тысячи людей экономят часы и дни, благодаря программистам на VBA, создающим макросы для автоматизации повседневных рутинных задач офисной обработки данных. Кто видел, как макрос в 10 строк кода делает за полминуты рассылку файлов по 200 клиентам, заменяя три часа тупой работы - тот меня поймет :)
Все вышеперечисленные программы - это сугубо мой личный выбор и персональная рекомендация на основе личного опыта. Никто из авторов не просил меня о рекламе и не платил за нее (а я бы и не взял, из принципа). Если у вас есть что добавить к приведенному списку - добро пожаловать в комментарии, благодарное человечество в долгу не останется.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Читайте также: