Do Until Loop of a Pause code execution Procedure (VBA Excel 2010)

Using MicroTimer sub (similar to Decision Model Limited) in Loop until line of a Pause sub by zorvek (Kevin Jones) be used as a more precision timer.
R SpencerAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Please see my article on How to Time Code.
0
 
Roy CoxGroup Finance ManagerCommented:
And the question is?
0
 
R SpencerAuthor Commented:
Martin the article has been helpful.

The pause loop is used as part of wait for page load.  The pause loop sub is in 3  page load loop sub which would be executed two thousands of times in one session.

Would this give precision in timing between doevents with varying computer speed by replacing:

Loop Until Int(Now) + Timer / Factor >= ResumeTime
With:
   getFrequency            ' get ticks/sec
    getTickCount             ' get ticks
    MicroTimer = getTickCount / getFrequency     ' calc seconds
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Martin LissOlder than dirtCommented:
Before I answer that question, let me as you this. Are you saying that you load (Activate?) the same page 2000 times? If so you can instead do something like this instead and not load it at all.

Dim ws As Worksheet

Set ws = Thisworkbook.Sheets("Name of your sheet")

'  And refer to that sheet like this
ws.Range("A1") = "blah" 

Open in new window

0
 
R SpencerAuthor Commented:
Scraping is done within the <FORM></FORM> tags, nested within a set of <IFRAME></IFRAME> tags.
Actions performed within the <FORM> within are handled by Java scripts.
The HTML code sample is part of the main <FORM>.  The sample code is a base for each of the four pages that the data is extracted from.
The excel macro may well be run on different models of HP and the interaction between Excel and IE versions.

 Do Until Loop of a Pause code execution as per file
Sanple_Only.zip
0
 
Martin LissOlder than dirtCommented:
I'm sorry but I don't think I can help.
0
 
R SpencerAuthor Commented:
Thank you for the valued advice you have given.

In your article 'How to Time Code. ' you write:

 I never actually use it, but here's a QueryPerformanceCounter example.

I have not seen any articles so far that incorporate the use of QueryPerformanceCounter  in a line:

. Do until int(now) + timer  over the use of GetTickCount , TimeGetTime  and QueryPerformanceCounter

Would appreciate your thoughts on the idea.
0
 
Martin LissOlder than dirtCommented:
It's just a matter of the precision (the time interval) you want. All of them are accurate but QueryPerformanceCounter is the most precise.
0
 
R SpencerAuthor Commented:
Thank you for the valued advice and has Lend to a solution of:

'-----------------------------------------------------------------------------
' Procedure : Pause
' Author    : by: Kevin zorvek Posted on 2007-06-22 at 14:04:39ID: 19344834
'                                                              ID: 262871882010-01-11
'               © 1996-2013 Experts Exchange, LLC. All rights reserved.
'               http://www.experts-exchange.com/
' Date      : 6/01/2013 2010-01-11
' Purpose   :
' Arguments : None
' Returns   : None
' Comments  : http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25414098.html
'-----------------------------------------------------------------------------
' History   : 07/04/2018 - Modified the variable Factor, ResumeTime, SleepDuration
'                           Replaced the functions Int(Now) znd Timer by using API 
'							in the MicroTimer procedure 
'							COPYRIGHT © DECISION MODELS LIMITED 2006. All rights reserved
'-----------------------------------------------------------------------------
Option Explicit
'
'-- **************************************************************************
'-- Required References to libraries Follow
'-- **************************************************************************
'
' ADDING A REFERENCE IN VBA.
' To add a reference to your VBA project, go to the Tools menu in the VBA editor and choose the References item.
' In the dialog that appears, scroll down the list until you find the appropriate library.
' Commonly used references are listed at the top of the list, and after those, the references are listed in alphabetical order.
' When you find the reference required by the code, check the checkbox next to the reference title and then click OK.'
'
'-- **************************************************************************
'-- Module DLL Declaractions Follow
'-- **************************************************************************
'
' Declared Functions do not require ADDING A REFERENCE IN VBA.
' Declare all the API-specific items Private to the module
'
' Declaraion for VBA7 MS Office 2010
'
' Windows API/Global Declarations:
'
Private Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
'
'Constants for API
'
'-- **************************************************************************
'-- Declare variables and allocates storage space
'-- **************************************************************************
'
'   Library variables and allocates storage space
'
'

Public Sub Pause(ByVal Seconds As Single, Optional ByVal PreventVBEvents As _
    Boolean = False)
		  
1         On Error GoTo ErrorHandler

          ' Pauses for the number of seconds specified. Seconds can be specified down to
          ' 1/100 of a second. The Windows Sleep routine is called during each cycle to
          ' give other applications time because, while DoEvents does the same, it does
          ' not wait and hence the VB loop code consumes more CPU cycles.

          Const MaxSystemSleepInterval = 25 ' milliseconds
          Const MinSystemSleepInterval = 1 ' milliseconds

          Dim ResumeTime As Double
          Dim Factor As Long
          Dim SleepDuration As Double

10         Factor = 100

11        ResumeTime = MicroTimer + Seconds

21        Do
31            SleepDuration = (ResumeTime - MicroTimer) * Factor
41            If SleepDuration > MaxSystemSleepInterval Then SleepDuration = MaxSystemSleepInterval
51            If SleepDuration < MinSystemSleepInterval Then SleepDuration = MinSystemSleepInterval
61            Sleep SleepDuration
71            If Not PreventVBEvents Then DoEvents
81        Loop Until MicroTimer >= ResumeTime

          
          '*  Lines being added for Error Handler
ExitPoint:
          '
91        On Error GoTo 0
          '
101       Exit Sub
          '
ErrorHandler:
          '
          ' Handle errors before calling or exiting the procedure
111       MsgBox vbCrLf & "Error " & Err.Number & " (" & Err.Description & ")" & _
              vbCrLf & vbCrLf & vbCrLf & "Error Line: " & Erl & _
              " in procedure Pause of Module Pause_Module", vbCritical
          
121       If Not Err.Number = 0 Then
              '
131           Err.Clear
              'Resume Next
141           Resume ExitPoint
151       Else
161           GoTo ExitPoint
171       End If
          '
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.