Call Function in another XLAM via Hyperlink Formula - Excel VBA

Question

I'm trying to use this answer, but set it up where the Function is in another xlam workbook.

Example:

This works from remote workbook:

Sub Test()
  FuncName = "#MyFunctionkClick()"
  MyVal = "TestVal"
  Range("A1").Value = MyVal
  Range("A1").Formula = "=HYPERLINK(""" & FuncName & """, """ & Range("A1").Value & """)"
End Sub
Sub TestTwo()
 Application.Run ("'remotewb.xlam'!MyFunctionkClick")
End Sub
Function MyFunctionkClick()
 Set MyFunctionkClick = Selection 'This is required for the link to work properly
 MsgBox "The clicked cell addres is " & Selection.Row
End Function

But I tried this without luck:

Sub Test()
'Application.Run ("'Master Calc with Macro.xlsm'!SummarizeMaster")
'Application.Run ("'remotewb.xlam'!testremote")
'Application.Run ("'remotewb.xlam'!#MyFunctionkClick()")
'Application.Run ("'remotewb.xlam'!MyFunctionkClick") ' When calling from Remote WB it errored if I used ()
 'Range("A1:A5").Formula = "=HYPERLINK(""#MyFunctionkClick()"", ""Run a function..."")"
 ' Range("A1:A5").Formula = "=HYPERLINK(""#MyFunctionkClick()"", ""Run a function..."")"
 Range("A1:A5").Formula = "=HYPERLINK(""[remotewb.xlam]!MyFunctionkClick"", ""Run a function..."")"
 'Range("A1").Formula = "=HYPERLINK(""Application.Run (" 'remotewb.xlam'!MyFunctionkClick")"", ""Run a function..."")"
End Sub

Nucleolated

It is possible to call a function from another workbook, but not directly. I will try imagining a (relevant) scenario and post an answer. I will try calling a function from "Personal.xlsb", but it can be replaced with any workbook containing that function. – Hewlett

Answer 1

Please, try the next scenario:

1. Create a function in that the other workbook. For testing reasons, it should be good to place it in "Personal.xlsb", as I am trying it:

Function GiveMeFive(x As Long, y As Long) As Long
    Debug.Print "In Personal.xlsb code: " & x + y 'not important, ONLY TO SEE IT WORKING with parameters in Immediate Window
    GiveMeFive = 5 'it can be calculated, but look to the function name :)
End Function

2. Create the (necessary) hyperlink in the active sheet (it can be created in any sheet):

Sub TestCalFunctionHyp()
  Dim FuncName As String, myVal As String
  FuncName = "#MyFunctionHyp()"
  myVal = "Call external Function (parameters):4|3" 'just to see how to call it with parameters
  Range("A1").Value = myVal
  Range("A1").Formula = "=HYPERLINK(""" & FuncName & """, """ & Range("A1").Value & """)"
End Sub

3. How the (directly) called (by hyperlink) function should look:

Function MyFunctionHyp()
Dim arr
 Set MyFunctionHyp = Selection
 arr = Split(Split(Selection.Value, ":")(1), "|")
 TestTwo CLng(arr(0)), CLng(arr(1)) 'calling the sub calling the one in the other wb
End Function

4. The sub calling the function in the other workbook should look like:

Sub TestTwo(arg1 As Long, arg2 As Long)
 Dim x As Long
 x = Run("'C:\Users\YourUser\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!GiveMeFive", arg1, arg2)
   Debug.Print "Received from called function: " & x
End Sub

The function calls the function using its full path, only due to the fact that, in case the workbook keeping the function is not open, it will open it...

Please, take care to adapt the path in order to use your real YourUser...

I would like to receive some feedback after testing it. If something not clear enough, do not hesitate to ask for clarifications.

Hewlett

I cant have macro code be stored in the active workbook, my macros are all stored externally and need to be able to run on any open workbook. – Nucleolated

I don't think its possible w hyperlink formula, but for now i did hyperlink, w reference to the active cell and then used follow hyperlinks to catch the event so that should work – Nucleolated

@Nucleolated I am afraid, I cannot get you. How do you mean by "This works from remote workbook"? Isn't "the remote workbook" the active one? If so, isn't it a xlsm type? IF so, you/I can programmatically write the two necessary pieces of code (the function and the sub). I could not understand from your question what you want. It is obvious that it is impossible to directly call a function from another workbook, with a hyperlink formula. – Hewlett

# is shorthand for ThisWorkbook. In my case xlam is where the function is stored, which is never the ActiveWorkbook but just an excel file containing macros. Your explanation is basically to have the function call a function in another workbook, which means I have to store the function in the ActiveWorkbook, but I never have control over ActiveWorkbook. – Nucleolated

@Nucleolated I'm afraid, I still cannot get you... You have all control to ActiveWorkbook running VBA code. It looks exactly opposite: You try controlling the add-in, running one of its function, from another workbook (the active one). I did not understand why this necessity, since the normal way to interact with the adding is to make it exposing some specific buttons on the ribbon, or to run in case of specific intercepted workbooks events. I thought is a fancy way to see if it is possible and I offered a solution for such a situation. – Hewlett

@Nucleolated If you want the user to execute a click in a cell (having a hyperlink formula) and run an add-in function, instead using the orthodox ways of interacting, you can do it as I explained. Automatically (in code) creating the necessary functions, too. I do not understand why will you do it in this way, but I only tried showing that it is possible. – Hewlett

I'm having trouble understanding what part you aren't understanding, but no VBA code can be stored in the ActiveWorkbook. – Nucleolated