Учебное пособие по информатике. Часть 3

61

Cells (2;1).Formula = “ = SUM (B1:B5)” – записывает в ячейку А2 формулу суммирования значений в ячейках В1:В5.

Примеры: Для блока ячеек по их имени:

Worksheets (“PRIMER”).Range (“A10;”F12”).Value = 10 – присвоение блоку ячеек постоянного значения 10.

Range (“D15”).Value = “Test” – в D15 записали “Test” в текущий рабочий лист активной рабочей книги.

Range (“Criteria”).ClearContents – очистка содержимого именованного блока ячеек текущего рабочего листа активной рабочей книги.

Пример:

Selection.Offset(2,5).Select – относительно текущей ячейки, например А1, происходит смещение в ячейку F3.

Каждую область формируют с помощью метода Range:

Union(Range(Cells(1;1);Cells(2;2));Range(Cells(5;5);Cells(7;7)).Select –

выделяет область ячеек: А1:В2, E5:G7.

2 СОЗДАНИЕ ФУНКЦИЙ ПОЛЬЗОВАТЕЛЯ

Программы на языке VBA для приложений, функционирующих в среде Excel, создаются 2 способами:

1)в автоматическом режиме как результат построения клавишной макрокоманды;

2)в неавтоматическом режиме путем создания программного кода.

Для создания программного модуля в составе рабочей книги выполняется команда Вид | Макрос | Макрос | Main | Создать или комбинацию клавиш

ALT+F11

При этом создается новый рабочий лист со стандартным именем – Модуль и порядковым номером программного модуля в рабочей книге. Имя модульного листа можно изменить. Затем в новом окне набираем текст функции.

Для вызова встать в ячейку рабочего листа и вызвать:

Вид | Макрос | Имя | Вызвать. Затем ввести ссылки на ячейки с данными.

3 ВСТРОЕННЫЕ ТИПЫ ДАННЫХ

VBA использует 11 встроенных типов данных [6].

1)Boolean использует 2 байта или 16 бит для хранения логических данных – True или False.

2)Integer используют 2 байта памяти для хранения целых чисел, в том числе 1 бит для знака и 15 бит – для числа: от -32768 до +32768.

3)Long используют 4 байта или 32 бита памяти для хранения целого числа двойной точности, также 1 бит для знака и 31 – для числа: от -2147483648

до +2147483648.

4)Single – 4 байта для раздельного хранения мантиссы (цифровая часть числа <= 7 знаков) и порядка (расположения десятичной точки): от

+3.402823Е+38 до 1.401298Е–45 (2 интервала).

62

5) Currency – использует 8 байтов, десятичная точка всегда располагается между 4 и 5 цифрами справа, обеспечивает автоматическое округление результатов вычислений: от -922337203685477.5808 до +922337203685477.5808.

6) Double использует 8 байтов для раздельного хранения мантиссы

(15 знаков) и порядка: от 769313486232Е+308 до 4.94065645841247Е-324.

7)Date используют 8 байтов и предназначен для хранения дат и времени. Даты записываются в виде целых чисел дней, а время как дробная часть дня:

01.01.100; 31.12.9999.

8)String – используют 1 байт для хранения 1 символа плюс 1 байт для отметки конца строки. Символы в кодах ANSI: от 0 до 65535.

9)Object – использует 4 байта памяти для ссылки на любой объект VBAлюбой определенный объект.

10)Array определяет список данных любого типа и любой размерности (любой встроенный тип данных).

11)Variant – используется, когда явно не указан ни один тип.

4 ПЕРЕМЕННЫЕ

Их надо объявлять явно и имя должно состоять из алфавитно-цифровых символов и специальных знаков; начинается с буквы, длина не более 255 символов. Нет различия прописных и строчных букв (в имени не используются пробел и специальные символы типа #,&,%,&,!…)

Можно: тарифная _ставка_1_разряда; ПРИмер; приМЕР.

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

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

Переменные в модуле объявляются с помощью операторов различных типов:

1) локальные переменные:

Dim <var> As <type>, <var> As <type>, <var> As <type>…

2) глобальные переменные:

Public <var> As <type>, <var> As <type>, <var>As <type>… где

<var> – имя переменной; <type> – встроенный тип данных переменной.

5 МАССИВЫ ПЕРЕМЕННЫХ

Массивы переменных имеют произвольную размерность (одномерные, двумерные, …) объявляют так:

1) локальный массив:

Dim <Arr> (размерность) As <type> 2) глобальный массив:

63

Public <Arr> (размерность) As <type> где Arr – имя массива; type – тип элементов; размерность массива цифра, указывающая размер массива.

Примеры.

Одномерный: Dim Ball (5) As Integer – индекс с 0 или Dim Basket (45 To 74) as Single – 30 элементов массива, номера с 45 по 74.

Двумерный: Dim Foot(1,3) as Long – 8 элементов массива или комбинация: Dim Cross(1,4 To 5) As Date 4 элемента массива: 0,4;0,5;1,4;1,5.

Трехмерный: Public Game (2, 2, 3) As Single – 36 элементов.

6 ОБЪЯВЛЕНИЕ КОНСТАНТ

Константы объявляют с помощью оператора: Const <const>=<значение>

Здесь <const> – имя константы; <значение> – значение константы. Они тоже есть локальные и глобальные (Public).

Примеры.

Public Const Date1=#12.12.96# или Const Date2=#12.12.97#

В VBA есть встроенные константы, которые применяют без специального объявления, но они начинаются с символов XL.

7ОПЕРАЦИИ ЯЗЫКА VBA

Вязыке VBA содержатся следующие операции [7].

1.Математические операции: +, -, *, /, \ (целочисленное деление). ОП1 Mod ОП2 – остаток от деления на целое.

ОП1^ОП2 – возведение в степень.

2.Операции отношения: <, >, <=, >=, <> не равно, =

ОП1 Is ОП2 – сравнение двух операторов, содержащих ссылки на объекты (проверяется, является ли значение пустым для объектов с NOOL).

ОП1 Like ОП2 – сравнение двух строковых выражений.

3.Логические операции: and – логическое умножение (получаем 1, если оба операнда равны 1, а иначе всегда 0), or – логическое сложение (получаем 1, если хотя бы один операнд равен 1, а 0, если все операнды равны 0), xor – исключающее или (возвращает 1, если один оператор возвращает 1); Not – логическое отрицание.

4.Другие операции: Строка 1 & Строка 2 – сложение строк (конкатенация).

Операции выполняются в следующей последовательности:

1)вызов функций и скобки;

2)возведение в степень (^);

3)- (минус) – смена знака;

4)умножение (*), деление (/);

5)деление целых чисел нацело (\);

6)mod;

7)сложение (+), вычитание(-);

8)операции сравнения (<,>,<=, >=, <>, =);

9)логическое отрицание (not);

66

If IsMissing(F) Then F=100 „IsMissing проверяет наличие аргумента F, если F не задано, по умолчанию F присваивают 100.

S=L*H : End Sub

SquarPr 12;23;sq „ могут быть через , - зависит от установки.

End Sub

II способ: Sub Proc_A()

Dim Sq as Single: Dim LL as Single; Dim HH as Single LL=12: HH=23: SquarPr LL HH Sq

End Sub

III способ: Sub Proc_A()

Dim Sq as Single: Dim LL as Single; Dim HH as Single LL=12: HH=23: SquarPr L:=LL; H:=HH; S:=Sq

End Sub

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

Static <var> As <type>

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

Для модификации:

Sub SquarPr (ByVal L As Single; ByVal H As Single; S As Single) S=L*H: L=10: H=20: End Sub

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

Пример.

Sub Proc_A()

Dim Sq as Single: Dim LL as Single: Dim HH as Single LL=12: HH=23: SqarPr L:=(LL); H:=(HH); S:=Sq

End Sub

Если хотим передать массив по ссылке (чтобы под него один раз выделялось место, а не в каждой процедуре), то перед именем массива в заголовке процедуры пишем не ByVal, а ByRef. В этом случае изменение значений элементов массива в вызванной процедуре, приведет к изменению их значений и в вызывающей процедуре. Это экономит оперативную память.

Пример 1.

В заданной матрице х(4,4) сложить элементы 1 и 3 строки и записать их на место элементов 1 строки в процедуре.

Sub zam(ByRef x() As Integer, ByVal n As Integer, ByVal m As Integer) Dim i, j As Integer

For j = 0 To m

x(1, j) = x(1, j) + x(3, j)

67

Next j End Sub

Sub commandbutton1_click() Dim i, j, n, m, x(4, 4) As Integer n = 4

m = 4

For i = 0 To n

For j = 0 To m

x(i, j) = Cells(i + 1, j + 1) Next j

Next i

zam x, n, m For j = 0 To m For i = 0 To n

Cells(i + 7, j + 1).Value = x(i, j) Next i

Next j End Sub

Пример 2. Подсчитать в процедуре сумму в каждом столбце.

Sub Sum(ByRef x() As Integer, ByVal n As Integer, ByVal m As Integer) Dim i, j, s(2) As Integer

For j = 0 To m s(j) = 0

For i = 0 To n s(j) = s(j) + x(i, j) Next i

MsgBox "s(j)=" & CStr(s(j)) Next j

End Sub

Sub commandbutton1_click() Dim i, j, n, m, x(4, 2) As Integer n = 4

m = 2

For i = 0 To n

For j = 0 To m

x(i, j) = Cells(i + 1, j + 1) Next j

Next i

Sum x, n, m End Sub

10.4Операторы управления

Коператорам управления относятся 2 типа операторов [6]:

1)перехода и выбора (Goto, If – Then – Else и Select Case);

2)повтора (For – Next, For Each, Do – Loop, While – Wend).

Оператор перехода имеет вид:

Goto метка, где метка – это цифровая комбинация, стоящая ещѐ и перед

оператором, на который нужно перейти. В современных программах его стараются не использовать.

Условный оператор If имеет несколько конструкций. Он может быть в виде простой или блочной структуры [9].

68

1.If условие Then [инструкция] [Else инструкция Else] – простая структура If (в [] заключена необязательная часть оператора).

Примеры:

А) If Сумма > 1000 then Скидка = 0.05 Else Скидка = 0 Б) If Сумма > 1000 then Скидка = 0.05

2.If Условие then [инструкция ] [Else (инструкция Else) End if]

Примеры:

А) If Сумма> 1000 then Скидка = 0.05 Else скидка = 0 End If Б) If сумма >1000 then Скидка = 0.05 End If

3. If условие1 then [блок инструкций 1] ElseIf Условие2 then

[блок инструкций 2] „ – блочная структура If

Else [блок инструкций Else] End If

Блок – это последовательность операторов одной строки, разделенная “двоеточием”.

Пример. Определить, какому интервалу принадлежит число:

X= InputBox (“Введите число”)

If X >0 and X<=1 then MsgBox “Число из интервала [0,1]” ElseIf X >1 And X< =2 then MsgBox “Число из интервала (1,2]”

Else MsgBox “Число либо отрицательное, либо>2” : End If

Разновидность простого оператора имеет вид: If <выражение> Then <оператор>

Если условие истинно в какой-либо конструкции оператора, то выполняется простой или составной оператор, следующий за Then. Рассмотрим примеры [5].

Пример 1:

If AvgPrice>12000 Then DiffPrice = Full(234, 45600) ElseIf AvgPrice>24000 Then DiffPrice = Full(12000, 45000) ElseIf AvgPrice>36000 Then DiffPrice = Full(24000, 50000)

Else DiffPrice = Full(36000, 70000 End If

Пример 2. Вычислить у по заданным на рабочем поле Excel а и х.

Private Sub CommandButtonl_Click()

Dim x As Integer, у As Integer, a As Integer x = Лист1.Range("A2").Value

a = Лист1.Range("B2").Value

If x >= 2 Then у = а*х^2 + Log(x) Else If (x > -2) And (x < 2) Then у = Sin(x) - a * x ^ 3

Else у = Log(Abs(x ^ 3-3*х^2-7*а)) End If

End If Лист1.Range("C2").Value = у End Sub

70

Case 1: MsgBox “Число равно 1”

Case 2, 3: MsgBox “Число равно 2 или 3” Case 4 to 6: MsgBox “Число от 4 до 6” Case Is>=7: MsgBox “Число не менее 7” End Select

Пример 2. Идентификация нажатой клавиши.

Private Sub UserForm_KeyDown (byval keykode As MsForm. _ ReturnInteger, byval Shift As Integer)

Select Case KeyKode

Case vbkey 0: MsBox “Нажали 0”

Case vbkey 1, vbkey 2: MsBox “Нажали 1 или 2”

Case vbkey 3 to vbkey 9, vbkey A to vbkey z: MsBox “Нажали цифру от 3 до 9 или буквенную клавишу”

Case Else: MsBox “Нажата не буква и не цифра”

End Select End Sub

Пример 3. Вычислить у по заданным на рабочем листе а и х.

Private Sub CommandButton1_Click() Dim x, y As Integer

X= Лист1.Range (“B2”). Value A=Лист1.Range (“A2”).Value

Select Case A Case Is >=x Y = 0

Case Is <= -x Y=X-A

Case Else Y=SQR(A^2-X^2) End Select

Лист1.Range (“C2”).Value=Y

End Sub

10.5Операторы повтора

1.Оператор цикла For_Next – это наиболее широко используемый оператор, позволяющий организовать циклический процесс [6].

В этом операторе заранее известно число повторений цикла. Заданы начальное, конечное значения параметра (переменной) цикла и шаг изменения параметра цикла. Он имеет вид:

For <переменная цикла> = <начало> То <конец> [Step<шаг>] <блок операторов 1> „тело цикла

[Exit For] „прекращение цикла по внутреннему условию <блок операторов 2> „– повторяется определѐнное число раз Next <переменная цикла>

Пример 1: Найти сумму элементов массива:

Dim A As Variant

A = Array (1, 4, 12, 23, 34, 3, 24) S = 0

For i = LBound (A) to UBound (A) S = S+A(i)

Next i