Run macro in background

[JLR-Mart]

I have several macros that I run each day, some of which take up to an hour
to complete. Whilst they are running I cannot use Excel. Is there a way to
run macros in the background or, better still, have them run during the night
like a cron job in UNIX ???

Many thanks for any help.

 

[Patrick Molloy]

you can use the Windows Task Scheduler to run the macro at whatever time you
like

Alternatively, use a separate instance of Excel.

 

[Gary''s Student]

You can always code your macros to "share focus". This way they will not hog
the processor, but let you get some work done as well.

for example:

Sub RunForever()
Do
Loop
End Sub

is a real hog, however:

Sub RunUntil()
Do
DoEvents
If Range("A1").Value <> "" Then
MsgBox ("I am stopping now.")
Exit Sub
End If
Loop
End Sub

is a good citizen.

 

[Rick Rothstein]

Here is an idea, but (and this could be the idea killer) it will require you
to lower your security setting for Excel so that it will run macros without
asking permission. If that is acceptable, then using the workbook Open event
code will handle the task...

Private Sub Workbook_Activate()
If Abs(DateDiff("n", "20:00", Time)) < 10 Then
Call Macro1
Call Macro2
Call Macro3
' etc.
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = True
End If
End Sub

First off, change my example time from 20:00 (8:00 PM) to the time you would
like to run your macros at. Second, change the macro names in my three
example Call statements to the macro names you actually want to run (adding
or deleting Call statements as necessary). Third, run Windows Task Scheduler
and set it up to run Excel with your workbook (along with its full path)
specified as the task's Argument... set this task to run at the same time
you changed my example time to at.