repair hyperlinks / testing for invalid address

[brzak]

I find hyperlinks are useful in workbooks, but you have to avoid
renaming worksheets as links don't update.

I've been trying to work out a way of identifying broken hyperlinks in
workbooks, and then go onto offering some way of repairing the link.
However, I've been unable to overcome an early hurdle...

My approach is to:

1 cycle through all worksheets in the workbook concerned
2 cycle through the all hyperlinks within each worksheet
3 for each hyperlink, test whether destination address
(Hyperlinks(i).SubAddress) is valid.

and here lies my problem...

i was after a quick test, something along the lines of:

If IsError ( Range(Hyperlinks(i).SubAddress) ) Then
' *Fix*
End If

However, the error caused by having an invalid range stops my
procedure, whereas I would like it to carry on regardless, only doing
something different when it is in invalid.

Any insights into this problem would be greatly appreciated.

 

[paul.robinson]

Hi
On error resume next
If IsError ( Range(Hyperlinks(i).SubAddress) ) Then
' *Fix*
End If
on error goto 0

regards
Paul

 

[brzak]

Thanks Paul,

I have thus far avoided useing the "On Error" statement as I eblieve
that my code shouldn't include any errors but I may have to change
that mindset.

With your advice, this is what my code looks like. I didn't know about
teh "On Error GoTo 0" line before, as I thought taht the whole Sub
would be affected by hte "On Error Resume Next" statement.

Sub Hypers()

Dim Hyper As Hyperlink
Dim WS As Worksheet
Dim n As Integer



For Each WS In Worksheets
For Each Hyper In WS.Hyperlinks

Debug.Print n & "; Name:" & Hyper.Name & "; TTD:" &
Hyper.TextToDisplay & "; Loc:" & WS.Name & "'" & Hyper.Range.Address &
vbCrLf & _
"HAdd: " & Hyper.Address & "HSubAdd: " &
Hyper.SubAddress

n = n + 1

On Error Resume Next
If IsError(Range(Hyper.SubAddress)) Then
Stop
End If
On Error go to 0
Next Hyper
Next WS
End Sub

Thanks again

 

[paul.robinson]

Hi
The construct
on error resume next
'code
on error goto 0

is extremely useful & widely used in VBA. If you want to add a comment
to a cell or add a certain sheet name for example, both of which may
already exist, it is invaluable in catching the error and letting you
proceed.
regards
Paul