Рабочий лист Ввод

Рабочий лист Ввод достаточно многофункциональный. Его задачами являются:

• облегчение просмотра базы данных клиентов, потому что вертикально расположенные данные воспринимаются пользователем легче, чем расположенные горизонтально;
• позволяет облегчить, ускорить и уменьшить количество ошибок вводимых в доверенность за счет экспорта данных из базы данных клиентов;
• помещает данные о клиентах в создаваемой доверенности в лист Реквизиты;
• служит своеобразным буфером для разрыва связи между формулами разрабатываемого приложения, за счет чего обеспечивается уменьшение занимаемого объема на жестком диске и быстродействие проведения расчетов.

Создание листа Ввод

Для создания области заголовка, но расположенного вертикально на рабочем листе Реквизиты выделите диапазон ячеек А1: АF4 и скопируйте в буфер обмена. Перейдите на лист Ввод и, выделив ячейку D16, вызовите диалоговое окно Специальная вставка, в котором отметьте опцию Транспонировать, произведите вставку из буфера обмена. Для придания заголовкам внешнего вида потребуется корректировка линий границ ячеек.

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

Формула в ячейке Н30, показанная в строке формул на рис. 7.16., использует для поиска данных функцию ГПР. В качестве искомого значения для поиска используется порядковый номер столбцов, которые были введены на листе Реквизиты в строку 4, и при вставке на лист Ввод находятся в столбце G. Область поиска расположена в области листа Реквизиты ограниченной строками 4: 1000, а в качестве номера строки, по которому выбирается запись, используется увеличенное на единицу значение, введенное в ячейку D9 и управляемое элементом управления Счетчик.

Рис. 7.16. Рабочий лист Ввод

Для последовательного просмотра данных листа Реквизиты можно воспользоваться элементом управления или для просмотра конкретной записи ввести в ячейку D9 порядковый номер записи на листе Реквизиты.

Обновление формул

На практике часты случаи неосторожного удаления введенных ранее формул. Для страховки запишите макрос (рис. 7.17.), вводящий формулы поиска в диапазон Н16: Н47 для чего выполните следующие действия:

• перед записью макроса предварительно введите формулу в ячейку Н16;
• после начала записи макроса выделите диапазон ячеек Н16: Н47 начиная с ячейки Н16;
• нажмите клавишу F2 (переход в режим редактирования активной ячейки), после чего нажмите комбинацию клавиш [Ctrl+Enter];
• остановите запись макроса.

Рис. 7.17. Макросы ОбновитьФормулы, Очистка и СкопироватьПункт

Для создания и последующей вставки формул создайте элемент управления Кнопка Обновить формулы поиска с назначенным ей макросом ОбновитьФормулы.

Элементы автоматизации ввода данных

Очистка области ввода данных

Для ввода данных в доверенность предназначен диапазон ячеек I16: I46. Но прежде чем вводить новые данные желательно произвести очистку этого диапазона. Для этого запишите макрос Очистка (рис. 7.27.). Запись макроса заключается в выделении этого диапазона ячеек, ячейки F13 и последующем выполнении команды Правка/Очистить/Содержимое или нажатии на клавишу Delete.

Для очистки содержимого предназначена кнопка Очистка.

Ввод данных

Данные, как правило, вводятся с клавиатуры, но если клиент приходит не в первый раз, то в базе клиентов имеются его паспортные данные. Поэтому достаточно ввести в ячейку D9 порядковый номер записи совершения операции в базе данных на листе Реквизиты. После этого выделить в столбце Н область ячеек с данными клиента и нажать на кнопку Скопировать пункты.

Этой кнопке назначен макрос СкопироватьПункт (рис. 7.17.). Этот макрос копирует выделенный диапазон ячеек, выделяет ячейку, находящуюся на одну ячейку правее от правой верхней ячейки выделенного диапазона и производит вставку значений, определенных формулами, находящимися в этом диапазоне.

Проверка содержимого двух областей на соответствие

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

В качестве примера рассмотрим соответствие содержащихся данных в диапазонах ячеек Н16: Н47 и I16: I47.

В ячейку J16 введите формулу:

=H16=I16

которая заключается в сравнении полученного результата формулы в ячейке Н16 и текстового содержания, введенного в ячейку I16. Если эти данные идентичны, то формула возвращает значение ИСТИНА, если есть различия, то значение - ЛОЖЬ. Если скопировать эту формулу в ячейки J17: J47 и объединить полученные результаты, заключив их в формулу в ячейке F13:

то при наличии расхождений хоть в одной ячейке диапазона Н16: Н47 от ячеек диапазона I16: I47 формула сформирует запись: Соответствия НЕТ! и в противном случае: Вводимые данные и данные базы данных соответствуют.

Анализ расхождений проводит логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА или возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. А функция ЕСЛИ в зависимости от полученного результата возвращает текст, помещенный во второй или третий аргументы.

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

Механическая запись макроса ПроверкаДанныхВвод производится в следующей последовательности:

• до начала записи формул введите формулы в ячейки J16 и F13;
• произведите запись ввода формул в диапазон J16: J47, затем в ячейку F13, в которой после этого замените формулу на определенной ею значение, произведите окраску в ней шрифта на красный цвет;
• после этого очистите содержимое диапазона J16: J47.

Сравните записанный макрос с макросом, приведенным на рис 17.18. Как видите, отличия существенны, и заключаются в присутствии инструкции Select Case и некотором изменении кода VBA.

Рис. 7.18. Макрос ПроверкаДанныхВвод

Внесенные изменения

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

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

Опишем некоторые детали кода VBA макроса.

В ячейку F13 вводится формула, а затем при помощи строки:

Range(" F13").Value = Range(" F13")

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

Вторая деталь - это строка:

Range(" J16: J47").ClearContents

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

Инструкция Select Case

Select Case выполняет одну из нескольких групп инструкций в зависимости от значения выражения.

Инструкция - это синтаксически завершенная конструкция, представляющая отдельное действие, описание или определение, и, как правило, занимает одну строку.

Выражение - это комбинация ключевых слов, операторов, переменных и констант, результатом которой является строка, число или объект.

Синтаксис инструкции Select Case содержит следующие элементы

Select Case выражение
[Case списокВыражений-n
[инструкции-n]]...
[Case Else
[инструкции_else]]
End Select

выражение - обязательный аргумент. Это любое числовое выражение или строковое выражение.

списокВыражений-n - обязательный аргумент при наличии предложения Case. Это список с разделителями, состоящий из одной или нескольких форм следующего вида: выражение, выражение To выражение, Is операторСравнения выражение. Ключевое слово To задает диапазон значений. При использовании ключевого слова To перед ним должно находиться меньшее значение. Ключевое слово Is с операторами сравнения задает диапазон значений. Если ключевое слово Is не указано, оно вставляется по умолчанию.

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

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

Удобство использования инструкции Select Case заключается в возможности выбора одного или нескольких возможных вариантов из всех предложенных. В нашем примере в зависимости от результата, полученного в ячейке F13, происходит изменение шрифта, которым пишется этот текст. Либо голубой, либо красный.

Рассмотрим выполнение Select Case на нашем примере:

Перенос данных из листа Ввод в лист Реквизиты

Следующая задача - перенести введенные данные диапазона ячеек I16: I47 листа Ввод в лист Реквизиты. Эту задачу выполняет макрос ПоместитьДанныеРеквизиты (рис. 7.19.).

Макрос выполняет следующие действия:

• копирование диапазона ячеек I16: I47 на листе Ввод;
• переходит на лист Реквизиты;
• производит поиск первой пустой строки, выделяет в ней ячейку в столбце В и производит транспонированную вставку значений;
• переходит на одну ячейку влево, определяет максимальный порядковый номер в столбце А, добавляет к нему единицу и вводит это значение в ячейку;
• возвращается на лист Ввод.

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

Рис. 7.19. Макрос переноса данных из листа Ввод в лист Реквизиты

Перенос данных из листа Ввод в лист Текст

Макрос ПереносДанныхТекст производит копирование диапазона ячеек I16: I47 на листе Ввод, переходит на лист Текст и производит вставку скопированного в диапазон ВВ1: ВВ32.

Рис. 7.20. Макрос переноса данных из листа Ввод в лист Текст