Указание свойств элементов управления

Чтобы установить свойства элементов управления, необходимо сначала вызвать на экран меню Properties (см. выше).

Чтобы установить свойства кнопки, следует выбрать ее с помощью мыши на рабочем листе Excel, или выбрать ее из списка элементов управления, имеющегося в верхней части меню Properties (кнопка по умолчанию имеет имя CommandButton1. Для кнопки требуется установить следующие свойства:

- Name: Obrabotka;

- Caption: Выполнить.

Свойство Name – имя, под которым кнопка будет использоваться в программе на VBA. Свойство Caption – подпись кнопки, т.е. текст, который будет указан на кнопке.

Для одного из переключателей (имена переключателей по умолчанию – OptionButton1 и OptionButton2) установим следующие свойства:

- Name: Bolshe;

- Caption: Больше;

- GroupName: Bol_men.

Здесь свойство Name – имя для ссылок на переключатель; Caption – подпись переключателя, т.е. текст, который будет указан рядом с ним; GroupName – имя группы переключателей. Из всех переключателей, для которых указано одинаковое имя группы, в любой момент может быть установлен только один, остальные – сброшены.

Аналогично установим свойства другого переключателя:

- Name: Menshe;

- Caption: Меньше;

- GroupName: Bol_men.

Для счетчика (имя по умолчанию – SpinButton1) установим следующие свойства:

- Name: Predel;

- LinkedCell: A10.

Свойство LinkedCell задает ячейку, в которой будет выводиться значение, установленное с помощью счетчика (ячейка A10 выбрана произвольно).

Для флажка (имя по умолчанию – CheckBox1) установим следующие свойства:

- Name: Summa;

- Caption: Сумма.

Для текстового поля (имя по умолчанию – TextBox1) требуется установить только свойство Name: Summa_diapazona.

Разработка программы

Чтобы приступить к написанию программы для работы с созданными элементами управления, следует выбрать на рабочем листе кнопку Выполнить (так как программа должна выполняться именно при нажатии кнопки) и в панели инструментов Элементы управления нажать кнопку Исходный текст. Вызывается редактор VBA, и открывается модуль текущего рабочего листа. В нем автоматически создается заголовок процедуры:

Private Sub Obrabotka_Click()

Здесь Obrabotka – имя элемента управления, для которого создается программа (в данном случае – имя кнопки). Click – имя события, для обработки которого создается программа (в данном случае – щелчок мыши по кнопке). Слово Private обозначает область видимости процедуры (см. лабораторную работу №4). Так как в данном случае вся программа располагается в одном модуле, указание области видимости процедуры никак не влияет на ее работу.

Программа, вызываемая при щелчке мышью по кнопке Obrabotka, имеет следующий вид.

Private Sub Obrabotka_Click()

Set d = Selection

m = d.Rows.Count

n = d.Columns.Count

granitsa = Predel.Value

If Bolshe.Value = True Then

For i = 1 To m

For j = 1 To n

If d.Cells(i, j).Value > granitsa Then d.Cells(i, j).Value = granitsa

Next j

Next i

Else

For i = 1 To m

For j = 1 To n

If d.Cells(i, j).Value < granitsa Then d.Cells(i, j).Value = granitsa

Next j

Next i

End If

If Summa.Value = True Then

For i = 1 To m

For j = 1 To n

s = s + d.Cells(i, j).Value

Next j

Next i

Summa_diapazona.Value = s

End If

End Sub

Основные действия, выполняемые программой, следующие. В начале программы определяются размеры области, выделенной на рабочем листе Excel (см. лабораторную работу №5). Затем в операторе granitsa = Predel.Value переменной granitsa присваивается значение счетчика Predel.Value, т.е. значение, установленное с помощью этого счетчика и отображающееся (в данном примере) в ячейке A10.

Проверяется значение переключателя Bolshe (свойство Value). Если переключатель установлен, то в выделенной области всем ячейкам, значение которых превышает переменную granitsa, присваивается значение этой переменной. Если переключатель Bolshe не установлен (значит, установлен переключатель Menshe), то, наоборот, изменяется значение ячеек в выделенной области, меньших, чем заданная величина.

В операторе If Summa.Value = True проверяется значение флажка Summa. Если оно равно True (флажок установлен), то вычисляется сумма элементов выделенного диапазона. Эта сумма присваивается свойству Value текстового поля Summa_diapazona (т.е. выводится в это поле).

Пример использования элементов управления: списки

Пример 6.2 – Пусть в столбце A рабочего листа Excel, начиная с ячейки A1, введены номера контрактов, в столбце B (начиная с B1) – их стоимости. Требуется разработать программу для отбора номеров контрактов, стоимость которых составляет не менее или, наоборот, не более (по выбору пользователя) некоторой заданной величины.

Пусть для этого предполагается разместить на рабочем листе Excel следующие элементы управления:

- кнопка Отобрать;

- список, содержащий два элемента: Не менее и Не более;

- текстовое поле.

При нажатии кнопки Отобрать должны выполняться следующие действия: если в списке выбран элемент Не менее, то должны отбираться номера контрактов, стоимость которых составляет не менее величины, указанной в текстовом поле. Если же выбран элемент Не более, то должны отбираться номера контрактов, стоимость которых не превышает величины, указанной в текстовом поле. Номера отобранных контрактов должны выводиться в столбец E, начиная с ячейки E1.

Пусть на рабочем листе Excel создана кнопка со свойствами Name – Vybor, Caption – Отобрать, а также текстовое поле со свойством Name – Granitsa.

Рассмотрим более подробно создание списка. Требуется в каком-либо месте рабочего листа Excel ввести элементы создаваемого списка. Пусть в ячейке M1 введен текст Не менее, а в ячейке M2 – Не более.

Примечание – Элементы списка могут и не перечисляться на рабочем листе Excel, а задаваться в программе. Этот способ задания элементов списка будет рассмотрен в лабораторной работе №7.

После того, как на рабочем листе Excel создан список (имя по умолчанию – ListBox1), для него необходимо указать следующие свойства: Name – Bol_men (или любое другое имя), ListFillRange – M1:M2 (т.е. диапазон ячеек, где указаны элементы списка).

Введем следующую программу, которая должна выполняться при щелчке мышью по кнопке Отобрать:

Private Sub Vybor_Click()

Set d = Range("A1").CurrentRegion

m = d.Rows.Count

x = CSng(granitsa.Value)

Set rez = Range("E1")

k = 0

If Bol_men.ListIndex = 0 Then

For i = 1 To m

If d.Cells(i, 2) >= x Then

k = k + 1

rez.Cells(k, 1) = d.Cells(i, 1)

End If

Next i

End If

If bol_men.ListIndex = 1 Then

For i = 1 To m

If d.Cells(i, 2) <= x Then

k = k + 1

rez.Cells(k, 1) = d.Cells(i, 1)

End If

Next i

End If

End Sub

Приведем некоторые пояснения по программе. Переменной x присваивается значение текстового поля granitsa; функция CSng требуется для преобразования значения этого поля (по умолчанию имеющего тип String) в тип Single, т.е. в число.

ListIndex – свойство элемента управления типа “Список” (в данном случае – списка Bol_men), представляющее собой номер текущего (выбранного) элемента списка, причем элементы списка нумеруются с нуля. Таким образом, условие bol_men.ListIndex = 0 означает, что выбран первый элемент списка (в данном случае – элемент Не менее); условие bol_men.ListIndex = 1 означает, что выбран второй элемент (Не более).

Вместо свойства ListIndex можно было бы использовать свойство Text, представляющее собой значение текущего (выбранного) элемента списка. В этом случае в программе вместо условия If bol_men.ListIndex = 0 было бы указано условие If bol_men.Text = “Не менее”, а вместо условия If bol_men.ListIndex = 1 – условие If bol_men.Text = “Не более”.

Варианты заданий

Примечание – Во всех вариантах заданий рекомендуется, разработав программу согласно заданию, внести в нее изменения, заменив переключатели на список.

ВАРИАНТ 1 – Установить на рабочем листе следующие элементы управления: два переключателя (Число и Среднее), счетчик, флажок Сумма, кнопку Подсчитать, для текстовых поля.

Разработать программу на VBA для обработки данных в диапазоне ячеек Excel, заполненном числами и выделенном с помощью мыши. При нажатии кнопки Подсчитать в заданной строке выделенного диапазона подсчитывается количество чисел, превышающих число, заданное в первом текстовом поле (если установлен переключатель Число), или среднее по этой строке (если установлен переключатель Среднее). Номер строки, для которой выполняется подсчет, задается с помощью счетчика. Подсчитанное количество отображается в ячейке справа от выбранной строки. Кроме того, если установлен флажок Сумма, то вычисляется сумма выбранной строки; она выводится во второе текстовое поле.

ВАРИАНТ 2 – Установить на рабочем листе следующие элементы управления: два переключателя (Среднее арифметическое и Среднее геометрическое), счетчик, два флажка (Строка и Столбец), кнопку Вычислить.

Разработать программу на VBA для обработки данных в диапазоне ячеек Excel, заполненном числами и выделенном с помощью мыши. При нажатии кнопки Вычислить вычисляется среднее арифметическое или среднее геометрическое (в зависимости от установленного переключателя) по строке или по столбцу выделенной области. Если установлен флажок Строка, то вычисление выполняется по строке, если установлен флажок Столбец – по столбцу; если установлены оба флажка, то вычисление выполняется и по строке, и по столбцу. Если не установлен ни один из флажков, то вычисления не выполняются. Номер строки (или столбца), для которой выполняются вычисления, задается с помощью счетчика. Результат вычисления выводится в ячейку рядом с соответствующей строкой или столбцом.

Указание – Среднее геометрическое из n чисел – это их произведение, из которого взят корень степени n.

ВАРИАНТ 3 – Установить на рабочем листе следующие элементы управления: два переключателя (Выбранный и Все), счетчик, флажок Максимальный, кнопку Вычислить, текстовое поле.

Ввести в рабочий лист исходные данные: в столбец A (начиная с ячейки A1) – фамилии работников, в столбцы B-F – их доходы за пять лет.

Разработать программу на VBA для вычисления среднего дохода каждого работника. Вычисление выполняется при нажатии кнопки Вычислить. Если при этом установлен переключатель Выбранный, то средний доход вычисляется для одного работника (его номер указывается с помощью счетчика), если установлен переключатель Все – то для всех работников. Вычисленные средние доходы выводятся в столбец G. Кроме того, если доходы вычисляются для всех работников, и при этом установлен флажок Максимальный, то в текстовом поле отображается фамилия человека с максимальным средним доходом.

ВАРИАНТ 4 – Установить на рабочем листе следующие элементы управления: два переключателя (По строке и По всему диапазону), счетчик, флажок Подсчет, три текстовых поля, кнопку Замена.

Разработать программу на VBA для обработки данных в диапазоне ячеек Excel, заполненном числами и выделенном с помощью мыши. При нажатии кнопки Замена выполняется замена числа, указанного в первом текстовом поле, на число, указанное во втором текстовом поле. В зависимости от состояния переключателей замена выполняется в одной строке выделенного диапазона или по всему диапазону. Если замена выполняется в одной строке, то ее номер задается с помощью счетчика. Кроме того, если установлен флажок Подсчет, то в третье текстовое поле выводится количество выполненных замен.

ВАРИАНТ 5 – Установить на рабочем листе следующие элементы управления: два переключателя (За последние и За все), счетчик, флажок Максимальный, кнопку Средний доход, текстовое поле.

Ввести в рабочий лист исходные данные: в столбец A (начиная с ячейки A1) – фамилии работников, в столбцы B-F – их доходы за пять лет.

Разработать программу на VBA для вычисления среднего дохода каждого работника. Вычисление выполняется при нажатии кнопки Средний доход. Если при этом установлен переключатель За последние, то средний доход вычисляется за последние годы (количество лет указывается с помощью счетчика). Если установлен переключатель За все, то средний доход вычисляется за пять лет. Вычисленные средние доходы выводятся в столбец G. Кроме того, если установлен флажок Максимальный, то в текстовом поле отображается фамилия работника с максимальным средним доходом.

ВАРИАНТ 6 – Установить на рабочем листе следующие элементы управления: два переключателя (Строки и Столбцы), два счетчика, флажок Сумма, текстовое поле, кнопку Переставить.

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

ВАРИАНТ 7 – Установить на рабочем листе следующие элементы управления: два переключателя (В начало и В конец), счетчик, флажок По всему диапазону, кнопку Переставить, текстовое поле.

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

ЛАБОРАТОРНАЯ РАБОТА №7