CELL("filename") acting Volatile? (in assoc. with PULL)

[sstackhoTEST]

Hello,
I was using Harlan Grove's PULL function for the first time, and found that it had started
behaving in a Volatile manner, when it shouldn't have.
I was able to hunt down the problem to my formula that builds the reference that PULL uses. As
a step in building the directory of the external workbook, I used "=LEFT(CELL
("filename",A1),FIND("[",CELL("filename",A1))-1)" to return the directory of the current
workbook. This formula is the problem. When I remove it, PULL no longer acts Volatile.
But it was my understanding that CELL("filename") was not volatile. (Sources:
<http://www.decisionmodels.com/calcsecretsi.htm> and
<http://support.microsoft.com/kb/211370/EN-US/>)
Does anyone know why things became volatile in my workbook?
(BTW, I wrote a tiny UDF to return the directory of the current workbook, instead of using the
CELL function, and the workbook works fine now.)
Thanks,
Shawn
--

 

[sstackhoTEST]

(Sorry, my last post wasn't formatted properly... Here it is again.)

Hello,

I was using Harlan Grove's PULL function for the first time, and
found that it had started behaving in a Volatile manner, when it
shouldn't have.

I was able to hunt down the problem to my formula that builds the
reference that PULL uses. As a step in building the directory of the
external workbook, I used "=LEFT(CELL("filename",A1),FIND("[",CELL
("filename",A1))-1)" to return the directory of the current workbook.
This formula is the problem. When I remove it, PULL no longer acts
Volatile.

But it was my understanding that CELL("filename") was not volatile.
(Sources: http://www.decisionmodels.com/calcsecretsi.htm and
http://support.microsoft.com/kb/211370/EN-US/)

Does anyone know why things became volatile in my workbook?

(BTW, I wrote a tiny UDF to return the directory of the current
workbook, instead of using the CELL function, and the workbook works
fine now.)

Thanks,
Shawn


--

 

[Harlan Grove]

(e-mail address removed) wrote...
....

But it was my understanding that CELL("filename") was not volatile.
(Sources: http://www.decisionmodels.com/calcsecretsi.htm and
http://support.microsoft.com/kb/211370/EN-US/)

....

Don't believe everything you read. Testing is always best to confirm.

In this case, create a nonvolatile udf like

Function nada()
Static n As Long
n = n + 1
MsgBox n
End Function

then enter 2 separate formulas,

=CELL("Filename")&nada()

and

=nada()

Now clear the cell containing the latter, and the former recalcs. Undo
the clear. Now clear the cell containing the former, but the latter
doesn't recalc. Sure looks like CELL("Filename") is volatile despite
what Charles Williams's site may say.

 

[sstackhoTEST]

But I read it on the internet! So it must be true!

Thanks for the analysis, Harlan. PULL is very handy, thank you.

Shawn
--

 

[Charles Williams]

Hi Harlan,

Just to confirm that Cell("filename") shows up as volatile when I test it
too: guess I got lazy and believed the MSKB article.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com