Curious inconsistent behaviour with Application.Run

[Cloudfall]

Hi. I have a subprocedure which works and which I run each week which
simply calls other subprocedures as follows:

Sub ByWeekAllReports()
k = MsgBox("UPDATE dB. Use US date format mm/dd/yy. If no file
exists, {TAB}{TAB}{enter} will modify these macros.", vbOKCancel)
If k = vbCancel Then Exit Sub
Application.Run ("AutoV05.xls!ByWeekSchoolsV01")
Application.Run ("AutoV05.xls!ByWeekSuppliersV01")
Application.Run ("AutoV05.xls!ByWeekSchoolsAllV01")
Application.Run ("AutoV05.xls!ByWeekNewUsersV01")
Application.Run ("AutoV05.xls!ByYearFinancialV01")
End Sub

So, I'm tidying up my latest programming effort and I want to do
something similar with it. I have a program called "subABNTidy3()"
which lives in workbook "Automation V005.xls". Now, "subABNTidy3()"
works. So, to begin with, all I code is this:

Sub FaxSuppliersAutomation()
Application.Run ("Automation V005.xls!subABNTidy3")
End Sub

Sub subABNTidy3()
:
End Sub

Please note that the new subprocedure is physically coded directly
above the subprocedure it is calling. This new subprocedure does NOT
work, but crashes with this message:

"Run-time error '1004' The macro 'Automation V005.xls!subABNTidy3'
cannot be found."

I've had another guy here at work who, I've discovered, programs in
Excel VBA look at this with me as well. Neither of us can work out why
the old program which uses "Application.Run" works and the new one
doesn't. This isn't a show-stopper or anything. I'll just use the
following which, incidently, works:

Sub FaxSuppliersAutomation()
subABNTidy3
:
End Sub

Sub subABNTidy3()
:
End Sub

Why can this find the subroutine but "Application.Run ("Automation
V005.xls!subABNTidy3")" can't? Thanking you now for any responses.

 

[Bernie Deitrick]

Cloudfall,

This works fine:

Application.Run "subABNTidy3"

I think that the Run method only likes to have the file name if it needs to go outside the calling
workbook.

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Maybe it's because of the space in the name of the workbook:

Option Explicit
Sub aaa()
Application.Run "'book 1.xls'!bbb"
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

Without the apostrophe's, it blew up for me, too. But with the apostrophes, it
worked fine.

(Although, I wouldn't use application.run to call a known procedure in the same
workbook anyway.)

 

[Cloudfall]

Your response regarding the space in the name of the workbook made me
do this experiment. The following is code in workbook "Book1.xls":

Sub aaa()
Application.Run ("Book1.xls!bbb")
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

Running "aaa" gives the following error message:

Run-time error '1004':
Application-defined or object-defined error.

I won't in future use application.run to call a known procedure in the
same workbook, but right now the mystery to me is why it worked for me,
and continues to work for me on a weekly basis, in my weekly reports!
I'm using Excel 2000. Incidently, the following code also crashes in
the above workbook (Book1.xls) with the same error message:

Sub aaa()
Application.Run "bbb"
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

The following works fine:

Sub aaa()
bbb
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

A riddle wrapped in a mystery inside an enigma. Thank you for your
responses.

Regards,

Terry R.

 

[Dave Peterson]

Your code worked fine for me (xl2003).

Your response regarding the space in the name of the workbook made me
do this experiment. The following is code in workbook "Book1.xls":

Sub aaa()
Application.Run ("Book1.xls!bbb")
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

Running "aaa" gives the following error message:

Run-time error '1004':
Application-defined or object-defined error.

I won't in future use application.run to call a known procedure in the
same workbook, but right now the mystery to me is why it worked for me,
and continues to work for me on a weekly basis, in my weekly reports!
I'm using Excel 2000. Incidently, the following code also crashes in
the above workbook (Book1.xls) with the same error message:

Sub aaa()
Application.Run "bbb"
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

The following works fine:

Sub aaa()
bbb
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

A riddle wrapped in a mystery inside an enigma. Thank you for your
responses.

Regards,

Terry R.

 

[Cloudfall]

I double-checked this code in a workbook "Book1" with Application.Run
("Book1.xls!bbb") and Application.Run "bbb",
and in workbook "Book 1" with Application.Run ("Book 1.xls!bbb") and
Application.Run "bbb" and in workbook "Book 1" with Application.Run
("'Book 1.xls'!bbb") and with xl2000 received:

Run-time error '1004':
Application-defined or object-defined error.

In my opinion it is not worth investigating this any further.

Just a quick rave to Dave and Bernie and the rest of you who help out
in these groups. I have finished my major project and it works. There
is NO WAY I could have done this without YOUR HELP!!! There is NO WAY I
could have done this without THESE FORUMS and, in particular,
"microsoft.public.excel"!!! As an inexperienced VBA programmer you can
just hit a brick wall with no way forward. You go to Excel Help, you
read your VB and VBA reference works, but still Buckleys! Sometimes you
just have to have human to human contact to solve some of this stuff. I
don't think you people get enough feedback about how some of us feel
about what you do but you have to understand something- you people are
awesome. The software I wrote is going to save thousands of hours of
soul-destroying work here. I'm not sure that you people fully
understand the impact you truly have on the world. I'll still be
posting questions because one of the programs can be improved to run
faster, but I just wanted you all to know what a truly big difference
you make to people's lives.

All the best,

Terry R.

 

[Dave Peterson]

Well, maybe one more test <vbg>...

This worked fine for me, too:

Option Explicit
Sub aaa()
Application.Run "'" & ThisWorkbook.Name & "'!bbb"
End Sub
Sub bbb()
MsgBox "bbb"
End Sub

 

[Cloudfall]

Thank you.

Regards

Terry R.