Excel provides an easy way to create a table of hyperlinks and even save the workbook as a web page. The built-in method for creating a hyperlink is Insert > Hyperlink (Ctrl+K). The problem with this method is mass changes, like renaming your server. Editing each one (if you have a lot) would be out of the question, but you could certainly write a macro to do it.
One of the engineers in my office asked why we couldn’t just define an environment variable somewhere and reference that. Well, we can, in a way.
By using the HYPERLINK worksheet function rather than Insert > Hyperlink, I can put some variables in a table and use VLOOKUPs to build the link.
Before
After
That’s pretty good. I can put my environment variables into their own workbook so I can refer to them from many different sheets and workbooks. One downside is that I can’t specify a tooltip when using the HYPERLINK worksheet function. Another downside is that the formula can get a bit long and hard to manage.
I’d like to create my own version of HYPERLINK, but I don’t know how. I’m just going to go right ahead and say it’s impossible to return a hyperlink from function called from a cell. There. Now one of you can prove me wrong. Here’s my best attempt so far – but it has problems.
ToolTip As String, _
ParamArray Paths() As Variant) As String
Application.Volatile
Dim i As Long
Dim sLink As String
Dim oLink As Hyperlink
For i = LBound(Paths) To UBound(Paths)
sLink = sLink & ConvertVar(Paths(i))
Next i
If Len(FriendlyName) = 0 Then
FriendlyName = sLink
End If
If Len(ToolTip) = 0 Then
ToolTip = sLink
End If
Application.Caller.Hyperlinks.Add Application.Caller, _
sLink, , ToolTip, FriendlyName
VIPERLINK = FriendlyName
End Function
Private Function ConvertVar(ByVal sVar As String) As String
Dim rFound As Range
Set rFound = Sheet1.Range(“EnvVars”).Find(sVar, , xlValues, xlWhole)
If Not rFound Is Nothing Then
ConvertVar = rFound.Offset(0, 1).Value
Else
ConvertVar = sVar
End If
End Function
Called like =VIPERLINK("","Click here","%MainServer%","%docs%","UserGuide.pdf").
ConvertVar is just a placeholder function right now to prove the concept. It definitely needs to be refactored. Problem #1 is that I’m changing a property of the cell that isn’t the Value property. That’s generally a no-no, but we’re all about pushing the envelope here at DDoE. Note that my UDF doesn’t return a Hyperlink object, but rather a String.
Problem #2 is that deleting the contents of the cell doesn’t clear the hyperlink-like formatting (blue with underline). Similarly, (problem #3 if you’re counting) replacing the formula with simple text doesn’t delete the hyperlink, it just changes the TextToDisplay.
Maybe those shouldn’t be deal-breakers, but I don’t think I’m getting that much from this function. I get a Tooltip – big whoop. I get a little-easier-to-write function – nice, but necessary? Maybe I just forget about the Tooltip and make a function that returns the link string.
Dim i As Long
Dim sLink As String
For i = LBound(Paths) To UBound(Paths)
sLink = sLink & ConvertVar(Paths(i))
Next i
ENVIROLINK = sLink
End Function
Used like =HYPERLINK(envirolink("%MainServer%","%docs%","UserGuide.pdf"))
Then I don’t have to worry about orphaned formatting and I get a slightly more manageable worksheet formula. What do you think? Worth it?
Google has a brilliant solution.
Here is an example: http://groups.google.se/group/gapminderworld/browse_thread/thread/d69fb8c6a48081fe
I think there are a couple of options here. First, you could fake cells to look like hyperlinks and trap the cell click (you would need a way of deciding which cells had hyperlinks, and which hyperlink applied, eg via a lookup table or range name). The simplest might be to set up a real hyperlink that did nothing, and trap the hyperlink event, but I haven’t managed to create a hyperlink that will stay in the same cell when clicked.
To make it easy for users, you could possibly write code that sets up a (dynamic) hyperlink when they doubleclick on a cell. It would simply ask them for the location of the cell containing the formula for the hyperlink, and the code would manage the rest.
Second, you could use objects. I’ve often used transparent text boxes with code behind them. Of course, that’s a bit harder for users.
First off, i’m not sure where the problem with the tooltip comes – doesn’t excel create the link and use it’s standard hyperlink tooltip – “url – click here to follow… etc”? And the formatting thing to me would be a big issue.
i think option 2 is good. You could even just parse out a standard syntax url:
=HYPERLINK(parselink(“//%MainServer%/%docs%/UserGuide.pdf”))
Rob
Dick: Nice idea. A couple of comments. I suspect you’ve considered them and I’m curious as to why you rejected them.
In the UDF version, why pass the ENVIRON variables to it? Can’t the UDF use the Environ() function to get the same information?
In the worksheet version, you could simplify the formula with named formulas that are the 2 VLOOKUPs you use. Or you could skip the VLOOKUPs and name each of the cells containing the appropriate environment variables.
“Can’t the UDF use the Environ() function” – Not unless I can customize the environment variables table. Mine are custom environment variables, not built-in ones.
“and name each of the cells” – My thinking was that I want to add and subtract variables from the list. I would have to add and subtract names too.
Hi!
Here is a solution to your problem…. Make the first entry in the hyperlink function a cell reference! Then you can use your own functions to write the url into the referenced cell. Here is an example:
CELL A1: =hyperlink(D1, “click here”)
CELL D1: http://www.ibm.com
Clicking on cell A1 launches the browser to the IBM home page!
Note no quotes in cell D1 and the “http://” is required.
This also works for things like PDF files when the value in D1 is “c: emp est.pdf”. Again no quotes. When you click the link the PDF is opened.
Of course D1 could be in another workbook, etc.
You can even have the description be a cell reference! So things like
CELL A1: =hyperlink(B1, C1)
CELL B1: http://www.ibm.com
CELL C1: IBM
Shows IBM in cell A1 and launches the browser to the IBM home page.
By using cell references you can use you own functions to build the values.
This was tested on Excel 2002.
Dave
“I’d like to create my own version of HYPERLINK, but I don’t know how.”
Please consider the SuperLink user-defined function discussed here: http://dailydoseofexcel.com/archives/2018/07/02/hyperlink-formula-events/#comment-1045646