4

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.
QHarr
  • 83,427
  • 12
  • 54
  • 101
Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29

2 Answers2

2

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.

Daniel
  • 814
  • 6
  • 12
  • Kudos for the idea, but I don't think it'll fly for us! INDIRECTs are volatile for a reason: it's unsafe to assume that the references to which they refer haven't changed, in general. By replacing INDIRECT with a non-volatile equivalent, some changes to source data might not get picked up. It's a risk we can't take, across the board, though it would probably work for 299,000 out of our 300,000 cells. The holy grail is to be able to effectively tell the INDIRECTs not to calculate whenever we want... but still have them calculate by default. – Colm Bhandal Mar 14 '19 at 18:16
  • 1
    That's a very good point which is why I said it's not ideal! I have another idea ;) You can create a worksheet_change event with cells that affect indirect functions as target area, and whenever they change value, re-calc the spreadsheet. This way it will still take the same amount of time to calculate, but data would be automatically refreshed only when it's needed. With a bit more work you could narrow it down just to affected cells as well to make it more efficient; get address of target cell, find it in indirect equivalent functions and only refresh those affected. – Daniel Mar 15 '19 at 10:12
  • That's not a bad idea at all. I have a similar idea. We could add to the non-volatile UDF an explicit parameter which overestimates the sheet/range upon which is relies. This way, if anything on that sheet/range changes, so does the indirect. It's similar to your idea but doesn't require any event handling. We'll see. For now though, we don't have scope for such a refactor, so I guess we're stuck with slow recalc for a while! Thanks for your inut. – Colm Bhandal Mar 15 '19 at 10:27
  • 1
    I like this, let us know when you make it happen, it's quite interesting and I'd love to know it works and is efficient! – Daniel Mar 15 '19 at 10:34
1

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

Chronocidal
  • 6,827
  • 1
  • 12
  • 26