Разработка учебно-методического пособия VBA for EXCEL
учебно-методическое пособие по информатике и икт (9 класс)

В зависимости от значения константы по разному представляются в памяти ПК. Целые представляют последовательный набор фиксированного количества байтов, а вещественные, даже не отличаясь от целых по значению, имеют другую форму внутреннего представления, обусловленную применением арифметики с плавающей точкой при операциях с такими константами и представлением вещественного числа. В общем случае форма представления данных в памяти ПК определяется типом данных.

2. Типы данных

Тип данных определяет, каким образом биты данных, представляющие конкретное значение, хранятся в памяти ПК. В каждом языке программирования имеется свой фиксированный набор базовых типов данных. Некоторые языки позволяют создание дополнительных (пользовательских) типов данных. В VBA имеются следующие типы данных:

Поясним некоторые характерные для VBA типы данных.

Byte - Массивы данного типа служит для хранения двоичных данных, например, изображений. Использование данного типа предохраняет двоичные данные во время преобразования формата.

Boolean - для хранения логических (булевых) значений. По умолчанию значением булевской переменной является False- ложь.

Currency - для хранения чисел с дробной частью до четырех цифр и целой частью до 15 цифр, то есть данных с фиксированной десятичной точкой, удобных для денежных вычислений. Числа с плавающей десятичной точкой (Single, Double) имеют больший диапазон значений, но могут приводить к ошибкам округления.

Decimal - в версии 5.0 поддерживается использование типа данных Decimal только в пределах типа Variant, т.е. невозможно описать переменную с типом Decimal. Пользователь, однако, имеет возможность создать переменную типа Variant с подтипом Decimal с помощью функции CDec.

Object - поскольку VBA является объектно-ориентированным языком, в нем можно манипулировать различными объектами, адреса расположения которых в памяти (указатели) имеют этот тип.

String - по умолчанию данные строкового типа имеют переменную длину и могут удлиняться или укорачиваться. Однако такие строки занимают на 10байт памяти больше, поэтому можно объявить строки фиксированной длины, явно указав количество символов. Если количество символов будет меньше объявленного, то свободные места заполняются пробелами, при попытке занесения большего количества символов лишние отбрасываются.

Variant - может быть использован для хранения данных всех базовых типов без выполнения преобразования (приведения) типов. Применение данного типа позволяет выполнять операции, не обращая внимание на тип данных, которые они содержат. Удобен для объявления переменных, тип которых заранее неизвестен. Переменные этого типа могут содержать специальные значения: Empty, Null, Error.

3. Идентификаторы, переменные, массивы

Имена (идентификаторы) - употребляются для обозначения объектов программы (переменных, массивов, процедур и дp.). В VBA имена констант, переменных и процедур должны удовлетворять следующим требованиям:

• должны начинаться с буквы;
• не могут содержать точки и символов объявления типа;
• не могут быть длиннее 255 символов. Длина имен объектов не должна

    превышать 40 символов.

• не могут быть ключевыми словами (именами операций, операторов,  

    встроенных функций).

Переменные представляют собой зарезервированное место в памяти ПК для хранения значения. Переменные обозначаются именами - словами, используемыми для ссылки на значение, которое содержит переменная, и характеризуются типом, определяющим вид данных, которые можно хранить в переменной. Переменные могут изменять свои значения в ходе выполнения программы. По умолчанию переменные имеют тип данных Variant, если в модуле отсутствует инструкция DefТип. Для явного указания типа переменной можно в конце ее имени указать символ описания типа:

Currency - @; Double - #; Integer - %; Long - &; Single - !; String - $.

Объявить переменную - значит заранее сообщить программе о ее существовании. Объявление переменной производится специальным оператором. Одновременно с объявлением переменной после ее имени можно записать ключевое слово As, после которого задается тип переменной.

Инструкции DefТип используются на уровне модуля (т.е. их нельзя использовать внутри процедур) для задания типа данных, используемого по умолчанию для переменных, имена которых начинаются с соответствующих символов. Функции: DefBool, DefByte, DefInt,DefLng, DefCur, DefSng, DefDbl, DefDec, DefDate, DefStr, DefObj, DefVar

     Пример: DefBool диапазонБукв [диапазонБукв]

    Обязательный аргумент диапазонБукв имеет следующий синтаксис:

буква_1[-буква_2]

Аргументы буква_1 и буква_2 указывают диапазон имен, для которых задается тип данных по умолчанию. Каждый аргумент представляет первую букву имени переменной и может быть любой буквой алфавита. Регистр символов в аргументе диапазонБукв не существенен.

Массивы - последовательности логически связанных элементов одного типа, которым присвоено одно имя. Положение элемента в массиве однозначно определяется его индексами (одним, в случае одномерного массива, или несколькими, если массив многомерный). Имена массивов имеют те же ограничения, что и имена переменных, тип данных, хранящихся в массиве, задается так же, как для переменных. Однако если массив имеет тип Variant, его отдельные элементы могут содержать данные разных типов.

В VBA существуют два вида массивов: массив фиксированного размера и динамический массив, размер которого может изменяться во время выполнения программы с помощью специальных операторов. При объявлении массива фиксированной длины за его именем в круглых скобках задаются через запятую верхние границы каждой размерности, не превышающие максимального значения типа Long. По умолчанию нижняя граница равна 0, но ее можно задать явно с помощью ключевого слова To:

Lines (100 To 120) As String

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

Примечание. В VBA массивы любых типов данных требуют 20 байт памяти плюс 4 байт на каждую размерность массива плюс число байт, требуемых для хранения данных. Объем памяти, требуемый для сохранения данных, рассчитывается как произведение числа элементов на размер элемента.

 Например, данные в одномерном массиве, который содержит четыре элемента типа Integer, требующих по 2 байт на элемент, занимают 8 байт. Вместе с 20 байт на массив и 4 байт на размерность общий требуемый объем составляет 32 байт.

Структуры - в VBA нет понятия структуры, но есть определяемый пользователем с помощью инструкции Type тип данных.

Типы данных, определяемые пользователем, могут содержать один или несколько элементов любого типа данных, массивы или ранее определенные пользователем типы. Например:

Type MyType

' Имя записывается в строковую переменную.

        MyName As String        

' День рождения записывается в переменную даты.

        MyBirthDate As Date        

' Пол записывается в целую переменную

' (0 для женщины, 1 для мужчины).

        MySex As Integer        

End Type        

4. Операции, выражения, операторы

Операции.

В VBA существуют следующие типы операций:

• арифметические операции, используемые для выполнения математических вычислений: ^, *, /, \, Mod, +, -.
• Здесь \ - Возвращает результат целого деления двух чисел, Mod - Возвращает остаток при целом делении двух чисел (значение по модулю).
• операции сравнения, используемые для выполнения операций сравнения
• < , > , <=, >= , = , <> ;
• логические операции, используемые для выполнения логических операций

And - Возвращает результат конъюнкции (логического И) для двух выражений с операциями сравнения, либо выполняет поразрядное сравнение двух числовых выражений:

Eqv - Используется для проверки логической эквивалентности двух выражений с операциями сравнения, либо выполняет поразрядное сравнение двух числовых выражений:

Imp - Выполняет операцию логической импликации для двух выражений с операциями сравнения, либо выполняет поразрядное сравнение двух числовых выражений:

Not - Выполняет над выражением операцию логического отрицания, а также поразрядное изменение значений каждого разряда переменной:

Or - Выполняет операцию логического ИЛИ (сложения) для двух выражений:

Xor - Выполняет операцию исключающего ИЛИ для двух выражений:

• операция конкатенации символьных значений дpуг с другом с образованием одной длинной строки:

& - Используется для слияния двух строковых выражений.

Выражения - предназначаются для выполнения необходимых вычислений, состоят из констант, переменных, функций (например, exp(x)), объединенных знаками операций.

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

Различают выражения арифметические, логические и строковые.

• Арифметические выражения служат для определения одного числового значения.

Например, (1+sin(x))/2. Значение этого выражения при x=0 равно 0.5,

а при x = p/2 - единице.

• Логические выражения описывают некоторые условия, которые могут удовлетворяться или не удовлетворяться.

Таким образом, логическое выражение может принимать только два значения - "истина" или "ложь" (да или нет).

Рассмотрим в качестве примера логическое выражение x*x + y*y < r*r, определяющее принадлежность точки с координатами (x,y) внутренней области круга радиусом r c центром в начале координат.

При x=1, y=1, r=2 значение этого выражения - "истина", а при x=2, y=2, r=1 - "ложь".

• Значения строковых выражений - тексты. В них могут входить литерные константы, литерные переменные и литерные функции, разделенные знаком операции сцепки.

Например, А & В означает присоединение строки В к концу строки А. Если А = "куст ", а В = "зеленый", то значение выражения А&В есть "куст зеленый".

Операторы (команды). 

Оператор - это наиболее крупное и содержательное понятие языка: каждый оператор представляет собой законченную фразу языка и определяет некоторый вполне законченный этап обработки данных. В состав операторов входят:

• ключевые слова;
• данные;
• выражения и т.д.

Операторы подразделяются на исполняемые и неисполняемые. Неисполняемые операторы предназначены для описания данных и структуры программы, а исполняемые - для выполнения различных действий (например, оператор присваивания, операторы ввода и вывода, условный оператор, операторы цикла, оператор процедуры и дp.).

При создании программ VBA настоятельно рекомендуется определиться с правилами, по которым будут присваиваться имена объектам — соглашение об именовании.

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

str (или s) — String, символьное значение;

fn (или f) — функция;

sub — процедура;

c (или все буквы имени заглавные) — константа;

b — Boolean, логическое значение (True или False);

d — дата;

obj (или o) — ссылка на объект;

n — числовое значение;

• имена функций, методов и каждое слово в составном слове должно начинаться с заглавной буквы:

MsgBox objMyDocument.Name

    Sub CheckDateSub()

• в ранних версиях VB не было слова Const, все константы определялись как переменные, а для отличия их записывали заглавными буквами, между словами ставили символ подчеркивания, например COMPANY_NAME.

5.Функции для форматирования данных

Для форматирования данных в вашем распоряжении функция Format() и целый набор функций, которые начинаются с префикса Format... (FormatNumber(), FormatCurrency(), FormatDateTime() и т. п.)

Синтаксис функции Format() выглядит так:

Format(выражение, "формат"). Эта функция принимает выражение и форматирует его в соответствии с параметром формат.

Несколько примеров использования Format() :

Format(15/20, "Percent")

Format(Date, "Long Date")

Format(1, "On/Off")

Format(334.9, "###0.00")

Format("Просто текст", ">" )

6. Примеры

Пример №1

“Разнести числа по листам”

Подготовка исходных данных

1. На Листе1 (Числа) в ячейки А1–А20 занести случайным образом значения из интервала (–50; 50).

2. На Лист1 (Числа) в ячейку С1 записать “Количество +”, а в ячейку D1 поместить подсчитанное значение с количеством положительных чисел.

3. На Лист1 (Числа) в ячейку С2 записать “Количество –”, а в ячейку D2 поместить подсчитанное значение с количеством отрицательных чисел.

4. На Лист1 (Числа) в ячейку С3 записать “Количество 0”, а в ячейку D3 поместить подсчитанное значение с количеством чисел, равных нулю.

5. На Лист2 (Положительные) в ячейку В1 записать “Положительные” и, начиная с ячейки B2, в столбик поместить все положительные числа.

6. На Лист3 (Отрицательные) в ячейку С1 записать “Отрицательные” и, начиная с ячейки D1, в строку поместить все отрицательные числа.

7. Создать кнопку “Количество” на листе “Числа”.

8. Создать кнопку “Перенос” на листе “Числа”.

9. Создать кнопку “Очистить” на листе “Положительные”.

10. Создать кнопку “Очистить” на листе “Отрицательные”.

План работы

1. Переименуйте: Лист1 в “Числа”, Лист2 в “Положительные”, Лист3 в “Отрицательные”.

2. Примените к столбцу А (лист “Числа”) условное форматирование. После заполнения диапазона ячеек числами к положительным числам будет применяться такой формат: полужирный курсив, красный цвет; к отрицательным — полужирный курсив, синий цвет; нулевые значения — полужирный курсив, зеленый цвет. Для этого: выделите столбец А; выполните команду Формат |Условное форматирование. Появится диалоговое окно, в котором введем значения по образцу. (см.рис.6.1)

     3. Перейдите в редактор VBA.

4. Создайте модуль с помощью команды Insert | Module.

5. Создайте в модуле процедуру с помощью команды Insert | Procedure. Присвойте имя процедуре “Числа”.

6. Напишите текст программы для занесения чисел на лист.

Public Sub Числа()

Dim I As Integer

Randomize Timer

For I = 1 To 20

Sheets("Числа").Cells(I, 1) =

Int(Rnd * 100) - 50

Next I

7. Нарисуйте на листе “Числа” автофигуру. Назначьте ей процедуру выполнения программы Числа (см.рис.6.2).

    Для этого:

• Выделите фигуру.
• Вызовите контекстно-зависимое меню.
• Выполните команду “Назначить макрос”.
• Выберите в открывшемся диалоговом окне только что созданную программу “Числа”.

        8. Проверьте работоспособность программы.

9. Создайте макрос “Очистка_Чисел” для очистки диапазона ячеек А1:D20 листа “Числа”.

Public Sub Очистка()

Sheets("Положительные").Select

Range("B1:B20").Select

Selection.ClearContents

Sheets("Отрицательные").Select

Range("B1:B20").Select

Selection.ClearContents

Sheets("Числа").Select

End Sub

10. Подготовьте автофигуру и привяжите к ней макрос. (см. рис.6.3)

11. Создайте в этом же модуле еще одну процедуру Количество для подсчета количества положительных, отрицательных и нулевых значений.

Public Sub Koличество()

Rem Объявление переменных

Rem Pol — переменная для подсчета количества положительных чисел

Rem Otr — переменная для подсчета количества отрицательных чисел

Rem Nul — переменная для подсчета нулевых значений

Dim I As Integer, Pol As Integer, Otr As Integer,

Nul As Integer

Rem обнуление переменных

Pol = 0

Otr = 0

Nul = 0

Rem Открытие цикла для проверки чисел

For I = 1 To 20

If Sheets("Числа").Cells(I, 1) > 0 Then

Pol = Pol + 1

ElseIf Sheets("Числа").Cells(I, 1) < 0 Then

Otr = Otr + 1

Else

Nul = Nul + 1

End If

Next I

Rem Вывод на лист "Числа" результатов подсчета

With Sheets("Числа")

.Range("C1") = "Количество +"

.Range("D1") = Pol

.Range("C2") = "Количество -"

.Range("D2") = Otr

.Range("C3") = "Количество 0"

.Range("D3") = Nul

End With

End Sub

12. Создайте автофигуру с именем “Количество” на листе “Числа” и привяжите к ней программу Количество.

13. Создайте в этом же модуле еще одну процедуру Перенос для переноса положительных и отрицательных чисел по разным листам.

Public Sub Перенос()

Rem Объявление переменных

Dim I As Integer, IndPol As Integer, IndOtr As Integer

IndPol = 2

IndOtr = 4

Rem Занесение в ячейку B1 слово "Положительные"

Sheets("Положительные").Range("B1") = "Положительные"

Rem Занесение в ячейку B1 слово "Отрицательные"

Sheets("Отрицательные").Range("C1") = "Отрицательные"

For I = 1 To 20

If Sheets("Числа").Cells(I, 1) > 0 Then

Sheets("Положительные").Cells(IndPol, 2) =

Sheets("Числа").Cells(I, 1)

IndPol = IndPol + 1

ElseIf Sheets("Числа").Cells(I, 1) < 0 Then

Sheets("Отрицательные").Cells(1, IndOtr) =

Sheets("Числа").Cells(I, 1)

IndOtr = IndOtr + 1

End If

Next I

End Sub

14. Создайте автофигуру с именем “Перенос” на листе “Числа” и привяжите к ней программу Перенос. (см.рис.6.4)

15. Сохраните работу.

Пример №2

“Нахождение суммы в диапазоне ячеек”

Найти максимальный и минимальный элементы, сумму значений из ячеек диапазона, среднее значение можно обычным способом, используя стандартные алгоритмы. Но приложение Excel содержит более 400 встроенных функций рабочего листа. Вызвать их позволяет объект Application: Application. Функция Рабочего Листа (Аргументы).

Задача.

Нахождение суммы в диапазоне ячеек

Напишите программу вычисления суммы чисел, находящихся в диапазоне ячеек B1:B20, и выведите результаты вычисления с поясняющими надписями в ячейки A22:B22.

Решим эту задачу двумя способами.

1-й способ (стандартный алгоритм)

Подготовка исходных данных.

1. Переименуйте Лист1 в “Пример1”.

2. Заполните диапазон ячеек В1:В22 целыми числами.

Подготовка текста программы.

3. Войдите в редактор VBA. Вставьте модуль.

4. Вставьте в модуле новую процедуру с именем Сумма1способ.

5. Наберите текст программы.

Public Sub Сумма1способ()

Dim i As Byte, Sum As Integer

Sum = 0 'Вычисление суммы

With Sheets("Пример1")

For i = 1 To 20

Sum = Sum +.Cells(i,2)

Next i

.Cells (22,1)="Сумма1 =": .Cells (22,2) = Sum

End With

End Sub

2-й способ (применение функции)

Текст программы:

Public Sub Сумма2способ()

Dim i As Byte, Sum As Integer

Sum=0 'Вычисление суммы

Sum = Application.Sum(Sheets("Пример1".Range("В1:В20"))

Sheets("Пример 1").Cells(23,1)="Сумма2 ="

Sheets("Пример 1").Cells(23,2) = Sum

End Sub

6. Интерфейс (см.рис. 6.5.)

7. Сохраните  работу

Пример №3

“Сравнение чисел”

Напишите программу, которая из пары чисел А2 и В2, А3 и В3 и т.д. будет выбирать большее и помещать в столбец D, начиная с ячейки D2. Создайте кнопку для стирания результатов из столбцов А, В и D.

Условия:

Количество пар чисел для сравнения, а также сами числа должны генерироваться случайным образом.

В ячейки А1, B1, D1 надписи “1-е число”, “2-е число” и “Большее” должны заноситься программно.

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

План работы:

Подготовка исходных данных.

1. Переименуйте Лист2 в “Пример2”.

2. Заполните диапазон ячеек А1:В30 целыми числами. (см.рис.6)

Подготовка текста программы.

3. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

4. Вставьте в модуле новую процедуру с именем Сравнение_чисел. 

5. Наберите текст программы.

Public Sub Сравнение_чисел()

Dim I As Integer, N1 As Integer, A As Integer, B As Integer

Randomize Timer

Rem N1 — количество пар

N1 = 1 + Int(Rnd * 30)

Rem Заполнение ячеек случайными числами в диапазоне от -50 до +50

With Sheets("Большее")

For I = 2 To N1

.Cells(I, 1) = Int(Rnd * 100) - 50

.Cells(I, 2) = Int(Rnd * 100) - 50

Next I

Rem Занесение пояснительных надписей в ячейки и форматирование

.Range("A1:D1").Font.Size = 11

.Range("A1:D1").Font.Bold = True

.Range("A1:D1").Font.Color = vbBlue

.Range("A1") = "1-е число"

.Range("B1") = "2-е число"

.Range("D1") = "Большее"

.Range("A1:D1").Select

Selection.Columns.AutoFit

For I = 2 To N1

A = .Cells(I, 1).Value

B = .Cells(I, 2).Value

If A > B Then

.Cells(I, 4).Value = A

ElseIf A = B Then

.Cells(I, 4).Value = "равны"

Else

.Cells(I, 4).Value = B

End If

Next I

End With

End Sub

6. Создайте кнопку для стирания результатов из столбцов А, В и D любым известным вам способом.

7. Интерфейс (см.рис.6.6.)

8. Сохраните свою работу.

Пример №4

“Функции листа”

Напишите программу вычисления максимума, минимума, среднего значения и произведения чисел, находящихся в диапазоне ячеек А1:А10 на листе “Пример3”. Выведите результаты вычисления с поясняющими надписями в ячейки С2:D5. Решите эти задачи двумя способами.

 Подготовка исходных данных

1. Для того чтобы узнать правильное написание функции рабочего листа и ее применение, вспомним работу № 1, в которой мы обращались к окну Object Browser.

2. Войдем в окно просмотра объектов. Выберем в раскрывающемся списке Проект | Библиотека в верхнем левом углу экрана библиотеку объектов Excel.

3. В окне Классы выберем Application.

4. В окне списка Компоненты выберем WorksheetFunction. В нижней части окна появится Property WorksheetFunction As WorksheetFunction (ссылка).

Нажмем на ссылку, и в окне Компоненты появится список всех функций.

 Подготовка текста программы.

5. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

6. Вставьте в модуле новую процедуру с именем Функции_листа.

7. Наберите текст программы.

Public Sub Функции_листа()

Dim I As byte, m As Integer, m1 As Integer,

SR As Long, proiz As Long

Randomize Timer

For I = 1 To 10

Sheets("Пример3").Cells(I, 1) = Int(Rnd * 10)

Next I

'Нахождение минимума

m = Application.Min(Sheets("Пример3").Range("A1:A10"))

'Нахождение максимума

m1 = Application.Max(Sheets("Пример3").Range("A1:A10"))

'Произведение

proiz = Application.Product(Sheets("Пример3").Range("A1:A10"))

'Среднее значение

SR = Application.Average(Sheets("Пример3").Range("A1:A10"))

'Вывод результатов

With Sheets("Пример 3")

.Cells(2, 3) = "МIN ="

.Cells(2, 4) = m

.Cells(3, 3) = "МAX ="

.Cells(3, 4) = m1

.Cells(4, 3) = "Произведение ="

.Cells(4, 4) = proiz

.Cells(5, 3) = "Среднее = "

.Cells(5, 4) = SR

End With

End Sub

8. Интерфейс (см.рис.6.7.)

9. Сохраните работу.

Пример №5

“Количество диаграмм”

Написать программу подсчета количества диаграмм на листе.

 Подготовка исходных данных

1. Заполните диапазон ячеек А1:А11 числами от -5 до 5 (использовать автоматическое заполнение по первым двум числам) (см. рис.10)
2. Заполнить диапазон  В1:В11 квадратами чисел диапазона А1:А11
3. Заполнить диапазон С1:С11 квадратами чисел диапазона А1:А11 с противоположными знаками.
4. Построить 2 графика :  1 график – переменные А1:А11, значение функции – В1:В11; 2 график – переменные А1:А11, значение функции – С1:С11.  

 Подготовка текста программы.

5. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

6. Вставьте в модуле функцию с именем КоличествоД.

7. Наберите текст программы.

Public Sub КоличествоД()

MsgBox ActiveSheet.ChartObjects.Count _

& "  диаграммы"

End Sub

6. Интерфейс (см.рис.6.8)

Пример №6

“Сортировка данных”

Написать программу сортировки данных, находящихся в диапазоне ячеек А1:А20.

 Подготовка исходных данных

1. Заполните диапазон ячеек А1:А20 случайными целыми числами. (см.рис.11)

 Подготовка текста программы.

2. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

3. Вставьте в модуле процедуру с именем Случайные числа. (см.прим. № 1)

4. Вставьте в модуле функцию с именем Сортировка.

5. Наберите текст программы.

Public Sub Сортировка()

 Worksheets("Сортировка").Range("A1:A20").Sort _

        Key1:=Worksheets("Сортировка ").Range("A1")

End Sub

6. Интерфейс (см.рис.6.9.)

7. Сохраните работу.

Пример №7

“Поиск”

Написать программу поиска числа в диапазоне. Найти количество вхождений.

 Подготовка исходных данных

1. Заполните диапазон ячеек А1:А20 случайными целыми числами. (см.рис.12)

 Подготовка текста программы.

2. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

3. Вставьте процедуру с именем Случайные числа. (прим. № 1)

4. Вставьте в модуле функцию с именем Поиск.

5. Наберите текст программы.

Public Sub Поиск()

Dim i As Integer, Col As Integer, A As Integer, B As Integer

Col = 0

.Range("A1:D1").Font.Size = 11

.Range("A1:D1").Font.Bold = True

.Range("A1:D1").Font.Color = vbBlue

.Range("A1") = "Числа"

.Range("B1") = "Искомое число"

.Range("C1") = "Количество"

.Range("A1:D1").Select

Selection.Columns.AutoFit

For i = 2 To 20

A = .Cells(i, 1).Value

B = .Cells(2, 2).Value

If A = B Then

Col = Col + 1

End If

.Cells(2, 3).Value = Col

Next i

End With

End Sub

6. Интерфейс (см.рис.6.10.)

        7. Сохраните работу.

Пример №8

“Математическое ожидание”

Вычислить математическое ожидание и дисперсию по табличным данным:

 Подготовка текста программы.

1. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

2. Вставьте в модуле функцию с именем Ожидание.

3. Наберите текст программы.

Public Sub Ожидание()

Dim i As Integer, X, P, Mx, Mx2, M2x, D As Single

With Sheets("Ожидание")

.Range("A1:A4").Font.Size = 11

.Range("A1:A4").Font.Bold = True

.Range("A1:A4").Font.Color = vbRed

.Range("A1") = " X "

.Range("A2") = " P "

.Range("A3") = " M {X} =  "

.Range("A4") = " D = "

Mx = 0

Mx2 = 0

For i = 2 To 20

X = .Cells(1, i).Value

P = .Cells(2, i).Value

Mx = P * X + Mx

M2x = Mx * Mx

Mx2 = P * X * X + Mx2

D = Mx2 - M2x

Next i

.Cells(3, 2).Value = Mx

.Cells(4, 2).Value = D

End With

End Sub

4. Интерфейс (см.рис.6.11.)

5. Сохраните работу.

1.7. Урок №7

Тема: Управление файлами с помощью Visual Basic for Application

Приобретаемые знания: понятие атрибута файла; создание директории; копирование, перемещение, переименовывание и удаление файлов на диске; понятие текущего диска; поиск файлов, копирование в файл, запись в файл.

Основной материал: 

1. Понятие об управлении файлами.

2. Атрибуты файла

3. Средства Visual Basic для управления файлами.

4. Примеры

1. Понятие об управлении файлами.

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

2.  Атрибуты файла

      Каждый файл на диске имеет атрибуты, какого бы типа ни был дисковод. Windows и DOS используют атрибуты файла, чтобы определить, какие действия по управлению файлами допустимы по отношению к нему. Например, эти системы запрещают вам – и любой прикладной программе – удалять, модифицировать или переименовывать файлы с атрибутом Только чтение.                  

Windows создает атрибуты, когда вами или работающей на компьютере прикладной программой создается файл. В сущности, атрибуты файла похожи на свойства объекта: они дают файлу некоторую характеристику. Атрибуты – это часть информации о файле, хранящемся на дисковом носителе. Windows хранит эту информацию вместе с именем файла, размером и данными о дате и времени.

Windows автоматически поддерживает и обновляет атрибуты файла. В основном вам не надо о них беспокоиться, и обычно нет причины знать, что это такое – атрибуты файла. Нов некоторых случаях уметь пользоваться ими очень важно. Знакомство с ними и их значениями особенно пригодиться при работе с функцией Dir системы VBA.

Для определения характеристик файла система Windows (и DOS) применяют всего 7 атрибутов файлов. Каждый из может быть скомбинирован с другими атрибутами, за исключением метки тома. Например, у файла одновременно могут быть атрибуты Скрытый, Системный, Архивный и Только чтение. Ниже перечислены имена и значения всех таких атрибутов.

• Архивный. Указывает, был ли файл изменен с того времени, как было проведено последнее резервное копирование такими программами, как Windows BACKUP, или средствами других производителей, например, Fastback!, BackIt, Norton Backup и т.д. если у файла установлен атрибут Архивный, то ему требуется резервное копирование. Если такого атрибута нет, то со времени последней такой операции файл изменен не был.
• Каталог. Если у файла атрибут Каталог, то это означает, что он в действительности каталог или подкаталог (по терминологии Windows – папка). Дисковый каталог в сущности файл, в котором хранится информация о других файлах; при создании каталога Windows создает соответствующий файл с атрибутом Каталог. Этот атрибут позволяет распознать файл с данными о других файлах и не дает его переименовать, скопировать или удалить как обычный файл данных.
• Скрытый. Если у файла атрибут Скрытый, то Windows «Скрывает» такой файл, не показывая его в большинстве случаев вывода каталогов на экран. Однако имеется режим просмотра, позволяющий показывать имена таких файлов.
• Обычный. Если у такого файла атрибут Обычный, то это, в сущности, означает отсутствие у него каких-либо специальных атрибутов. Это так называемый обычный атрибут файла (иногда называемый общим) только означает, других атрибутов файла нет, кроме, возможно, архивного, если требуется резервное копирование.
• Только чтение. Означает, что файл можно считывать, но не изменять. Windows запрещает изменять, удалять или переименовывать файлы с атрибутом Только чтение.
• Системный. Указывает Windows, что файл является частью операционной системы компьютера. Эта накладывает запрет на изменение файла, как и в случае с атрибутом Только чтение. Если с помощью DOS-команды Sys (или панели управления Windows) создается диск для загрузки, то на него переносится все файлы с атрибутом Системный.
• Метка тома. Указывает Windows, что файл является меткой тома на диске. Метка – это имя, которое дается жесткому или гибкому диску при его форматировании: для этого используется команда Dos Label или меняется свойство Метка в окне свойств диска. Метка диска, в сущности, - не полный файл, а только имя файла в корневом каталоге диска, имеющее атрибут файла Метка тома. На диске может быть только одна метка.

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

3. Средства Visual Basic для управления файлами.

В VBA предусмотрен набор встроенных функций для выполнения различных операций с файлами, каталогами, дисками и прочими объектами файловой системы.

Встроенные функции для работой с файловой системой, предусмотренные в VBA:

• CurDir() — функция, которая возвращает путь к текущему каталогу, в котором будут сохраняться файлы нашего приложения по умолчанию.
• Dir() — позволяет искать файл или каталог по указанному пути на диске
• EOF() — при операции записи в файл на диске эту функция вернет True, если вы находитесь в конце файла. Используется при записи в файл своего собственного формата. При сохранении документов Word, книг Excel и т.п. лучше использовать стандартные методы объектов этих документов: Save и SaveAs().
• Error() — позволяет вернуть описание ошибки по ее номеру. Генерировать ошибку нужно при помощи метода RaiseError() специального объекта Err (см. главу 6, в которой рассказывается про перехват ошибок и отладку).
• FileAttr() — позволяет определить, как именно был открыт вами файл в файловой системе: на чтение, запись, добавление, в двоичном или текстовом режиме и т.п.
• FileDateTime() — позволяет получить информацию о последнем времени обращения к указанному вами файлу. Если к файлу после создания ни разу не обращались, то это будет время создания файла.
• FileLen() — позволяет определить длину указанного вами файла в байтах.
• FreeFile() — позволяет определить следующую свободную цифру, которую можно использовать как номер файла при его открытии.
• GetAttr() — возможность обратиться к файлу к файловой системе и получить информацию об его атрибутах (скрытый, доступен только для чтения, архивный и т.п.)
• Input() — позволяет считать информацию из открытого файла.
• Вариант этой функции — InputB() позволяет указывать количество байт, которые надо скачать из файла.
• Loc() — от Location, то есть местонахождение — возвращает число, которое определяет текущее место вставки или чтения в открытом файле. Похоже работает функция Seek(), но она возвращает информацию о позиции, с которой будет выполняться следующая операция чтения или вставки.
• LOF() — от length of file — позволяет определить длину открытого файла в байтах.
• DoEvents() — очень важная функция. Она позволяет на время отвлечься от выполнения какой-то операции VBA и передать управление операционной системе, чтобы обработать накопившиеся в операционной системе события (например, нажатия клавиш пользователем). После этого продолжение операции VBA продолжается. Если у вас работает очень долгая операция (поиск на дисках, обработка большого объема данных и т.п.) и вы хотите дать пользователю возможность быстро прервать эту операцию, можно выполнять эту команду, например, каждый раз после обработки определенной "порции" данных.
• Environ() — возвращает абсолютный путь для переменных окружения компьютера (полный список переменных, доступных на вашем компьютере, можно просмотреть, если в командной строке выполнить команду SET). Например, вам нужно записать что-то в файл во временном каталоге. Абсолютный путь к временному каталогу на вашем компьютере можно получить так: MsgBox Environ("TEMP")
• GetAllSettings() — получить (в виде двухмерного массива) из реестра все параметры, которые относятся к указанному вами приложению. SaveSetting() позволяет записать эту информацию в реестр, DeleteSetting() — удалить. GetSetting() позволяет получить информацию об определенном параметре. Замечу, что эти методы позволяют обращаться только к одному очень далекому уголку реестра в ветви HKEY_CURRENT_USERS. Обращаться к другим параметрам реестра при помощи этих методов бесполезно. Рекомендую для работы с реестром использовать объектную библиотеку Windows Script Host Object Model, которая также есть на любом компьютере под управлением Windows 2000, XP и 2003. Нужный объект называется WSHShell, методы — RegRead(), RegWrite() и RegDelete().
• Partition() — позволяет определить, к какому диапазону из наборов значений относится переданное вами число и возвращает описание этого диапазона (в виде строки). Обычно используется при выполнении запросов к базам данных.
• QBColor() — позволяет перевести обозначение цвета из древнего номерного обозначения с возможными 16 значениями в RGB-код, который понимается VBA. Обычно используется при переделке унаследованных программ.
• RGB() — еще одна функция для работы с цветом. Позволяет вернуть цветовой код, который можно использовать для присвоения цвета в коде, приняв три значения для цветов: красного (Red), зеленого (Green) и синего (Blue). Значение для каждого из основных цветов могут варьироваться от 0 до 255. Например, самый зеленый из возможных цветов получится, если переданные этой функции значения будут выглядеть как RGB(0,255,0).
• Shell() — позволяет запустить из VBA внешний программный файл и вернуть информацию о его Program ID в операционной системе. Обычно используется опытными разработчиками при применении ими в программах возможностей Windows API. С практической точки зрения эту функцию можно использовать для запуска любых внешних программ из вашего приложения, хотя, с моей точки зрения, применение специальных объектов WshShell и WshExec из библиотеки Windows Script Host Object Model удобнее (можно передавать в окно клавиатурные комбинации, принимать и передавать значения через командную строку и т.п.).
• TypeName() — функция, которая возвращает имя типа данных для переданной ей переменной. Очень удобна для определения типа данных для значения, полученного из базы данных или путем вызова метода какого-то объекта.
• VarType() — делает почти то же самое, но вместо имени возвращает числовой код, который обозначает тип данных. Можно использовать для программных проверок типов данных для переменных.

4. Примеры

Пример 1. Использование функции CurDir для получения текущих каталога и дисковода

Sub Получение_текущих_каталога_и_дисковода ()

1:  Dim dirName As String

2:  Dim dirLetter As String

3:  dirName = CurDir()

4:  dirLetter = Left(dirName, 1)

5:  MsgBox "Текущий дисковод - " & dirLetter

6:  MsgBox "Текущая директория - " & dirName

7:  dirName = CurDir("D")

8:  MsgBox "Текущая директория на диске D - " & _

9:  dirName

10:  End Sub

В строках 1 и 2 описаны переменные этой процедуры. Переменная DirName предназначена для хранения результата выполнения  CurDir, а DirLetter – для хранения буквы дисковода, извлеченной из DirName.

В строке 3 вызывается функция CurDir без аргументов, чтобы получить текущие папку и дисковод, результат же выполнения присваивается переменной DirName. В строке 4 для копирования первой буквы у CurDir и присвоения ее DirLetter используется функция Left. Поскольку CurDir возвращает полный путь, с буквой дисковода включительно, первый символ результата выполнения этой функции всегда является буквой дисковода.

В каждой из строк 5 и 6 на экран выводятся сообщения; в первом из них говорится о букве текущего дисковода, а во втором – о текущей папке этого дисковода.

Затем в строке 7 снова вызывается функция CurDir, на этот раз с аргументом – буквой D. в данном случае требуется получить текущую папку на диске D. если при выполнении процедуры в этом дисководе нет диска, то вы можете получить сообщение об ошибки времени выполнения.

В строке 8 выводится сообщение о текущей папке на дисководе D.

Пример 2. Использование функции  MkDir для создания нового каталога

Sub Создание_нового_каталога ()

1:  Dim newDir As String

2:  newDir = "D:\test1"

3:  MkDir newDir

4:  ChDir newDir

5:  MsgBox "Текущая директория на диске D: - " & _

6:  CurDir("D")

7:  End Sub

В строке 1 описана переменная newDir типа String для хранения имени новой папки.

В строке 2 этой переменной присваивается значение D:\test1.

В строке 3 для создания новой папки используется инструкция MkDir.

В строке 4 новая папка назначается текущей на диске D.

В строках 5 и 6 для вывода на экран сообщения о текущей папке на D используется MsgBox (как подтверждение создания новой папки).

Пример 3. Использование функции  RmDir для удаления каталога

Sub Удаление_каталога ()

1:  Dim delDir As String

2:  delDir = "D:\test1"

3:  If CurDir("D") = delDir Then ChDir "D:\"

4:  RmDir delDir

5:  MsgBox "Удален каталог test1"

6:  End Sub

В строке 1 описана переменная delDir типа String для хранения имени удаляемой папки.

В строке 2 этой переменной присваивается значение D:\test1 (предполагается, что папка test1 существует на диске D).

В строке 3 для определения на диске D текущей папки вызывается функция CurDir. Если удаляемая папка оказывается текущей, то в этой же строке 3 вызывается функция ChDir, чтобы на диске D сделать текущим корневой каталог. Папку нельзя удалить, если она является текущей или не пустой. В строке 4 для удаления с диска D подкаталога test1 используется инструкция RmDir.

Пример  4. Использование функции FileCopy для копирования файла на другой диск или в другую директорию

Sub Копирование_файла ()

1:  Dim sName As String

2:  Dim dName As String

3:  Do

4:  With Application

5:  sName = .GetOpenFilename(Title:= _

6:                          "Выбор копируемого файла")

7:  If sName = "False" Then Exit Sub

8:   dName = .GetSaveAsFilename(Title:= _

9:                           "Выбор места для копии")

10:  If dName = "False" Then Exit Sub

11:   End With

12:   FileCopy Source:=sName, Destination:=dName

13:  Loop

14:  End Sub

Эта процедура дает пользователю возможность выбрать для копирования файл, а затем указать диск и папку, куда копировать, и имя для копии (целевого файла).

В строках 1 и 2 описаны 2 переменные типа String для хранения имен исходного и целевого файлов.

В строке 3 начинается бесконечный цикл Do (цикл без определенных условий). Цикл из строк с 3 по 13 выполняется до тех пор, пока пользователь не отменит одну из двух файловых операций.

В строке 4 начинается инструкция With Application.

В строках 5 и 6 находится инструкция, которая вызывает метод GetOpenFilename для вывода на экран окна открытия файла и присваивает имя файла (результат выполнения метода) переменной sName.

В строке 7 проверяется, не отменил ли пользователь открытие файла. Если да, то процедура завершается. Если не отменил, то VBA продолжает работу и выполняет в строках 8 и 9 инструкцию, вызывающую метод GetSaveAsFilename, чтобы дать пользователю выбрать для целевого файла имя, дисковод и папку.

В строке 10 проверяется, не отменил ли пользователь эту операцию. Если отменил, то процедура завершается. Если нет, то VBA продолжает работу и выполняет в строке 12 инструкцию FileCopy. При выполнении этой инструкции происходит копирование файла (чье имя хранится в sName) в другой файл, чье имя, дисковод и папка хранятся в dName. После копирования цикл Do  повторяется.

Пример 5. Использование функции Kill для удаления файлов

Sub Удаление_файла ()

1:  Dim fName As String

 2:  Dim Ans As Integer

 3:  Do

 4:  With Application

 5:   fName = .GetOpenFilename(Title:="Удаление  

                              файла")

 6:  End With

 7:   If fName = "False" Then Exit Sub

 8:   Ans = MsgBox(prompt:="Удалить " & fName & "?", _

 9:                 Title:="Удаление файла ", _

10:                  Buttons:=vbQuestion + vbYesNo)

11:    If Ans = vbYes Then

12:       Kill fName

13:    End If

14:       Loop

15:  End Sub

В этой процедуре вначале используется метод GetOpenFilename, чтобы пользователь  выбрал имя файла, затем подтверждается удаление, удаляется файл и затем выполнение повторяется.

В строке 1 описана переменная типа String для хранения имени файла, а в строке 2 переменная типа Integer для хранения ответа пользователя на сообщение с просьбой дать подтверждение.

В строке 3 начинается бесконечный цикл Do, который выполняется до тех пор, пока пользователь не отменит операцию с файлом.

В строке 4 начинается инструкция With.

В строке 5 вызывается метод GetOpenFilename  и присваивается имя файла (результат выполнения метода) переменной fName.

В строке 7 проверяется, не отменил ли пользователь открытие файла. Если да, то процедура завершается.

В строках 8-10 находится инструкция MsgBox, которая просит пользователя подтвердить удаление выбранного файла. В случае подтверждения VBA в строке 12 выполняет инструкцию Kill, которая удаляет файл.

! Перед удалением файлов следует проверять их атрибуты. В случае попытки удалить файлы с атрибутами Скрытый, Системный или Только чтение VBA выдаст сообщение об ошибке времени выполнения. Если требуется удалить файлы с этими атрибутами, используйте такие функции как GetAttr, чтобы узнать атрибуты, и SetAttr, чтобы их изменить.

Пример 6. Использование функции Name для переименование и перемещения файлов

Sub Переименование_и_или_премещение_файла ()

1:   Const iTitle = "Переименование - "

2:   Dim oldName As String

3:   Dim newName As String

4:   Dim oldDir As String

5:   oldDir = CurDir()

6:   With Application

7:   oldName = .GetOpenFilename(Title:=iTitle & "Выбор  

                                файла")

8:       If oldName = "False" Then Exit Sub

9:   newName = .GetSaveAsFilename (InitialFileName:=  

                                       oldName, _

10:    Title:=iTitle & "Новое имя")

11:  If newName = "False" Then Exit Sub

12:  End With

13:  If Left(oldName, 1) = Left(newName, 1) Then

14:      Name oldName As newName

15:  Else

16:      FileCopy oldName, newName

17:  Kill oldName

18:     End If

19:  ChDrive oldDir

20:  ChDir oldDir

21:  End Sub

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

В строке 1 описана строковая константа, используемая в заголовках окон, выводимых в процедуре. Переменная oldName предназначена для хранения старого имени файла и newName – для нового, а oldDir предназначена для хранения имени диска и папки, являющихся текущими в момент начала выполнения процедуры, с тем чтобы восстановить их после ее завершения.

В строке 5 вызывается функция CurDir и присваивается ее результат переменной oldDir.

В строке 6 начинается инструкция With Application.

В строке 7 используется метод GetOpenFilename, чтобы пользователь выбрал файл, который надо переименовать или переместить.

В строке 8 используется инструкция If для проверки, не отменил ли пользователь открытие файла. Если да, то процедура завершается.

В строке 9 вызывается метод GetSaveAsFilename, чтобы дать пользователю выбрать новые имя, дисковод и папку.

В строке 11 проверяется, не отменил ли пользователь эту операцию. Если отменил, то процедура завершается.

В строке 13 начинается инструкция If…Then…Else, которая проверяет, выбрал ли пользователь для переноса файл на другой диск. В ее логическом выражении для получения первой буквы из каждой строки oldName и newName используется функция Left. Если буквы одни и те же, то это значит, что пользователь переименовывает или переносит файл на том же диске.

В строке 14 выполняется инструкция Name, чтобы переименовать файл. Если пути по каталогам в oldName и newName различны, то Name переносит файл в новую папку.

Если первые буквы в строках oldName и newName различны, это значит, что пользователь выбрал перенос файла на другой диск. В этом случае нельзя использовать инструкцию Name, поэтому в процедуре вначале для копирования файла с одного на другой применяется FileCopy, а затем для удаления исходного файла – Kill.

! Не путайте инструкцию Name со свойством Name, которое имеется у многих объектов. Инструкция переименовывает файлы, а свойство хранит имя объекта.

Не пытайтесь с помощью инструкции Name переместить файл с одного диска на другой.

Не пытайтесь переименовать открытый файл. Если вы это сделаете, VBA  выдаст сообщение об ошибке времени выполнения. Файл вначале надо закрыть.

Пример 7. Использование функции GetAttr и интерпретация ее результата

*: Sub Атрибут (fName As String)

1:  Dim fAttr As Integer

2:  Dim mStr As String

3:  fAttr = GetAttr(fName)

4:  mStr = UCase(fName)

5:  mStr = mStr & "  имеет следующие атрибуты:  " &  

    vbCr

6:  If (fAttr And vbReadOnly) Then _

7:    mStr = mStr & "Только чтение" & vbCr

8:  If (fAttr And vbHidden) Then _

9:    mStr = mStr & "Скрытый" & vbCr

10: If (fAttr And vbSistem) Then _

11:   mStr = mStr & "Системный" & vbCr

12: If (fAttr And vbVolume) Then _

13:   mStr = mStr & "Volume" & vbCr

14: If (fAttr And vbDirectory) Then _

15:   mStr = mStr & "Директорий" & vbCr

16: If (fAttr And vbArchive) Then _

17:   mStr = mStr & "Архивный" & vbCr

18: MsgBox mStr

19: End Sub

20: Sub Чтение_атрибута_файла()

21: Dim strPath As String

22: Атрибут fName:="c:\io.sys"

23: Атрибут fName:="c:\msdos.sys"

24: strPath = Application.StartupPath

25: Атрибут fName:=strPath

26: End Sub

В примере 1 представлены 2 различные процедуры. Первая, Атрибут, находится в строках 1-19, а вторая, Чтение_атрибута_файла, - в строках 20-26. Вторая процедура просто несколько раз вызывает первую, каждый раз передавая другое имя файла.

В строке * находится описание процедуры Атрибут, у которой имеется один обязательный аргумент с именем fName и типом данных String.

В строке 1 описана переменная fAttr с типом данных Integer, предназначенная для хранения значения, возвращаемого функцией GetAttr.

В строке 2 описана переменная mStr с типом данных String, в которой будет хранится сообщение, создаваемое этой процедурой.

Когда VBA выполняет инструкцию из строки 3, то он вызывает GetAttr, передавая в качестве аргумента строку, хранящуюся в переменной fName; результат выполнения сохраняется в переменной fAttr. Если файл с указанным именем не найден на диске, то на экран выводится сообщение об ошибке времени выполнения с текстом: File not found.

В строках 4 и 5 находится первая часть создаваемого процедурой сообщения. В первой их них находится функция UCase, предназначенная для преобразования fName в верхний регистр.

В строках 6-17 находится несколько инструкций It…Then, оценивающих хранящееся в fAttr число, которое является суммой числовых кодов атрибутов файла. Обратите внимание, что эти инструкции не являются вложенными. Наоборот, значение fAttr тестируется 6 раз и при том проверяется каждое возможное значение. Если инструкция If…Then  находит интересующий ее атрибут, то в строку из mStr добавляется ее имя.

В строке 18 выводится сообщение, созданное предыдущими инструкциями, а в строке 19 процедура Атрибуты завершается.

В строке 20 дано описание процедуры Чтение_атрибута_файла, которая несколько раз вызывает Атрибуты .

В строке 22 Атрибуты выводит на экран атрибуты файла IO.SYS. он является частью операционной системы Windows и всегда расположен в корневом каталоге диска начальной загрузки. Как часть операционной системы файл IO.SYS имеет атрибут Системный; у него есть еще атрибут Только чтение, который предотвращает удаление этого жизненно важного файла. Кроме того, он имеет атрибут Скрытый, так что его обычно не видно в окнах файлов Windows или в списках каталогах программы проводник.

В строке 25 вместо имени файла задается имя папки (папка автозагрузки). После того, как выполнится эта строка, на экране появится сообщение, в котором будет говориться об атрибуте Каталог. Последнее сообщение, в зависимости от того, выполнили ли вы недавно резервное копирование Personal.exe или нет, выведет или не выведет атрибут Архивный.

Пример 8. Использование функций FileDateTime и FileLen для определения даты, времени изменения и размера файлов.

Sub Определение_атрибутов_файла (fName As String)

1:  Dim msg1 As String

2:  Dim msg2 As String

3:  Dim fDate As Date

4:  Dim fLen As Long

5:  fDate = FileDateTime(fName)

6:  fLen = FileLen(fName)

7:  msg1 = "Размер: "& Format(fLen, "###,###,###") & _

8:                  " байт."

9:  msg2 = "Дата последнего изменения: " & _

    10:         Format(fDate, "long date") & _

11:        Format(fDate, "long time")

12: MsgBox Title:="File date and size", _

13:              prompt:=fName & Chr(13) & _

14:              msg1 & Chr(13) & msg2

15:  End Sub

16:  Sub Определение_даты_времени_изменения_и_размера  

        _файла ()

17:  Dim sName As String

18:  Do

19:  With Application

20:  sName = .GetOpenFilename(Title:="Размер и дата  изменения файла")

21:  End With

22:  If sName <> "False"

        Then Определение_атрибутов_файла sName

23:  Loop Until sName = "False"

24:  End Sub

В строках с 1 по 4 описано насколько переменных. Первые 2 – msg1 и msg2 – используются при составлении текста, выводимого процедурой сообщения. Переменная fDate предназначена для хранения даты и времени, а fLen – для хранения длины файла. Обратите внимание, что у fLen тип Long, так как длина файла может достигать миллионов байтов.

В строке 5 вызывается функция FileDateTime, которая для указания имени файла использует аргумент fName, и в той же строке результат выполнения функции сохранится в переменной fDate.

В строке 6 вызывается функция FileLen, использующая тот же аргумент fName, и в той же строке 6 результат выполнения функции сохранится в переменной fLen.

В строках 7 и 8 содержится инструкция, создающая первую часть выводимого процедурой сообщения и сохраняющая ее в msg1.

В строках 9-11 содержится другая инструкция, создающая вторую часть сообщения и сохраняющая ее в msg2.

В строках 12-14 инструкция MsgBox выводит на экран имя файла, его размер, а так же дату и время его последнего изменения.

В строках 16-23 содержится процедура, которая тестирует ShowFileDateSize. При этом вначале используется метод GetOpenFilename, чтобы обеспечить возможность выбрать файл, а затем вызывается процедура ShowFileDateSize. Она выводит на экран сообщение о дате, времени и размере файла.

Пример 9. Использование функций Open, Input, Print для копирования информации из файла в файл.

Sub Копирование_из_файла()

1: Dim T As String, Text1, Text2 As Integer

2: Text1 = FreeFile()

3: Open "E:\TEXT\MyS.txt" For Input _

                          As Text1

4: Text2 = FreeFile()

5: Open "E:\TEXT\MyD.txt" For Output _

                          As Text2

6: Do While Not EOF(Text1)

7: Input #Text1, T

8: If Left(T, 1) = "3" Then

9: Print #Text2, T

10: End If

11: Loop

12: Close #Text1, #Text2

13: End Sub

В строке 1 объявляются переменные  T , Text1, Text2.

В строках 2-3 открывается файл MyS.txt, с которого будет проводиться копирование.

В строках 4-5 открывается файл MyD.txt, в который будет производиться копирование.

Примечание: Файлы  MyS.txt и MyD.txt должны быть созданы до выполнения программы, иначе будет выдано сообщение о времени выполнения.

В строках 6-7 производится считывание из файла.

В строках 8-9 производится копирование третьей строки в файл.

В строке 12 закрываем оба файла.

Пример 10. Использование функций Open, Output, Print для записи в файл.

Sub Запись_в_файл()

1: Dim Text1 As Integer

2: Text1 = FreeFile()

3: Open "E:\TEXT\MyS.txt" For Output _ As Text1              

4: Print #Text1, "VBA for Excel"

5: Print #Text1, "4. Обработка данных, статистика"

6: Print #Text1, "6. Формирование файлов"

7: Close #Text1

8: End Sub

В строке 1 объявляется переменная Text1.

В строке 2-3 открывается файл MyS.txt для записи.

В строках 4-6 производится запись в файл строк: VBA for Excel, 4. Обработка данных, 6. Формирование файлов.

В строке 7 файл закрывается.

2. Задачи для самостоятельной работы

2.1. Урок №1

1. Запись макроса с помощью макрорекордера. Например, создать список своей группы.
2. В редакторе Visual Basic измените созданный вами в задании№1  макрос. Например, изменить список группы на список экзаменов.  
3.  Создайте простую функцию, которая перемножала бы два числа и возвращала результат.
4.  Записать с помощью макрорекордера «Титульный лист».
5.  Используя функцию и процедуру создать макрос для расчета силы тяжести по известной формуле F=m*g, где g=9,81.
6.  При помощи редактора VBA изменить предыдущий макрос для расчета прогнозной величины ВВП на следующий год на основании данных 2008 и 2009 годов, используя формулу арифметической прогрессии.
7. Используя Макрорекордер создать макрос – пустой фирменный бланк предприятия.
8.  Используя функции и процедуры написать в редакторе VBA макрос по расчету сложной формулы ax+by=? a,x,b,y являются исходными данными.
9.  Оформляя созданную форму необходимым образом, создать несколько макросов для расчета sin(x), cos(x), tan(x), ctg(x).
10.  При помощи редактора VBA изменить предыдущие макросы и заменить в них искомые формулы на: arcsinх, arccosх, arctanх, arcctgх.

2.2. Урок №2

1. Спроектируйте окна сообщений с помощью MsgBox.

1. Откройте Excel, переименуйте Лист1 в “Сообщения”.
2. На листе Excel создайте кнопку: .
3. Присоедините к кнопке программу, в результате которой появятся окна с различными сообщениями.
4. Спроектируйте несколько окон сообщений и оформите их посредством MsgBox.
5. Сохраните свою работу.

2. Используя теорему Пифагора создать программу для вычисления гипотенузы в прямоугольном треугольнике с помощью окон MsgBox, InputBox.
3. Создать простейший калькулятор(+, -, *, /) с помощью окон MsgBox, InputBox.
4. Создать пользовательскую форму: изменить окраску, вставить рисунок, поместить кнопки и переименовать их.
5. Создать 3 кнопки с присвоением каждой из них макроса. Каждая из кнопок должна считать соответственно НДС (18%), НДФЛ (13%), НП (24%). Для каждой кнопки использовать InputBox, MsgBox.
6. Написать макрос по расчету среднего количества учащихся в классах А, Б, В и Г с помощью InputBox, MsgBox.
7. Воспользовавшись InputBox, MsgBox, создать программу нахождения определителя матриц размера 3х3.
8. На чистой форме создать кнопку и присвоить ей макрос. Макрос должен выводить InputBox 2 раза для ввода числа и размера его процента, а также MsgBox для вывода результата.
9. С помощью InputBox, MsgBox написать макрос по решению следующей задачи: водитель проехал расстояние S (км) со средним расходом топлива R (л/100км), также известна цена 1л. топлива P (р/л). В какую стоимость обошлась поездка водителю?
10. Используя InputBox, MsgBox создать пригласительную открытку на корпоративный вечер.
11. Написать макрос для расчета чистого дисконтированного дохода предприятия на 5 лет. Ставку дисконтирования (Ен) принять равной 10%. Для ввода исходных данных Зi и Pi использовать InputBox. Для вывода результата MsgBox. Искомую формулу принять ЧДД = ∑ ((Pi-Зi)/(1+Ен)i)

2.3. Урок №3

1. С помощью конструкций If-then, Select Case составить  процедуру,

которая выводит день недели  по его номеру 1..7.

2. С помощью конструкций Go to,  For Each Next  составить процедуру, которая переводит каждый элемент массива a[i] *в –a[i].
3. С помощью конструкции With-End With составить процедуру, которая изменяет несколько различных свойств объекта Range.
4. Составить  процедуру,  которая выводит оценку по количеству баллов x:

100>=x>80 – 5;

80>=x>60 – 4;

60>=x>40  –3;

x<40 –неудов.

5. Для матрицы  =     найти .
6. Для матрицы  =     найти .
7. Составить  процедуру,  которая выводит порядковый номер месяца 1.. 12 по его названию.
8. Составить процедуру, которая каждый четный элемент массива переводит в 0 , а не четный в 1.
9. Составить процедуру, которая  находит скалярное произведение двух векторов.
10. Составить процедуру, которая находит длину вектора.

2.4. Урок №5

1. Отсортировать одномерный массив, состоящий из 10 элементов.
2. Отсортировать квадратную матрицу 5*5.
3. В матрице 3*4  найти элемент с наибольшим и элемент с наименьшим значением с помощью сортировки.
4. В матрице 3*4  найти элемент с наибольшим и элемент с наименьшим значением без использования сортировки.
5. Для матрицы найти разность между значениями наибольшего и наименьшего элементов.
6. Для вектора найти сумму значений наибольшего и наименьшего элементов.
7. Составьте процедуру, которая находит количество элементов равных данному числу.
8. Вычислите матрицу В, элементами которой являются квадраты элементов матрицы А, и найдите среди них наибольший.  Причем в матрице А элементы различного знака.
9. Дан вектор В. Составьте вектор А, такой что А[1]=B[1]+B[2], А[2]=B[2]+B[3] и т.д. А[n]=B[n], и найдите элемент с наименьшим значением.
10. Дан вектор В. Составьте вектор А, такой что А[1]=B[1]*B[2], А[2]=B[2]*B[3] и т.д. А[n]=B[n], и найдите элемент с наибольшим значением.

2.5. Урок №6

1. Напишите программу вычисления процента от суммы чисел, находящихся в диапазоне ячеек А1:А20. Сравните полученное число с исходными данными. При совпадении чисел вывести индексы элемента.
2. Написать программу сравнения дробных чисел, находящихся в диапазоне А1:В10. При равенстве чисел, сравнить сумму их координат. Оформить решение как диапазона вида

3. Найдите корни многочлена, заданного массивом своих коэффициентов, по схеме Горнера.
4. Составьте квадратное уравнение по его корням, используя обратную теорему Виета.
5. Напишите программу сравнения второй цифры сначала числа и третьей цифры с конца  числа. Найти разность и произведение этих цифр.
6. Дан массив А = {1..9}. Записать элементы массива в одну ячейку.  
7. Дано число С в десятичной с.с.  Перевести это число в двоичную с.с.
8. Найти НОК и НОД двух чисел; трех чисел.
9. Используя встроенные функции, написать программу вычисления значения функции. Построить график этой функции.
10.  Вычислить математическое ожидание и дисперсию

2.6. Урок №7

1. Найдите ошибку.

В этих двух фрагментах программы есть источник ошибки выполнения. В чем именно? (При выполнении упражнения исходите из предложения, что fName – переменная типа String)

(А) fName = GetOpenFilename

(Б)  With Application

       fName = GetSaveAsFileName

           End With

2. Найдите ошибку.

В инструкции есть источник ошибки времени выполнения. Где именно?

Name “C:\EXAMPLES\SALES.XLS” As “A:\SALES.XLS”

3.  Эквивалентны ли следующие два оператора:

(А)        If A >= B Then A = A * 2

(Б)   If A >= B Then

        A = A * 2

        End If

4. Создайте вложенные друг в друга каталоги на диске С, следующим

образом: С:\test1\test2\test3.

5.  Выполнить следующие действия:

1. Создать новый файл.

2. Скопировать все необходимые из исходного файла в этот новый файл.

3. Закрыть исходный файл и с помощью оператора Kill удалить его.

4. С помощью оператора Name переименовать новый файл, дав ему имя      исходного файла.

6. Создайте программу, которая возвращает список каталогов на диске C.
7.  Используя функцию IsDickFile напишите функцию с именем   IsDiscFolder, возвращающую значение True, если указанная папка   существует, и False в противном случае.
8. Создайте 2 файла: Файл1 и КопияФайл1. Заполните Файл1 своими личными  данными. Копируйте данные из Файл1 в КопияФайл1. В КопияФайл1 форматируйте текст: задайте цвет, шрифт. Удалите Файл1.
9. Создайте программу, записывающую саму себя в файл.
10. Создайте программу, которая запишет данные Файл1 в Файл2, данные  Файл2 в Файл1, а исходные данные удалит.

3. Контрольные вопросы

3.1. Урок №1

1. Что такое  макрорекордер, макрос?
2. В чем заключается принцип работы макрорекордера? Как записать    

      макрос в макрорекордере?

3. Способы остановки макроса в макрорекордере?
4. Каким образом можно запустить макрос, которому не назначена кнопка и

      клавиатурная комбинация?

5. Какими способами можно вызвать редактор?
6. Опишите следующие окна: ToolBox, Code, Properties.
7. Назовите три способа вызова окно проводника Project Explorer.
8. Что такое стандартный модуль, модуль классов, пользовательская форма?
9. Как открыть окно свойств и что оно позволяет?
10. Как открыть редактор кода Code Editor, и как он устроен?
11. Какие типы процедур вы знаете?
12. Чем полезны закладки и когда они применяются?
13. В каком случае выполняется процедура типа Sub?
14. Для чего нужны процедуры свойства Property?
15. Каким образом можно объявить процедуру?
16. Понятие параметра? Способы передачи параметров, их различия?
17. Когда применяется конструкция End и когда Exit?

3.2. Урок №2

1.  Дайте определение понятию экранная форма?
2. Этапы работы с пользовательской формой?
3. Что необходимо сделать для создания пользовательской формой?
4. Каким способом возможно вызвать панель элементов управления?
5. В чем суть элементов управления: Label, TextBox, ListBox, ComboBox?
6. Назовите команду необходимую для отображения окна свойств?
7. Наиболее часто используемые свойства UserForm и их значения?
8. Каким образом производится размещение элементов на форму?
9. Запишите функцию для создания окна сообщения?
10. Что такое сообщение, атрибуты, заголовок, умолчание?
11.  Для чего применяются окна InputBox? Ее синтаксис?
12.  Для чего применяется MsgBox?
13.  В чем отличия между функциями InputBox, MsgBox?

3.3. Урок №3

1. В каких случаях используется конструкция  Go to? Укажите синтаксис этой конструкции.
2. В каких случаях используется конструкция   If-then? Укажите синтаксис этой конструкции.
3. В каких случаях используется конструкция  With-End-With? Укажите синтаксис этой конструкции.
4. В каких случаях используется конструкция  Select Case? Укажите синтаксис этой конструкции.
5. В каких случаях используется конструкция   For Each Next? Укажите синтаксис этой конструкции.
6. Когда происходит выход из конструкции Go to?
7. Когда происходит выход из конструкций If-then, With-End With, Select Case,  For Each Next?
8. Назовите сходства и различия операторов If-Then-Else и Select Case.
9. Каким образом объявляются массивы?
10. Назовите разновидности массивов.

3.4. Урок №4

1. Назовите функции объекта Aplication.
2. Перечислите свойства объекта Aplication. Что они означают?
3. Являются ли свойства объекта Aplication  объектами Excel?
4. Что собой  представляют коллекции? Перечислите коллекции объектов.
5. Назовите методы  и свойства Workbook. Что они озночают?
6. Что собой  представляет объект Range?
7. Перечислите свойства объекта Range. Что лни озночают?
8. Если вы хотите вывести что-либо в ячейку, какой объект вы выберите?
9. Что означает слово Active перед объектом?
10. Какие свойства отвечают за формулы в ячейках?

3.5. Урок №5

1. Каким образом сортируются многомерные массивы?
2. Каким образом находятся элемент с наименьшим и элемент с наибольшим значением в массиве?
3. Назовите способы сортировки массивов.
4. Вы отсортировали массив. Где в массиве будут располагаться элементы с наибольшим и наименьшим значениями?
5. Дан массив Dim A(0 To n,0 To m) As Integer. Определите сколько в нем элементов.
6. Что выполняет цикл

For i = 0 To n-1

For j = i + 1 To n

If A(i) > A(j) Then

Temp = A(j)

A(j) = A(i)

A(i) = Temp

End If

Next j

Next i

7. Что выполняет процедура

Sub BubbleSort2()

Dim A(0 To n) As Integer

Dim Temp As Integer

For i = 0 To n-1

For j = i + 1 To n

If A(i) < A(j) Then

Temp = A(j)

A(j) = A(i)

A(i) = Temp

End If

Next j

Next i

End Sub

3.6. Урок №6

1.  Какие величины называют данными?
2.  Назовите виды данных. Дайте краткую характеристику по каждому виду.
3.  Перечислите типы данных и диапазон их значений.
4.  Дайте определение понятию «имена». Перечислите условия, которые необходимо выполнять при задании имени объекту.
5.  Дайте определение понятию «переменная». Объясните смысл выражения «объявить переменную».
6.  Дайте определение понятию «массивы». Назовите виды массивов и способы их задания.
7.  Назовите виды операций, существующих в VBA.
8.  Что представляет собой оператор?
9.  Как назначить автофигуре процедуру?
10. Расскажите принцип работы  процедур Сумма1способ и

    Сумма2способ(Пр.Раб. №2). В какой из них используются встроенные

    функции?

11. Для чего применяют функцию Selection.Columns.AutoFit? (Пример №3)
12. Какой объект будет добавлен после применения функции Charts.Add?

    (Пример №5)

13. Напишите формулы для вычисления математического ожидания и

    дисперсии. (Пример №8)

14.  Найдите ошибки в программе:

Dim i, A, B, Col As String

With Sheets("Лист1")

Col = 0

For i = 2 To 20

A = .Cells(i, 1).Value

B = .Cells(2, 2).Value

If A = B Then

Col = Col + 1

End If

.Cells(2, 3).Value = Col

End Sub

3.7. Урок №7

1. Назовите семь атрибутов файлов. Дайте характеристику этим атрибутам.
2. Если у файла несколько атрибутов, то как их выразить одним числом?
3. Какая функция применяется для чтения атрибутов файла? Что она возвращает?
4. В каком случае может быть виден файл, имеющий атрибут Скрытый?
5. Можно ли использовать SetAttr для задания атрибутов Каталог или Метка тома?
6. Сопоставьте:

7. В каких единицах функция FileLen() определяет длину файла?
8. Что делают методы GetOpenFilename и GetSaveFilename?
9. Чем отличаются функции LOF() и EOF()?  CurDir() и Dir()?
10. Как пользоваться функцией Dir для поиска в папке нескольких файлов?
11. Как узнать текущие дисковод и папку?
12. Какую инструкцию VBA необходимо использовать для копирования файла?
13.  Можно ли удалить открытый файл или папку, содержащую файлы?  Почему?
14.  Какое сообщение будет выведено инструкцию VBA необходимо использовать для переноса файла папки в другую в пределах одного дисковода? Можно эту же инструкцию использовать для переноса на другой дисковод?
15.  Какой цикл был использован в большинстве примерах?

4. Приложение

Рис.1.1. Диалоговое окно Запись макроса

Рис.1.2. Панель для работы с

макросами

Рис.2.1. Окно редактора

Рис. 2.2. Панель элементов управления

Рис.2.3. Панель инструментов

Рис.2.4. Экранная форма

Рис.2.5. Наиболее часто используемые свойства объекта UserForm

Рис.2.6. Значения параметров

Рис.2.7. Окно сообщений

Рис.2.8. Список значений, возвращаемых функцией MsgBoх

Рис.2.9. Пример применения функций InputBox и MsgBox.

Рис.2.10. Создание окон сообщений

Рис.3.1. Интерфейс программы из Пример 4.1.

Рис.5.1. Сортировка массива

Рис. 6.1. Диалоговое окно условного форматирования

Рис.6.2. Назначение макроса кнопке

Рис.6.3. Порядок выполнения программы из Пример1

Рис.6.4. Интерфейс программы из Пример 1

Рис.6.5.  Интерфейс программы из Пример 2

Рис.6.6.  Интерфейс программы из Пример 3

Рис.6.7.  Интерфейс программы из Пример 4

Рис.6.8.  Интерфейс программы из Пример 5

Рис.6.9.  Интерфейс программы из Пример 6

Рис.6.10.  Интерфейс программы из Пример 7

Рис.6.11.  Интерфейс программы из Пример 8

Заключение

С ростом производительности персональных компьютеров и сложности программных средств пользователям становится все труднее «держать в уме» все функции применяемого языка программирования. По этой причине в повседневной работе, как правило, используется далеко не весь спектр существующих возможностей конкретною инструментария или операционной среды, а только небольшая их часть. Практика показывает, что и вводные обучающие курсы дают представление только о самых важных командах и навязывают пользователю применение ряда типовых процедур, многие из которых далеко не оптимальны. Большинство пользователей, преодолев первый страх перед новым инструментарием и накопив достаточный опыт, начинают испытывать естественное желание познакомиться с возможностями среды разработки поближе. Им хочется использовать если не все, то многие из доступных средств.

Как следует из названия VBA, он относится к диалектам BASIC, являясь при этом объектно-ориентированным языком, обладающим значительными возможностями в отношении объектной модели и многократно используемого кода, что позволяет реализовать с его помощью достаточно сложные проекты.

Visual Basic for Applications — открытая система, допускающая расширение посредством элементов управления ActiveX. Благодаря этому качеству VBA существенно происходит по своим возможностям старые редакторы макросов, которыми компоненты Office оснащались раньше,

В то же время макрос можно записать «старым, добрым» способом, при этом он будет записан и станет доступным для редактирования в форме процедуры на языке Visual Basic; с одной стороны, это может оказаться полезным при создании сложной VBA-программы, с другой — представляет собой наиболее простой и легкий способ сделать первые шаги в программировании на VBA.

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

Список использованной литературы

1. Г. З. Гарбер “Основы программирования на VB и VBA в

        Excel 2007”.

2. И. Н. Порублев, А. Б. Ставровский “Алгоритмы и программы”.
3. Г. Штайнер “VBA. 6.3.справочник”.
4. http://all-ebooks.com “Основы программирования в Microsoft Office”