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

fibonacci1101

Board Regular
Joined
May 23, 2013
Messages
83
Hello Everyone,

Here is my code:

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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi fibonacci1101 - In trying this out, it seems like what you need is a line to "Activate" the original workbook that you started the macro from. In the case of the example code below that is: Windows("ClickTest.xlsm").Activate ---> Windows("YourOriginalFileNameHere.xlsm").Activate

Hope that helps.

Happy Excelling,

goesr

Code:
Sub fibonacci1101()
wbName = "test1101.xlsm"
sPath = "C:\Excel\test\"
wbPath = sPath & wbName
mName = "Macro1"
'[1st part of code]
Workbooks.Open (wbPath)
Application.Run ("'" & wbName & "'!" & mName)
'[2nd part of code]
'NOTE - Add code to Activate the original spreadsheet.
'In my case that was ClickTest.xlsm
    Windows("ClickTest.xlsm").Activate
    Cells(1, 1).Select
End Sub
 
Upvote 0
What does the macro you are calling do?

Can you post its code?
 
Upvote 0
@goesr, isn't the workbook activated automatically right after using Workbooks.Open() method? I guess if that would be a problem called code wouldn't run at all. My fargment of code allows to execute macro from another workbook, but after it's done it won't continue with 2nd part of my code.

@Norie, unfortunatelly I cannot post the code cause it's password protected. There is also no way for me to get the password so I can see the code. I use this macro at work, it pulls data from our internal system. I've been using other macros created by the same owner along with my code and there was no problem. So far I've seen two workbooks/macros where these issue appears.
 
Upvote 0
It sounds like there's something in the code you are calling that stops all code execution.
 
Upvote 0
@fibonacci1101 - After using Workbooks.Open() the workbook is activated and then Macro1 is run on that workbook. That is what happened in my example code also. To reactivate the original workbook, that the first macro was run from, I had to use Windows("ClickTest.xlsm").Activate. The code Cells(1,1).Select was then run on the original workbook. Did you try to use Windows("ClickTest.xlsm").Activate with your file name and if so, what was the result? Hope this helps. Merry Christmas,

goesr
 
Upvote 0
goesr

If code execution is being stopped then the code to activate the original workbook will never be reached.
 
Upvote 0
Good point Norie. In my example code, until I added the Windows("ClickTest.xlsm").Activate line, I had a similar (spinning) result.

@fibonacci1101 - For diagnostic purposes, I would add a msgbox to the end of Macro1 that pops up when Macro1 is complete. Then you would know if Macro1 is completing.
 
Upvote 0
@goesr, I don't think that this is the issue but I will give it a try tommorrow at work. The problem is that I cannot add any additional code to Macro1 cause its VBA code is password protected.

@Norie, I'm affraid you are right. Do you know any method/procedure that could be embeded in the password protected code and could stop all code execution?
 
Upvote 0
fibonacci

I can think of quite a few things that could cause code execution to stop.

Have you spoken with the author of the code?

Are other users having the same problem?
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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