Список из нескольких колонок

Пример 7.3 – В рабочем листе Excel введен список сотрудников некоторой организации (столбец A) и их доходы (столбец B). Эти данные должны отображаться в списке, содержащемся в пользовательской форме (см. рисунок 7.5).

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

Для решения этой задачи требуется создать пользовательскую форму. Установить для нее свойства: Name = Nalogi, Caption = Расчет налогов.

Рисунок 7.5 – Пользовательская форма со списком из двух колонок (пример 7.3)

Требуется разместить на форме элементы управления и задать им свойства, как показано в таблице 7.1.

Таблица 7.1 – Элементы управления для примера 7.3

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

Private Sub Userform_Initialize()

Set d = Range("A1").CurrentRegion

With spisok_sotr

.ColumnCount = 2

.RowSource = d.Address

End With

End Sub

Здесь в операторе Set d = Range("A1").CurrentRegion переменная d связывается с диапазоном, заполненным данными (в данном примере – фамилиями сотрудников и их доходами). Оператор With Spisok_sotr указывает, что дальнейшие действия выполняются с объектом Spisok_sotr (где Spisok_sotr - имя списка, размещенного на форме). Область действия оператора With завершается строкой End With. Использование оператора With позволяет сократить ссылки на объект (в данном случае – на список Spisok_sotr). В операторе .ColumnCount = 2 устанавливается свойство списка Spisok_sotr, задающее количество его колонок (имя списка уже задано в операторе With). В операторе .RowSource = d.Address свойству списка RowSource, задающему источник данных для заполнения списка, присваивается адрес объекта d (т.е. диапазона с данными).

Для обработки щелчка по элементу списка введем следующую процедуру:

Private Sub Spisok_sotr_Click()

With spisok_sotr

i =.ListIndex

dohod =.List(i, 1)

Call raschet_naloga(dohod, nalog)

Pole_nalog.Value = nalog

Cells(i + 1, 3).Value = nalog

End With

End Sub

Здесь в операторе i =.ListIndex переменной i присваивается номер текущего элемента (точнее, текущей строки) списка Spisok_sotr; следует обратить внимание, что строки (как и столбцы) списка нумеруются с нуля. В операторе dohod =.List(i, 1) переменной dohod присваивается величина из i -й строки и первого столбца списка Spisok_sotr, т.е. величина дохода выбранного сотрудника. Следует еще раз обратить внимание, что столбцы списка нумеруются с нуля, т.е. в нулевом столбце находятся фамилии, а в первом – доходы.

В операторе Call raschet_naloga(dohod, nalog) вызывается процедура, реализующая алгоритм расчета налога. Результатом ее выполнения является переменная dohod. В двух следующих операторах эта величина выводится в текстовое поле пользовательской формы и на рабочий лист Excel.

Процедура расчета налога может быть, например, следующей:

Sub raschet_naloga(ByVal summa_dohoda, summa_naloga)

If summa_dohoda < 20000 Then

summa_naloga = summa_dohoda * 0.09

Else

summa_naloga = 20000 * 0.09 + (summa_dohoda - 20000) * 0.12

End If

End Sub

Здесь обозначение ByVal указывает, что в переменную summa_dohoda передается только значение соответствующей переменной, указанной при вызове процедуры (в данном случае – значение переменной dohod). Тем самым исключается случайное изменение переменной dohod в процедуре. Следует обратить внимание, что обозначение ByVal относится только к переменной summa_dohoda; таким образом, изменение переменной summa_naloga вызывает изменение соответствующей переменной nalog в вызывающей процедуре.

Приведем также процедуру для закрытия формы нажатием кнопки Vyhod:

Private Sub Vyhod_Click()

Unload Nalogi

End Sub