Thanks, your "wrapper" function idea worked!!!

Excel has to look inside the parameters because how else would it update the
cells that call user-defined functions.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

Excel doesn't build its dependency tables by looking inside the parameters
passed to User-Defined-Functions.

So, either
1) get into the habit of using Ctrl-Alt-F9 to force a full recalc, or
2) rewrite your function so that there is a "wrapper" function with the
correct dependency. In your case

=IF(B1<"",mytest(B1),"")

would work.

HTH,
Bernie
MS Excel MVP


"Mike King" wrote in message
...
The following steps will show you how to reproduce the problem that I'm
having. In the following test case, I would expect the following to
occur.
When I update the contents of cell A1, Excel should update the cells that
depend on it, so cell B1 should be updated then C1 because C1 depends on
B1.
This is not what happens. C1 gets updated before B1. Can anyone explain
this or offer a workaround?


Create New Workbook
Set B1 to equal "=A1"
Set C1 to equal "=MyTest(B1)"
Insert VBA Module into Workbook
Create a function (see below)
Public Function MyTest(r As Range) As String
MyTest = r.Text
End Function

A1 should be empty
B1 should be 0
C1 should say #NAME?
Type a into A1
B1 should say "a"
*** C1 should say "a" but it doesn't it say "0"



P.S. I'm using Windows XP SP2 with Office XP SP3