Referencing
 

Hi
not really sure what you're trying to do.<could you give an example?

--
Regards
Frank Kabel
Frankfurt, Germany


solex wrote:
Hello,
Is it possible to reference the current cells value that a
forumula/macro is working on?

I have a macro that retrieves values from a database, but if the
database is down I would like the macro to return the current value
of the cell is this possible?

Thanks,
Dan

Referencing
 

Frank,

Thanks for the response, but I found the answer to my question,
Application.Caller does exactly what I would like, with the exception of
causing a Circular Reference error, which I cannot seem to turn off with
Application.Iterations=True.

This must be a bug since I should be able to bypass the Circular reference
error with the Iterations property, have you seen anything with reagards to
the Iterations property not working?

Thanks,
Dan


"Frank Kabel" wrote in message
...
Hi
not really sure what you're trying to do.<could you give an example?

--
Regards
Frank Kabel
Frankfurt, Germany


solex wrote:
Hello,
Is it possible to reference the current cells value that a
forumula/macro is working on?

I have a macro that retrieves values from a database, but if the
database is down I would like the macro to return the current value
of the cell is this possible?

Thanks,
Dan

Referencing
 

If you're referring to the .value property of the calling cell, try using .text.

Or post the relevant portions of your function.

solex wrote:

Frank,

Thanks for the response, but I found the answer to my question,
Application.Caller does exactly what I would like, with the exception of
causing a Circular Reference error, which I cannot seem to turn off with
Application.Iterations=True.

This must be a bug since I should be able to bypass the Circular reference
error with the Iterations property, have you seen anything with reagards to
the Iterations property not working?

Thanks,
Dan

"Frank Kabel" wrote in message
...
Hi
not really sure what you're trying to do.<could you give an example?

--
Regards
Frank Kabel
Frankfurt, Germany


solex wrote:
Hello,
Is it possible to reference the current cells value that a
forumula/macro is working on?

I have a macro that retrieves values from a database, but if the
database is down I would like the macro to return the current value
of the cell is this possible?

Thanks,
Dan

--

Dave Peterson

Referencing
 

Dave,

The "Text" property raises the same error. Per your suggestion below is the
relevant code.

Thanks,
Dan


Public Function Rates(ByVal UseCache As Boolean) As Double

Dim rng As Range
Dim buf As Double
Dim itersave As Boolean

On Error GoTo ErrorHandler


buf = 0#

itersave = Application.Iteration
Application.Iteration = True
' Here is the ticket per the documentation:
' If the callers is A custom function entered in a single cell then
' A Range object specifying that cell is returned
Set rng = Application.Caller

If IsConnectionDead Then
' Get the data from the cache
If UseCache Then
buf = rng.Offset(0, 1).Text ' <--- Generates the "circular
reference" error.
Else
' this will generate a circular reference error
' which can be turned of using the interations property
' in options/calculations and must be retained the next
' time the sheet is opened
buf = rng.Value
End If
Else
Call Randomize(0.03)
buf = Rnd
End If

ExitHandler:
Set rng = Nothing
Application.Iteration = itersave
Rates= buf
Exit Function

ErrorHandler:
'TODO Report Error?
Resume ExitHandler

End Function


"Dave Peterson" wrote in message
...
If you're referring to the .value property of the calling cell, try using
..text.

Or post the relevant portions of your function.

solex wrote:

Frank,

Thanks for the response, but I found the answer to my question,
Application.Caller does exactly what I would like, with the exception of
causing a Circular Reference error, which I cannot seem to turn off with
Application.Iterations=True.

This must be a bug since I should be able to bypass the Circular
reference
error with the Iterations property, have you seen anything with reagards
to
the Iterations property not working?

Thanks,
Dan

"Frank Kabel" wrote in message
...
Hi
not really sure what you're trying to do.<could you give an example?

--
Regards
Frank Kabel
Frankfurt, Germany


solex wrote:
Hello,
Is it possible to reference the current cells value that a
forumula/macro is working on?

I have a macro that retrieves values from a database, but if the
database is down I would like the macro to return the current value
of the cell is this possible?

Thanks,
Dan

--

Dave Peterson

Referencing
 

I don't know what Isconnectiondead does, but maybe:

buf = Val(rng.Offset(0, 1).Text)
and
buf = val(rng.text)

(I didn't go through the error handler anyway.)

And I'm not sure what you're doing, but shouldn't this function be volatile?

Or pass it the address of the cell under it:
Public Function Rates(ByVal UseCache As Boolean, cellbelow as range) As Double

so in A1:
=rates(true,a2)



solex wrote:

Dave,

The "Text" property raises the same error. Per your suggestion below is the
relevant code.

Thanks,
Dan

Public Function Rates(ByVal UseCache As Boolean) As Double

Dim rng As Range
Dim buf As Double
Dim itersave As Boolean

On Error GoTo ErrorHandler

buf = 0#

itersave = Application.Iteration
Application.Iteration = True
' Here is the ticket per the documentation:
' If the callers is A custom function entered in a single cell then
' A Range object specifying that cell is returned
Set rng = Application.Caller

If IsConnectionDead Then
' Get the data from the cache
If UseCache Then
buf = rng.Offset(0, 1).Text ' <--- Generates the "circular
reference" error.
Else
' this will generate a circular reference error
' which can be turned of using the interations property
' in options/calculations and must be retained the next
' time the sheet is opened
buf = rng.Value
End If
Else
Call Randomize(0.03)
buf = Rnd
End If

ExitHandler:
Set rng = Nothing
Application.Iteration = itersave
Rates= buf
Exit Function

ErrorHandler:
'TODO Report Error?
Resume ExitHandler

End Function

"Dave Peterson" wrote in message
...
If you're referring to the .value property of the calling cell, try using
.text.

Or post the relevant portions of your function.

solex wrote:

Frank,

Thanks for the response, but I found the answer to my question,
Application.Caller does exactly what I would like, with the exception of
causing a Circular Reference error, which I cannot seem to turn off with
Application.Iterations=True.

This must be a bug since I should be able to bypass the Circular
reference
error with the Iterations property, have you seen anything with reagards
to
the Iterations property not working?

Thanks,
Dan

"Frank Kabel" wrote in message
...
Hi
not really sure what you're trying to do.<could you give an example?

--
Regards
Frank Kabel
Frankfurt, Germany


solex wrote:
Hello,
Is it possible to reference the current cells value that a
forumula/macro is working on?

I have a macro that retrieves values from a database, but if the
database is down I would like the macro to return the current value
of the cell is this possible?

Thanks,
Dan

--

Dave Peterson


--

Dave Peterson