Date: Tue, 13 Nov 2001 16:03:35 -0600 (CST)
   From: Mark Jacobson <jacobson@math-cs.cns.uni.edu>
     To: 810-022-01@uni.edu
Subject: Sleep() instead of pauseDelayAmt()


Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Macro1()

' Macro recorded 11/13/2001 by jacobson

    For Each theCell In Selection
        theCell.Value = 1234
        Sleep (1000)               '  same as pauseDelayAmt(1)
    Next theCell

End Sub

The Windows API sleep() function can be invoked from within
Excel macros and VBA programs.  Thus the technique
of using the Timer function to return the number of seconds
since midnight could be replaced by the use of sleep().

However, sleep(1) would only pause the computer program for
1/1000 of a second, since 1 represents a millisecond, not a second.

sleep(1000) would sleep for 1 second,
sleep(500) would sleep for 1/2 second,
sleep(2000) would sleep or pause for 2 seconds.

Sub pauseDelayAmt()
     stopTime = Timer + delayAmt
     theTime = Timer
     Do While theTime < stopTime
         theTime = Timer
     Loop
End Sub

could be replaced by:

Sub pauseDelayAmt()

     sleep(delayAmt * 1000)         ' Assumes the global variable delayAmt
                                    ' contains the number of seconds you
End Sub                             ' want to delay.

or by

Sub pauseDelayAmt( numSeconds )

      sleep( numSeconds * 1000 )

End Sub

What is the output of the following macro VBA program, when you select the
Range of cells C4 through E9?  Try it out.
-----------------------------------------------------------------------
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub testingTheSleep()
    theSum = 0
    For Each theCell In Selection
        theCell.FormulaR1C1 = Int(Rnd() * 5)
        pause (theCell.Value)
        theSum = theSum + theCell.Value
    Next theCell

    MsgBox "The macro took " & theSum & " seconds to run."

End Sub

Sub pause(n As Double)

    Sleep (n * 1000)

End Sub
------------------------------------------------------------------------

See you in class.

Mark