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.

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.

On Feb 19, 1:03*am, joel <joel.46m...@thecodecage.com> wrote:
> You macro isn't letting the workbook fully update all the calculations.
> *this could be for a number of different reasons. *You could be turing
> off screen updating (or not trun screen updating) off. *
>
> Are you using a UDF? *A UDF for the macro to run porperly must have all
> the cell used in the function passed as a parameter. *A UDF only gets
> 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 call
> functions when the cells in the parameter list get changed. *A function
> should look like this
>
> Function Myfunction(Target)
> Myfunction = Target
> end function
>
> Then call the function like this
> =MyFunction(A1)
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=180681
>
> Microsoft Office Help

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.

On Thu, 18 Feb 2010 19:42:13 -0800 (PST), downwitch <(E-Mail Removed)>
wrote:

>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

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.

On Feb 19, 10:50*am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Thu, 18 Feb 2010 19:42:13 -0800 (PST), downwitch <downwi...@gmail.com>
> wrote:
>
> >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

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.

On Feb 19, 10:38*am, joel <joel.46n...@thecodecage.com> wrote:
> 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.
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=180681
>
> Microsoft Office Help

On Fri, 19 Feb 2010 08:16:46 -0800 (PST), downwitch <(E-Mail Removed)>
wrote:

>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

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.

On Fri, 19 Feb 2010 20:02:35 -0800 (PST), downwitch <(E-Mail Removed)>
wrote:


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

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.