Пользовательские формы в Excel.

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

Необходимо помнить, что MS Excel – это мощное и гибкое средство разработки информационных систем, как простых, так и сложных, с индивидуальным интерфейсом, максимально приспособленным для решения конкретной задаче. MS Excel совмещает в себе как преимущества электронных таблиц с большим количеством встроенных функций, так и средств визуального программирования по средствам Visual Basic for Applications. VBA позволяет автоматизировать весь комплекс работ от сбора информации, ее обработки, анализа до создания отчетной документации и ее публикации как для офисного пользования, так и на Web-узле.

Отдельно необходимо сказать о встроенных интеллектуальных средствах, которые быстро позволяют даже начинающему пользователю самостоятельно разрабатывать профессиональные приложения. Новым примером встроенных интеллектуальных средств VBA, в отличие от VB, является макрорекордер, который переводит все вручную выполняемые действия на рабочем листе Excel на язык VBA. Таким образом, макрорекордер позволяет поручить компьютеру самому создавать большие куски кода разрабатываемого приложения. Макрорекордер создает стандартный программный модуль, что дает нам возможность в будущем редактировать его.

Исходя из всего выше сказанного, преподавая Visual Basic в старших классах нельзя не затронуть тему Visual Basic для приложений (Visual Basic for Applications сокращенно VBA). Данный язык по своим способностям ни чем не уступает Visual Basic, а в совокупности с функциями приложений даже превосходит его, если не по мощности, то по удобству и быстроте выполнения многих специфических функций (хранение и сортировка данных, выполнения различного рода расчетов и т.д.). Поэтому в 11 классе, после изучения языка программирования Visual Basic и электронных таблиц Excel, я ввожу дополнительно 5 часов для работы на языке VBA в Excel. На мой взгляд, это позволяет ученикам лучше понять объектную модель любого приложения из пакета Microsoft Office, и дает возможность сравнить программирование, в общем, на одном и том же языке, но в различных средах. При этом не следует забывать что, работая только на уровне «Рабочей Области» и «Панелей инструментов», мы используем только 10% возможностей любого приложения.

Разработка уроков по теме
"Создание приложений на основе Excel и языка программирования
Visual Basic for Applications"

 

Данное электронное пособие является продолжением работы с языком программирования Visual Basic и может быть использовано после работы с пособием «Разработка уроков по теме "Основы объектно-ориентированного программирования и алгоритмизации в Visual Basic", которое было разработано мною в 2007 году.

Также данное пособие можно использовать, как начальное введение в язык программирования Visual Basic. Способ подачи материала и уровень сложности можно регулировать в зависимости от уровня подготовки учащихся. Для ознакомления рекомендую давать код в отпечатанном варианте, с предварительным объяснением принципов его работы. В классах, где учащиеся знакомы с языком программирования Visual Basic, предлагаю больший упор делать на объектную модель Excel и на основные свойства, события и методы главных объектов. Таких как Application, Workbook, Worksheet и Range.

Данное пособие состоит из следующих уроков:
Урок 1. Особенности языка VBA в Excel. Запись макросов с помощью макрорекордера.
Урок 2. Панель управления «Элементы управления».
Контрольный проект "Табель успеваемости".
Урок 3. Особенности языка VBA в Excel. Запись макросов с помощью макрорекордера.
Урок 4. Проект «Табель успеваемости». Создание формы "Ввод оценок".
Урок 5. Проект «Табель успеваемости». Создание формы просмотр оценок и собственного меню.

© Белкин Виктор, 2008

Все разработку можно скачать с сайта "Роялти - педагогу-новатору". Подробнее...

Урок 1. Особенности языка VBA в Excel. Запись макросов с помощью макрорекордера.

Цель занятия: Рассказать об особенностях языка VBA в Excel, научить пользоваться макрорекордером для записи макросов и редактировать их.

Вступление.

MS Excel является очень популярным средством обработки данных, поэтому широко используется как разработчиками, так и простыми пользователями. Но примерно 90% от общего числа пользователей работают только со средствами рабочего листа Excel без применения языка программирования VBA (Visual Basic for Applications), а значит, они используют не более 10% от реальных возможностей Excel. Таким образом, большая часть средств Excel остаётся невостребованной просто потому, что многие не знают о наличии VBA или не умеют им пользоваться.
На самом деле VBA – относительно простой и легкий язык программирования. Он прост в освоении и позволяет быстро получать ощутимые результаты – конструировать профессиональные приложения, решающие практически все задачи в среде Windows.
VBA использует технологию визуального программирования, т.е. конструирование рабочей поверхности приложения и элементов его управления непосредственно на экране, а также запись всей программы или ее частей при помощи макрорекордера.

Макрорекордер является встроенным интеллектуальным средством VBA, которое переводит все вручную выполняемые пользователем действия на рабочем листе Excel на язык VBA.

Также необходимо добавить, что язык VBA очень похож на язык программирования VB, что позволяет пользователям знакомым с основами языка VB, быстро вникнуть в суть программирования на VBA.
В конце вступления можно только добавить, что многие магазины и салоны используют Excel и созданные с помощью VBA приложения для своей повседневной работы ("Книги и книжечки", салон БМВ "Балтавтотрейд").

Особенности языка VBA в Excel.

Объектная модель Excel представляет собой иерархию объектов, подчиненных одному объекту Application, который соответствует самому приложению Excel. По аналогии этот объект соответствует объекту Form в VB. VBA точно также, как в VB используются полные и неявные ссылки на объект.
Например, полная ссылка на ячейку А1 рабочего листа Данные рабочей книги с именем Архив имеет следующий вид:
Application. Workbooks(“Архив”).Worksheets(“Данные”).Range(“A1”).
Но обычно работают с рабочей книгой или рабочим листом, поэтому мы будем использовать ссылки в неявном виде:
Workbooks(“Архив”).Worksheets(“Данные”).Range(“A1”) или Worksheets(“Данные”).Range(“A1”).

Работа с макрорекордером.

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

Запустим программу Excel, и активизируем макрорекордер выбрав команду Сервис, Макрос, Начать запись. Появиться диалоговая панель Запись макроса (рис. 1). Это диалоговая панель позволяет задать параметры макроса.

Рис. 1
Пока мы будем работать только с полем Имя макроса, все остальные параметры лучше оставить без изменений. После щелчка по кнопке ОК появиться плавающая панель (рис. 2) с кнопкой Остановить запись.

Рис. 2

Для просмотра только что записанного макроса выберите команду Сервис, Макрос, Макросы, что приведет к отображению диалоговой панели Макрос (рис. 3)

Рис. 3

В диалоговом окне Макрос в списке выделите макрос и нажмите кнопку Изменить.
На экране отобразиться окно редактора VBA с активизированным стандартным модулем (рис. 4), в котором будет код только что записанного макроса.

Рис. 4

Sub Макрос1()
' Макрос1 Макрос
' Макрос записан 01.08.2008 (Виктор)
Sheets("Лист2").Select
ActiveCell.FormulaR1C1 = "234"
Range("A2").Select
ActiveCell.FormulaR1C1 = "125"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("A4").Select
End Sub

Записанный макрорекордером код надо рассматривать только как подсказку со стороны компьютера. В данном случае лучше записать данный макрос в следующем виде:
Sub Макрос1()
' Макрос1 Макрос
' Макрос записан 01.08.2008 (Виктор)
Sheets("Лист2").Select
Range("A1").Value = "234"
Range("A2").Value = "125"
Range("A3").Formula = "=A1+A2"
End Sub
Такой макрос более понятен, так как мы используем знакомое по VB свойство Value (Значение) и стиль ссылок в формуле привычный для Excel.

Теперь давайте запишем макрос сортировки данных. Для этого введем в столбец А набор однотипных данных, например текстовых. Вызовем макрорекордер и запишем процесс сортировки данных по возрастанию и убыванию.
Сортировка по возрастанию в алфавитном порядке.
Sub сортировка1()
' сортировка1 Макрос
' Макрос производит сортировку по возрастанию в алфавитном порядке
Range("A1:A9").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Единственное изменение, которое необходимо внести в этот макрос – это вместо конкретного диапазона в первой строке кода выделить ячейку А1. Тогда исправленный макрос будет иметь вид:
Sub сортировка1()
' сортировка1 Макрос
' Макрос производит сортировку по возрастанию в алфавитном порядке
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Макрос для обратной сортировки будет иметь вид:
Sub сортировка2()
' сортировка2 Макрос
' Макрос производит сортировку по убыванию в алфавитном порядке
Range("A1:A9").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Или в исправленном виде:
Sub сортировка2()
' сортировка2 Макрос
' Макрос производит сортировку по убыванию в алфавитном порядке
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Обращаем внимание, что за порядок сортировки отвечает параметр Order1, который принимает значение xlAscending для сортировки по возрастанию и xlDescending – для сортировки по убыванию.

Вверх

Урок 2. Форма и Элементы управления.

Цель занятия: Научить пользоваться панелью инструментов VBA и использовать формы для создания собственного интерфейса Рабочей книги.

Введение

По своей сути форма (или пользовательская форма UserForm) представляет собой диалоговое окно, в котором можно размещать различные элементы управления. В приложении может быть как одна, так и несколько форм. Основные свойства формы Name и Caption имеют такое же значение, как и в среде программирования VB. Метод Show отображает форму на экране, Hide – закрывает форму.

В VBA имеется обширный набор встроенных элементов управления. Используя этот набор и редактор форм, нетрудно создать любой пользовательский интерфейс, который будет удовлетворять всем требованиям, предъявляемым к интерфейсу в среде Windows.

Добавление формы в проект

1. Перейдите в редактор Visual Basic (ALT+F11).
2. Выберите команду Insert, UserForm.

В проекте появиться новая форма (рис. 1).

Рис. 1. Новая форма в редакторе Visual Basic

Как уже говорилось выше, пользовательская форма в Excel имеет точно такие же свойства, методы и события, как и стандартная форма Visual Basic6. Для желающих узнать больше, по данному вопросу, рекомендую скачать с сайта Роялти разработку 54 уроков по программированию в среде Visual Basic6, учителя информатики гимназии №2, г. Гурьевска Белкина В.В. Данная разработка содержит не только поурочные планы, но и содержит большой справочник по VB. Также в формате Word здесь содержатся таблицы с основными свойствами, методами и событиями многих элементов управления и формы.

В качестве примера создадим форму с помощью, которой будем вводить данные в определенные ячейки рабочего листа. Для этого разместим на форме элементы управления TextBox и CommandButton. Создадим, обработчик события Click для кнопки, результатом работы которого будет последовательный ввод данных в пустые ячейки столбца А.

Элемент управления	Свойство	Значение
UserForm	Caption	Ввод данных
TextBox	Name	txt1
	Text
CommandButton	Name	cmd1
	Caption	Ввод

Для перехода к обработчику события Click кнопки Ввод, дважды щелкните по ней. Для того чтобы написать код можно воспользоваться макрорекордером или материалом предыдущего урока.
Private Sub cmd1_Click()
Sheets("Лист1").Select
Range("A1").Value = Me.txt1
End Sub
Но данный макрос содержит одну проблему, он всегда вставляет данные только в ячейку А1 сколько бы раз мы не выполняли данную программу. Для того чтобы научить компьютер определять первую пустую ячейку в столбце А, можно воспользоваться свойством CurrentRegion объекта Range.
n = Worksheets(“Лист1”).Range("A1").CurrentRegion.Rows.Count – в данном примере переменная n примет значение равное количеству заполненных строк начиная с ячейки А1. Кроме того мы воспользуемся свойством Cells объекта Worksheets, которое возвращает семейство всех ячеек рабочего листа или объект Range – конкретную ячейку если в скобках указаны номер строки и номер столбца.
Тогда макрос примет следующий вид:
Private Sub cmd1_Click()
Sheets("Лист1").Select
n = Range("A1").CurrentRegion.Rows.Count
Cells(n + 1, 1).Value = Me.txt1
End Sub

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

Данный макрос содержит ошибку, если лист чистый то данные будут начинать вставляться всегда с ячейки А2. Это связанно с тем что в проверяемом диапазоне уже есть сама ячейка А1, которая считается заполненной по умолчанию.

Задание для самостоятельной работы.
Попробуйте использовать макрос на чистом листе и вы увидите, что данные будут записываться только начиная с ячейки А2. Исправите макрос так, чтобы он всегда работал правильно.
Решение. Необходимо проверить ячейку А1, если она не содержит данные то n присвоить 0.
Private Sub cmd1_Click()
Sheets("Лист1").Select
n = Range("A1").CurrentRegion.Rows.Count
If Cells(1, 1).Value = "" Then n = 0
Cells(n + 1, 1).Value = Me.txt1
End Sub

Вверх

Урок 3. Проект «Табель успеваемости».

Цель занятия: Показать практическое применение VBA совместно с Excel.

Введение

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

Для лучшего понимания проблемы можно просмотреть следующую презентацию: «VBA-приложения в Excel».

Работа над проектом.

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

Пользовательские формы очень часто применяются тогда, когда необходимо защитить рабочий лист от несанкционированных действий пользователя с одной стороны и унифицировать работу с приложением с другой стороны. Для примера можно рассмотреть приложения на основе Excel, которые выполняют расчет заработной платы работников школы или ведут учет материально-технической базы предприятия (слайды №3-4).

Постановка задачи.

Создать приложение, которое должно иметь (слайд №6):

1. Удобный способ хранения информации.
2. Возможность быстрого ввода оценок по всем предметам и возможность просмотра всех оценок за отдельный день.
3. Возможность просмотра всех оценок и среднего балла по всем предметам.
4. Графический пользовательский интерфейс (свое меню).

Логично разбить все задачу в соответствии с требованиями, предъявляемыми к приложению, тогда это будет выглядеть следующим образом.
Подзадачи:

1. Организация удобного способа хранения информации.
2. Ввод оценок по нескольким предметам сразу.
3. Просмотр оценок и среднего балла по всем предметам.
4. Создание собственного меню.

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

Этапы создания рабочей книги

Теперь можно приступить к созданию нашего приложения на основе рабочей книги Excel. Так как все листы будут хранить однотипную информацию, то нет необходимости создавать все листы с нуля, мы пойдем более легким путем. Создадим один лист, зададим все необходимое форматирование и скопируем его столько раз, сколько предметов нас будет интересовать (слайды №9-16). Для упрощения проекта выберем 10 предметов.

Создание формы

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

Как видно из рисунка, форма содержит: 11 надписей (Label), 10 текстовых полей (TextBox), 10 рамок (Frame) внутри каждой по 3 переключателя (OptionButton) и одного календаря (DTPicker). Все перечисленные элементы управления (ЭУ), кроме календаря являются основными и всегда располагаются на панели инструментов VBA, для использования календаря его необходимо подключить. Для этого надо вызвать контекстное меню панели инструментов и выбрать пункт Additional Controls…
В появившемся диалоговом окне установить флажок напротив пункта: Microsoft Date and Time Picker Control (слайды №18-22).

Далее создаем заготовку под будущее приложение. Сохраняем рабочую книгу под именем «Табель успеваемости». Переходим в редактор VBA (Alt+F11) и добавляем объект UserForm (пользовательская форма). Задаем следующие значения свойств формы.

Свойство	Значение
Name	frmВвод
Caption	Ввод оценок
Height	380
Width	575

Работа с элементами управления.

Этапы создания ЭУ (слайды №23-26).

1. Создать календарь (DTPicker)
2. Создать две надписи (Label)
3. Создать текстовое поле (TextBox)
4. Создать рамку (Frame)
5. Внутри рамки разместить 3 переключателя (OptionButton)

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

Элемент управления	Свойство	Значение
DTPicker	Name	dtpДата
Label	Caption	Дата проведения занятия
Label	Caption	Русский язык
Frame	Caption	Тип урока
TextBox	Name	txtРусс
	Tag	Русский язык
OptionButton	Name	opt1Русс
		opt2Русс
		opt3Русс

Для того чтобы программа могла определять в какой рабочий лист вносить изменение, зададим каждому текстовому полю значение свойства Tag=имени предмета. Это связано с тем что все листы носят название соответствующих предметов, что позволит безошибочно определить куда вносить изменения. Для первого предмета – это Русский язык, для второго – Литература и т.д.

Вверх

Урок 4. Проект «Табель успеваемости». Создание формы "Ввод оценок".

Цель занятия: Объяснить основные приемы записи кода в редакторе VBA.

Введение

Сегодня на уроке мы продолжим работать над приложением «Табель успеваемости». Нам необходимо расположить ЭУ на форме и создать код обеспечивающий вод данных в соответствующие листы рабочей книги.

Создание остальных элементов управления.

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

Алгоритм создание ЭУ.

1. Выделить созданные ЭУ.
2. Скопировать их.
3. Вставить из буфера обмена и задать новые имена и значения свойств.
• Имена должны различаться только в русскоязычной части.
• Русскоязычная часть состоит из первых 4-х букв названия рабочего листа (Русс, Лите, Алге, и т.д.).

Вставка кнопок управления.

Для отображения всех оценок и среднего балла, ввода оценок, очистки текстовых полей и закрытия формы нам понадобятся 4 кнопки с именами: Просмотр, Ввод, Очистить и Выход (слайды №30-36).

Элемент управления	Свойство	Значение
CommandButton	Name	cmdПросмотр
	Caption	Просмотр
	Name	cmd Ввод
	Caption	Ввод
	Name	cmdОчистить
	Caption	Очистить
	Name	cmdВыход
	Caption	Выход

Две последние кнопки работают очень просто, поэтому у них маленький код. Кнопка Выход завершает работу всего приложения без генерации, каких либо событий.
Private Sub cmdВыход_Click()
End
End Sub

Кнопка Очистить очищает все поля ввода формы, т.е. присваивает свойству Text пустую строку, для определения полей мы будем использовать свойство Tag элементов, только у текстовых полей ввода оно равно не пустой строке.
Private Sub cmdОчистить_Click()
Dim txt As Object
For Each txt In Controls
If txt.Tag <> "" Then
txt.Text = ""
End If
Next txt
End Sub

Кнопка Ввод должна определить, по какому предмету поставлена оценка, и выставить ее в первую свободную строку соответствующего листа.
Private Sub cmdВвод_Click()
Dim txt As Object
For Each txt In Controls
If txt.Tag <> "" Then
strОценка = Trim(txt.Text)
If strОценка <> "" Then
strИмяЛиста = txt.Tag
Worksheets(strИмяЛиста).Select
intКолСтрок=Worksheets(strИмяЛиста).Range("A1").CurrentRegion.Rows.Count
'определяем кол-во пустых строк по ячейке
'содержащей дату начиная с ячейки A1
'вводим дату, оценку, примечания и вес в следующую строку
Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 1).Value = dtpДата.Value
Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 2).Value = strОценка
strИмя1 = "opt1" & Mid(strИмяЛиста, 1, 4)
strИмя2 = "opt2" & Mid(strИмяЛиста, 1, 4)
strИмя3 = "opt3" & Mid(strИмяЛиста, 1, 4)
If Me.Controls(strИмя1).Value Then
strВес = "1"
strТип = "обычный"
ElseIf Me.Controls(strИмя2).Value Then
strВес = "1.5"
strТип = "самостоятельная работа"
ElseIf Me.Controls(strИмя3).Value Then
strВес = "2"
strТип = "контрольная работа"
End If
Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 3).Value = strТип
Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 4).Value = strВес
Columns("A:C").EntireColumn.AutoFit
'сортировка по дате
Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End If
Next txt
End Sub

Для совместной работы всех частей приложения необходимо ввести переменные уровня модуля.
Dim strОценка As String
Dim strТип As String
Dim strИмяПоля As String
Dim strИмя1 As String
Dim strИмя2 As String
Dim strИмя3 As String
Dim strВес As String
Dim strДата As Date
Dim intКолСтрок As Integer
Dim intКолЛист As Integer
Dim i As Byte
Dim strИмяЛиста As String

Чтобы календарь показывал текущую дату, при появлении формы на экране, введем в код инициализации формы следующие команды.
Private Sub UserForm_Initialize()
Me.dtpДата.Value = Date
Me.Show
End Sub

Код кнопки Просмотр, закрывает форму ввода и отображает форму просмотра оценок.
Private Sub cmdПросмотр_Click()
Me.Hide
frmПросмотр.Show
End Sub

Вверх

Урок 5. Проект «Табель успеваемости». Создание формы просмотр оценок и собственного меню.

Цель занятия: Закончить работу над приложением, отладить программный код.

Введение

Сегодня на уроке мы продолжим работать над приложением «Табель успеваемости». Нам необходимо создать форму Просмотра оценок и написать код обеспечивающий работу формы (слайды №37-41).

Создание формы просмотра оценок.

Форма содержит текстовые поля для ввода всех оценок по предмету. Их имена совпадают с именами полей ввода оценок в рабочий лист (txtРусс, txtЛите и т.д.). Также форма содержит поля для вывода среднего балла по предмету, их имена образуются из первых путем добавления к ним букв Ср (txtРуссСр, txtЛитеСр и т.д.).

Элемент управления	Свойство	Значение
Форма	Caption	Просмотр всех оценок и среднего балла на данный момент
	Name	frmПросмотр
	Height	395
	Width	490
Текстовые поля (первая группа)	Name	От txtРусс до txtФизк
Текстовые поля (вторая группа)	Name	От txtРуссСр до txtФизкСр
Командная кнопка	Name	cmdВвод
	Caption	Ввод и редактирование
Командная кнопка	Name	cmdВыход
	Caption	Выход

Кнопки ввод и выход имеет следующий код:
Private Sub cmdВвод_Click()
Me.Hide ‘ закрывается текущая форма
frmВвод.Show ‘открывается форма Ввода оценок
End Sub

Private Sub cmdВыход_Click()
End
End Sub

Теперь можно приступить к объяснению работы основного кода, записанного в событии инициализации формы. Первоначально вводим три переменные, роль которых понятна из их названий. Строковая переменная strВсеОценки хранит все оценки по данному предмету, переменная целого типа intСуммОценки хранит сумму всех оценок, с учетом их «веса» по этому же предмету и переменная sngСуммБалл – хранит средний балл по предмету.

Private Sub UserForm_Initialize()
Dim strВсеОценки As String
Dim intСуммОценки As Integer
Dim sngСуммБалл As Single

Далее в цикле мы проходим все листы, используя свойство Count рабочего листа Worksheets, которое вернет количество листов в книге. Выделяем i-ый лист определяем его имя, находим число занятых строк. Определяем в какие текстовые поля будем выводить оценки strИмяПоля = "txt" & Mid(strИмяЛиста, 1, 4) и strИмяПоля1 = "txt" & Mid(strИмяЛиста, 1, 4) & "Ср".
Потом в цикле, начиная с ячейке А2, пробегаем все занятые строки и считаем средний балл, и набираем строку всех оценок.
После завершения внутреннего цикла выводим полученный результат используя команды форматирования в соответствующие поля нашей формы. Но для среднего балла, чтобы исключить деление на 0 используем проверку на не равенство 0 суммы всех оценок.

For i = 1 To Worksheets.Count
Worksheets(i).Select
strИмяЛиста = ActiveSheet.Name
intКолСтрок = Worksheets(i).Range("A1").CurrentRegion.Rows.Count
strИмяПоля = "txt" & Mid(strИмяЛиста, 1, 4)
strИмяПоля1 = "txt" & Mid(strИмяЛиста, 1, 4) & "Ср"
For j = 2 To intКолСтрок
intСуммОценки = intСуммОценки + (Cells(j, 2).Value * Cells(j, 4))
sngСуммБалл = sngСуммБалл + Cells(j, 4).Value
strВсеОценки = strВсеОценки & " " & Cells(j, 2).Value
Next j
Me.Controls(strИмяПоля).Text = Trim(strВсеОценки)
If sngСуммБалл <> 0 Then
Me.Controls(strИмяПоля1).Text=CStr(Format((intСуммОценки)/(sngСуммБалл),"fixed"))
Else
Me.Controls(strИмяПоля1).Text = CStr(Format(0, "fixed"))
End If
strВсеОценки = ""
intСуммОценки = 0
sngСуммБалл = 0
Next i
End Sub

После завершения работы над формами необходимо поговорить о том, как вызывать созданные формы непосредственно из Excel, не входя в среду разработки VBA. Здесь можно пойти двумя путями, первый путь это использование кнопок на рабочем листе и второй – это создание собственного меню.
Объясню сразу чем мне не нравиться первый путь. Для его реализации придется создавать по две командные кнопки на каждом листе и их место положение будет жестко зафиксировано.
Создание собственного меню, дает возможность расположить его непосредственно верхней части окна приложения среди других панелей инструментов. Кроме того можно отключить ненужные в данном приложении панели инструментов, не только используя команды меню Вид, но и программно. Покажем, как это можно сделать (слайды №42-46).
Сначала создадим код, убирающий ненужные панели инструментов, воспользуемся для этого макрорекордером. Вызовем макрорекордер и отключим панели инструментов: форматирование и стандартная. Мы получим следующий код:

Sub Макрос5()
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
End Sub

Для того чтобы создать собственную панель инструментов войдем в редактор VBA и в правой панели проектов дважды щелкним по объекту «ЭтаКнига». Верхнем левом поле выберем объкт Workbook, а в правом событие Open и введем туда следующий код (кроме первой и последней строк).

Private Sub Workbook_Open()
Dim MyBar As CommandBar
Dim MyButton(1 To 2) As CommandBarButton
'Отключение панелей Стандартная, Форматирование и Строки формул.
With Application
.DisplayFormulaBar = False
.CommandBars("Formatting").Visible = False
.CommandBars("Standard").Visible = False
End With
'Создание своей панели
Set MyBar = Application.CommandBars.Add(Name:="Моя панель", _
Position:=msoBarTop, MenuBar:=False, Temporary:=True)
'Создание своих кнопок
With MyBar
.Visible = True
.Protection = msoBarNoMove + msoBarNoChangeVisible + msoBarNoCustomize
With .Controls
Set MyButton(1) = .Add(Type:=msoControlButton, ID:=1, Temporary:=True)
Set MyButton(2) = .Add(Type:=msoControlButton, ID:=1, Temporary:=True)
End With
End With
'Установка свойств кнопок
With MyButton(1)
.Caption = "Просмотр"
.TooltipText = "Просмотр всех оценок"
.Style = msoButtonCaption
.OnAction = "Просмотр"
End With
With MyButton(2)
.Caption = "Ввод"
.TooltipText = "Ввод оценок"
.Style = msoButtonCaption
.OnAction = "Ввод"
End With
End Sub

Для того чтобы созданное меню заработало необходимо, в стандартном модуле VBA, добавить следующий код, который будет соответственно привязан к строкам OnAction = "Просмотр" и OnAction = "Ввод".

Sub Ввод()
frmВвод.Show
End Sub

Sub Просмотр()
frmПросмотр.Show
End Sub

После закрытия приложения Excel и его последующего открытия, с другими рабочими книгами, все панели будут на своих местах. Но этого не произойдет при закрытии рабочей книги «Табель успеваемости». Для возврата к первоначальному состоянию окна приложения, после закрытия книги, необходимо добавить следующих код в событие BeforeClose объекта Workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.DisplayFormulaBar = True
.CommandBars("Formatting").Visible = True
.CommandBars("Standard").Visible = True
End With
'Удаление созданной панели
Dim bar As CommandBar
For Each bar In Me.Application.CommandBars
If Not bar.BuiltIn Then bar.Delete
Next
End Sub

Как мы видим, из кода, панели инструментов возвращаются практически так же, как и удалялись. Просто свойству Visible присваиваем значение True.

Задание для самостоятельной работы.

Описать событие Change() для ЭУ Microsoft Date and Picker Control, так чтобы при изменение даты выводились все оценки по предметам за этот день.
Подсказка. Данное событие противоположно событию Click() для кнопки Ввод формы «Ввод оценок».

Вверх
Полезные советы и ссылки

1. Для начинающих программистов и интересующихся дополнительными возможностями Excel советую прочитать книгу Гарнаева А.Ю. "Excel, VBA, Internet в экономики и финансах." - СПб.: БХВ-Петербург. Книга является руководством по использованию Microsoft Excel, разработке бизнес-приложений средствами VBA и конструированию Web-страниц на базе DHTML и VBScript.
2. Еще одна книга Гарнаева А.Ю. "Microsoft Excel 2000: разработка приложений." - СПб.: БХВ - Санкт-Петербург. Книга является руководством по разработке приложений средствами Microsoft Excel 2000 и Visual Basic for Applications (VBA) и содержит ответы на большинство вопросов, возникающих при создании собственных систем. <