Skip to main content

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


Comments

Post a Comment

Popular posts from this blog

Generating the "ACBA-Mapping" Add-In

Background This Add-In is designed to map the underlying structure of an Excel worksheet. It. provides the means of reviewing the way that a worksheet operates both in terms of its layout and the relationship between blocks of formulae. The program for the Add-In can be downloaded from the link ' ACBA-Mapping '. Please read the following description of the process before attempting to activate it.  Generating 'ACBA-Mapping' The 'ACBA-Mapping' link will download the Add-In file to your personal Downloads folder. The File Explorer icon on your Task Bar, is the entry point. From your TaskBar Clicking this will open File Explorer. You want the Downloads folder - encircled in green. File Explorer normally opens to the 'Quick access' panel in the first instance. The Downloads folder is usually ordered youngest to oldest. So 'ACBA-Mapping' should be right at the top. The Downloads Folder ' ACBA-Mapping ' contains active code, which will have bee...
Read more

Big Brother at a Distance

We know that our spreadsheets should be functional and error free, but there is  a tension between how we review a spreadsheet to ensure that it is correct and the functionality of the spreadsheet itself. None of us like Big Brother breathing down our necks too closely. In What is a Cell Map?  I discussed the creation of a simplified version of a spreadsheet to aid review and error trapping. However, the map is itself a spreadsheet. If you created maps for each worksheet in your workbook, the workbook would quickly bloat and become unmanageable. The obvious answer is to post your maps somewhere else, but clearly linked to primary workbook. The ACBA-Mapping package does just that. It generates a separate Map Book to hold all the spreadsheet maps (and other analyses) associated with the parent workbook. The parent workbook has a new worksheet that maintains a summary of all the detailed analyses generated for the workbook. This new worksheet is called 'DataList_and_MappingC...
Post a Comment
Read more

Cell Maps - Critical Comments

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected. The cell mapping software provides a method for recording a reviewer's comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet. In this case, there's just one comment. The comment above refers to the area highlighted towards the bottom of the map below and are hyperlinked.to the specific area of the map through the column headed 'Map/Source Link'. The top left cell the source range provides a link back to the 'Comments. worksheet'. The yellow cells suggest that users should be entering information for collection by a formula, but which one.  At the this point I went back to the orginal worksheet and traced the dependents for some of these 'so called' user entry cells - also coloured yellow as it ...
Post a Comment
Read more