MS Excel HYPERLINK() function with dynamic link_location does not work - why?

Question

I am trying to include the spreadsheet's own filename in an e-mail body dynamically created with the following formula including a VBA Module (?) function:

=HYPERLINK("mailto:"&"address"&"?subject="&B7&"&body="&B7&FullName(); "E-Mail")

The function is

Function FullName() As String
    FullName = ThisWorkbook.FullName()
End Function

in a "module" of the document (I don't have a clue about VBA: ALT+F11, Insert Module. Apparently this is also called user defined function (UDF)).

The function works on its own in a cell, the HYPERLINK formula works without adding to its link_location the function, but together (as above), it evaluates to

#NAME?

Links:

Office docs: HYPERLINK function
possibly related, but somewhat reverse problem: VBA to open Excel hyperlink does not work when hyperlink generated with a formula
Hyperlink custom link_location formula is not dynamic
Very similar problem: Creating a custom hyperlink function in excel

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

Follow this answer from the latter link in the question (do not use HYPERLINK formula, instead a function changes the manually created link in the cell).

edited May 23 '17 at 12:22

Community

1
1

answered Jun 23 '15 at 16:08

handle

5,859
3
54
82

Depending on settings, this cell/function may not be evaluated automatically. Forcing this for the cell with `Calculate` does not seem to always work when called in the `Workbook.BeforeSave` event / function. Modifying the settings there does work for me, e.g. http://stackoverflow.com/a/29094142/1619432 – handle Jun 23 '15 at 17:13

MS Excel HYPERLINK() function with dynamic link_location does not work - why?

1 Answers1