Array formula does not calculate correctly when run from macro

[downwitch]

Hi folks, hope someone can help with this little tear-your-hair-out
number.

I have an array formula that I use to calculate row-by-row totals from
a table range. It looks like this:
=SUM(OFFSET(TableWks,ROW(Wks_Total)-10,0,1,COLUMNS(TableWks)))
where TableWks is a multi-column, multi-row table, and Wks_Total is
the calculating column itself. There is no problem with the array
formula; it does exactly what I want it to do.

Except when I run a VBA procedure (what it does is not important, I
don't think); the result of the array formula comes up the same in
every row, corresponding to the sum of the first column in TableWks,
at procedure's end. A simple tap of the F9 key, however, and it
corrects itself.

I think this corresponds to some sort of array formula/volatility
problem as discussed here http://www.decisionmodels.com/calcsecretsj.htm
but none of the fixes there worked. Indeed, I've tried every fix I can
think of, including:
- not setting calculate to Manual at proc's start or restoring it to
Automatic at proc's end
- liberal use of DoEvents
- liberal use of .Calculate and .EnableCalculation
- various forms of copying/pasting the .FormulaArray of Wks_Total
- various forms of manually setting .FormulaArray at runtime
and none of them work. I have tried this in both Excel 2007 and 2003,
both running on Windows XP.

As I say, simply invoking calculate on the sheet--nothing deeper--
causes the array results to right themselves, as soon as the app state
has returned to user control. But something is keeping this formula
from working right while under VBA control.

Thanks in advance for any thoughts you might have.

 

[joel]

You macro isn't letting the workbook fully update all the calculations
this could be for a number of different reasons. You could be turin
off screen updating (or not trun screen updating) off.


Are you using a UDF? A UDF for the macro to run porperly must have al
the cell used in the function passed as a parameter. A UDF only get
run when a parameter in in the function call get changed.
Here is a simply UDF

Function Myfunction()
Myfunction = Range("A1")
end function


This function will never get run because the workbook only cal
functions when the cells in the parameter list get changed. A functio
should look like this

Function Myfunction(Target)
Myfunction = Target
end function


Then call the function like this
=MyFunction(A1)

 

[downwitch]

No, there are no in-cell UDFs. (This is much of what the link I
pointed to discusses, and as I already stated, nothing discussed there
as a solution has worked for me.)

In 13 years of Excel programming, I have never heard of turning screen
updating off as a cause for calculation incompleteness. But
nevertheless, I have now tried disabling it completely, and can
add .ScreenUpdating to the list of workarounds that don't work around.

 

[downwitch]

One more piece of information that may be useful: Excel 2003 (filed
saved as .xls) and 2007 (as .xlsm) do not actually produce the same
result as the code call ends. In 2007, I receive #N/A errors, whereas
2003 produces the single-column-sum result described in my original
post. A manual calculation call fixes the wks in both versions
however.

I have also now tried deleting and re-adding range names at runtime.
Still no luck.

 

[joel]

Just some more thoughts. Pressing the F9 buton and getting the correct
results means that the calculations didn't complete, not that the
formula is wrong which is what you are saying. so you have to start
thinking why the formula didn't complete. The obvious answers is there
some error or the dependencies chain of events is being broken.


Lets see if there are errors. A common problem when soembody goes from
one version of excel to another on the same PC or even differnt PC is
the break option is different. Try changing the following VBA menu


Tools - Options - General - Error Trapping.


Set the error trapping to "Break on all Errors". Maybe this will give
some clues. I can't tell if your formula is giving valid rows and
column locations. Maybe you are getting an error becuse your row/column
is less than 1, or you column reference is greater than 256.

 

[Ron Rosenfeld]

Except when I run a VBA procedure (what it does is not important, I
don't think);

Since it works when you don't run the VBA procedure, and doesn't work when you
do run it ... it might be a whole lot easier to find your problem if you post
the code.

There are so many possibilities ..
--ron

 

[downwitch]

Ordinarily I would, but this is hundreds of lines of code that serve
essentially to copy some worksheets, pull data from SQL, and do a
little formatting--nothing that directly affects the calculation
itself though. I understand your point, and if I thought it was at all
material, believe me, it would be here.

 

[downwitch]

I agree with you, it's a simple problem of the formula not completing.
I wasn't saying that the formula was wrong, but my various workarounds
have been based on the theory that by "imposing" the formula at
procedure's end that I would force it to complete. Clearly, that's not
working.

Setting the code to break on all errors did not produce any new
results.

 

[Ron Rosenfeld]

Ordinarily I would, but this is hundreds of lines of code that serve
essentially to copy some worksheets, pull data from SQL, and do a
little formatting--nothing that directly affects the calculation
itself though. I understand your point, and if I thought it was at all
material, believe me, it would be here.

Good luck then. I hope someone comes up with an idea for you.
--ron

 

[downwitch]

After exhausting every option in terms of reworking my code and making
sure there was nothing in there that was causing a problem, I decided
to reproduce the error in the simplest possible setup, in hopes of
getting a little (more) help here. You can now see what I see: create
a fresh blank workbook, add a module to it, and paste this code in:

'---BEGIN CODE---
Sub Test_Me1()
Create_Test
Crash_Test
End Sub

Sub Test_Me2()
Create_Test
Crash_Test2
End Sub

Sub Create_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("$B$2").Value = "'2010"
.Range("$C$2").Value = "'2011"
.Range("$D$2").Value = "'2012"
.Range("$E$2").Value = "'2013"
.Range("$G$2").Value = "RowTotal"
.Parent.Names.Add Name:="Sheet1!TableWks", RefersTo:="=Sheet1!
$B$3:$E$11"
.Parent.Names.Add Name:="Sheet1!Wks_Total", RefersTo:="=Sheet1!
$G$3:$G$11"
.Range("Wks_Total").FormulaArray = _

"=SUM(OFFSET(TableWks,ROW(Wks_Total)-3,0,1,COLUMNS(TableWks)))"
End With
Set wks = Nothing
End Sub

Sub Crash_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("TableWks").Value = 0
.Range("$B$4").Value = 31
.Range("$C$5").Value = 12
.Range("$D$3").Value = 9
.Range("$E$5").Value = 15
.Range("$B$6").Value = 121
.Range("$C$6").Value = 19
.Range("$D$7").Value = 6
.Range("$D$8").Value = 222
.Range("$E$9").Value = 43
End With
Set wks = Nothing
End Sub

Sub Crash_Test2()
Dim rng As Excel.Range
Set rng = ThisWorkbook.Worksheets(1).Range("TableWks")
With rng
.ClearContents
.Value = 0
.Cells(2, 1).Value = 31
.Cells(3, 2).Value = 12
.Cells(4, 3).Value = 9
.Cells(5, 3).Value = 15
.Cells(4, 1).Value = 121
.Cells(5, 2).Value = 19
.Cells(6, 3).Value = 6
.Cells(7, 3).Value = 222
.Cells(8, 4).Value = 43
End With
Set rng = Nothing
End Sub
'---END CODE--

Then all you have to do, from the immediate window, is run Test_Me1 or
Test_Me2 (or, if you want, run Create_Test and Crash_Test or
Crash_Test2 separately, if you feel like fiddling in between) to see
the error result I'm getting. Make one manual data change or hit F9
once user control returns--invoke volatility--and you'll see the error
vanish before your very eyes. Note that this occurs without any UDFs
at all, and without altering .ScreenUpdating, .Calculation, etc.

Any help on what is causing this formula to fail would be really,
really appreciated, as I am now into double-digit hours trying to
figure this out. If I don't hear back here I will be (cross-)posting
this to the worksheet functions forum, as it now appears to straddle
VBA and pure Excel.

 

[Ron Rosenfeld]

Then all you have to do, from the immediate window, is run Test_Me1 or
Test_Me2 (or, if you want, run Create_Test and Crash_Test or
Crash_Test2 separately, if you feel like fiddling in between) to see
the error result I'm getting. Make one manual data change or hit F9
once user control returns--invoke volatility--and you'll see the error
vanish before your very eyes. Note that this occurs without any UDFs
at all, and without altering .ScreenUpdating, .Calculation, etc.

Any help on what is causing this formula to fail would be really,
really appreciated, as I am now into double-digit hours trying to
figure this out. If I don't hear back here I will be (cross-)posting
this to the worksheet functions forum, as it now appears to straddle
VBA and pure Excel.

Good news and bad news.

The bad news is that I can't explain why your method doesn't work. It does
compute, as you noted, after making any type of manual change in the worksheet.
But the Evaluate Formula function only returns a single answer (not an array),
so it is hard for me to tease out exactly what is going on with the formula.
Given the results, it does seem to return a vertical array of values, but they
are inaccessible using the INDEX function.

For your example data, I do have a workaround that seems to work. I don't know
whether it will be applicable to your original work or not.

The work around consists of entering a single array formula in each line,
rather than entering the entire Wks_Total as a single array.

======================================================
Sub Create_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("$B$2").Value = "'2010"
.Range("$C$2").Value = "'2011"
.Range("$D$2").Value = "'2012"
.Range("$E$2").Value = "'2013"
.Range("$G$2").Value = "RowTotal"
.Parent.Names.Add Name:= _
"Sheet1!TableWks", RefersTo:="=Sheet1!$B$3:$E$11"
.Parent.Names.Add Name:= _
"Sheet1!Wks_Total", RefersTo:="=Sheet1!$G$3:$G$11"
.Range("Wks_Total")(1, 1).FormulaArray = _
"=SUM(OFFSET(TableWks,ROW()-ROW(Wks_Total),0,1,COLUMNS(TableWks)))"
.Range("Wks_Total").FillDown
End With
Set wks = Nothing
End Sub
==============================

Of course, doing it this way, there is no need for the formula to be an array
formula.

====================================
Sub Create_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("$B$2").Value = "'2010"
.Range("$C$2").Value = "'2011"
.Range("$D$2").Value = "'2012"
.Range("$E$2").Value = "'2013"
.Range("$G$2").Value = "RowTotal"
.Parent.Names.Add Name:= _
"Sheet1!TableWks", RefersTo:="=Sheet1!$B$3:$E$11"
.Parent.Names.Add Name:= _
"Sheet1!Wks_Total", RefersTo:="=Sheet1!$G$3:$G$11"
.Range("Wks_Total")(1, 1).Formula = _
"=SUM(OFFSET(TableWks,ROW()-ROW(Wks_Total),0,1,COLUMNS(TableWks)))"
.Range("Wks_Total").FillDown
End With
Set wks = Nothing
End Sub
==================================

I'm sorry I couldn't be of more help. Perhaps others can explain things
better.
--ron

 

[joel]

I have generated UDF which I called from the worksheet using it like a
array formula. It only worked if I selected on the worksheet the exqac
size of data I wanted returned from the UDF. It didn't behave like
stadnard function which calculates the size of data that was returned b
the UDF and then automatically puts all the data into the worksheet.

 

[Rmorrone]

Hey, I may not be able to help with the solution but may provide some
possibly useful information. In some spreadsheets I have used arrays
(Excel 2003), sometimes one or more of the array formulas do not
correctly update. Then I need to edit it (crtl-shift-enter) and then the
result is correctly displayed. If the same worksheet is used in Excel
2007 then the problem is more serious: most of the array formulas do not
update correctly. Ctrl-shift-enter also solves the issue in Excel 2007.
This may give a hint on where to look for the solution for your problem
(and hopefully for mine also...)




After exhausting every option in terms of reworking my code and making

 

[downwitch]

Thanks for all the feedback everyone.

Ron, yes, there are plenty of row-based workarounds I can use; I was
trying to stick to the simplicity of just the one array formula, and
the fact that it works so straightforwardly for an Excel user unless
that user's name is VBA is maddening.

If I understand what you're saying correctly Joel, yes, I think the
dynamic nature of the array must be where the problem lies. What I
can't believe is that there is no way to force the _exact same_ type
of calculation that takes place when a user makes a simple change via
the UI (and Rmorrone re-entering a formula counts as such a change--I
don't want the user to have to do anything here) to occur through
code. Doesn't sound like I will get an explanation for it here though--
I'll try cross-posting, though I don't have much hope for an answer
from that either.

 

[Ron Rosenfeld]

Thanks for all the feedback everyone.

Ron, yes, there are plenty of row-based workarounds I can use; I was
trying to stick to the simplicity of just the one array formula, and
the fact that it works so straightforwardly for an Excel user unless
that user's name is VBA is maddening.

If I understand what you're saying correctly Joel, yes, I think the
dynamic nature of the array must be where the problem lies. What I
can't believe is that there is no way to force the _exact same_ type
of calculation that takes place when a user makes a simple change via
the UI (and Rmorrone re-entering a formula counts as such a change--I
don't want the user to have to do anything here) to occur through
code. Doesn't sound like I will get an explanation for it here though--
I'll try cross-posting, though I don't have much hope for an answer
from that either.

I sure hope you get an answer as to the "why", and that I see the response.
There are many more knowledgeable than I around here.
--ron