Практикум по vba для Microsoft Excel Учебное пособие

• Название: Практикум по vba для Microsoft Excel Учебное пособие
• Анкор: Excel
• Дата: 03.10.2022
• Размер: 2.32 Mb.
• Имя файла: Shireva_VBA.pdf
• Тип: Практикум; #710617
• страница: 4 из 10

Mid возвращает все символы в строке
S от позиции N1 до конца строки.
Right(S, N)
Возвращает значение типа String; копирует N символов из S, начиная с правого крайнего символа
S. Например, Right( outright , 5) возвращает строку right .
RTrim(S)
Возвращает копию строки S после удаления символов пробела из правой части строки (конечные символы).
Space(N)
Возвращает строку пробелов длиной N символов.
StrComp(Sl, S2, N)
Сравнивает S1 с S2 и возвращает число, обозначающее результат сравнения: -1, если S1 < S2;
0, если S1 = S2; и 1, если S1 > S2. N является необязательным и указывает, следует ли выполнять сравнение с учетом регистра. Если N опускается, строки сравниваются с использованием текущей установки Option Compare.
StrConv(S, N)
Возвращает строку, преобразованную в новую форму в зависимости от числового кода, заданного аргументом N. VBA предоставляет внутренние константы для использования с функцией StrConv;
наиболее полезными являются: vbProperCase
(преобразует строку так, что каждая буква, начинающая слово, становится заглавной),
vbLowerCase (преобразует строку в буквы нижнего регистра) и vbUpperCase (преобразует строку в

31 буквы верхнего регистра).
String(N, S)
Возвращает строку длиной N символов, состоящую из символа, заданного первым символом в S.
Например, String(5, "x") возвращает строку "ххххх".
Trim(S)
Возвращает копию строки S после удаления начальных и конечных символов пробела из этой строки.
UCase(S)
Возвращает S со всеми символами нижнего регистра, преобразованными в символы верхнего регистра.
Несколько перечисленных в табл. 5 функций преобразования типа данных относятся также к манипулированию строками: Chr, Format, CStr, в частности.
Примеры использования строковых функций:
n_probel=Instr(―Найти пробел‖,‖ ―) perv_simv=Left(―Выделение текста‖,1)
Пользовательские функции
Создание пользовательских функций или, как их иногда еще называют,
UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги
(ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
При написании функций-процедур для использования в качестве UDF в рабочих листах Excel необходимо знать несколько фактов, помимо общих требований для определенных пользователем функций:

Определенные пользователем функции, которые будут использоваться в Excel, не должны иметь имена, похожие на записи ссылок на ячейку
(например, А2; С1В5);

Любые строковые данные, возвращаемые из VBA в Excel, не должны иметь более 255 символов в длину. Если UDF возвращает строку, имеющую больше 255 символов в длину, в ячейку рабочего листа,
Excel укорачивает строку до максимальной длины 255 символов перед вставкой в ячейку;

При написании UDF, возвращающей значение даты для Excel убедитесь, что задаете тип результата функции как Date. Excel

32 применяет формат Date для результата функции в ячейке рабочего листа только, если результат имеет VBA-тип Date.
Структура пользовательской функции
Function имя_функции(арг1 As тип, арг2 As тип …) As тип
Тело функции имя_функции = выражение
End Function
Досрочный выход из функции выполняется с помощью оператора Exit
Function
Порядок создания пользовательской функции
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок
Function вместо Sub и непустой список аргументов (в нашем случае это
Summa). Если тив переменных и функции не задан, то тип интерпретируется как Variant. После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные
пользователем (User Defined):

33
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Обратите внимание, что в диалоговом окне выводится : «Справка недоступна». Для того, чтобы отображать справку для пользовательской функции надо:
1.
В редакторе VBA открыть окно Object Browser View → Object Browser.
2.
В списке выбрать пользовательскую функцию. Эта функция располагается по алфавиту.

34 3.
Щелкнув правой кнопкой по названию функции, выбрать Properties …

35 4.
В диалоговом окне Member Options ввести пояснения в текстовое окно
Description.
ВАЖНО
Чтобы пояснения отображались в окне Мастер функций, надо Перед пользовательской функции указать доступ Public:
При вызове функции в окне будет отображаться:
Рассмотрим примеры пользовательских функций.
Задание 1
Создать новую книгу Функции. Набрать и отладить функции Vozr и
ИНИЦИАЛЫ/
Функция, вычисляющая возраст по дате рождения.

36
Функция получения фамилии и инициалов
Обеспечить вывод справки по данным функциям.
Продемонстрировать работу функций преподавателям.
Задания для самостоятельной работы
Вариант 1
1. Даны длина катета, найти длину гипотенузы
2. Найти количество дней между датами.
3. Написать функцию вставляющую подстрока в строку с заданной позицией.
Вариант 2
1. Дана гипотенуза и катет, найти второй катет
2. Найти количество месяцев между датами.
3. Дана строка, содержащая название книги, а затем фамилия и инициалы автора. Получить фамилию автора.
Вариант 3
1. Дан радиус окружности найти ее площадь
2. Найти количество недель между датами.
3. Дана строка, содержащая фамилию и группу. Получить строку фамилия и курс.
Вариант 4
1. Дан радиус найти длину окружности
2. Найти количество кварталов между датами.
3. Написать функцию, удаляющую из строки заданную подстроку.
Вариант 5
1. Даны стороны прямоугольника найти площадь прямоугольника

37 2. Сколько дней прошло с начала года
3. Дана строка с информацией о фирме содержащей расчетный счет.
Расчетный счет начинается со знака №. Выделить расчетный счет фирмы.
Вариант 6
1. Даны стороны прямоугольника. Найти периметр прямоугольника
2. Сколько дней осталось до конца года
3. Дано вещественное число получить из него цену в виде целая часть руб дробная часть из двух цифр коп.
Вариант 7
1. Даны длины сторон равнобедренной трапеции. Найти высоту трапеции.
2. Определить день недели первого дня заданного года.
3. Дана строка с ценой число руб. число коп. Преобразовать ее в вещественное число.
Вариант 8
1. Даны координаты начала и конца прямой линии.
2. Определить день недели сотого дня заданного года.
3. Дана дата в виде строки в американском формате месяц/день/год.
Переделать ее в русский формат день.месяц.год.
Вариант 9
1. Найти координаты середины линии.
2. Определить день недели 8 марта в заданном году.
3. Из строки Фамилия Имя Отчество дата рождения в виде день.месяц.год получить:
Фамилия Имя Отчество возраст.
Вариант 10
1. Даны стороны равнобедренного треугольника. Найти его высоту.
2. По дню рождения определить 10000 день жизни.
3. Из города улицы дома и квартиры получить адрес в виде: г. Город, ул. Улица, д.дом, кв. квартира
Вариант 11
1. Даны стороны параллелограмма. Найти площадь параллелограмма.
2. Определить день недели для 25-летнего юбилея.
3. Функцию выделяющую из строки подстроку по начальному и конечному символу.
Вариант 12
1. Даны длины сторон равнобедренной трапеции. Найти ее площадь.
2. По введенной дате определить век.
3. Даны отдельно фамилия, имя отчество получить фамилию и инициалы.

38
Контрольные вопросы
1. Где пишется код пользовательской функции.
2. Что такое аргументы функции.
3. Для чего надо указывать Public в заголовке функции.
4. Как задать справку для пользовательской функции.
5. Для чего указывается тип для функции.
6. Чем отличаются стандартные функции от пользовательских функций.

39
Лабораторная работа 3
Ветвления
Как и во всех других языках программирования, в VBA имеются различные управляющие конструкции, позволяющие изменять порядок выполнения программы При отсутствии управляющих конструкций операторы программы выполняются последовательно, начиная с первого и кончая последним Хотя в некоторых самых простых случаях этого и бывает достаточно, обычно все-таки требуется изменять порядок выполнения операторов при выполнении определенных условий, либо пропуская выполнение некоторых операторов, либо, наоборот, многократно повторяя их. Оказывается, для реализации любых алгоритмов достаточно иметь только два вида управляющих конструкций: ветвления и циклы
Управляющие конструкции ветвления позволяют проверить некоторое условие, а затем, в зависимости от результатов этой проверки, выполнить ту или иную группу операторов Для организации ветвлений в VBA используются различные формы оператора ветвления if и оператор выбора
Select Case
Краткая форма оператора ветвления if может иметь как однострочную, так и блочную форму. Простейшая, краткая форма оператора if используется для проверки одного условия, а затем, в зависимости от результата проверки, либо выполнения, либо пропуска одного оператора или блока из нескольких операторов
If <условие> Then
<блокОператоров1>
[Else
<блокОператоров2>]
End If
В качестве условия можно использовать логическое выражение, возвращающее значение True (ИСТИНА) или False (ЛОЖЬ). Если условие истинно, выполняется первый блок операторов, заключенный между ключевыми словами Then и Else, а в противном случае — второй блок, заключенный между ключевыми словами Else и End If.
Условия, которые записываются в условном операторе после слова If, бывают простые и сложные. Простые условия имеют следующую структуру:
Выражение Операция отношения
Выражение
Операции отношения
= равно
<> неравно
>
Больше
<
Меньше
>=
Больше или равно
<=
Меньше или равно

40
Например: a+b>6, Sin(x)<=0, f*h<>k+d
Оператор if может иметь краткую и полную форму. В краткой форме, если условие возвращает значение False, оператор или блок операторов, заключенных между ключевыми словами Then и End if, составляющих тело краткого оператора ветвления, не будет выполняться.
Пример краткой формы оператора if:
If a<0 Then a=-a
End If
Полная форма оператора if используется в тех случаях, когда имеются два различных блока операторов, и по результатам проверки условия нужно выполнить один из них.
Пример полной формы оператора if:
If x=0 Then x=1
Else z=y/x
End If
Для того чтобы текст вашей процедуры был понятным и удобным для восприятия, рекомендуется делать отступы для групп операторов так, как это указано при описании их синтаксиса. В VBA предусмотрено удобное средство изменения отступов — нажатие на клавишу <Таb> увеличивает отступ вправо, нажатие комбинации клавиш + уменьшает этот отступ.
Использование оператора if делает функции, позволяющие избежать некоторых ошибок. Например, использование функции ИНИЦИАЛЫ предполагает, что исходная строка обязательно содержит фамилию, имя и отчество. А если отчество отсутствует, то возникнет ошибка. Поэтому в коде программы надо проверить значение переменной n2_pr – положение второго пробела. Если эта переменная равна нулю, то отчество в строке отсутствует.
Функция будет иметь вид:

41
Задание 1
Набрать и отладить код функции ИНИЦИАЛЫ.
Еще один пример необходимости использования оператора If.
Задание 2
По году определить день недели для 1 января данного года.
Существует стандартная функция для определения номера для недели. Но эта функция дана для американского стандарта, в котором первым днем недели является воскресенье. Для русого стандарта, если день недели не воскресенье, то из него достаточно вычесть единицу. Но тогда воскресенье будет нулевым днем. Здесь требуется использовать оператор If. Функция будет иметь вид:
Иногда приходится делать выбор одного из целой группы альтернативных действий на основе проверки нескольких различных условий. Для этого можно использовать цепочку операторов ветвления
If...Then...ElseIf.
If <условие1> Then
<блокОператоров1>
Elself <условие2> Then
<блокОператоров2>
Elself <условие3> Then
<блокОператоров3>
Elself <условиеN> Then
<блокОператоровN>
Else
<блокОператоров_Еlsе>
End If
Пример использования блочного оператора if:
If n=1 Then текст=”год”
ElseIf n<5 Then текст =”года”
Else текст =”лет”
End If

42
В данном примере для числа n, обозначающем срок обучения от 1 до 6, определяется текст комментария.
Задание 3
Написать функцию определяющую по заданному весу (в килограммах) и росту (в метрах) ИНДЕКС МАССЫ ТЕЛА (Индекс Кетле) по формуле: вест/(рост*рост) и вывести комментарии согласно таблице
ИМТ
Комментарии
<18,5
Излишняя худоба
>=18,5 и <=22,9
Нормальный вес
>=23 и <=27,4
Избыточный вес
>27,5
Ожирение
Данная функция будет иметь вид:
Набрать и отладить данную функцию.
Задания для самостоятельной работы
Вариант 1
1. Написать функцию вставляющую подстроку в строку с заданной позицией. Проверить, не выходит ли заданная позиция за пределы строки.
2. В зависимости от возраста и пола выдать приветствие: Здравствуй девочка, мальчик , девушка, юноша, женщина, мужчина.
Вариант 2
1. Дана гипотенуза и катет, найти второй катет. Проверить, может ли существовать этот треугольник.
2. В зависимости от возраста ребенка выдать сообщение: младенец, ясельник, детсадовец, школьник.
Вариант 3
1. Найти количество недель между датами. Проверить, чтобы первая дата была меньше второй, иначе переставить их местами.
2. В зависимости от введенной суммы и валюты: доллар, евро, фунт, иена, перевести сумму в рубли.

43
Вариант 4
1. Написать функцию, удаляющую из строки заданную подстроку.
Проверить существует в стоке данная подстрока.
2. В зависимости от номера месяца вывести: зима, лето, осень, весна.
Вариант 5
1. Дана строка с информацией о фирме содержащей расчетный счет.
Расчетный счет начинается со знака №. Выделить расчетный счет фирмы. Проверить присутствует ли в адресе расчетный счет.
2. В зависимости от часа вывести время суток: утро, день, вечер, ночь.
Вариант 6
1. Дано вещественное число получить из него цену в виде целая часть руб. дробная часть из двух цифр коп. Проверить положительное ли данное число.
2. В зависимости от возраста ребѐнка вывести лет, года, год.
Вариант 7
1. Дана строка с ценой число руб. число коп. Преобразовать ее в вещественное число. Проверить, есть ли в данной строке руб. или коп.
2. Задумать число от 1 до 10 и запросить ответ. В зависимости от введенного числа вывести больше, меньше или равно.
Вариант 8
1. Дана дата в виде строки в американском формате месяц/день/год.
Переделать ее в русский формат день.месяц.год. Проверить правильно ли заданы месяц и день.
2. Ввести год, месяц, день. В зависимости от даты вывести: прошлое, настоящее будущее.
Вариант 9
1. Из строки Фамилия Имя Отчество дата рождения в виде день.месяц.год получить:
Фамилия Имя Отчество возраст. Проверить правильно ли заданы месяц и день.
2. Ввести рост человека и пол. В зависимости от роста и пола вывести низкий, средний и высокий.
Вариант 10
1. Даны стороны равнобедренного треугольника. Найти его высоту.
Проверить, существует ли такой треугольник.
2. Ввести летнюю температуру. И вывести комментарии: тепло прохладно, холодно и мороз.

44
Вариант 11
1. Функцию выделяющую из строки подстроку по начальному и конечному символу. Проверить, не выходит ли конечный символ за пределы строки.
2. Ввести температуру в помещении. Вывести комментарий: жарко тепло прохладно и холодно.
Вариант 12
1. По введенной дате определить век. Проверить, чтобы год относился или к 20 или к 21 веку.
2. Ввести пол и возраст взрослого человека и вывести работающий человек или пенсионер.
Ветвления. Сложные условия
Сложные условия состоят из нескольких простых условий, соединенных логическими операциями. Существуют следующие логические операции: not
Операция отрицания
And
Логическое «и»
Or
Логическое «или»
Если два условия соединены логическим «и» (and), то условие выполняется если оба условия выполняются одновременно.
Пример: a>6 and a<20 – условие выполняется если a находится в интервале от 6 до 20 (например a = 10).
Если условия соединены логическим «или» (or), то условие выполняется, если выполняется хотя бы одно условие.
Пример: x<0 or b>10 – если х отрицательный, то независимо от того, чему равен b, условие выполняется.
Логические операции, как и арифметические имеют приоритет. Высший приоритет у операции not, следующий приоритет имеет операция and и самый низкий приоритет у операции or.
Использование сложных условий повышает наглядность операции и сокращает количество операторов If.
Задание 4
Создать функцию СЕЗОН, которая по дате выводит название сезона.
Набрать и отладить код функции.

45
Задание 5
Создать функцию A:
Если X или У отрицательные вычислить A по формуле
A=X * Y,
Иначе
1, если X>Y
A= 0, если X=Y
`
- 1, если XПредставим код данной функции:
Если программа вывела результат, это еще не значит, что она работает правильно. Для проверки необходимо протестировать программа, задавая значения X и Y, чтобы они попали на каждую ветку нашей развилки и вычислить значения A для этих Xи Y.
X
Y
A
-2 4
-6 3
4
-1 5
5 0
6 3
1