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.

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

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

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 Thu, 18 Feb 2010 19:42:13 -0800 (PST), downwitch
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