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_functi on,rand())

Thanks

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote...
is there a way to call the existing value of cell inside a UDF?

Yes,

Application.Caller.Value

but it creates a circular reference.

Thank you


"Harlan Grove" wrote in message
...
"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote...
is there a way to call the existing value of cell inside a UDF?

Yes,

Application.Caller.Value

but it creates a circular reference.

Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

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

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
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

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

--

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.

"Dave Peterson" wrote in message
...
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

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
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.

"Dave Peterson" wrote in message
...
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

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

--

Dave Peterson

--

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....

;)

"Dave Peterson" wrote in message
...
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.

"Dave Peterson" wrote in message
...
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

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" wrote in message
...
Maybe you could use:

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

(.Text will return a string)

"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_functi on,rand())

Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson