VBA Precedence Problem

[Mike King]

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

 

[keepITcool]

create the module with function
then set cells' formulas using that function.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mike King wrote :

 

[JE McGimpsey]

I get the same result in XL03 and XL04.

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

Mike,

Interestingly, if you enter the formula =A1 into cell B1 after the formula
in cell C1 has been entered, then the calculations proceed as you would
expect.

HTH,
Bernie
MS Excel MVP

 

[Mike King]

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.

 

[Mike King]

I get the same result with XL2K and XL97.

I get the same result in XL03 and XL04.

 

[Bernie Deitrick]

You're welcome.

Excel does "look inside", but not when building its calc tree.

HTH,
Bernie
MS Excel MVP

 

[Niek Otten]

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

It certainly does! That's why we so often have to tell posters to include
all input for UDFs in the argument list.

Nevertheless, in this case something goes wrong obviously. I still can't see
where.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[keepITcool]

seeing the reactions of others I looked
into this with a bit more care:

Looks like most of this is due to using the TEXT property.

your original function not only depends on the value in b2,
it also depends on it's numberformat.

check what happens:
format b1 as "0,00"
no enter in a1
1 0
2 1,00
3 2,00

it's always 1 step behind..

Making it volatile does not help.
Setting iteration doesnot help either.


it looks like the updating sequence as follows
the values in the worksheet are updated.
the text in all cells are updated using
numberformats and other formatting.

since a change in TEXT does not trigger
a calculation event c1 is not aware it needs another recalc.


setup the workbook as described
insert module and add function BUT USE THE VALUE PROPERTY
Function myfunc(r As Range)
myfunc = r.Value
End Function


If VBE Tools Options
General tab :
IF compile on demand is UNCHECKED then you must manually compile.

enter z in a1. >> z in c1



hope this clarified what happens..




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mike King wrote :

 

[keepITcool]

Niek,

I think I found it.
see my other post

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Niek Otten wrote :

 

[JE McGimpsey]

seeing the reactions of others I looked
into this with a bit more care:

Looks like most of this is due to using the TEXT property.

That's interesting - good catch.

But it's also interesting that in the original scenario, after
demonstrating the anomaly, modifying the function to

Function MyTest(r As String) As String
myTest = r
End Function

causes it to work correctly, and then, going back to the original .Text
formula:

Function MyTest(r As Range) As String
myTest = r.Text
End Function

works correctly too...

So it may be partly due to the Text property, but it also appears to be
due to the order in which the calculation dependency table is created.

it looks like the updating sequence as follows
the values in the worksheet are updated.
the text in all cells are updated using
numberformats and other formatting.

in that case, restoring the .Text property in the macro above should
cause the lagging behavior to return, but it doesn't. I also find it
hard to believe that the Text property is not updated at the same time
as the Value property.

since a change in TEXT does not trigger
a calculation event c1 is not aware it needs another recalc.

Don't think so - if the dependency tree is properly structured, C1
should never be marked "clean" until B1 is calculated and its value and
text properties updated.

That said, I don't have a clue what's going on internally...