GetURL

[handysmurf]

I have a sub to display the URL of a link for me. But it sometimes returns a 0 instead of the link.

Public Function GetURL(Cell As Range, Optional default_value As Variant) As Variant
Dim output As Variant
If (Cell.Range("A1").Hyperlinks.Count <> 1) Then
output = default_value
Else
output = Cell.Range("A1").Hyperlinks(1).Address
End If
GetURL = output
End Function

Help ... it's annoying.

 

[Micron]

Don't know why you sometimes get zero as a return value but "Cell" as a function parameter is a variable that is a range object, so your code is saying
If(range object.Range("A1") which doesn't make sense to me, especially if 'range object' is not actually cell A1. However, if Cell is a multi column/row range then rng would be more intuitive as to what the variable could contain but again, I see the possibility that A1 is not a cell in such a range. Perhaps that is why you sometimes get a zero. Anyway, using what are commonly called 'reserved words' for any object name is a bad idea. Not that it matters to the issue, but you don't need the variable 'output' either.

I suggest you step through your code (F8) or put a break point in it and run it to see if the variables and references behave as you expect. Or change the function return data type to String if both parameters are strings. Taking the above into account, perhaps:

Public Function GetURL(Cel As Range, Optional default_value As Variant) As String
     If (Cel.Range("A1").Hyperlinks.Count <> 1) Then 
         GetURL = default_value
     Else
         GetURL= Cel.Range("A1").Hyperlinks(1).Address
     End If
End Function

Please post more than a few lines of code with code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[handysmurf]

All gibberish to me. I wish I understood all the code but I don't. This code is one I found to accomplish the goal.

 

[Micron]

So post some data for others to play with? No pics of data though because that forces responders to type it all out. Use the XL2BB app or just copy a range and paste it in a post. The app will retain formulas and such whereas a simply paste will not.

 

[handysmurf]

06/16/2023	Link	D:\OneDrive\Owen Divorce\1 (17).jpg
06/16/2023	Link
06/16/2023	Link
06/16/2023	Link

 

[handysmurf]

Public Function GetURL(cell As Range, Optional default_value As Variant) As Variant
Dim output As Variant
If (cell.Range("a1").Hyperlinks.Count <> 1) Then
output = ""
Else
output = cell.Range("a1").Hyperlinks(1).Address
End If
GetURL = output
End Function


Ignore this ... I was trying to figure out how to post it properly

 

[Micron]

Not what I had in mind. No idea of what column header is up there anywhere; no idea how A1 fits into that or what significance any of it is to your sub.

 

[handysmurf]

Date Link Disclosed File

05/31/2023
05/31/2023
05/31/2023 Link D:\OneDrive\Owen Divorce\1 (1).jpg
05/31/2023 Link D:\OneDrive\Owen Divorce\1 (1).png
06/01/2023
06/01/2023
06/01/2023 Link D:\OneDrive\Owen Divorce\1 (2).png
06/01/2023 Link D:\OneDrive\Owen Divorce\1 (3).jpg
06/01/2023 Link D:\OneDrive\Owen Divorce\1 (3).jpg

well now it isn't keeping the format ... Date is A, Link = B, Disclosed File = C

rows don't matter as it has a link on every row

 

[Micron]

Like this then?

A	B	C
5/31/23​	Link	D:\OneDrive\Owen Divorce\1 (1).jpg
5/31/23​	Link	D:\OneDrive\Owen Divorce\1 (1).jpg
5/31/23​	Link	D:\OneDrive\Owen Divorce\1 (1).jpg
5/31/23​	Link	D:\OneDrive\Owen Divorce\1 (1).png
6/01/23​	Link	D:\OneDrive\Owen Divorce\1 (1).png
6/01/23​	Link	D:\OneDrive\Owen Divorce\1 (1).png
6/01/23​	Link	D:\OneDrive\Owen Divorce\1 (2).png
6/01/23​	Link	D:\OneDrive\Owen Divorce\1 (3).jpg
6/01/23​	Link	D:\OneDrive\Owen Divorce\1 (3).jpg

You still haven't explained about A1 (why the code is only looking for a hyperlink in A1 when you have many rows). Now that you've said A contains the date, it makes no sense. No row in column A will ever have a hyperlink if the cell value is a date so the logical test will always be false.

 

[handysmurf]

"You still haven't explained about A1 (why the code is only looking for a hyperlink in A1 when you have many rows). Now that you've said A contains the date, it makes no sense. No row in column A will ever have a hyperlink if the cell value is a date so the logical test will always be false."

Yes like that. I tried to change the references to B in the script but that changed nothing. A pitfall of not understanding code and just pasting it from a source but I don't have time to learn code. sigh.

Yea I have no idea how it ever works at all because as you said there are no links in column A