Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function Help | Excel Worksheet Functions | |||
User Defined FUNCTION | Excel Discussion (Misc queries) | |||
Creating my own user defined function help statements | Excel Worksheet Functions | |||
If statement to avoid Creating Circular Reference | Excel Worksheet Functions | |||
I have had difficulty in creating user defined functions in Excel | Excel Worksheet Functions |