ППП (Пакеты Прикладных Программ)
Курс 2 семестр
Фрезин Ирина Григорьевна
Введение в VBA
Visual Basic for Application (VBA) представляет собой набор средств программирования для создания собственных программ и подгонки имеющихся приложений под нужды пользователей. VBA является также объектно-ориентированным языком.
Обзор типов данных:
Тип данных определяет область возможных значений типа, структуру организации данных и операции, определенные над данными этого типа.
Явно объявить переменную можно как вначале блока, так и в том произвольном месте, где возникла использовать новую переменную. Переменные можно объявлять на двух уровнях: уровень процедуры и уровень модуля. Создание переменной путем ее использования в операторе называется неявным объявлением переменной. Тип переменной – variant.
Переменные лучше явно объявлять в программе, используя операторы: dim, public, private, static.
Оператор dim используется на обоих уровнях. Public, private используются на уровне модуля. Static – только на уровне процедуры.
Объявление простых переменных можно оформить так:
Dim счетчик As single
Dim цена As currency
Dim дата_платежа As date
Dim сообщение As string
Существует несколько типов констант:
1) Литеральные (строковые) константы. Строковые константы должны быть заключены в двойные кавычки. Пустая строка обозначается двумя двойными кавычками. Строковая константа должна находиться на одной строке.
2) Численные константы. Состоят только из символов 0-9. Впереди может стоять знак минус. Можно использовать экспоненциальную запись числа.
3) Константы date. Обязательно помещаются между знаками #.
4) Типизированные константы – константы, которые имеют тип. Const Pi As Double = 3.14
Арифметические операторы VBA:
+, -, * - сложение, вычитание, умножение
/ - деление
\ - целочисленное деление
Mod – деление по модулю
^ - возведение в степень
Оператор like позволяет выполнить операции сравнения строк, и его используют только для строк.
Выражение1 like выражение2
Оператор условного перехода позволяет выбрать одну ветвь при выполнении условия, либо две ветви.
If условие then оператор
If условие then
Оператор 1
Оператор 2
…
Оператор n
End if
If условие then оператор1 else оператор2
If условие then
Оператор 1
Оператор 2
…
Else
Оператор n
Оператор n+1
…
End if
Организация множественного выбора производится путем использования оператора case
Select case выражение
Case условие 1
Оператор 1
Case условие 2
Оператор 2
…
Case условие n
Оператор n
[ Case else
Оператор n+1]
End select
Циклы
Процесс выполнения всех операторов, заключенных в структуру цикла один раз, называется итерацией. Существуют циклы с фиксированным количеством итераций и неопределенные циклы.
Фиксированный цикл представляется двумя структурами:
1. Цикл for next используется, когда известно количество итераций до написания программы.
For a = start to end [step stepsize]
Операторы
Next [a]
2. Цикл for each выполняются столько раз, сколько имеется элементов в определенной группе.
For each a ln группа
Операторы
Next [a]
Основные понятия VBA
Одномерные массивы
Чтобы создать массив, нужно определить его имя, количество элементов и тип данных. Массивы создаются при помощи оператора Dim.
Dim ИмяМассива (размер) As ТипДанных
Если вместо слово Dim набрать слово Public, будет создан массив, областью видимости которого станет вся программа.
Пример:
Dim Моймассив (3) As integer
Dim МойМассив (1 to 3) As integer
В большинстве программ VBA при создании массива его сразу инициализируют, присвоив каждому элементу нулевое значение или пустую строку (используя цикл for next).
Dim сотрудник (4) As string
Dim i As integer
For i=0 to 4
Сотрудник (i)= “”
Next i
Обычно элементы массива содержат значения, относящиеся к одному типу. Если необходимо создать массив, в котором будут содержаться разные типы данных, используется тип массива object.
Dim MyMassiv (2) As object
MyMassiv (0) = ”Спрут”
MyMassiv (1) = 56
MyMassiv (2) = 3.1415
Двухмерные массивы и более
Порядок создания многомерных массивов такой же, только указывается соответствующая размерность.
Dim ДвухММассив (строки,столбцы) As типДанных
Dim ТрехММассив (X, Y, Z) As типДанных
Динамические массивы
Динамические массивы создаются с помощью оператора dim, private, public, static, причем список размерности опускается. Затем их размер устанавливается с помощью оператора ReDim во время выполнения программы.
ReDim [Preserve] varname (subscripts)[As type] [, varname (subscripts) [As type]]
Необязательное ключевое слово preserve приводит к тому, что VBA сохраняет данные в имеющемся массиве когда изменяется размер массива с помощью ReDim.
Varname – имя существующего массива.
Subscripts – размерность массива.
Примеры использования оператора ReDim:
1. Dim Month () As string – объявляет динамический массив Month
2. ReDim Month (1 to 30) – изменяет размер массива до 30 элементов
3. ReDim Month (31) – изменяет размер массива до 31элемента
4. ReDim Preserve Month (1 to 31) – изменяет размер массива до 31 элемента, сохраняя содержимое
5. Dim Table () As Integer – объявляет динамический массив Table
6. ReDim Table (3, 15) – делает массив двухмерным
7. ReDim Table (4, 20) – изменяет размер двухмерного массива
8. ReDim Preserve Table (4, 25) – только изменяет последний размер массива
9. Dim Mas As Variant – объявляет переменную типа variant
10. ReDim Mas (20) As Integer – создает массив 20 целых чисел в variant
Можно создать массив поэлементно:
Dim B (1 to 2, 1 to 2) As single
B (1, 1) = 2
B (1, 2) = 5
B (2, 1) =4
B (2, 2) =3
Получаем: 2 5
4 3
При работе с массивами можно использовать следующие функции и процедуры:
1) Переопределение переменной в типизированный массив
Array (списокАргументов)
Dim День As variant
День = Array (“Пн”, ”Вт”, ”Ср”, …)
2) VBA имеет две функции, которые отслеживают верхний и нижний индекс предела массива
Lbound (имяМассива [, размерность])
Ubound (имяМассива [, размерность])
3) Оператор Erase позволяет выполнять одну из двух задач, в зависимости от того, каким будет массив. Для статических массивов Erase очищает все элементы массива указанного массива, переустанавливая массив в первоначальное состояние.
Для динамических массивов Erase полностью удаляет массив из памяти.
Erase array1 [, array2,…]
Типы данных, возвращаемые пользователю
Функции преобразования типов:
| Функции | Возвращаемый тип |
| Asc | Integer |
| cBool | Boolean |
| CByte | Byte |
| CCur | Currency |
| CDate | Date |
| CDbl | Double |
| cDec | Decimal |
| CInt | Integer |
| CLng | Long |
| CSng | Single |
| CStr | String |
| CVar | Variant |
| Chr | Символ |
Private Sub CommandButton1_Click()
Dim пер1, пер2, summa As Integer
Пер1 = CInt (textbox1, text)
Пер2 = CInt (textbox2, text)
Summa = пер1+пер2
Label3.caption = CSng (summa)
End sub
Функции работы со строками:
| Функции | Пример | Описание |
| Len (str) | Из a=len(“персонажи”) следует a=9 | Определяет длину строки |
| Left (<строка>,<длина>) | Left(“1234string”,4)=1234 | Выбирает из аргумента строка указанное количество символов слева |
| Right (<строка>,<длина>) | Right(“1234string”,6)=string | Выделяет из аргумента строка указанное количество символов справа |
| Mid (<строка>,<старт>[,<длина>]) | Mid (“12345678,4,3) - 456 | Выделяет из аргумента строка под строку с указанным количеством символов, начиная с позиции старт |
| Mid (<строка>,<старт>) | Mid (“12345678,4,3) - 45678 | Выделяется под «строка» с позиции старт до конца |
| LTrim (<строка>) | LTrim(“ печать”) = “печать” | Удаляет пробелы в начале строки |
| RTrim (<строка>) | RTrim(“печать ”) = “печать” | Удаляет пробелы в конце строки |
| Trim (<строка>) | Trim(“ печать ”) = “печать” | Удаляет пробелы с обоих сторон строки |
| InStr([<старт>,]<строка 1>,<строка 2>[,<сравнение>]) | Instr(“C:\temp\fest.mdb”,”test”)=9 Если искомая строка не находится в указанной строке, функция возвращает ноль. | Производит поиск подстроки в строке, возвращает позицию первого вхождения строки (строка 2) в строку (строка 1) (старт – позиция, с которой начинается поиск) |
Функции преобразования строк:
1. LCase (<строка>) – преобразует все символы строки к нижнему регистру
2. UCase(<строка>) – преобразует все символы строки к верхнему регистру
3. StrConv(<строка>,<преобразование>) – выполняет несколько типов преобразования строки, в зависимости от второго параметра
4. Space(<число>) – создает строку, состоящую из указанного числа пробелов
5. String(<число>,<символ>) – создает строку, состоящую из указанного в первом аргументе числа символов
Свойства и методы объекта UserForm
Свойства UserForm:
1. Activecontrol – возвращает объектную ссылку на элемент управления
2. Hide – скрывает форму
3. Show – отображает форму
Свойства стандартных элементов управления:
1. ControlTipText – устанавливает текст, который отображается виде всплывающей подсказки, когда указатель мыши помещается на элемент управления.
Задача 1: Создать форму, в которой при вводе имени в текстовое поле после нажатия кнопки ОК выдается приветственное сообщение в метку в виде «привет, ИМЯ, сегодня – дд,мм,гг чч,мм».
Введите имя textbox1
Label2 – для вывода
Кнопка ОК
После двойного щелчка на кнопку, в блоке прописать:
Dim imya as string
Imya = textbox1.text
Label2.caption = imya & “, привет!” & Format (Now, “dddddd,hh, ч. mm мин.”)
Задача 2: используя инструкцию case, создать программу, которая в зависимости от введенного значения переменной a производит различные вычисления с переменными b, c. Если значения a не совпадают с программными, то выдается сообщение «введено не то значение».
Если а=5, то b+c
Если а=0, то –(b+c)
Если а=10, то b*c
Введите а textbox1.text
Введите b textbox2.text
Введите c textbox3.text
Label4 для вывода сообщения
Кнопка ОК
После двойного щелчка на кнопку, в блоке прописать:
Dim a,b,c,d as integer
a =val(textbox1.text)
b=val(textbox2.text)
c =val(textbox3.text)
select case a
case 0
d=-(b+c)
label4.caption = “-(b+c)= “& d
case 5
d=b+c
label4.caption = “b+c= “& d
case 10
d=b*c
label4.caption = “b*c= “ + Cstr(d)
case else
label4.caption = “введено неверное значение”
end select
Задача 3: создать программу, которая производит следующие действия: при вводе двух положительных чисел находится их произведение, при вводе отрицательных чисел находится частное, если числа разные – разность. При других значениях выдается сообщение.
Введите а textbox1.text
Введите b textbox2.text
Label3 для вывода сообщения
Кнопка ОК
Dim a, b, c As Integer
a =var(textbox1.text)
b=var(textbox2.text)
if (a<0) And (b<0) Then
label3. caption = “значение а/b” & (a/b)
end if
if (a>0) And (b>0) Then
label3. caption = “значение а*b” & (a*b)
end if
if (a>0) And (b<0) Then
label3. caption = “А+В” & (а+b)
end if
if (a<0) And (b>0) Then
label3. caption = “Значение не верное”
end if
Создать программу, работающую с тремя двухмерными массивами. Первый массив «а» вводится с клавиатуры. Второй массив «b» определен поэлементно в программе. Массив «с» организуется путем суммирования соответствующих элементов массива «а» и «b». Результат выводится в метку.
Создаем форму
«введите элементы массива»
«а 1,1» «поле ввода» «а 1,2» «поле ввода» «а 2,1» «поле ввода» «а 2,2» «поле ввода»
«массив а»
«массив b»
«массив с»
Button вывод
Обработка кнопки вывод:
Dim a (1 to 2, 1 to 2) as integer
Dim b (1 to 2, 1 to 2) as integer
Dim c (1 to 2, 1 to 2) as integer
Dim I, j as integer
A[1,1] = val(textbox1.text)
A[1,2] = val(textbox2.text)
A[2,1] = val(textbox3.text)
A[2,2] = val(textbox4.text)
b[1,1] =
b[1,2] =
b[2,1] =
b[2,2] =
for i=1 to 2
for j=1 to 2
c[I,j]=a[I,j]+b[I,j]
next j
next i
label6. caption =””
label7. caption =””
label8. caption =””
Задача 2
Составить программу вычисляемое значение функции:
Y={ inX/a2+b2, 0<X<1
{ √x*(a2+b2), x≥1
Введите а textbox1.text
Введите в textbox2.text
Введите х textbox3.text
Label и кнопка
Dim a,b,x,y as double
a = val(textbox1.text)
b = val(textbox2.text)
x = val(textbox3.text)
if x<0 then msgbox(‘x>0’)
if a=0 and b=0 then msgbox(‘a и b не ровны 0’)
if x>0 and x<0 then
y=lon(x) / (a^2 + b^2)
label4.caption=y
end if
if x>=1 then
y=(x*(a^2+b^2))^(1/2)
label4.caption=y
end if
Функции и процедуры
sub имя процедуры ()
…
End sub
Function имя функции ()
…
End function
Call имя процедуры
Переменная имя функции ()
Пример: создать программу, работающую с процедурами и функциями, параметрами которых являются значения, вводимые в текстовые поля формы. После нажатия кнопки отчет главная программа вызывает процедуру или функцию, и выводим полученный результат в соответствующую метку. Процедура выполняет следующие действия:
1. Суммирует первые два введенных значения
2. Уменьшает 3 и 4 значения.
Dim a, b, c, d, s, m as double
Sub sum()
A=val (textbox1.txt)
B=val (textbox2.txt)
S=a+b
End sub
{Тут как бы оставшиеся темы по VBA в Word}
VBA Excel
Объект Range является одним из ключевых объектов VBA в Excel.
Кнопка 1:
X = Range (‘ D1’).Value – возвращает значение в метку формы
Label1.caption = x
Используя то же свойство value, можно присвоить ячейкам значение программно.
Кнопка 2:
Range (“A1:B2”). Value = 5
Кнопка 3:
Возвращает число объектов в наборе
N = Range (“A1:D3”). Rows. Count
Label2.caption = n
Метод GoalSeek используется для решения уравнений с одной переменной х.
Пример 1: Разработать программу, которая по введенным числовым коэффициентам некоторого уравнения находит неизвестную переменную, при этом результаты вычисления выдаются на форму и в Excel.
Y=ax3+b*sin x
1) Выйти в Excel и сделать заготовку на листе:
| Решение уравнения y=ax^3+b*sin(x) | ||
| Коэффициент: | Значение: | |
| A | ||
| B | ||
| Y | ||
| X |
2) Сделать форму:
Label введите а: edit1
Label введите b: edit2
Label значение y: edit3
Button1 вычислить button2 очистить
Label найденное значение х: edit4
Button3 закрыть
3) Кнопка закрыть:
UserForm1.Hide
4) Кнопка очистить:
TextBox1.Text = “ ”
TextBox2.Text = “ ”
TextBox3.Text = “ ”
With ActiveSheet
.Range(“B3”).value = 0
.Range(“B4”).value = 0
.Range(“B5”).value = 0
.Range(“B6”).value = 0
.Range(“B6”).value = 0
End With
End Sub
5) Кнопка вычислить:
Dim a, b, c as double
A = CDbl(Textbox1.Text)
B = CDbl(Textbox2.Text) глобальные переменные
C = CDbl(Textbox3.Text)
With ActiveSheet
.Range(“B3”).value = a
.Range(“B4”).value = b
.Range(“B5”).value = c
.Range(“B6”).FormulaLocal = “=B3*B7^3+B4*sin(B7)”
.Range(“B6”).GoalSeek Goal:= c, changingCell:=Range(“B7”)
TextBox4.Text = Cstr(.Range(“B4”).Value)
TextBox4>Text = FormatNumber(Textbox4.text, 20
End With
Метод AutoFill
Этот метод используется для заполнения ячеек диапазона элементами последовательности.
Пример 2: Создать программу, которая автоматизирует ввод данных на студентов некоторой группы.
1. Подготавливаем заготовку на листе Excel:
| A | B | C | D |
| Список студентов | Группа | Адрес | |
| № | Фамилия |
2. Создаем форму:
Label фамилия edit1
Label группа edit2
Label адрес edit3
Label куратор edit4
Button1 создать таблицу
Button2 добавить строку
Button3 закончить
3. Создаем таблицу:
ActiveWorkBook.SaveAs(“работа с базой данных.xls”)
Nomer = 1
WorkSheets(“Лист3”).Range(“A4:D20”).Clear
4. Добавить строку:
StrName1 = Trim(Str(strNomer+nomer))
With ActiveSheet
.range(“A”+str(Name1).value = nomer
.range(“B”+str(Name1).value =textbox1.text
.range(“C”+str(Name1).value = textbox2.text
.range(“D”+str(Name1).value = textbox3.text
StrName2 = Trim(Str(strNomer+nomer+1))
Setrange1 =.range(“A”+strName1+”:D”+strname1)
Sterange2 =.range(A”+strname1+”:D”+strname2)
Range1.AutoFill Destination:=range2
.range(“A”+strname2+”:D”+strname2).clear
End With
Textbox1.text
Textbox2.text
Textbox3.text
Textbox.setfocus
Nomer = nomer+1
5. Кнопка 3:
Userform2.hide
With activesheet
Strname2 = trim(str(strnomer+nomer+2))
.range (“A”+strname2).value = “классный руководитель”
.range (“D”+strname2).value = textbox4.text
6. Глобальные переменные:
Const strnomer = 3
Dim strname1 as string
Dim strname2 as string
Dim strname3 as string