Программирование сценариев
Рассмотрим теперь объекты, обеспечивающие программное создание сценариев и дальнейшую работу с ними. Вот процедура, решающая все эти задачи:
Sub ДобавитьСценарии() 'Создание трех сценариев Dim mys As Worksheet Dim Scen As Scenario, Scens As Scenarios Set mys = ThisWorkbook.Worksheets("Лист6") With mys Set Scens = .Scenarios If .Scenarios.Count > 0 Then 'Удаление сценариев For Each Scen In Scens Scen.Delete Next Scen End If With Scens .Add Name:="Минимальный тираж", ChangingCells:=Range("J37:L37"), _ Values:=Array("5000", "0", "1,5") .Add Name:="Максимальный тираж", ChangingCells:=Range("J37:L37"), _ Values:=Array("30000", "5", "2,5") .Add Name:="Нормальный тираж", ChangingCells:=Range("J37:L37"), _ Values:=Array("10000", "2", "2") 'Запуск сценариев на выполнение For Each Scen In Scens Scen.Show Next Scen 'Построение отчета '.CreateSummary ReportType:=xlStandardSummary, _ ' ResultCells:=Range("H49,D49,B49") .CreateSummary ReportType:=xlSummaryPivotTable, _ ResultCells:=Range("H49,D49,B49")
End With End With End Sub
Рассмотрим на этом примере основные объекты и методы, связанные с применением сценариев. Рабочие листы (объекты Sheet) включают в свой состав коллекцию Scenarios. Новые элементы в эту коллекцию добавляются, как чаще всего бывает, методом Add. Параметр Name задает имя сценария, ChangingCells - изменяемые ячейки. Обычно эти ячейки располагают подряд, чтобы можно было их указать одним смежным интервалом, но делать так не обязательно - объект Range может задавать и несмежные интервалы. В параметре Comment указывается дополнительная информация, по умолчанию задается автор сценария. Эти данные выводятся в итоговом отчете. Остальные два параметра задают возможность скрытия сценария и его защиты от несанкционированного доступа, - не всегда и не всем требуется объяснять принятое решение.
Совсем просто запустить сценарий на выполнение, удалить или изменить. Для этого у объектов Scenario есть методы Show, Delete, ChangeScenario. В нашем примере методом Show все три сценария поочередно запускаются на выполнение. Чтобы процедура работала в случаях ее многократного запуска, то сценарии на рабочем листе предварительно удаляются, для чего используется метод Delete.
Для создания отчета и подведения итогов используется метод CreateSummary. У параметра ReportType, задающего тип отчета, возможны значения xlStandartSummary и xlSummaryPivotTable. С первым отчетом, принимаемым по умолчанию, Вы знакомы, во втором случае отчет определяет сводную таблицу. Параметр ResultCells позволяет задать результирующие ячейки. В отличие от работы вручную я включил в итоговый отчет сведения о трех параметрах: доходе, периоде продажи и количестве проданных книг. Объект Range здесь задает три несмежные ячейки. В процедуру включены два вызова метода CreateSummary, каждый из которых создает свой тип отчета. Один из вызовов, естественно, закомментирован. Поскольку стандартный тип отчета уже приведен, то взгляните, как выглядит сводная таблица, построенная в результате выполнения этой процедуры:
Рис. 8.36. Сводная таблица, программно построенная по результатам выполнения сценариев
