Macro exits sub after calling macro from another workbook (Application.Run)

[fibonacci]

Hello Everyone,

Here is my code:

Sub example()

wbName = "exampleWB.xlsm"
sPath = "C:\Users\Username\Documents\"
wbPath = sPath & wbName
mName = "Macro1"

'[1st part of code]

Workbooks.Open (wbPath)

Application.Run ("'" & wbName & "'!" & mName)

'[2nd part of code]

End Sub

When I run this macro it executes 1st part of the code, then it opens exampleWB.xlsm workbook, executes Macro1 but then stops omitting 2nd part of the code. When I do it step by step after executing Application.Run ("'" & wbName & "'!" & mName) line the cursor is just blinking under this line.

Could you help me on this one?

[GTO]

Hi there,

I believe we'd be guessing a bit without seeing the code being called. It might be best to create a simplified/no sensitive info example of both workbooks so we can see what is happening (you can zip both wb's and attach/post the zip).

Mark

[fibonacci]

Here is another problem. The macro being called was written by someone else and its code is password proteced. There is no way for me to get this password. I don't know if it helps but after code is done msgbox pops up, the only thing you can do is to click "OK" and then macro stops. Is it possible that there is a part of code in this password protected macro that does not allow to use it as a part of another macro? Or is that rather issue with the macro/workbook that I'm creating?

[GTO]

I am afraid that at least in my opinion, guessing at what is in the called procedure(s) would just be stabbing blindly at something six-foot away, with a pocket knife. Barring that something in your code (the 2nd part) is hiccupping, you simply need to see what is happening in the called workbook.

Mark

[Jan Karel Pieterse]

I suspect the called code contains an End statement, but as others already said: no way to know without the code.

[fibonacci]

>GTO, I know, I suspect that there must be something in that password protected code that does not allow to continue with my code. But basiclly after the procedure is done 2nd part of my code should be executed. Password protected code shouldn't even "know" that it's being used as a part of another code.

>Jan Karel Pieterse, could you please explain how End Statement works and if it could cause 2nd part of my code not to execute?

[Kenneth Hobs]

For help with VBA's End command, in the VBE, use F2 to browse for help with commands or with cursor in or near the command word, press F1 to get help.

For example, you can see that the variable s is cleared and all steps after are not executed either. End has its uses but it can be a real killer. Comment out the End command and uncomment the Exit Sub and see what happens. Then try it with both End and Exit Sub commented out.

Other issues in the other code can cause problems with your code as well.

Public s As String

Sub k()
  s = "k"
  r
  MsgBox s
  MsgBox "test"
End Sub

Sub r()
  s = "r"
  End
  'Exit Sub
  h
End Sub

Sub h()
  s = "h"
End Sub

[fibonacci]

>Kenneth Hobs, big thank you for explaination! This must be it. I saved your code in new workbook and than tried to call it using another macro from another workbook. The effect was exact the same - 1st part has been executed and after Sub r() everything stopped. Am I right that there is no solution? Something like "On Error Resume Next" that I could put before calling the code to ignore End statement?

[Kenneth Hobs]

Right, relying on others code can cause all sorts of grief. Of course on the off chance that an error could be trapped, try it and see.

[Aflatoon]

Error handling in your code won't work because you're using Run. Consider:
[vba]Sub foo()
On Error Resume Next
Application.Run "Bar"
End Sub
Sub bar()
Err.Raise 5
End Sub
[/vba]