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

Dear Derek,

I did not want to write:

on error resume next.

I did not need per se to find an error trapping code.

Maybe the problem is that I did not understand your code fast enough, and as I said previously, I had seen Tim's reply before I say yours.

So to tell the truth, I did not try the code, but I will try it tonight (I can't do that during daytime, when I need the sheet), because I just understood the line where it would do what I wanted.

I will tell you how it went.

Although Tim's code works well, to understand you code would learn me new ways to handle similar problems

Thank you very much, I'll be back on this.

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

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