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

Dear Derek,

Thank you for this explanation of the

"on error goto 0".

You are right, the VBA langage (and this is not the only example) is not always intuitive.

I have tried your

TestErrClear()

and I must say that I did not notice any difference with or without Err.Clear .

What should I have seen?

(I know we seem to getting out of topic, but this is a very interesting subject)

Louise

Le --26042014 à 06:07, Derek Turner <g4swy@yahoo.com> a écrit :

Dear Louise, Dear Tim

You are quite right about GoTo.

What I was saying is that you should keep the program flow in a straight line and GoTo destroys the flow. This applies equally to the putting of the error handling in a separate part of the code.

Google the classic 'GoTo statement is considered harmful'

To clarify error handling further, this code tests what Err.Clear does :-
Sub TestErrClear()
On Error Resume Next
Err.Clear
MsgBox 1 / 0
End Sub

Try it with and without the Err.Clear.

All Err.Clear does it to clear the error object. It does not switch off the error handling, the GoTo 0 does that. Tim had to use Err.Clear in his loop because he made the Resume Next active at the start of the subroutine which means that any other error in any other line would have been ignored, creating unnecessary confusion.

Think of 'On Error GoTo 0' as a single construct. Microsoft should have called it 'End Resume' or similar.

By the way like Louise I started programming many years ago with an Acorn Atom, then an Apple II and then all the way up to Dot Net in modern machines. Because of my experience in VB6, VBA and .Net I recently found that it took me only a few days to understand Basic4Android and to write programs for Android devices (phone and tablets). I heartily recommend Basic4Android to members of this group in this Post-PC age.

Regards

Derek turner
England
+++

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

Hi again Derek,

I just tested your code, and it also does exactly the job.

About your remark on
On error Resume Next
as the first line of the subroutine, may I reassure you that that I never did that.
Either there is an error and you want to correct it (or in this case you want to use a specific error as part of the code).
I know that there has to be exceptions, but I guess the resume next should be placed in an Error trap at the end of the sub.

I am glad that you explained to me what for one could use
On Error GoTo 0,
because I had seen that very often, and did not know what that was for, especially that everywhere, I read "NEVER use GoTo, there is always a workaround".
I started programming learning basic on the first computer that came in our home, the VIC 20! And the the VIC 64.
[This gives you an idea that I am not that young ;-) ]
I still miss sometimes the GOTO.

Tim used Err.Clear, I guess it served the same objective.

I will also remember the part on
Select Case
for the error, that will help me.
I thought I knew enough about error handling (a few years ago there was a very long discussion about how people debugged their code, I was a total beginner, and I had taken many notes), but I can't remember this one.

So my post like always not only gave me a solution, but gave me new tools.

Thank you very much for your time,

Louise

Le --25042014 à 11:14, Louise Gariépy <garilou@cgocable.ca> a écrit :

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

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