PDA

View Full Version : Solved: Referencing UDF from another workbook; Evaluate?



Digita
07-09-2008, 11:04 PM
Hi guys :hi: ,

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
07-09-2008, 11:50 PM
=Book2.xls!test(A1,B1)

Digita
07-10-2008, 12:12 AM
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:

Function ShCount()
ShCount = ActiveWorkbook.Sheets.Count
End Function

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


If ShCount > 15 then .....

Regards


kp

mdmackillop
07-10-2008, 12:31 AM
This is how you call a sub. I've never tried to call a function.

c = Application.Run("book2!test", Range("A1"), Range("B1"))



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

mdmackillop
07-10-2008, 12:41 AM
Try

'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

Digita
07-10-2008, 04:50 AM
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
07-10-2008, 03:44 PM
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
07-10-2008, 06:00 PM
Thanks again MD. Thread now noted as solved.

Cheers


kp