Text Property of the Range Object

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary''s Student - 24 Sep 2007 21:20 GMT

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?

Signature

Gary''s Student - gsnu200746

Reply to this Message

JW - 24 Sep 2007 21:33 GMT

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(),"@").

> I am having intermittent problems with the Text property
>
[quoted text clipped - 18 lines]
> --
> Gary''s Student - gsnu200746

Reply to this Message

Charles Williams - 25 Sep 2007 00:09 GMT

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

>I am having intermittent problems with the Text property
>
[quoted text clipped - 17 lines]
>
> Is there a solution?

Reply to this Message

Gary''s Student - 25 Sep 2007 01:16 GMT

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

Signature

Gary''s Student - gsnu200746

> The Text property only gets updated when the formatting layer has been
> refreshed. This usually happens after the calculation has completed.
[quoted text clipped - 33 lines]
> >
> > Is there a solution?

Reply to this Message