Variable Hyperlinks

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.

=HYPERLINK(VLOOKUP(“%MainServer%”,EnvVars,2,FALSE)& VLOOKUP(“%docs%”,EnvVars,2,FALSE)&”UserGuide.pdf”)

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.



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.

Function VIPERLINK(FriendlyName As String, _
    ToolTip As String, _
    ParamArray Paths() As Variant) As String
    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
        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.

Function ENVIROLINK(ParamArray Paths() As Variant) As 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?

Posted in Uncategorized

7 thoughts on “Variable Hyperlinks

  1. 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.

  2. 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:



  3. 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.

  4. “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.

  5. 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:

    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:
    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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.