Методика преподавания объектно-ориентированного программирования на базе VBA в Excel.
по информатике и икт (10 класс) на тему

Методика преподавания объектно-ориентированного программирования на базе VBA в Excel.

Введение

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

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

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

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

Также необходимо добавить, что язык VBA является основой языка программирования VB, что позволяет пользователям знакомым с основами языка Visual Basic, быстро вникнуть в суть программирования на 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”)

Также нам необходимо познакомиться с основными свойствами, методами и событиями объекта Application,Workbook и Worksheet.

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

Для быстрого получения чернового варианта кода программы можно использовать макрорекордер – транслятор, создающий программу на языке 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

End Sub

Единственное изменение, которое необходимо внести в этот макрос – это вместо конкретного диапазона в первой строке кода вставить ячейку А1, что автоматически приведет к сортировки всего столбца. Тогда исправленный макрос будет иметь вид:

Sub сортировка1()

' сортировка1 Макрос

' Макрос производит сортировку по возрастанию в алфавитном порядке

Range("A1").Select

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

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 – для сортировки по убыванию.

UserForm (пользовательская форма)

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

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

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

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

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

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

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

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

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

Private Sub cmd1_Click()

Sheets("Лист1").Select

Range("A1").Value = Me.txt1

End Sub

Но данный макрос содержит одну проблему, он всегда вставляет данные только в ячейку А1 сколько бы раз мы не выполняли данную программу. Для того чтобы научить компьютер определять первую пустую ячейку в столбце А, можно воспользоваться свойством CurrentRegion объекта Range.

Введем переменную n = Worksheets(“Лист1”).Range("A1").Rows.Count, которая в будет принимать значение равное количеству заполненных строк начиная с ячейки А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

Использование программирования в среде VBA не является обязательным, но в классах с углубленным изучением информатики, можно  давать этот язык параллельно изучению Visual Basic. Это расширит возможность  применения учащимися программирования для решения задач. Кроме того необходимо добавить, что VBA позволяет создавать пользовательские функции, которые могут вычислять не стандартные, с точки зрения Excel, функции.

Приведу пример. В математике очень часто используют функции Sin(2a) и Cos(2a). Если использовать стандартные средства работы с функциями нам придется использовать функцию Sin(х), но изменяя ее на этапе определения значения аргумента функции. Кроме того, мы должны помнить, что аргументами этих функций выступают углы, выраженные в радианах, а это новое неудобство по сравнению с математикой.

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

Function Sin2a(УголГрадусы)

   x = УголГрадусы

   Pi = 3.14159265358979

   a = x * Pi / 180

   Sin2a = Sin(2 * a)

End Function

Еще одним плюсом VBA является возможность использовать в качестве аргументов переменные на русском языке. Тогда при вызове этой функции из раздела «Определенные пользователем» мы увидим следующее диалоговое окно.

Таким образом, можно создать сколь угодно сложную функцию. Единственный недостаток такого подхода в том, что эти функции нельзя будет использовать в других рабочих книгах Excel. Но и эту проблему можно решить если сохранить файл содержащий пользовательские функции с расширением .xla. С таким расширением сохраняются библиотечные файлы-надстройки. А если поместить этот файл в папку по адресу: C:\Program Files\Microsoft Office\OFFICE11\XLSTART, то данные функции будут доступны во всех вновь создаваемых книгах.

Единственным замечанием здесь может служить то, что все файлы-надстройки, расположенные по данному адресу, выполняются автоматически. Поэтому при их открытии запрос насчет нежелательности подключения макросов (возможно вредоносных) выдаваться не будет.