User-defined function creating circular reference
 

In Excel XP, I have cells E3:E62 that contain either
values or errors. (The errors are =NA() for charting
purposes.) I need to find the last value in the column
and place that value in cell E64.

The function I wrote (in its own module) is:

Function LastAADF()
ActiveSheet.Range("E62").Activate
Do
If IsError(ActiveCell) = True Then
ActiveCell.Offset(-1, 0).Activate
Else
LastAADF = ActiveCell.Value
Exit Do
End If
Loop
End Function

When I put the formula =LastAADF() in cell E64, Excel
says I've created a circular reference. Can someone tell
me what I've done wrong?

Lesa

User-defined function creating circular reference
 

Try adding

msgbox activecell.address
directly after your "activesheet.range("e62").activate" line and you'll see that
E62 wasn't activated. UDFs from a worksheet can't do this kind of thing. They
can only return values. They can't change cells, format cells, or other stuff
that Subs can.

And since you can't change cells, then lastaadf = activecell.value is just
pointing to itself. Hence, the reference to the circular reference.

And if you don't pass the cells that you want to look at to the UDF, it won't
know when to recalculate. You could add "application.volatile" at the top, but
then each time excel recalcs, this'll recalc. Might be a waste of cycles.

Maybe:

Option Explicit
Function LastAADF(rng As Range) As Variant

Dim iCtr As Long

If rng.Columns.Count 1 _
Or rng.Areas.Count 1 Then
LastAADF = CVErr(xlErrRef)
Exit Function
End If

For iCtr = rng.Cells.Count To 1 Step -1
If IsError(rng(iCtr).Value) Then
'keep going
Else
LastAADF = rng(iCtr).Value
Exit Function
End If
Next iCtr

LastAADF = CVErr(xlErrNA)

End Function

and use it in a worksheet cell like:
=lastaadf(E3:E62)

By passing it a range, xl knows to only look to reevaluate when something in
that range changes.

Lesa Richmond wrote:

In Excel XP, I have cells E3:E62 that contain either
values or errors. (The errors are =NA() for charting
purposes.) I need to find the last value in the column
and place that value in cell E64.

The function I wrote (in its own module) is:

Function LastAADF()
ActiveSheet.Range("E62").Activate
Do
If IsError(ActiveCell) = True Then
ActiveCell.Offset(-1, 0).Activate
Else
LastAADF = ActiveCell.Value
Exit Do
End If
Loop
End Function

When I put the formula =LastAADF() in cell E64, Excel
says I've created a circular reference. Can someone tell
me what I've done wrong?

Lesa

--

Dave Peterson

User-defined function creating circular reference
 

As far as I can see nothing in your macro can cause this error,
suggesting that the circular reference is already there.

Look at the status bar where 'Circular' will appear. Tab through your
worksheets in turn until something like 'Circular A1' appears.

Regards
BrianB
==================================================


"Lesa Richmond" wrote in message ...
In Excel XP, I have cells E3:E62 that contain either
values or errors. (The errors are =NA() for charting
purposes.) I need to find the last value in the column
and place that value in cell E64.

The function I wrote (in its own module) is:

Function LastAADF()
ActiveSheet.Range("E62").Activate
Do
If IsError(ActiveCell) = True Then
ActiveCell.Offset(-1, 0).Activate
Else
LastAADF = ActiveCell.Value
Exit Do
End If
Loop
End Function

When I put the formula =LastAADF() in cell E64, Excel
says I've created a circular reference. Can someone tell
me what I've done wrong?

Lesa

User-defined function creating circular reference
 

-----Original Message-----
Try adding

msgbox activecell.address
directly after your "activesheet.range("e62").activate"
line and you'll see that
E62 wasn't activated. UDFs from a worksheet can't do
this kind of thing. They
can only return values. They can't change cells, format
cells, or other stuff
that Subs can.

And since you can't change cells, then lastaadf =
activecell.value is just
pointing to itself. Hence, the reference to the
circular reference.

And if you don't pass the cells that you want to look at
to the UDF, it won't
know when to recalculate. You could
add "application.volatile" at the top, but
then each time excel recalcs, this'll recalc. Might be
a waste of cycles.

Maybe:

Option Explicit
Function LastAADF(rng As Range) As Variant

Dim iCtr As Long

If rng.Columns.Count 1 _
Or rng.Areas.Count 1 Then
LastAADF = CVErr(xlErrRef)
Exit Function
End If

For iCtr = rng.Cells.Count To 1 Step -1
If IsError(rng(iCtr).Value) Then
'keep going
Else
LastAADF = rng(iCtr).Value
Exit Function
End If
Next iCtr

LastAADF = CVErr(xlErrNA)

End Function

and use it in a worksheet cell like:
=lastaadf(E3:E62)

By passing it a range, xl knows to only look to
reevaluate when something in
that range changes.

Lesa Richmond wrote:

In Excel XP, I have cells E3:E62 that contain either
values or errors. (The errors are =NA() for charting
purposes.) I need to find the last value in the column
and place that value in cell E64.

The function I wrote (in its own module) is:

Function LastAADF()
ActiveSheet.Range("E62").Activate
Do
If IsError(ActiveCell) = True Then
ActiveCell.Offset(-1, 0).Activate
Else
LastAADF = ActiveCell.Value
Exit Do
End If
Loop
End Function

When I put the formula =LastAADF() in cell E64, Excel
says I've created a circular reference. Can someone
tell
me what I've done wrong?

Lesa

--

Dave Peterson

.
Thanks, Dave! Your code works perfectly. Also, I
really appreciate the explanation of what I did wrong.
Now I understand.

User-defined function creating circular reference
 

-----Original Message-----
As far as I can see nothing in your macro can cause this
error,
suggesting that the circular reference is already there.

Look at the status bar where 'Circular' will appear. Tab
through your
worksheets in turn until something like 'Circular A1'
appears.

Regards
BrianB
================================================= =


"Lesa Richmond"
wrote in message news:<042f01c34bd3$9f4ecc70
...
In Excel XP, I have cells E3:E62 that contain either
values or errors. (The errors are =NA() for charting
purposes.) I need to find the last value in the
column
and place that value in cell E64.

The function I wrote (in its own module) is:

Function LastAADF()
ActiveSheet.Range("E62").Activate
Do
If IsError(ActiveCell) = True Then
ActiveCell.Offset(-1, 0).Activate
Else
LastAADF = ActiveCell.Value
Exit Do
End If
Loop
End Function

When I put the formula =LastAADF() in cell E64, Excel
says I've created a circular reference. Can someone
tell
me what I've done wrong?

Lesa
.
Thanks for your response, Brian. Looks like we both
learned something today: see Dave Peterson's response -
he explained the problem.