Calling a procedure in another workbook (97-2000)

Topic

New Reply

I want to be able to call a Public subroutine in another workbook from a subroutine in the current
workbook.

Documentation says that this can be done using the syntax

vbaProjectName.ModulkeName.SubroutineName

This I have tried in assorted formats and it does not work.

Maybe I have the syntax wrong or I have missed the plot completely.

I know that I can do this using Application.Run(cccc) but I wanted to use the alternate suggested method.

Can anyone tell me how this is done.

As an Example, suppose I am writing a subroutine in a document called TestVB.xls
I want to Call a subRoutine called ShowInfo
that is in a Module called basGeneral
in a workbook called MyMacroLib.xls

Sub CallOutside()

SubroutineCallGoesHereButWhatIsIt?

End Sub

I have also tried to set a reference to the Library Workbook in Tools References as a third alternative but no joy. Would this final method work if it was an XLA Add-In instead of a Standard XLS

Any answers gratefully received.

Andrew Walker – andrew@d-l-r.co.uk

Reply | Quote

Replies

Andrew

I just wanted to ask that you make sure that the workbook that contains the code you want to run is open.

If that is the case, then it should work, OK for you.

HTH

Wassim

Reply | Quote

Again Andrew

Sorry I missed this one thing, you may need to try this kinda syntax:

Application.Run Workbooks(“Workbook Name”).Name & “!Name of Sub”

Try this, unless your typing missed it.

Wassim

Reply | Quote

In reply to both your mails.
The workbook is open yes.
I have already used Application.run method and this works fine.

However my question was different.
According to VBA books you should be able to call the routine from another project
using the syntax

Call VBAProjectName.ModuleName.ProcedureName

Unfortunately I cannot get this alternate syntax to work at all.
It works ok within the same Project if you have two routine with the same name but in different modules.

It is meant to work with subroutines in other open projects as well.

I cannot make it work like this though.

Maybe it is not possible, in which case I will stick with Application.run

However if it is possible, I’d love to know how to do it.

Reply | Quote

Have you included the full path to the workbook ?

Andrew C

Reply | Quote

Yes

I don’t want to use APPLICATION.RUN

If I want to call a routine in another module where there are two routines with the same name within the overall project I can successfully use the syntax

VBAProject.ModuleName.ProcedureName

It works fine

I want to use this notation to a procedure in a module in another project
The book says I can, my attempts say I cannot.

Is there a notation I can use to do this.
If there is, How do I reference the Project ?
This is the Bit it doesn’t Like

For Example
I want to call a subroutine called ShowInfo
In a Module called basGeneral
In an open book called Personal.XLS

I know the syntax for Application.Run – It works fine

How do I write the alternate syntax ?

[Personal.xls].basGeneral.GetInfo
I even tried this with the Path. Can’t see why it needs that though since it’s in memory.

I have even named the Project in personal.xls to be VBAProjectPersonal

I tried VBAProjectPersonal.basGeneral.GetInfo

No joy here either.

According to John Walkenbachs book Excel 2000 Power Programming it can be done
but he doesn’t give a specific example.

I am getting round to the opinion it cannot.

But if ANYONE knows otherwise I’d love to know the correct reference method

Reply | Quote

Hi Andrew,
You can do this as follows:
Let’s say you have a project called Utilities (doesn’t matter what the workbook is called as long as it’s open) with a module called modTestCode and a procedure called calltest.
First, select Tools-References, locate Utilities and check it.
Then in your calling module use the syntax:
Utilities.modtestcode.calltest
and it should run.
Hope that helps.
(Note: make sure the routine you’re calling is not Private)

Reply | Quote

I’ve had a go at this thanks. However it doesn’t seem to be bullet proof and depending upon project names it appears to get confused at times and cannot find the project.
Typical Microsoft really. here’s a feature that kinda work some of the time.

I’ve come to the conclusion that using the Run variant was is a darned site easier.

I might alternatively just convert the library file to an ADD-IN and distribute that way.

Thanks for the help anyway – Andrew

Reply | Quote

I think that you need to set a Reference to the workbook containing the macro before you can use that method to execute the macro. You use the References command on the Tools menu in the VBE to do that.

However, I highly recommend using the Run method instead. I have found that if I set the Reference and use your method, then if you make any changes in the module containing the macro, then you have to go to every workbook that uses the Reference and remove the Reference and then re-establish it before it will work again. I have found no disadvantage to using the Run method.

Reply | Quote

I’d come to the conclusion that using the Run variant was a darned site easier.

I might alternatively just convert the library file to an ADD-IN and distribute that way.

Thanks – Andrew

Reply | Quote

Using an Addin for macros that you are calling from other workbooks has the same disadvantage. You still need a Reference to the workbook to call them, and they still break if you make any changes in the addin so that you have to remove and re-establish the Reference in every workbook that calls a macro.

Can I be curious and ask why you are so dead set against using the Run method? If there is a disadvantage I would like to know what it is.

Reply | Quote

No I don’t have a problem with the RUN method.
I have always used it in the past and it works fine. I will continue to use it in the future too.
“If it ain’t bust then don’t fix it.”
I was just curious from an academic point of view to find out if there was an alternative method.
The texts I had read from Microsoft and other sources suggested the alternate method.
Had this worked ok it would have been nice because it follows a more object based approach to the programming (as much as VB allows) rather than a function based method like using the API.
It will all change when they force .NET on us anyway. Fortunately not immediately though.
Thanks for your help.

Reply | Quote

What API are you talking about? The Run method is a method in the Excel Object Model, it has nothing to do with the Windows API.

Reply | Quote