Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Property of the Range Object
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? -- Gary''s Student - gsnu200746 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Property of the Range Object
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(),"@"). Gary''s Student wrote: 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? -- Gary''s Student - gsnu200746 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Property of the Range Object
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 "Gary''s Student" wrote in message ... 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? -- Gary''s Student - gsnu200746 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Property of the Range Object
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 -- Gary''s Student - gsnu200746 "Charles Williams" wrote: 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 "Gary''s Student" wrote in message ... 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? -- Gary''s Student - gsnu200746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding custom property to Range object | Excel Programming | |||
Range object lacks Value property | Excel Programming | |||
range property of range object | Excel Programming | |||
How to use Cells property to Range object? | Excel Programming | |||
Can Range.Find search a Cells' Text property? | Excel Programming |