Hyperlink Keyboard Shortcut Update

I have a custom keyboard shortcut, Ctrl+L, to “click” on a hyperlink in Excel. I thought I had posted that code, but I can’t find it. It’s not much.

It doesn’t work with links created with the HYPERLINK function because a formula doesn’t create a member of the Hyperlinks collection. I fixed it by parsing the formula and trying to follow the link inside.

Here’s an example of a HYPERLINK formula I use.

This links to a file named CurrentFileName_Backup.pdf. The first thing the code does is remove the last parenthesis.

Next it removes the function name. It doesn’t remove the equal sign because I’ll need that for the Evaluate function later.

Next it splits the string on a comma. A comma will separate the link argument from the friendly name argument. This one has more than one comma, but we’ll deal with that later by Joining the array.

The friendly name argument is optional. This example has a friendly name, but not every one will. To determine if the friendly name argument is used, I try to evaluate the string. A legitimate formula with a

after it won’t evaluate and will return an error. If that’s the case, I remove the last element of the array and evaluate the remaining elements.

In this case, the Redim Preserve gets rid of element #5, but leaves the others intact. The remaining five elements are joined into

The Evaluate function turns into the result of the formula (the file is named JE35.xlsm).

Passing that to FollowHyperlink opens the file. Unless it doesn’t exist. Then I get an error “Cannot open the specified file.” and a message box tells me the file name it tried to open. That way I can troubleshoot any problems before I go ask why the backup wasn’t included in this journal entry.

4 thoughts on “Hyperlink Keyboard Shortcut Update

  1. Nice – will add to my list of shortcuts. One possible simplification could be to replace “HYPERLINK(” with “IF(1,” then I’m not sure parsing would be necessary since the friendly name will be ignored if it is present.

    And good to see you back, dick..

  2. Genius! Thanks Lori. That code just got a lot simpler.

  3. I`m guessing there is a hard-won reason for the way you specify the ReDim as:

    And not like this:

  4. O.k. Now Ive RTFMd:

    “…the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.”

    That`s what happens when you spend too much time in VBScript!

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax