0

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
FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57
  • 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. – FaneDuru Oct 16 '21 at 10:04

1 Answers1

0

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
  1. 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
  1. 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
  1. 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.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • 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. – FreeSoftwareServers Oct 16 '21 at 19:18
  • 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 – FreeSoftwareServers Oct 16 '21 at 19:19
  • @FreeSoftwareServers 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. – FaneDuru Oct 16 '21 at 19:28
  • `#` 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`. – FreeSoftwareServers Oct 17 '21 at 01:56
  • @FreeSoftwareServers 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. – FaneDuru Oct 18 '21 at 11:32
  • @FreeSoftwareServers 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. – FaneDuru Oct 18 '21 at 11:34
  • I'm having trouble understanding what part you aren't understanding, but no VBA code can be stored in the `ActiveWorkbook`. – FreeSoftwareServers Oct 18 '21 at 19:12