Call private sub

[NicholasP]

This is piece of code that has worked for me for over a year and now it is not working.

cs__Port = "'Prices.xlsm" & "'!cs_Port"
Application.Run cs__Port

I believe the code is correct, but the VB excel run-time error 1004 is stating:

"Cannot run the macro "Prices.xlsm'!cs_Port'. The macro may not be available in this workbook or all the macros may be disabled."

It appears to me that the second " is missing in that message for some reason, i.e. -- "Prices.xlsm'!cs_Port' instead of "Prices.xlsm'!cs_Port'"

using the immediate window shows cs__Port = 'Prices.xlsm'!cs_Port

I'm at a bit of a loss because I have code that is exactly similar (diff variables) and that code is working...

Any help would be greatly appreciated, I'm hoping this is just a weird typo?

 

[wigi]

The code (unchanged) works perfectly in a new file that a save as Prices.xlsm.

The macro cs_Port is just a simple MsgBox in my test case.

 

[Jerry Sullivan]

Can the active Application find the Workbook Object?

Try in the Immediate Window:

?Workbooks("Prices.xlsm").Name

If that Workbook is open in a separate instance of Excel, that might be one cause for the seemingly inconsistent behavior.

 

[NicholasP]

Jerry -- Using the immediate window it does find the workbook object. As far as I can I can tell there is only one instance of Excel open....

 

[NicholasP]

Here is a link to a screenshot that shows the difference between what the run-time error is saying is wrong vs. what the immediate window is showing....the difference appears to be in the quotation marks?

http://farm6.staticflickr.com/5114/7441549774_aaf738909f_z.jpg

 

[Jerry Sullivan]

Just so there's no misunderstanding....what was the result of your entering this code into the Immediate window?

?Workbooks("Prices.xlsm").Name

The linked image you reference shows the result of reading the variable cs__Port in the Immediate Window. That doesn't tell us whether the Workbook can be found by the Application.

 

[NicholasP]

Sorry, I thought I had mentioned that it does appear that the workbook is being found by the application...

Below is the output from the immediate window...

?Workbooks("Prices.xlsm").Name
Prices.xlsm

 

[Jerry Sullivan]

It's strange that the code that previously worked started generating that error.
Is there anything you've changed in your process since when it last worked?

Regarding your observation about the double quotes in the error message,
it appears the normal error message has single quotes around the 'filename!macro'.

You can test that with this example.

cs__Port = "Prices.xlsm" & "!no_such"
Application.Run cs__Port

A pattern similar to yours can be found when using the single quotes around the filename.

cs__Port = "'Prices.xlsm" & "'!no_such"
Application.Run cs__Port

The evidence is showing that the application finds the workbook but not the Private Sub.
Is it possible that your Sub cs__Port code was moved to a Sheet Code, Userform or Class Module?
When I move the Sub to a Sheet Code Module, it yields that error message.

 

[NicholasP]

After looking at your last suggestion,

"Is it possible that your Sub cs__Port code was moved to a Sheet Code, Userform or Class Module?"

I looked at where the macro was located...It was in its own module so that wasn't it. The problem was I had started on another project, which used the original cs_Port code as a base and I hadn't changed the name of the sub yet. So I had 2 private subs called cs_Port and that was generating the error. Totally my fault and rather boneheaded of me in general...usually I can pick out these types of errors w/little problem. I appreciate all your assistance in going through the troubleshooting process!

Thanks again
Nick