Создание приложений на языке VBA

Модули, процедуры, переменные

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

Процедура — любая совокупность кода VBA, рассматриваемая как единое целое. Процедура состоит из операторов, выполняющих какую-либо задачу или вычисляющих какое-либо значение. Каждая процедура идентифицируется своим уникальным именем. Существует два типа процедур: процедура-подпрограмма и процедура-функция.

Примером подпрограммы является макрос. Макрос может записать только подпрограмму, но не функцию.

Подпрограмма имеет следующее описание:

Sub<имя_процедуры>(параметры

тело процедуры (операторы)

End Sub

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

Функция имеет оформление:

Function<имя_процедуры>(аргументы)As<тип_данных>

тело процедуры(операторы)

End Function

Для создания процедур нужно в Редакторе Visual Basic выполнить команды [Вставка]-[Модуль], [Вставка]-[Процедура]. Откроется диалоговое окно Добавить процедуру, в котором нужно указать тип процедуры, установив переключатель в нужную позицию, написать имя процедуры, нажать кнопку <ОК> и ввести код процедуры.

VBA требует выполнения определенных правил для имен подпрограмм и функций:

* первые три символа имени должны быть буквами;

* имя не должно иметь более 255 символов;

* имя не может содержать пробелы, точки, запятые, восклицательные знаки, символы @,&,$,#.

Для выполнения процедуры можно нажать клавишу <F5> или воспользоваться командой [Выполнить подпрограмму/экранную форму].

Чтобы сохранить новую процедуру, надо сохранить рабочую книгу, в которой находится эта процедура.

Переменной называется поименованная область в памяти компьютера во время выполнения программы.

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

Dim <имя_переменной> As < тип_данных>.

Правило именованиия переменных совпадают с правилами задания имен процедурам.

Некоторые типы переменных:

Денежный Currency

Целое Integer

Универсальный Variant

Длинное целое Long

Объект Object

Одинарной

точности Single

Строковый String

Дата/Время Date/Time

Константа — величина, сохраняющая постоянное значение во время выполнения программы.

Константы применяются для хранения постоянной информации, не изменяющейся в процессе выполнения процедуры. Для объявления констант и их значений используется оператор Const, имеющий синтаксис:

Const <имя_константы> As < тип_ данных>=<значение>.

Задание 1.

Создайте диалоговое окно для ввода числа в активную ячейку (ActiveCell).

Порядок выполнения:

Запустите редактор Visual Basic.

Выберите команду [Вставка]-[Модуль].

Выберите команду [Вставка]-[Процедура].

Введите название процедуры "Ввод".

Введите следующий код процедуры:

Dim iResult As Integer

iResult=InputBox(" Введите число:")

InputBox iResult

ActiveCell.Value=iResult

6. Нажмите клавишу <F5>, чтобы отобразить окно сообщения.

7. Введите число и нажмите клавишу <Enter>.

8. Число появляется в окне сообщения. Подтвердите ввод числа. Закройте окно сообщения. Перейдите в рабочую книгу и убедитесь, что число введено в ячейку.

Задание 2. Cоздайте процедуру, выполняемую при наступлении событий. Например, запишите код процедуры, которая выдаёт на экран окно сообщения, если число, введенное в ячейку, меньше заданного.

Порядок выполнения задания:

Запустите редактор Visual Basic, задав команду [Сервис]-[Макрос] –[Редактор Visual Basic](или нажав комбинацию клавиш <Alt>+<F11>).

Выберите команду [Вставка]-[Модуль].

Выберите команду [Вставка]-[Процедура].

Введите название процедуры "ПроверкаВвода".

Введите следующий код процедуры:

Sub ПроверкаВвода ()

If Range("A1").Value < 5 Then

MsgBox"Значение вводимого числа должно быть больше 5"

Range("A1").Clear

Range("A1").Activate

End If

End Sub

Перейдите в рабочую книгу.

Назначьте созданному макросу комбинацию клавиш для быстрого вызова. Для этого задайте команду [Сервис]-[Макрос]-[Макросы]-[Параметры] и в появившемся диалоговом окне впишите букву d в пустое поле параметра Сочетание клавиш.

В ячейку А1 введите число меньше 5, нажмите комбинацию клавиш <Ctrl>+<d> и проверьте работу созданной функции.

Задание 3.

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

Порядок выполнения задания:

1. Откройте новую рабочую книгу.

2. Создайте таблицу:

3. Выделите диапазон ячеек для построения диаграммы (A1:B6).

4. Выполните команду [Сервис]-[Макрос]-[Начать запись] (или нажмите соответствующие кнопки на панели инструментов Visual Basic). Введите название макроса: КруговаяДиаграмма. В списке Сохранить укажите в Этой книге.

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

6. Отформатируйте заголовок диаграммы. Дважды щелкните на заголовке — откроется диалоговое окно Формат названия диаграммы. Перейдите на вкладку Шрифт, задайте полужирное курсивное начертание шрифта и установите размер шрифта 14 пунктов. Закройте диалоговое окно Формат названия диаграммы.

7. Остановите запись макроса.

8. Проверьте выполнение макроса.

9. Перейдите в окно редактора Visual Basic.

10.Откройте папку Модули,активизируйте процедуру КруговаяДиаграмма. и просмотрите код процедуры.

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

· Удалите имеющуюся диаграмму с рабочего листа.

· Вернитесь к процедуре КруговаяДиаграмма

· Измените код процедуры.

Введите две строки с объявлением и заданием новой переменной:

Dim rCurrentRange As Object

Set rCurrentRange=Selection

Найдите фрагмент кода:

ActiveChart.SetSourceData_

Sourse:=Sheets("Лист1 ").Range("A1:B6"), _

PlotBy:=xlColumns

Замените Sheets("Лист1 ").Range("A1:B6) на переменную rCurrentRange.

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

ActiveChart.SetSourceData_

Sourse:= rCurrentRange, PlotBy:=xlColumns

12.Вернитесь в рабочую книгу.

13.Выделите любой диапазон, например, А1:В3, и выполните макрос КруговаяДиаграмма.

14.Выберите автофигуру на панели Рисование, поместите её в рабочую книгу и назначьте ей макрос создания круговой диаграммы.

15.Сохраните файл под именем Диаграммы.

Задание 4.

Создайте процедуру, которая, в зависимости от числа выделенных столбцов, строила бы или круговую диаграмму или гистограмму. Если выделено 2 столбца — строится круговая диаграмма, если больше 2-х — гистограмма.

Порядок выполнения задания:

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

2.Создайте макрос Гистограмма, строящий гистограмму.

3.Создайте новую процедуру Диаграмма. Введите следующий код в эту процедуру:

Dim rCurrentRange As Object

Set rCurrentRange=Selection

If Selection.Columns.Count=2 Then

4.Скопируйте из процедуры КруговаяДиаграмма и вставьте в новую процедуру после оператора If следующие строки:

Charts.Add

ActiveChart.ChartType = xlPie

ActiveChart.SetSourceData Source:=rCurrentRange, PlotBy:= _

xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

5.Введите код:

ElseIf Selection.Columns.Count>2 Then

6.Cкопируйте из процедуры Гистограмма и вставьте после оператора ElseIf следующие строки:

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=rCurrentRange, PlotBy:= _

xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

7.В операторе ActiveChart.SetSourceData замените

Sheets("Лист1").Range("A1:С6) на rCurrentRange

8.Введите с новой строки:

Else

MsgBox "Диапазон не подходит для построения диаграммы"

Exit Sub

End If

With ActiveChart

.HasTitle=True

.ChartTitle.CharaktersText="Заголовок диаграммы"

.ChartTitle.Select

End With

9.Создайте собственную панель инструментов, задав команду [Вид]-[Панели инструментов]-[Настройка]. Нажмите кнопку Создать и введите имя панели, например, Моя панель.

10.Создайте настраиваемую команду меню Моя диаграмма и и поместите её на созданную Вами панель инструментов. Для этого:

· Выполните команду [Сервис]-[Настройка].

· В диалоговом окне Настройка перейдите на вкладку Команды.

· В списке категорий выберите пункт Макросы.

· В списке Команды выберите команду Настраиваемая команда меню.

· Перетащите мышью команду Настраиваемая команда меню на созданную панель инструментов.

· Щелкните мышью на кнопке[Изменить выделенный объект]. В появившемся меню выбрать команду [Назначить макрос] и в поле Имя ввести текст: Моя диаграмма.

11.Проверьте, как функционирует процедура, используя созданное меню на панели инструментов.

Задание 5.

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

Порядок выполнения работы.

1. Откройте файл Диаграммы.

2. Откройте окно редактора VBA и выберите команды [Вставка]-[Модуль] и [Вставка]-[Процедура]. Введите название процедуры: Гистограмма.

3. Из кода процедуры Моя диаграмма скопируйте операторы, позволяющие строить гистограмму по выделенному диапазону, и вставьте их в код процедуры Гистограмма.

4. Проверьте работу процедуры Гистограмма.

5. Выделите диапазон ячеек Е1:Н9 и залейте их любым цветом.

6. Выведите панель формы, задав команду [Вид]-[Панели инструментов]-[Формы].

7. Разместите в залитом цветом диапазоне два переключателя и назначьте одному из них макрос КруговаяДиаграмма, а другому – Гистограмма.

8. Используя контекстное меню измените текст каждого переключателя соответственно его назначению.

9. Объедините оба переключателя в группу, используя элемент Группа переключателей панели Формы.

10.Используя контекстное меню, измените текст группы переключателей на “Тип диаграммы”.

11.Создайте макрос, закрывающий рабочую книгу Диаграммы и сохраните его в Личной книге макросов.

12.В цветном диапазоне разместите кнопку “Закрыть” и назначьте ей макрос, закрывающий рабочую книгу.

13.Проверьте, как функционируют все элементы управления.

14.Закройте файл Диаграммы с помощью созданной кнопки [Закрыть].

Задание 6.

Создайте в качестве надстройки Excel функцию, вычисляющую подоходный налог.

Порядок выполнения задания:

1. Откройте новую рабочую книгу.

2. Запустите редактор Visual Basic, задав команду [Сервис]-[Макрос]-[Редактор Visual Basic](или нажав комбинацию клавиш [Alt]+[F11]).

3. Выберите команду [Вставка]-[Модуль].

4. Выберите команду [Вставка]-[Процедура].

5. Указать тип процедуры Функция.

6. Введите название процедуры "Налог".

7. Введите следующий код процедуры:

Function Налог(Х As Variant)

Налог=Х*0.14

End Function

8. Сохранить рабочую книгу под именем Функция.

9. Выполните команду [Файл]-[Сохранить как]. В диалоговом окне сохранения документа в поле Тип файла выберите Надстройка Microsoft. Автоматически откроется папка AddIns. Для создания надстройки щелкните по кнопке <Сохранить>.

10.Закройте все открытые рабочие книги.

11.Откройте новую рабочую книгу.

12.Выполните команду [Cервис]-[Надстройки], откроется диалоговое окно Надстройки.

13.Щелкните по кнопке Обзор. В открывшемся диалоговом окне выберите созданный Вами файл.

14.Щелкните по кнопке <ОК> в диалоговом окне Надстройки.

15.Введите в ячейку А1 рабочей книги любое число, в ячейку А2 введите формулу "=Налог(А1) " и убедитесь , что функция выполняется.

Задание 7

С помощью VBA написать программу для решения следующей задачи.

Дана таблица цен на апельсины на 4 рынках.

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

Формула для расчета

Цср=(ОП1+ ОП2+ ОП3+ ОП4)/( (ОП1/Ц1)+ (ОП2/Ц2)+ (ОП3/Ц3)+ (ОП4/Ц4))

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

Вопросы для контроля

1. Какие типы адресации в ЭТ Вы знаете?

2. Какие форматы данных применяются в ЭТ?

3. Как выполнить сортировку данных?

4. Как выполнить фильтрацию данных в ЭТ?

5. Как вывести Пакет анализа в меню электронных таблиц.

6. Перечислите этапы построения простой и множественной регрессии.

7. Дайте интерпретацию показателям, полученным при построении множественной регрессии.

8. Напишите общий вид уравнения регрессии, если число независимых переменных равно 3.

9. Какова последовательность создания макроса?

10. Как можно отредактировать макрос?

11. Как назначить макросу комбинацию клавиш?

12. Что такое личная книга макросов?

13. Как создать командную кнопку?

14. Какова последовательность действий при назначении макроса графическому изображению?

15. Где можно сохранить создаваемый макрос?

16. Как вставить элемент управления на рабочий лист или в форму?

17. Что такое форма?

18. Что такое объект? Какие объекты Вам известны?

19. Что называется модулем?

20. Что такое процедура?

21. В чем отличие переменной от константы?

22. Какие типы диалоговых окон Вам известны?

23. ?

Дата добавления: 2014-11-08; просмотров: 374; Нарушение авторских прав

Мы поможем в написании ваших работ!