Макросы занятие 9 (на 8-12 часов). Заработка интерфейса пользователя.

Автофирма. Форма пользователя. Диалог с пользователем.

Для неопытного пользователя работа с данными может оказаться трудной задачей. Например, выбор транспортного средства осуществляется не по названию, а по грузоподъемности. Любое несоответствие приведет к неверным расчетам. Вставить новый груз – означает не только внести характеристики груза, но и корректно копировать расчетные формулы. Для начала структурно преобразуем данные. Вырезая в память таблицы с перечнем грузов и характеристиками транспортных средств, разместим их на отдельных листах файла “Avtofirm”, соответственно «Грузы» и «Транспорт». Коррекцию макроса оптимального выбора изменить и проверить самостоятельно.

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

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

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

В окне редактора VBA через пункт меню Insert вставим объект UserForm, который представляет собой заготовку автономного окна, подобного любому в среде Windows. Это Ваша строительная площадка, которую Вы «начиняете» элементами управления и логикой обработки как событий, так и данных. Вызов формы осуществляется при помощи макроса или кода в кнопке с инструкциями:

Load коррекция

коррекция.Show

На рисунке представлена форма для выполнения операций с данными по автофирме. В центре рисунка расположено окно инструментов с элементами управления, из которых нами использованы TextBox – текстовое окно для ввода информации, ComboBox – поле со списком для выбора фиксированных значений, CommandButton – командная кнопка. Кроме названных удобно использовать элемент Label («Метка») для пояснений вводимой в текстовое окно информации. Слева на рисунке отображены два окна – сверху окно проекта (project) и снизу окно свойств (properties) текущего (выделенного) объекта. В окне проекта отмечен объект форма с именем (name) «коррекция», в окне свойств – свойства объекта внутри формы с именем «груз». Это очень важный момент. Элементам формы и самой форме желательно присвоить имена, с тем, чтобы в программном коде было меньше путаницы, так как в дальнейшем будет проще к ним обращаться («зряче»). Переход к программному коду конкретного объекта можно осуществлять по двойному щелчку мыши или через контекстное меню (правая кнопка мыши на выделенном элементе – View kod). К примеру, по двойному щелчку в форме Вы попадаете в подпрограмму (на слэнге - программная «заглушка»):

Private Sub UserForm_Click()

End Sub

Аналогично относительно объекта «груз» (текстовое окно):

Private Sub груз_Change()

End Sub

Мы видим, что обработка событий по умолчанию предлагается разная. В первом случае «Щелчок», во втором – «Изменение». Оперировать объектами и событиями проще в окне кода через использование раскрывающегося списка. Слева расположен список всех объектов формы, справа – набор обработки событий выбранного объекта.

Предположим, эскиз формы с необходимыми элементами создан. Как их наполнить значениями и в какой момент? Для этого исследуем события формы. Среди прочих обнаружится Initialize – инициализация, момент «прописки» формы в памяти компьютера.

Private Sub UserForm_Initialize ()

End Sub

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

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

Алгоритм может выглядеть так:

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

2. Запомнить номер строки активной ячейки

3. Наполнять соответствующие текстовые окна формы содержимым ячеек выбранной строки

Последняя операция проста. Предположим мы сохранили номер строки в переменной noms = ActiveCell.Row, тогда груз=Cells(noms, 1), где груз – имя текстового окна в форме. Аналогично заполняются все остальные текстовые окна, где будут изменяться их имена и, соответственно, помещаться данные из последующих колонок.

Но как быть с транспортными средствами? На листе «Грузы» внесена грузоподъемность конкретного тр.средства. Пользователю в форме необходимо предоставить возможность выбора в форме транспортного средства по названию, а вносить на лист его грузоподъемность.

Алгоритм наполнения элемента поле со списком может выглядеть так:

1. Выделить лист с перечнем тр.средств

2. объявить цикл по условию: пока не пуста текущая ячейка (начиная со 2 строки 1 колонка) с заполнением очередного значения тр.средства.

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

Фрагмент текста программы может выглядеть так:

I=2 ‘ Счетчик строк на 2 строку – первое название тр.средства

While Cells(i, 1)< > ”” ‘Пока не пуста ячейка – цикл по условию

ComboBox1.AddItem Cells(i, 1) ‘ Добавить содержимое в поле со списком

i=i+1

Wend

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

Шаг 1. В самой кнопке мы возвращаем на лист измененные значения текстовых окон формы. То есть зеркально наоборот сделать то, что делалось при наполнении формы:

Cells(noms, 1)=груз.Value

Cells(noms, 2)=Val(габарит.Value)

Cells(noms, 3)= Val(вес_ед.Value)

Cells(noms, 4)= Val(вес_общ.Value)

Cells(noms, 5)= Val(дальн.Value)

Cells(noms, 6)= Val(рейсов.Value)

Cells(noms, 8)= Val(стоим.Value)

Вопрос читателю. Зачем добавлен оператор Val в обработке большинства текстовых окон?

Шаг 2. В поле со списком хранятся названия тр.средств, нам же необходимо возвращать грузоподъемность. Решение – в свойствах элемента ComboBox. По умолчанию этот элемент возвращает Value, т.е. в нашем случае – наименование тр.средства. Но у него есть свойство ListIndex – порядковый номер выбора, который назначался по мере наполнения ComboBox. Начинается этот порядок с нуля. Таким образом для нашего примера индексы присвоены следующие:

Мерседес - 0

Уаз - 1

Камаз - 2

Отсюда прямая зависимость индекса с номером строки для выбранного тр.средства на листе «Транспорт», т.е. индекс+2. Если известна строка, то из 5 колонки мы извлечем грузоподъемность. Отсюда получим окончательный текст кода кнопки «ok», в который добавлен вызов подпрограммы оптимального выбора грузов к перевозке, рассмотренный в занятии 8:

Private Sub ok_Click ()

Sheets(“Транспорт”).Select

Ns = ComboBox1.ListIndex+2

Gr = Cells(ns, 5)

Sheets(“Грузы”).Select

Cells(noms, 1)=груз.Value

Cells(noms, 2)=Val(габарит.Value)

Cells(noms, 3)= Val(вес_ед.Value)

Cells(noms, 4)= Val(вес_общ.Value)

Cells(noms, 5)= Val(дальн.Value)

Cells(noms, 6)= Val(рейсов.Value)

Cells(noms, 7)= Gr

Cells(noms, 8)= Val(стоим.Value)

Unload Коррекция

Call выбор

End Sub

В кнопке «отмена» на текущий момент размещаем инструкцию только закрытия формы:

Private Sub отмена_Click ()

Unload Коррекция

End Sub

Этап 2. Добавление функции вставки нового груза через диалог с пользователем и контроль

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

Далее необходимо организовать диалог о том, какую из операций (коррекция или вставка) выбирает пользователь. Определиться в какой момент и где разместить программный код этого диалога. Есть два варианта. По первому – диалог разместить в коде инициализации формы, по второму в макросе вызова формы.

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

Алгоритм может быть таким:

1. Добавить в код инициализации формы диалоговое окно сообщений с присвоением переменной результата выбора (кнопка «Да» - коррекция, «Нет» - новый груз)

2. Обработать переменную диалога оператором условия.

3. Если переменная равна 7 (ответ – нет), добавить строки кода вставки новой строки с копированием формул.

4. Иначе (переменная равна 6, ответ – да)

4.1. Запомнить номер строки активной ячейки.

Остальные действия могут быть повторены.

Этап 3. Добавление функции определения рекомендуемой цены для груза не принятого к перевозке.

Алгоритм может выглядеть так:

1. Определить переменной прибыльность груза, последнего принятого к перевозке (2510400). Признак выбора 1 в 20 колонке. Цикл до последней 1.

2. Временно поставить 1 в колонке 20 в строке корректируемого груза для контроля изменений прибыльности текущего груза.

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

4. Вывести в окно сообщений рекомендованную цену и вернуть прежнее значение цены в колонку 8 текущей строки.