Коррекция блока ячеек таблицы

Часто приходится выполнять одну операцию над группой элементов электронных таблиц. Этими элементами могут быть ячейки, листы в книге или книги в приложении.

Для автоматизации повторяющихся действий, следует записать макрос. Хотя при записи макроса невозможно записать циклы, используйте эту процедуру для создания исходного текста модуля VB. Затем измените полученную таким образом программу, применяя различные циклы в зависимости от выполняемой задачи.

В приведенных ниже примерах в ячейках столбца А содержатся числа и в зависимости от числа, находящегося в ячейке, изменяется цвет соответствующей ячейки столбца B (пример из файла Samples.xls).

Сначала следует записать по шагам процесс изменения цвета заливки для ячейки. В результате будет получен макрос

 

 

Sub Recorded_Macro()

With Selection.Interior

.ColorIndex = 6

.Pattern = xlSolid

End With

End Sub

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

Цикл For Each...Next

Если известен диапазон ячеек, для которого необходимо применить записанный макрос, следует использовать цикл For Each…Next.

В нашем примере цвет в ячейке столбца В изменяется, если соответствующая ячейка столбца А содержит число больше 20. Решение поставленной задачи реализуется с помощью оператора if. В результате программа трансформируется (для перехода к столбцу В используется метод Offset), например, к следующему виду:

Sub rrr()

For Each cell In Range("A1:A5")

If cell > 20 Then

With cell.Offset(0, 1).Interior ‘Переход к столбцу В

.ColorIndex = 6

.Pattern = xlSolid

End With

End If

Next

End Sub

 

Если задано конкретное число обрабатываемых элементов массива, а область ячеек определена через выделение, можно воспользоваться циклом For..Next. Используйте наш пример, если необходимо проверить только 10 ячеек вниз, начиная с выбранной ячейки (используется объект Selection). После некоторого изменения предыдущего фрагмента программа примет вид:

Sub ggg()

For Counter = 0 To 9

If Selection.Offset(Counter, 0).Value > 20 Then

…

End If

Next

End Sub

Объект Selection использован для того, чтобы программа просматривала 10 ячеек в выделенной области. Переменная Counter возрастает при каждом прохождении через цикл и может использоваться внутри циклической структуры. Здесь она использована как аргумент Offset для определения смещения текущей ячейки от начальной ячейки. Так, если программа была запущена при активной ячейке А1, то при первом вхождении в цикл переменная Counter равна 0, это означает, что смещение от ячейки А1 также равно 0.

Цикл Do…

Если необходимо остановить цикл при определенном условии, следует использовать цикл Do…. Эта циклическая структура позволяет проверить свойства или условия до вхождения в цикл. В приведенном ниже примере вход в цикл разрешается до тех пор, пока номер строки ячеек Selection.Offset(Counter, 0). Row не превысит 100. Это может быть полезно, если необходимо остановить выполнение цикла ниже сотой строки.

Sub Do_Loop_Sample()

Counter = 0

Do Until Selection.Offset(Counter, 0).Row > 100

If Selection.Offset(Counter, 0).Value > 20 Then

…

End If

Counter = Counter + 1

Loop

Ввод и вывод данных

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

Чтение и запись последовательных файлов

Последовательный файл является наиболее общим типом файлов. Любой файл, который считывается с внешнего носителя в память ЭВМ целиком, должен быть последовательным. Большинство неформатированных текстовых файлов являются последовательными - также, как и большинство файлов программ. В VB текстовые файлы также представляют собой файлы последовательного доступа. Данные в этих файлах записаны в форме строк символов ANSI. Если вы откроете последовательный файл в текстовом процессоре, то сможете убедиться, что этот файл пригоден для чтения.

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

Прежде чем VB сможет что-либо сделать с дисковым файлом, вы должны открыть этот файл и подключить к нему файловое число. Файловые числа - это небольшие целые числа, которые связываются с файлом при его открытии. Команды чтения и записи используют файловые числа для указания файла, в который надо писать или из которого надо читать. Для открытия последовательного файла используют оператор Open, имеющий следующий синтаксис:

Open имя_файла For режим As файловое_число

Аргумент " имя_файла " является строкой, содержащей имя и путь файла, который вы хотите открыть. Если файл находится в текущей директории, то достаточно указать только его имя, иначе для него необходимо указывать полный путь. Для того чтобы файл, подлежащий открытию, выбирал пользователь, обычно прибегают к функции GetOpenFilename(), отображающей стандартное диалоговое окно открытия файла.

Аргумент " режим" определяет тип файла, который вы хотите открыть, и способ открытия файла. Режим должен принимать одно из литеральных значений: Input (ввод данных), Output (вывод данных), Append (добавление последовательного файла) или Random (добавление файла произвольного доступа).

Аргумент " файловое_число " определяет число, которое вы хотите присвоить файлу. Если файл закрывается, то освободившееся файловое число может быть использовано повторно; но не пытайтесь открыть файл, используя файловое число, которое уже присвоено открытому файлу. Часто для определения файлового числа используется функция FreeFile. Затем эта переменная используется в качестве аргумента оператора Open, а также операторов чтения и записи файла, например :

N1=FreeFile

Open "myfile.dat" For Input As N1

Закрытие файла осуществляется командой " close файловое число" или просто " close ", если необходимо закрыть все открытые файлы.

Диалоговые функции

Современный стиль программирования ориентирован на дружеский интерфейс, позволяющий уточнить процесс обработки данных. В профессионально разработанных программах пользовательский интерфейс занимает около 90% объема кода и только 10% используется для обработки данных. Наряду со стандартными приемами передачи данных (через таблицы, файлы и др.) здесь важное место отводится диалогу.

MsgBox()

Для передачи пользователю некоторого текстового сообщения и получения простых ответов типа "Да" - "Нет" используется функция MsgBox(). Эта функция выводит на экран диалоговое окно, содержащее сообщение и группу кнопок, устанавливает режим ожидания нажатия кнопки пользователем, а затем возвращает значение целого типа, указывающее, какая кнопка была нажата. В простом представлении (без справки) функция имеет следующий синтаксис: MsgBox(prompt[, buttons] [, title]).

Таблица 4.4

Константа	Значение	Описание
Группа 1. Число и тип кнопок
VbOKOnly		Отображается только кнопка "OK".
VbOKCancel		Отображаются кнопки "OK" и "Отмена" (Cancel).
VbAbortRetryIgnore		Отображаются кнопки "Прервать" (Abort), "Повторить" (Retry) и "Пропустить" (Ignore).
VbYesNoCancel		Отображаются кнопки "Да", "Нет" и "Отмена" (Cancel).
VbYesNo		Отображаются кнопки "Да" (Yes) и "Нет" (No).
VbRetryCancel		Отображаются кнопки "Повторить" (Retry) и "Отмена"
Группа 2. Тип значка
VbCritical		Используется значок "Критическое сообщение".
VbQuestion		Используется значок "Предупреждающий запрос".
VbExclamation		Используется значок "Предупреждение".
VbInformation		Используется значок "Информационное сообщение".
Группа 3. Основная кнопка
VbDefaultButton1		Основной является первая кнопка.
VbDefaultButton2		Основной является вторая кнопка.
VbDefaultButton3		Основной является третья кнопка.
VbDefaultButton4		Основной является четвертая кнопка.
Группа 4. Модальность окна ()МО) сообщения
VbApplicationModal		МО на уровне приложения: для работы с текущим приложением, необходимо ответить на сообщение.
VbSystemModal		МО на уровне системы: приложения недоступны до тех пор, пока пользователь не ответит на данное сообщение.

Параметры

· Prompt ( обязательный параметр ) - строковое выражение, отображаемое как сообщение в диалоговом окне. Максимальная длина текста составляет приблизительно 1024 символов и зависит от ширины используемых символов. Текст может состоять из нескольких физических строк.

· buttons (кнопки) - числовой код, позволяющий определить совокупность кнопок и их статус. По умолчанию buttons =0 (одна кнопка "ОК"). В табл. 4.4 перечислены допустимые значения этого аргумента. При определении комплексного значения аргумента buttons следует суммировать значения кнопок, но не более одного из каждой группы.

· title - заголовок диалогового окна. Если этот аргумент опущен, в строку заголовка помещается имя приложения.

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

Возвращаемые функцией значения приведены в табл. 4.5.

Дополнительные сведения. Если окно диалога содержит кнопку "Отмена" (Cancel), нажатие клавиши ESC эквивалентно нажатию этой кнопки.

Пример

Программный модуль	Комментарий
Public Sub cc()
Dim dd As Integer, ddd As Integer
dd = MsgBox(Title:="Веселый диалог", _	"_" - Перенос оператора
Prompt:="Привет старина! Хочешь поговорить?", _	Подстановка параметров
Buttons:=4)	- в любом порядке
Select Case dd	Пример использования
Case vbYes	оператора Select Case
ddd = MsgBox(prompt:="Я рад, но говорить пока не о чем", _	Нажата кнопка "Да"
Title:="Веселый диалог")
Case vbNo
ddd = MsgBox(prompt:="Не хочешь? Ну, как хочешь!",	Нажата кнопка "Нет"
_ Title:="Веселый диалог")
End Select
End Sub

Рис. 4.4. Веселый диалог

Результаты диалога представлены на рис. 4.4. Первая панель - результат первого вызова функции. Появление второй или третьей панели зависит от того, какая кнопка нажата - "Да" или "Нет".

Таблица 4.5

Константа	Значение	Нажатая кнопка
VbCancel		Отмена (Cancel)
VbAbort		Прервать (Abort)
VbRetry		Повторить (Retry)
VbIgnore		Пропустить (Ignore)
VbYes		Да (Yes)
VbNo		Нет (No)

Самостоятельная работа

· Выполнить отладку представленного модуля.

· Разработать самостоятельно и отладить иной диалог.

Нетрудно сообразить, что диалог с однозначными ответами можно продолжать сколь угодно долго. Как правило, этого недостаточно. Часто нам требуется получить от пользователя информацию в виде текста (хотим узнать его имя или возраст). Это реализуется с помощью функции InputBox().

Функция InputBox()

Функция InputBox() выводит на экран диалоговое окно, содержащее сообщение и поле ввода, устанавливает режим ожидания ввода текста пользователем или нажатия кнопки, а затем возвращает значение типа String, содержащее текст, введенный в поле. Синтаксис в упрощенной записи (без справки):

InputBox(prompt[, title] [, default] [, xpos] [, ypos])

Параметры функции представлены в табл. 4.6.

Таблица 4.6

Параметр	Описание параметра
Prompt	Обязательный параметр. Аналогично функции MsgBox().
Title	Аналогично функции MsgBox().
Default	Строковое выражение, отображаемое в поле ввода как используемое по умолчанию, если пользователь не введет другую строку. Если этот аргумент опущен, поле ввода изображается пустым.
Xpos	Числовое выражение, задающее расстояние по горизонтали между левой границей диалогового окна и левым краем экрана. Если этот аргумент опущен, диалоговое окно выравнивается по центру экрана по горизонтали.
ypos	Расстояние по вертикали между верхней границей диалогового окна и верхним краем экрана. Если этот аргумент опущен, диалоговое окно помещается по вертикали примерно на одну треть высоты экрана.

Примечание

· Если пользователь нажимает клавишу " OK " или " ENTER ", функция InputBox () возвращает содержимое поля ввода. Если пользователь нажимает кнопку "Отмена", функция возвратит пустую строку ("").

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

Public Sub vv()

aa = InputBox(Prompt:="Назовите Ваше имя, пожалуйста", _

Title:="Приветствие", Default:="Семен")

MsgBox ("Доброе утро, " & aa)

End Sub

Рис. 4.5. Приветствие

Самостоятельная работа

· Выполните отладку и апробирование приведенного модуля.

· Модифицируйте модуль, спрашивая у пользователя год рождения и отвечая, сколько ему сегодня лет. Здесь следует заметить, что функция InputBox () возвращает строку, несмотря на то, что вводится число. Поэтому для последующей обработки текст необходимо преобразовать в число (см. строковые функции).

· Добейтесь от пользователя, чтобы возраст не превышал 150 лет.

Ввод названия файла

В приложениях мы часто обращаемся к внешним файлам, где хранятся наши данные. Найти нужный файл для его последующего открытия в режиме диалога может помочь метод GetOpenFilename () объекта Application. Этот метод вызывает знакомый нам по меню " Файл " поисковый диалог и возвращает полное название выбранного файла. В общем случае метод имеет 5 параметров (FileFilter, FilterIndex, Title, ButtonText, MultiSelect), но все они являются необязательными и чаще всего не используются. В то же время в качестве самостоятельной работы вам следует вызвать "справку" и ознакомиться с функциональным назначением параметров.

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

Public Sub сс()

FileName = Application.GetOpenFilename()

MsgBox ("Ваш FileName это " & FileName)

End Sub

 

Самостоятельная работа

· Выполните отладку и апробирование приведенного модуля.

· Модифицируйте модуль, меняя параметры метода.

Поиск названия существующего файла для последующего сохранения в нем некоторой совокупности данных практически ничем не отличается от описанной выше процедуры. Соответствующий метод - GetSaveAsFilename().

· Вызовите "справку" по методу и рассмотрите специфику параметров.

· Модифицируйте модуль с использованием метода GetSaveAsFilename().

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

Диалоговые окна Excel

Последняя работа наводит на размышления - если мы смогли извлечь из Excel два встроенных диалога по открытию и сохранению файлов, то нельзя ли воспользоваться и остальными диалогами, например по форматированию данных в ячейках таблиц? - Нет проблем. Воспользуйтесь набором Dialogs объекта Application. В качестве аргумента набора указывается число- индекс, выбирающее диалоговое окно. Было бы нелепо заставлять нас запоминать, какому числу какой диалог соответствует. Разработчики догадались заменить числа текстовыми константами, имеющими больший смысл. Все константы начинаются конструкцией xlDialog, а далее - в зависимости от цели.

Напомним, что конструирование операторов выполняется по подсказке VB: выбрали из списка объектов Application - получаем подсказку- раскрывающееся меню, где находим набор Dialogs; поставили левую круглую скобку - получаем подсказку - список констант, причем настолько большой, что наверняка удовлетворит все наши потребности.

Пример. В приведенной ниже процедуре в диалоге меняется формат содержимого ячейки A1.

Public Sub xx()

Range("a1”).Select

Application.Dialogs(xlDialogFormatNumber).Show

Application.Dialogs(xlDialogActiveCellFont).Show

End Sub

Самостоятельная работа

· Выполните отладку и апробирование приведенного модуля.

· Поменяйте цвет фона ячейки.