Search code examples
excelarray-formulasvba

Excel VBA Calculate Method not update Array Formulas; showing as #N/A

I have a series of array formulas in Excel that key off of each other. These are automatically resized to fit a range of data that is generated via a proprietary Excel add-in.

However, whenever my code rewrites some of the array formulas to the correct new size, the cells all show as #N/A until either you edit another unrelated cell on the sheet, save the sheet, or press F9.

Using code such as Application.Calculate, ActiveSheet.Calculate, etc do not have any effect.

However, using SendKeys "{F9}" does.

As an example, these are two formulas on the sheet:

={IF(LEN(INDEX(A:A, ROW()))>0,ROW(A:A)+2)}

and

={LARGE(OFFSET($J$1,0,0,ROW()),1)}

The first formula works fine after writing it programmatically to a range of cells. It merely tells me the row number of a cell that has text in it.

The second formula does not work after writing it programmatically to a range of cells. It gives me the largest row number that has been previously seen in a list of numbers (which is the output of the first formula). If I press F9, the second formula updates correctly. If I do Application.Calculate in VBA, nothing happens. I've also tried the various other recalculate methods available at the Worksheet level as well, but no luck.

Has anyone encountered something like this before?

edit: The resize code essentially boils down to something like this (stripping out all of the support code that allows me to make more generalized calls to it):

First, I do:

formula = dataSheet.Cells(startRow, startColumn).formula

Then later:

Set DeleteRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(bottomBound, rightBound))
DeleteRange.ClearContents
Set DeleteRange = Nothing

Then later on:

Set resultRange = dataSheet.Range(dataSheet.Cells(startRow, startColumn), dataSheet.Cells(startRow + Height - 1, startColumn + Width - 1))
resultRange.FormulaArray = formula
Set resultRange = Nothing

In a nut shell, I make a copy of the formula, clear the range, then rewrite it.

Solution

  • I have fleshed out my comment above given you have implemented this approach

    using this code

    Dim strFormula As String
strFormula = "=LARGE(OFFSET($J$1,0,0,ROW()),1)"
Range("a1:a5").FormulaArray = strFormula
    • xl03 gives numbers but needs a calc to update the cells properly
    • xl07 gives the "#N/A" (and raises a Calculate in the statusbar)
    • xl10 works fine

    As you point out none of the calculation options including a full dependency tree rebuild work

    using my RAND suggestion above does force the update in xl07

    Dim strFormula As String
strFormula = "=LARGE(OFFSET($J$1,0,0,ROW()),1)+ RAND()*0"
Range("a1:a5").FormulaArray = strFormula

    OFFSET is a volatile function see Voltatile Excel Functions (which includes a file that tests volatility)

    Perhaps Charles Williams can shed some light on this, I will ping him