Hyperlink Keyboard Shortcut Update

Posted on May 11, 2016May 10, 2016 by Dick Kusleika

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.

If ActiveCell.Hyperlinks.Count > 0 Then
    ActiveCell.Hyperlinks(1).Follow
End If

If ActiveCell.Hyperlinks.Count > 0 Then

ActiveCell.Hyperlinks(1).Follow

End If

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.

Public Sub FollowLink()
    
    Dim vaSplit As Variant
    Dim sForm As String
    
    Const sLINKFORM As String = "=HYPERLINK("
    
    On Error GoTo ErrHandler
    
    If ActiveCell.Hyperlinks.Count > 0 Then
        ActiveCell.Hyperlinks(1).Follow
    Else
        If InStr(1, ActiveCell.Formula, sLINKFORM) = 1 Then
            sForm = ActiveCell.Formula
            sForm = Left(sForm, Len(sForm) - 1) 'remove last parent
            sForm = Replace(sForm, Mid(sLINKFORM, 2, 255), vbNullString) 'Remove function name
            vaSplit = Split(sForm, ",")
            If IsError(Evaluate(Join(vaSplit, ","))) Then 'friendly name argument used
                ReDim Preserve vaSplit(0 To UBound(vaSplit) - 1)
            End If
            ActiveWorkbook.FollowHyperlink Evaluate(Join(vaSplit, ","))
        End If
    End If
    
ErrExit:
    On Error Resume Next
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description & vbNewLine & Evaluate(Join(vaSplit, ","))
    Resume ErrExit
End Sub

Public Sub FollowLink()

Dim vaSplit As Variant

Dim sForm As String

Const sLINKFORM As String = "=HYPERLINK("

On Error GoTo ErrHandler

If ActiveCell.Hyperlinks.Count > 0 Then

ActiveCell.Hyperlinks(1).Follow

Else

If InStr(1, ActiveCell.Formula, sLINKFORM) = 1 Then

sForm = ActiveCell.Formula

sForm = Left(sForm, Len(sForm) - 1) 'remove last parent

sForm = Replace(sForm, Mid(sLINKFORM, 2, 255), vbNullString) 'Remove function name

vaSplit = Split(sForm, ",")

If IsError(Evaluate(Join(vaSplit, ","))) Then 'friendly name argument used

ReDim Preserve vaSplit(0 To UBound(vaSplit) - 1)

End If

ActiveWorkbook.FollowHyperlink Evaluate(Join(vaSplit, ","))

End If

ErrExit:

On Error Resume Next

Exit Sub

ErrHandler:

MsgBox Err.Description & vbNewLine & Evaluate(Join(vaSplit, ","))

Resume ErrExit

End Sub

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

=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup")

1	=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup")

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

=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

1	=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

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

=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

1	=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup"

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.

vaSplit(0) = =LEFT(SUBSTITUTE(CELL("filename")
vaSplit(1) = "["
vaSplit(2) = "")
vaSplit(3) = FIND("."
vaSplit(4) = CELL("filename"))-2)&"_Backup.pdf"
vaSplit(5) = "Backup"

vaSplit(0) = =LEFT(SUBSTITUTE(CELL("filename")

vaSplit(1) = "["

vaSplit(2) = "")

vaSplit(3) = FIND("."

vaSplit(4) = CELL("filename"))-2)&"_Backup.pdf"

vaSplit(5) = "Backup"

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

, friendly_name

1	, friendly_name

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

=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf"

1	=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf"

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

S:\Accounting\General_Ledger_Information\201606\JE35\JE35_Backup.pdf

1	S:\Accounting\General_Ledger_Information\201606\JE35\JE35_Backup.pdf

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.

How Not to Learn Spanish

Posted on January 16, 2015January 15, 2015 by Dick Kusleika

I’ve been trying to learn Spanish for almost 20 years. Of the seven words I know, four are swear words. So when I tell you how not to learn Spanish, you can take it to the bank.

I signed up for Carlos Muñiz’s RSS feed. It’s in Spanish. Every so often I open the next post and see if I can translate. I’m not very good. I’m also not sure I’m learning anything, but it’s kind of fun like a puzzle. Once I’ve completed my translation (with plenty of guessing), I go to translate.google.com and see how I did.

Post 4

Title: Escribir metros cuadrados con subíndice

DK: To write meters words with subscript

Google: Write square meters with subscript.

I’d like to think I would have got subíndice, but actually the picture helped me out. It didn’t help enough that I thought to look at what was in the cell, because I probably could have got cuadrados instead of punting and guessing it was something related to ‘word’. ‘Word’ is ‘palabra’. I told you my Spanish sucked.

Body: Para escribir metros cuadrados con subíndice es muy fácil del texto en la celda seleccionar sólo el número 2, pulsar click derecho del “mouse” ir a formato de celdas y en efectos seleccionar subíndice.

DK: To write square meters in subscript is very easy. In the cell select only the number two, click the right mouse button to go to cell formats and select subscript.

Google: To write sqm with subscript text easily select only the cell number 2, press right click the “mouse” go to format cells and select subscript effects.

I’m always inserting punctuation into Carlos’ sentences instead of trying to figure out what it really says – my ‘is very easy’ should be ‘easily’. And I glossed over ‘en efectos’ because I didn’t know what it meant. Now I see that the frame in the dialog is captioned Effects.

The posts are a reasonable length so it doesn’t take an hour to translate. And there’s usually a picture which provides some valuable clues. Plus I have a little domain knowledge, which I’m sure doesn’t hurt.

A Daily Overdose of Arrays

Posted on October 15, 2014 by Dick Kusleika

snb, who wrote the detailed piece on dictionaries, has applied the same rigor to arrays.

I knew there would be something in there that I didn’t know (there was plenty). Under the section about identifying the existence of an item in an array, you can use a defined name:

6.2.2.3 The Excel-object ‘Names’

Assign the array to a Name’.
Excel creates an arrayformula, consisting of a string.
The item can be searched for in that string.
Embed the search string in quotation marks.
Embed numerical values in commas.

Visual Basic

Names.Add "snb_002", a_sn msgbox InStr(Names("snb_002").Value, """abcde""")>0 msgbox InStr(Names("snb_002").Value, ",1245,")>0

1
2
3

Names.Add "snb_002", a_sn
msgbox InStr(Names("snb_002").Value, """abcde""")>0
msgbox InStr(Names("snb_002").Value, ",1245,")>0

snb’s VBA for Smarties: Arrays

The Encyclopedia of Dictionaries

Posted on October 22, 2013October 24, 2013 by Dick Kusleika

Frequent commenter snb has written a thorough page on the Scripting.Dictionary object. I resisted Dictionaries for the longest time, but lately I’ve been using them in almost every situation where I would have used a Collection object. Here’s the table of contents:

Contents
– What is a dictionary ?
– What is the purpose of a dictionary ?
– Where does the Dictionary derive from ?
– How to create a Dictionary ?
– How to populate a Dictionary ?
– Add or replace ?
– Keys
– When is a key unique ?
– Create unique keys automatically
– A list of unique elements
– Items
– The number of elements in a Dictionary
– Check the existence of a key / item in the Dictionary
– How to retrieve 1 element from the Dictionary ?
– How to use the array .Keys ?
– How to use the array .Items collection ?
– Change the key of an item
– Copy an item inside the Dictionary
– Remove an item from the Dictionary
– Adapt the contents of an item in the Dictionary
– Remove all items from the Dictionary
– Early binding and late binding
– Examples

If you use Dictionaries, you should bookmark this page. If not, you should start.

UK Excel User Conference

Posted on December 14, 2011 by Dick Kusleika

If you’re in or around London in late January, don’t miss the UK Excel User Conference. It’s Wednesday, January 25th at Skills Matter

Here’s the agenda.