Array formula does not calculate correctly when run from macro

D

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.
 
D

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.
 
D

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.
 
R

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
 
D

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.
 
D

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.
 
R

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
 
D

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.
 
R

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
 
D

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top