Выполнение задания

Лабораторная работа № 25.
РАБОТА С ЛИСТАМИ И ДИАГРАММАМИ

Приложения VBA должны работать не только с диапазонами, но и с листами и диаграммами, копировать данные и т.п. Для примера разработаем книгу, которая позволит хранить данные о ежедневных выплатах сотрудниками.

1. Выполнение задания

1. Запустите приложение Microsoft Excel и сохраните файл Книга1 в своей рабочей папке под именем Выплаты.xlsm (не забудьте изменить расширение файла).

2. Переименуйте Лист1 в Подоходный налог. В ячейку A1 этого листа вставьте текст «Ставка налога», а в ячейку B1 – значение 0,13. Измените формат ячейки B1 на процентный.

3. В качестве имени для листа Лист2 задайте какую-нибудь дату, например, «05.11.2011». Создайте на листе таблицу, показанную на рис. 1.

Рис. 1. Данные о выплатах

4. В ячейки H1:H4 вставьте формулы для расчёта налога путём умножения начисленной суммы на процентную ставку, значение которой задано на листе Подоходный налог.

5. В ячейки I1:I4 вставьте формулы для расчёта выдаваемой суммы как разности между начисленной суммой и взимаемым налогом.

6. В ячейки G5:I5 вставьте формулы для суммирования начисленных сумм, налоговых отчислений и выданных сумм.

7. Удалите лист Лист3.

8. Далее разработаем процедуру на VBA, вставляющую новый лист и копирующую на него данные с предыдущего листа. Процедура будет выглядеть следующим образом:

' Добавление рабочего листа

Public Sub AddWorksheet()

Dim name As String

' Активируем последний рабочий лист

Worksheets(Worksheets.Count).Activate

' Добавляем новый лист после активного, новый лист становится активным

Worksheets.Add after:=ActiveSheet

' Вводим имя для нового рабочего листа

name = InputBox("Введите имя нового рабочего листа", "Добавление листа", _

Default:=str(Date))

' Если нажата кнопка "Отмена", удаляем новый рабочий лист

If name = "" Then

MsgBox "Операция отменена. Лист будет удалён", Title:="Добавление листа"

ActiveSheet.Delete

Exit Sub

End If

' Задаём имя нового рабочего листа

ActiveSheet.name = name

' Копируем на новый рабочий лист содержимое предыдущего рабочего листа

Worksheets(Worksheets.Count - 1).UsedRange.Copy ActiveSheet.Range("A1")

' Меняем ширину столбцов

ActiveSheet.Columns("B:I").ColumnWidth = 12

End Sub

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

10. Далее необходимо разработать процедуру для добавления нового сотрудника. Данные о сотруднике будем вводить с помощью формы. Поэтому, прежде всего, необходимо разработать форму, показанную на рис. 2.

Рис. 2. Форма для ввода данных о новом сотруднике

11. Добавьте процедуры обработки события Change для всех текстовых полей так, чтобы кнопка «Добавить» становилась активной, только если заполнены все поля.

12. Добавьте процедуру обработки события Click для кнопки «Отмена».

13. Добавьте процедуру обработки события Click для кнопки «Добавить». Процедура будет выглядеть следующим образом:

Private Sub CommandButtonAdd_Click()

Dim row As Long, r As Range

' Определяем номер последней используемой строки

row = ActiveSheet.UsedRange.Rows.Count

' Определяем диапазон для заполнения

Set r = Intersect(ActiveSheet.Rows(row), ActiveSheet.Columns("A:I"))

' Задаём границы ячеек диапазона

With r.Borders

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

' Записываем данные о новом сотруднике

r.Cells(1, 1).Value = r.Cells(0, 1).Value + 1

r.Cells(1, 2).Value = TextBoxSurname.Value

r.Cells(1, 3).Value = TextBoxName.Value

r.Cells(1, 4).Value = TextBoxParentName.Value

r.Cells(1, 5).Value = TextBoxBirthYear.Value

r.Cells(1, 6).Value = TextBoxPost.Value

r.Cells(1, 7).Value = TextBoxSalary.Value

' Копируем формулы из предыдущей строки

r.Cells(1, 8).FormulaR1C1 = r.Cells(0, 8).FormulaR1C1

r.Cells(1, 9).FormulaR1C1 = r.Cells(0, 9).FormulaR1C1

' Устанавливаем обычный шрифт

r.Cells(1, 7).Font.Bold = False

r.Cells(1, 8).Font.Bold = False

r.Cells(1, 9).Font.Bold = False

' Записываем формулы суммирования

r.Cells(2, 7).Formula = "=SUM(G2:G" + Format(row) + ")"

r.Cells(2, 8).Formula = "=SUM(H2:H" + Format(row) + ")"

r.Cells(2, 9).Formula = "=SUM(I2:I" + Format(row) + ")"

' Форматируем ячейки с формулами суммирования

Set r = Intersect(r.Offset(1, 0), ActiveSheet.Columns("G:I"))

With r.Borders

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

r.Font.Bold = True

Unload Me

End Sub

14. Протестируйте работу формы.

15. Далее разработаем процедуру на VBA, вставляющую на новый лист диаграмму выплат. Процедура будет выглядеть следующим образом:

' Добавление диаграммы

Public Sub AddChart()

Dim str As String, i As Integer, r As Range, c As Chart

' Добавляем новый рабочий лист в конец рабочей книги

Worksheets(Worksheets.Count).Activate

str = ActiveSheet.name

Worksheets.Add after:=ActiveSheet

' Задаём имя новому рабочему листу

ActiveSheet.name = "Диаграмма (" + str + ")"

' Формируем заголовок для таблицы с данными

ActiveSheet.Range("A1").Value = "Дата"

ActiveSheet.Range("A2").Value = "Начисления"

ActiveSheet.Columns("A").ColumnWidth = 12

' Устанавливаем границы ячеек

With ActiveSheet.Range("A1:A2").Borders

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

' Собираем данные для диаграммы из рабочих листов книги

Set r = ActiveSheet.Range("B1:B2")

For i = 2 To Worksheets.Count - 1

r.Cells(1, 1).Value = Worksheets(i).name

' Берём значение из ячейки столбца G последней используемой на i-ом листе строки

r.Cells(2, 1).Value = Worksheets(i).Range("G" + _

Format(Worksheets(i).UsedRange.Rows.Count))

r.ColumnWidth = 10

With r.Borders

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

' Задаём форматы данных в ячейках

r.Cells(1, 1).NumberFormat = "m/d/yyyy"

r.Cells(2, 1).NumberFormat = "#,##0.00$"

' Переходим к следующей паре ячеек

Set r = r.Offset(0, 1)

Next i

Set r = ActiveSheet.UsedRange

' Вставляем диаграмму на рабочий лист

Set c = ActiveSheet.Shapes.AddChart(xlCylinderColClustered, 5, 45, 900, 350).Chart

' Задаём данные для диаграммы

c.SetSourceData r

End Sub

16. Протестируйте работу процедуры.

17. Для удобства использования назначьте сочетания клавиш всем разработанным действиям.

18. Если всё работает корректно, покажите результаты работы преподавателю.

19. Закройте приложение Microsoft Excel.

2. Вопросы для контроля

1. Что означает имя ActiveSheet?

2. Как добавить новый лист в рабочую книгу?

3. Как удалить лист из рабочей книги?

4. Как изменить ширину столбца и высоту строки рабочего листа?

5. Зачем используется свойство UsedRange?

6. Что представляют собой свойства Formula и FormulaR1C1? Почему используется разные свойства?

7. Зачем используется свойство Offset?

8. Что делает оператор With … End With?

9. Как определить число листов в рабочей книге?

10. Как изменить формат ячейки?