EXCEL FEED: Re: [ExcelVBA] Find if image has hyperlink

Rabu, 23 April 2014

Re: [ExcelVBA] Find if image has hyperlink

Dear Louise

This worked for me :-

Sub Find_Image_URL()

Dim Shp As Shape, strUrl As String

For Each Shp In ActiveSheet.Shapes

strUrl = "0"

On Error Resume Next

strUrl = Shp.Hyperlink.Address

On Error GoTo 0

Range(Shp.TopLeftCell.Address) = Not strUrl = "0"

Next Shp

End Sub

I took out the intersect thing to simplify the testing. You will have to put it back. The strUrl = "0" allows for testing your old web pages.

The proper way to do error trapping in VBA is to surround the offending line with the error trapping code. Do not use On Error Goto Label because this creates spaghetti code. Keep the trapping in-line.

The On Error Goto 0 switches off the Resume Next immediately after the line where the error is expected so if there are later errors you will not be confused.

To be more professional you might want to test the error in case it is not the one you expect :-

On Error Resume Next

strUrl = Shp.Hyperlink.Address

nErrorNumber = Err.Number

sErrorDescription = Err.Description

On Error GoTo 0

Select Case nErrorNumber

Case 0, 1004

Case Else: MsgBox sErrorDescription, vbCritical, "Unexpected Error"

End Select

By the way your InStr(strUrl, "0") failed for me because my test URL had a zero in it.

You said :-

" If I write at the beginning: On Error goto next, or GoTo Next
I get an script error, "
This is because 'Next' is a Key Word in VBA. So you cannot use it as a label. In any case note my advice above about writing in-line code. The GoTo statement is considered harmful.
Regards
Derek Turner
England
+++

From: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Wednesday, 23 April 2014, 14:56
Subject: Re: [ExcelVBA] Find if image has hyperlink

Louise, nothing more I can help with, unfortunately. Anybody else have advice on images and hyperlinks?

From: Louise Gariépy <garilou@cgocable.ca>
Reply-To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Date: Wednesday, 23 April 2014 2:12 pm
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Subject: Re: [ExcelVBA] Find if image has hyperlink

Sorry Paul,

But before I wrote this message, I had already tried the on error Resume Next.

It does not work.

I know pretty well the error handling codes.

Louise

Le --23042014 à 02:08, Paul Vermeulen <paul.vermeulen@vulcantech.com.au> a écrit :

My apologies, Louise, GoTo should be Resume. On Error Resume Next. ;)

You should also read Chip Pearson's advice on http://www.cpearson.com/excel/errorhandling.htm.

Paul

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Louise Gariépy
Sent: Wednesday, 23 April 2014 2:03 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Find if image has hyperlink

Thank you Paul for this prompt reply.

1. Could you write the exact code that you think of, because I am used to

On Error GoTo errTrap

and at the end:

errTrap:

code ...

If I write at the beginning:

On Error goto next, or GoTo Next

I get an script error,

and if I write it in the errTrap:

GoTo Next

I also get a script error.

2. It would not solve the problem that in the case of an error for that particular cell (there is an image but no hyperlink), I would need to write FALSE in the cell.

Louise

Le --23042014 à 01:30, Paul Vermeulen <paul.vermeulen@vulcantech.com.au> a écrit :

Hi Louise

Just use an error-catch to say On Error goto next. Place it above you For statement and on an error it will just go to the next picture.

Regards

Paul

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Louise Gariépy
Sent: Wednesday, 23 April 2014 1:26 PM
To: excelvba@yahoogroups.com
Subject: [ExcelVBA] Find if image has hyperlink

Hi Group!

First I want to apologize if I have double posted.
I first sent this to the wrong group, (ms_excel), but I do not see it appear.

I have a worksheet where I copy paste HTML code for a group of symbols.
In column E, I do not receive text, but images (some rows have no image at all and it means something else)
The hyperlinks on the images carry the information that I need.

Until recently, some images had as hyperlink content: "0", the other ones had text.
For me, it was enough to detect if there was text: then the cell content was set to TRUE.
For the images where the hyperlink content was "0", then the cell content was set to FALSE.

My code was:
--------------------------------
Sub Find_Image_URL()

'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

For Each Shp In ActiveSheet.Shapes
If Not Application.Intersect(Range("E6:E60"), Shp.TopLeftCell) Is Nothing Then
strUrl = Shp.Hyperlink.Address

If InStr(strUrl, "0") Then
Range(Shp.TopLeftCell.Address) = False
Else
Range(Shp.TopLeftCell.Address) = True
End If
end if
Next Shp

End Sub
-------------------------------------
Now the web site has changed.
The hyperlink that used to be 0 on the web site are now "NO PATTERN", but this is NOT copied when pasted on the sheet.

So the code blocks on
strUrl = Shp.Hyperlink.Address
because there is no hyperlink

I can't find a way to write something like

If "there is no hyperlink" then
Range(Shp.TopLeftCell.Address) = False

Thanks for your always great help,

Louise G.

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (8)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group