Создание приложений Excel на языке VBA
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Microsoft Excel относится к типу программ, которые называются табличными процессорами или электронными таблицами.
VBA сочетает в себе практически неограниченные возможности с простотой изучения и использования. Теперь большинство функций можно описать, не прибегая к программированию. Пожалуй, самое главное достоинство VBA в том, что этот язык является единым для всех офисных приложений Microsoft и поэтому позволяет связывать их между собой
VBA — `объектно — ориентированный язык программирования высокого уровня, являющийся одним из диалектов языка программирования Visual Basic.
Очень удобно, что VBA применяет технологию визуального программирования, то есть конструирование рабочей поверхности приложения и элементов его управления непосредственно на экране, а также запись всей программы или ее частей при помощи специальной программы MacroRecorder.
Создадим приложение Excel на языке VBA ,с помощью которого можно рассчитать внутреннюю норму доходности.
Внутренняя норма доходности (англ. internal rate of return, общепринятое сокращение — IRR (ВНД)) — это процентная ставка, при которой чистая приведённая стоимость (чистый дисконтированный доход — NPV) равна 0. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.
Таким образом, для потока платежей CF, где CFt — платёж через лет () и начальной инвестиции в размере IC=? CFo внутренняя норма доходности рассчитывается из уравнения:
Если все CFt положительны, то уравнение имеет единственный корень, больший ?1. Если в уравнении все платежи и свободный член положительны, то уравнение имеет положительный корень, то есть внутренняя норма доходности положительна.
Внутренней доходностью для финансовых инструментов называют процентную ставку, при которой дисконтированная стоимость будущего потока платежей по данному финансовому инструменту совпадает с его рыночной ценой. Определённая таким образом внутренняя доходность равна внутренней норме доходности инвестиции в данный финансовый момент времени.
Показатель внутренней нормы доходности основывается на допущении, что денежные потоки, полученные до погашения инвестиции (истечения срока её действия), будут реинвестированы по ставке, равной внутренней норме доходности, и что она останется неизменной.
Инвестиция считается приемлемой, если внутренняя норма доходности выше, чем минимальный приемлемый показатель окупаемости инвестиций в финансовый инструмент. Такие инвестиции представляют интерес для инвестора.
Показатель внутренней нормы доходности для облигаций называется доходностью к погашению. Доходность к погашению облигации может отличаться от купонной ставки, если облигация продаётся по цене, отличающейся от номинальной стоимости.
Для нахождения внутренней нормы доходности используются численные методы решения нелинейных уравнений. В программе используется метод половинного деления. Рассмотрим его содержание.
Перед применением метода для поиска корней функции необходимо отделить корни, то есть определить границы, внутри которых находятся корни.
Будем считать, что корень t функции f(x) отделён на отрезке [a;b]. Задача заключается в том, чтобы найти и уточнить этот корень методом половинного деления. Другими словами, требуется найти приближённое значение корня с заданной точностью .
Пусть функция f непрерывна на отрезке [a;b], и — единственный корень уравнения
Поделим отрезок [a;b] пополам. Получим точку и два отрезка [a;c] и [c;b].
Если нет, то из двух полученных отрезков [a;c] и [c;b] надо выбрать один [a1;b1] такой, что , то есть
Новый отрезок [a1;b1] делим пополам. Получаем середину этого отрезка так далее.
Для того, чтобы найти приближённое значение корня с точностью до , необходимо остановить процесс половинного деления на таком шаге n, на котором и вычислить Тогда можно взять
Описание алгоритма программы с описанием функций составных частей и связи между ними
Программа может работать в различных версиях Excel: Excel 97, Excel 2002 и Excel 2013 под управлением операционной системы Windows. Приведем листинг программы.
MsgBox » Не введено число платежей или не числовое данное», vbInformation, «Ошибка»
MsgBox «Не введен объем инвестиций или не числовое данное», vbInformation, «Ошибка»
MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»
MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»
MsgBox «Левая граница больше или равна правой. Проверьте данные», vbInformation, «Ошибка»
MsgBox «Ошибка при вводе данных», vbInformation, «Ошибка»
MsgBox «Ошибка при вводе данных. Точность вычислений не может быть отрицательной», vbInformation, «Ошибка»
IsNumeric(Worksheets(«Данные»).Cells(i, 1).Value) = False Then
MsgBox «Или пустая ячейка или не числовые данные», vbInformation, «Ошибка»
Нахождение внутренней нормы доходности методом половинного деления
MsgBox «Внутренняя норма доходности с точностью » + Trim(CStr(epsilon)) + » равна: » + CStr(c), vbInformation, «Решение»
Найдем внутреннюю норму доходности t с точностью 0,001. Уравнение для нахождения внутренней нормы доходности имеет следующий вид:
Предварительно было установлено, что корень уравнения находится на отрезке [0; 1].
В результате решения значение внутренней нормы доходности равно 0,155 или 15,5% с точностью 0,001. Значение левой части уравнения приближенно равно ?0,046.
1. Уокенбах, Д.. Профессиональное программирование на VBA в Excel 2002. М.: Издательский дом «Вильямс», 2003.
2. Гарнаев А. Ю. Excel, VBA, Internet в экономике и финансах. СПб: БХВ — Петербург, 2003.
3. Гарнаев А. Ю. Самоучитель VBA — СПб.: БХВ — Санкт — Петербург, 2000.
4. Румянцева Е. Л., Трояновский В. М. Microsoft Excel как инструмент разработки информационных систем // Мир ПК, май, 2004.
Лучшие книги Джона Уокенбаха
ВПЕРВЫЕ НА РУССКОМ ЯЗЫКЕ!
в книгу вошли цитаты
из никогда не выходивших в России работ:
Курт Гёдель. О формально неразрешимых предложениях «Оснований математики»
Алан Тьюринг . О вычислимых числах
Приручи данные с помощью Power Query в Excel и.
Excel 2016. Профессиональное программирование на VBA (Майкл Александер, Ричард Куслейка) — купить книгу с доставкой в интернет-магазине «Читай-город». ISBN: 978-5-9909446-3-3
Если все CFt положительны, то уравнение имеет единственный корень, больший ?1. Если в уравнении все платежи и свободный член положительны, то уравнение имеет положительный корень, то есть внутренняя норма доходности положительна.
| Артикул: 3145377 |
| ISBN: 978-5-907114-72-2 |
| Тип переплета: твердый |
| Тираж: 150 |
| Название: Excel 2013: профессиональное программирование на VBA |
| Автор: Д. Уокенбах |
| Место издания: СПб |
| Издатель: ООО «Диалектика» |
| Дата издания: 2019 |
| Количество страниц: 960 |
| Высота, см.: 24 |
| Ширина, см: 17 |
| Толщина, см: 4,4 |
| Вес в граммах: 1364 |
Учимся пользоваться Object Browser
Выше была упомянута функция MsgBox, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).
Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:
Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).
Примечание: Библиотеки предоставляют стандартные и дополнительные возможности работы с объектами Excel (ячейки, листы, книги и т.д.), файловой системы, изображениями, базами данных и другими.
Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.
Результаты поиска отображаются в поле №3 и представляют из себя таблицу с тремя столбцами: библиотека, класс и член класса.
Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.
В области №5 содержится список всех членов класса, выбранного в поле №4.
Поле №6 содержит краткую справку о выделенном результате поиска, классе или члене класса.
Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.
Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.
Для получения полной справки кликните по кнопке с изображением вопросительного знака. В зависимости от версии Excel, Вас перенаправить на файл справки или сайт с документацией от Microsoft.
Excel 2019 для чайников
На ленте также расположено несколько команд для связи с разработчиком. Вы в любой момент можете оставить замечание по работе программы, сделать предложение по улучшению работы той или иной функции, оставить заказ на разработку макроса.
Обратная связь
На ленте также расположено несколько команд для связи с разработчиком. Вы в любой момент можете оставить замечание по работе программы, сделать предложение по улучшению работы той или иной функции, оставить заказ на разработку макроса.
Я хочу сделать программу лучше, поэтому за дельный совет или найденную ошибку, в качестве благодарности, вы получите бесплатный ключ активации.
Обращаем Ваше внимание, что функционал, описанный в данной статье, ОТСУТСТВУЕТ в Excel. Чтобы его добавить, необходимо установить надстройку VBA-Excel. Данная программа содержит более сотни функций, которые сделают работу с Excel в разы эффективней. С ней рутинные задачи будут занимать считанные секунды. Программой уже воспользовались чел., попробуйте и Вы!
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Визуализация данных при помощи дашбордов и.
По мере углубления в язык VBA Ваш код будет усложняться и иметь более гибкую логику. Вам часто придется сталкиваться с тем, что некоторые части программы будут дублировать код частично или полностью. Таких ситуаций нужно избегать, так как они усложняют понимание процедур и затрудняют их отладку.
