Новые возможности Visual Basic of Application в Excel
Сводные таблицы постоянно развивались. Они появились в Excel 5 и были усовершенствованы в Excel 97. В Excel 2000 принцип создания сводных таблиц с помощью VBA был сильно изменен. Некоторые новые параметры были добавлены и в Excel 2002. В Excel 2007 были добавлены новые свойства, например PivotFilters и TableStyles2. В Excel2010 появились срезы и новые параметры в раскрывающемся списке Дополнительные вычисления (Show Values As).
Поэтому при написании кода для управления сводными таблицами в Excel 2010 нужно проявлять осторожность, чтобы этот код можно было выполнять в Excel 2007 и в устаревших версиях Excel.
Большинство примеров кода, используемых в книге, обладают обратной совместимостью с Excel 2000. Для создания сводных таблиц в Excel 2007 используется мастер сводных таблиц (PivorTableWizard). Хотя в разделе отсутствуют примеры кода для Excel 97, вы найдете исключение из этого правила — макрос PivotExcel97Compatible.
Новые возможности VBA в Excel 2010
В сводных таблицах Excel 2010 появилось много новых возможностей. И если при создании кода VBA воспользоваться хотя бы одной из этих них, ваш код гарантированно не будет выполняться в более ранних версиях Excel. В таблице ниже перечислены элементы, которые появились в VBA для Excel 2010. Не используйте эти элементы в коде VBA, если хотите обеспечить его совместимость с Excel 2007 и более ранними версиями Excel.
| Возможность | Свойства и методы |
|---|---|
| Срезы | Все, что включает слово «Slicer» (срез), не будет работать в Excel 2007. В данном случае идет речь о свойствах SlicerCaches, Slicers и Slicerltems |
| Обратная запись | В Excel 2010 поддерживается средство обратной записи при работе с наборами данных OLAP. Свойства: AllocateChanges, Allocation, Allocation Method,AllocationValue,AllocationWeightExpression, ChangeList и EnableWriteback. Методы: AllocateChanges,CommitChanges, DiscardChanges и Ref reshDataSourceValues. Объекты: PivotTableChangeList, PivotCell.AllocateChange, PivotCell.CellChanged, PivotCell-DataSourceValue, PivotCell.DiscardChange и PivotCell.MDX |
| Повторение подписей | В Excel 2010 пустые ячейки заполняются значениями, находящимися во внешних полях строк. Код VBA, вкл ючающий метод uses-RepeatAllLabels либо свойство RepeacLabels, будет неработоспособным в Excel 2007 |
| Наборы | Именованные наборы могут применяться только при работе со сводными таблицами OLAP в Excel 2010. Перечисленные ниже элементы нельзя использовать в предыдущих версиях Excel: AlternativeText, CalculatedMembersInFilters. DisplayContextTooltips, Show ValuesRow, Summary и VisualTotalsForSets |
| Дополнительные вычисления | Ниже перечислены свойства объекта хlPivotFieldCalculation, которые появились в Excel 2010: xlPercentOfParentColumn, xlPercentOfParentRow, xlPercentTunningTotal, xlRankAscending и xlRankDesсending |
Новые возможности, которые появились в Excel 2007
Несмотря на то что базовые концепции построения сводных таблиц в Excel 2007 остались такими же, как и в Excel 2003, в Excel 2007 все же появились несколько новых возможностей. К ним относятся новые функции на контекстной вкладке Конструктор (Design), а именно: функция добавления промежуточных итогов, параметры макета отчета, пустые строки и новые стили сводных таблиц. В Excel 2007 также существенно усовершенствованы фильтры (по сравнению с устаревшими версиями программы). Если планируете распространять сводные таблицы среди пользователей устаревших версий Excel, избегайте применения любых новых методов. Лучше всего в таком случае открывать рабочую книгу Excel 2003 в режиме совместимости и записывать макросы в нем. Это пожелание касается всех программ, в том числе и nero для windows 8.
Если же макросы будут использоваться исключительно в Excel 2007 либо Excel 2010, то все новые средства можно применять без особых ограничений. В таблице ниже показаны методы, добавленные в Excel 2007. При записи макросов, в которых применяются эти методы, вы не сможете выполнить их в устаревших версиях Excel.
| Метод | Описание |
|---|---|
| ClearAllFilters | Очищает сводную таблицу от действия всех фильтров |
| ClearTable | Удаляет из сводной таблицы все поля, но оставляет ее активной |
| ConvertToFormulas | Преобразует сводную таблицу в формулы куба данных. Этот метод применяется только по отношению к сводным таблицам, основанным на базах данных OLAP |
| DisplayAllMembersPropertiesInTooltip | Эквивалентен опции Показывать свойства во всплывающих подсказках (Show Properties in ToolTips) вкладки Вывод (Display) диалогового окна Параметры сводной таблицы (PivotTable Options) в Excel 2003 |
| RowAxisLayout | Изменяет макет для всех полей в области строк. Принимает значение xlCompactRow, xlTabularRow или xlOutlineRow |
| SubtotalLocation | Указывает, будут ли промежуточные итоги отображаться в верхней или нижней части группы данных. Принимает аргументы xlAtTop и xlAtBottom |
В следующей таблице перечислены новые свойства, которые появились в Excel 2007. Если созданные вами макросы включают эти свойства, вы не сможете выполнять их в устаревших версиях Excel.
| Свойство | Описание |
|---|---|
| ShowTableStyleColumnHeaders | Определяет, будет ли стиль 2 изменять заголовки столбцов |
| ShowTableStyleColumnStripes | Определяет, будет ли стиль 2 изменять столбцы поочередно |
| ShowTableStyleLastColumn | Определяет, будет ли стиль 2 изменять последний столбец |
| ShowTableStyleRowHeaders | Определяет, будет ли стиль 2 изменять заголовки строк |
| ShowTableStyleRowStripes | Определяет, будет ли стиль 2 изменять строки поочередно |
| SortUsingCustomLists | Указывает на сортировку элементов полей согласно пользовательским спискам, как исходно, так и в процессе применения. Значение False этого свойства позволяет оптимизировать сортировку данных, но предотвращает использование пользовательских списков |
| TableStyle2 | Определяет стиль, в текущий момент примененный к сводной таблице. В устаревших версиях Excel можно было воспользоваться лишь столь малофункциональным средством, как Автоформат (AutoFormat). Это средство соответствовало свойству TableStyle, поэтому стилями сводных таблиц стали управлять с помощью нового свойства TableStyle2. Оно принимает такие значения, как PivotStyleLightl7 |