Archive for the ‘Hyperlinks’ Category.

Variable Hyperlinks

4th June 2008, 12:53 pm by Dick Kusleika

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.

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.

Function VIPERLINK(FriendlyName As String, _
    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.

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?

Category: User Defined Functions, Hyperlinks, List and Table Functions, Formulas  |  5 Comments

Deleting Hyperlinks

6th January 2007, 02:17 pm by Dick Kusleika

This Is Broken has a post about hyperlinks in Excel and specifically about how to delete them. I checked the help files and sure enough the post is accurate. That bit of help truly is broken.

At the end of that post is a link to a product that will remove hyperlinks for you. It costs $20. I didn't try it out, but I'm sure that product does a fantastic job, is user-friendly, and is valuable to those who buy it.

If I wanted to delete hyperlinks, I'd do this:

Sub RemoveHyperlinks()
   
    Dim hl As Hyperlink
    Dim ws As Worksheet
   
    For Each ws In ActiveWorkbook.Worksheets
        For Each hl In ws.Hyperlinks
            hl.Delete
        Next hl
    Next ws
   
End Sub

But that may not catch them all.

Category: Links, Products, Hyperlinks  |  15 Comments

Hyperlinks and Network Shares

12th February 2006, 11:32 pm by Rob van Gelder

The other day I wanted to insert a hyperlink to another spreadsheet.

I hardly ever put Hyperlinks into Spreadsheets - I almost always link to a web page.
This time, however, I wanted to link to a document on the file server.

Insert - Hyperlink - Double click the file - done!
"Wow! that was easy" I thought, then promptly attached and e-mailed the spreadsheet to the customer.

"Great spreadsheet" was the feedback, "but the hyperlink is broken - it's pointing to my C: drive!"

It appears as though hyperlinks are stored by relative path (rather than absolute path) when the linked document is on the same Network Share.
His e-mail program had saved the attached spreadsheet to C:\Temp before opening it, so the hyperlink also looked to the C: drive.

When I hover my mouse pointer over a hyperlink, the tool tip reveals the true path.

Here is me hovering over the hyperlink from my computer:

Here is my customer hovering over the hyperlink from their computer:

Fixed it - Here is my customer hovering over the fixed hyperlink from their computer:

What's the difference between the first and last hyperlink?
The last hyperlink is actually a formula which forces an absolute path:
=HYPERLINK("Z:\SpecialFile.xls")

Category: Worksheet Functions, Hyperlinks  |  4 Comments

Return the hyperlink

26th May 2005, 10:02 am by Juan Pablo Gonzalez

To get the hyperlink of a cell you can use a simple UDF, like this:

Function RETURNHYPERLINK(Rng As Range) As String
   If Rng.Hyperlinks.Count> 0 Then
      RETURNHYPERLINK = Rng.Hyperlinks(1).Address
     
      If Len(RETURNHYPERLINK) = 0 Then
         RETURNHYPERLINK = Rng.Hyperlinks(1).SubAddress
      End If
   End If
End Function

You can then use it in your worksheet like

=RETURNHYPERLINK(A13)

I check for the Address and the SubAddress properties because a hyperlink to a "place in this document" for example is not stored in the Address property (which appears empty), but in the SubAddress.

Category: VBA Basics, MVP, VBA Code Library, User Defined Functions, Hyperlinks  |  2 Comments