Hyperlinks in VBA

I have programmed in MS Excel using VBA (Visual Basic for Applications) fairly regularly over the past 25+ years. This makes me experienced but not necessarily expert. Frustration over the past several days seems to have demonstrated the lack of expertise, particularly in handling hyperlinks between Excel workbooks.

The project involved building a hyperlink bridge between specific pages/worksheets within two workbooks. The environment wasn’t too difficult since both workbooks would be housed in the same directory. However, the two workbooks would have a parent / child relationship.

The code was designed to build a new control worksheet in the parent workbook and hyperlink it to a specific worksheet and cell range in a child workbook book. The child workbook would be generated entirely in code before being saved.

This didn’t sound too difficult and it wasn’t; until it came to trying to create the hyperlinks between pages of the different workbooks.

The first thing to note is that hyperlink functionality is not native to VBA (for Excel). It is effectively a scripting function for HTML. Also, although the Microsoft Office software elements (e.g. Word, Excel and Access) use identical scripting functions, the SubAddress nomenclature elements vary according to the software used. For example, the SubAddress for Word employs “bookmarks” whereas in Excel we use cell addresses or “names”. Furthermore, the structure of a Word file is fundamentally different from an Excel file. Word files have a single pot, whereas Excel files have multiple worksheets which act as separate pots.

These differences, although apparently small and insignificant, make programming hyperlinks in Excel’s version of VBA frustratingly difficult.

Programming a Hyperlink in Excel

In Excel the hyperlink is programmed for a worksheet or a specific range, but not the workbook. See the example “ThisControlSht.Hyperlinks.Add”, where ThisControlSht is a worksheet variable generated earlier in the program.

The Hyperlinks.Add function has several parameters – Anchor, Address, SubAddress, ScreenTip and TextToDisplay. Each parameter has its own elements which may not be obvious at first glance.

Anchor must be a range. It is the cell in which you wish to place the hyperlink. So either Anchor:=ThisControlSht.Range("MapControlsheet") or Anchor:=ThisControlSht.Range(B5) will work. In this case the cell B5 was named “MapControlSheet”. However, Anchor≔”=MapControlSheet” does NOT work even though you may appear to be in the correct environment.

Address is usually a website URL but in our case we are linking to another Excel file. For security I employ the full path of the file address and name (including suffix of the file).

SubAddress allows you to specify both the worksheet and particular range of the target. In the Excel environment “names” can be set for the Workbook or specific to a particular Worksheet, but you must ignore that distinction when specifying a SubAddress. You need to specify both the Worksheet and the range. The range can be in the form of a cell address like B5 or a named range like SubAddress:="=DataList_and_MappingIndex!MapListMappingIndex". Note that the sheet name and the range are separated by “!”.

TextToDisplay is an optional parameter and is usually only employed when the cell is empty. However, in the context of this particular program the workbooks / worksheets were being generated in code and therefore all cells were empty unless you had already coded some value to be posted in. In this case I hadn’t. In testing, the program fell over until I had placed some suitable value in the  TextToDisplay.

I would not have got even this far without Excel’s macro recorder. That process generated this code.

Sub Macro2()

'

' Macro2 Macro

'

'

    Windows("My_Test_Book.xlsx").Activate

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _

        "My_Test_Book_MapBook.xlsx", SubAddress:="DataList_and_MappingIndex!A1", _

        TextToDisplay:="My_Test_Book_MapBook.xlsx#DataList_and_MappingIndex!A1"

End Sub