Jumping to a certain address
In order to jump to address
N18 on the
same worksheet, the target is simply
#N18:
=hyperlink("#N18"; "Go to N18)
The address can also be specifed in RxCy notation:
=hyperlink("#r18c9"; "Jump to to row 18, column 9")
=hyperlink("#target"; "Go to target")
In order to have a hyperlink to another sheet, the sheet name must be separated from the target by an exclamation mark. (See also
referring to ranges in Excel):
=hyperlink("#SheetName!addr"; "…")
A target in another
workbook can be linked to like so (the linked name is assumed to be
sheet here):
=hyperlink("[C:\users\rene\Documents\workbook.xlsx]sheet!addr"; "…")
Finally, it is even possible to jump to a sub/function declaration in the VBA code with #subname (without parantheses):
=hyperlink("#subname"; "jump to declaration of sub")
Calling a VBA function with a hyperlink
It is possible to call a
VBA function (not a sub!) with a hyperlink by prepending the name of the
target with a hash symbol (
#).
=hyperlink("#hyperlinkClick()"; "go")
The function's return value is a
range object which specifies the cell to which the hyperlink jumps.
Such a function can be declared in a
VBA module like shown below. Because this function returns the current selection, Excel jumps nowhere.
function hyperlinkClick() as range
set hyperlinkClick = selection
msgBox "clicked
end function
The function
must return a
Range object. If no return value is assigned, the function is invoked twice, then the VBA runtime throws the error message
Reference isn't valid.
In addition, it seems that the function must be placed in a VBA-module, not in a «worksheet file»: it possible to call the function with =hyperlink("#sheet1.func()"; …, but eventually, the Reference isn't valid would still be thrown.
It is also possible to pass parameters (references, scalars…) to to the function:
=hyperlink("#funcWithParam( a1)" ; "go")
=hyperlink("#funcWithParam(""hard coded value"")"; "go")