Solved: Referencing UDF from another workbook; Evaluate?

[Digita]

Hi guys ,

Not sure if this is feasible. I have an XL file containing a custom UDF. This particular file is open concurrently with another workbook. Would there be some way to make the UDF available to any other workbook? I only need to use this UDF on rare occasions so I guess, rather copying the UDF in question to the new workbook to work on, I want to do a short cut here.

Thanks in advance for your thoughts on this.

Regards



kp

[mdmackillop]

[VBA]=Book2.xls!test(A1,B1)[/VBA]

[Digita]

Hi MD,

Thanks for your quick response. Sorry I don't quite get it.
Adopting the suggested solution I get a popup file open window. This is an example. Say, I have the following UDF in a file:

[VBA]Function ShCount()
ShCount = ActiveWorkbook.Sheets.Count
End Function[/VBA]

How do I make reference to this UDF in a code from another workbook which is also active? For instance:

[VBA]
If ShCount > 15 then .....[/VBA]

Regards


kp

[mdmackillop]

This is how you call a sub. I've never tried to call a function.
[vba]
c = Application.Run("book2!test", Range("A1"), Range("B1"))

[/vba]

My previous post was intended to be entered on a spreadsheet.

[mdmackillop]

Try
[VBA]
'In Book 1
Sub Macro1()
c = Evaluate("=Book2.xls!test(B11, D11)")
End Sub
'In Book 2
Function Test(ff1, ff2)
Test = ff1 * ff2
End Function
[/VBA]

[Digita]

Brilliant. Thanks MD. Forgot about the old Evaluate - a very handy function indeed. Really appreciate your help MD.

Have a great day.

Regards


kp

[mdmackillop]

Glad it worked out!
Please mark it solved using the Thread Tools dropdown.
Regards
MD
PS
I've amended your title just to help any future searches.

[Digita]

Thanks again MD. Thread now noted as solved.

Cheers


kp