 |
Visual Basic for Applications (VBA)
|
|
|
|
Программирование в Excel, в основном, сводится к управлению объектами. Эта задача выполняется с помощью инструкций, введённых на языке, понятном Excel.
Visual Basic For Application (далее VBA) – немного упрощённая реализация языка программирования Visual Basic, встроенная в линейку продуктов Microsoft Office (включая версии для Mac OS), а так же во многие другие программные пакеты, такие как AutoCAD, WordPerfect и ESRI ArcGIS. VBA покрывает и расширяет функциональность ранее использовавшихся специализированных макро-языков, таких как WordBasic.
VBA является интерпретируемым языком. Как и следует из его названия, VBA близок к Visual Basic, но может выполняться лишь в рамках приложения, в которое он встроен. Кроме того, он может использоваться для управления одним приложением из другого, с помощью OLE Automation (например, таким образом можно создать документ Word на основе данных из Excel). В будущем Microsoft планирует заменить VBA на Visual Studio Tools for Applications (VSTA) — инструментарий расширения функциональности приложений, основанный на Microsoft.NET.[6]
VBA в настоящее время входит в состав всех приложений Microsoft Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, можно сразу перейти к созданию макросов для других программных продуктов Microsoft (равно, как и приложений других компаний). Более того, можно создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.
Объектные модели
Секрет использования VBA заключается в правильном понимании объектной модели в каждом отдельном приложении. Следует отметить, VBA всего лишь управляет объектами, а у каждого программного продукта (Excel, Word, Access, PowerPoint и т.п.) своя объектная модель. Приложением можно управлять программным образом только с помощью объектов, которые представлены в этом приложении.
|
|
|
Например, в объектной модели Excel представлено несколько мощных объектов анализа данных, например, рабочие листы, диаграммы, сводные таблицы, сценарии, а также многочисленные математические, финансовые, инженерные и общие функции. С помощью VBA можно работать с этими объектами и разрабатывать автоматизированные процедуры.
Основы VBA
Предназначение VBA:
1.Действия в VBA осуществляются в результате выполнения кода VBA.
2. Разработчик создаёт (или записывает) программу VBA, которая сохраняется в модуле VBA
Модуль VBA состоит из процедур.
Процедура, по существу, представляет собой элемент компьютерной программы, выполняющей определенное действие. Ниже приведен пример простой процедуры под названием Test: она вычисляет сумму, а затем отображает результат в окне сообщений:
Sub Test ()
Sum =1+1
MsgBox "Ответ: " & Sum
End Sub
Кроме процедур Sub, в модуле VBA может использоваться второй тип процедур — функции.
Процедура функции возвращает одно значение (или массив). Функция может быть вызвана из другой процедуры VBA или использоваться в формуле рабочего листа. Ниже приведен пример функции с названием AddTwo:
Function AddTwo(arg1, arg2)
AddTwo = arg1 + arg2
End Function
Excel позволяет управлять более, чем ста классами объектов, включая рабочую книгу, рабочий лист, диапазон ячеек рабочего листа, диаграмму и нарисованный прямоугольник. В распоряжении разработчика находятся и другие объекты, с которыми можно работать в VBA.
Классы объектов
Классы объектов в VBA организованы в иерархическую структуру.
Объекты могут быть контейнерами для других объектов. Например, Excel — это объект под названием Application, он содержит другие объекты, например, Workbook (Рабочая книга).
Объект Workbook может состоять из других объектов, например, Worksheet (Рабочий лист) и Chart (Диаграмма).
|
|
|
Объект Worksheet также содержит объекты, например, Range (Диапазон), PivotTable (Сводная таблица) и т.д. Организацию таких объектов называют объектной моделью Excel.
Одинаковые объекты формируют коллекцию.
Например, коллекция Worksheets состоит из всех рабочих листов конкретной рабочей книги, а коллекция CommandBars — из всех объектов CommandBar. Коллекции — это объекты в себе.
При ссылке на объект, вложенный в другой объект, положение в иерархической структуре объектной модели задается с помощью точки-разделителя.
Например, на рабочую книгу с названием Книга1.xls можно сослаться следующим образом:
Application. Workbooks(«Книга1.xls»)
Это ссылка на рабочую книгу Книга1.xls в коллекции Workbooks. Коллекция Workbooks находится в объекте Application. Переходя на следующий уровень, можно сослаться на лист под названием «Лист1» в книге Книга1.xls:
Application. Workbooks («Книга1.хls»).Worksheets («Лист1»)
Сослаться на ячейку, перейдя на один уровень ниже, следующим образом:
Application. Workbooks(«Книга1.xls»).Worksheets(«Лист1»).Range(«А1»)
При опущенной ссылке на объект Excel по умолчанию используются активные объекты. Если книга Лист1 — активная рабочая книга, то предыдущую ссылку можно упростить: Worksheets («Лист1»).Range(«А1»)
Если лист «Лист1» — активный, то ссылку можно упростить еще больше:
Range(«A1»)
Свойства объектов
Свойство можно считать параметром или настройкой объекта. Например, объект диапазона имеет такие свойства, как Value (Значение) и Name (Имя). Объект диаграммы обладает такими свойствами, как HasTitle (Заголовок) и Туре (Тип). Разработчик вправе использовать VBA, чтобы задать свойства объектов и их изменить.
Свойства в программном коде отделяются от названия объекта точкой.
Например, можно сослаться на значение в ячейке А1 листа «Лист1» следующим образом:
Worksheets («Лист1»).Range(«А1»).Value
Пользователь вправе присваивать значения переменным VBA. Переменную можно считать константой, которая используется для хранения конкретного значения.
Чтобы присвоить значение ячейки А1 листа Лист1 переменной с названием Interest, можно использовать следующий оператор VBA:
Interest = Worksheets («Лист1»).Range («А1»).Value
Методы
У объектов есть методы.
Метод - это действие, которое выполняется над объектом. Например, один из методов объекта Range — ClearContents. Этот метод удаляет содержимое диапазона ячеек.
|
|
|
Методы вводятся после названия объекта с методом, в роли разделителя выступает точка.
Например, для удаления содержимого ячейки А1 активного рабочего листа используется следующая команда:
Range(«А1»).ClearContents
Редактор VBE
В Excel 5 и Excel 95 модуль VBA записывался на отдельном листе рабочей книги. Начиная с версии Excel 97t модули VBA уже не представляются в виде листов рабочей книги. Вместо этого для работы и просмотра модулей VBA используется редактор Visual Basic (VBE — Visual Basic Editor).
Модули VBA все еще сохраняются вместе с файлами рабочей книги; просто они не видны до тех пор, пока не запущен редактор VBE.
VBE— это отдельное приложение, запускающееся в Excel. Программа сама выполняет всю операцию по запуску VBE, когда это необходимо, VBE не может запускаться отдельно от Excel; для запуска VBE используется только Excel.
Во время работы в Excel перейти к окну VBE с помощью одного из следующих способов.
1)Нажать <AU+F1
2)Выбрать команду Сервис=>Макрос=>Редактор Visual Basic.
3)Щелкнуть на кнопке Редактор Visual Basic, расположенной на панели инструментов Visual Basic.
Иерархия объектов
Объект Application (то есть Excel) содержит другие объекты. Ниже приведено несколько примеров объектов, которые находятся в объекте Application:
Workbooks (коллекция всех объектов Workbook — рабочих книг);
Windows (коллекция всех объектов window — окон);
Addlns (коллекция всех объектов Addln — надстроек).
Некоторые объекты могут содержать другие объекты. Например, коллекция Workbooks состоит из всех открытых объектов Workbook, а объект Workbook включает другие объекты, некоторые из которых представлены ниже;
Worksheets (коллекция объектов Worksheet — рабочих листов);
Charts (коллекция объектов Chart — диаграмм);
Names (коллекция объектов Name — имен).
Каждый из этих объектов, в свою очередь, может содержать другие объекты. Коллекция Worksheets состоит из всех объектов Worksheet рабочей книги Workbook. Объект Worksheet включает другие объекты, среди которых следующие;
Chartobjects (коллекция объектов Char tobject — элементов диаграмм);
Range — диапазон;
|
|
|
PageSetup — параметры страницы;
PivotTables (коллекция объектов PivotTable — сводных таблиц).
Одной из ключевых концепций в программировании на языке VBA являются коллекции. Коллекция — это группа, объектов одного класса [и сама коллекция тоже является объектом). Как указывалось выше: Workbooks — это коллекция всех открытых в данный момент объектов Workbook. Worksheets — коллекция всех объектов Worksheet, которые содержатся в конкретном объекте Workbook. Разработчик может одновременно управлять целой коллекцией объектов или отдельным объектом этой коллекции. Чтобы сослаться на один объект из коллекции, необходимо ввести название или номер объекта в скобках после названия коллекции:
Worksheets («Лист1»)
Если лист «Лист1» — это первый рабочий лист в коллекции, то можно использовать следующую ссылку:
Worksheets (1)
На второй лист в рабочей книге Workbook можно сослаться соответственно следующим образом:
Worksheets (2)
Кроме того, существует коллекция с названием Sheets, состоящая из всех листов рабочей книги, рабочих листов и листов диаграмм. Если «Лист1» — первый лист в книге, то на него можно сослаться так
Sheets (1)
Свойства объектов
Все объекты обладают свойствами. Например, объект Range обладает свойством с названием Value. Например, можно создать оператор VBA, чтобы отобразить свойство Value или задать свойству Value определенное значение. Ниже приведена процедура» использующая функцию VBA MsgBox для отображения окна, в котором представлено значение ячейки А1 листа Лист! активной рабочей книги.
Sub ShowValue()
HsgSox Worksheets(«Лист1»).Range(«А1»).Value
End Sub
Код предыдущего примера отображает текущее значение свойства Value для конкретной ячейки — А1 рабочего листа Лист1 активной рабочей книги. Однако если в активной книге отсутствует лист с названием Лист1, то макрос выдаст ошибку.
MsgBox — полезная функция, часто используемая для отображения результатов выполнения операторов VBA.
Также разработчик вправе изменять свойство Value. Ниже приведена процедура по изменению значения ячейки А1 путем определения значения свойства Value:
Sub ChangeValueO
Worksheets["Лист1"),Range("Al").Value = 123
End Sub
После выполнения этой процедуры ячейка A1 листа Лист1 получает значение 123. Можно ввести описанные процедуры в модуль и протестировать их.
Многие объекты имеют свойство по умолчанию. Для объекта Range свойством по умолчанию является Values. Следовательно, выражение.Value в приведенном выше коде можно опустить, и ничего не изменится. Однако лучше включать ссылку на свойство, даже если оно используется по умолчанию.
Методы объектов
Кроме свойств, объекты имеют методы. Простой пример - использование метода Clear по отношению к диапазону ячеек. После выполнения этой процедуры ячейки А1:СЗ листа Лист1 станут пустыми, и дополнительное форматирование ячеек будет удалено.
|
|
|
Sub SapRange ()
Worksheets(«Лист1»).Range(«Al:C3»).Clear
End Sub
Если необходимо удалить значения в диапазоне, но оставить форматирование, можно использовать метод ClearContents объекта Range.
Многие методы получают аргументы, определяющие выполняемые над объектом действия более детально. Далее приводится пример, в котором ячейка А1 копируется в ячейку В1 с помощью метода Сору объекта Range.
В данном примере метод Сору получает один аргумент (адрес ячейки, в которую следует скопировать данные):
Sub CopyOnet ()
Worksheets(«Лист1»).Range(«Al»).Copy _
Worksheets(«Лист1»).Range(«В1»)
End sub
Практическая часть
При выполнении данной работы была поставлена цель изучить основы VBA, объектную модель данного языка, основные принципы программирования. Изучить приемы, применяемые при анализе результатов теста, способы определения качества теста то есть провести анализ результатов теста.
Поэтому на данном этапе работы было начато написание кода на языке Visual Basic for Applications с использованием табличного редактора Microsoft Excel.
Написанный код позволяет копировать результаты тестирования из исходного файла, выбираемого пользователем, в текущий файл с последующей обработкой данных.
По результатам сгенерированным текущим кодом преподаватели могут определить, как часто задавался тот или иной вопрос, а также определить частоту правильных ответов на него тестируемыми.
Это пригодится, например, для формирования у преподавателя знания о качестве владения студентами материала и сделать упор на ошибки, допущенные в тесте, в процессе дальнейшего изучения курса.
В приложении 1 содержится код основной программы с комментариями (записаны после знака апострофа «’»).
В приложении 2 содержится код, включающий в себя используемые в программе процедуры с комментариями
Ниже представлена блок – схема программы:
 |
Описание работы программы по анализу результатов теста.
Запуск макроса можно осуществить следующим образом:
1) Сервис=>Макрос=>Макросы
2) Нажав комбинацию клавиш Alt+F8
как показано на рисунке 1.
Затем, в появившемся диалоговом окне «Макрос», выбрать Test и нажать кнопку «Выполнить», как показано на рисунке 2:
После нажатия кнопки «Выполнить» начнётся выполнение кода макроса и пользователь увидит сообщение-приветствие и просьбу выбрать файл для анализа:
Затем появляется пользовательское окно, в котором пользователь должен указать путь к файлу как показано на рис.4:
После того, как пользователь выберет файл начнётся выполнение макроса, при этом файл, выбранный пользователем, откроется. Затем из импортированного файла будут скопированы необходимые данные в текущий файл, название которого «Тест.xls». По завершении копирования будут произведены необходимые вычисления. Затем появится окно, в котором будет показано время выполнения макроса как на рисунке 5:
Затем пользователь может работать с полученными результатами примерный вид которых можно увидеть на рисунке 6:
В дальнейшем планируется осуществить вывод полученных результатов в отдельный файл с последующим сохранением, оформление в виде документа приложения Microsoft Word или составления презентации на основе Microsoft PowerPoint
Заключение
1. Изучена система тестирования АСТ и принципы формирования ею результатов тестирования.
2. Изучены принципы обработки результатов тестирования с использованием понятия теории тестирования.
3. Изучен принцип составления матрицы результатов и её дальнейшей обработки
4. Изучены такие показатели теста как валидность, надёжность, дисперсия, дифференцирующая способность.
5. Написана программа на языке программирования VBA, позволяющая проводить частичный анализ тестовых результатов.
В дальнейшем планируется в данной программе сводить результаты тестирования не только конкретной группы, а нескольких с целью уточнения показателей качества теста. Будет проводиться построение корреляционной таблицы для каждого вопроса в тесте с целью анализа построения теста.
Планируется осуществить сбор результатов, содержащий показатели надёжности тестовых заданий с последующим представлением в виде документа на базе Microsoft Word преподавателю с целью дальнейшей корректировки содержания теста.
Список использованной литературы
1. Аванесов В.С. Форма тестовых заданий. М.: Центр тестирования, 2005
2. Аванесов В.С. Тесты в социологическом исследовании. М.: Наука, 1982
3. Аванесов В.С. Научные проблемы тестового контроля знаний. М. Иссл. Центр проблем качества подготовки специалистов. М.: 1994
4. Яноши Л. Теория и практика обработки результатов измерений. М.: Мир, 1965
5. Пособия «Композиция тестовых заданий». М.: Центр Тестирования. 2002
6. www.wikipedia.org.ru/VBA
7. http://bit.pirit.info/forum/index.php
8. Уокенбах Дж. Профессиональное программирование на VBA в Excel 2002. М.: Диалектика, 2003
9. Берлинер Э.М. Microsoft Office 2003
10. Мюррей К. Microsoft Office 2003. Новые горизонты
11. Кузьмин В. Microsoft Office 2003. Учебный курс
Приложения
Приложение 1: Код основной программы
Sub Test()
Dim FNXLS As String 'Путь к импортированному файлу
Dim FNO As String 'Название импортированного файла
Dim StartTime As Date 'Время начала модуля
Dim EndTime As Date 'Время окончания работы модуля
Dim CountOfSheets As Integer
Dim CountOfUsers As Integer
Dim Fn1 As String
Dim i As Integer 'Переменная - счётчик
Dim j As Integer
Dim n1 As Integer
Dim k As Integer
Fn1 = "Тест.xls"
Dim q1 As Integer
Dim q2 As Integer
Dim q3 As Integer
ОСНОВНАЯ ЧАСТЬ
GreetMe4
FNXLS = FileNameXLS
If FNXLS = " " Then Exit Sub
StartTime = Timer
FNO = FileNameOnly(FNXLS)
MsgBox "Работаем с файлом: " & FNO
Workbooks.Open FNO 'Открытие рабочей книги с названием, соответствующим значению переменной FNO на чтение
CountOfSheets = Sheets.Count 'Определение количества листов в книге
CountOfUsers = Num(FNO)
Workbooks(Fn1).Worksheets(1).Range("A1:IV256").Clear
Workbooks(FNO).Sheets(1).Cells(2, 2).Copy Workbooks(Fn1).Sheets(1).Cells(2, 2)
Workbooks(FNO).Sheets(1).Cells(3, 2).Copy Workbooks(Fn1).Sheets(1).Cells(3, 2)
Workbooks(FNO).Sheets(1).Cells(4, 2).Copy Workbooks(Fn1).Sheets(1).Cells(4, 2)
Workbooks(Fn1).Sheets(1).Cells(6, 1) = "Вопрос" ' Оформление конечного файла
Workbooks(Fn1).Sheets(1).Cells(7, 1) = "Кол-во"
Workbooks(Fn1).Sheets(1).Cells(8, 1) = "Ri"
Workbooks(Fn1).Sheets(1).Cells(9, 1) = "Ri/N"
Workbooks(Fn1).Sheets(1).Cells(11, 1) = "Вопрос"
Workbooks(Fn1).Sheets(1).Cells(12, 1) = "Кол-во"
Workbooks(Fn1).Sheets(1).Cells(13, 1) = "Ri"
Workbooks(Fn1).Sheets(1).Cells(14, 1) = "Ri/N"
Workbooks(Fn1).Sheets(1).Cells(17, 1) = "Вопрос"
Workbooks(Fn1).Sheets(1).Cells(17, 2) = "Кол-во"
Workbooks(Fn1).Sheets(1).Cells(17, 3) = "Ri"
Workbooks(Fn1).Sheets(1).Cells(17, 4) = "Ri/N"
Workbooks(Fn1).Sheets(1).Cells(17, 7) = "Кол-во"
Workbooks(Fn1).Sheets(1).Cells(17, 8) = "Процент"
n1 = 2
n = CountOfUsers ' Количество учащихся в группе
' Копирование данных а текущий файл
k = 1
For i = 1 To CountOfSheets
For j = 1 To 256
If j = 255 Then k = 0
If Workbooks(FNO).Sheets(i).Cells(n + 1 + 6, j + k) > 0 Then Workbooks(FNO).Sheets(i).Cells(n + 1 + 6, j + k).Copy _
Workbooks(Fn1).Sheets(1).Cells(7, n1)
If Workbooks(FNO).Sheets(i).Cells(n + 1 + 6, j + k) > 0 Then Workbooks(FNO).Sheets(i).Cells(6, j + k).Copy _
Workbooks(Fn1).Sheets(1).Cells(6, n1)
If Workbooks(FNO).Sheets(i).Cells(n + 1 + 6, j + k) > 0 Then Workbooks(FNO).Sheets(i).Cells(n + 2 + 6, j + k).Copy _
Workbooks(Fn1).Sheets(1).Cells(8, n1)
If Workbooks(FNO).Sheets(i).Cells(n + 1 + 6, j + k) > 0 Then n1 = n1 + 1
Next j
Next i
n1 = n1 - 1 - 7
For i = n1 + 2 To 256
Workbooks(Fn1).Sheets(1).Cells(6, i).Clear
Next i
For i = 2 To n1 + 1
q1 = Workbooks(Fn1).Sheets(1).Cells(6, i)
q2 = Workbooks(Fn1).Sheets(1).Cells(7, i)
q3 = Workbooks(Fn1).Sheets(1).Cells(8, i)
Workbooks(Fn1).Sheets(1).Cells(11, i) = Workbooks(Fn1).Sheets(1).Cells(6, n1 + 3 - i)
Workbooks(Fn1).Sheets(1).Cells(12, i) = Workbooks(Fn1).Sheets(1).Cells(7, n1 + 3 - i)
Workbooks(Fn1).Sheets(1).Cells(13, i) = Workbooks(Fn1).Sheets(1).Cells(8, n1 + 3 - i)
Workbooks(Fn1).Sheets(1).Cells(11, n1 + 3 - i) = q1
Workbooks(Fn1).Sheets(1).Cells(12, n1 + 3 - i) = q2
Workbooks(Fn1).Sheets(1).Cells(13, n1 + 3 - i) = q3
Next i
For i = 2 To n1 + 1
Workbooks(Fn1).Sheets(1).Cells(9, i) = Workbooks(Fn1).Sheets(1).Cells(8, i) / Workbooks(Fn1).Sheets(1).Cells(7, i)
Workbooks(Fn1).Sheets(1).Cells(14, i) = Workbooks(Fn1).Sheets(1).Cells(13, i) / Workbooks(Fn1).Sheets(1).Cells(12, i)
Next i
For i = 1 To n1
Workbooks(Fn1).Sheets(1).Cells(11, i + 1).Copy Workbooks(Fn1).Sheets(1).Cells(17 + i, 1)
Workbooks(Fn1).Sheets(1).Cells(12, i + 1).Copy Workbooks(Fn1).Sheets(1).Cells(17 + i, 2)
Workbooks(Fn1).Sheets(1).Cells(13, i + 1).Copy Workbooks(Fn1).Sheets(1).Cells(17 + i, 3)
Workbooks(Fn1).Sheets(1).Cells(17 + i, 4) = Workbooks(Fn1).Sheets(1).Cells(17 + i, 3) / Workbooks(Fn1).Sheets(1).Cells(17 + i, 2)
Next i
For i = 0 To 9
Workbooks(Fn1).Sheets(1).Cells(18 + i, 7) = 0
Workbooks(Fn1).Sheets(1).Cells(18 + i, 8) = (i + 1) * 10
Next i
For i = 1 To n1
If Workbooks(Fn1).Sheets(1).Cells(17 + i, 4) = 0 Then Workbooks(Fn1).Sheets(1).Cells(18, 7) = Workbooks(Fn1).Sheets(1).Cells(18, 7) + Workbooks(Fn1).Sheets(1).Cells(17 + i, 2)
Next i
For j = 0 To 9 Step 1
For i = 1 To n1
If Workbooks(Fn1).Sheets(1).Cells(17 + i, 4) > j * 0.1 And Workbooks(Fn1).Sheets(1).Cells(17 + i, 4) <= (j + 1) * 0.1 Then _
Workbooks(Fn1).Sheets(1).Cells(18 + j, 7) = Workbooks(Fn1).Sheets(1).Cells(18 + j, 7) + Workbooks(Fn1).Sheets(1).Cells(17 + i, 2)
Next i
Next j
For i = 2 To n1 + 1
Workbooks(Fn1).Sheets(1).Cells(9, i) = Format(Workbooks(Fn1).Sheets(1).Cells(9, i), "0.00%")
Workbooks(Fn1).Sheets(1).Cells(14, i) = Format(Workbooks(Fn1).Sheets(1).Cells(14, i), "0.00%")
Workbooks(Fn1).Sheets(1).Cells(16 + i, 4) = Format(Workbooks(Fn1).Sheets(1).Cells(16 + i, 4), "0.00%")
Next i
Workbooks(FNO).Close SaveChanges:=False 'Закрытие рабочей книги с названием, соответствующим значению переменной FNO без сохранения изменений
EndTime = Timer
MsgBox "Выполнено за " & Format(EndTime - StartTime, "0.0") & " сек." 'Определение времени выполнения
End Sub
|
|
|
