Создание макроса путем программирования. Обработка массивов данных в VBA

Цель: Изучить основы создания макросов путем программирования.

8.1 Создание макроса

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

Общий формат процедуры Visual Basic:

Sub имя()

…..

End Sub

8.2 Способы ввода данных

Исходные данные можно вводить одним из следующих способов:

а) присвоением переменной значения;

имя переменной=значение

Например:

a=4.5

b=a

c=2*a+5

б) с помощью окна запроса;

имя переменной=InputBox(запрос,заголовок)

Например:

a = InputBox("Введите а", "Ввод данных")

В результате выполнения оператора InputBox на экране появится окно запроса с полем для ввода требуемого значения (рисунок 8). Введенное значение будет присвоено переменной.

Рисунок 8.1 Окно запроса

в) из ячейки таблицы Excel

имя переменной=Cells(i,j)

Объект Cells представляет собой ячейку таблицы Excel, i-номер строки, j-номер столбца на пересечении которых находится данная ячейка.

a=Cells(4,3)

В результате выполнения данной инструкции переменной a будет присвоено значение из ячейки с адресом C4 активного листа Excel.

8.3 Способы вывода результатов вычислений

а) с помощью окна сообщения

MsgBox выражение,,заголовок

Например:

MsgBox "Результат равен " & d,, "Вывод результатов"

Окно выводимое в результате выполнения данной инструкции приведено на рисунке 8.2.

Рисунок 8.2 Окно сообщения

б) в ячейку таблицы Excel

Cells(i, j) = выражение

Например:

Cells(2, 1) = " Результат равен " & d

Рисунок 8.3 Результат выполнения оператора MsgBox

8.4 Пример создания макроса

Создадим процедуру, позволяющую вычислить сумму вклада по истечении n периодов времени.

Исходными данными являются сумма вклада, количество периодов процентная ставка на 1 период времени.

Пример1:

Sub vv()

v = Val(InputBox("Введите сумму первоначального взноса"))

n = Val(InputBox("Введите количество периодов"))

c = Val(InputBox("Введите процентную ставку на 1 период"))

Sum = v

For i = 1 To n

Sum = Sum + Sum / 100 * c

Next i

MsgBox " Sum=" & Sum

End Sub

Пример2:

Sub vv()

v = Cells(2, 3)

n = Cells(3, 3)

c = Cells(4, 3)

Sum = v

For i = 1 To n

Sum = Sum + Sum / 100 * c

Next i

Cells(6, 1) = "Cумма вклада равна " & Sum

End Sub

На рисунке 8.4 приведен фрагмент таблицы с исходными данными и результатом выполнения программы.

Рисунок 8.4 Исходные данные и результат выполнения макроса

8.5 Массивы данных

Массив – это совокупность переменных, каждая из которых может принимать значения. Массивы различают одномерные и многомерные (в частности двумерные).

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

Перед использованием их нужно заранее объявлять.

Dim имя(число) или Dim имя(нижний индекс to верхний индекс)

Пример: Dim sum (5), b (1 to 5)

Dim x(3, 4), y(1 To 3, 1 To 4)

Объявляются:

- одномерный массив sum из 6-ти элементов sum(0), sum(1),….,sum(5)

- одномерный массив b из 5-ти элементов b(1), b(2),….,b(5)

- двумерный массив x из 20-ти элементов x(0,0), x(0,1),….,x(0,4), x(3,0), x(3,4)

-Двумерный массив y из 12-ти элементов y(1,1), y(1,2),…..,y(1,4),……..,y(3,4)

Ввод элементов одномерного массива.

1) For i=1 to 10

A(i) = InputBox(“Введите элемент массива”)

Next i

Значения элементов массива вводятся с помощью блока запроса.

2) r = 2

For i = 1 to 10

A(i) = Cells(r,i).value

Next i

Элементам массива присваиваются значения из рабочего листа,

расположенные во второй строке с первого по десятый столбец, т.е. в диапазоне A2:J2

A B C D E F G H I J

8.6 Ввод элементов двумерного массива

1) For i = 1 to 5

For j = 1 to 4

X (i, j) = InputBox(“Введите элемент массива”)

Next j

Next i

Элементам массив Х присваиваются значения с помощью блока запроса.

Обращение к элементам будет происходить в следующем порядке:

X(1,1), X(1,2),……..X(1,4) i=1, j=1,…..4

X(2,1), X(2,2),……..X(2,4) i=1, j=1,…..4

…………………………… ……………

X(5,1), X(5,2),……..X(5,4) i=5,j=1,….. 4

2) For i = 1 to 5

For j = 1 to 4

X (i, j) = Cells(i+2,j+1).value

Next j

Next i

Элементам массива Х присваиваются значения, расположенные в рабочем листе Excel, в диапазоне B3:E7

8.7 Вывод элементов одномерного массива

1) For i = 1 to 10

Cells(3, i).value = A(i)

Next i

Значения элементов массива выводятся на рабочий лист в диапазон A3:J3

2) T=””

For i = 1 to 10

T=T & A(i) & “ “

Next i

MsgBox T

Значения элементов массива A будут выводиться в окнах сообщений.

8.8 Вывод элементов двумерного массива

1) For i = 1 to 5

For j = 1 to 4

Cells(i+2,j+1).value = X (i, j)

Next j

Next i

Значения элементов двумерного массива X будут выведены на рабочий лист в диапазоне B3:E7.

2) T=””

For i = 1 to 5

For j = 1 to 4

T=T & X (i, j) & “ “

Next j

Next i

MsgBox T

Значения элементов двумерного массива X будут выведены в окнах сообщений.

8.9 Метод Select

Выбор указанного объекта.

Примеры: Сells(“b2”).Select – выбор ячейки b2

Range(“a2:c4”).Select – выбор диапазона a2:c4

Range(“b3:g7”).Rows(2).Select – выбор 2-й строки диапазона

b3:g7, т.е. b4:g4

8.10 Работа с листами

Примеры:

WorkSheets(“Лист1”).Activate – активизирует Лист1 рабочей книги Excel

WorkSheets(“Лист1”).Name = “Итоги” – переименовывает Лист1 в Итоги

WorkSheets(“Итоги”).Visible = False – скрывает лист Итоги

WorkSheets(“Итоги”).Visible = True – отображает скрытый лист Итоги

Sheets.Add – вставляет новый лист в книгу перед текущим листом

Sheets.Add Before: = WorkSheets(“Отчет”), Count:=3 – вставляет три новых листа перед листом Отчет

Sheets.Add After:= WorkSheets(“Лист1”) – вставляет новый лист после

Лист1

WorkSheets(“Итоги”).Delete – удаляет лист Итоги

A = ActiveSheets.Name – переменной А присваивается имя активного

листа

8.11 Индивидуальные задания

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

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

Задание №2 В Лист1 рабочей книги Excel занести исходные данные задачи,не менее 10 записей.

Создать процедуру в VB выполняющую следующую работу:

Данные с Лист1 считать в двумерный массив.Лист1 переименовать в «Исходные данные» и скрыть.

Обработать данные в массиве соответственно требованиям условия задачи.

Лист2 переименовать в “Результат” и на этом листе выдать результат обработки массива.

Вариант1.

1)Даны координаты (x,y) 10-ти точек. Определить принадлежит ли точка заштрихованной фигуре.Сколько точек не принадлежит фигуре?

2) Ввести следующие данные: Фамилия пациента, диагноз, возраст.

Вычислить средний возраст пациентов и выдать список тех, чей возраст моложе среднего.

Вариант2.

1) Даны координаты (x,y) 10-ти точек. Определить принадлежит ли точка заштрихованной фигуре. Сколько точек принадлежит фигуре?

2) Ввести следующие данные:Фамилия владельца, марка автомобиля, номер, год выпуска. Вычислить количество автомобилей марки “ВАЗ 2105” и выдать список тех автомобилей, год выпуска которых не позднее 1985.

Вариант3.

1)Даны координаты (x,y) 10-ти точек. Определить принадлежит ли точка заштрихованной фигуре. Определить количество точек принадлежа-щих фигуре.

2) Ввести следующие данные: Наименование товара, количество, стоимость.Для каждого вида товара вычислить общую сумму стоимости.

Выдать список добавив столбец вычисленных сумм. Какого вида товара имеется в наименьшем количестве?

Вариант4.

1)Даны координаты (x,y) 10-ти точек. Определить принадлежит ли точка заштрихованной фигуре. Сколько точек удалены от центра координатной плоскости более чем на 2 ед.?

2) Ввести следующие данные: Фамилия сотрудника, оклад, стаж. Вычислить премию по следующей схеме: если стаж <5 лет, то премия составляет 10% от оклада, если от 5 до 10 лет, то 15% от оклада, если более 10 лет то 20% от оклада. Выдать список добавив столбец вычисленной премии. Сколько сотрудников имеют стаж менее 10-ти лет?

Вариант5.