Ирина Фризен - Офисное программирование

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

Технология выполнения

1. Сохраните новый документ Excel.

2. Создайте форму приложения, отвечающую требованиям задачи (см. рис. 99а).

3. Пропишите процедуры обработки нажатия кнопок Создать отчетную таблицу, Добавить строку и Закончить (см. пример 47).

4. При выводе информации на рабочий лист Excel придерживайтесь оформления, предложенного на рис. 99б.

Рис. 99а. Разработанная форма примера 51 в рабочем состоянии

Рис. 99б. Вывод информации на лист excel после нажатия кнопки «Закончить»

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

1) выберите в главном меню Microsoft Excel <Сервис> <Макрос><Макросы…>;

2) укажите имя макроса, например Макрос1, и нажмите <Создать>. После этого загрузится редактор VBA с заготовкой

Sub Макрос1()

End Sub

3) вставьте в эту заготовку оператор UserForm1.Show, активизирующий созданную форму;

4) далее выберите в главном меню книги Excel <Вид><Панели инструментов><Формы>. Появится панель с элементами управления. Выберите <Кнопка>, после чего можно нарисовать кнопку на рабочем листе Excel;

5) после этого сразу появляется диалоговое окно «Назначить макрос объекту». Выберите Макрос1;

6) чтобы название кнопки было более содержательным, щелкните на ней правой клавишей мыши. В появившемся рядом меню выберите <Изменить текст>. После этого установите текст «Диалог».

Теперь при нажатии кнопки «Диалог» на рабочем листе появится разработанное диалоговое окно примера.

7.3. Финансовые функции

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

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

Таблица 21

Функция SLN вычисляет амортизацию (снижение стоимости) за один период времени, используя метод равномерной амортизации. Ее вызов имеет вид:

SLN(cost, salvage, life) =SLN(стоимость, остаток, период)

Пусть компьютер стоимостью 6000 руб. имеет срок эксплуатации 5 лет, после чего его стоимость оценивается в 1500 руб. Тогда его ежегодная амортизация составит (при равномерном методе оценки): SLN(6000,1500, 5) = 900 руб.

Функция SYD используется для расчета годовой амортизации по линейному методу. Сумма долей амортизации в этом методе определяется как сумма номеров лет эксплуатации:

1+2+…+ life = life(life+1)/2,

а доля амортизации за i-й год пропорциональна количеству лет (life – i +1), оставшихся до конца периода эксплуатации. Синтаксис этой функции:

SYD(cost, salvage, life, period) =SYD(стоимость, остаток, время_экспл, период)

При расчете предыдущего примера получим за первый год эксплуатации компьютера амортизацию

SYD(6000,1500,5,1)= 1500 руб.

а за последний год —

SYD(6000,1500,5,5)= 300 руб.

Примечание. Все параметры указываются через запятую.

Функция DDB вычисляет величину амортизации имущества для заданного периода с применением метода двукратного (или k-кратного) учета амортизации. В этом методе амортизация максимальна в первый период и снижается в последующие периоды.

Синтаксис:

DDB(cost, salvage, life, periodi, factor)=DDB(нач_ стоим, остаток, время_экспл, период, коэффициент)

Параметр factor (коэффициент) – это норма снижения балансовой стоимости (амортизации). По умолчанию он равен 2 (метод двукратного учета амортизации).

Функция DDB использует следующую формулу для вычисления амортизации за период:

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

В примере с компьютером по методу двукратной амортизации она составит:

за первый год: DDB(6000,1500, 5,1) = 2400 руб.;

за второй – 1440 руб.;

за третий – 660 руб.;

а за четвертый и пятый будет равна 0.

Все пять аргументов должны быть положительными числами.

Замечания. Метод двойного процента со снижающегося остатка вычисляет амортизацию, используя увеличенный коэффициент. Амортизация максимальна в первый период, в последующие периоды уменьшается.

Примеры. Предположим, что предприятие приобрело новую машину. Машина стоит 2400 $ и имеет срок службы 10 лет. Остаточная стоимость составляет 300 $. Следующие примеры показывают амортизацию за несколько периодов. Результаты округляются до двух знаков после запятой.

ddb(2400,300,3650,1) равняется 1,32 $, амортизация за первый день.

Microsoft Excel автоматически предполагает, что коэффициент равен 2.

ddb(2400,300,120,1) равняется 40,00 $, амортизация за первый месяц.

ddb(2400,300,10,1) равняется 480,00 $, амортизация за первый год.

ddb(2400,300,10,2,1,5) равняется 306,00 $, амортизация за второй год при использования коэффициента, равного 1,5 (а не метода двойного процента).

ddb(2400,300,10,10) равняется 22,12 $, амортизация за десятый год. Microsoft Excel автоматически предполагает, что коэффициент равен 2.

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

Технология выполнения

1. Запустите приложение Excel и сохраните книгу под соответствующим именем.

2. Создайте форму аналогично приведенному рис. 100.

3. Пропишите работу кнопки «Вычислить»:

Private Sub CommandButton1_Click()

Dim timeall As Integer

Рис. 100. Разработанная форма примера 52 в рабочем состоянии

Dim amort As Double

With ComboBox1

If.ListIndex = 0 Then

timeall = Textbox3.Value * 365

ElseIf.ListIndex = 1 Then

timeall = Textbox3.Value * 12

Else

timeall = Textbox3.Value

End If

End With

amort = DDB(TextBox1.Value, TextBox2.Value, timeall,

TextBox4.Value)

TextBox5.Value = amort

CommandButton2.Enabled = True

End Sub

Обратите внимание на вычисление значений параметра timeall в зависимости от выбора пункта в раскрывающемся списке (день, месяц, год) и использования функции DDB.

4. Заполнение поля со списком происходит в процедуре инициализации формы:

Private Sub UserForm_initialize()

With ComboBox1

AddItem «день»

AddItem «месяц»

AddItem «год»

ListIndex = 0

End With

End Sub

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

5. Нажатие кнопки «Вывести отчет» должно приводить к выводу отчета на рабочий лист Excel в следующем формате (рис. 101):

Рис. 101. Вывод вычислений на лист excel в примере 52

Пример 53. Функция ddb() имеет пять аргументов, последний из которых является коэффициентом амортизации. Необходимо в форме предусмотреть новое текстовое поле, куда пользователь смог бы вводить коэффициент, а в коде нужно учесть присутствие этого коэффициента.