Running macro in another project


Posted by kaiowas on June 16, 2000 2:40 AM

I'm having trouble running a VBA procedure located in another project, from reading the help file for "Calling procedures with the same name" I would expect that the required line of code would be:

[ProjectName.xls].[ModuleName].Procedure name(arguments)

The use of square brackets in the help file is confusing as calling a procedure in another module using ModuleName.Procedure name(arguments) works fine. (I can understand that the brackets would be required for the filename as the period in the filename would otherwise cause problems but i've no idea why they are suddenly used for the module name)

Anyway my main question is what is the line of code required to get the procedure to run, I've tried dropping the brackets from the module name, I even tried dropping the module name altogether. As a test, I copied the module to the current project and got it to run ok (although this is not a viable long term solution).

I even tried using the macro recorder to see what code that generated, it used application.run which although it ran the procedure ok, one of the arguments needs to be passed by reference and using application.run passed all arguments by value.

Well this seems to have got a bit long winded, but personally I hate it when people ask for help without giving enough information or being clear about what they need....

kaiowas

Posted by Ryan on June 16, 0100 5:38 AM

Kaiowas,

To run a procedure from an unopened workbook you must first establish a reference with it. To do this go to Tools -> References, and click browse and find the workbook that that you want to get the procedure from. It will show up as VBAProject. From here you can now use the syntax:

Call MyProject.MyModule.MySub <--No brackets

Hope this helps
Ryan



Posted by Kaiowas on June 18, 0100 11:55 PM

Thanks

I knew I was forgetting something really stupid!