Calling Function in Code Module of Foreign WorkBook 1

[HughLerwill]

Dear all,

Further to thread707-1332233 I've been trying to get the third verse of this to work, without any luck.

Sub RunMac()

'This macro in Book1
'Book1, Sheet1 is active

'call function in sheet of foreign WorkBook - works
ActiveSheet.Cells(1, 1) = Workbooks("Book2.xls").Worksheets(1).myfunction1(1)

'call function in ThisWorkBook of foreign WorkBook - works
ActiveSheet.Cells(2, 1) = Workbooks("Book2.xls").myfunction2(2)

'call function in MyModule of foreign WorkBook - will not work
'ActiveSheet.Cells(3, 1) = Workbooks("Book2.xls").VBAProject.MyModule.myfunction3(6)

End Sub

The myfunction1, 2 and 3 procedures in Book2 are all Public

Can anyone tell me the correct syntax.

regards Hugh,

 

[PHV]

Have you tried this ?
ActiveSheet.Cells(3, 1) = Workbooks("Book2.xls").Application.Run("myfunction3", 6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Thanks PHV,

I copied and pasted your code, rechecked the code and tested after saving both WorkBooks

RT error 1004 - The macro 'myfunction3' cannot be found.

was returned.

The call is to this

Public Function MyFunction3(n!)

MyFunction3 = n * 2


End Function

which sits in MyModule of Book2

regards Hugh

 

[PHV]

Works for me (XL2003).
Another way is to add Book2.xls in the references of book1's project and then you may simply call the function without qualifier.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

That's pretty wierd PH because I'm on 2003 here too and its automatically updated. Could be a glitch in my installation I guess. I'll give it a try on my Excel XP system tommorow.

Up to now the caller 'RunMac' was always in Module1 of Book1
Interestingly if I move it to Book1 ThisWorkBook or Sheet1 I get an error window headed 'Microsof Visual Basic', a vbCaution icon, and the text '400'. The first two verses work in all cases. The former error message was given in a standard grey message box.

 

[HughLerwill]

No sorry I'm getting exactly the same behaviour under Excel XP.

 

[HughLerwill]

Going down the references avenue...

<Another way is to add Book2.xls in the references>

I seem to be having trouble today - now I can't find a References menu option. I know I've seen/ used it before. Which major menu in the VBA editor is that supposed to appear under? I guess Project, but I see no Project menu.

 

[PHV]

Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Thanks for that PH; found it!

Setting a Project reference to Book2 allows the following;

Sub RunMac()

'This macro in Book1
'Book1, Sheet1 is active

'call function in sheet of foreign WorkBook - works
ActiveSheet.Cells(1, 1) = Workbooks("Book2.xls").Worksheets(1).myfunction1(1)

'call function in ThisWorkBook of foreign WorkBook - works
ActiveSheet.Cells(2, 1) = Workbooks("Book2.xls").myfunction2(2)

'call function in MyModule of foreign WorkBook - works but only with Project ref to Book2
ActiveSheet.Cells(3, 1) = Workbooks("Book2.xls").Application.Run("MyFunction3", 6)

'call function in MyModule of foreign WorkBook - works with Project ref to Book2
ActiveSheet.Cells(4, 1) = myfunction3(6)

End Sub

You will see that verse three is now working but I seem to need the Project ref before it will. Thanks for your help in this exercise; any further thoughts on verse three requiring the ref?

regards Hugh,

 

[Shippwreck]

Hi,

I have been trying to do exactly the same thing as HughLerwill's Verse 3.

I have already got it to work by setting a reference, however as i explained in the thread posted by HughLerwill at the top of this thread setting a reference is not an option in my case as the file will not always be in the same locations and the users will not have the expertise to set there own references.

PHV the interesting thing is that you say the code works for you, yet both me and HughLerwill are getting the same error if there is no reference set??? The error that i am getting is that the macro cannot be found...

Any Ideas?

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[PHV]

OOps, I'm sorry :~/
This the code that worked for me without references:
ActiveSheet.Cells(3, 1) = Application.Run("Book2.xls!MyFunction3", 6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Yes that's the one!

Thanks again PH...