CODEWITHSUNDEEP
vbaexcelmacrosinstances
TJYen
TJYen

Reputation: 373

How to Run macro independently in seperate instance without stalling current excel instance?

I currently have a macro that creates and opens instances of excel files. I am looking to run macros within these new instances. The thing is the macros are large and take a considerable amount of time to run, which pauses both instances of excel.

Is there a way to run the macro of another instance within the vbeditor of that specific excel app? This way the current macro can continue on while the other instance runs.

Upvotes: 1

Views: 3609

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You can create a new instance of Excel, but you'll need to use the OnTime method of the new instance. If you simply try to execute them from the current workbook using a .Run statement, that will tie up the thread.

Here is an example, I have tested this for normal called procedures and also included the .EnableEvents to prevent Event Handlers from firing in the second workbook when it is opened. Without this, the event handlers, if any, may fire and pause execution for the duration of their runtime.

Sub main()
Dim oXL As Excel.Application
Dim wb As Workbook
Dim filePath$, fileName$, moduleName$, procName$

'## The locaation of the file you want to open, which contains the macro
filePath = "C:\debug\"
'## The name of the workbook containing the macro
fileName = "book2.xlsm"
'## The name of the module containing the macro:
moduleName = "Module1"
'## The name of the macro procedure
procName = "foo"
'## Create new instance of Excel
Set oXL = New Excel.Application
oXL.Visible = True 'or True, if you prefer
'## Open the file containing the macro
oXL.EnableEvents = False
Set wb = oXL.Workbooks.Open(filePath & "\" & fileName)
oXL.EnableEvents = True
'## Use the OnTime method to hand the thread to other instance of Excel
oXL.Application.OnTime Now + TimeValue("0:00:05"), fileName & "!" & moduleName & "." & procName
'## Inform user that the macro is running in the other workbook/instance of Excel
Debug.Print "The procedure " & procName & " is running in " & fileName

wb.Activate
Set oXL = Nothing
End Sub

Upvotes: 1

Related Questions