UDF recalc problem between sheets

[Sandy V]

My UDF "Func1" accepts a range array as one of its arg's
and passes this to "Func2" for intermediate processing,
works fine, except...

If I cause a recalc of udf in a formula on Sheet2 correct
result is returned. However if I switch to Sheet1, cells
with this udf show errors. If I recalc (F9) the errors
revert to correct results. Calculation is automatic and
nothing "volatile" in the wb.

What appears to be happening is when I recalc the udf in
Sheet2, udf formulas on Sheet1 are also recalculated, but
the array arg that relates to the udf on Sheet2 is passed
to "Func2" when the udf's on Sheet1 are being calculated,
hence the errors.

Recalc on Sheet1 corrects, and udf's in formulas on Sheet2
remain correct. I don't think there is a circular issue.

Debug.? .Caller.Address shows udf's in Sheet1 are
calculated before those in Sheet2, even if I initiate the
recalc in Sheet2. Re-ordering the sheets, and/or renaming
in different alphabetical order makes no difference. And
finally, initiating a recalc on any other sheet, even one
without udf's, causes errors in udf's Sheet1 but not in
Sheet2. (Same scenario in XL97 & XL2K)

Hope this is not too convoluted!

TIA for any suggestions
Sandy

 

[Frank Kabel]

Hi Sandy
you may have to reference the parent object of your range. Could you
post the code of your UDF

 

[Charles Williams]

Could be a problem with the UDF not handling uncalculated cells in the
range.
Have a look at http://www.DecisionModels.com/calcsecretsj.htm


regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Sandy V]

Charles,

I had actually looked at your page before posting, first
thing I thought of and thanks. Would not be at all
surprised if it contains the answer and I just can't see
it for looking. IsEmpty does not flag anything re
uncalculated cells, assuming I'm checking correctly. Both
my udf/Func1 and Func2 are error handled.

Frank,

Parent Object, I'll work on this. The input range array
is purely to get the row/col size of a single block of
cells, not necessarily the actual block I'm going to
process. In Func1 I set a new range var of same size but
with the address of Cell(1,1) obtained by other means.
This new range may or may not be on the same sheet as the
udf formula. Then I pass the new range to Func2 which is
where the error occurs.

All computed ranges relating to my udf in Sheet1 are on
same sheet. The udf in Sheet2 processs ranges in different
sheets. But it's the udf in Sheet1 that gets the error,
unless recalc is initiated in Sheet1 in which case no
errors occur anywhere. Maybe I need to be more careful
about the Sheet (ie parent).

I'm not sure that posting the code would help without
seeing it in situ in the wb, which would take an even more
convoluted explanation to describe!

Thank you both,
Sandy

 

[Frank Kabel]

Hi Sandy
if you like mail me your example file:
email: frank[dot]kabel[at]freenet[dot]de

 

[Charles Williams]

well, it does sound like either a calculation sequence problem or that the
UDF is somehow referring to the active sheet when it shouldnt be.
some things it could be:

- you are using Application.evaluate in one of your functions
- you have a multi-area range as input
- you have duplicate global and local names, or multiple local names
- you have a name that starts with !
- INDIRECT?
- you have an unqualified range reference somewhere.

maybe if you post the UDF code and the formula that calls it ...

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Frank Kabel]

Hi
as a follow up:
Sandy sent me her file and the original reason for this was the
creation of a range without a worksheet qualifier. Therefore the range
was always created based on the active sheet.

 

[Sandy V]

Since Frank's last post there have been one or two more
off-line exchanges between us. I'm very grateful to his
help.

A bit more followup:

Frank was absolutely correct about a range not being fully
referenced with the sheet. Charles similarly and also for
another reason (see below). However this was not
something lacking in the code but an inadequate way of
passing details of the range to the function.

The location of the ranges to be processed are identified
as a strings contained in another range of cells which is
an input argument. If the range tb processed is on the
same sheet as the udf formula, it's top left cell was
identified simply as (say) "$A$1". But if on another
sheet as (say) "Sheet!3$A$1".

My udf formula on Sheet1 was only concerned about
processing ranges on same sheet, hence the strings only
included the cell address. My formula in Sheet2 used
ranges from other sheets, so addresses were fully
referenced with Sheet-name ! cell-address.

All worked well until I introduced INDIRECT in cell
formulas (Charles - my eyes popped out when I read
Indirect in your post). This being a Volatile function
would trigger recalc on Sheet1 even if recalc initiated on
another sheet. Now because my range addresses as strings
did not include sheet names in Sheet1, the range became
incorrectly referenced to the Active sheet, and hence
error only in the Sheet1 udf.

Ironically INDIRECT caused a recalc problem which
fortuitously highlighted a bad approach to my input
method, which otherwise could have gone unnoticed for a
long time.

Many thanks again to Frank and Charles.

Sandy

PS Frank alluded to my being a "her" whereas I am in fact
a "him"