Externall call

[MichaelH]

Hi Guys,

I've still a long way to go so maybe I'm thinking too far ahead with this one and I can't find a solution.

Scenario is that I think it would be a good idea to have all the common subs and functions within a single module within a single project and be able to call those common routines from any other project(s), but in these others it just doesn't recognise them.

Is there a way to call a sub in another peroject?

If I create an Add-In then this seems to work OK via a tool bar menu, but not from within a different project.

Is there a qualifier to precede the subroutine call?


Thanks for any help, or confirm that it can't be done.

Cheers

Michael

[Ken Puls]

Hi Michael,

If you know that your file will always have access to your addin, you could set a reference to it in VBA. (Tools|References in the VBE).

Also, you may want to check out the Run Method in the VBA help. This method can call a macro from another project.

The caveat on both, though, is that if your file is opened on more than just your computer, this could give you issues. If the file being referenced (in either case) is not available, you won't be able to run the code. It's usually wiser to load the required macros right into your workbook so that they're available wherever the workbook goes, but then again, I use both methods frequently.

HTH,

[Zack Barresse]

Hi Michael, How's it going?


If you have a routine in an add-in (or a Function) make sure that you've declared them as Public. Then you can just use the Add-in name in the call.

I put this Function and Routine in an add-in of mine (titled 'POM.xla') ...

Public Function MyTest() As String
    MyTest = "TESTING"
End Function

Public Sub Testing()
    MsgBox "It's alive!"
End Sub

In a seperate test workbook, you can call the function like this ...

Method 1:

=MyTest()

Method 2:

=POM.xla!MyTest()

They should both work as long as you don't have a name conflict.

As for the Routine, use the Application.Run method, like so ...

Option Explicit

Sub RunIt()
    Application.Run "POM.xla!Testing"
End Sub

It's much like calling the function as you explicitly tell where it is. Some caveats, if the add-in is not installed, the routines will not work, or if you change the add-in name it will not work either.


HTH

[MichaelH]

Thanks Ken, I'd tried adding a Reference but couldn't get that to work, I think by that stage I was trying referencing the Project and not an Add-In.

Zack, thanks also. I'm playing around more and trying to experiment, I'm nearing completion of my own replacement for the MS Template Wizard add-in which updates a s/s as a database from an originating s/s (e.g. form); since I loaded the add-in Excel seemed to be slower so I'm creating my own macro that will do just what I want without the overhead of the unwanted bits. All of this based upon what I learned on the beginners course!

Re this problem:
I thought all Subs were public by default so I didn't explicitly define as such in the .xla, when I tried to call a sub in another project it came up as not defined; so I'll try again.
The Application.Run looks the simplest so I'll try that.

Thanks both,

Michael

[Zack Barresse]

You should be good to just reference the routine name. Although I've run into (this) more and more the fact that I have so many routines in various files/add-ins, that it's nice to explicitly define where a routine came from - even if just for historical value, which helps when trying to find routines in an ocean of them. I try and implore the same habit with defining routines as Public; it shouldn't be problematic, but I border on safe when possible.

[MichaelH]

Thanks again Zack,

I'm not at my own PC now so further work will have to be tomorrow.

I'm jut about to log a separate query though - have a look at 'Error on Workbook Open'

Cheers
Michael

[MichaelH]

Zack, Ken,

I have to be doing something wrong here!!

A. I created an new AddIn exactly as per Zack's MyTest and Testing and then created a new workbook to use the Function and Subroutine but when I try to set a string variable to MyTest is arises 'Variable not defined' and MyTest is highlighted:
Dim MyStr as String
MyStr = MyTest()

B. Similarly if I just have a line:
Testing
It also arises Variable not defined.

c. However both the Function and Sub work if I use them via Application.Run......

d. Also, If I add a menu item to the Command Bar to call the sub, it works OK.

e. To create the AddIn I am creating the workbook with inserted module holding Zack's code, then saving it as an .xla into my Documents and Settings\Michael\Application Data\Microsoft\AddIn folder; I am then going into Tools \ AddIns and browseing to the above and selecting it.
My "TestAddIn" is definetly there because I can list it via:
Dim MyAddy as Addin
For Each MyAddy in Addins
MsgBox MyAddy.FullName
Next

So, If I can see the AddIn is there via the above, and it lists in Tools \ Addins, and I can call the sub from a Tool Bar menu,
Why can't I just call it from another workbook?


re adding a Reference; I have tried this and it then shows as 'VBA Project' in my list of available references, but it still arises'Variable not defined'.

Also, I now have 3 entries in my list of References all called VBA Project, and I can't tell which is which because the proprties window isn't wide enough to display the full path!
Is there a way to remove references?

Thanks
Michael (frustrated)

[Zack Barresse]

You will have the references in the VBE but you shouldn't need to mess with those at all. If you're having trouble with the formula, did you try and call it explicitly? ..

=TestAddIn!MyTest()

If this doesn't get it, can you upload the add-in?

[Ken Puls]

Also, I now have 3 entries in my list of References all called VBA Project, and I can't tell which is which because the proprties window isn't wide enough to display the full path!
Is there a way to remove references?

Yes, you should be able to go back into the References options, and just uncheck the boxes.

As a tip to make it a bit easier to figure out who's who in future, in the VBE Project Explorer, click on the main heading... say it's VBAProject(Book1), Press F4 to see the Properties window, and change the name of the project. This should help you identify it in the References window.

I'll check into my use of Add-ins and get back to you on the rest...

[Ken Puls]

Hi Michael,

Just tested with a subroutine from an addin. I have an addin called CalcTools.xla, which is installed as an Addin (through Tools|Addins).

I opened up a brand new workbook, (Book1), set a reference to CalcTools, and inserted a new module (in Book1). In that module, I entered the following code:

Sub test()
Call Calculation_Recalc_Selection
End Sub

Where "Calculation_Recalc_Selection" is from the CalcTools addin.

It worked perfectly. You could try adding the Call keyword, but I think that it should be implicit (I only use it to make things self documenting.)

Is there anythind different in what you're doing?

[MichaelH]

Zack, Ken,

Thanks for persisting to help me with this one, I think it's solved now.

Ken, that tip about naming the projects is what did it for me; as I said I had available references all called VBAProject and although I thought I'd tried selecting all of them I couldn't have done.
By renaming my project to "MyTestAddIn" it was clear which one I should select as the reference in my test workbook - when I did that then the MyTest function and Testing sub (as from Zack's sample) worked fine. Without adding the reference then using "Call" explicitly still failed, unless I used the "Application.Run...format".

So, jobs-a-good-un as we say in Yorkshire and I have learned that:
A. If you are calling a function in an AddIn from the menu bar then it finds it OK
B. If you are calling a function in an AddIn from another workbook then make sure you have a Reference from the latter to the former; so you may as well do this anyway!

Thanks for your help, you have been really great.

Cheers,

Michael

[Ken Puls]

Glad to be of help!

Cheers,