Sleep API vs OnTime

Jon von der Heyden

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
Hi

I believe I know the answer to this but I need to be absolutely sure before testing on bloomberg terminal.

Bloomberg method RefreshAllStatic data doesn't suspend execution therefore any code called directly after this method is called is likely to be executed before the refresh is complete. Suggestions so far (thanks Colin :)) are to call RefreshAllStaticData and then use Ontime to call a routine that has the remaining code.

Am I correct in thinking that I can use Sleep API instead? Something like:

Code: 
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private mlngCALC_COUNT As Long

Public Sub RefreshData(ByVal rngTable As Range)
    Const lngMaxCalcCount As Long = 50
    Const lngMsIncrement As Long = 300
    Dim lngMsSleep As Long: lngMsSleep = lngMsIncrement
    
bb_refresh:
    mlngCALC_COUNT = mlngCALC_COUNT + 1

    If mlngCALC_COUNT > lngMaxCalcCount Then
        Call RaiseError("Calculation timed out!")
    Else
        Application.Calculation = xlCalculationAutomatic
        BloombergUI.RefreshAllStaticData
        [highlight]Sleep lngMsSleep[/highlight]
        If IsCalculated(rngTable) Then
            rngTable.Value = rngTable.Value
            Application.Calculation = xlManual
        Else
            lngMsSleep = lngMsSleep + lngMsIncrement
            GoTo bb_refresh
        End If
    End If
End Sub

Private Function IsCalculated(ByVal rngTable As Range) As Boolean
    Const strProcessing As String = "#N/A Requesting Data..."
    Const strLimit As String = "#N/A * Lmt"
    
    With Application
        IsCalculated = CBool(.CountIf(rngTable, strProcessing) = 0)
        If Not IsCalculated Then
            If .CountIf(rngTable, strLimit) Then
                Call RaiseError("BB limits preventing calculation!")
            End If
        End If
    End With
End Function
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi Jon,

I don't think that the Sleep API will work for this - but if I'm wrong then I'll buy you a beer. :)

As we discussed, you can adapt the first suggestion on that referenced post by have a smaller OnTime delay and then, if the formulae haven't refreshed, set the timer again, thus creating a recursive loop.

Let us know how you get on! :cool:
 
Upvote 0
I don't have access to a terminal today so my best attempt to emulate has been to setup a sheet as follows:

A1: =NOW()
A3: Now + 30 seconds as a constant
C3: =IF(A3>=A1,"#N/A Requesting Data…",1)

The code below uses calculate method instead of the BBUI RefreshAllStaticData. Seems to do the trick... Any comments Colin? Possible problems?

Code: 
Private mdblSTART_TIME As Double
Private Const mdblMAX_TIME As Double = 2.08333333333333E-03
Private mdblSTART_WHEN As Double
Private Const mlngSECONDS_DELAY As Long = 2

Private Sub StartTimer()
    Debug.Print "running", Time
    mdblSTART_WHEN = Now + TimeSerial(0, 0, mlngSECONDS_DELAY)
    Application.OnTime EarliestTime:=mdblSTART_WHEN, Procedure:="CalculateBB", Schedule:=True
End Sub

Private Sub StopTimer()
    Debug.Print "stop", Time
    On Error Resume Next
    Application.OnTime EarliestTime:=mdblSTART_WHEN, Procedure:="CalculateBB", Schedule:=False
End Sub

Public Sub TriggerCalc()
    With Application
        .Calculation = xlAutomatic
        mdblSTART_TIME = Timer
        Call CalculateBB
    End With
End Sub

Private Sub CalculateBB()
    Dim rngTable As Range: Set rngTable = ActiveSheet.UsedRange
    
    'BloombergUI.RefreshAllStaticData
    Application.Calculate
    
    If Now > mdblSTART_TIME + mdblMAX_TIME Then
        Call StopTimer
        Call RaiseError("Calculation timed out!")
    End If
    
    If IsCalculated(rngTable) Then
        Call StopTimer
        With rngTable
            .Value = .Value
        End With
        Application.Calculation = xlManual
    Else
        Call StartTimer
    End If
End Sub

Code: 
Public Function IsCalculated(ByVal rngTable As Range) As Boolean
    Const strProcessing As String = "#N/A Requesting Data*"
    Const strLimit As String = "#N/A * Lmt"
    
    With Application
        IsCalculated = CBool(.CountIf(rngTable, strProcessing) = 0)
        If .CountIf(rngTable, strLimit) Then
            Call RaiseError("BB limits preventing calculation!")
        End If
    End With
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
I can not trigger OLEObject.GotFocus event by ConnectToConnectionPoint API
Replies
0
Views
150
loquat
L
Jaafar Tribak
  • Question
Blurring images using Direct2D & Windows Imaging Component
Replies
3
Views
253
Dan_W
D
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
299
huiettcm
H
Jaafar Tribak
  • Question
Monitoring the Windows Shell ___ [Files and Folders activity]
Replies
3
Views
1K
Dan_W
D
Mousehunter
  • Question
Sleep Functionality - Can you please test my code for efficiency?
Replies
3
Views
512
GWteB
GWteB

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,058
Latest member
oculus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top