Программно контролировать/перехватывать обновление таблицы данных

Фон

У меня есть чрезвычайно большая таблица данных которая занимает до 12 часов для запуска около 1 миллиона входных сценариев на высокопроизводительной 64-битной машине, Сценарии основаны на ряде дискретных моделей Excel, которые затем передаются в финансовую модель для детальных вычислений.

Чтобы улучшить процесс, я хочу проверить и сравнить скорости:

  • Текущий ручной процесс
  • Использование VBA для обновления таблицы данных (с Calculation, ScreenUpdating и т.д.)
  • Запуск VBS для обновления таблицы данных в невидимом экземпляре Excel

Итак, я ищу наилучший подход к программному управлению таблицей данных

Обновление: использование кода в (2) и (3) не принесло пользы при тестировании простого примера с книгой с одной большой таблицей данных

Скорее удивительно, что в VBA для таблиц данных очень мало - возможно, нет - прямая поддержка

Мой текущий поиск знаний и литературы

  • QueryTable BeforeRefresh и AfterRefresh События могут быть добавлены с помощью этого кода модуля класса. Intellisense не предоставляет это в качестве опции для таблиц данных
  • Индивидуальные сводные таблицы и QuertyTables могут быть доступны таким образом ActiveWorkbookk.Sheets(1).QueryTables(1). Не так Таблицы данных
  • Устранение всех остальных Data Tables, а затем запуск RefreshAll было предложено в этом потоке MrExcel в качестве обходного пути.

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

Да, я придерживаюсь Excel:)

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

  • часть хорошо зарекомендовавшего себя процесса, который останется на основе Excel,
  • прошли профессиональную проверку,
  • были оптимизированы и оптимизированы некоторыми опытными разработчиками Excel.

Мне было просто любопытно, есть ли способ настроить процесс, обновив конкретную таблицу данных с кодом, и мои первоначальные результаты теста выше не сделали этого.

Ответы

Ответ 1

Итак, вы ищете лучший подход к программному управлению таблицей данных.

Хорошо, Excel 2013 действительно записывает макрос для меня, когда я вручную создаю таблицу данных, она идет

Selection.Table ColumnInput:=Range("G4")

Подпись

Range.Table(RowInput as Range, ColumnInput as Range) as Boolean

который описан в Range.Table Method. Функция Range.Table(), кажется, всегда возвращает true.

Это единственный способ создать таблицы данных с помощью VBA. Но все равно все таблицы данных.

AFAIK нет класса или объекта для таблиц данных, поэтому нет dt.refresh() или аналогичного метода. И нет набора таблиц данных, которые вы могли бы запросить. Вы должны обновить лист или воссоздать таблицу с помощью Range.Table().

Существует DataTable Interface, но он связан с диаграммами и не имеет ничего общего с Range.Table().

Как вы говорите, вы должны отключить обычных подозреваемых, т.е.

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Постарайтесь иметь как можно меньше формул в вашей книге. Удалите все формулы, не связанные с ячейками, на которых находится база данных. Удалите промежуточные результаты. Лучше всего иметь одну ячейку с одной, возможно большой, формулой.

Пример: G4 - это ваш ColumnInput, и он содержит = 2 * G3, причем G3 содержит = G1 + G2, то лучше положить = 2 * (G1 + G2) в G4.

У вас может быть 6 ядер в вашей машине высокого класса. Разделите свои сценарии на 6 кусков и имеют 6 экземпляров Excel, которые вычисляют их параллельно.