UDF question

[Adam Kroger]

is there a way to call the existing value of cell inside a UDF?

Cell A1 has a formula in it, including a UDF.
Inside that UDF's code, is there a way to retreive what the existing value
of A1 is, before the UDF replaces that value with a new one.

intended purpose:
to prevent a volitile function from running every time Excel refreashes its
calculations

nonexistant code example:
IF(ISNUMBER(CellValueFunction()),cell_value_function,rand())

Thanks

 

[Harlan Grove]

is there a way to call the existing value of cell inside a UDF?

Yes,

Application.Caller.Value

but it creates a circular reference.

 

[Adam Kroger]

Thank you

Yes,

Application.Caller.Value

but it creates a circular reference.

 

[Dave Peterson]

Maybe you could use:

Application.Caller.Text
or
clng(Application.Caller.Text)
or
cdbl(Application.Caller.Text)

(.Text will return a string)

"Adam Kroger

 

[Adam Kroger]

How would application.caller.text be implemented? Does it need a cell
reference?

my test looks like this
=IF(ISNUMBER(Q84),apcheck(),"")

Function apcheck()
If (CLng(Application.Caller.Text) > 1) And (CLng(Application.Caller.Text) <
19) Then
apcheck = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() * 6,
0)
Else: apcheck = Application.Caller.Text
End If
End Function

it returns #NAME?

 

[Dave Peterson]

application.caller.text
is used to return the text in the cell.

If the cell were formatted to display currency, you'd want to convert the string
to the underlying value.

If you use Application.caller.text in your UDF, then you'll see what's in the
cell that holds the formula.

But if I remember your struggle with this correctly, I think I'd just put a
button from the forms toolbar that would repopulate the cells with the toss of
your die.

If you never hit the button, they'll never be refreshed. If you click the
button, they will.



"Adam Kroger

 

[Adam Kroger]

I already have a macro, and button that does just that, and it works well.
This "project" of mine has wandered into the relm of "intelectual exercise"
to see just how much I can acomplish within the framework. I want to try
to automate as much as possible/practical. I hope to be able to find a way
to get the worksheet to the point where the only information that has to be
entered has to do with "real world" interaction, and Excel handles the rest.

 

[Dave Peterson]

Excel likes to calculate formulas. If you use formulas, you shouldn't be too
surprised that excel will want to recalculate whenever it thinks it needs to.



"Adam Kroger

I already have a macro, and button that does just that, and it works well.
This "project" of mine has wandered into the relm of "intelectual exercise"
to see just how much I can acomplish within the framework. I want to try
to automate as much as possible/practical. I hope to be able to find a way
to get the worksheet to the point where the only information that has to be
entered has to do with "real world" interaction, and Excel handles the rest.

 

[Adam Kroger]

I'm not. I'm just trying to politely persuade it to leave this one alone.
If politeness doesn't work, a brick is not out of the question....

 

[Dave Peterson]

I'd use that (figurative???) brick <vbg>.

"Adam Kroger

I'm not. I'm just trying to politely persuade it to leave this one alone.
If politeness doesn't work, a brick is not out of the question....