Лекция: Беляева Лилия Ренатовна

Таблица 1. Строковые типы данных

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

Объединение строк (конкатенация): выполняется с помощью оператора & (можно также использовать +). При объединении двух строк вторая помещается в конец первой, например, в результате команды “пример “ & “№1” получим строку “пример №1”, а по команде
“25” & “693” получим строку “25693”..

Кроме указанного оператора, в VBA используется большое количество встроенных строковых функций.

Функция Len( ) используется для определения длины строки. Общий вид команды:

n% = Len(строка)

Например, функция Len(“файл”) возвращает число 4.

Функция Instr([номер позиции], строка, подстрока) – осуществляет поиск подстроки в строке, начиная с символа, указанного[номером позиции]. Если [номером позиции] не указан, то поиск осуществляется с 1-го символа. Например:

В приведенном примере сочетание “форма” встретилось в слове “Информатика”, начиная с позиции номер 3.

Функции UCase( ) и LCase( ) используются, чтобы изменить регистр символов заданной строки на верхний или нижний соответственно. Например:

a$ = “Информатика“: b$ = Ucase(a$): c$ = LCase(a$)

В результате переменная b$ = “ИНФОРМАТИКА”, а
c$ = “информатика“

Функции Left( ), Right( ) и Mid( ) используются для выделения части строки. Общий вид команд Left или Right:

Новая строка = Left | Right (Исходная строка, число символов)

Например, в результате команды Left(“самолет”, 3) получим строку «сам», а по команде Right (“пароход”, 3) получим строку «ход».

Функция Mid( ) является более универсальной и часто заменяет собой Left( ) и Right( ). Общий вид команды:

Новая строка = Mid (Исходная строка, начальная позиция, [число символов])

Например, в результате команды Mid (“информатика”, 3, 5) получим строку «форма». Если необязательный параметр [число символов] опущен, то выделяются символы, начиная с с начальной позиции до конца строки, например, по команде Mid (“подберезовик”, 4) получим строку «березовик». Если начальная позиция указана равной нулю или больше длины строки, то результатом выполнения функции будет пустая строка. Например, если строка программы содержит операторы:

a$ = Mid (“процессор”, 10, 2): b$ = Mid (“процессор”, 0, 3)

то результатом их выполнения будет значение ”” (пусто) для обеих переменных a$ и b$.

Функции LTrim ( ), RTrim ( ) и Trim ( ) отсекают пробелы с начала, конца или с обеих сторон строки соответственно.

В VBA используются также встроенные функции преобразования типов данных.

Функция SPACE (n) – формирует строку из n пробелов.

Функция STRING(n, строка_символов) – формирует строку из n одинаковых символов, равных первому символу в строке_символов:

1.2 Функции преобразования типов данных и прочие полезные функции

Функция Str( ) преобразует число в строку текста. Если число положительное, то первым символом в полученной строке будет пробел. Общий вид команды:

Строка = Str(число)

Например, в результате команды Str(895) получим строку « 895», а по команде Str (‑5678) получим строку «‑5678».

Функция Val( ) преобразует строку, содержащую цифры, в число, последовательно считывая символы строки, пока не встретится нецифровой знак. Общий вид команды:

Number$ = Str (Строка)

Например, в результате команды Val(“45Т89”) получим число 45, а по команде Val (“К593”) получим число 0.

Функция Date возвращает текущую дату компьютера в формате “дд.мм.гггг” значением типа Variant.

Функция Chr( ) преобразует ASCII-код символа в соответствующий символ. Например, в результате команды Chr(72) получим символ H, а по команде Chr (65) получим символ А. Одним из распространенных символов при формировании строк является Chr (13) – переход к началу новой строки:

Функция Asc ( ) выполняет обратную задачу: преобразует символ в соответствующий ему ASCII-код. Например, в результате команды Asc(“0”) получим код 48, а по команде Asc (”a“) получим код 97.

2. Примеры решения типовых задач и задачи для самостоятельного решения

2.1 Примеры решения типовых задач

Пример 1. На листе Excel вызвана определенная пользователем функция text, фактические параметры которой указаны в приведенной ниже палитре функции. Какой результат появится на листе?

Решение.

1. Сопоставляем формальные (a$, b$) и фактические параметры:

a$ = «Kazan Power Engineering University»

b$ = «U»

2. nl равно числу символов в строке a$ ‑ считаем, получается nl = 34.

3. Открываем цикл по i:

Проверяем i = 1 < nl ?, Да! Следовательно, приступаем к выполнению функции Instr: начиная с первой позиции, просматривается строка a$ и в ней ищется позиция, на которой стоит заглавнаяU – это позиция № 25. Значит, k = 25.

4. k не равнонулю, следовательно переходим на оператор с меткой 20, досрочно покидая цикл For … Next.

5. Рассчитываем величину nl – k + 1 = 34 – 25 + 1 = 10.

6. Берем 10 правых символов из строки a$, получаем строку “University”.

Заканчиваем программу.

Ответ: На листе Excel появится слово “University”.

2.2 Задания для самостоятельного чтения программ

1. Какое сообщение появится после выполнения предложенной программы?

2. Какие данные появятся на листе Excel при вызове пользовательской функции test2, фактические параметры которой указаны на палитре функции?

3. На компьютере установлена дата 12 июля 2010 г. Какое сообщение появится после выполнения предложенной программы?

4. Какие данные появятся на листе Excel при вызове пользовательской функции test4 при вводе данных, показанных на палитре функции? Какое сообщение будет выведено оператором Msgbox? Как вы ответите на вопрос программы?

5. Какое сообщение появится после выполнения предложенной программы?

6. Какой результат будет показан в окне сообщения программы? Как вы ответите на вопрос программы?

7. В ячейке А2 листа вызвана функция test7 с указанными фактическими параметрами. Какой результат появится после ввода функции? Как вы ответите на вопрос программы?

8. Какой результат будет показан в окне сообщения программы test8, если в диалоговом окне ввода ввести слово “SALUTE!”?

9. Какой результат будет показан в окне сообщения программы test9, если в диалоговом окне ввода ввести фразу “Когда компьютеры были большими — программы были маленькими.”?

10. Какой результат будет показан в окне сообщения программы test9, если в диалоговом окне ввода ввести фразу “Капля никотина убивает лошадь, а чашка кофе — клавиатуру ”?

2.3 Задания на разработку программ с неявно заданным алгоритмом

1.В заданном тексте удалить часть текста, заключенную в скобки (вместе со скобками).

2.В тексте убрать лишние пробелы между словами, оставив по одному.

3.Напечатать самое длинное слово из текста.

4.Определить, какой процент слов в тексте содержит удвоенную согласную.

5.В данном предложении определите слова, которые начинаются с заданной буквы.

7.Ввести фразу «Thy Kingdom come! Thy Will be done in Earth as it is in Heaven!» Сколько букв «e» в фразе стоит на четных местах?

8.Даны два слова. Поменяйте местами буквы этих слов, занимающие одинаковые позиции.

9.Составьте программу шифрования текстового сообщения Можно использовать такой способ шифрования Шифровальщик задает ключ шифровки — целое число, который определяет величину смещения букв русского алфавита, например ключ = 3, тогда в тексте буква «а» заменяется на «г» и т. д Используются все буквы русского алфавита.

10.Составьте программу дешифрования текстового сообщения, зашифрованного программой из задачи 9.

11.Даны две символьные строки А и В. Определите, в какой из них больше символов и на сколько.

12.Найдите первую и последнюю букву К во введенном тексте и символы между ними замените звездочками.

13.Дана строка символов. Дано слово. Удалить из строки это слово.

14. Составить программу-функцию, которая определяет счастливое или не счастливое число и выводит соответствующее сообщение. Число считается «счастливым», если сумма цифр первой половины числа равна сумме цифр второй половины, при этом количество цифр в числе четное. Число вводится через формальный параметр.

15. Ввести произвольную символьную фразу (через диалоговое окно, или формальный параметр). Вывести номер позиции, в которой первый раз встречается буква «Б», и остаток фразы, которая начинается со следующей после «Б» буквы. Если такой буквы нет, то выдать об этом сообщение.

16. Ввести в программу любое (больше двухзначного) число. Найти среднее арифметическое его цифр.

17. Подсчитать среднее геометрическое цифр числа 243516. Указание: среднее геометрическое равно корню n-й степени из произведения n цифр.

18. Составить программу-функцию, которая удаляет среднюю букву строки с нечетным числом символов.

19. Ввести произвольную символьную фразу. Разделить все слова знаком «*».

20. Разработать программу, которая давала бы в окне сообщения следующую фигуру (знак «*» вытесняется знаком подчеркивания «_»)

II. ОБЩИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ ПО РАБОТЕ С МАССИВАМИ НА VBA

1. Понятие массив и индексированные переменные

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

Примеры данных, которые могут быть описаны массивами.

В математике принято компоненты вектора или элементы определителей или матриц обозначать одной переменной, к которой приписывается справа один или два, или несколько индексов:

Предусматривать свое имя для каждой компоненты неудобно: для проведения однотипных операций, даже над тремя переменными, потребуется несколько раз повторить один и тот же оператор, например, оператор ввода.

Массивами удобно описывать однородную информацию, которая содержит большое количество однотипных данных. Например, каждый студент в группе обладает рядом параметров: ФИО, год рождения, оценки за экзамены и т.д. Каждый параметр будет описан своим именем, но их количество будет равно количеству студентов в группе, а связь с носителем информации будет зафиксирована порядковым номером, или индексом. При программировании на VBA этот индекс записывается после идентификатора в скобках, например: F(1) – фамилия первого студента; a(i) – дата рождения i-го студента; b(1,3) – оценка первого студента по третьему экзамену. Очевидно, что индексы могут быть как числовыми константами, так и числовыми переменными, в том числе числовыми арифметическими выражениями: d(k,l); c(i + 2,j ‑ 3).

Количество индексов у перемененной определяет размерность массива, а общее число элементов в массиве – размер. В Бейсике предусмотрена возможность образования одномерных и двумерных массивов. На VBA возможны многомерные массивы до 32 измерений. Наиболее распространенными в обычной практике являются массивы до трех измерений (Рис.1)

Одномерный массив ‑ ячейки линии

Двумерный массив – ячейки плоскость

Трехмерный массив – ячейки объемной фигуры с тремя измерениями

Рис. 1 Массивы разной размерности

Таким образом, массив представляет собой набор значений, логически связанных друг с другом. Массив позволяет ссылаться на эти связанные значения по одному имени и используя номер, называемый индексом или подиндексом, чтобы отличить элементы друг от друга. Отдельные значения, называются элементами массива. Они представляются непрерывной последовательностью от индекса 0 до наибольшего значения индекса.

2. Создание массивов на VBA

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

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

Объявить (создать) массив в программе на VBA можно несколькими способами.

2.1 Статические массивы

Представьте себе, что вы являетесь старостой студенческой группы и хотели бы хранить фамилии всех 25 студентов этой группы. Для этого можно просто определить и использовать 25 различных переменных типа String. Но все языки программирования высокого уровня предлагают лучшее решение — массивы (arrays). Вместо объявления 25 различных переменных (типа namel, name2, name3, пате4,...) можно просто объявить один массив, содержащий 25 однотипных элементов. Для объявления массива используется оператор Dim с указанием в круглых скобках после имени массива его максимального индекса;

Dim aName (25) As String

В этом случае элементы переменной aName различают не по имени, а по индексу:

aName (4) = «Иванов» ‘ студенту с номером 7 присвоена фамилия Иванов

Область определения.Статические массивы, определенные локально внутри процедуры, становятся недоступны для других процедур, для преодоления этого ограничения статический массив необходимо определять только глобально на уровне модуля:

[Static | Public | Dim]Имя_переменной(Верхняя_граница)

Индексирование с нуля. При использовании массивов не следует забывать, что в VBA индексирование всегда начинается с нуля, т.е. индекс 0 обозначает первый элемент массива, индекс 1 — второй и т.д.

Оператор Option Base ‑ оператор позволяет задать индексацию массива с 1:

Option Base 1

Этот оператор должен находиться в области (General) (Declarations) формы, модуля или класса. Допустимыми значениями для OptionBase являются только 0 и 1.Этот оператор служит для того, чтобы обеспечить совместимость VBA с другими диалектами Basic, индексация в которых начинается с 1.

Для установки других границ массива необходимо использовать следующий синтаксис:

[Static I Public I Dim] Имя_переменнои ([Нижн_пределТо] Верхн_предел)

Указанием верхней и нижней границ можно задать любые диапазоны индекса. Это удобно, если индекс несет также определенную смысловую нагрузку (дата, номер заказа, возраст и т.п.):

Dim aBirthDate (1980 То 2050)

Многомерные массивы. При объявлении многомерного массива верхние границы каждой размерности разделяются запятыми:

Dim aName(10, 25) As String

‘Массив с именем aName может содержать 286 различных значений
(11 × 26 = 286).

Private Sub Comandl_Click ( )

aName (1, 3) = «X»

End Sub

Размерность массива может быть и больше. Например, вы составляете список учащихся пяти различных курсов, на каждом из которых по десять групп, а в каждой группе максимум 25 студентов. В этом случае можно использовать трехмерный массив, где первое измерение относится к курсу, второе — к группе, а третье — к номеру студента:

DimaStudent (1 To 5, 1 To 10, 1 To 25)

аStudent (3, 5,17)= «Иванов»

Студент 3-го курса, 5-й группы с номером 17 имеет фамилию Иванов.

Для отдельных значений размеров могут указываться как диапазоны индексов, так и только верхняя граница:

Dim aArrayCI( 80 То 120, 40 То 45, 256, 1997 То 2050) As Byte

В данном примере создан четырехмерный массив, для которого используются два способа указания диапазонов. Размер полученного массива вычисляется как (120 ‑ 79) ×(45 – 39) × 257 × (2050 – 1996) = 3413988 элемента. Объем памяти, занимаемой этим массивом, при условии, что каждый элемент занимает один байт, будет ~3333 Кбт.

2.2 Динамические массивы

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

Динамический массив создается в два этапа. Сначала массив определяют без указания размера, например:

Dim aArray ( ) As Variant

Затем с помощью оператора ReDim устанавливают фактический размер массива:

Dim aArray ( ) As Variant

Private Sub Commandl_Click()

ReDim aArray (50, 10)

'Код программы

End Sub

Синтаксис оператора ReDim:

ReDim (Preserve] Имя_переменной (Границы) [As Тип_данных]

В отличие от обычного Dim, оператор ReDim используется только в процедурах. При этом тип данных указывать не обязательно, особенно если он уже определен оператором Dim. Вы можете использовать оператор ReDim для изменения числа элементов или размерности массива. Однако вы не можете объявить массив с данными одного типа, а затем использовать ReDim для приведения массива к другому типу, за исключением случая, если массив содержит переменные типа Variant.

DimaArray ( ) As Real

Private Sub Commandl_Click()

ReDim aArray (50)

'Код

ReDim aArray (1 TO 20) ' допустимо

'Код

ReDim aArray (2 TO 5,3) ' допустимо

'Код

ReDim aArray (100) As Real ' допустимо

'Код

ReDim aArray (70) As Integer ' ошибка

End Sub

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

Однако VBA предоставляет возможность изменять размерность массива без потери содержимого. Для этого следует использовать ReDim вместе с ключевым словом Preserve (Предохранить):

DimaArray ( ) As Variant

Private Sub Commandl_Click()

ReDim Preserve aArray (50, 15)

'Код

End Sub

Использование с оператором ReDim зарезервированного слова Preserveпозволяет сохранить содержимое массива при изменении его размера или размерности. Но следует учитывать, что для многомерных массивов можно изменять только последнее измерение:

Dim aArray ( ) As Variant

Private Sub Cornmandl_Click( )

ReDim aArray (10, 10)

ReDim Preserve aArray (10, 15) 'допустимо

ReDim Preserve aArray (15, 15) 'ошибка

End Sub

При изменении размерности можно также изменять верхнюю и нижнюю границы индекса. Но если при этом используется ключевое слово Preserve, то разрешено изменять только верхнюю границу:

Dim aArray ( ) As Variant

Private Sub Commandl_Click( )

ReDim aArray (10 To 20)

ReDim Preserve aArray (10 To 25). 'допустимо

ReDim Preserve aArray (15 To 25) 'ошибка

End Sub

3. Область видимости массивов на VBA

Любая переменная или массив имеют свою область действия. Термин область действия относится к области процедуры или модуля VBA, где данная переменная, процедура или другой идентификатор, являются доступными.

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

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

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

В первой строке, приведенного выше листинга, объявлен символьный массив А ( ) модульного типа. Однако в процедуре example_02 имеется свое объявление массива А ( ) как целочисленного. VBA выбирает наиболее локальный массив А( ), объявленный как массив процедурного уровня.

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

Потенциально использование дубликатных имен несет в себе определенную угрозу путаницы. На уровне модуля и его отдельных процедур противоречий между именами локальных и глобальных переменных не должно быть (ведь модуль пишется одним человеком). Реально это может пригодиться только для устранения возможных конфликтов между идентификаторами переменных, определенных на уровне модуля (Private) и на уровне приложения (Public). Хотя для разрешения подобных конфликтов классические принципы языков программирования требуют описания глобальных переменных не только в модулях, где они резервируются, но и в модулях, где они используются (в качестве входных точек). К сожалению, в VBА этого механизма почему-то нет.

Если вы хотите всегда использовать только явное объявление переменных, то для таких целей VBA предоставляет команду Option Explicit. При использовании Option Explicit VBA требует объявления всех переменных перед их использованием.

В настройках редактора VBA можно указать, чтобы команда Option Explicit автоматически включалась в новый модуль (Рис. 2).

Рис. 2 Указание обязательного описания типа переменных в настройках редактора VBA

4. Функции VBA для работы с массивами

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

Array(список аргументов)позволяет автоматически создать массив нужного размера и типа и сразу загрузить в него переданные значения. Функцияявляется удобным способом определения одномерных массивов, преобразующая список аргументов, разделенных запятыми, в вектор из этих значений, и присваивающая им тип Variant:

При формировании векторов День и N в задаче example_03 с помощью функции Arrayиндексация начинается c нуля. Об этом необходимо помнить!

IsArray(Имя переменной)возвращает True (истина), если переменная содержит массив; в противном случае возвращается False (ложь). Функцию IsArray используют для проверки значений переменных типа вариант, содержащих массивы.

LBound(Имя_массива [, Размер]) и UBound(Имя_массива [, Размер])возвращаютминимальное и максимальное допустимые значения указанной размерности. Размер – целое число (необязательный параметр) определяет измерение массива, для которого надо получить верхний или нижний предел. При отсутствии параметра Размер подразумевается значение 1.

Определяет измерение массива, для которого надо получить верхний или нижний предел. При отсутствии dimension возвращается предел для первого измерения массива.

Оператор Erase СписокМассивов позволяет выполнять очистку для статических массивов и удаление ‑ для динамических.

Когда элементы массива заполнены, данные в массиве остаются до тех пор, пока пользователь не присвоит новые значения элементам массива или пока VBA не освободится от массива. Зачастую бывает, что в дальнейших вычислениях динамический массив ни при каких обстоятельствах использоваться не будет, поэтому нецелесообразно «держать» его в памяти компьютера, т.к. это может сказаться на скорости работы программы. Или же может понадобиться очистить все значения в статическом массиве, устанавливая числовые значения на 0, а строковые ‑ на пустые строки. Это можно осуществить при помощи вложенных циклов, но можно сделать гораздо проще, используя оператор Erase:

Поведение оператора Erase для статических массивов зависит от конкретного типа элементов массива (Табл. 1):

Таблица 1. Значения элементов массива после действия оператораErase

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

5. Передача массивов в процедуры и функции

При программировании в VBA не очень часто приходится сталкиваться с массивами. Вместо них в объектных моделях приложений Office обычно используются коллекции. Коллекции — это специальные объекты, которые предназначены для хранения наборов одинаковых элементов. Например, в Excel — коллекции Workbooks (открытые книги) и Worksheets (листы в книге) и т.п. Коллекции обычно удобнее, чем массивы: они изначально безразмерны и в них предусмотрен стандартный набор свойств и методов (метод Add() для добавления нового элемента, свойство Count для получения информации о количестве элементов, метод Item() для получения ссылки на нужный элемент). Тем не менее, в VBA предусмотрены все возможности, предоставляемые процедурными языками программирования, в том числе и возможности передачи массивов в процедуры и функции через параметры.

Параметры — значения, которые передаются от одной процедуры другой. В принципе, можно обойтись и без параметров, воспользовавшись только переменными и массивами уровня модуля, но при использовании параметров читаемость программы улучшается. Чтобы процедура имела возможность принимать параметры, ее вначале нужно объявить с параметрами.

5.1 Способы передачи параметров для обычных переменных

Для обычных (скалярных) переменных примером такой функции может служить, в частности, простейшая функция сложения двух целых чисел:

Вызов ее может выглядеть так:

MsgBox(nsum(3, 2))

В данном случае мы объявили оба параметра как обязательные, и поэтому попытка вызвать функцию без передачи ей какого-либо параметра (например, так: MsgBox(nsum(3))) приведет к ошибке "Argument not optional" — «Параметр не является необязательным». Чтобы можно было пропускать какие-то параметры, эти параметры можно сделать необязательными. Для этой цели используется ключевое слово Optional:

Function nsum (N1 As Integer, Optional N2 As Integer)

В справке по встроенным функциям VBA необязательные параметры заключаются в квадратные скобки.

Для проверки того, был ли передан необязательный параметр, используется либо функция IsMissing (если для этого параметра был использован тип Variant), либо его значение сравнивается со значениями переменных по умолчанию (ноль для числовых данных, пустая строка для строковых и т.п.)

Вызов функции с передачей параметров может выглядеть так:

nResult = nsum (3, 2)

Однако здесь есть несколько моментов, которые необходимо рассмотреть.

В нашем примере мы передаем параметры по позиции, то есть значение 3 присваивается первому параметру (N), а значение 2 — второму (N2). Однако параметры можно передавать и по имени:

nResult = nsum (N1 := 3, N2 := 2)

Обратите внимание, что, несмотря на то, что здесь выполняется вполне привычная операция — присвоение значений, оператор присвоения используется не совсем обычный — двоеточие со знаком равенства, как, например, в C++. При использовании знака равенства возникнет ошибка.

Конечно, вместо явной передачи значений (как у нас — 3 и 2) можно использовать переменные. Однако что произойдет с переменными после того, как они «побывают» в функции, если функция изменяет их значение? Останется ли это значение за пределами функции прежним или изменится?

Все зависит от того, как именно передаются параметры — по ссылке (по умолчанию, можно также использовать ключевое слово ByRef или по значению — нужно использовать ключевое слово ByVal).

Если параметры передаются по ссылке, то фактически в вызываемую процедуру передается ссылка на эту переменную в оперативной памяти. Если эту переменную в вызываемой процедуре изменить, то значение изменится и в вызывающей функции. Это — принятое в VBA поведение по умолчанию.

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

Продемонстрировать разницу можно на простом примере:

Если поменять строку объявления функции на следующую строку:

Function nsum(byVal nItem1 As Integer, nItem2 As Integer)

то сообщения по этой программе покажут, что значение переменной nP1 не изменится:

5.2 Особенности передачи массивов

Многое из того, что описано для скалярных переменных, справедливо и для массивов. Однако массивы нельзя передавать по значению: попытка включить в программу опцию ByVal обнаруживается на уровне трансляции и выдается сообщение:

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

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

Вот пример простой функции, которая находит сумму элементов целочисленного массива.

Вызов это функции можно осуществить в следующей программе:

Функции LBound и UBound, используемые в программе fSum избавляют от необходимости передавать значения границ массива.

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

Эту процедуру можно вызывать несколько раз для разных массивов в одной программе. Следующий пример демонстрирует такую возможность для просмотра исходного массива СС и преобразованного массива DD. Однако, поскольку в программе example_10 реализуется передача размерностей через переменные, первая строка программы out_matrix должна быть переписана следующим образом:

Sub out_matrix(A( ) As Integer, ByVal l1 As Byte, ByVal u1 As Byte, _

ByVal l2 As Byte, ByVal u2 As Byte, Optional nameA As String)

6. Примеры решения типовых задач и задачи для самостоятельного решения

6.1 Примеры чтения программ обработке массивов

Пример 2. Прочесть программу и вычислить по ней результат. В диалоговые окна вводятся следующие числа: 3, ‑6, 3, ‑2.

Решение.

1. В программе описаны три целочисленных переменных n, i, j, строковая переменная s и вещественная одинарной точности mes. Задан динамический целочисленный массив А.

2. Согласно условию задачи через диалоговое окно вводится значение переменной n = 3. Эта величина указывает реальный размер массива – 4 элемента: A(0), A(1), A(2), A(3).

3. Открываем цикл по i

i = 1. mes =”Ввести координату с номером 1” (поскольку для слияния строки используется знак «&», то автоматически числовое значение переменной i конвертируется в строковое.

В диалоговом окне выдается сообщение mes и вводится A(1) = -6. Поскольку массив описан как целочисленный, происходит автоматическая конвертация текстового значения поля ввода в числовое.

Процедура повторяется для i = 2, i = 3, всякий раз выдавая соответствующее сообщение mes. А(2) =3, А(3) = -2.

4. В следующем цикле реализуется накопление суммы квадратов элементов массива: i = 1, sum = 36; i = 2, sum = 36 + 9 = 45;
i = 3, sum = 45 + 4 = 49.

5. Вычисляем modul = .

6. В диалоговом окне формируется сообщение

«Модуль заданного вектора = 7»

Данная программа предназначена для вычисления модуля n-мерного вектора.

Пример 3. Разработать программу для создания матрицы размером m ´ n.

Решение 1. Рассмотрим сначала способ создания матрицы с помощью генератора случайных чисел. Напомним, что при задании оператора ReDim A(n,m) в ОП создается область нумерованных ячеек, в которых хранятся значения по умолчанию: для числовых переменных – это нули, для символьных – пустой символ “”, для вариантных – значение Empty (Пусто).

Операция задания массива должна быть выполнена обязательно в вызывающей процедуре. В отличие от примера example_10, в данном варианте перенесем из главной программы операторы, ответственные за присвоение значений. Составим программу, которая будет заносить в заданные ячейки целые числа от ‑50 до +50.Размерности массива ограничим числом 10.

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

Для вызова созданной процедуры создаем «главную» процедуру с именем main_primer_02. Тексты обеих программ с соответствующими комментариями представлены ниже.

Еще раз посмотри, как происходит заполнение значениями элементов массива:

i = 1

j = 1 Þ A(1,1) = ‑20; j = 2 Þ A(1,2) = 45; ¼ j = n Þ A(1,n) = ‑10;

j = n+1 Þ Выход из цикла по j

i = 2

j = 1 Þ A(2,1) = ‑22; j = 2 Þ A(2,2) = ‑34; ¼ j = n Þ A(2,n) = 15;

j = n+1 Þ Выход из цикла по j

¼

i = m

j = 1 Þ A(m,1) = ‑42; j = 2 Þ A(m,2) = ‑4; ¼ j = n Þ A(m,n) = ‑24;

j = n+1 Þ Выход из цикла по j

i = m+1 Выход из цикла по i.

Решение 2. Рассмотрим теперь способ чтения матрицы с листа Excel. Такая ситуация встречается достаточно часто, когда предварительно данные заносятся на внешний носитель памяти, в данном случае – лист Excel, либо заранее, либо в результате работы какой-либо программы.

Напомним, что, к сожалению, нельзя создать массив в процедуре, а затем передать его в любую вызывающую программу. Возможно только обратное.

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

Рис. 3 Образец листа данных для ввода в программу

Программа primer_03 реализует присвоение данных из ячеек листа элементам массива. Формальные параметры программы включают адрес ячейки, где находится первый элемент массива – nr (номер строки), nc (номер столбца). В таких задачах всегда важно определить зависимость номера ячейки от индекса элемента массива. Только в случае, когда элементы массива расположены с первой строки и первого столбца, эти параметры совпадают:

Пример 4. Разработать программы для умножения двух сцепленных матриц A(m ´ n) и B(n ´ l)и выводаматрицы любого размера на лист Excel в заданное программистом место.

Решение.

Прежде, чем писать программу primer_04, необходимо понять алгоритм вычисления элементов матрицы-произведения С(m ´ l). Этот алгоритм задается известной в алгебре формулой.

Программа primer_05 построена аналогично primer_03: тот же принцип адресации к ячейкам листа, только в операторе присваивания операнды поменяны местами. Текст главной программы main_primer_04 и новых вызываемых функций представлен ниже.

Главная программа

Программа для произведения матриц

Программа для вывода матриц на лист Excel

Результаты работы программы vain_primer_04

Пример 5. Сформировать ведомость для выдачи стипендии. Исходные данные: фамилии N студентов, отметки за m экзаменов – вводятся оператором на лист Excel (Рис.4). Базовая стипендия 1000 руб. занесена в отдельную ячейку.

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

В программу необходимо ввести операторы для начисления каждому студенту стипендии при отсутствии двоек. При среднем балле менее 3,5 выделяется 50 % от базовой стипендии, не более 4,5 – базовая стипендия и выше 4,5 – на 50 % выше базовой. Найти общую сумму стипендий, выдаваемых группе.

Рис. 4 Образец ведомости для обработки данных на начисление стипендий

Решение.

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

1. Список фамилий – это одномерный символьный динамический массив размером n. Дадим ему имя fio.

2. Матрица n´m для оценок всехn студентов по m предметам – тип байт, так как оценки не превышают числа 5 и являются целыми величинами. Пусть имя массива ball.

3. Массив средних баллов (sr_b) – одномерный, размером n, массив одинарной(Single) точности.

4. Для ввода и вывода массивов и одномерных массивов – векторов – используем процедуры, построенные на аналогичных программах, примеры которых даны в предыдущем примере. Процедуры, названия которых заканчиваются на «_s» – предусмотрены для ввода/вывода символьных данных, на «1» ‑ для вывода данных на лист. Для быстрого просмотра используются процедуры вывода в окно сообщений.

Фрагмента начала главной программы vedomost, реализующий указанные действия, представлен ниже:

5. Количество отличников (n5) – это те студенты, у кого средний балл равен пяти. Значит, для их нахождения необходимо использовать перебор элементов массива sr_b и рассчитывать число элементов, у которыхsr_b = 5.

6. Количество неуспевающих студентов (n2) – это те, у кого среди оценок есть хотя бы одна двойка. Значит необходимо для каждого студента (цикл по i до n) провести проверку его баллов студента (цикл поj до m): как только встречается оценка 2, реализуeм выход из цикла по j, при этом увеличивая число n2 на единицу.

Продолжение программы, реализующий алгоритм пунктов 5 и 6.

7. Для нахождения макс. балла включаем стандартный алгоритм поиска максимума в одномерном массиве sr_b.

8. Для нахождения студентов, имеющих наивысший средний балл, организуем цикл по количеству студентов в массиве средних баллов. Если балл студента равен высшему, то выводим его фамилию из массиваfio в соответствующую строку.

9. Наконец, переходим к вычислению стипендии. Организуем цикл по числу студентов, в теле которого используем оператор Select Case по среднему баллу.

Текст завершающей части программы vedomost:

Результаты работы программы, выведенные в окна сообщения, представлены по ходу текста программ, выведенные на лист Excel – представлены на рис. 5.

Рис. 5 Образец ведомости после работы программы vedomost

6.2. Задания для самостоятельного чтения программ

1. В каком месте приведенного листа Excel и какие результаты появятся после выполнения указанной программы?

2. В каком месте приведенного листа Excel и какие результаты появятся после выполнения указанной программы?

3. Какие результаты и где на листе Excel будут выведены результаты представленной программы?

4. Как будет выглядеть окно сообщений в результате работы представленной программы?

5. На запрос диалогового окна введено число 17.Как будет выглядеть окно сообщений в результате работы представленной программы?

6**. Даны тексты главной программы и вызываемой процедуры. Как будет выглядеть окно сообщений в результате работы главной программы?

7. В каком месте приведенного листа Excel и какие результаты появятся после выполнения указанной программы?

8. В каком месте приведенного листа Excel и какие результаты появятся после выполнения указанной программы?

9. На листе Excel подготовлена таблица (рис. 6). Какую информацию вычисляет программа, представленная в два столбца, какие данные появятся на листе в результате её работы?

Рис. 6

10. Как будет выглядеть окно сообщений в результате работы представленной программы?

6.3. Задания по разработке программ для задач с одномерными массивами

1.Дано целое число N (> 2). Сформировать и вывести целочисленный массив размера N, содержащий N первых элементов последовательности чисел Фибоначчи FK:

F1 = 1, F2 = 1, FK = FK–2 + FK–1, K = 3, 4, … .

2. Даны целые числа N (> 2), A и B. Сформировать и вывести целочисленный массив размера N, первый элемент которого равен A, второй равен B, а каждый последующий элемент равен сумме всех предыдущих.

3. Дан целочисленный массив размера N. Вывести все содержащиеся в данном массиве нечетные числа в порядке возрастания их индексов, а так-же их количество K.

4. Дан массив A размера N и целое число K (1 ≤ K ≤ N). Вывести элементы массива с порядковыми номерами, кратными K: AК, A2K, A3K, …. Условный оператор не использовать.

5. Дан целочисленный массив A размера 10. Вывести порядковый номер последнего из тех его элементов AК, которые удовлетворяют двойному неравенству A1 < AK< A10. Если таких элементов нет, то вывести 0.

6. Дан массив размера N и целые числа K и L (1 ≤ K ≤ L ≤ N). Найти сумму элементов массива с номерами от K до L включительно.

7. Дан массив размера N и целые числа K и L (1 ≤ K ≤ L ≤ N). Найти среднее арифметическое элементов массива с номерами от K до L включительно.

8. Дан целочисленный массив размера N, не содержащий одинаковых чисел. Проверить, образуют ли его элементы арифметическую прогрессию. Если образуют, то вывести разность прогрессии, если нет — вывести 0.

9. Дан массив ненулевых целых чисел размера N. Проверить, образуют ли его элементы геометрическую прогрессию. Если образуют, то вывести знаменатель прогрессии, если нет — вывести 0.

10. Дано число R и массив A размера N. Найти элемент массива, который наиболее близок к числу R (то есть такой элемент AK, для которого величина |AK – R| является минимальной).

11. Даны два массива A и B одинакового размера N. Сформировать новый массив C того же размера, каждый элемент которого равен максимальному из элементов массивов A и B с тем же индексом.

12. Дан массив A размера N. Сформировать новый массив B того же размера по следующему правилу: элемент BK равен сумме элементов массива A с номерами от 1 до K.

13. Дан массив A размера N. Сформировать новый массив B того же размера по следующему правилу: элемент BK равен среднему арифметическому элементов массива A с номерами от K до N.

14. Дан целочисленный массив размера N. Увеличить все нечетные числа, содержащиеся в массиве, на исходное значение последнего нечетного числа. Если нечетные числа в массиве отсутствуют, то оставить массив без изменений.

15. Дан массив размера N (N — четное число). Поменять местами его первый элемент со вторым, третий — с четвертым и т. д.

16. Дан массив размера N. Обнулить элементы массива, расположенные между его минимальным и максимальным элементами (не включая минимальный и максимальный элементы).

17. Дан массив размера N. Осуществить циклический сдвиг элементов массива вправо на одну позицию (при этом A1 перейдет в A2, A3 — в A4, …, AN — в A1).

18. Дан массив размера N и целое число K (1 ≤ K ≤ N). Удалить из массива элемент с порядковым номером K.

19. Дан целочисленный массив размера N. Удалить из массива все нечетные числа и вывести размер полученного массива и его содержимое.

20.Дан массив размера N. После каждого отрицательного элемента массива вставить элемент с нулевым значением.

6.4. Задания по разработке программ для задач с двумерными массивами

21. Даны целые положительные числа M и N. Сформировать целочисленную матрицу размера M . N, у которой все элементы I-й строки имеют значение 10·I (I = 1, …, M).

22. Дана матрица размера M × N и целое число K (1 ≤ K ≤ M). Вывести элементы K-й строки данной матрицы.

23. Создать целочисленную матрицу размера M ´ N, элементы которой могут принимать значения от 0 до 100. Различные столбцы матрицы назовем похожими, если совпадают множества чисел, встречающихся в этих столбцах. Найти количество столбцов, похожих на последний столбец данной матрицы.

24. Дана матрица размера M . N. Вывести ее элементы, расположенные в строках с четными номерами (2, 4, …). Вывод элементов производить по строкам, условный оператор не использовать.

25. Дана матрица размера M . N. Найти минимальный среди максимальных элементов ее столбцов.

26. Дана матрица размера M . N. В каждом ее столбце найти количество элементов, больших среднего арифметического всех элементов этого столбца.

27. Дана матрица размера M × N и целые числа K1 и K2 (1 ≤ K1 < K2 ≤ M). Поменять местами строки матрицы с номерами K1 и K2.

28. Дана матрица размера M . N (M — четное число). Поменять местами верхнюю и нижнюю половины матрицы.

29. Дана матрица размера M × N и целое число K (1 ≤ K ≤ N). Удалить столбец матрицы с номером K.

30. Дана матрица размера M × N и целое число K (1 ≤ K ≤ M). Перед строкой матрицы с номером K вставить строку из нулей.

31. Дана матрица размера M . N. Продублировать строку матрицы, содержащую ее максимальный элемент.

32. Дана матрица размера M . N. Элемент матрицы называется ее локальным минимумом, если он меньше всех окружающих его элементов. Заменить все локальные минимумы данной матрицы на нули. При решении допускается использовать вспомогательную матрицу.

33. Дана матрица размера M . N. Упорядочить ее строки так, чтобы их минимальные элементы образовывали убывающую последовательность.

34. Дана квадратная матрица A порядка M. Найти сумму элементов ее главной диагонали, то есть диагонали, содержащей следующие элементы:

A1,1, A2,2, A3,3, …, AM,M.

35. Дана квадратная матрица A порядка M. Найти среднее арифметическое элементов ее побочной диагонали, то есть диагонали, содержащей следующие элементы:

A1,M, A2,M–1, A3,M–2, …, AM,1.

36. Дана квадратная матрица A порядка M. Найти сумму элементов каждой ее диагонали, параллельной главной (начиная с одноэлементной диагонали A1,M.).

37. Дана квадратная матрица порядка M. Обнулить элементы матрицы, лежащие на главной диагонали и выше нее.

38. Дана квадратная матрица A порядка M. Зеркально отразить ее элементы относительно главной диагонали (при этом элементы главной диагонали останутся на прежнем месте, элемент A1,2 поменяется местами с A2,1, элемент A1,3 — с A3,1 и т. д.). Вспомогательную матрицу не использовать.

39. Дана строка-предложение. Зашифровать ее, поместив вначале все символы, расположенные на четных позициях строки, а затем, в обратном порядке, все символы, расположенные на нечетных позициях (например, строка «Программа» превратится в «ргамамроП»).

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

6.5. Задания по разработке программ с неявно заданным алгоритмом

41. Даны два n-мерных векторов и. Составить программу для нахождения скалярного произведение этих векторов.

42. Даны два n-мерных векторов и. Составить программу для нахождения координат вектора-произведения этих векторов.

43. Дано множество А и множество B. Составить програм