- •Макросы занятие 1. Макрорекодер и редакторVba.
- •Макросы занятие 2. Переменные, простые функции
- •Типы переменных
- •Макросы занятие 3. Условия, ветвление решения.
- •Макросы занятие 4. Циклы.
- •Макросы занятие 5. Функции для обработки текстов.
- •Макросы занятие 6. Объекты и их свойства.
- •Макросы занятие 7. Монопольные окна диалога.
- •Макросы занятие 8. Автоматизация для пользователя.
- •Макросы занятие 9 (на 8-12 часов). Разработка интерфейса пользователя.
- •Макросы занятие 10. Работа с массивами.
- •Макросы занятие 11. Работа с файлами.
- •Макросы занятие 12. Обязательные условия для выполнения индивидуальных заданий.
Макросы занятие 8. Автоматизация для пользователя.
Знакомство с моделью планирования перевозок, реализованной в файле Avtofirm.xls.
Цель – максимально автоматизировать процесс выбора грузов, выгодных для перевозок при условии соблюдения ограничения по срокам и изменяющегося состава грузов. Достижение поставленной цели распределяется на ряд подзадач.
Произвести определение перечня грузов с дальнейшим выделением произвольной таблицы для сортировки в сторону убывания прибыльности перевозки каждого груза.
Проставить напротив неприбыльных грузов символы, отличные от 1 до тех пор пока затраты дней на перевозки будут меньше 31.
Обеспечить ввод новых данных или коррекцию с возвратом значений из окна формы пользователя в ячейки таблицы.
Предложить рекомендуемые цены для однозначного выбора текущего груза к перевозке.
Реализация 1-ой подзадачи.
Записать макрорекодером макрос который:
Устанавливает курсор в начало таблицы со сведениями о грузах.
По клавиатурной комбинации <Ctrl+<стрелка вниз> перемещается в последнюю строку таблицы.
Проставляет 1 в последней колонке для расчета прибыльности в день
Выделяет всю таблицу и производит сортировку по убыванию прибыльности в день.
Текст данного макроса будет таким:
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 столбца Т таблицы.
Следующим этапом необходимо научить макрос самостоятельно набирать грузы к перевозке.
Путем экспериментов и написания автономных функций приходим к следующим результатам и выводам.
Проще работать с ячейками, как с объектами при помощи обращения к ячейке Cells(номер_строки, номер _колонки).Value.
При обработке множества строк необходимо использовать операторы цикла.
Обращать внимание на объекты, прописанные макрорекодером с целью дальнейшего применения.
Накапливать по крупицам опыт использования операторов и методов.
Итоги исследований и преобразований в виде готового макроса выбора.
Public Sub выбор()
Dimnstr%,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 Назначить переменной номер строки следующей за последней
WhileCells(ndn, 18).value>30 Цикл по условию < 30 дней на перевозки
Cells(nstr, 20).Value=0 Проставить 0 в очередной ячейке (не перевозить)
nstr=nstr-1 Подняться на ячейку выше.
Wend
End Sub
Решить самостоятельно предлагаемые задачи.
Задачи.
Операцию выбора программировать помощью оператора If…Then…[else] end if
Операцию выбора программировать помощью оператора For …….next с шагом -1.