Application Calculate from VBA - Ignore Volatile Functions

Question

Is it possible to efficiently simulate the result of application.calculate from VBA, but ignoring volatile functions?

Detail

Definition: Non-Volatile Equivalent: For any volatile workbook, define the non-volatile equivalent as the workbook whereby all cells with volatile references have been replaced by their raw values.

Definition: Non-Volatile Calculation Diffs: Suppose we have any workbook. Now suppose we took its non-volatile equivalent and did a full recalc on that. Take the cells whose values changed in the non-volatile equivalent, and their values. These are the non-volatile calculation diffs.

The question is asking this. For any workbook, with potential volatile references in it, is there a way to efficiently apply just the non-volatile diffs? Efficiency is key here- it's the whole reason we're looking to ignore volatile functions - see the use case below. So any solution that does not outperform a full recalc is useless.

Use Case

We have a workbook that is rife with INDIRECT usage. INDIRECT is an Excel-native volatile function. There are around 300,000 of these in the workbook, which contains about 1.5 million used cells in total. We do not have capacity to change these. As a result of these INDIRECT usages, our Workbook takes a long time to recalculate. Circa 10 seconds. So we have automatic calculation turned off & users periodically hit manually re-calc to refresh data as they go.

Our business users are fine with that, but some of the new VBA functions we are adding could use something more efficient than a 10 second wait.

What We've Tried so Far

Sheet.Calculate - this is handy in some cases. And we do use it. But it treats the data in all other sheets as values, not formulas. So if there are any zig-zag references this does not provide a fully consistent result. E.g. imagine a reference from sheet A -> sheet B -> sheet A: then one would need to calculate sheet A, then B, then A. The number of zig-zags is arbitrary. And that's just one case, with two sheets. To solve this, one would need to essentially rewrite the entire calculation of Excel.

Upvotes: 4

Answers (2)

Chronocidal

Reputation: 8081

Use a non-volatile User Defined Function that does the same job as 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

Alternatively, depending on the use case, it may be possible to use INDEX and MATCH (non-volatile functions) to replace the INDIRECT queries instead

Upvotes: 1

Daniel

Reputation: 954

There's one thing that comes to my mind and it's not ideal. Perhaps you can make your own function that's not volatile and is stored in the spreadsheet, that would do exactly what INDIRECT does (or whatever formulas you use). You could call it INDIRECT2 or something, and just replace them functions on your spreadsheet.

Unfortunately, this function is not availible for Application.WorksheetFunction.functionName but there are ways around it. This is just my general idea.

Upvotes: 2