2003

My Macro is supposed to add a sheet and list information related to the
location of a Circular Reference.

When I first forced a circular reference (for testing the macro), the
following VBA query works in the Immediate window:
? ActiveSheet.CircularReference.Address
$F$10

If I run another macro to check for other "errors" then go back to the
check for the known circular reference - the result is:
? ActiveSheet.CircularReference.Address
Runtime error '#91'
Object variable or With block variable not Set

In addition to the error my macro does not create the intended report
because the initial status of ActiveSheet.CircularReference is, I
guess Null or Nothing, instead of True.

What is happeninhg here that is avoiding my brain. And what do I need
to do to cause the VBA Code to print the report when I thought that it
should have?

TIA EagleOne

Well, I think that I may have the answer. I need to Select the CR
first:

Worksheets("Sheet1").CircularReference.Select

Apparently, that is the "Set" that the code needs. If there is more to
the picture, please offer some thoughts

EagleOne


EagleOne wrote:
2003

My Macro is supposed to add a sheet and list information related to the
location of a Circular Reference.

When I first forced a circular reference (for testing the macro), the
following VBA query works in the Immediate window:
? ActiveSheet.CircularReference.Address
$F$10

If I run another macro to check for other "errors" then go back to the
check for the known circular reference - the result is:
? ActiveSheet.CircularReference.Address
Runtime error '#91'
Object variable or With block variable not Set

In addition to the error my macro does not create the intended report
because the initial status of ActiveSheet.CircularReference is, I
guess Null or Nothing, instead of True.

What is happeninhg here that is avoiding my brain. And what do I need
to do to cause the VBA Code to print the report when I thought that it
should have?

TIA EagleOne

This is great, an excellent dialog with myself. But for all who can
benefit: The sequence to "set-up" the ability to obtain the
CircularReference.Address is:

Sub GetCircularReferenceAddress()

..... previous code

Calculate ' Must force a calculation before "Select"
On Error Resume Next
MyWorksheet.CircularReference.Select
MyWorksheet.CircularReference.Address

...... other code

End Sub

EagleOne wrote:
2003

My Macro is supposed to add a sheet and list information related to the
location of a Circular Reference.

When I first forced a circular reference (for testing the macro), the
following VBA query works in the Immediate window:
? ActiveSheet.CircularReference.Address
$F$10

If I run another macro to check for other "errors" then go back to the
check for the known circular reference - the result is:
? ActiveSheet.CircularReference.Address
Runtime error '#91'
Object variable or With block variable not Set

In addition to the error my macro does not create the intended report
because the initial status of ActiveSheet.CircularReference is, I
guess Null or Nothing, instead of True.

What is happeninhg here that is avoiding my brain. And what do I need
to do to cause the VBA Code to print the report when I thought that it
should have?

TIA EagleOne