Объекты Application, Workbook, Worksheet и Range. Встроенные функции Dir, MkDir, MsgBox, InputBox
Программа СлучайныеЧисла
Для знакомства с некоторыми методами и свойствами указанных объектов внесем изменения в программу СлучайныеЧисла и напишем код, который:
1) определяет количество листов в новой рабочей книге и сохраняет его в переменной Листов;
2) устанавливает количество листов в новой рабочей книге, равное 1;
3) добавляет новую книгу;
4) изменяет название первого листа на Случ. числа;
5) разыгрывает 10 случайных чисел, лежащих в интервале от 0 до 100, и помещает их в первые десять ячеек первого столбца:
For i = 1 To 10
СлучЧисло= Int(100 * Rnd())
Cells(i, 1).Value = СлучЧисло
Next i
6) активизирует ячейку А1 листа Случ. числа;
7) добавляет в конец книги новый рабочий лист;
8) активизирует лист Случ. числа;
9) возвращает количество листов в новой рабочей книге, которое было до его изменения;
10) сохраняет текущую книгу в файле C:\St\Случайные числа.xls;
11) выводит сообщение: Случайные числа разыграны!.
Прежде чем писать программу, подумайте о том, какие команды можно записать с помощью макрорекордера. Это особенно актуально, если вы не помните синтаксис команды или какой-то фрагмент кода – большой по объему (например, фрагмент кода, относящийся к форматированию таблицы Excel).
В данном случае, с помощью макрорекордера можно записать пункты (2) – (4), (6), (8) и (10). Полученный макрос будет выглядеть так[7]:
| 1) | Sub Макрос1() |
| 2) | With Application |
| 3) | .UserName = "Муратова С.Ю." |
| 4) | .StandardFont = "Arial Cyr" |
| 5) | .StandardFontSize = "10" |
| 6) | .DefaultFilePath = "D:\" |
| 7) | .SheetsInNewWorkbook = 3 |
| 8) | .EnableSound = False |
| 9) | .RollZoom = False |
| 10) | End With |
| 11) | Workbooks.Add |
| 12) | Sheets("Лист1").Select |
| 13) | Sheets("Лист1").Name = "Случ. числа" |
| 14) | Range("A1").Select |
| 15) | Sheets("Случ. числа").Select |
| 16) | Sheets.Add |
| 17) | ActiveWorkbook.SaveAs _ Filename:="C:\St\Случайные числа.xls", _ FileFormat:=Normal, Password:="", _ WriteResPassword:="", ReadOnlyRecommended: = False, _ CreateBackup:=False |
| 18) | End Sub |
Даже не зная конструкцию With легко сообразить, что строки программы со 2-й по 10-ю задают свойства объекта Application. Так как нам надо изменить только одно свойство (SheetsInNewWorkbook), то вместо блока With можно написать одну команду:
Application.SheetsInNewWorkbook = 1
11-я строка макроса – добавление рабочей книги (метод Add) – относится к одиночному объекту семейства рабочих книг (Workbooks).
Так как в нашей рабочей книге имеется только один лист, то следующую команду (активизация листа с именем Лист1, метод Select) можно из программы исключить.
В 13-й строке для объекта Лист1 задается новое значение свойства Name (Лист1 переименовывается). Объект Лист1 – это одиночный объект семейства листов рабочей книги. Согласно правилу, к одиночному объекту семейства обращаются либо по имени, либо по его номеру в семействе объектов. Поэтому 13-ю строку можно записать и в таком виде:
Sheets(1).Name = "Случ. числа"
Если вспомнить, что переименовываемый лист является текущим, команду можно записать и так:
ActiveSheet.Name = "Случ. числа"
В 14-й строке активной становится ячейка с адресом А1 (объект – Range , метод – Select).
15-я и 16-я строки относятся к добавлению листа. ОБРАТИТЕ ВНИМАНИЕ: при выполнении этой операции новый лист вставляется перед текущим листом. Для того чтобы добавить лист в конец рабочей книги необходимо передать в метод аргумент After(который указывает номер листа, после которого вставляется новый лист). В данном конкретном случае, когда в книге содержится только один лист, команду можно было бы записать так:
Sheets.Add After:=Worksheets(1)
Мы же запишем её в общем случае, когда количество листов в рабочей книге определяется свойством Count:
Sheets.Add After:=Worksheets(Worksheets.Count)
В 17-й строке текущая книга сохраняется в папке St под именем Случайные числа.xls. Так как мы не изменяем аргументы, установленные по умолчанию, то эту команду можно записать в следующем виде:
ActiveWorkbook.SaveAs Filename:="C:\St\Случайные числа.xls"
Обратите внимание на то, как VBA ссылается на активную книгу (ActiveWorkbook).
Дополним код оставшимися командами и в результате получим следующую процедуру:
| 1) | Sub СлучайныеЧисла() |
| 2) | Dim СлучЧисло As Integer |
| 3) | Dim Листов As Integer |
| 4) | Листов = Application.SheetsInNewWorkbook |
| 5) | Application.SheetsInNewWorkbook = 1 |
| 6) | Workbooks.Add |
| 7) | ActiveSheet.Name = "Случ. числа" |
| 8) | For i = 1 To 10 |
| 9) | СлучЧисло = Int(100 * Rnd()) |
| 10) | Cells(i, 1).Value = СлучЧисло |
| 11) | Next i |
| 12) | Sheets.Add After:=Worksheets(Worksheets.Count) |
| 13) | Worksheets(1).Select |
| 14) | Range("A1").Select |
| 15) | Application.SheetsInNewWorkbook = Листов |
| 16) | ActiveWorkbook.SaveAs Filename:="C:\St\Случайные числа.xls" |
| 17) | MsgBox "Случайные числа разыграны!" |
| 18) | End Sub |
Комментарии к программе:
1-я строка – строка начала процедуры СлучайныеЧисла.
Во 2-й и 3-й строках объявляются 2 переменные: СлучЧисло и Листов – как целочисленные переменные.
4-я строка: в переменную Листов считываем значение свойства Excel «Листов в новой книге». Таким образом мы запомнили значение этого параметра, который был установлен до нашего вмешательства.
В 5-й строке изменяем это свойство на 1 (т.е. теперь в новых книгах будет 1 лист).
6-я строка: добавляем новую книгу.
В 7-ой строке переименовываем единственный лист на Случ. числа.
В строках с 8-ой по 11 разыгрываем 10 случайных чисел. В ней для генерации случайного целого числа от 0 до 100 используются встроенные функции Rnd и Int. Первая из них генерирует действительное случайное число в интервале от 0 до 1 и может принимать один аргумент — «затравочное» число (подробности — в справочной системе VBA). Функция Int преобразует действительное число в целое, удаляя все цифры после десятичного разделителя (то есть всегда округляя до меньшего). Умножая случайное число, полученное функцией Rnd, на 100 и отбрасывая дробную часть, получаем случайное целое, принадлежащее диапазону от 0 до 100.
Обратите внимание на 10-ю строку программы. Здесь объект, ячейка, задается свойством Cells, где 1-й индекс – это номер строки, а второй индекс – номер столбца. Данная конструкция особенно удобна в случае, когда заполнение/считывание ячеек осуществляется в цикле или заранее не известен адрес конечных ячеек.
12-я строка – добавление листа в конец книги.
13-я строка – активизация 1-го листа (Случ. числа) – пользователь должен сразу увидеть результаты выполнения программы, а не рыскать по книге в их поиске.
14-я строка – активизация ячейки А1. Даже если вы активизировали лист с результатами выполнения программы, их может быть не видно или видна лишь какая-то часть. Чтобы этого не произошло, подумайте, какую часть таблицы надо показать пользователю по завершении программы и активизируйте соответствующую ячейку. В данном случае вывод результатов начинается с первой ячейке листа, поэтому эту ячейку мы и сделали текущей.
15-я строка: после того как все манипуляции выполнены, вернем настройки пользователя, которые были изменены в начале программы («Листов в новой книге»). Как вы помните, значение этого свойства мы сохранили в переменной Листов, которое возвращаем теперь объекту Application (Excel).
В 16-ой строке происходит сохранение рабочей книги на диске С:, в папке St, с именем Случайные числа.xls.
В 17-й строке выводится сообщение о завершении выполнения операции. Пользователь не должен гадать, работает программа или она уже завершилась, поэтому в конце программы всегда выводите на экран соответствующее сообщение.
Задание:
1) откройте окно локальных переменных, установите точку останова, как показано на Рис. 2‑1, и запустите программу;
Рис. 2‑1
2) выполните цикл в пошаговом режиме, наблюдая за изменениями в окне локальных переменных;
3) создайте панель инструментов Случайные числа. Свяжите созданный макрос с кнопкой Разыграть;
4) запустите программу ещё раз – появится сообщение об ошибке и возникнет останов в команде, при выполнении которой произошла ошибка (Рис. 2-2);
Рис. 2‑2
5) прервите выполнение программы.
Комментарий к ошибке:
Возникшая ошибка демонстрирует несовершенство данной программы. По мере изучения языка VBA вам придется модернизировать программу так, чтобы она была удобна пользователю и «не вылетала» ни при каких условиях.
Программа УдалениеКниги
Напишем программу УдалениеКниги (в Модуле1), которая:
– удаляет файл C:\St\Случайные числа.xls;
– выводит сообщение: Файл Случайные числа.xls удален!
Если писать эту программу что называется в «в лоб», то получим следующий код:
| 1) | Sub УдалениеКниги() |
| 2) | Kill "C:\St\Случайные числа.xls" |
| 3) | MsgBox "Файл Случайные числа.xls удален!" |
| 4) | End Sub |
Это программа работает корректно только в том случае, когда книга Случайные числа.xls закрыта и хранится в папке C:\St. В реальности книга Случайные числа.xls может находиться в следующих состояниях: открыта, закрыта, не существует.
Задание:
1) запустите макрос УдалениеКниги при закрытой книге Случайные числа.xls;
2) создайте эту книгу заново и запустите макрос УдалениеКниги, когда книга Случайные числа.xls открыта. Обратите внимание на возникшую ошибку (УДАЛЕНИЕ ОТКРЫТОЙ КНИГИ ЗАПРЕЩЕНО!)
Прежде чем модернизировать программу УдалениеКниги, в Модуле1 напишем код, который проверяет существование файла C:\St\Случайные числа.xls:
Sub ПроверкаСуществованияФайла()
Dim Файл As String
Файл = Dir("C:\St\Случайные числа.xls")
If Файл <> "" Then _
MsgBox "Файл Случайные числа.xls существует!" _
Else MsgBox "Файл Случайные числа.xls не найден!"
End Sub
Обратите внимание на значения, которые возвращает функция Dir в зависимости от того, существует файл или нет.
Вернемся к программе УдалениеКниги. Для того чтобы эта программа «отслеживала» все возможные ситуации, запишем её в следующем виде:
| 1) | Sub УдалениеКниги() |
| 2) | Dim Файл As String |
| 3) | Файл = Dir("C:\St\Случайные числа.xls") |
| 4) | If Файл = "" Then |
| 5) | MsgBox "Файл не найден!" |
| 6) | Exit Sub |
| 7) | End If |
| 8) | For i = 1 To Workbooks.Count |
| 9) | If Workbooks(i).Name = "Случайные числа.xls" Then |
| 10) | Workbooks("Случайные числа.xls").Close False |
| 11) | Exit For |
| 12) | End If |
| 13) | Next i |
| 14) | Kill "C:\St\Случайные числа.xls" |
| 15) | MsgBox " Файл Случайные числа.xls удален!" |
| 16) | End Sub |
Комментарии к программе:
1-я строка – строка начала процедуры УдалениеКниги.
Во 2-й строке объявляется переменная Файл как строковая переменная.
3-я строка: с помощью встроенной функции Dir проверяем существование книги Случайные числа.xls.
4 – 7 строки: если книга не существует, то выводится сообщение о том, что файл не найден и выполнение программы прекращается (Exit Sub).
Далее следует код, который выполняется при наличии книги Случайные числа.xls. Тут возможны следующие ситуации:
a.Искомая книга открыта.
b.Искомая книга закрыта.
Проверка события: открыта ли книга Случайные числа.xls, осуществляется в цикле, в процессе перебора всех открытых книг (8-я строка). Само условие прописано в 9-ой строке. Данный фрагмент кода необходим для того, чтобы избежать ситуации «Удаление открытой книги».
Если книга с именем Случайные числа.xls найдена, то программа закрывает её без сохранения (10-я строка), выполнение цикла прерывается (11-я строка) и управление передается 14-й строке.
В эту же 14-ю строку мы попадаем после завершения цикла, если среди открытых книг искомая книга не найдена (т.е. она закрыта). Книга удаляется.
В 15-й строке выводится сообщение о выполнении операции, и выполнение программы прекращается (16-я строка).
Задания:
1. Добавьте кнопку Удалить книгу на панель инструментов Случайные числа. Свяжите её с макросом УдалениеКниги.
2. Протестируйте работу программы в различных режимах.