Введение

Без преувеличения можно сказать, Microsoft Office любых версий является самым полезным и самым используемым продуктом Microsoft. И современный руководитель, и менеджер, и преподаватель, и студент, и школьник – все, кто имеет в своем распоряжении компьютер, в той или иной степени используют эту систему.

Одним из наиболее важных и полезных аспектов подготовки Microsoft Office к выполнению определенных задач является автоматизация процессов взаимодействия пользователя с приложениями Microsoft Office. Эти приложения не являются законченными продуктами, настроенными на выполнение всех возможных задач, а представляют собой системы, которые нуждаются в определенной настройке, что обеспечивается разнообразными средствами, как интерактивными, так и программными. Все приложения Microsoft Office поддерживают язык программирования Visual Basic for Applications (VBA). VBA позволяет работать с Microsoft Office, как с некоторым конструктором: в распоряжении разработчика VBA-приложения не только большое количество объектов и коллекций, но и возможности настроек, позволяющие до такой степени программно настроить любое приложение, что пользователь такого приложения может и не понять, с каким приложением происходит «общение».

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

Привлекательная особенность VBA в том, что он очень удобен для первого знакомства с программированием в среде Windows. Этому способствует широкое распространение приложений Microsoft Office, бесконечное разнообразие возможных практических задач, интуитивно понятная интегрированная среда редактора Visual Basic, возможность обучения программированию посредством анализа кода, записанного при помощи макрорекордера, наличие огромного количества объектов, которыми можно управлять из VB-кода. Более глубокие знания VBA-программирования позволят решать, практически, любые задачи: от автоматизации создания простых документов до обработки баз данных с использованием как настольных, так и сетевых СУБД.

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

На базе предложенного материала можно организовать вариативность в преподавании VBA в зависимости от подготовки студентов. Лабораторные работы охватывают основы языка Visual Basic, а также операции по созданию макросов, процедур и функций, приложений обработки электронных таблиц с

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

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

ь цель занятия;

ь необходимые материалы к заданиям;

ь справочный материал;

ь комментированные практические задания;

ь упражнения для самостоятельной работы;

ь контрольные вопросы.

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

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

Часть 1. Макросы и язык программирования vba. Среда редактора visual basic

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

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

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

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

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

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

Лабораторная работа № 1. Запись новых макросов в Excel. Выполнение

макросов

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

Материалы к занятию: MS Excel 2003.

Задание 1. Создайте макрос в Excel, который форматирует текст в текущей ячейке шрифтом Arial, полужирным, 12 размером.

1. Задайте стартовые условия.

Для этого:

^Запустите Excel 2003 (Пуск/Все программы / Microsoft Office / Microsoft Office Excel 2003), если он еще не запущен;

откройте какую-либо рабочую книгу;

выберите какой-либо рабочий лист;

выделите любую ячейку в рабочем листе.

2. Выберете место и имя хранения макроса.

Для этого:

Выберете в меню Сервис/Макрос/Начать запись… (Tools/Macro/Record New Macro…);

* в раскрывшимся диалоговом окне Запись макроса (Record Macro ) (рис. 1), в текстовом окне Имя макроса (Macro Name ) введите FormatArialBold 12 в качестве имени макроса;

ь оставьте без изменений текст, который Excel вставила в поле Описание (Description ), но добавьте следующее: Форматирует текст диапазона: Arial , Bold , 12 ; этот дополнительный комментарий поможет вам (и другим) определить назначение данного макроса;

ь если вы уверены в том, что будете часто использовать макрос, который

собираетесь записывать, можете назначить для его запуска горячую клавишу; если - да, введите горячую клавишу в текстовое окно Сочетание клавиш (Shortcut Key ) окна Запись макроса;

* щелкните на кнопке ОК для начала записи макроса; как только вы щелкните на кнопке ОК в диалоговом окне Запись макроса, Excel запустит

макрорекордер, отобразит панель ^ Остановить запись (Stop Recorder ) и начнет запись ваших действий. Макрорекордер сохранит каждое ваше действие в новом макросе.

Замечание 1. Доступными вариантами при сохранении макросов являются Личная книга макросов (Personal Macro Workbook ), Новая книга (New Workbook ) и Эта книга (This Workbook ). Когда вы выбираете в качестве места для хранения макроса Личная книга макросов, Excel сохраняет макрос в файле специальной книги с именем Personal.xls в папке, в которую установлена Excel. Excel автоматически открывает эту книгу каждый раз в начале работы. Поскольку вам всегда доступны макросы из всех открытых книг, макрос, сохраненный в книге Personal.xls, также будет доступен вам всегда. Если книга Personal.xls не существует, Excel создаст ее. Выбор Эта книга приведет к тому, что Excel сохранит новый макрос в текущей активной рабочей книге. Выбор Новая книга приведет к созданию в Excel новой рабочей книги, в которой будет сохранен этот макрос, - рабочая книга, которая была активной при запуске вами макрорекордера, остается активной рабочей книгой; любые действия, которые вы записываете, выполняются в этой книге, а не в новой рабочей книге, созданной для сохранения макроса.

Замечание 2. Не всегда при запуске макрорекордера вы можете увидеть на экране панель Остановить запись. Поскольку это - обычная панель, ее можно отображать или не отображать. Этим, как и другими панелями, управляет команда Панели инструментов (Toolbars ) меню Вид (View ). В любом случае (при наличии на экране панели Остановить запись или ее отсутствии) вы можете остановить макрорекордер, выбрав Сервис/Макрос/Остановить запись.

3. Запишите действия и остановите макрорекордер.

Для этого:

выберите команду Формат/Ячейки… (Format / Cells …) для отображения диалогового окна Формат ячеек (Format Cells );

щелкните на ярлычке Шрифт (Font ) для отображения опций шрифта (рис. 2);

выберите Arial в списке Шрифт (Font ); выполните этот шаг, даже если шрифт Arial уже выбран;

выберите Полужирный (Bold ) в списке Начертание (Font Style );

щелкните на кнопке ОК, чтобы закрыть диалоговое окно Формат ячеек и изменить выделенную ячейку в рабочем листе;

щелкните на кнопке Остановить запись (Stop Macro ) на панели Остановить запись (Stop Recorder ) или выберите команду Сервис/Макрос/Остановить запись (Tools / Macro / Stop Recording ).

Замечание 3. По умолчанию панель l ? f Остановить запись в Excel содержит две командные кнопки. Левая кнопка - это кнопка Остановить запись (Stop ); щелкните на этой кнопке для остановки макрорекордера. Правая кнопка -это кнопка Относительная ссылка (Relative Reference ). По умолчанию Excel записывает абсолютные ссылки на ячейки в ваши макросы. Кнопка Относительная ссылка является кнопкой-переключателем (toggle). Когда запись с относительными ссылками отключена, кнопка Относительная ссылка выглядит плоской; при помещении курсора мыши на кнопку вид кнопки изменяется и она выглядит отжатой. Когда запись с относительной ссылкой включена, кнопка Относительная ссылка на панели Остановить запись нажата (находится в «утопленном» положении). Щелкая на кнопке Относительная ссылка, можно включать и выключать запись с относительными ссылками во время записи по вашему желанию.

Задание 2. Выполните макрос FormatArialBoIdl 2.

Для этого:

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

^выберите команду Сервис/Макрос/Макросы… для отображения диалогового окна Макрос;

Выберите макрос PERSONAL . XLS ! FormatA ri aIBoIdl 2 в списке Имя макроса и щелкните на кнопке Выполнить для запуска макроса FormatArialBoldl2. Текст в любой ячейке, которая была выделена до запуска вами этого макроса, будет теперь иметь формат полужирного шрифта Arial 12-го размера.

Упражнение

Создайте макрос в Excel, который вычисляет сумму ячеек A1, A2 и выводит результат в ячейку A3 шрифтом Times New Roman, курсивом, 12 размером.

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

Создайте макрос в Excel, который выводит красными буквами в ячейку A3 слово “ Частное: ”, справа от нее выводит частное ячеек A1 и A2 шрифтом

Times New Roman, курсивом, 12 размера.

Макросы Visual Basic for Applications сохраняются как часть файлов, в которых Excel (а также Word и Access) обычно содержит свои данные, – макросы сохраняются в файлах рабочих книг в Excel. Макросы сохраняются в специальной части файла данных, называемой Modules (модули ). Модуль VBA содержит исходный код (source code ) макроса – текстовое представление инструкций. Каждый файл рабочей книги Excel может не содержать модулей или содержать один или несколько модулей. Модули, сохраняемые в одной рабочей книге Excel, имеют общее название Project (проект ).

При записи макроса в Excel вы можете определять только рабочую книгу, в которой Excel сохраняет записанный макрос, – текущую рабочую книгу, новую рабочую книгу или рабочую книгу Personal.xls. Excel выбирает модуль, в котором сохраняется записанный макрос, и при необходимости создает этот модуль. Когда Excel создает модуль, в котором сохраняется записанный макрос, модулю присваивается имя ModuleN , где N – это количество модулей, созданных для определенной рабочей книги во время текущего сеанса работы. Например, в первый раз, когда вы сохраняете записанный макрос в Personal.xls (личной книге макросов), Excel создает модуль с именем Module 1 . Если вы продолжаете записывать макросы в том же сеансе работы и сохранять их в Personal.xls, Excel продолжает сохранять записанные макросы в том же модуле Module 1 до тех пор, пока вы не выберете другую рабочую книгу. Если позже в том же сеансе работы вы опять захотите сохранить записанные макросы в Personal.xls, Excel добавляет другой модуль с именем Module 2 в эту книгу.

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

Для просмотра модулей, сохраненных в определенной рабочей книге (и исходного кода макроса, который они содержат), вам необходимо использовать компонент Visual Basic Editor (Редактор Visual Basic). Этот компонент предоставляет инструментальные средства, которые используются для создания новых модулей, просмотра содержимого существующих модулей, создания и редактирования исходного кода макроса, создания пользовательских диалоговых окон и выполнения других задач, относящихся к написанию и обслуживанию программ на VBA. Редактор Visual Basic (Редактор VB) содержит одни и те же возможности в Excel, Word и Access.

Задание 3. Запустите Редактор VB.

Для этого:

ь выберите Сервис/Макрос/Редактор Visual Basic (Tools / Macro / Visual Basic Editor ) или нажмите сочетание клавиш Alt + F 11 ;

ь выберите в меню View / Code или нажмите клавишу F 7 .

Excel запустит Редактор VB (рис. 3).

Project Explorer

Properties Window

Окна Редактора VB

В окне Редактора VB имеются три дочерних окна, каждое из которых отображает важную информацию о VBA-проекте. Project (Проект) - это группа модулей и других объектов, сохраняемых в определенной рабочей книге или шаблоне рабочей книги. Каждое из окон Редактора VB отображается по умолчанию в прикрепленных (docked) положениях (рис. 3).

Если необходимо, вы можете переместить любое из дочерних окон Редактора VB в любое место на экране, перетаскивая строку заголовка (title bar ) этого окна таким же образом, каким бы вы перемещали любое окно на рабочем столе Windows. Перетаскивание одного из дочерних окон из его прикрепленного положения приводит к тому, что оно становится плавающим окном. Плавающие (floating ) окна всегда остаются видимыми поверх других окон. Вы можете также изменять размер любого из дочерних окон Редактора VB, расширяя или уменьшая рамку окна для увеличения или уменьшения его размера, что подобно изменению размера любого окна на рабочем столе Windows.

Project Explorer (Окно проекта) содержит дерево-диаграмму открытых в данный момент файлов (рабочих книг) и объектов, содержащихся в этих файлах (объекты host-приложения, модули, ссылки, формы и так далее). Project Explorer можно использовать для перехода к различным модулям и другим объектам в проекте VB при помощи кнопок (панели инструментов этого окна) = View Code (Программа), ^ View Object (Объект) и Ш. Toggle Folders (Папки).

Properties Window (Окно свойств) содержит все свойства объекта текущего выбора. Вкладка Alphabetic (по алфавиту) этого окна предоставляет список

свойств выделенного объекта, составленный из имен свойств в алфавитном порядке. Вкладка Categorized (по категориям) отображает свойства объекта, отсортированные по категориям.

Code Window - это окно, в котором вы можете просматривать, редактировать или создавать исходный код VBA. В режиме F Full Module View весь исходный код макроса в модуле отображается сразу в прокручивающемся текстовом окне, а макрос отделяется от другого макроса серой линией. Редактор VB позволяет также просматривать содержимое модуля в режиме 1= Procedure View (представление процедуры). Чтобы выбрать режим просмотра, щелкайте кнопки в нижнем левом углу Code Window (рис. 3).

Когда Code Window находится в режиме Procedure View, видимым является исходный код только одного макроса. Используйте раскрывающийся список Procedure (процедура) для просмотра другого макроса. В режиме Full Module View вы можете также использовать раскрывающийся список Procedure для быстрого перехода к отдельному макросу.

Используйте список Object List (объект) для выбора объекта, процедуры которого хотите просмотреть или редактировать. В случае стандартных модулей, таких как модули, в которых сохраняются записанные вами макросы, единственным выбором в списке Object List является General (общая область).

Меню Редактора VB

В Редакторе VB меню File (Файл) предоставляет команды, необходимые для сохранения изменений в проекте VBA и вывода на экран исходного кода вашего макроса VBA. В табл. 1 приведены команды меню File, их горячие клавиши и назначение каждой команды.

Таблица 1 - Команды меню File

Меню Edit (Правка) содержит команды, относящиеся к управлению исходным кодом макроса в Code Window и объектами в формах. В табл. 2 приведены имеющиеся команды меню Edit, их горячие клавиши и описывается действие, выполняемое каждой командой.

Таблица 2 – Команды меню Edit

Меню View (Вид) содержит команды, позволяющие выбирать элементы Редактора VB для просмотра и способ просмотра. В табл. 3 приведены команды меню View, их горячие клавиши и действие, производимое каждой командой.

Таблица 3 – Команды меню View

Команды меню Insert (Вставка) позволяют добавлять различные объекты,

такие как модули и формы, в ваш проект. В меню Insert никакие команды не имеют «горячих клавиш». В табл. 4 приведены действия, выполняемые каждой командой этого меню.

Таблица 4 – Команды меню Insert

Команда Действие

P rocedure… (процедура) UseForm

Вставляет новую процедуру (Sub, Function или Property) в текущий модуль. Процедура – это еще одно название макроса. Добавляет новую форму (используется для создания

пользовательских диалоговых окон) в проект. M odule (модуль) Добавляет новый модуль в проект. Редактор VB дает этому модулю имя в соответствии с правилами, описанными ранее.

(модуль класса) Fil e… (файл)

Добавляет в проект class module (модуль класса ). Модули класса используются для создания пользовательских объектов в вашем проекте.

Позволяет вставлять текстовый файл, содержащий исходный код VBA , в модуль.

Команды меню Format (Формат) используются при создании пользовательских диалоговых окон и других форм. Команды меню Format позволяют выравнивать объекты в форме по отношению друг к другу, настраивать размер элемента управления в соответствии с его содержимым и выполнять многие другие полезные задачи. Команды меню Format представлены здесь для полноты изложения материала, хотя вы не будете их применять до тех пор, пока не начнете создавать собственные пользовательские диалоговые окна. В табл. 5 приведены команды меню Format и их действия. Заметьте, что эти команды не имеют «горячих клавиш».

Таблица 5 – Команды меню Format

Команда Действие

A lign (выровнять)

Открывает подменю команд, которые позволяют выравнивать

выбранные объекты в форме по отношению друг к другу.

Здесь можно выравнивать объекты по верхней/нижней,

правой/левой границам, по центру или середине создаваемого

объекта.

M ake Same Size

(выровнять

размер)

Size to Fit

(подогнать

размер )

Size to Grid

(выровнять

размер по сетке)

Открывает подменю команд, позволяющих изменять размер

выделенных объектов до размера указанного объекта.

Одновременно изменяет ширину и высоту объекта до соответствия размеру его содержимого.

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

Команда Действие

Команды меню Debug (Отладка) используются при выполнении тестирования или отладки макросов. Debugging – так называется процесс нахождения и исправления ошибок в программе. В табл. 6 приведены команды меню Debug, их «горячие клавиши» и выполняемые действия.

Таблица 6 – Команды меню Debug

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

Команды меню Run (Запуск) позволяют начать выполнение макроса, прерывать или возобновлять его выполнение или возвращать прерванный макрос в состояние до выполнения (табл. 7).

Таблица 7 – Команды меню Run

Команды меню Tools (Сервис) не только позволяют выбрать макрос для выполнения, но и получить доступ к внешним библиотекам макросов и дополнительным элементам управления форм (кроме встроенных в VBA). Команды меню Tools обеспечивают также доступ к диалоговому окну Options (параметры) Редактора VB и свойствам проекта VBA текущего выбора в Project Explorer. В табл. 8 приведены команды меню Tools и их действия. Команды меню Tools не имеют «горячих клавиш».

Таблица 8 – Команды меню Tools

В меню Add - lns находится всего одна команда, Add - In Manager , которая приводит к отображению диалогового окна Add - In Manager . Это окно позволяет регистрировать, загружать или выгружать и определять поведение дополнений.

В Редакторе VB имеются два дополнительных меню: Window (окно) и Help (помощь). Оба этих меню содержат команды, идентичные меню Window и Help, имеющимся в других приложениях Microsoft Windows. Команды в меню Window позволяют выбирать активное окно, разбивать текущее окно, размещать дочерние окна вертикально и горизонтально, организовывать дочерние окна VB в виде каскада или выравнивать значки минимизированных дочерних окон. Команды меню Help также идентичны командам меню Help в Word, Excel и других приложениях Microsoft Windows. Меню Help Редактора VB позволяет получать контекстно-зависимую подсказку посредством справочной системы Microsoft Office и просматривать файлы справочной системы VBA для host-приложения, из которого вы запустили Редактор VB. Если у вас имеется модем или доступ к Internet, вы можете использовать Help / MSDN on the Web для соединения с разнообразными страницами Web, содержащими информацию о продуктах Microsoft и VBA. Последняя команда в меню Help – это команда About Microsoft Visual Basic . Она отображает диалоговое окно, содержащее сведения об авторских правах на Microsoft Visual Basic. Диалоговое окно About Microsoft Visual Basic содержит также командную кнопку System Info … , которая отображает информацию о вашей вычислительной системе: какие драйверы видеосистемы, звуковой системы и принтера установлены, какие программы загружены в память в данное время, какие программы зарегистрированы в системном реестре (Windows System Registry) и другую техническую информацию.

Панелями инструментов Редактора VB

По умолчанию Редактор VB отображает только панель инструментов Standard (рис. 4). В конце панели, справа, находится кнопка (со стрелкой) More Buttons . Если вы хотите удалить с панели или добавить некоторые кнопки на панель Standard, нажмите на эту кнопку, а затем укажите соответствующую кнопку в появляющемся меню.

Запуск программы Помощник по Office

Прервать

1- 1

\ h

Дополнительные кнопки элементов

LnS , ColZ

Копировать

Приложение Excel

Повторить

Конструктор

Просмотр объектов

Позиция курсора

Кроме панели Standard Редактор VB предлагает еще три панели: Edit (правка), Debug (отладка) и UseForm .

Вы можете управлять тем, какие панели инструментов отображает Редактор VB с помощью команды View / Toolbars (Вид/Панели инструментов ).

Контрольные вопросы

Что такое макрос?

Что такое макрорекордер? Каковы его возможности?

Как задаются стартовые условия для макроса?

Как записать макрос?

Как выполнить макрос?

Назовите основные компоненты интегрированной среды VBA.

Макрос в «Эксель» — небольшая программка (скрипт) написанная на языке VBA (Visual Basic for Applications) разновидности языка Basic (Бейсик).

В макросах, как правило, прописывают последовательность действий с данными в таблице Excel.

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

Как создать простой макрос.

Создавать макросы в Excel можно двумя способами:

• При помощи опции «Запись макроса»;
• Написать макрос на языке VBA в редакторе макросов и применить его к документу.

Самый простой способ «Запись макроса», его и рассмотрим.

Для начала необходимо включить вкладку «Разработчик», если она не включена.

Необходимо кликнуть правой кнопкой мыши на панели инструментов, выбрать «настройка ленты», в настройках ленты поставить галочку напротив панели «Разработчик» и нажать «ОК».

После того, как включили панель разработчика можно начинать запись макроса.

Чтобы записать макрос, следует:

— при сохранении в «Эта книга» макрос будет работать только в текущем документе;

— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.

1. Можно добавить описание макроса, оно поможет Вам вспомнить, какие действия совершает макрос.
2. Нажать «Ок».
3. Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
4. Когда идет запись, Вы должны совершать требуемую последовательность действий.
5. Когда закончите, нажимайте кнопку остановить запись.

Записанные макросы отображаются в книге макросов.

Чтобы их посмотреть следует нажать кнопку «макросы». В появившемся окне появится список макросов. Выберете нужный макрос и нажмите «Выполнить».

Макросы, находящиеся в книге можно редактировать. Для этого нужно выбрать макрос и нажать кнопку «Изменить». При нажатии на кнопку «Изменить» откроется редактор макросов с записанным на языке VBA скриптом.

Попробуем отредактировать макрос.

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

После корректировок закрываем редактор и пробуем снова запустить макрос.

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

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия "коллекция", то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в "Эксель" используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

Как начать

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

• в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
• выбирают команду Mudule;
• сохраняют, нажав на иконку с изображением ;
• пишут, скажем так, набросок кода.

Он выглядит следующим образом:

Sub program ()

"Наш код

Обратите внимание, что строка «"Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

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

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

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

Для этого:

• открывают вкладку «Вид»;
• переходят на пиктограмму «Макросы»;
• жмут на «Запись макроса»;
• заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

• вновь переходят на строку «Макросы»;
• в списке выбирают «Макрос 1»;
• нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

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

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 - cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

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

Sub programm()

shag = 0.1

Do While x1 < x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (значение x1 записывается в ячейку с координатами (i,1))

Cells(i, 2).Value = y (значение y записывается в ячейку с координатами (i,2))

i = i + 1 (действует счетчик);

x1 = x1 + shag (аргумент изменяется на величину шага);

End Sub.

В результате запуска данного макроса в "Эксель" получаем два столбца, в первом из которых записаны значения для x, а во втором — для y.

Затем по ним строится график способом, стандартным для "Эксель".

Пример 3

Для реализации циклов в VBA Excel 2010, как и в других версиях, наряду с уже приведенной конструкцией Do While используется For.

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

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

For i = 1 to 10 Next

Команда переводится на «человеческий» язык, как «Повторять от 1 до 10 с шагом один».

Если ставится задача получить столбец с квадратами, например, всех нечетных чисел из диапазона от 1 до 11, то пишем:

For i = 1 to 10 step 1 Next.

Здесь step — шаг. В данном случае он равен двум. По умолчанию отсутствие этого слова в цикле означает, что шаг единичный.

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

В целом код будет выглядеть, как:

Sub program()

For i = 1 To 10 Step 1 (можно записать просто For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (т.е. в ячейку (i,1) записывается значение квадрата i)

Next (в некотором смысле играет роль счетчика и означает еще один запуск цикла)

End Sub.

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

Пример 4

В повседневной жизни сплошь и рядом возникает необходимость принять то или иное решение в зависимости от какого-то условия. Не обойтись без них и в VBA Excel. Примеры программ, где дальнейший ход выполнения алгоритма выбирается, а не предопределен изначально, чаще всего используют конструкцию If …Then (для сложных случаев) If …Then …END If.

Рассмотрим конкретный случай. Предположим, необходимо создать макрос для "Эксель", чтобы в ячейку с координатами (1,1) было записано:

1, если аргумент положительный;

0, если аргумент нулевой;

-1, если аргумент отрицательный.

Создание такого макроса для "Эксель" начинается стандартным способом, через использование «горячих» клавиш Alt и F11. Далее записывается следующий код:

Sub program()

x= Cells(1, 1).Value (эта команда присваивает x значение содержимого ячейки с координатами (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

Остается запустить макрос и получить в "Эксель" нужное значение для аргумента.

Функции VBA

Как вы уже могли заметить, программировать в самом известном табличном процессоре Microsoft не так уж сложно. Особенно, если научиться применять функции VBA. Всего в этом языке программирования, созданном специально для написания приложений в "Эксель" и Word, около 160 функций. Их можно разделить на несколько больших групп. Это:

• Математические функции. Применив их к аргументу, получают значение косинуса, натурального логарифма, целой части и пр.
• Финансовые функции. Благодаря их наличию и используя программирование в Excel, можно получать эффективные инструменты для ведения бухгалтерского учета и осуществления финансовых расчетов.
• Функции обработки массивов. К ним относятся Array, IsArray; LBound; UBound.
• Функции VBA Excel для строки. Это достаточно многочисленная группа. В нее входят, например, функции Space для создания строки с числом пробелов, равных целочисленному аргументу, или Asc для перевода символов в код ANSI. Все они имеют широкое применение и позволяют работать со строками в "Эксель", создавая приложения, значительно облегчающие работу с этими таблицами.
• Функции преобразования типа данных. Например, CVar возвращает значение аргумента Expression, преобразовав его в тип данных Variant.
• Функции работы с датами. Они значительно расширяют стандартные Так, функция WeekdayName возвращает название (полное или частичное) дня недели по его номеру. Еще более полезной является Timer. Он выдает число секунд, которые прошли с полуночи до конкретного момента дня.
• Функции для преобразования числового аргумента в разные системы счисления. Например, Oct выдает в восьмеричное представление числа.
• Функции форматирования. Важнейшей из них является Format. Она возвращает значение типа Variant с выражением, отформатированным согласно инструкциям, которые заданы в описании формата.
• и пр.

Изучение свойств этих функций и их применение позволит значительно расширить сферу применения "Эксель".

Пример 5

Попробуем перейти к решению более сложных задач. Например:

Дан бумажный документ отчета фактического уровня издержек предприятия. Требуется:

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

Рассмотрим один из вариантов решения.

Создание шаблона

Все действия осуществляются на стандартном листе в Excel. Резервируются свободные ячейки для внесения данных по названию компании-потребителя, сумме издержек, их уровня, товарооборота. Так как количество компаний (обществ), относительно которых составляется отчет, не зафиксировано, ячейки для внесения значений по итогам и ФИО специалиста заранее не резервируют. Рабочему листу присваивается новое название. Например, "Օтчет".

Переменные

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

• NN- номер текущей строки таблицы;
• TP и TF - планируемый и фактический товарооборот;
• SF и SP - фактическая и планируемая сумма издержек;
• IP и IF - планируемый и фактически уровень издержек.

Обозначим теми же буквами, но с «приставкой» Itog накопление итога по данному столбцу. Например, ItogTP - касается столбца таблицы, озаглавленного, как «планируемый товарооборот».

Решение задачи с использованием программирования на VBA

Используя введенные обозначения, получаем формулы для отклонений. Если требуется осуществить расчет в % имеем (F - P) / P * 100, а в сумме — (F - P).

Результаты этих вычислений можно лучше всего сразу внести в соответствующие ячейки таблицы "Эксель".

Для итогов по факту и прогнозу получают по формулам ItogP=ItogP + P и ItogF=ItogF+ F.

Для отклонений используют = (ItogF - ItogP) / ItogP * 100, если расчет ведется в процентах, а в случае суммарной величины — (ItogF - ItogP).

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

Перед запуском созданной программы, требуется сохранить рабочую книгу, например, под названием "Отчет1.xls".

Клавишу «Создать отчетную таблицу» требуется нажать всего 1 раз после ввода заголовочной информации. Следует знать и другие правила. В частности, кнопка «Добавить строку» должна нажиматься каждый раз после ввода в таблицу значений по каждому виду деятельности. После занесения всех данных требуется нажать кнопку «Закончить» и затем переключиться в окно "Эксель".

Теперь вы знаете, как решать задачи для Excel с помощью макросов. Умение применять vba excel (примеры программ см. выше) может понадобиться и для работы в среде самого популярного на данный момент текстового редактора "Ворд". В частности, можно путем записи, как показано в самом начале статьи, или через написание кода создавать кнопки меню, благодаря которым многие операции над текстом можно будет осуществлять нажатием дежурных клавиш или через вкладку "Вид" и пиктограмму "Макросы".

Что такое макрос? Макрос — это сохраненная последовательность действий или программа, созданная на языке VBA (Visual Basic for Application). Частый вопрос как написать макрос в Excel? Просто.

Т.е. если нам необходимо выполнять одни и те же действия несколько раз, мы можем запомнить эти действия и запускать их одной кнопкой. Я не буду заставлять вас учить язык VBA и даже не буду предлагать много стандартных макросов в этой статье. В действительности создать/написать макрос в Excel сможет действительно каждый. Для этого существует самая интересная и необычная возможность Excel — Макрорекордер (запись ваших действий в виде кода). Т.е. вы можете записать свои действия, как на видео и перевести их в код (последовательность).

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

1.Разрешить использование макросов

Меню (круглая кнопка вверху слева) — Параметры Excel — Центр управления безопасностью — Параметры центра управления безопасностью — Параметры макросов. Поставьте флажок «Включить все макросы».

Или сделать тоже самое на вкладке Разработчик

2. Включить меню Разработчик для быстрой работы с макросами

Меню (круглая кнопка вверху слева) — Параметры Excel — Основные — Показывать вкладку Разработчик.

Чтобы созданный макрос сохранился в книге, необходимо сохранить файл в .xlsm или.xlsb. Нажмите Сохранить как — Книга Excel с поддержкой макросов или Двоичная книга.

Итак, как написать макрос в Excel?

Все просто. Заходим в Excel. Внизу страницы, под ярлычками листов есть кнопка «Запись макроса».

Нажимаем кнопку, открывается окно «Запись макроса». Называем макрос как нам нравится в поле Имя макроса:. Задаем сочетание клавиш, которыми потом будем вызывать его (не обязательно).

Жмем ОК. Начались запись Макрорекордера.

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

Во время этих манипуляций вместо кнопки «Запись макроса» под листами, будет гореть квадратик, нажав на который вы остановите выполнение макроса.

Как запустить то, что получилось? Нажмите кнопку , появится окно Выбор макроса, выбирите нужный макрос, а затем щелкните кнопку выполнить.

Как посмотреть что получилось? Нажмите кнопку . Выберите нужный макрос и нажмите изменить. Откроется окно записи макросов (окно VBA)

Код макроса должен получиться примерно следующим.

‘ — символ комментария, т.е. эта строка не участвует в коде. Для макроса обязательно наличие имени (Sub Пример1()) и окончания (End Sub).

Как создать кнопку для макроса можно прочитать .

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

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

Для этого применяется файл Personal.

Как запустить макрос по условию можно прочитать

Удачи, уверен вам это здорово пригодиться!

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

Итак, что такое макросы и как их писать?

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

Макросы пишутся на языке VBA - Visual Basic for Applications. Эту аббревиатуру стоит запомнить и использовать в поисковых запросах при поиске нужной информации. VBA - объектно-ориентированный, иерархический язык. Это значит, что управлять придется объектами, подчиненными друг другу. Например, книга Excel - объект. В ней есть листы, на листах есть ячейки. Листы, ячейки, диапазоны и многое другое - это объекты. Подчиненность можно примерно приравнять вложенности - например, ячейка вложена в лист, а лист в книгу.

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

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

Итак, объекты "встроены" друг в друга и имеют различные свойства. Познакомимся с ними поближе.

Не будем здесь приводить весь список, потому что он огромен. Ограничимся тем, что понадобится даже на первом этапе.

Объекты:

Workbook - рабочая книга Excel.

Sheet - лист.

Range - диапазон.

Cell - ячейка.

Row - строка.

Column - столбец.

Действия с объектами

Activate - активировать, то есть, "поставить курсор". Активировать можно книгу, лист, ячейку.

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

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

Copy - копировать.

И отдельно идет действие Paste - вставить. Если за всеми предыдущими действиями стоит слово "что?" (что активировать, что скопировать), то за словом вставить идет вопрос "куда?". Поэтому и при написании программы нужно указывать не что вставить, а куда вставить.

Кроме выполнения программ Excel может "отдавать информацию" по заданным командам. Вот несколько примеров таких команд:

Sheets.Count - выдает количество листов в книге.

Date - выдает сегодняшнюю дату в формате строки.

Len("строка") - выдает длину строки в количестве символов. В этом примере длина равна 6.

Теперь нужно пару слов сказать о типах данных (переменных).

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

Все строковые величины в VBA пишутся в кавычках. Все названия/имена книг или листов - это текст, то есть строковая величина, поэтому всегда должна обрамляться кавычками.

И числа, и строки можно присваивать переменным.

Например:

S="Привет"

Можно переопределять значения переменных, например, так:

I=i+10

S=s & ", мир!"

После этого i=11, а s="Привет, мир!".

Тут мы столкнулись с первой операцией над строковыми величинами. Знак & означает, что две строки нужно соединить. Порядок важен: если написать

s=", мир!" & s, то получим, s=", мир!Привет".

Так как переменная s уже хранит в себе кавычки, при её использовании не нужно заключать её в кавычки. Наоборот, именно отсутствие кавычек подскажет Excel"ю, что воспринимать её нужно как переменную, а не как текст. То есть, записи:

S="Привет, мир!"

H="Привет, мир!"

дадут одинаковый результат - присвоят переменной h значение "Привет, мир!"

Но запись

S="Привет, мир!"

H="s"

присвоит переменной h значение "s".

Надо сказать, что объекты в Excel иногда пишут в единственном числе, иногда во множественном. Как запомнить, в каком случае что используется? Можно использовать такое правило: всё, чего в Excel"е много, пишется во множественном числе, всё, что в единственном экземпляре - в единственном. В Excel"е много книг, много листов и очень много ячеек. Все они одинаковы для Excel"я и отличить их можно только по имени или координатам. Поэтому в программе используется множественное число. Например:

Workbooks("Книга1").Activate

Sheets("Лист1").Copy

Rows(1).Delete

Ячейки определяются по координатам: первая - номер строки, вторая - столбца.

Например, команда

Cells(1,1).Activate

поставит курсор в левую верхнюю ячейку.

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

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

I=ActiveCell.Row

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

Например, команда

Cells(1,1).Copy

скопирует верхнюю левую ячейку на активном листе.

А команда

Sheets("Лист1").Cells(1,1).Copy

скопирует верхняя левую ячейку на листе "Лист1", независимо от того, активен этот лист сейчас или нет.

После этого вы уже можете писать макросы:)

Но лучше прочитайте еще про циклы и условный оператор , а потом про то, что такое коллекции объектов и что они нам могут дать.