Автоматизация расчёта ожидаемой прибыли средствами VBA

Microsoft Excel – это очень мощный инструмент, который можно использовать для управления данными, их анализа и представления. Но иногда, несмотря на богатый набор возможностей стандартного интерфейса пользователя Excel, может понадобиться найти несложный способ выполнять повседневные повторяющиеся задачи или какие-то задачи, которые не удается решить с помощью интерфейса пользователя. Для расширения возможностей этих приложений используются макросы. В качестве языка программирования макросов используется VBA[10].

Макрос – это программа, автоматизирующая выполнение различных часто повторяющихся операций [9].

Макросы Microsoft Office Excel – это простые, легко адаптируемые и вместе с тем мощные инструменты, позволяющие пользователю экономить свое время и работать более продуктивно. Циклы, представляющие собой разновидность макросов, особенно полезны при работе с диапазонами данных [9].

Работая с книгой Excel, пользователь может  записать несколько макросов. Все они сохраняются в VBA-модуле. Запуск макроса выполняется одним из трех способов:

1. с помощью «горячих» клавиш;
2. посредством выбора имени макроса в меню;
3. щелчком мыши по графическому объекту, связанному с макросом.

При записи макроса Excel происходит запись кода VBA, описывающего выполняемые действия, в модуль, прикрепленный к книге. Модуль можно рассматривать как контейнер, вмещающий ряд макросов [1].

Одним из наиболее важных и полезных аспектов использования MS Office в честности Excel является автоматизация некоторых процессов взаимодействия пользователя и приложений MS Office, которые позволяют решать, практически, все современные бизнес – задачи: от создания простых документов и отчетов до полной автоматизации документооборота с использованием систем управления БД. Это связано с тем, что все приложения Office поддерживают язык программирования Basic for application (VBA) [5].

Visual Basic for Applications – язык макропрограммирования, встроенный во все программы пакета Office. Включение в этот пакет современного полнофункционального языка программирования, инструментов для написания на нем программ, их редактирования и отладки, а также обширных библиотек стандартных программ позволяет создавать программные продукты, с достаточной полнотой автоматизирующие решение конкретных задач пользователя [5].

 Язык VBA – объектно-ориентированный, в нем в полной мере реализованы концепции объектно-ориентированного программирования. Важнейшая из них – объединение стандартных структур данных (объектов) и операций для работы с ними (методов) в едином описании класса объекта[5].

Этот язык позволяет  создавать приложения, выполняемые  в среде Microsoft Office. Это могут быть разнообразные аналитические программы, финансовые системы, программы учета  кадров, системы автоматического  создания официальных писем/документов с помощью библиотеки готовых шаблонов и т.п. При этом интерфейс создаваемой программы может быть совершенно непохожим на интерфейс того приложения, в котором она написана [8]. 

При работе с VBA пользователю автоматически  становятся доступны описания всех классов  объектов, использующихся во всех программах пакета Microsoft Office (библиотеки объектов). Это позволяет разработчику строить свои проекты не из самостоятельно построенных классов объектов, а из целых стандартных объектов программ Microsoft Office. За счет этого эффективность работы над проектами автоматизации существенно возрастает, а ее трудоемкость уменьшается, позволяя справиться с такими проектами в некоторых случаях даже непрофессиональному разработчику [10].

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

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

Также к недостаткам  часто относят и слишком высокую открытость кода для случайного изменения, тем не менее, многие программные продукты (например, Microsoft Office и IBM Lotus Symphony) позволяют пользователю использовать шифрование исходного кода и установку пароля на его просмотр [21].

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

Основные принципы, которые отличают объектно-ориентированное  программирование от процедурных:

1. отделение объектов программы, которые связаны с интерфейсом пользователя, от их программной части;
2. скорость и простота создания и настройки интерфейса пользователя, в котором используются готовые блоки – кнопки, поля ввода, переключатели и т.д. [14].

Таким образом, для разработчиков АИС  важными являются следующие возможности использования среды разработки VBА в приложении MS Excel [8]:

• Файловая структура. Ориентация на многолистовую структуру позволяет легко организовывать элементы приложения и хранить его в единственном файле;
• VBA – это макроязык, позволяет создавать структурированные про-граммы непосредственно в Excel.
• Excel позволяет довольно легко вставить в рабочий лист различные элементы управления, например, кнопки, поля со списком, переключатели. Можно также легко создавать диалоговые окна, имеющие профессиональный внешний вид.
• Для упрощения формул и вычислений можно с помощью VBA создавать пользовательские функции рабочих  листов.
• Имеется возможность вносить изменения в элементы меню, добавлять в имеющиеся меню новые элементы или создавать полностью новые меню.

Подводя итоги нужно отметить, что выбранные нами инструментально программные средства для реализации автоматизированной системы расчёта ожидаемой прибыли, является среда программирования VBA в приложении MS Excel. Выбор именно этих программно инструментальных средств в нашем случае обусловлен тем, что среда программирования Visual Basic for Application (VBA) в сочетании с MS Excel, это объединение одного из самых простых языков программирования и всех вычислительных возможностей такой многогранной системы как Excel. С помощью VBA можно легко и быстро создавать разнообразные приложения, даже не являясь специалистом в области программирования. VBA содержит относительно мощную графическую среду, позволяющую наглядно конструировать экранные формы и управляющие элементами. В общем Visual Basic for Application позволяет с легкостью реализовать автоматизированную систему расчёта ожидаемой прибыли.

. 
РАЗДЕЛ 2. ПРАКТИЧЕСКАЯ РЕАЛИЗАЦИЯ

АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ ДЛЯ  РАСЧЁТА 

ОЖИДАЕМОЙ ПРИБЫЛИ СРЕДСТВАМИ VBA

2.1. Структура и функциональные  возможности автоматизированной системы

В процессе выполнения курсовой работы мы разработали  автоматизированную систему для расчёта ожидаемой прибыли средствами  Visual Basic for Applications (VBA) в соответствии с ТЗ (Техническое задание находится в приложении 1).

При реализации системы учитывались быстродействие выполнения команд, возможность ее использование на версиях Microsoft Office 2003 и выше.

Работа над программной реализацией проекта начиналась с создания формы UserForm1 - «Источники дохода» (Рис.1), которая отвечает за:

• заполнения таблиц «баз данных» доходов;
• расчёт объёма чистой прибыли;
• расчёт объёма валовой прибыли;
• расчёт объёма капитала на закупку продукции ;
• расчёт  денежного эквивалента введённым процентам;

Рис.1. Форма «Источники дохода»

На форме «Источники дохода» применялись стандартные визуальные компоненты среды программирования Visual Basic for Applications следующих классов:

• TextBox – применялся для ввода информации: период расчёта, название, дополнительная информация, количество, себестоимость ;
• Label – применялся для вывода подписи объектов TextBox;
• Frame – объединял некоторые компоненты;
• ComboBox – ниспадающие списки для выбора для выбора вида дохода.
• CommandButton – использовался для выполнения команд: добавить, очистить таблицу, закрыть;

На событии  UserForm_Activate происходит создания заголовков таблицы и заполнения значениями объекта ComboBox. Ниже приведён пример кода процедуры.

Private Sub UserForm_Activate()

TextBox5.Text = SpinButton1.Value

TextBox6.Text = SpinButton2.Value

Range("A1:J1").Value = Array("№", "Вид", "Название", "Доп. Инф.", "Кол-во(шт.)", "Сибистоимость(1 шт.)", "Маржа (%)", "Маржа (p.)", "Капитал на закупку", "Чистая прибыль")

ComboBox1.AddItem "Инвестиции"

ComboBox1.AddItem "Товар"

ComboBox1.AddItem "Услуга"

End Sub

После нажатия на кнопку «Добавить» происходит события CommandButton1_Click входе которого выполняются следующие действия заполнения картежа таблицы на листе «доход» данными введёнными пользователям на форме, выполнения расчётов: объёма чистой прибыли, объёма валовой прибыли, объёма капитала на закупку, расчёт  денежного эквивалента введённым процентам. Ниже представлен отрывок кода процедуры CommandButton1_Click (кнопка «Добавить»).

 

Worksheets("доход").Select

НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1

With ActiveSheet

Cells(НомерСтроки, 1).Value = НомерСтроки  - 1   'НомерСтроки

Cells(НомерСтроки, 2).Value = ComboBox1.Text    'Вид

Cells(НомерСтроки, 3).Value = TextBox1.Text 'название

Cells(НомерСтроки, 4).Value = TextBox2.Text 'доп инф

Cells(НомерСтроки, 5).Value = TextBox3.Text 'кол-во

Cells(НомерСтроки, 6).Value = TextBox4.Text 'себестоимость

Cells(НомерСтроки, 7).Value = TextBox5.Text 'маржа %

Cells(НомерСтроки, 8).Value = (TextBox4.Text / 100)*TextBox5.Text 'маржа р.

Cells(НомерСтроки, 9).Value = TextBox3.Text * TextBox4.Text         'оборотный капитал

Cells(НомерСтроки, 10).Value = TextBox3.Text * Cells(НомерСтроки, 8).Value 'Валовая прибыль прибыль

Cells(8, 12).Value = TextBox6.Text * Cells(3, 12).Value

Cells(8, 14).Value = TextBox6.Text * Cells(3, 14).Value

Cells(6, 13).Value = TextBox6.Text

End With

При нажатии на копку  «Очистить таблиц» происходит очистка  таблицы на листе «доходы».

Следующим шагом при  разработке нашего программного продукта была реализована форма UserForm2 - «Расходы» (Рис.2) , которая отвечает за:

• заполнения таблиц «базы данных» расходов;
• вычисления ежемесячных расходов;
• вычисления  однократных расходов;
• вычисления  общей суммы расходов за определённое пользователем количество месяцев.

 

Рис.2. Форма «Расходы»

На форме «Расходы» применялись следующие визуальные компоненты-объекты среды программирования VBA:

• TextBox – применялся для ввода информации: период расчёта, названия, дополнительная информация, сумма;
• Label – применялся для вывода подписи объектов TextBox;
• SpinButton – применялся для ввода период расчёта;
• CommandButton – использовался для выполнения команд: добавить, очистить таблицу, закрыть;

При запуске формы «Расходы» происходит события UserForm2Activate, в ходе которого создаются значения для объекта ComboBox.

После нажатия на кнопку «Добавить» происходит события CommandButton1_Click в ходе которого выполняются следующие действия заполнения картежа таблицы на листе «расходы»  данными введёнными пользователям на форме «Расходы», вычисления ежемесячных расходов, вычисления  однократных расходов, вычисления  общей суммы расходов за определённое пользователем количество месяцев. Ниже представлен отрывок кода процедуры CommandButton1_Click (кнопка «Добавить»).

Worksheets("расходы").Select

НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1

With ActiveSheet

Cells(НомерСтроки, 1).Value = НомерСтроки - 1   'НомерСтроки

Cells(НомерСтроки, 2).Value = ComboBox1.Text    'Вид

Cells(НомерСтроки, 3).Value = TextBox1.Text 'название

Cells(НомерСтроки, 4).Value = TextBox2.Text 'доп инф

Cells(НомерСтроки, 5).Value = TextBox3.Text 'кол-во

End With

If Cells(НомерСтроки, 2).Value = "Однократный" Then Cells(2, 10).Value = Cells(2, 10).Value + Cells(НомерСтроки, 5).Value

If Cells(НомерСтроки, 2).Value = "Ежемесячный" Then Cells(2, 9).Value = Cells(2, 9).Value + Cells(НомерСтроки, 5).Value

Cells(2, 8).Value = Cells(2, 10).Value + (Cells(2, 9).Value * TextBox6.Text)

Cells(1, 7).Value = TextBox6.Text

При нажатии на копку  «Очистить таблиц» происходит очистка  таблицы на листе «расходы»

Следующим шагом при разработке автоматизированной системы ожидаемой прибыли была реализована процедура Worksheet_Activate, которая срабатывает при открытии листа «срок_окупаемости». Входе этой процедуры выполняются: расчёты срока окупаемости (количество месяцев), расчёты суммы чистой прибыли, вывод сообщения, если деятельность убыточна, и объём убытка в месяц. Ниже приведён отрывок кода процедуры Worksheet_Activate.

Worksheets("срок_окупаемости").Select

Cells(4, 1).Value = " "