Call a Public Sub from another Excel 2000 workbook. 1

[SQLBI]

Hi,

Is there a way of calling/running a Public Sub in one Excel workbook from another.

I've played around with various methods;

Call MyWorkbook.xls!Modules!Module1!MySub etc

But can't get it to work, anyone out there with any ideas?

Thanks in advance.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TonyJollans]

Hi Leigh,

You need to have a reference to the other workbook (Tools > References). Then you can just call it like any other Sub.

Enjoy,
Tony

 

[SQLBI]

Tony,

I see where you're going with this.

This workbook is stored on a network, would the other users automatically inherit this reference upon opeing the workbook, or should i add it to all the other machines also?

Thanks,

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TonyJollans]

Hi Leigh,

If you reference WorkbookTo (really, VBAProjectTo) from WorkbookFrom, the reference is stored with WorkbookFrom and whenever WorkBookFrom is opened (anywhere on the network), WorkbookTo is also opened (wherever it happens to be).

Enjoy,
Tony

 

[IvanMoala]

If your other workbook is open then just

Application.Run "BookName!MacroName"

http://www.xcelfiles.com

 

[TonyJollans]

Hi Ivan,

That does not work. I believe the syntax is for backward compatibility with Excel 4.0 Macro Sheets (about which I know very little).

Enjoy,
Tony

 

[IvanMoala]

Well this worked for me ??

Application.Run "'BookName.xls'!macroname"

Other workbook was Open!!

 

[TonyJollans]

Hi Ivan,

I do think there ought to be a way of running a macro in an open workbook (Excel manages it if you have a button on a toolbar) but I have never found one and I cannot make this work either. Is there a reference needed or an AddIn or some other pre-req, or a particular version of Excel needed?

Enjoy,
Tony

 

[combo]

Hi all,
thera are many ways to run, with VBA, a macro from other project. We only need in all cases:
- have the workbook open,
- reference it somehow.

1. We can set a reference via Tools>References to a file (precisely, its VB project) in VB editor. To avoid names conflict, a referenced project should have unique name, changed from standard 'VBAProject'. After this we can refer to the project as a library containing the macro:
[tt]ProjName.Macroname[/tt]
The referenced workbook is automatically opened when we open our workbook.

2. An add-in. It works in the same way as (1), i.e. we have to change its VBA project name and reference it. We do not see the second workbook and we are not asked for macros if the add-in is opened and installed.

3. Application.Run "'BookName.xls'!macroname": "Bookname.xls" has to be manualy open. There is no "xls" if the file has not been saved yet. According to help file, you cannot use named arguments with this method.

combo

 

[TonyJollans]

Hi combo,

I agree completely, EXCEPT that I can't make method 3 work at all - and the Help isn't much help Am I just having an off day (it wouldn't be the first)?

Enjoy
Tony

 

[combo]

Tony,
In XP, this works:
Workbook MyWorkbook.xls, saved and opened, with a standard module with:
[tt]Sub abc()
MsgBox "Hello!"
End Sub[/tt]

The second workbook with standard module with:
[tt]Sub xyz()
Application.Run "MyWorkbook!abc"
End Sub[/tt]

When I run xyz (macro>run) I get the message. How do you use 'Run'?

combo

 

[TonyJollans]

Hi combo,

I must be having a bad day (or several). I have rebooted and tested in 97 and 2K just as you say and just as I thought I had done before and I can't get it to fail now!

Hi Ivan,

My apologies for my post. You are right and what makes complete sense does, indeed, work. Have a star.

Enjoy,
Tony

 

[IvanMoala]

Hi Tony
I must admit I looked again , just to be sure I didn't have anything referenced.

cheers