Testing for Hyperlink

[JJ \(UK\)]

Hi again

This little bit of code will remove a Hyperlink from a cell:

Range("L3").Select
Selection.Hyperlinks(1).Delete

(Or presumably 'Range("L3")..Hyperlinks(1).Delete' will also work)

But this throws up an error if there is no Hyperlink.

So, two questions I hope someone can help with:

1 - How do you trap errors in VB?
2 - How do you test for the existence of the hyperlink to avoid the error in
the first place?

Oh, and while I'm here, how do I find out which version of VB I've got
installed. The usual 'Help' -> 'About' doesn't tell me a version number (it
just confirms what I already know, which is that I'm using VB!)

Cheers all.

 

[Norman Jones]

Hi JJ (UK),

Try:

On Error Resume Next
Range("L3").Hyperlinks(1).Delete
On Error GoTo

Or, for a more general approach:


Sub AAA()

Dim myHLink As Hyperlink

On Error Resume Next
Set myHLink = Range("L3").Hyperlinks(1)
On Error GoTo 0

If Not myHLink Is Nothing Then
'hyperlink exists, do something
MsgBox "Hi!"
Else
'hyperlink does not exist, do something else
MsgBox "Ho!"
End If
End Sub

 

[Norman Jones]

Hi JJ,

Oh, and while I'm here, how do I find out which version of VB I've got
installed. The usual 'Help' -> 'About' doesn't tell me a version number
(it
just confirms what I already know, which is that I'm using VB!)

If you refer to VBA, then the Help About dialog give a VB version at the top
and a more specific VBA version at the foot of the dialog.

If you refer to VB, and I notice that your post was also directed to a VB
group, then the VB Help about dialog gives similar information at the top
and bottom of the dialog.

 

[Nick Hebb]

#1: That can be an involved subject. Sorry to defer you, but a quick
search on Visual Basic error handling should turn up some good
tutorials. For Google, add "-.Net" to the search term to filter out
VB.Net material.

#2:

If Range("L3").Hyperlinks.Count > 0 Then
Range("L3").Hyperlinks(1).Delete
End

Out of curiousity, I tried several methods to add more than one
hyperlink to a cell and they just converted to plain text. So
hyperlinks(1) should cover it (at least in Excel XP/2002).

 

[Nick Hebb]

Well, since Norman gave an example of error handling, I'll offer this
2-minute drill.

There are 3 (?) ways to handle errors.

1. On Error GoTo <error handling label name> - This will divert the
program to the error handler in the event of an error.

2. On Error Resume Next - continues to the next line where you may
cjhoose to terst the Err.Number and do special handling in-line.

3. On Error Goto 0 - turns off all error handling. This is needed
especially if you use Err.Raise to raise the error to the calling code.

So, an example of error handling for our delete hyperlinks function
might look like:

Public Sub DeleteHyperlink()
On Error GoTo Err_Handler

Range("L3").Hyperlinks(1).Delete
MsgBox "This is an example of Resuming operations after an expected
error"

Exit_Handler:
' place code here to set any objects to Nothing
Exit Sub

Err_Handler:
If Err.Number = 9 Then
MsgBox "There is no hyperlink in the selected cell"
Resume Next
Else
MsgBox "Error #: " & Err.Number & vbNewLine & Err.Description
End If
Resume Exit_Handler

End Sub

If you have a no hyperlink error, you will see 2 message boxes. If you
have some other error, you will just see the one with Err.Number ...
Err.Description.

 

[Randy Birch]

: Oh, and while I'm here, how do I find out which version of VB I've got
: installed. The usual 'Help' -> 'About' doesn't tell me a version number
(it
: just confirms what I already know, which is that I'm using VB!)

Not sure what you mean here. Help>About shows my VB6 version right at the
top of the dialog:

MS Visual Basic 6.0 (SP5)

As I recall, VB5 did not have this info on the about dialog (though I won't
bet the farm on that), and I thought with VB5 you had to rely on reading -
really quickly - the splash screen,.


--

Randy Birch
MS MVP Visual Basic
http://vbnet.mvps.org/
----------------------------------------------------------------------------
Read. Decide. Sign the petition to Microsoft.
http://classicvb.org/petition/
----------------------------------------------------------------------------



: Hi again
:
: This little bit of code will remove a Hyperlink from a cell:
:
: Range("L3").Select
: Selection.Hyperlinks(1).Delete
:
: (Or presumably 'Range("L3")..Hyperlinks(1).Delete' will also work)
:
: But this throws up an error if there is no Hyperlink.
:
: So, two questions I hope someone can help with:
:
: 1 - How do you trap errors in VB?
: 2 - How do you test for the existence of the hyperlink to avoid the error
in
: the first place?
:

:
: Cheers all.
:
: --
: JJ (UK)
:
:

 

[JJ \(UK\)]

If you refer to VBA, then the Help About dialog give a VB version at the top
and a more specific VBA version at the foot of the dialog.

If you refer to VB, and I notice that your post was also directed to a VB
group, then the VB Help about dialog gives similar information at the top
and bottom of the dialog.

I'm not clear on the difference between VB and VBA. I'm using the VB that
came with Office 97.

And I've tried the 'About Visual Basic' item from the 'Help' menu, it
doesn't tell me anything I didn't already know though.

JJ (UK)