Цветкова О.Н., Миронова И.В. Программирование на VBA

Подождите немного. Документ загружается.

Кнопка «Записать на лист» предназначена для записи содержимого

текстового поля в текущую ячейку.

Для вывода окна на экран напишите в модуле с макросами сле-

дующий макрос:

Public Sub Редактирование()

UserForm1.TextBox1.Text = CStr(ActiveCell.Value)

UserForm1.Show

End Sub

Для вызова макроса используйте кнопку новой панели инстру-

ментов и комбинацию клавиш. Испробуйте различные варианты

оформления кнопки, создайте свой рисунок.

Ниже приведена процедура для кнопки «Записать на лист»:

Private Sub CommandButton1_Click()

If IsNumeric(TextBox1.Text) Then

ActiveCell.Value = CDbl(TextBox1.Text)

ElseIf IsDate(TextBox1.Text) Then

ActiveCell.Value = CDate(TextBox1.Text)

Else

ActiveCell.Value = CStr(TextBox1.Text)

End If

End Sub

Обратите внимание, что в приведенных выше процедурах ис-

пользуются функции преобразования типов. Они необходимы для чи-

сел и дат, чтобы распознавалась национальная настройка.

Для редактирования содержимого текущей ячейки очень удобно

также использовать свойство ControlSource элемента Поле (это свой-

ство есть и у некоторых других элементов). Значением свойства явля-

ется адрес ячейки, с которой у элемента устанавливается связь. В

этом случае при изменении содержимого ячейки значение элемента

изменяется автоматически, и наоборот. В качестве примера создайте

форму, содержащую текстовое поле:

При выводе формы на экран нужно выполнить команду:

TextBox1.ControlSource = ActiveCell.Address

Проверьте работу программы, вызывая форму для ячеек с дан-

ными различного типа и внося в них изменения.

Задание 8.3

В этом примере показано, как можно определить минимальный

и максимальный номера столбцов и строк диапазона ячеек, а также

имя текущего листа и ссылку на текущую ячейку. Указанные значе-

ния выводятся на форме.

Ниже приведен текст модуля формы. Переменная MyRng со-

держит ссылку на диапазон. Это либо выделенные ячейки, либо диа-

пазон, определенный с помощью свойства CurrentRegion (диапазон

расширен до пустых строк и столбцов).

Dim MyRng As Range

Private Sub OptionButton1_Click()

Set MyRng = Selection

ПараметрыДиапазона

End Sub

Private Sub OptionButton2_Click()

Set MyRng = ActiveCell.CurrentRegion.Cells

ПараметрыДиапазона

End Sub

Private Sub ПараметрыДиапазона()

'Имя активного листа

TextBox1.Text = ActiveSheet.Name

'Адрес активной ячейки

TextBox2.Text = ActiveCell.Address

'Номер первого столбца диапазона

TextBox3.Text = MyRng.Columns(1).Column

'Номер последнего столбца диапазона

TextBox4.Text = MyRng.Columns(MyRng.Columns.Count).Column

'Номер первой строки дипазона

TextBox5.Text = MyRng.Rows(1).Row

'Номер последней строки диапазона

TextBox6.Text = MyRng.Rows(MyRng.Rows.Count).Row

End Sub

Напишите макрос для вывода формы на экран аналогично зада-

нию 8.2.

9. Создание приложений, управляемых с помощью

диалоговых окон

Элементы Поле со списком (ComboBox), Список (ListBox)

Элемент Поле со списком даёт возможность пользователю вы-

брать нужную информацию из списка возможных значений или вве-

сти её в поле списка.

Список в элементе Поле со списком может содержать несколько

столбцов. Строки и столбцы нумеруются с 0.

Основные свойства элемента Поле со списком:

List Используется для доступа к элементу списка. В качестве

параметров указываются номера строк и столбцов. Также

можно использовать для инициализации списка. Например,

если в программе описан массив MyArray(10), то присво-

ить списку значения этого массива можно, выполнив инст-

рукцию:

MyList.List() = MyArray

Обратиться к элементу списка можно так:

MyList.List(i) = MyList.List(i) + 1

ListIndex Содержит номер текущей строки. Равен -1, если никакой

элемент не выбран.

ListCount Количество строк в списке.

RowSource Определяет источник элементов списка. В качестве значе-

ния используется ссылка на диапазон рабочего листа

Microsoft Excel. Например, MyList.RowSource = "A1:A10"

Text Содержит выбранное или введенное значение, которое

отображается в текстовом поле.

Style Определяет, как пользователь может ввести значения в по-

ле списка:

0 - поле с раскрывающимся списком. Позволяет ввести

данные, которых нет в списке;

2 - раскрывающийся список. Не позволяет ввести новые

данные.

Основные методы элемента Поле со списком:

AddItem Object.AddItem строка[, индекс]

Добавляет элемент строка в список. Если задан индекс, то

элемент помещается в указанную позицию. Если индекс не

задан, то элемент добавляется в конец списка.

RemoveItem Object.RemoveItem (индекс)

Удаляет из списка элемент с заданным индексом

Clear Удаляет все строки из списка.

Основные события элемента Поле со списком:

Change Введено или выбрано новое значение списка.

Enter Возникает перед тем как элемент получит фокус от друго-

го элемента на этой же форме.

Exit Возникает непосредственно перед тем, как фокус будет пе-

редан от данного элемента другому на той же форме.

Элемент Список применяется для хранения списка значений. Во

время работы приложения пользователь может выбрать из списка од-

но или несколько значений.

Основные свойства элемента Список:

List, ListCount,

RowSource

Имеют такой же смысл, как у элемента Поле со списком.

ListIndex Содержит номер текущей строки. Равно -1, если никакой

элемент не выбран. Если выделено несколько строк, то

равно номеру строки, которая имеет фокус.

MultiSelect Определяет, можно ли выделить несколько элементов спи-

ска.

Selected Массив, состоящий из того же количества элементов, что и

список. Для каждого элемента списка свойство равно True,

если элемент выделен, и False, если нет.

Text Возвращает выбранный в списке элемент.

Основные методы и события элемента Список такие же, как у

элемента Поле со списком.

Практические задания

Задание 9.1

Поместите на рабочий лист таблицу:

A B C D

ФИО Отдел Должность Стаж

Иванов И.И. 1 нач. отдела 19

Васильева В.В. 1 менеджер 11

Петров П.П. 2 нач. отдела 10

Коваль К.К. 2 рук. группы 5

Акимов А.А. 3 менеджер 8

Белов Б.Б. 2 менеджер 3

Волков В.В. 3 нач. отдела 12

Голиков Г.Г. 2 менеджер 7

Создайте форму, которая позволяет выполнять следующие опе-

рации с данными из этой таблицы: "Загрузить все данные", "Загру-

зить с учетом условий", "Новая таблица с учетом условий", "Таблица

из выделенных".

Операция "Загрузить все данные" предполагает, что все строки

таблицы за исключением заголовка и все столбцы помещаются в спи-

сок сотрудников, имеющийся на форме (элемент ListBox1).

Операция "Загрузить с учетом условий" помещает в список

только те строки, для которых выполняются заданные условия.

Операция "Новая таблица с учетом условий" формирует на но-

вом листе таблицу такой же структуры, содержащую только те стро-

ки исходной таблицы, которые удовлетворяют заданным условиям.

Операция "Таблица из выделенных" формирует на новом листе

таблицу точно такой же структуры, содержащую выделенные строки

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

произвольных элементов, значение свойства MultiSelect у списка

должно быть равно 1.

Кроме элемента ListBox на форме имеются 3 элемента

ComboBox. Они содержат список отделов, список должностей и спи-

сок операций. Выбранная операция выполняется при нажатии на

кнопку «Выполнить».

Далее приведен текст модуля формы. Разберитесь, как работает

программа. Особое внимание обратите на реализацию работы со спи-

сками. Значения всех списков формируются по-разному. Это сделано

специально, чтобы показать имеющиеся возможности. Узнать номер

выбранного элемента списка можно, используя свойство ListIndex.

Для анализа выбранной операции в примере использована инструк-

ция Select Case. Проверка условия запрограммирована в виде специ-

альной функции Условие. Процедура НоваяТаблица добавляет в ра-

бочую книгу новый рабочий лист и копирует на него шапку таблицы.

Dim MyRng As Range

Dim RMin As Integer, RMax As Integer

Dim CMin As Integer, CMax As Integer

Dim CCount As Integer

Dim MySheet As Worksheet

Dim NewSheet As Worksheet

Private Sub CommandButton1_Click()

Select Case ComboBox3.ListIndex

Case 0 'Загрузить все данные

MySheet.Activate

ListBox1.RowSource = Range(Cells(RMin, CMin), _

Cells(RMax, CMax)).Address

ListBox1.ListIndex = 0

Case 1 'Загрузить с учетом условий

ListBox1.RowSource = ""

j = 0

For i = RMin To RMax

If Условие(MySheet.Cells(i, 2), MySheet.Cells(i, 3), _

MySheet.Cells(i, 4)) Then

ListBox1.AddItem MySheet.Cells(i, 1)

ListBox1.List(j, 1) = MySheet.Cells(i, 2)

ListBox1.List(j, 2) = MySheet.Cells(i, 3)

ListBox1.List(j, 3) = MySheet.Cells(i, 4)

j = j + 1

End If

ListBox1.ListIndex = 0

Case 2 'Hовая таблица с учетом условий

НоваяТаблица

j = RMin - 1

For i = RMin To RMax

If Условие(MySheet.Cells(i, 2), MySheet.Cells(i, 3), _

MySheet.Cells(i, 4)) Then

j = j + 1

For k = CMin To CMax

NewSheet.Cells(j, k) = MySheet.Cells(i, k)

Next k

End If

Case 3 'Таблица из выделенных

НоваяТаблица

j = RMin - 1

For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) Then

j = j + 1

For k = CMin To CMax

NewSheet.Cells(j, k) = ListBox1.List(i, k - 1)

Next k

End If

Next i

End Select

End Sub

Private Sub UserForm_Initialize()

Dim Отделы(3) As String

Set MyRng = ActiveCell.CurrentRegion.Cells

Set MySheet = ActiveSheet

RMin = MyRng.Rows(1).Row + 1

RMax = MyRng.Rows(MyRng.Rows.Count).Row

CMin = MyRng.Columns(1).Column

CMax = MyRng.Columns(MyRng.Columns.Count).Column

CCount = MyRng.Columns.Count

'Для заполнения списка используется свойство RowSource

ListBox1.ColumnCount = CCount

ListBox1.RowSource = _

Range(Cells(RMin, CMin), Cells(RMax, CMax)).Address

ListBox1.ListIndex = 0

ListBox1.MultiSelect = fmMultiSelectMulti

'Для заполнения списка используется массив,

'созданный в программе

Отделы(0) = "все": Отделы(1) = "1"

Отделы(2) = "2": Отделы(3) = "3"

ComboBox1.List = Отделы

ComboBox1.ListIndex = 0

'Для заполнения списка используется метод AddItem

ComboBox2.AddItem "все"

For i = RMin To RMax

НетДолжности = True

For j = 0 To ComboBox2.ListCount - 1

If ComboBox2.List(j) = Cells(i, 3) Then

НетДолжности = False: Exit For

End If

Next j

If НетДолжности Then ComboBox2.AddItem Cells(i, 3)

ComboBox2.ListIndex = 0

'Для заполнения списка используется массив фиксированных значений

ComboBox3.Style = fmStyleDropDownList

ComboBox3.List = Array("Загрузить все данные", _

"Загрузить с учетом условий", "Новая таблица с учетом условий", _

"Таблица из выделенных")

ComboBox3.ListIndex = 0

End Sub

Private Function Условие(Отдел As String, Должность As String, Стаж As Single)

If TextBox1.Text = "" Then СтажМ = 0 Else СтажМ = CSng(TextBox1.Text)

If TextBox2.Text = "" Then СтажБ = 0 Else СтажБ = CSng(TextBox2.Text)

Условие = ((ComboBox1.Value = "все") Or (ComboBox1.Value = Отдел)) And _

((ComboBox2.Value = "все") Or (ComboBox2.Value = Должность)) And _

((TextBox1.Text = "") Or (Стаж < СтажМ)) And _

((TextBox2.Text = "") Or (Стаж > СтажБ))

End Function

Public Sub НоваяТаблица()

Sheets.Add after:=Worksheets(Worksheets.Count)

Sheets(Worksheets.Count).Name = InputBox("Введите имя листа")

Set NewSheet = Sheets(Worksheets.Count)

n = RMin - 1

For k = CMin To CMax

NewSheet.Cells(n, k) = MySheet.Cells(n, k)

NewSheet.Columns(k).ColumnWidth = MySheet.Columns(k).ColumnWidth

Next k

End Sub

Задание 9.2

Создадим форму, которая позволяет редактировать имеющуюся

на листе таблицу: добавлять, удалять, изменять значения строк (запи-

сей). Для просмотра таблицы в форме имеется элемент ListBox.

Свойство MultiSelect этого элемента рано 2, что позволяет выделять

любое количество записей. Кнопка «Удалить» предназначена для

удаления выделенных записей. Кнопка «Новая запись» позволяет до-

бавить новую запись перед текущей, причем одновременно в элемент

ListBox и на рабочий лист. Кнопка «Изменить» служит для редакти-

рования текущей записи. В этом случае выводится другая форма (она

приведена ниже), на которой имеются элементы, показывающие зна-

чения полей. Кнопка «Выход», как обычно, используется для завер-

шения работы.