MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Bryant    Date: Jan 30    Category: MS Office    Views: 735

I'm using Excel/VBA to extract data from a worksheet, then feeding it
into a solver, taking the solution from the solver (a .dll program),
then putting it back in readable Excel tables. It works great for
small problems. However, for big problems the solver needs more time,
and Excel crashes (i.e., "Program Not Responding" under task manager)
while waiting for it to complete.

Is there a simple way I can tell Excel (via VBA code) to wait, and not
to run itself into a crash?



1 Answer Found

Answer #1    Answered By: Herbert Weaver     Answered On: Jan 30

I have used this function between Access and Word to ensure that Access
has enough time  to push the information over to Word. You may be able
to modify it for your purposes:

Option Compare Database
Option Explicit
'***************** code  Start *******************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' Code Courtesy of
' Dev Ashish
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sTestSleep()
Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)
MsgBox "Before this Msgbox, I was asleep for " _
& cTIME & " Milliseconds."
End Sub
'***************** Code End *********************

Another Option:
Here's a little function that uses the system Timer to wait  for a
specified length of time.

Public Sub Wait(dblTime As Double)

Dim dblT1 As Double
Dim dblT2 As Double

dblT1 = Timer()

Do Until dblT2 >= dblT1 + dblTime
dblT2 = Timer()

End Sub

Didn't find what you were looking for? Find more on Timeouts/Crashes Or get search suggestion and latest updates.