Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
create the module with function then set cells' formulas using that function. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Mike King wrote : 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
I get the same result in XL03 and XL04.
In article om, "keepITcool" wrote: create the module with function then set cells' formulas using that function. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
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 "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
I get the same result with XL2K and XL97.
"JE McGimpsey" wrote in message ... I get the same result in XL03 and XL04. In article om, "keepITcool" wrote: create the module with function then set cells' formulas using that function. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
You're welcome.
Excel does "look inside", but not when building its calc tree. HTH, Bernie MS Excel MVP 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
<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 "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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
seeing the reactions of others I looked into this with a bit more ca 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 : 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
Niek,
I think I found it. see my other post -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Niek Otten wrote : Nevertheless, in this case something goes wrong obviously. I still can't see where. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Precedence Problem
In article om,
"keepITcool" wrote: seeing the reactions of others I looked into this with a bit more ca 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I SET UP A PRECEDENCE DIAGRAM? | Excel Worksheet Functions | |||
Module Precedence? | Excel Discussion (Misc queries) | |||
MAJOR BUG: Excel operator precedence is wrong | Excel Worksheet Functions | |||
Need explanation on operator precedence | Excel Discussion (Misc queries) | |||
With formulas does anyone know the order of precedence BOMDAS | Excel Worksheet Functions |