Одномерные массивы в MS EXCEL VBA

Лекция №3. ОДНОМЕРНЫЕ МАССИВЫ В MS EXCEL VBA 3.1 Связь программы с ячейками текущего рабочего листа Для доступа к ячейкам текущего рабочего листа можно использовать команду Cells или команду Range. Рассмотрим возможности использования данных команд на примере. Реализуем программы вычисления суммы двух чисел, которые расположены в ячейках листа MS Excel. Внешний вид данной программы представлен на рис. 1. Активация данной программы осуществляется путем нажатия на кнопку с изображением «+». Код данной программы представлен следующим листингом: Private Sub CommandButton1_Click() Range("F2")= Cells(2,2)+Cells(2,4) End Sub Для получения значений суммы чисел использована команда Cells. Для команды Cells нужно указать номер строки и столбца. Результат вычисления суммы помещается в ячейку с помощью команды Range в качестве входного параметра, для которой требуется передать номер ячейки. В примере результат выводится в ячейку F2. Запись длинных операторов В случае длинных строк в программе Visual Basic не разбивает их на части, а перемещает экран таким образом, чтобы вводимый текст был в поле зрения. Для переноса строки следует выполнить специальные действия (правило переноса строк): ▪ Ввести пробел. ▪ Ввести символ подчеркивания «_». ▪ Нажать клавишу Enter для перехода на следующую строку. Рассмотрим оператор следующего вида: MsgBox A1 & " + " & A2 & " = " & A3 Используя правило переноса, запишем этот оператор на пяти строках: MsgBox A1 & " + " & A2 _ & " = " & A3 3.2 одномерные массивы в VBA Массив – это совокупность (набор) данных, обозначаемых одним именем (идентификатором) и хранящихся вместе. Отдельные данные в массиве называются элементами массива. Каждому элементу од­номерного массива соответствует конкретный порядковый номер, ко­торый называется индексом. Количество элементов массива определяет размер массива. Все элементы в массиве должны иметь один тип. Тре­бования к имени массива те же, что и к имени переменной. Массив А, содержащий n элементов, представлен на рис. 1. Массив хранится в последовательно расположенных ячейках памяти. Доступ к каждой отдельной ячейке памяти (элементу массива) осуществляется путем указания индекса. Как и обычные переменные, массивы должны быть объявлены. Для определения массива используется оператор Dim: Dim A (100) As Integer При таком описании массива его элементы индексируются (т. е. нумеруются), начиная с нуля. Чтобы задать определенную размерность массива можно использовать зарезервированное слово To. Элементы массива индексируются с того номера, с которого явно указан интервал изменения нижней и верхней границы номеров элементов массива: Dim A (1 To 100) As Integer Перед обработкой массива его необходимо заполнить (ввести). Массив можно считывать с рабочего листа Excel, в который числа заносятся вручную, или задать при помощи генератора случайных чисел. 1-й с п о с о б. В ячейки электронной таблицы рабочего листа Excel введем числа (рис. 2), откроем окно программного кода и запишем в него программу, которая считывает данный массив чисел. Если в разделе объявлений модуля поместить оператор Option Explicit, то при попытке использования предварительно не объявленной переменной редактор VBA будет сообщать об ошибке. После такого своевременного предупреждения можно добавить необходимое объявление переменной в соответствующем месте программы. Программный код: Option Explicit Sub PR1() Dim A(6) As Integer Dim i As Integer For i = 1 To 6 A(i) = Cells(1, i) ‘массив заполняется Next i ‘числами с рабочего листа Excel End Sub 2-й с п о с о б. Массив можно заполнить с помощью генератора случайных чисел. Если требуется получить последовательность случайных целых чисел с равномерным законом распределения на интервале (a; b), то используется выражение: х = Int((b-a)*Rnd+a). Фрагмент программы: Randomize ‘инициализация генератора случайных чисел For i = 1 To 10 Cells(1,i)=Int(Rnd*10–5) ‘заполнение строки случайными числами от –5 до 5 A(i) = Cells(1, i) ‘ заполнение массива Next i В среде VBA удобно пользоваться различными рабочими листами в одной программе. Для установки нужного рабочего листа используется команда Worksheets(Имя листа).Select, Например: Worksheets("Лист1").Select – переход на рабочий лист с именем "Лист1". Пример 1. Вычислить среднее арифметическое значение и количество четных элементов массива А, состоящего из N целых чисел, а также среднее геометрическое значение и количество нечетных элементов этого же массива. Массив считать с рабочего листа Excel. Вычисление суммы, произведения и количества элементов в одномерных массивах производится в цикле. Программный код: Option Explicit Sub PR1 ( ) Dim i As Integer, S As Integer, K1, K2, N As Integer Dim P As Double, SA As Double, SG As Double Dim A(50) As Integer N = Val(InputBox("Введите N")) S = 0 : P = 1: K1 = 0: K2 = 0 ‘ начальные значения For i = 1 To N A(i) = Cells(1, i) ‘ ввод массива с рабочего листа Excel Next i For i = 1 To N If A(i) mod 2 = 0 Then S = S + A(i) ‘ вычисление суммы и K1 = K1 + 1 ‘ количества четных элементов Else P = P * A(i) ‘ вычисление произведения K2 = K2 +1 ‘ количества нечетных элементов End If Next i MsgBox("S=" & S & " K1=" & K1) MsgBox("PR=" & PR & " K2=" & K2) End Sub Пример 2. Прочитать с рабочего листа Excel одномерный массив Y. Найти минимальный и максимальный элементы этого массива и поменять их местами. Новый массив поместить на тот же рабочий лист ниже первого массива. Минимальный и максимальный элементы также напечатать на этом рабочем листе. За начальное значение максимального и минимального элемента принять первый элемент массива, например: Max = Y(1); Min = Y(1). Алгоритм расчета максимального и минимального элемента массива показан на рис. 3. Программа вычисления минимального и максимального элемента представлена процедурой PR2. Программный код: Option Explicit Sub PR2 () Dim A (10) As Integer Dim I As Integer, R As Integer Dim Min As Integer, Max As Integer Dim imin As Integer, imax As Integer For i = 1 To 10 A(i) = Cells(1, i) ‘ ввод массива Next i Min = A(1): Max = a(1) For i = 1 To 10 If A(i) > Max Then Max = A(i): k = i ‘ вычисление максимума и его номера End If If A(i) < Min Then Min = A(i): n = i ‘ вычисление минимума и его номера End If Next i Cells (2, 1) = "Max=" Cells(2, 2) = Max Cells(2, 4) = "imax" Cells(2, 5) = k Cells(3, 1) = "Min=" Cells(3, 2) = Min Cells(3, 4) = "imin" Cells(3, 5) = n R = A(k) ‘ меняем местами A(k) = A(n): A(n) = R ‘ максимальный и минимальный элементы For i = 1 To 10 Cells(5, i) = A(i) ‘ вывод нового массива Next i End Sub Результаты вычисления минимального и максимального элементов массива представлены на листе Excel (рис. 4).