Файл: Лабораторная работа 3 Работа с диапазонами ячеек (объекты Range, Calls).pdf

Теоретическая работа
Операторы, переменные и типы данных
1.
Понятие оператора
Оператор – наименьшая способная выполняться единица кода VBA.
Оператор
Расшифровка
+,-,*,/
Сложение, вычитание, умножение, деление
^
Возведение в степень
\
Целочисленное деление
Mod
Остаток от деления (деление по модулю)
=
Dim nVar=10 или nVar=10
=,
>,<,<>,<=,>=
Равно, больше, меньше, не равно, меньше или равно, больше или равно
Is
Сравнение объектов. Определяет, ссылаются ли объектные переменные на один и тот же объект.
Like
Сравнивает строковый объект с шаблоном и определяет подходит ли шаблон.
“Таня” Like “Т*”
*любое количество любых символов
# любая цифра
? один любой символ
[f,k,h
] любой один символ из приведенного в квадратных скобках списка
[!f,k,h
] любой символ КРОМЕ приведенного в квадратных скобках списка
AND
Логическое И
OR
Логическое ИЛИ
NOT
+
Конкатенация
&
Конкатенация с автоматическим преобразованием чисел в строки
Порядок применения операторов:
1.
Арифметические
2.
Конкатенации
3.
Операторы сравнения
4.
Логические
Если в выражении несколько операторов одного уровня, то они выполняются по порядку слева направо.
18

2.
Переменные и типы данных
Переменная – это контейнеры для хранения изменяемых данных.
Объявление переменной:
Dim nMyAge as Integer
Тип
Расшифровка
Byte
Целое число от 0 до 255
Integer
Целое число от -32 768 до 32 767
Long
Большое целое число -2 147 483 648 до
2 147 483 647
Single, Double
Значение с плавающей запятой (дробное)
String
Строка длиной до 2 млрд. символов
Date
Дата и время
Boolean
Может принимать значения True, False
Object
Хранит ссылку на любой объект в памяти
Variant
Специальный тип данных, который может хранить любые другие типы данных.
Правила выбора имени переменной:
1.
Должно начинаться с буквы.
2.
Не должно содержать в имени пробелов и символов пунктуации.
3.
Максимальная длина имени – 255 символов.
4.
Не должно совпадать с зарезервированными словами
(подсвеченные синим цветом).
5.
Имя переменной должно начинаться с префикса типа: a. str
(или s) – строка b. с – константа c. b – Boolean d. d – дата e. obj
(или o) – ссылка на объект f. n – числовое значение.
6.
Имена функций, методов и каждое слово в составном имени должно начинаться с большой буквы.
19

Лабораторная работа №3
Работа с диапазонами ячеек (объекты Range, Cells)
1.
Свойство Range
Свойство Range возвращает объект Range, определяемый аргументами. Используется два разных способа записи свойства
Range
Первый способ object.Range(Cell1)
Второй способ object.Range(Celll [,Ce1l2])
object
- ссылка на объект, например, на рабочий лист или на интервал ячеек.
Ссылка необязательна. По умолчанию используется активный лист;
Celll, Cell2
- аргументы для задания интервала ячеек.
Cell1
- указание обязательно при обоих cnocoбаx записи свойства
Range
Первый способ
Аргумент
Celll задает интервал ячеек произвольного размера.
!
Важно
• могут использоваться определенные имена в таблице, или координаты ячеек, столбцов, строк или интервалов.
• координаты задаются в стиле A1.
•
Координаты и имена заключатся о кавычки.
•
При задании интервалов координаты левого верхнего угла и правого нижнего угла интервал разделяются двоеточием.
•
Для задания несмежных интервалов используется запятая.
•
Для задания пересечения интервалов используется пробел.
Таблица 8
Примеры записи оператора Range
Запись
Возвращаемый объект
ActiveSheet.Range("A1:A10") интервал ячеек A1:A10 на активном листе
Range("A:B") столбцы A:D
Range
("Налог") интервал с именем Налог
Range("1:3") строки с первой по третью
Range("A1:C2,B10:D24")
объединение двух несмежных интервалов A1:C2 и E10:D24
Range("A1:C10 B10:D24")
пересечение двух интервалов А l :C10 и
B10:D24, т.е. интервал В 10:C10

свойствами или методами, представляющими объект
Range
– одну ячейку, одну строку или один столбец рабочего листа.
Таблица 9
Примеры записи оператора Range
Запись
Возвращаемый объект
Range("A5","D18") интервал ячеек A5:D8
Range(Columns(1),Columns(5)) столбцы A:E
2.
Свойство Cells
Свойство Cells возвращает единственную ячейку рабочего листа, которая находиться на пересечении строки и столбца, задаваемых целыми числами.
Синтаксис следующий: object.Cells(Номер_строки, Номер_столбца)
, где object
- ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист.
Номер_строки
– индекс строки;
Номер_столбца
– индекс столбца.
!
В свойстве Cells индекс строки является первым аргументом, а индекс столбца –
вторым аргументом, тогда как при задании адреса ячейки в системе A1 сначала
указывается столбец, а затем строка.
!
Понятие «индекс» всегда подразумевает целое число, целочисленную переменную или
выражение, результат вычисления которого есть целое число или может быть
преобразован в целое число.
Таблица 10
Примеры записи оператора Cells
Запись
Возвращаемый объект
ActiveSheet.Cells
Все ячейки активного листа
Cells(7,3)
Ячейка С7
Range("С5:С10").Cells(1,1)
Ячейка C5
Range(Cells(7,3),Cells(10,4))
Интервал ячеек С7:В10
Задание 15
Откройте книгу MS Excel
Учебная\Tests1_4.xlsm.
Выполните все макросы в модуле Листа1, разберитесь в их коде, добавьте комментарии к каждому из них.
Задание 16
Откройте книгу MS Excel
Учебная\Tests.xlsm.
Выполните макрос в модуле
Листа1, разберитесь в коде, добавьте комментарии к каждому из них.

Пригласите преподавателя и продемонстрируйте выполнение заданий.
3.
Свойства объектов Range
Таблица 11
Свойства объекта Range
Запись
Возвращаемый объект
21

Column
Индекс первого столбца
Row
Индекс первой строки
Columns
Возвращает диапазон столбцов
Rows
Возвращает диапазон строк
Address
Возвращает адрес текуoего диапазона
Задание 17
Создать макрос
Сумма который суммирует числа в выделенном диапазоне и помешает результат под этим диапазоном в последний столбец.
Для начала заполним диапазон какими-нибудь числами. Далее запишем макрос с помощью макрорекодера и вызовем в нем функцию суммирования с указанием диапазона суммирования.
В результате макрос будет иметь вид:
Sub Сумма ()
Range("K20").Select
ActiveCell.FormulaR1C 1 = "=SUM(R[- l 4]C[-5]: R[-1]C[-1])"
Range("K21").Select
End Sub
Из текста макроса видно, что преобразовать данный макрос к нужной форме достаточно сложно.
Для создания данного макроса надо использовать функцию
СУММ
(диапазон суммирования). Для определения диапазона суммирования необходимо выделить этот диапазон перед началом записи макроса, а затем в макросе определить адрес этого диапазона, его местоположение и размеры, для определения местоположения ячейки, где будет находится сумма.
Макрос будет иметь вид представленный в модуле книги Сумма.xlsm.
Задание 18
Откройте книгу с макросом
Сумма.xlsm,
разберитесь в тексте макроса, проверьте его работоспособность.
Выполните следующие задания:
1)
Добавьте вывод в предпоследний столбец слова «Итого».
2)
Измените макрос так, чтобы «Итого» и сумма выводилась в последнюю строку в следующий столбец за последним.
3)
Заполните диапазон одинаковыми значениями.
4)
Посчитайте суммы в каждом столбце диапазона.

Пригласите преподавателя и продемонстрируйте выполнение заданий.
4.
Задание для самостоятельного выполнения
Организовать расчет формул в макросе.
22

Лабораторная работа №4
Функции
1.
Понятие функции
При записи строковой константы ее надо заключать в кавычки. В выражениях, наряду с константами могут встречаться и функции.
Фvнкция (fiinclion) — это встроенная формула, выполняющая действия над выражениями и генерирующая значение. Функция всегда возвращает значение, которое VBА подставляет в программу в том месте, где появляется имя функции. Функции VBA делятся на несколько rpyпп в зависимости от типа операции или вычисления, которое они выполняют.
Чтобы использовать функцию, надо просто авссти имя функции в оператор VBA вместе с любыми аргументами, которые требуются для этой функции, в том мecтe в операторе, где необходимо использовать результат функции. Помещение имени функции в оператор VBA для активизации функции называют вызовом функции. При использовании функций в выражениях существуют следующие правила:
• можно использовать результат функции как часть выражения.
• можно присваивать результат функции какой-либо переменной.
• можно использовать результат функции для предоставления значения в список аргументов другой процедуры или функции.
• функции имеют списки аргументов, заключенные в круглые скобки.
В основном функцию можно использовать для предоставления значения в любом месте в любом операторе VBA, где может быть оправданно использование значения константы или переменной. Тип данных значения, возвращаемого функцией, зависит от этой конкретной функции.
Болыиинство функций возвращают значения типа
Variant
, хотя некоторые функции позвращают данные определенных типов, таких как
String
,
Double и
Integer
. V
BA во многих случаях может автоматически преобразовывать результат какой-либо функции в данные типа, совместимого с другими типами значений в выражении, содержащем эту функцию, точно, как
VBA преобразует типы данных в присваиваниях переменных и вы числении выражений.
Встроенные функции VBA делятся на несколько категорий на основе общего назначения функций (математи ческие, преобразования данных, даты и времени, строковые и работы с диском). Далее обсуждаются категории функций и описыпаются их дейстпия. Большинство функций VBA, такие как математичсские функции, являются довольно ясными из их названия и не требуют подробного объяснения. Другие функции, такие как функции преобразования типа данных и обработки строк, описаны более подробно. VВА- функции обработки строк имею важное значение, поэтому рассмотрены способы их использования.
2.
Математические функции
VBA предоставляет стандартный набор математических функций. В
23

таблице 12 приведены мамтематическис функции, имеющиеся в VBA. В этой таблице N означает любое численное выражение; все аргументы функций являются обязательными, если только не указано иначе.
Таблица 12
Функция
(аргумент)
Действие
Abs(N)
Возвращает абсолютное значение аргумента N
Atn(N)
Возвращает арктангенс N (радиан)
Cos(N)
Возвращает косинус N (радиан)
Exp(N)
Возвращает константу е (натуральный логарифм =
2.718282...), возведенную в степень N
Fix(N)
Возвращает целую часть N. Не округляет число, а отбрасывает любую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое большее, чем или равное N
Пример:
Dim
MyNumber
MyNumber = Int(99.8)
'
Возвращает 99
MyNumber = Fix(99.2)
'
Возвращает 99
MyNumber = Int(-99.8)
'
Возвращает -100
MyNumber = Fix(-99.8)
'
Возвращает -99
MyNumber = Int(-99.2)
'
Возвращает -100
MyNumber = Fix(-99.2)
'
Возвращает -99
Int(N)
Возвращает целую часть N. Не округляет число, а отбрасывает любую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое меньшее, чем или равное N
Log(N)
Возвращает натуральный логарифм N
Rnd(N)
Возвращает случайное число: аргумент является необязательным. Используется только после инициализации генератора случайных чисел (оператор Randomize)
Sgn(N)
Возвращает знак числа: -1, если N - отрицательное; 1, если N
- положительное; 0, если N равно 0
Sin(N)
Возвращает синус N (радиан)
Sqr(N)
Возвращает корень квадратный из N. Если N является отрицательным числом - возвращается ошибка времени исполнения
Tan(N)
Возвращает тангенс N (радиан)
Функция
(аргумент)
Действие
Прмеры записи арифметических выражений приведены в таблице 13.
24

Таблица 13
Математическая запись
Запись на языке VBA
2 * A* (-B)
(A * X ^ 2 + B * X + C) / (D - 2.5) ^ (1 / 3)
SIN(X) ^ 2 – COS(X ^ 3)
LOG(X) / LOG(10) * COS(2 * X) / (SQR(B) + C)
Задание 1
Записать на языке программирования VBA:
а) 3sin5x б) 3tg25x в) –cos3x+5.12-ctg2+sin35x-3x г) -4lg39.45-e2x+xtg38z
3.
Функции преобразования данных
VBA представляет несколько функции для преобразования одного типа данных в другй. Надо использованить эти функии для устранения ошибок несовпадения типов и обеспечения явного контроля за типми данных в выражениях.
Например, при получении сообщения об ощибке несовпадения типов в определенном выражении можено преобразовать значения в выражении в типы, совместимые друг с другом, используя функции преобразования. В таблице 14 приведены функции преобразования данных в VBA.
В этой таблице N – любе численное, S – любое строковое, Е – выражение любого типа.
Таблица 14
Функция
(аргумент)
Действие
Asc(S)
Возвращает число кода символа, соответствующее первой букве строки S
Chr(N)
Возвращает строку из одного символа, соответствующего коду символа N, который должен быть числом между 0 и
255 (обратна предыдущей)
Format(E,S)
Возвращает строку, содержащую значение, представленное выражением Е, в формате в соответствии с инструкциями,
25

Функция
(аргумент)
Действие
содержащимся в S
Hex(N)
Возвращает строку, содержащую шестнадцатеричное представление N
Oct(N)
Возвращает строку, содержащую восьмеричное представление N
RGB(N,N,N)
Возвращает целое типа Long, представляющее значение основных цветов изображения. N для каждого аргумента должно быть целым и лежать в пределах от 0 до 255
Str(N)
Возвращает строку, эквивалентную численному выражению N
Val(S)
Возвращает численное значение, соответствующее числу, представленному строкой S, которая должна содержать только цифры и одну десятичную точку, иначе возвращается 0
CBool(N)
Возвращает Boolean-эквивалент численного выражения N
CByte(E)
Возвращает численное значение типа Byte (от 0 до 255). Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CCur(E)
Возвращает численное значение типа Currency. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CDate(E)
Возвращает значение типа Date. E может быть любым допустимым выражением (строкой или числом), представляющим дату в диапазоне 1/1/100 - 12/31/9999
CDbl(E)
Возвращает численное значение типа Double. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CInt(E)
Возвращает численное значение типа Integer. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CLng(E)
Возвращает численное значение типа Long. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CSng(E)
Возвращает численное значение типа Single. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CStr(E)
Возвращает численное значение типа String. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
CVar(E)
Возвращает численное значение типа Variant. Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число
26

Примеры использования:
Пример 1
Dim retval retval=CStr(1234.56789)
' преобразуем Double в String
Пример 2
Dim A, retval
A=1234.5678
' A имеет тип Double retval = CInt(A)
' преобразуем в Integer
Debug.Print retval
' получаем 1235
Кроме этого существуют функции проверки типа, приведенные ниже.
Название функции
Обозначение в VBA
Пример использования
Проверка переменной на число
IsNumeric()
If Not IsNumeric(nn) then ...
Проверка переменной на дату
IsDate()
Fl=IsDate(dd)
4.
Функции преобразования даты и времени
Таблица 15
Функция(аргумент)
Действие
Date
Возвращает системную дату
Time
Возвращает системное время
Now
Возвращает системные дату и время
Year(D)
Возвращает целое, являющееся частью выражения типа
Date и содержащее год. Год возвращается как число между
100 и 9999
Month(D)
Возвращает целое, являющееся частью выражения типа
Date и содержащее месяц. Месяц возвращается как число между 1 и 12
Day(D)
Возвращает целое, являющееся частью выражения типа
Date и содержащее день. День возвращается как число между 1 и 31
Weekday(D)
Возвращает целое, являющееся частью выражения типа
Date и содержащее день недели. День недели возвращается как число между 1 и 7 (1 - воскресенье..)
Hour(D)
Возвращает целое, содержащее часы как часть времени, содержащегося в выражении типа Date. Часы возвращаются как число между 0 и 23. Если аргумент не содержит значения времени, то возвращается 0
Minute(D)
Возвращает целое, содержащее минуты как часть времени, содержащегося в выражении типа Date. Минуты возвращаются как число между 0 и 59. Если аргумент не содержит значения времени, то возвращается 0 27