Вычисление приложений из VBA - игнорировать изменчивые функции

Вопрос

Можно ли эффективно смоделировать результат application.calculate из VBA, но игнорируя изменчивые функции?

Деталь

Определение: энергонезависимый эквивалент. Для любой энергонезависимой книги определите энергонезависимый эквивалент как книгу, в которой все ячейки с изменчивыми ссылками заменены их необработанными значениями.

Определение: энергонезависимые различия в расчетах. Предположим, у нас есть рабочая тетрадь. Теперь предположим, что мы взяли его энергонезависимый эквивалент и полностью пересчитали его. Возьмите ячейки, значения которых изменились в энергонезависимом эквиваленте, и их значения. Это энергонезависимые расчеты.

Вопрос в этом. Есть ли способ эффективно применить только энергонезависимые различия к любой книге с потенциально изменчивыми ссылками? Ключевым моментом здесь является эффективность - именно по этой причине мы стараемся игнорировать изменчивые функции - см. Пример использования ниже. Поэтому любое решение, которое не превосходит полный перерасчет, бесполезно.

Вариант использования

У нас есть рабочая тетрадь, в которой INDIRECT используется. INDIRECT - это нестабильная функция Excel. В книге их около 300 000, что в общей сложности содержит около 1,5 миллиона использованных ячеек. У нас нет возможности их изменить. В результате этих INDIRECT использований наша Рабочая книга требует много времени для перерасчета. Примерно 10 секунд. Таким образом, у нас отключен автоматический расчет, и пользователи периодически нажимают кнопку ручного пересчета, чтобы обновлять данные по мере их поступления.

Наших бизнес-пользователей это устраивает, но некоторые из новых функций VBA, которые мы добавляем, могут использовать что-то более эффективное, чем 10-секундное ожидание.

Что мы пробовали до сих пор

  • Sheet.Calculate - это удобно в некоторых случаях. И мы этим пользуемся. Но он обрабатывает данные на всех других листах как значения, а не формулы. Таким образом, если есть какие-либо зигзагообразные ссылки, это не дает полностью согласованного результата. Например. представьте себе ссылку с листа A -> листа B -> листа A: тогда нужно будет вычислить лист A, затем B, затем A. Число зигзагов произвольно. И это всего лишь один случай с двумя листами. Чтобы решить эту проблему, необходимо существенно переписать весь расчет Excel.

excel excel-formula vba volatile calculation
person Colm Bhandal    schedule 14.03.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Одна вещь, которая приходит мне в голову, не идеальна. Возможно, вы можете создать свою собственную функцию, которая не является изменчивой и хранится в электронной таблице, которая будет делать именно то, что делает INDIRECT (или любые другие формулы, которые вы используете). Вы можете назвать это INDIRECT2 или что-то в этом роде и просто заменить их функциями в своей электронной таблице.

К сожалению, эта функция недоступна для Application.WorksheetFunction.functionName, но есть способы обойти ее. Это всего лишь моя общая идея.

person Daniel    schedule 14.03.2019
comment
Престижность за идею, но я не думаю, что она нам понравится! INDIRECT изменчивы по одной причине: небезопасно предполагать, что ссылки, на которые они ссылаются, в целом не изменились. При замене INDIRECT энергонезависимым эквивалентом некоторые изменения в исходных данных могут не поддерживаться. Это риск, на который мы не можем пойти по всем направлениям, хотя он, вероятно, сработает для 299 000 из наших 300 000 ячеек. Святой Грааль заключается в том, чтобы иметь возможность эффективно указывать КОСВЕННОМУ не вычислять, когда мы хотим ... но при этом они должны вычислять по умолчанию. - person Colm Bhandal; 14.03.2019
comment
Это очень хороший момент, поэтому я сказал, что это не идеально! У меня есть другая идея;) Вы можете создать событие worksheet_change с ячейками, которые влияют на косвенные функции в качестве целевой области, и всякий раз, когда они изменяют значение, пересчитывать электронную таблицу. Таким образом, для расчета по-прежнему потребуется такое же количество времени, но данные будут автоматически обновляться только тогда, когда это необходимо. Приложив немного больше усилий, вы могли бы сузить его до пораженных клеток, чтобы сделать его более эффективным; получить адрес целевой ячейки, найти его в косвенных эквивалентных функциях и обновить только затронутые. - person Daniel; 15.03.2019
comment
Это совсем не плохая идея. У меня похожая идея. Мы могли бы добавить к энергонезависимой UDF явный параметр, который завышает размер листа / диапазона, на который он полагается. Таким образом, если что-либо на этом листе / диапазоне изменяется, то же самое и косвенное. Это похоже на вашу идею, но не требует обработки событий. Посмотрим. Однако на данный момент у нас нет возможности для такого рефакторинга, поэтому я думаю, что на какое-то время мы застряли в медленном пересчете! Спасибо за инут. - person Colm Bhandal; 15.03.2019
comment
Мне это нравится, дайте нам знать, когда это произойдет, это довольно интересно, и я хотел бы знать, что это работает и эффективно! - person Daniel; 15.03.2019

arrow_upward
1
arrow_downward

Использовать энергонезависимую функцию, определяемую пользователем, которая выполняет ту же работу, что и INDIRECT?

Public Function INDIRECT_NV(ByVal ref_text As String, Optional ByVal a1 As Boolean) As Variant
    'Non-volatile INDIRECT function
    'Does not accept R1C1 notation - the optional is purely for quick replacement
    INDIRECT_NV = CVErr(xlErrRef) 'Defaults to an  error message
    On Error Resume Next 'If the next line fails, just output the error
    Set INDIRECT_NV = Range(ref_text) 'Does the work of INDIRECT, but not for R1C1
End Function

В качестве альтернативы, в зависимости от варианта использования, можно использовать INDEX и MATCH (энергонезависимые функции) для замены запросов INDIRECT.

person Chronocidal    schedule 14.03.2019