Jump to content
xisto Community
Jonnyabc

Excel Vba Supress #value! Within Vba Code? Nothing seems to work?

Recommended Posts

My code is as follows:

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

I am attempting to pull the hyperlink out of a single cell reference. I test the code on three cell value types: one with hyperlink text, one without hyperlink text, and one that's completely blank. Generally I am successful getting the first and third cells to return the URL value and blank value respectively, but I always get the fatal #VALUE! error on a cell that contains text without a hyperlink.

I have researched this problem to no avail, although I have come up with several near tries (but all misses). Here are just a few variations that I've tried:

Function URL(rngValue As Range)    If rngValue <> "" AND  Then        URL = Application.WorksheetFunction.IfError(rngValue.Hyperlinks.Item(1).Address, "")        ' It works if I catch the value in another cell referencing the answer but this does not.    Else        URL = ""    End IfEnd Function

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

Function URL(rngValue As Range)    If rngValue = "" Or Application.WorksheetFunction.IsError(rngValue.Hyperlinks.Item(1).Address) Then        URL = ""    Else        URL = Application.WorksheetFunction.IfError(rngValue.Hyperlinks.Item(1).Address, "")    End IfEnd Function

I've even tried to check and see if #VALUE! is a string return of '#VALUE!' but this does not seem to be the case either? I've even removed the 'Option Explicit' from the top of the module in case it had to do with back data type conversion.

Also, from the intel I've gathered with experimentation, it would appear as though this when it tries to read the address of a cell that has no URL, it immediately skips any other following code. For instance, the excerpt:

URL = CStr(rngValue.Hyperlinks.Item(1).Address)URL = ""

would immediately return #VALUE!, even though I overwrote it! So maybe this is the key? Interestingly enough, however, it would not appear to be causing any pop-up debugging errors so when I am unable to catch it as a VBA error and an Excel spreadsheet function, it's sort of a limbo error that's burning my side.

I've even tried the following code: When I search for a solution, all I am able to find is an alternative to the logic and not a way to suppress the error.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.