Text Property of the Range Object

[Guest]

I am having intermittent problems with the Text property

In A1:
=rand()

In A2:
=txet(A1)

Where txet is:

Function txet(r As Range) As String
Application.Volatile
txet = r.Text
End Function

Sometimes txet works just fine. Other times, it updates; but stays one step
behind. That is it displays the value before F9 rather than the new value.

Is there a solution?

 

[JW]

Not sure about your text problem in your function, but is there a
specific reason that you are doing this this way? You could use a
formula in A2 of =TEXT(A1,"@") and accomplish the same thing. Even
better, if you are just trying to get the random number in a text
format, you could use =TEXT(RAND(),"@").

 

[Charles Williams]

The Text property only gets updated when the formatting layer has been
refreshed. This usually happens after the calculation has completed.
If r has not yet been calculated when txet is calculated then you will get
the previous value of r.Text.

Try adding something like
if isempty(r) then exit function before the txet=r.Text
that might prevent txet being calculated before r


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

 

[Guest]

Thank you Charles.

I guess the key issue is making sure the cell is truely calculated. I
discovered that if I access the Value property first, the Text property
becomes valid:



Function txet(r As Range) As String
Dim v As Variant
Application.Volatile
v = r.Value
txet = r.Text
End Function