Application.Run Call Err: 1004 Cannot Find Macro

[Travich]

This topic has been discussed numerous times that I can find, but even
with all the discussion I cannot get Application.Run to work for me.

So here's what I did. I set up a test.xls and a test2.xls. In
test.xls, I created a basTest and put in the following code:

Public Sub GetMessage()
MsgBox "Message from test was received.", vbOKOnly, "GetMessage"
End Sub

In test2.xls, I've created a button and tried to call application.run,
here's what I currently have:

Private Sub CommandButton1_Click()
Dim WB As Excel.Workbook
Dim Path as String

On Error GoTo ErrorHandler

Path = Application.GetOpenFilename("Excel File,*.xls", , "Test",
"Open", False)
Set WB = GetObject(Path)
WB.Application.Run "!GetMessage'"
'GetMessage

Exit Sub

ErrorHandler:
MsgBox "Err: " & Err.Description & " Number: " & Err.Number

End Sub


This throws the Err: 1004, Cannot Find Macro. As well as do the
following changes that I've made to the code.

'Tried this, and got the same error.
WB.Application.Run WB.Name & "!GetMessage'"

'Tried this and got the same error.
Application.Run WB.Name & "!GetMessage'"

'Tried this and got the same error.
Application.Run FileName & "!GetMessage'"


I've tried several other variations and absolutely cannot access the
sub. What am I doing wrong???

 

[Dave Peterson]

I try:

Application.Run "'" & WB.Name & "'!GetMessage"

Note that the apostrophes are in different locations from what you tried.

 

[Dave Peterson]

And if that macro were in the same workbook as the code calling it, I'd just
use:

Call GetMessage

 

[Travich]

Ah! The single quotes may be the problem. I will try it now and let
you know.

These are in seperate workbooks.

As a tangeant, can someone explain why I would use "call" in front a
sub instead of just calling the sub by its name? Thanks!

 

[Travich]

Wow, great catch on the single quotes!! That solved the problem!!!!

One final question - does anyone know if I have the modules password
protected, if this would possibly prevent a macro from being called
(one in another workbook?). I'm going to test it out now. Thanks!

 

[Dave Peterson]

I think you'll find your answer to the second question pretty quickly!

 

[Dave Peterson]

Besides the syntax differences, I think it's just a matter of personal
preference.

I like to use Call. I think it makes the code easier to read. Others disagree,
though.