VBA Return Hyperlink Function

[ryan.bott]

I'm having a problem writing a VBA function. I want to call a function that looks something like this... GetLink("CCF-2000",K18) where the CCF-2000 is a project folder on my computer and K18 references a code related to the city. Using K18 and the Case function I'll differentiate between different file paths related to different cities.

Anyways, I got the Case function to work properly, but when I try to run the Excel Hyperlink function in VBA it returns #VALUE. I want it to return something simliar to =HYPERLINK("G:\CCF-2000", "CCF-2000"), but using a function to figure out the path based on the city code.

The K column only contains data such as SG, SP, LE, ENW, etc... that specify a city. Each city has it's own path on the local computer.

Here's the function I was trying to build to accomplish this. I wanted to call this function in another column... AC, to return a hyperlink, but to no avail.

Function GetLink(Filename as String, City as String) as String

Select Case City

Case "ENW", "ENE", "ESW", "ESE"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\" & Filename, Filename)
Case "SG"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\SG\" & Filename, Filename)
Case Else
GetLink = "Escape"
​

End Select

Hopefully, there's something in there that makes sense. It's my first VBA function, so hopefully that's not too far from the truth.