Макросы занятие 8. Автоматизация для пользователя.

Знакомство с моделью планирования перевозок, реализованной в файле Avtofirm.xls.

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

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

2.Проставить напротив неприбыльных грузов символы, отличные от 1 до тех пор пока затраты дней на перевозки будут меньше 31.

3.Обеспечить ввод новых данных или коррекцию с возвратом значений из окна формы пользователя в ячейки таблицы.

4.Предложить рекомендуемые цены для однозначного выбора текущего груза к перевозке.

Реализация 1-ой подзадачи.

Записать макрорекодером макрос который:

1.Устанавливает курсор в начало таблицы со сведениями о грузах.

2.По клавиатурной комбинации < Ctrl+< стрелка вниз> перемещается в последнюю строку таблицы.

3.Проставляет 1 в последней колонке для расчета прибыльности в день

4.Выделяет всю таблицу и производит сортировку по убыванию прибыльности в день.

Текст данного макроса будет таким:

Sheets(" FIRMA").Select Выделение листа FIRMA

Range(" A23").Select Выделение ячейки A23

Selection.End(xlDown).Select < Ctrl+< стрелка вниз>

Range(" T24").Select Выделение ячейки Т24

ActiveCell.FormulaR1C1 = " 1" Ввод 1 в активную ячейку

Selection.Copy Копирование содержимого акт. ячейки

Range(" T25: T31").Select Выделение ячеек Т25: Т31

ActiveSheet.Paste Вставка из памяти копии

Application.CutCopyMode = False Снятие выделения с области копирования

Range(" A23: T31").Select Выделение ячеек А23: Т31 (вся таблица)

Selection.Sort Key1: =Range(" S24"), Order1: =xlDescending, Header: =xlGuess _

, OrderCustom: =1, MatchCase: =False, Orientation: =xlTopToBottom

После выполнения данного макроса пользователю необходимо вручную проставить 0 в последней колонке до тех пор пока количество затраченных дней станет меньше 30 (ячейка R32).

Макрос обладает существенными недостатками – сортируется таблица фиксированных размеров и необходимо проставлять 1 вручную в колонке Т.

Для задачи определения размеров произвольных таблиц выясняем, что за объект ActiveCell (активная ячейка) и свойство объектов Value (содержимое).

У объекта ActiveCell множество свойств, но рассуждая, пробуем использовать свойство Address, после того как оказались в последней ячейке таблицы.

Что возвращает объект с указанным свойством?

Посмотрите раздел методички о переменных. Оператор описания Dim.

Объявляем текстовую переменную и присваиваем ей значение ActiveCell.Address

Как просмотреть это значение? См раздел отладка программ.

Выясняется, что (фрагмент программы):

Range(" A23").Select

Selection.End(xlDown).Select

Txt= ActiveCell.Address

Txt принимает значение “$A$31”. Получается если отбросить первые три символа то можно использовать номер строки в текстовом виде для выделения произвольных диапазонов ячеек. В помощь получаем оператор Mid(текст, старт позиция, [длина]).

В итоге макрос приобретает вид:

Sheets(" FIRMA").Select

Range(" A23").Select

Selection.End(xlDown).Select Перейти к последней заполненной строке таблицы

Txt= ActiveCell.Address Текстовой переменной присвоить адрес ячеки

Txt= Mid(Txt, 4) Извлечь из адреса номер строки (с 4 позиции исходного значения переменной)

Range(" T25: T" +Txt).Value=1 Проставить содержимое ячеек с Т2 до последней строки =1

Range(" A23: T" +Txt).Select Выделить всю таблицу

Selection.Sort Key1: =Range(" S24"), Order1: =xlDescending, Header: =xlGuess _

, OrderCustom: =1, MatchCase: =False, Orientation: =xlTopToBottom

Но теперь макрос работает с любой размерностью таблицы! И определяет размерность по всем заполненным ячейкам 1 столбца Т таблицы.

Следующим этапом необходимо научить макрос самостоятельно набирать грузы к перевозке.

Путем экспериментов и написания автономных функций приходим к следующим результатам и выводам.

1. Проще работать с ячейками, как с объектами при помощи обращения к ячейке Cells(номер_строки, номер _колонки).Value.

2. При обработке множества строк необходимо использовать операторы цикла.

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

4. Накапливать по крупицам опыт использования операторов и методов.

Итоги исследований и преобразований в виде готового макроса выбора.

Public Sub выбор()

Dim nstr%, ndn%, txt$ Объявление переменных

Sheets(" FIRMA").Select Выделение листа FIRMA

Range(" A23").Select

Selection.End(xlDown).Select

nstr= ActiveCell.Row Присвоить переменной номер строки акт.яч.

Txt= trim(str(nstr)) Перевести число в текст.

Range(" T25: T" +Txt).Value=1 Проставить 1 в назначенном диапазоне

Range(" A23: T" +Txt).Select Выделить таблицу диапазона

Selection.Sort Key1: =Range(" S24"), Order1: =xlDescending, Header: =xlGuess _

, OrderCustom: =1, MatchCase: =False, Orientation: =xlTopToBottom

ndn=nstr+1 Назначить переменной номер строки следующей за последней

While Cells(ndn, 18).value > 30 Цикл по условию < 30 дней на перевозки

Cells(nstr, 20).Value=0 Проставить 0 в очередной ячейке (не перевозить)

nstr=nstr-1 Подняться на ячейку выше.

Wend

End Sub