Run macro in background

[Yohan]

I created some macros to work with Access database.
Apparently, those macros took quite some time to
calculate, around 1-3 minutes. While the macro is
running, the Excel application freezes and I can't work
during that time, only waiting for the macro to finish
the calculation.

Is there any way to tell Excel to run the calculation in
the background, so that I may still be able to work with
my worksheet while the macro is running?

Regards,
Yohan

 

[Bob Phillips]

Yohan,

You could start another instance of Excel, and put that code in a workbook
open event, and open that workbook in the other Excel instance.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onedaywhen]

Do you mean it takes a long time to recalculate the workbook once
you've got the data or it takes a long time to fetch the data? If it's
the latter, you may be able to use an ADO recordset to fetch the data
asynchronously. Here's some code which fetches millions of rows from a
table my MS Access database. When the workbook opens, I can use it
normally. After about a minute a message appears telling me the data
has been fetched.

Option Explicit

Private m_con As ADODB.Connection
Private WithEvents m_rs As ADODB.Recordset

Private Sub Workbook_Open()
Set m_con = New ADODB.Connection
With m_con
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
.Open
End With

Set m_rs = New ADODB.Recordset
With m_rs
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.Source = "SELECT * FROM LongTime"
.ActiveConnection = m_con
.Open , , , , adAsyncFetch Or adCmdText
End With
End Sub

Private Sub m_rs_FetchComplete(ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)
MsgBox "Done!"

m_rs.Close
m_con.Close
Set m_rs = Nothing
Set m_con = Nothing
End Sub