Как написать и использовать собственную надстройку в Excel

Как написать и использовать собственную надстройку в Excel

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

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

Если макросов много, а используются они часто более рационально будет создание собственной надстройки Excel — отдельного файла особого формата (xla – Excel2003, xlam – Excel2010 и старше), содержащего все макросы.

Плюсы такого подхода очевидны:

• больше не будет срабатывать защита от макросов, потому что надстройки по определению входят в доверенные источники;
• однократное подключение надстройки в Excel. Теперь её VBA процедуры и функции можно будет использовать в любом файле на компьютере. Сохранять ваши рабочие файлы в xlsm- и xlsb-форматы, также не требуется потому, что исходный текст будет храниться не в них, а в файле надстройки.
• можно сделать отдельную вкладку на ленте Excel для запуска макросов надстройки.
• Надстройку легко переносить с компьютера на компьютер — это отдельный файл.

Рассмотрим процесс создания своей собственной надстройки для Excel на примере Excel 2010.

Почему макросы используются в Excel?

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

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

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

• Импорт данных
• Форматирование его в соответствии с требованиями вашей бизнес-отчетности.

Второй вид защиты — Project is Unviewable

Время на снятие: от 10 до 15 мин (в ручную)

Недостаток: доступ к исходному коду модуля VBA

Один из самых распространённых видов защит. Встречается в 95% файлах с защитой модуля VBA. При попытке открыть проект, открывается диалоговое окно, с сообщением: Project is Unviewable.

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

Основан, данный вид защиты, на изменение ключей:

в файле vbaProject.bin .

Сохранение файла Excel с названием, которое сформировано из значений двух ячеек

Теперь представим, что нам нужно сформировать файл с названием из значений двух ячеек. Например, в нашем случае это может быть «Марка Авто – VIN Номер», в качестве разделителя я указал символ – (дефис), но им может выступать любой символ или вовсе отсутствовать.

В этом примере я покажу, как можно это реализовать с привязкой к конкретным ячейкам, в нашем случае B14 и D14.

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

Все ОК, файл создан.

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

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

Код процедуры

Становитесь на любую ячейку со значением в столбце B, и запускайте макрос.

Exceltip

VBA считается стандартным языком написания сценариев для приложений Microsoft, и в настоящее время он входит в состав всех приложений Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию макросов для других программных продуктов Microsoft. Более того, вы сможете создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.

Как включить макросы в Excel

По умолчанию, вкладка, отвечающая за управление и навигацию макросов в Excel, скрыта. Чтобы активировать данную опцию перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры Excel, перейдите по вкладке Настройка ленты, в правом поле со списком ставим маркер напротив вкладки Разработчик. Данные действия актуальны для версий Excel 2010 и старше.

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

Написание макросов в Excel

Во вкладке Разработчик в группе Код, нажмите кнопку Запись макроса. Появиться диалоговое окно Запись макроса, которая запрашивает некоторую информацию о будущем записываемом коде. Если вы впервые создаете макрос, можете просто нажать кнопку ОК. С данного момента Excel будет фиксировать каждое действие пользователя в модуле VBA, будь то ввод данных, форматирование или создание диаграмм. Чтобы остановить запись макроса, нажмите кнопку Остановить запись, которая находится в той же группе Код.

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

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

Редактирование макросов

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

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

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

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module1. Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Если вы посмотрите на приведенный выше код, вы увидите, что макрос определенным образом структурирован. Сначала приложение перемещает курсор на ячейку с помощью команды Range("A1").Select, затем редактирует его содержимое с помощью ActiveCell.FormulaR1C1 = "1". Таким образом, для оставшихся шагов мы можем повторить эти действия, меняя адрес ячейки и значение, которое вы хотите записать в эту ячейку. Например, чтобы задать ячейке A4 значение 4, вы должны написать:

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

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

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

Увеличение скорости выполнения макросов Excel

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

Использование команды Application.ScreenUpdating

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

Команда Application.ScreenUpdating говорит Excel, чтобы он прекратил выводить пересчитанные данные на экран, а выдал готовые значения в конце выполнения кода.

Использование команды Application. Calculation

Вторая хитрость заключается в отключении автоматических вычислений. Давайте я поясню. Каждый раз, когда пользователь или процесс обновляет ячейку, Excel пытается пересчитать все зависимые от нее ячейки. Так скажем, если ячейка, которую пытается обновить макрос, влияет на 10000 остальных ячеек, Excel будет пытаться пересчитать их все до того, как закончится выполнение кода. Соответственно, если существует целый ряд влияющих ячеек, пересчет может значительно замедлить выполнение кода. Чтобы этого не происходило, вы можете установить команду Application. Calculation в начале кода, которая переключит пересчет формул в ручной режим, а затем вернуть автоматическое вычисление в конце макроса.

Будьте внимательны, не забудьте переключить данную опцию снова в автоматический режим в конце макроса. В противном случае, вам необходимо будет это сделать в самом Excel, перейдя по вкладке Формулы в группу Вычисление и выбрать Параметры вычислений –> Автоматический.

Избежание выбора ячеек и диапазонов

В режиме автоматической записи макросов, вы можете заметить, что Excel очень часто использует команду выбора ячеек, например, Range(«A1»).Select. В нашем примере, мы использовали данную команду несколько раз, чтобы выбрать ячейку и изменить ее значение. Вы можете избежать этого просто указав адрес ячейки и задав ей необходимое значение (Макрос записал движение курсора от одной ячейки к другой, следовательно, вставил эти шаги. Однако они не являются необходимыми). Так что, более эффективный код будет выглядеть следующим образом.

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

Примеры макросов Excel

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

Способ 4

Для переноса текста в ячейке используется еще одна формула – СЦЕПИТЬ(). Давайте возьмем только первую строку с заголовками: Фамилия, Долг, К оплате, Сумма. Кликните по пустой ячейке и введите формулу:

Вместо A1, B1, C1, D1 укажите нужные Вам. Причем их количество можно уменьшить или увеличить.

Результат у нас получится такой.

Поэтому открываем уже знакомое окно Формата ячеек и отмечаем пункт переноса. Теперь нужные слова будут начинаться с новых строчек.

В соседнюю ячейку я вписала такую же формулу, только указала другие ячейки: A2:D2.

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

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

Исходный текст в excel

Многие из вас наверняка пробовали свои силы в написании макросов в Word, Excel, Access и других продуктах Microsoft. И немало программистов завидовало Word’у и мечтало встроить поддержку макрокоманд и в свои приложения

С чего начать

Конечно, выбор, как всегда, есть.

Самый трудный путь — это написание собственного интерпретатора макрокоманд. Естественно, возиться с написанием собственных разборщиков синтаксиса, исполнителей команд и т.д., не каждый захочет — вот если бы найти такой компонент, который принимал бы исходный текст макроса и выводил результат. К счастью несчастных программистов и здесь на помощь пришла всеми любимая Microsoft, разработавшая Windows Script Control — компонент, который соответствует практически всем требованиям, выдвигаемым к поддержке макросов в ваших программах.

Итак, для начала нам понадобится сам компонент Windows Script Control, который можно загрузить с сайта разработчика (название архива — sct10en.exe). Распаковав его, мы увидим собственно компонент msscript.ocx и дополнительные файлы справки.

Теперь смело можно браться за разработку поддержки макросов в вашем приложении.

Пример 1

К этому времени вы уже запустили среду разработки (которая, естественно, должна поддерживать работу с ActiveX, к примеру, Delphi) и создали новое приложение (New > Application).

Теперь нужно импортировать данные из библиотеки msscript.ocx в наш проект. Для этого воспользуемся пунктом меню Project > Import Type Library — и для достоверности выберем нашу библиотеку, щелкнув на кнопке Add…(рис. 1). После чего выберем файл msscript.ocx.

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

После выполнения этого кода увидим на экране сообщение системы (Testing:This is the Simple Test).

Теперь рассмотрим приведенный выше код более подробно. Сначала создаем объект TScriptControl, который, собственно, и проделывает за нас всю грязную работу. Далее присваиваем свойству Language значение «VBScript», уведомляя тем самым компонент о том, что переданный ему код будет написан на Visual Basic. Помимо VBScript: тут возможны и другие значения: можно, например, воспользоваться Jscript — при этом будет использован синтаксис JavaScript или же синтаксис любого другого интерпретатора, поддерживающего технологию ActiveX-скриптов (Visual Basic, Java, ActivePython, ActivePerl и т.п).

В следующих строчках пишем исходный код функции DoSmth, которая возвращает нам вторую часть предложения. Далее записываем этот код в компонент — а в следующей строчке исполняем его, передавая возвращаемое им значение в функцию MsgBox. Все это пишется с использованием синтаксиса Visual Basic. Функции AddCode и ExecuteStatement имеют следующий вид:

Где Code — код процедуры, функции (или любого их сочетания в любом количестве), который записывается в компонент и после этого может быть вызван с помощью ExecuteStatement или Run:

Где Statement — текст программы, который будет сразу же исполнен.

Пример 2

Осуществлять вывод сообщений при помощи макроязыка мы уже научились, однако это не единственная возможность компонента. Так, компонент TScriptControl представляет нам возможность использования собственной объектной модели в создаваемых макросах — то есть доступ к специфическим объектам нашего приложения. Для этого в нашем приложении потребуется сначала создать объект автоматизации Automation Object (пользователи Microsoft Visual Basic могут пропустить этот раздел, так как в Visual Basic поддержка объектов автоматизации встроена изначально). Чтобы создать этот объект, при открытом приложении щелкнем на пункте меню Новый и выберем закладку ActiveX. Здесь выберем пункт Automation Object.

Далее предстоит создать интерфейс, который мы собираемся включить в объектную модель ScriptControl. Для начала просто создадим объект с единственной функцией print, которая будет выводить в компонент TlistBox, размещенный на главной форме, некоторый текст. Все существенные настройки показаны на рис. 2.

Далее обновляем информацию об объекте, щелкнув для этого на соответствующей кнопке (рис. 2), и переходим к секции реализации объекта.

Здесь уже нас поджидает созданный средой разработки шаблон, в который остается внести только некоторые исправления:

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

Как и в прошлый раз, создадим на нашей главной форме кнопку и объект ListBox1. Затем в реакцию кнопки на нажатие напишем следующий код:

Опять же, как и в прошлый раз, сначала создаем компонент ScriptControl, затем инициализируем интерфейс ISimpleText и добавляем его в нашу объектную модель посредством функции:

• Name — название нашего компонента во внутреннем пространстве имен.
• Object — ссылка на наш объект.
• AddMembers — опциональный параметр, который устанавливается в True, если все члены класса Object должны быть доступны глобально, и False — в противном случае.

Следующая строка кода демонстрирует использование объекта Test при написании макроса. Как видно, в тексте макроса мы пользуемся названием, определенным при помощи параметра Name функции AddObject. Результат — на рис. 3.

Послесловие

Встроенный макроязык — это то средство, которое может превратить вашу программу в мощный и универсальный продукт. Но имейте в виду, что показанная техника — только вершина айсберга, в составе библиотеки имеется еще много компонент (IScriptError, IScriptModule, IScriptModuleCollection, IScriptProcedure, IScriptProcedureCollection), которые позволяют всесторонне и тонко управлять интерпретатором.