Referencing

S

solex

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
 
S

solex

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
 
D

Dave Peterson

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

Or post the relevant portions of your function.
 
S

solex

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
 
D

Dave Peterson

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)
 
Top