Running macro in different instance of excel

[St Jimmy]

I'm receiving out of resources errors in Excel 365 (32-bit, 64-bit remedies the problem but our IT wont allow it for the number of users that would use this book, so I'm stuck with 32-bit). I have plenty of RAM so I'm trying to split the two workbooks into difference instances to allow more memory allocation (if this is a bad strategy I'd be happy to know why/alternatives!). My set-up has two workbooks, Workbook A opens Workbook B and Workbook B runs the heavy lifting code and then transfers the data back to Workbook A in value format. It worked fine before I started mucking around with the instances so I think the issue is the way I'm calling the macro in Workbook B.

WorkBook A Code:

Dim WkbB As Object
Set WkbB = New Excel.Application
With WkbB
    .Visible = True
    .Workbooks.Open "P:\Root\WorkbookB.xlsm", True, False
End With

Application.Run "'WkbB.xlsm'!Main"

Workbook B opens in its own instance as desired but when the code gets to the Application.Run line it opens WorkbookB again but in the same instance as WorkbookA defeating my strategy.

Is there a way I can specify the instance of excel in the Application.Run line?

Thank you!

 

[St Jimmy]

I figured it out and wanted to post for reference.

Change the last line of code from

Application.Run "'WkbB.xlsm'!Main"

to

WkbB.Run "'WorkbookB.xlsm'!Main"

Dear future user, you're welcome

 

[Ruddles]

Future user here!

Thank you so much for this piece of code. I've been racking (wracking?) my brain and stretching my VBA capabilities to the utmost with a particular problem... I mean "opportunity to shine", and this is exactly what I've been looking for. If you're still around and receiving notifications of new replies, please give me a shout and I'll acknowledge your brilliance in my project.

 

[Ruddles]

One slight mod though...

If I'm interpreting things correctly, when you use the WkbB.Run command to start the macro from Workbook B, it seems to use the VBA processor of Workbook A, so it ties up Workbook A until the macro from Workbook B has completed. I've changed this to WkbB.OnTime Now(), "'WorkbookB.xlsm'!Main" which appears to force it to use the VBA processor in Workbook B and allows any code running in Workbook A to continue running normally.

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2406 Build 16.0.17726.20078) 64-bit.

I'm sorry, I realise this thread is five years old but I'm updating it because someone might do a search for the same issue in the future, and if this helps them resolve their problems, then great.

Words I could have mentioned but didn't: multi-thread multithread simultaneous multiple parallel execution

 

[St Jimmy]

One slight mod though...

If I'm interpreting things correctly, when you use the WkbB.Run command to start the macro from Workbook B, it seems to use the VBA processor of Workbook A, so it ties up Workbook A until the macro from Workbook B has completed. I've changed this to WkbB.OnTime Now(), "'WorkbookB.xlsm'!Main" which appears to force it to use the VBA processor in Workbook B and allows any code running in Workbook A to continue running normally.

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2406 Build 16.0.17726.20078) 64-bit.

I'm sorry, I realise this thread is five years old but I'm updating it because someone might do a search for the same issue in the future, and if this helps them resolve their problems, then great.

Words I could have mentioned but didn't: multi-thread multithread simultaneous multiple parallel execution

Thanks Ruddles,
Nice add on the .OnTime Now()!
For this project I wasn't using Workbook A while I was using Workbook B, but that's a great bit that brings much more flexibility to the code!

Can't believe it's been 5 years...

 

[Ruddles]