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

Dear Louise

Are you saying that you tested this code and that it does not work :-

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 was making some general comments about how you do error handling in VBA, so I mentioned Select Case on the error number.

The main point I was making is that when you turn on VB error handling you turn it off as soon as possible after the line where you expect a known error to occur. It is completely wrong to put On Error Resume Next as the first line of your subroutine.

Regards

Derek Turner

England

+++

From: Louise Gariépy <garilou@cgocable.ca>
To: ExcelVBA@yahoogroups.com
Sent: Friday, 25 April 2014, 14:25
Subject: Re: [ExcelVBA] Find if image has hyperlink

Sorry Derek,
I took time to reply, I think I had not seen your solution.
First the "0" was a left over from my previous code, there is not 0 in the hyperlinks anymore, only text or nothing.

I understand how your select case allows do make the difference between the possible errors, but it does not enter FALSE in the cell where the error 1004 happens (or I least I did not catch it).

I have adopted Tim's solution and it works good, but I will remember your select case, because I will need it for another problem that I have.

Thank you for taking the time to try.

Louise

Le --23042014 à 13:08, Derek Turner <g4swy@yahoo.com> a écrit :

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 (13)

----------------------------------
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