use code from another workbook

[venkman]

How do I use code from another workbook in the current workbook. I have a list class that I'd like to be the return type of a function in another workbook. So in personal.xls I have CMyList. In other.xls I want:

function foo() as CMyList
...
end function

How do I do this?
-Venkman

 

[kylua]

You can allocate a macro from another workbook when you allocate macros to a button or picture. In code I believe it would be along the lines:
Application.Run ("C:\Excel VBA Practice\Finished\Lesson1.xls!Module1.FormatCurrency&quot

Except that doesn't work. It opens the workbook, but won't find the macro.

All in all, it's easier to copy the macro from on workbook, and paste into the other as the even if you do get the above to work, the workbook has to opened before it can be run and if the source workbook has changed name or location, it crashes.

 

[RobBroekhuis]

Kylua - you're right in general, but personal.xls will always be open and available. I wouldn't want to duplicate macros from personal.xls in other workbooks. I couldn't find an easy solution either, so I'll be watching this thread...
Rob

 

[kylua]

Well if personal.xls is always open then

Application.Run ("Personal.xls!Module1.FormatCurrency&quot

Actually worked fine. To use as a function I got this from help:
mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)

 

[venkman]

Still doesn't answer the original question though. I'm looking to import class definitions from personal.xls into the current workbook, not just subroutines and functions.
-venkman