Д.Т.ШАЙДУЛЛИНА

преподаватель кафедры АСУ Военно-инженерного

института радиоэлектроники и связи, город Алматы

АВТОМАТИЗАЦИЯ В MICROSOFT EXCEL ПРИ ПОМОЩИ VBA

(VISUAL BASIC APPLICATION)

Аннотация

Статья рассматривает применение языка программирования Visual

Basic for Applications (VBA) для тех задач, с которыми можно столкнуться

при внедрении нового программного обеспечения на крупном предприятии.

Также в данной статье рассмотрены плюсы и минусы использования языка

программирования VBA.

Ключевые

слова:

макрос,

планово-предупредительные

ремонт,

ремонтная сложность, трудоемкость ремонтных работ

Microsoft Excel имеет встроенный язык программирования — Visual

Basic for Applications (VBA). Этот язык позволяет создавать приложения,

которые доступны и выполняются средствами Microsoft Office. Это могут

быть

разнообразные

аналитические

программы,

финансовые

системы,

программы учета кадров и т. д. Также VBA позволяет решать конкретные

локальные и глобальные задачи.

VBA – это сочетание больших возможностей с простотой изучения и

использования. Одним из главных достоинств VBA является использование

этого языка программирования во всех офисных приложениях Microsoft и

связь между ними.

VBA (Visual Basic for Applications) — это упрощенная версия Visual

Basic,

встроенная

в

множество

продуктов

линейки

Microsoft

Office.

Она

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

требуется устанавливать различные IDE — всё, включая отладчик, уже есть в

Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы

на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также

возможно, но требуется научится читать, изменять и писать файлы офиса —

встраивать в документы не получится. А интерфейсы Microsoft работают

через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием

COM.

Для автоматизации работы приложений часто используются макросы.

Любой

макрос

—

это

последовательность

действий,

записанная

под

определенным

именем.

Если

при

работе

с

Microsoft

Excel

возникает

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

операций

(например,

сложное

форматирование

текущей

ячейки

или

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

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

потребуется.

Записанный

макрос

можно

вызывать

для

выполнения

с

помощью

специального диалогового окна или при помощи комбинации клавиш. Кроме

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

случае, для того чтобы записать макрос, не нужно быть программистом —

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

результату.

Как

уже

было

сказано

ранее

–

все

приложения

Microsoft

Office

поддерживают язык программирования Visual Basic for Applications (VBA). В

распоряжении разработчика VBA-приложения большое количество объектов,

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

степени программно настроить приложение. Важнейшим достоинством VBA

является возможность объединять любые приложения Microsoft Office для

решения практически любых задач по обработке информации. В этом смысле

Microsoft Office можно считать системой программирования, подобной C++,

Delphi и т.д.

К положительным особенностям VBA можно отнести то, что он очень

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

среде Windows. Этому способствует большое распространение продуктов

Microsoft

Office,

простота

понимания

и

использования

языка

программирования

VBA,

интуитивно

понятная

интегрированная

среда

редактора

Visual

Basic,

наличие

огромного

количества

объектов.

Более

глубокие

знания

VBA-программирования

позволят

решать

практически

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

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

сетевых СУБД [1, с. 3].

Недостатком

VBA

являются

проблемы

с

совместимостью

разных

версий. Также к недостаткам часто относят и слишком высокую открытость

кода,

но

многие

продукты

позволяют

использовать

шифрование

кода

и

установку пароля на просмотр.

2. Постановка реальной задачи

Имеется крупное промышленное предприятие, на котором в недавнем

времени была установлена корпоративная информационная система (КИС). В

ее

основе

лежит

автоматизация

всех

бизнес-процессов

на

данном

промышленном предприятии. В состав этой КИС входят многочисленные

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

другими системами, применяемыми на предприятии. Конечно же, как и во

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

так и локальные проблемы. Так и произошло в этом случае.

Возьмем

для

примера

один

из

модулей

–

модуль

«Планово-

предупредительные ремонты» (ППР). Данный модуль позволяет вести учет

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

вести

фактические

цифры

по

трудоемкости

работ,

учитывать

простои

оборудования и т.д. Основой этого модуля являются – годовые и месячные

графики ППР. Данные графики содержат в себе следующую информацию:

1) список оборудования с указанием его характеристик и следующих

параметров:

- ремонтная сложность выполнения работ;

- группа ремонтной сложности;

- виды ремонтов в месячной разбивке;

- виды обслуживания и ремонтов;

-

трудоемкость

по

видам

работ,

рассчитанная

по

каждой

единице

оборудования.

2) итоговые суммы по каждой единице и общая сумма по ремонтной

бригаде.

Графики

ППР

составляются

для

ремонтных

цехов.

Каждый

ремонтный

цех

имеет

не сколько

ремонтных

бригад,

которые

специализируются на определенном виде оборудования и на определенном

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

либо проблемы или меняется стандарт представления данных, то изменения

касаются всех этих бригад.

Стандартный функционал внедренной КИС не обеспечивал полный

набор функций и процедур для обеспечения целостности работы планово-

предупредительных

ремонтов

на

предприятии.

Доработка

модуля

силами

производителя КИС требовала большого количества времени. А для того,

чтобы

исправить

эту

недоработку

силами

отдела

корпоративной

информатизации

предприятия

пришлось

бы

полностью

перерабатывать

функционал, что тоже требует большого количества времени и человеческих

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

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

Теперь рассмотрим сами задачи, которые необходимо было решить:

1) применение понижающих коэффициентов, а также округление до

сотых долей трудоемкости работ, как в годовых, так и в месячных графиках

ППР;

Данная

трудоемкость

напрямую

связана

с

заработной

платой

работников

ремонтных

цехов

и

поэтому

она

должна

быть

приведена

в

соответствие;

2) графики ППР должны быть представлены в том виде, которое

прописано в стандарте по ремонту и обслуживанию оборудования;

3) формирование обязательных номенклатур на основании месячных

графиков

ППР.

По

новому

стандарту

предприятия

«Планово-

предупредительный

ремонт

оборудования.

Формирование

и

выполнение

плана-графика ППР» было введено в действие формирование ежемесячно

обязательных номенклатур на ремонт оборудования по ремонтным цехам.

Это те виды ремонтов, которые в первую очередь должны быть выполнены,

чтобы не допустить простоев оборудования. Эти виды ремонтов, а также их

трудоемкость формируются из ремонтных бригад по ремонтным цехам. А как

уже было сказано выше, их довольно много.

Все эти операции, выполняемые специалистами в данной области,

занимают

довольно

большое

количество

времени.

Поэтому

доработка

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

их

исполнения,

а

также

снизить

трудоемкость

выполнения

рутинных

операций.

3.

Результат

использования

языка

программирования

VBA

для

реализации поставленной задачи

Макрос — это последовательность команд и функций, хранящаяся в

модуле Visual Basic. Ее можно запускать каждый раз, когда необходимо

выполнить данную задачу.

При записи макроса Microsoft Excel сохраняет информацию о каждом

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

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

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

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

отдельной библиотеке макросов для простоты работы с ними [2].

Для редактирования макросов используется редактор Visual Basic. Для

создания и редактирования макросов для электронных таблиц необходимо

знание языка программирования Visual Basic. Редактор Visual Basic.

После

написания

всех

необходимых

функций

и

процедур,

было

реализовано

изменение

интерфейса

печатной

форм

также

с

помощью

редактора

Visual

Basic

в

этом

же

макросе.

И

в

конце

были

добавлены

выводимые диалоговые окна для определения количества листов в книге,

определения цеха и месяца расчета.

Описание

последовательности

операций

при

выполнения

макроса

«Понижающие коэффициенты»:

1.

Указание

количество

листов

в

книге,

цеха,

месяца

расчета

в

диалоговых окнах.

2. Нахождение столбца трудоемкости, вида ремонта и общего числа

столбцов.

3. Получение обозначения бригады.

4.

Получение

понижающих

коэффициентов

по

видам

ремонта

из

соответствующего файла.

5. Пробег по столбцу трудоемкости и применение коэффициентов.

6. Суммирование по видам работ и округление до сотых долей.

7. Удаление лишней информации с листов.

Описание

последовательности

операций

при

выполнения

макроса

«Обязательная номенклатура»:

1.

Указание

количество

листов

в

книге,

цеха,

месяца

расчета

в

диалоговых окнах.

2. Нахождение столбца с видами ремонтов.

3. Поиск капитальных, средних и текущих ремонтов, а также нового

оборудования.

4. Копирование на отдельный лист необходимых полей в соответствии

с формами, приведенными в стандартах предприятия.

5. Приведение печатной формы к форме, обозначенной в стандартах

предприятия.

Допустим, Вам досталась достаточно сложная математическая задача

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

назначения

и

должна

быть

выполнена

на

ПЛК.

Данную

задачу

проще

разработать на языках C# или Visual Basic Application – в итоге рождается

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

по аппроксимации данных в большом нелинейном массиве и нахождении

коэффициентов

линейной

функции

при

условии

приращения

к

прямой.

Бывают

и

намного

более

простые

задачи,

которые

также

удобно

визуализировать в программе MS EXCEL при помощи языка VBA, например,

отчеты.

Написание

программ

на

VBA

существенным

образом

снизит

издержки, связанные с уточнением технического задания при производстве

работ [3].

Бывают

и

намного

более

простые

задачи,

которые

также

удобно

визуализировать в программе MS EXCEL при помощи языка VBA, например,

отчеты.

Написание

программ

на

VBA

существенным

образом

снизит

издержки, связанные с уточнением технического задания при производстве

работ [4].

Использование языка программирования Visual Basic for Applications

не всегда себя оправдывает, особенно в рамках решения больших проектов.

Также безопасность данного языка не позволяет углубляться в более глубокое

его изучение. Но когда перед вами стоит задача автоматизации рутинных

операций, то язык программирования VBA очень полезен. Особенно стоит

выделить простоту использования и понимания. Эти два критерия позволяют

в короткие сроки выполнить необходимые действия по реализации алгоритма

автоматизации.

Литература:

1. "Информационные технологии. Учебное пособие". Под ред. А.К.

Волкова. Москва. Издательство "ИНФРА-М". 2001 г.

2. "Информатика: Учебник" Под ред. проф. Н.В. Макаровой. Москва.

Издательство "Финансы и статистика". 2005 г.

3.

Уокенбах

Дж.

Профессиональное

программирование

на

VBA

в

Excel 2002. М.: Диалектика, 2003

4. "Информационные технологии управления: Учебное пособие". Под

редакцией Ю.М. Черкасова. Москва. Издательство "ИНФРА-М". 2004 г.