Работа с макросами в Excel

Работа с макросами в Excel.

• Создание макроса.
• Запуск макроса.
• Назначение макроса кнопке или объекту.
• Использование Visual Basic for Applications.

Создание макроса.

В Excel, так же как и в любом другом пакете Microsoft Office, существует возможность сохранить набор проделанных операций и, в дальнейшем, выполнить его, используя только одну команду. Такая последовательность команд и функций хранится в модуле Visual Basic и называется макросом. Пользователь может сам создать макрос, выполнив следующие шаги:

• Спланируйте всю последовательность действий, которые будут выполняться в макросе. Имейте в виду, что записаны будут все Ваши действия, включая отмену операций и исправление ошибок.
• Выполните последовательно команды Macro (Макрос), Record New Macro (Начать запись) меню Tools (Сервис). Появится окно диалога Record Macro (Запись макроса).
• Введите в поле ввода Macro name (Имя макроса) название создаваемого макроса.
  Первым символом в имени должна быть буква, пробелы не допускаются (используйте знак подчеркивания вместо пробела).
  Чтобы впоследствии запускать макрос с помощью сочетания клавиш, введите букву в поле Shortcut key (Сочетание клавиш). При назначении клавиш быстрого вызова учитывается различие между прописными и строчными буквами. Для строчных букв используется сочетание CTRL+буква, для заглавных -CTRL+SHIFT+буква. Цифры и специальные знаки в качестве клавиш быстрого вызова не допускаются.
  В поле Store macro in (Сохранить в книге) выберите книгу, в которой Вы хотите сохранить этот макрос.
  Помимо стандартной информации (когда и кем был записан макрос), Вы можете ввести в поле ввода Description (Описание) краткое описание назначения макроса. После этого нажмите кнопку OK.
  
• Начиная с этого момента, все Ваши действия будут записаны в макросе. Признаком того, что Вы работаете в режиме записи макроса является слово Recording (Запись), появившееся в строке состояния. При этом на экране отобразится специальная панель инструментов Stop Recording (Остановка записи). Вы можете выключить эту панель инструментов, если она Вам не нужна. Однако имейте в виду, что по умолчанию при записи используются абсолютные ссылки, т.е. при выполнении макроса будут обрабатываться те же самые ячейки, которые обрабатывались при записи. Чтобы макрос обрабатывал ячейки, определяя их местоположение относительно активной ячейки, необходимо включить опцию Relative Reference (Относительная ссылка), кликнув на соответствующей кнопке панели инструментов Stop Recording (Остановка записи).
• После того, как Вы выполнили все операции, составляющие запоминаемый набор, запись макроса необходимо остановить. Для этого достаточно кликнуть на кнопке Stop Recording (Остановить запись) панели инструментов Stop Recording (Остановка записи). Если Вы выключили эту панель, воспользуйтесь меню Tools (Сервис), выбрав последовательно команды Macro (Макрос), Stop Recording (Остановить запись).
  

Запуск макроса.

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

• Запуск макроса с помощью клавиш быстрого вызова
  Если при записи макроса Вы определили клавиши быстрого вызова, заполнив поле ввода Shortcut key (Сочетание клавиш), достаточно нажать это сочетание клавиш для запуска макроса.
• Запуск макроса путем выбора имени
  Активизируйте последовательно команды Macro (Макрос), Macros (Макросы) меню Tools (Сервис), выберите имя нужного макроса и нажмите кнопку Run (Выполнить).
  С помощью диалогового окна Macro (Макрос) Вы можете удалить макрос, который Вам больше не нужен, изменить макрос средствами встроенного редактора Visual Basic или войти в макрос для построчной отладки.
• Запуск макроса с помощью кнопок и графических объектов
  Вы можете создать кнопку или графический объект на рабочем листе и присвоить макрос этому элементу. Вы можете, также, присвоить макрос любой кнопке на панели инструментов. В этом случае, кликнув рабочей клавишей мыши на соответствующем объекте, Вы запустите связанный с ним макрос.

Назначение макроса кнопке или объекту.

Назначение макроса кнопке на рабочем листе

Microsoft Excel предоставляет пользователю возможность создавать свои электронные формы для ввода данных. Для этого предназначены панели инструментов Forms (Формы), Control Toolbox (Элементы управления). Остановимся подробнее на процессе создания кнопки с помощью панели инструментов Forms (Формы):

• Выберите инструмент Button (Кнопка) на панели инструментов Forms (Формы).
• Нарисуйте кнопку на рабочем листе.
• В окне диалога Assign Macro (Назначить макрос объекту) выберите макрос и нажмите кнопку OK.
• Кликните мышкой вне созданной кнопки.
• Кнопка с назначенным ей макросом создана. Вы можете изменить текст на кнопке, назначить ей другой макрос или удалить кнопку с рабочего листа, выбрав соответствующие команды из контекстного меню. Для вызова контекстного меню необходимо кликнуть на кнопке правой клавишей мышки. При этом вокруг кнопки появятся позиционные маркеры, с помощью которых Вы можете менять ее размер.

Назначение макроса кнопке на панели инструментов

Кнопку, созданную на рабочем листе, нельзя перетащить на панель инструментов. Однако Вы можете сформировать панель инструментов по своему усмотрению и назначить необходимый макрос любой кнопке на этой панели. Для этого последовательно активизируйте команды Toolbars (Панели инструментов), Customize (Настройка) меню View (Вид). Затем щелкните правой клавишей мыши на нужной кнопке и выберите пункт Assign Macro (Назначить макрос) в появившемся меню.

Назначение макроса графическому объекту

Кликните на графическом объекте правой клавишей мыши и выберите команду Assign Macro (Назначить макрос) из появившегося контекстного меню.

Использование Visual Basic for Applications.

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

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

Сначала содержимое VBA-модуля не отображается в этом окне. Чтобы начать редактирование, необходимо в окне Project (Проект) открыть папкуModules (Модули) и выбрать имя модуля. В рабочем поле Visual Basic откроется окно, содержащее текст макроса.

Чтобы требуемый модуль сразу появился в рабочем поле Visual Basic, кликните на кнопке Edit (Изменить) диалогового окна Macro (Макрос).

Создадим для примера простой макрос, не прибегая к услугам Visual Basic.

Макрос включает следующие действия:

1. добавление рабочего листа;
2. ввод текста в первую ячейку;
3. показ рабочего листа в режиме предварительного просмотра.

На рисунке показано как выглядит этот макрос в окне Visual Basic. Рассмотрим процедуру построчно:

1. активизируется рабочий лист Лист2;
2. добавляется новый рабочий лист;
3. активизируется ячейка A1;
4. в активную ячейку вводится текст Новый текст;
5. активный лист в текущем окне отображается в режиме предварительного просмотра.

Электронные формы в Excel.

• Панель инструментов "Формы".
• Панель инструментов "Элементы управления".
• Создание пользовательской формы средствами VBA.

Microsoft Excel хранит информацию в ячейках рабочего листа. Однако не всегда удобно вводить данные прямо в ячейки, Вы можете упростить этот процесс, автоматизировав ввод часто встречающихся данных или, предлагая выбрать значение из списка. Поля, вычисляемые по достаточно сложному алгоритму, имеет смысл рассчитывать с помощью процедуры, запускаемой нажатием определенной кнопки. Все эти возможности реализуются через элементы управления, совокупность которых образует электронную форму. В Excel существуют две встроенные панели инструментов, которые предназначены для создания электронных форм: Forms (Формы) и Control Toolbox (Элементы управления). Кроме того, Вы можете создать пользовательскую форму средствами встроенного редактора Visual Basic.

Панель инструментов "Формы".

Рассмотрим несколько примеров:

Флажок

• Выберите элемент управления Check Box (Флажок) на панели инструментов Forms (Формы).
• Щелкните в том месте рабочего листа, где Вы хотите видеть флажок.
• Кликните правой клавишей мышки на флажке и выберите в появившемся контекстном меню пункт Format Control (Формат объекта).
• Задайте свойства переключателя в диалоговом окне Format Control (Формат элемента управления).
• Введите адрес какой-либо ячейки в поле Cell link (Связать с ячейкой). В указанной ячейке будет храниться значение, соответствующее состоянию флажка (ИСТИНА, если флажок установлен и ЛОЖЬ в противном случае).

Группа переключателей

• Выберите элемент управления Group Box (Группа) на панели инструментов Forms (Формы).
• Нарисуйте на рабочем листе рамку, в которую будут помещены переключатели, составляющие определенную группу.
• Выберите на панели инструментов элемент управления Option Button (Переключатель) и щелкните внутри рамки там, где должен находиться переключатель.
• Повторите процедуру для каждого переключателя этой группы.
• Выберите команду Format Control (Формат объекта) из контекстного меню любого переключателя и введите адрес ячейки, в которой будет храниться номер выбранного переключателя, в поле Cell link (Связать с ячейкой).

Список

• Выберите элемент управления List Box (Список) на панели инструментов Forms (Формы).
• Нарисуйте на рабочем листе рамку, в которую будет помещен список.
• Кликните правой клавишей мышки на списке и выберите в появившемся контекстном меню пункт Format Control (Формат объекта).
• В поле Input range (Формировать список по диапазону) укажите адрес диапазона, в котором находятся элементы списка на рабочем листе.
• Введите адрес какой-либо ячейки в поле Cell link (Помещать результат в ячейку). В указанной ячейке будет храниться номер элемента, выбранного из списка.

Панель инструментов "Элементы управления".

Элементы, расположенные на панели инструментов Control Toolbox (Элементы управления), называются элементами Active X. Они несколько отличаются от элементов управления, расположенных на панели Forms (Формы). Скорее они ближе к элементам управления Visual Basic, т.к. при добавлении объекта Active X на рабочий лист создается макрос, который сохраняется вместе с этим элементом, а не только запускается при его выборе. При копировании или перемещении такого объекта автоматически будут скопированы или перемещены все процедуры, связанные с ним.

Некоторые элементы управления, имеющиеся на панели инструментов Control Toolbox (Элементы управления), отсутствуют на панели Forms (Формы):

• Text Box (Поле) - поле, в которое можно ввести текст.
• Toggle Button (Выключатель) - кнопка, которая остается нажатой, если Вы ее выбираете. Чтобы выключить эту кнопку, необходимо нажать ее еще раз.
• Image (Рисунок) - элемент управления, который позволяет внедрить рисунок в форму.
• More Controls (Дополнительные элементы) - список дополнительных элементов Active X.

Для редактирования элемента Active X необходимо войти в Design Mode (Режим конструктора), выбрав соответствующий инструмент на панели Control Toolbox (Элементы управления). Чтобы сделать элемент активным, режим конструктора следует отключить. Выбрав кнопку Properties (Свойства) на этой панели инструментов, Вы сможете задать свойства элемента Active X, инструмент (Исходный текст) позволяет просмотреть модуль Visual Basic, содержащий текст макроса, связанного с элементом.

Рассмотрим простой пример. Попробуем создать элемент Toggle Button (Выключатель) на рабочем листе:

• Выберите инструмент Toggle Button (Выключатель) на панели Control Toolbox (Элементы управления) и кликните мышкой в том месте рабочего листа, где Вы хотите видеть выключатель. При этом автоматически активизируется кнопка Design Mode (Режим конструктора). Редактирование и изменение свойств элементов управления происходит именно в этом режиме.
• Нажмите на кнопку Properties (Свойства) панели инструментов, появится окно, в котором Вы можете задать свойства объекта. В этом окне слева находится список свойств, которыми обладает данный объект, а справа задаются значения этих свойств. Например, установив LinkedCell=A1, Вы сможете определить состояние выключателя с помощью связанной ячейки (если в ячейке A1 хранится значение ИСТИНА, значит выключатель нажат). Свойство Picture позволяет выбрать картинку, которая будет помещена на объекте.
• Выберите инструмент View Code (Исходный текст) для того, чтобы отредактировать текст программы связанной с элементом Active X. Здесь Вы можете установить поведение объекта в связи с различными событиями (клик, двойной клик, нажатие рабочей клавиши мышки, освобождение рабочей клавиши мышки). Например, действия, которые должны произойти при клике на выключателе ToggleButton1 описываются в процедуре ToggleButton1_Click.
• Отожмите кнопку Design Mode (Режим конструктора) на панели инструментов Control Toolbox (Элементы управления) для того, чтобы сделать выключатель активным.

Подобным способом можно создать элемент управления List Box (Список). Диапазон данных для заполнения списка, созданного с помощью панели инструментов Control Toolbox (Элементы управления), определяется с помощью свойства ListFillRange категории Misc (Разное). Список можно связать с ячейкой рабочего листа, и выбранный из списка элемент будет отображен в этой ячейке (свойство LinkedCell категории Misc (Разное)). Обратите внимание, что связанная ячейка в этом случае хранит само выбранное значение, а не его номер в списке.

Создание пользовательской формы средствами VBA.

Описанными выше методами Вы можете создать достаточно простые электронные формы, хранящиеся на определенных листах рабочей книги. Однако может возникнуть ситуация, когда Вы заранее не знаете, как именно должна выглядеть форма, а определяете это в процессе работы. Например, если Вы заполняете бланк заказа товара, необходимо вводить данные в зависимости от вида предполагаемой покупки (если Вам нужна обувь, видимо, не имеет особого значения рост или объем груди). То есть количество и вид элементов управления заранее не известны. В этом случае Вам поможет объект VBA UserForm (пользовательская форма), который имеет определенные свойства и методы. Его можно создать программно, описав элементы управления и поведение в зависимости от различных событий. Это достаточно сложно, поскольку требует знания языка Visual Basic for Application (Visual Basic для приложений). Однако простую пользовательскую форму можно создать, обладая минимальным опытом работы со встроенным Visual Basic. Такая форма будет иметь заданную конфигурацию, но не зависеть от определенного листа рабочей книги. Вы сможете инициализировать ее, описав элементы управления до открытия формы. Так как пользовательская форма открывается в отдельном окне, можно скрыть или показать ее на экране одновременно с рабочей книгой. То есть, если Вы хотите, чтобы электронная форма жила своей собственной жизнью, создайте ее средствами VBA.

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

• Откройте окно Microsoft Visual Basic, выбрав последовательно команды Macro (Макрос), Visual Basic Editor (Редактор Visual Basic) из меню Tools (Сервис).
• В меню Insert (Вставка) выберите пункт UserForm. Появится макет будущей формы, которая по умолчанию называется UserForm1. Вы можете поменять имя, указав его в окошке свойств справа от поля (Name). Не путайте имя пользовательской формы со свойством Caption, которое определяет подпись объекта (заголовок).

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

Теперь, с помощью панели инструментов Control Toolbox (Элементы управления), следует поместить на форму элементы управления.

• Выберите элемент управления Combo Box (Поле со списком) на панели инструментов.
• Щелкните на макете мышкой в то место, где Вы хотите видеть этот элемент.
• Кликните правой клавишей мышки на объекте и выберите в появившемся контекстном меню пункт Properties (Свойства). Появится окошко, в котором следует задать свойства элемента управления.
• По умолчанию полю со списком присваивается имя ComboBox1. Вы можете определить другое имя, указав его справа от поля (Name).
• Определим ячейки, которые будут хранить элементы нашего списка и выбранное значение:
• свойство ControlSource задает адрес ячейки, в которой хранится выбранное значение. Содержимое этой ячейки, кроме того, отражается в текстовом поле нашего элемента. Введем, например, B2 в это поле;
• свойство RowSource определяет источник элементов списка (предположим, что список содержится в диапазоне A1:A3 на текущем листе).

Теперь надо сделать так, чтобы пользовательскую форму можно было запускать непосредственно из Excel:

• В меню Insert (Вставка) выберите пункт Module (Модуль). Обратите внимание, что проект рабочей книги пополнился еще одной папкой Modules (Модули).
• Наберите текст процедуры, как показано на рисунке.
• Теперь непосредственно в Excel мы можем пользоваться нашей формой, запуская ее как макрос. Она будет фигурировать под именем Show_Form или под тем, которое Вы сами ей дадите.