Calling a computationally intensive routine from VBA without stalling Excel GUI
If you want to do this well you need to give up on VBA and write a COM add-in.
Posting my comment as an answer...
You could implement an event in your COM object and have it call back when done. See http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/ for an example of how to run a COM object asynchronously.
My dirty hack is: create a new instance of Excel, run the code there.
Another option is to schedule the run for later, have the user say when. (In the example below, I've just hard-coded 5 seconds.) This will still freeze the user interface, but at a scheduled, later time.
Sub ScheduleIt() Application.OnTime Now + TimeValue("00:00:05"), "DoStuff"End SubSub DoStuff() Dim d As Double Dim i As Long d = 1.23E+302 For i = 1 To 10000000# ' This loop takes a long time (several seconds). d = Sqr(d) Next i MsgBox "done!"End Sub