Programming Excel with VBA and .NET

Use Hyperlinks

Most of us think of hyperlinks as links that take you to a web page when you click them; Excel uses a broader definition. Yes, Excel hyperlinks can take you to a web page, but they can also:

• Create a new document or open an existing one for editing

• Take you to a reference in an Excel workbook

• Compose an email

To create a hyperlink in Excel:

1. Right-click a cell.

2. Choose Hyperlink from the pop-up menu.

3. Choose the type of link and set the link properties in the Edit Hyperlink dialog box (Figure 10-2).

Figure 10-2. Creating a hyperlink in Excel

To create a hyperlink in code, use the Hyperlinks collection’s Add method. Like comments, hyperlinks are anchored to a cell address that you specify in Add. For example, the following code adds a link at cell A3 to my web site:

  ActiveSheet.Hyperlinks.Add [a3], "http:\\excelworkshop.com\", _
    , "Go to Jeff's site.", "Excel Workshop"

To link to a location on a worksheet , set the Add method’s Address argument to "" and the SubAddress argument to the target location. SubAddress has this format:

sheetName!targetAddress

However, the targetAddress part can’t include dollar signs, like normal Excel addresses. To use normal Excel addresses, you must strip out the dollar signs using VBA.Replace. For instance, the following code adds hyperlinks that link to the first and last cells of a worksheet; the ConvertAddress helper function reformats the ...