Hyperlink.Item(1).Address #VALUE! Suppression?

[Jonnyabc]

Although my first post to the MrExcel forum, I have been using you folks more and more in recent months whenever I've run into coding issues.

In light of that fact, I am in need of assistence to determine how to intercept the error #VALUE! from showing up in my VBA hyperlink extraction function. Here's an excerpt:

Option Explicit

Function URL(rngValue As Range) As String
    If rngValue <> "" Then
        URL = CStr(rngValue.Hyperlinks.Item(1).Address)
    Else
        URL = ""
    End If
End Function

If used on a referenced cell that contains a hyperlink, it works fine. This also passes well if the cell is blank. But I cannot for the life of me figure out how to make it check ahead for the hyperlink to determine if it should or not...and trust me, Jonnyabc didn't just pickup a beginner's programming book just yesterday, so test out your solution before you tell me something I've already tried.


Let me list out a few things I've found:

• I've used the IF statement to determine if Item(1).Address is null using various ISNULL() ISEMPTY() ISMISSING() ISERROR() functions to no avail. Once after debugging I found a critical issue: once Excel determines there was no hyperlink, it abruptly ends the function and returns #VALUE!, ignoring all future code in the function, although not displaying any VBA errors.
• #VALUE!, while it can be copied and tested in an Excel cell as text, cannot be tested as such in VBA because neither ISERROR() nor Application.WorksheetFunction.IFERROR() seems to find or replace the error. I've even converted the value to a string to no avail!

Still no clue at all where the problem lies so any assistance is welcome! It's probably a really simple tweak that I haven't tried (or messed up when my eyes were getting bloodshot.

Thanks folks! And remember that people outside of your valuable forum really do read and need this stuff too! Have a great day, and I hope this won't be our last great discussion together!

 

[Scott Huish]

and trust me, Jonnyabc didn't just pickup a beginner's programming book just yesterday, so test out your solution before you tell me something I've already tried.

OK, I have an answer for you, but before you say something like this, please note that it's not going to help you get an answer and if you didn't pick up a programming book yesterday, you would know that string data types initialize to empty string, making the ELSE portion of your IF redundant.

Now, see if this works for you:

Function URL(rngValue As Range) As String
Application.Volatile
If rngValue.Hyperlinks.Count Then URL = rngValue.Hyperlinks.Item(1).Address
End Function

Hit F9 to recalculate if the values change.

 

[Jonnyabc]

I didn't mean to sound defiant or snobby, I just don't want to cover ground I already have. I just want to see same progress!

 

[Scott Huish]

OK.

Did my code work for you?

 

[Jonnyabc]

OK.

Did my code work for you?

Yes sir! I thank you. Sorry for the delay, I had to get to my testing environment. Now is there a way to do this without using it 'as Range'? I would like to shore up this code as best as possible but I wasn't sure how to make Hyperlink.Count work with a string. Thanks!

 

[Scott Huish]

Are you not wanting to refer to a cell in your function?

 

[Jonnyabc]

Let me rephrase it...yes I do want to reference the cell, but I would like to use the input 'as String' rather than 'as Range' if possible.

 

[Scott Huish]

I think .Range is going to be your best bet, since we need to look at properties of the Range object. If the range contains a string, that is the value the function will work with.

 

[Jonnyabc]

Yeah, you're right. I just wanted to see if it was possible but what I've got works and I'm happy with it. Thanks for the assist HOTPEPPER!

 

[repairman615]

Jonnyabc,

Please be sure to update the other post(s) you may have at different sites. It is a good idea to post links to these other site when you have cross-posted.

http://www.knowledgesutra.com/forums/topic/74130-excel-vba-supress-value-within-vba-code/