Macros stops when closing Workbook

Question

Hello,

I have two macros enabled files. Let's call them A.xlsm and B.xlsm

The following is the order of operations that causes my macros to terminate.

1.  VBAProject(A.xlsm)>Module A > Subroutine RUN calls a subroutine called VBAProject(B.xlsm)>Module B> Subroutine CALCusing Application.Run "'B.xlsm'!B.CALC"

2.  CALC does some stuff and then finally calls RUNusing Application.Run "'A.xlsm'!A.RUN"
3.  RUN knows that it has been called by CALCand then closes Workbook(B.xlsm).Close

When B.xlsm closes, the macros running in A.xlsm terminates. The VBA editor windows does not close, just the RUN subroutine stops!

Does anyone know how to close the workbook B.xlsm without this happening?

Thanks

Answer 1

Hi ScottFH,

So you could use code like this?

Sub Do100Times()

For i = 1 To 100

Workbooks.Open "c:\PathToB\B.xlsm"

'Do something here, like set up input values

Application.Run "'B.xlsm'!B.CALC"

'We return here when macro B.CALC has finished completely

'Do something with result?

Workbooks("B.xlsm").Close False 'Set to True to save changes

Next

End Sub

Answer 2

Hi again Jan,

Going back to your example code, is there any way to terminate the subroutine B.CALC if B.CALC takes too long to run?

Something like:

Sub Do100Times()

For i = 1 To 100

Workbooks.Open "c:\PathToB\B.xlsm"

'Do something here, like set up input values

Application.Run "'B.xlsm'!B.CALC"

If Runtime > 5mins then cancel B.CALC and close Workbooks("B.xlsm"). Go to the next i.

'We return here when macro B.CALC has finished completely

'Do something with result?

Workbooks("B.xlsm").Close False 'Set to True to save changes

Next

End Sub

Thanks

Answer 3

Yes that works! I didn't think that after calling B.CALC it would return to A.RUN

Thanks so much for the simple solution!

Answer 4

Hi there, 

Workbook A is essentially a script that opens B again and again until B has run, say, 100 times. Workbook B is a template that runs a bunch of calculations and then closes. I would like B to call A so that A knows when B is finished.

An alternative is to have A call B every 5mins or so but I feel that it can be done more efficiently.

The statement after the one that called the macro in B is to close B because I want to reopen B without the previous calculation results.

Thanks

Answer 5

Hi ScottFH,

Why does B need to call A when its macro finishes; the next statement in the

routine in A (the statement after the one that called called the macro in B) is

automatically exectuted?