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.

How Not to Learn Spanish

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

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

snb’s VBA for Smarties: Arrays

The Encyclopedia of Dictionaries

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:

– 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

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
Big Ben, Westminster
Here’s the agenda.

Check out this gem from the agenda:

15:30 – 16:30
VBA to C : Pratfalls and Perils
– Stories based on a c++ newby’s efforts to convert 10K lines of VBA UDFs to
C++ XLLs.

If that doesn’t sound interesting to you, you’re just not trying.

Pay the fee here and somebody live blog the event. I can’t make it because I’ll be in Cancun that week but I’ll still be thinking about VBA.