Find invalid names via VBA

[efree]

Using VBA, how do I identify invalid dynamic range names?

For Example, I would like the following to be flagged:

"TargetRng" is defined as OFFSET(StartPoint, 0, 0, 1, 100) where
"StartPoint" is undefined.

Thank you for your help

 

[Ardus Petus]

Dim rng as Range
On Error resume Next
Set rng=Range("TargetRng")
On Error Goto 0
If rng is Nothing then
MsgBox "Error"
Else
Whatever
Endif

HTH

 

[efree]

Dim rng as Range
On Error resume Next
Set rng=Range("TargetRng")
On Error Goto 0
If rng is Nothing then
MsgBox "Error"
Else
Whatever
Endif

HTH

Thanks for that. Almost there...

In adapting the methodology to my workbook at large, via:

For Each n In ActiveWorkbook.Names
....
Next

Along with invalid dynamic ranges, the subroutine flags all of the
names defined for formulas because, strictly speaking, those do not
refer to ranges. For example:

"oftenUsedFormula" - name defined as COUNTIF(A1:A500, "< 6")

Any thoughts?