Creating URL Validation Function

[k2hunter]

Hello all I'm trying to get this function to work.

I created a function to check the validity of URL in VBA.

I try to run it and get a compile error. Any help thanks!

Please Login or Register  to view this content.

[1aaaaaaaaaaaaaaa]

Hiya, please wrap your code in code tags [ CODE ] [ /CODE ]

[1aaaaaaaaaaaaaaa]

Code debug-- you have one too many "End Function" statements, change your code to

Please Login or Register  to view this content.

[k2hunter]

Hi I changed code as mentioned now I receive

http://google.com =checkURL(A1)

"This method cannot be called until the Send method has been called"

[k2hunter]

Hello, I've adjusted code to bellow. Is there a way to get the infor from the URLs with out opening the browser? Thank you for the look !

Please Login or Register  to view this content.

[Arkadi]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html


I believe this would have worked?

Please Login or Register  to view this content.

Edit: just FYI I had http://google.com/ in A1, and in another cell I put = CheckURL(A1)
It returned 200

[k2hunter]

Yes this seems to work,
Thank you.
however

http://google.com =checkURL(A1) "The operation timed out"
www.google.com =checkURL(A2) "The URL does not use a recognized protocol"


Thanks again
Also is this easy enough to transfer into a macro button?

[Arkadi]

The button part is easy. Insert a button on the sheet, right-click, choose assign macro, and pick the macro you want... but that suggests not really needing a UDF (user defined function). The reason the second example does not work is that the text of A2 is not a real hyperlink, it is missing the http:// part (which your browser takes as implied, but the code does not)
My guess is that A1 does work most of the time?, but it depends on internet speed perhaps?

you could try adding one extra line and see if that fixes the timed out issue:

Please Login or Register  to view this content.

If we do this via a button then we can modify the code a little, so that it picks up the hyperlink address instead of the text of the cell. That may solve issue of A2. That is, assuming that there are hyperlinks and not just plain text?

[k2hunter]

Hey I really appreciate your input. I think that last thing is a really good idea. Currently I have them formatted as text.
some background, I have to test aprox. 50 links almost every day. So I thought the Function would be good enough to run daily and then have a button to spot check though out the day. since the links are up and down quite a bit. the links are referenced on different spreadsheets so having the functions separate from the main workbook would keep it running more efficiently. since after running the function on all links its freezes for a couple min as it runs. and as you know every refresh of sheet it runs again. :/

But its much farther progress then doing it manually

[Arkadi]

The "freezing" could be partially caused by any urls that are slow responding since we added .WaitForResponse, if you take that out you will get some "timed out" errors.

If all the cells had the url in the complete format ("http://.....") then I think you would have no issues but if they are not all in that format then we can try to fix it. Could you provide a sample workbook with a list of urls that we can use for testing?

[Arkadi]

Here is a starting point for a macro that you could assign to a button. Check for the worksheet name, columns I am using in the code as well as rows, so you can adjust those if needed:

Please Login or Register  to view this content.

[k2hunter]

Here is the sample you requested.

[Arkadi]

The file you provided has very few links... which is fine, but when we spoke yesterday you mentioned some urls were just www.google.com for example, without "http://"
It is always best to give some sample data that covers all the possible situations.

However based on what you provided, you would just need to change the "for i = 2 to lr" to start at 2 instead of 3... and ONE button anywhere on the sheet, with the macro I posted in #11 should work. However, it outputs results in B so you may want to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and in the error catch:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Unless you REALLY want to use a button for each url (which seems painful to me), instead of one button to process the whole list?

[k2hunter]

The one button in #11 to do whole list it Excel hangs up on the .send

Please Login or Register  to view this content.

The function if I format it down for each link its takes a long time freezes and or crashes.
As for the link the ones I'm using are specifics to company and need to omit that data.

[Arkadi]

Hmm... That code worked perfectly for the sample data... I even added a bunch of addresses some with and some without http://
Does it get stuck on one particular url?

[k2hunter]

I've attached screenshots of the errors.

I have used


http://google.com "The operation timed out"
http://yahoo.com
http://cnn.com

[Arkadi]

hmm... what is the value of url at that point?

[k2hunter]

Those are the URL's when I run the macro. Colum A and Colum B
Google times out and that message is generated and then it stops.

[Arkadi]

Using exactly the code from post #11 this is what I get (with no errors)... so I'm a bit confused as to what is wrong:

result.jpg

[k2hunter]

Well here is the file with the company specific links removed.

[Arkadi]

Opened the file... ran the code, and got 200 for all three. Any chance there is a network/internet problem on your end?

[k2hunter]

ugh... well.. I can access the links in browsers or clicking on the hyperlink itself. it does take longer then usual but that what I thought .wait for response corrected.

Not sure what would cause the return value to time out. I am getting 440 Automation error however.

Also the loop breaks when it times out.

would "on error resume next" be something to add?

[Arkadi]

Yes it should have corrected that... though I don't know how long it waits before deeming it timed out. The unresolved url on yahoo confuses me more. On error resume next would work for getting through the list, but disables your error checking so then you would not get the error description.

[k2hunter]

mind sending me a file you created that works to compare?

[sanakhana]

but to use iit