Е. А. Рябухина, О. А. Гущина, Д. А. Губанов, Д. В. Емельянов
 Скачать 3.08 Mb.
|
|
14. Условный оператор VBA Операторы, выполняющие роль ветвления программы на основании какого-либо условия, называются операторами условного перехода. П  ростейшими операторами условного перехода являются операторы If…Then… (краткая форма) и If...Then...Else… (полная форма). Рис. 8. Блок-схема, соответствующая краткой форме условного оператора Простейший вид условного оператора в краткой форме соответствует конструкции рис. 8. If Условие Then Оператор В этом случае при выполнении указанного Условия выполняется Оператор, после чего происходит переход к следующей строке программы. Если же условие не выполняется, то Оператор игнорируется и сразу же происходит переход к следующей строке. Примеры (см. алгоритмы суммы и количества): If F>0 Then S=S+F If F>0 Then K=K+1  Рис. 9. Блок-схема, соответствующая краткой форме условного оператора с группировкой операторов, выполняемых при истинности условия Для конструкции рис. 9 условный оператор имеет вид: If Условие Then Оператор1:Оператор2:…ОператорN Более удобной для этой конструкции является форма If Условие Then Оператор1 Оператор2 … ОператорN EndIf Пример (см. алгоритм среднего арифметического): If F>0 Then S=S+F:K=K+1 или же If F>0 Then S=S+F K=K+1 EndIf Полная форма условного оператора имеет вид: If Условие Then Оператор1 Else Оператор2 и  соответствует конструкции рис.10.Рис. 10. Блок-схема, соответствующая полной форме условного оператора В этом случае при выполнении Условия выполняется Оператор1, в противном случае Оператор2, после чего происходит переход к следующей строке программы. Пример вычисления модуля числа Х: If X>0 Then MOD_X=X Else MOD_X=-X  Рис. 11. Блок-схема, соответствующая полной форме условного оператора с группировкой операторов, выполняемых при истинности и ложности условия Для конструкции, представленной на рис. 11 условный оператор в полной форме имеет общий вид: If Условие Then Оператор1 Оператор2 … ОператорN Else ОператорN+1 ОператорN+2 … ОператорN+K EndIf Пример: вычисление двух средних арифметических при отсутствии нулевых значений функции If F>0 Then S1=S1+F K1=K1+1 Else S2=S2+F K2=K2+1 EndIf  Рис. 12. Блок-схема, соответствующая полной форме условного оператора, в которой при ложности условия рассматриваются n-1 вложенный условный оператор Для конструкции, представленной на рис. 12, используются два способа: 1. Вложенный условный оператор: If Условие1 Then Оператор1 Else If Условие2 Then Оператор2 Else … EndIf … EndIf 2. If Условие1 THEN Оператор1 If Условие2 THEN Оператор2 … If УсловиеNTHEN ОператорN В последнем случае количество условных операторов в краткой форме соответствует количеству возможных вариантов, а условия должны быть взаимоисключающими. Пример: If X<0 Then Y=-2*X Else If X<2 Then Y=X^2 Else Y=4 EndIf EndIf Более удобной является следующая форма: If X<0 Then Y=-2*X If X>=0 and X<=2 Then Y=X^2 IfX>2 ThenY=4 Для более сложных ветвлений можно использовать оператор Case, имеющий вид: Select Case Выражение- Case 1 Выражение1 (блок_операторов) Case 2 Выражение2 (блок_операторов) … Case Else (блок_операторов) End Select Пример: Select Case X- Case 1 MsgBox(“золотая медаль”) Case 2 MsgBox(“серебряная медаль”) Case 3 MsgBox(“бронзоваямедаль”) Case Else MsgBox(“без медали”) End Select 15. Оператор безусловного перехода Оператор безусловного перехода имеет вид: GoTo M и изменяет порядок выполнения операторов в процедуре или функции, осуществляя переход к строке с меткой M. Оператор безусловного перехода должен быть последним оператором в строке, в противном случае все последующие операторы игнорируются. Метка представляет собой набор буквенно-цифровых символов и отделяется от основной части оператора знаком :.(двоеточие). Традиционно метка имеет вид целого числа. Соответственно оператор, имеющий метку должен быть первым оператором в строке. Следует отметить, что неоправданное применение оператора GoTo может значительно усложнить понимание программы. 16. Организация циклов в VBA Для организации циклов в VBA используются несколько видов операторов цикла.  Рис. 13. Пример блок-схемы цикла с заранее определенным числом повторений Для цикла с заранее определенным числом повторений, соответствующего алгоритмической конструкции на рис. 13. Удобнее всего использовать оператор For-Next, имеющий вид: For V=A To B [Step H] Тело цикла NextV здесь V– имя циклически изменяющейся переменной, A – начальное значение, B – конечное, H – шаг. Знак [ ] означает необязательный параметр, т.е. при отсутствии значения H этот параметр предполагается равным 1. Пример: вывести на печать значения функции  , при x изменяющемся от 2 до 8 с шагом 0,5.K=1 For x=2 To 8 Step 0.5 F=x^2+x+1 CELLS(K,1)=FORMAT(F,”0.00”) K=K+1 Nextx Здесь переменная K означает номер строки в столбце Microsoft Excel и обеспечивает вывод значений функции в виде столбца. Для организации циклов с неопределенным числом повторений, т.е. выход из которых осуществляется по выполнению некоторого условия, а не по достижению параметром цикла конечного значения, удобнее использовать следующие виды операторов цикла: Do While-Loop (вариант Do Loop While); Do Until Loop (вариант Do Loop Until); While-Wend. Оператор цикла DoWhile-Loop имеет следующую конструкцию: DoWhile Условие Тело цикла {ExitDo} Loop Здесь знак { } означает необязательный параметр. При наличии оператора Exit Do происходит досрочный выход из цикла. Оператор действует следующим образом: сначала проверяется указанное условие, и в случае его выполнения выполняется тело цикла. Очевидно, что тело цикла должно включать оператор, задающий изменение параметра цикла. Как только условие перестает выполняться, происходит выход из цикла и переход к оператору, последующему за служебным словом Loop. Таким образом, возможна ситуация, что цикл не будет выполнен ни разу. Пример (см. задачу о концентрации раствора): T=0: M=1 Do While C>=CK C=C0*EXP(-K*T) CELLS(M,1)=C M=M+1 T=T+1 Loop Данный вариант программы является неправильным, потому что переменная С, не определенная перед циклом, по умолчанию примет значение 0. Таким образом, условие сразу же будет нарушено, и цикл не будет выполнен ни разу. Для исправления ситуации присвоим переменной С до оператора цикла произвольное значение, заведомо большее чем СK, которое все равно будет изменено сразу же в цикле, но даст возможность выполнить цикл должным образом. Оператор цикла Do Until Loop имеет следующую конструкцию: DoUntil Условие Тело цикла {ExitDo} Loop Отличие его от предыдущего заключается в том, что цикл выполняется, наоборот, пока указанное условие ложно. Как только условие становится истинным, цикл завершается. Если же использовать варианты операторов цикла DoLoopWhile и Do Loop Until, то цикл будет выполнен по крайней мере один раз, так как сначала выполняется тело цикла, а лишь затем проверяется условие. Таким образом, для нашей задачи более целесообразным будет использование конструкций T=0: M=1 Do C=C0*Exp(-K*T) CELLS(M,1)=C M=M+1 T=T+1 Loop While C>=CK Или Do C=C0*EXP(-K*T) CELLS(M,1)=C M=M+1 T=T+1 LoopUntilC<CK Для работы с массивами можно использовать оператор цикла For Each...Next Цикл For Each…Next не использует счетчик цикла. Цикл For Each…Next выполняется столько раз, сколько имеется элементов в определенной группе, например, в массиве. Оператор имеет вид: For Each Element As Type In Group Тело цикла NextElement Здесь Element – переменная, используемая для итерации по всем элементам в определенной группе, Group – это объект коллекции или массив. Цикл For Each...Next всегда выполняется столько раз, сколько имеется элементов в определенной группе. Пример: Dim numbers() As Integer = {1, 4, 7} Dim letters() As String = {"a", "b", "c"} For Each number As Integer In numbers For Each letter As String In letters MsgBox(STR(number)& letter ) Next Next В результате получим набор текстовых констант “1a”,”1b”,…”7c”. 17. Массивы в VBA Массивом называется набор данных одного типа, имеющий общее для всех его элементов имя. Каждый элемент в массиве имеет уникальный порядковый номер, или индекс. По умолчанию индекс имеет начальное значение 0, однако это значение можно изменить на 1. Чаще всего используются одномерные и двумерные массивы(матрицы), однако. VBA позволяет создавать массивы, имеющие до 60 измерений. Элемент одномерного массива обозначается V(k), двумерного – V(n,m). Здесь k– индекс элемента одномерного массива, n– номер строки, m– номер столбца, на пересечении которых находится элемент матрицы. Внимание! В двумерных массивах всегда первое число обозначает строку, а второе – столбец! Различают два вида массивов: статические и динамические. Разница заключается в том, что для статических массивов размерность определяется при первом же описании массива и в дальнейшем в программе не изменяется. Однако бывают ситуации, когда изначально неизвестно количество элементов в массиве, или же, в процессе работы это количество может изменяться. Такие массивы называются динамическими массивами. Динамический массив может увеличиваться или сжиматься, чтобы вмещать точно необходимое число элементов без напрасного расходования памяти. Описание одномерного статического массива можно выполнить различными способами: 1. Dim V(n) As Type Здесь V – имя массива, Type – тип элементов, n– максимальное значение индекса, причем индексация начинается с 0, таким образом, массив содержит n+1 элемент. 2. Dim V(1 To n) As Type В данном случае индексация массива начинается с 1, т.е. массив содержит nэлементов. 3. С помощью функции Array, определяющей сразу же значения элементов массива. V=Array(v0, v1,…vk) Здесь в скобках перечисляются значения элементов массива V. Следует отметить, что в этом случае индексация массива начинается с нуля, а сам массив имеет тип Variant. Пример: Dim A(7) As Byte, N(4,6) As single В данном примере описан одномерный массив А, размерность которого начинается с нуля, содержащий 8 чисел типа Byte, и вещественная матрица N, состоящая из пяти строк и семи столбцов, индексация элементов которой начинается с нуля. Если же пользователю удобно начинать индексацию с единицы, то описание примет вид: Dim V1(1 to N) As Type1, V2(1 to K, 1 to M) As type2,… Здесь V1 – имя одномерного массива,V2 – имя матрицы, Type1 – тип элементов массива, Type2 – тип элементов матрицы, N – количество элементов в массиве, K – количество строк матрицы, M – количество столбцов матрицы. Пример: Dim X(1 to 50) As Integer, D(1 to 10, 1 to 15) As single В приведенных выше примерах описаны статические массивы. Пример: A=Array(“A”,”B”,”C”) P=A(1) В результате получим P=”B”. В VBA существует полезная функция Array, позволяющая формировать массив с заданными элементами; при этом индексация начинается с нуля. Пример: А=Array(1, 2, 3) В результате получим массив А, состоящий из трех элементов. Следует отметить, что при применении к переменной А в данном примере функцию Vartype, то получим значение 8204, являющееся суммой двух чисел – 8192, означающего тип данных массив, и 12, представляющее собой тип Variant. Для одномерных массивов используются функции Unound(V) и Lbound(V). Первая определяет максимальное значение индекса одномерного массива V, вторая – минимальное значение. Эти функции удобно использовать в подпрограммах, где в качестве параметров используются массивы различной длины. Двумерные статические массивы описываются аналогично первому и второму способам: DimV(3,5) As Integer – описывается матрица, состоящая из 4 строк и 6 столбцов, все элементы которой являются целочисленными, индексация начинается с 0. DimV(1 to 4, 1 to 6) As Integer – описывается матрица той же размерности, но индексация начинается с 1. Элемент одномерного массива указывается в виде V(K), где V – имя массива, K – значение индекса; элемент двумерного – V(K,N), где первый индекс всегда означает номер строки, а второй – номер столбца. Если же в программе необходимо изменить размер массива, то используют динамические массивы. Описание динамического массива выполняется следующим образом: Dim V() As Type Redim V(n) или Redim V(1 to N) Здесь N и n может быть константой или переменной. Если массивы используются в качестве параметров подпрограмм, то они описываются в основной программе как динамические. Если ранее указанная размерность динамического массива изменяется в процессе программы, то все существующие в нем значения обнуляются. Если возникает ситуация, при которой требуется увеличить размерность массива, сохранив его содержимое, то в операторе Redim используется служебное слово Preserve. Dim A() As Single – объявляет динамический массив. ReDimA(1 To 3, 1 To 5) ReDim Preserve A(1 To 3, 1 To 6) – изменяет последний размер массива, сохраняя содержимое. Внимание! При использовании ключевого слова Preserve можно изменять только последнее измерение многомерного массива! При этом изменяется только верхняя граница последней размерности массива, то есть можно добавить в одномерный массив новый элемент или новый столбец в матрицу. Если данные, хранящиеся в массиве, становятся ненужными для дальнейшей работы, то можно удалить массив с помощью оператора Erase Имя_массива с целью экономии оперативной памяти. Для двумерного массива также допустимо использование функций Ubound и Lbound, но они имеют вид Ubound(V,2) и Lbound(V,2). Здесь V – имя массива, 2 – номер измерения. Пример: даны матрицы A(3,3) и B(3,3). Получить матрицу С=А+В. Dim A(1 to 3,1 to 3) As Integer, B(1 to 3,1 to 3) As Integer, C(1 to 3, 1 to 3) As Integer Dim I As Byte, J As Byte For I=1 to 3 For J=1 to 3 A(I,J)=CELLS(I,J) B(I,J)=CELLS(I, J+2) Next J Next I For I=1 to 3 For J=1 to 3 C (I,J)=A(I,J)+B(I,J) CELLS(I+4,J)=C(I,J) Next J Next I Range (“A5:C7”).Select With Selection .FONT.NAME=”Times New Roman” .BORDERS.COLOR=RGB(200,0,0) End With Внимание! Предполагается, что при суммировании целочисленных матриц А и В результат не выходит за пределы типа Integer. 18. Строковые переменные и их обработка в VBA Строковой (символьной текстовой) константой называется последовательность символов, количество которых не превышает 256, при этом последовательность заключается в кавычки(“ ”). Пример: “ABC”, “$CV$EDRE” и т.д. Строковой переменной называется переменная, значением которой является строковая константа. Строковые переменные описываются типом String или суффиксом $. Пример: A$=”ABC” Если реальная длина строковой константы меньше 256, то память под нее все равно выделяется в размере как для 256-символьной, поэтому, если реальная длина текстовой константы значительно меньше 256, целесообразно использовать строки фиксированной длины. Объявление строковой переменной фиксированной длины имеет вид: Dim V As String * n где n – длина переменной Пример: DimA As String*5 Объявленная таким образом переменная V всегда будет содержать ровно 5 символов. Если ей присвоить более короткую строку, VBA добавит после символов этой строки нужное число пробелов. Если же переменной присвоить более длинную строку, VBA сохранит в переменной только 5 первых символов, отбросив остальные: Строковые константы и переменные можно сравнивать между собой, при этом сравнение происходит посимвольно слева направо. В VBA одна строка равна другой только, когда обе строки содержат точно такие же символы в точно таком же порядке и обе строки имеют одну и ту же длину. Следует быть внимательным при сравнении строк переменной длины. В VBA существуют два способа сравнения строк – двоичное и текстовое сравнение. Разница заключается в том, что при текстовом сравнении по существу сравниваются уникальные коды символов – коды ASCII. Так, цифры 0-9 имеют коды 48-57, латинские буквы A-Z – коды 65-90, буквы a-z – коды 97-122. Таким образом, если символ С является цифрой, то для него выполняется условие “0”<=C<=”9” а для латинских прописных букв – “A”<=C<=”Z” При использовании данного способа сравнения, из двух констант одинаковой длины меньшей считается та, в которой первый из несовпадающих символов имеет меньший код ASCII. Таким образом, “Abc”<”abc”. При текстовом сравнении строк VBA не использует коды символов, и не "различает" верхнего и нижнего регистра. В текстовом сравнении строка "абв" равна строке "АБВ". Для выбора метода сравнения строк используется директива Option Compare: Option Compare [Text | Binary] Данная директива должна находиться в области объявления модуля. К символьным константам применяется операция, называемая конкатенацией, или сложением строк. При этом происходит склеивание строк в порядке их следования. Для конкатенации используется символ & (амперсанд), хотя допускается использование обычного знака +. Символ (&) операции конкатенации обязательно необходимо отделять пробелом от имени переменной, т.к. в противном случае VBA может интерпретировать этот символ как символ определения типа Long. К строковым данным применяются следующие встроенные функции: Len(S) – вычисляет длину символьной константы, т.е. количество символов в ней, кавычки при этом не учитываются; результат относится к числовому типу Пример: Len(“ABC”)=3 Left(S,N) – копирует N символов из S, начиная с левого крайнего, результат относится к строковому типу. Пример: Left(“ABCD”,2)=”AB” Right(S,N) – копирует N символов из S, начиная с правого крайнего, результат относится к строковому типу. Пример: Right(“ABCD”,2)=”CD” Mid(S,N1,N2) – копирует N2 символов из S, начиная с позиции символа в S, заданной аргументом N1, результат относится к строковому типу. Пример: Mid(“ABCDEF”,2,3)=”BCD” LCase (S) – заменяет в символьной константе S все символы верхнего регистра соответствующими символами нижнего регистра. Пример: LCase(“ABC”)=”abc” UCase(S) – заменяет в символьной константе S все символы нижнего регистра соответствующими символами верхнего регистра. Пример: UCase(“abc”)=”ABC” ASC(“C”) – определяет ASCII-код символа С, результат относится к числовому типу. Пример: ASC(“Z”)=90 Chr(N) – результатом является символ, с кодом ASCII, равным N, результат относится к строковому типу. N принимает значения не более 255. Пример: Chr(90)=”Z” Spase(N) – возвращает строку пробелов длиной N символов. Внимание! При применении функции Len к константам фиксированной длины результат всегда равен формальной, т.е. объявленной длине константы. Чтобы узнать фактическую длину, применяем конструкцию Len(Trim(S)). InStr ([k, ] S1, S2 [, Compare]) – дает возможность определить, содержит ли одна строка другую строку. Результатом функции является число, обозначающее порядковый номер символа в S1, с которого начинается S2. Если InStr не находит S2 в String1, то возвращается 0. Если S1 (или S2) имеет значение Null, то функция также возвращает Null. Compare – необязательный аргумент, может быть любой из следующих предопределенных констант (если он опускается, то используется текущая установка Option Compare) vbBinaryCompare – бинарное сравнение двух строк; vbTextCompare – текстовое сравнение двух строк; k – необязательный аргумент, является численным выражением и указывает положение символа в S1, с которого должна начинаться проверка/ Пример: InStr(“ABCD”,”BC”)=2 Пример программы, определяющей диапазон ячеек по номерам столбцов (для простоты будем считать, что используются только столбцы A-Z). Sub primer() n_nomer% = 4 k_nomer% = 8 If k_nomer% < n_nomer% Then MsgBox ("Неверныеданные"): GoTo 1 n_stroka% = 5 k_stroka% = 10 If k_stroka < n_stroka Then MsgBox ("Неверныеданные"): GoTo 1 x% = 64 + n_nomer y% = 64 + k_nomer L1$ = Chr(x) L2$ = Chr(y) m1 = LTrim(Str(n_stroka)) m2 = LTrim(Str(k_stroka)) diap$ = L1 + m1 + ":" + L2 + m2 ActiveSheet.Range(diap).Select With Selection .Font.Bold = True .Font.Italic = True .Font.Size = 14 .Font.Name = "Times New Roman" .Borders.Color = RGB(100, 0, 100) End With 1: End Sub Обратная задача: по буквенным обозначениям столбцов определить их номера и задать определенные свойства соответствующему диапазону. SUB primer() Dim A As Object DIAP$=”a1:b6” D$=UCase(DIAP) NSTOLB$=Left(D,1) POSDV=InStr(D,”:”) KONSTOLB$=Mid(D,POSDV+1,1) NSTR$=Mid(d,2,POSDV-2) KSTR$=Right(D,Len(D)-POSDV-1) NROW1=ASC(NSTOLB)-64 NROW2=ASC(KONSTOLB)-64 NLINE1=VAL(NSTR) NLINE2=VAL(KSTR) For I=NLINE1 To NLINE2 For J=NROW1 To NROW2 SET A=CELLS(I,J) A.INTERIOR.COLORINDEX=I+J NextJ NextI 19. Функции даты и времени 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. Second(D) – возвращает целое, содержащее секунды как часть времени, содержащегося в выражении типа Date. Секунды возвращаются как число между 0 и 59. Если аргумент не содержит значения времени, то возвращается 0. DateSerial(N,N,N) – возвращает значение последовательной даты для заданной даты. Слева направо аргументы представляют год (целое число от 100 до 9999), месяц (от 1 до 12) и день (от 1 до 31). TimeSerial(N,N,N) – возвращает значение последовательного времени. Слева направо аргументы представляют часы (целое число от 0 до 23), минуты (от 0 до 59) и секунды (от 0 до 59). DateValue(E) – возвращает значение типа Date, эквивалентное дате, заданной аргументом Е, который может быть строкой, числом или константой, представляющей время. TimeValue(E) – возвращает значение типа Date, содержащее время, заданное аргументом Е, который может быть строкой, числом или константой, представляющей время. Timer – возвращает число, представляющее количество секунд от полуночи в соответствии с системным временем компьютера. 20. Подпрограммы в языке VBA В языке VBA существуют два вида подпрограмм – подпрограмма-функция и подпрограмма-процедура. Принципиальное их отличие заключается в том, что подпрограмма-функция позволяет получить только один результат, присваиваемый имени функции; подпрограмма-процедура позволяет получить любое количество результатов. Подпрограмма-функция имеет следующую структуру: FUNCTION имя(P1 As T1, P2 As T2,…) As T |