Объекты 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, и запустите программу;

Объекты Application, Workbook, Worksheet и Range. Встроенные функции Dir, MkDir, MsgBox, InputBox - №1 - открытая онлайн библиотека

Рис. 2‑1

2) выполните цикл в пошаговом режиме, наблюдая за изменениями в окне локальных переменных;

3) создайте панель инструментов Случайные числа. Свяжите созданный макрос с кнопкой Разыграть;

4) запустите программу ещё раз – появится сообщение об ошибке и возникнет останов в команде, при выполнении которой произошла ошибка (Рис. 2-2);

Объекты Application, Workbook, Worksheet и Range. Встроенные функции Dir, MkDir, MsgBox, InputBox - №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. Протестируйте работу программы в различных режимах.