Новые возможности 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

Top