Call private sub

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
291
This is piece of code that has worked for me for over a year and now it is not working.

Code:
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?
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
Can the active Application find the Workbook Object?

Try in the Immediate Window:

Code:
?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.
 
Upvote 0
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....
 
Upvote 0
Just so there's no misunderstanding....what was the result of your entering this code into the Immediate window?

Code:
?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.
 
Upvote 0
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...

Code:
?Workbooks("Prices.xlsm").Name
Prices.xlsm
 
Upvote 0
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.
Code:
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.
Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,434
Messages
6,101,826
Members
447,758
Latest member
jojo3strada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top