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

Question

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.

Answer 1

If you can't beat 'em, join 'em.

SendKeys "{F9}"

Answer 2

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

Answer 3

Looks like a FormulaArray bug in 2003 and 2007.
A simpler bypass for your formula would be to use Range("a1:A5").Formula instead of formula array since =LARGE(OFFSET($J$1,0,0,ROW()),1) does not need to be an array formula

Answer 4

My recollection is that there is also an Application.CalculateFull method - does that work?

Answer 5

My cells were not refreshing after ranges they depended on were modified either. I also had to hand edit each one to get them to re-calculate.

SOLUTION:

Use fully qualified references within your formulas. e.g. any formulas that look like this YourFunction(G200:H700) should be changed to look like this YourFunction('Your Sheet Name'!G200:H700).

Auto-Refresh now works perfectly for me.