VBA Question on Application.Run Range("PLPauseResumeEventHandler") and Application.Run Range("TRE...

Alex.Conti

...xcelReCalculateUpdatedFctManualMode")

1. For Application.Run Range("PLPauseResumeEventHandler") and Application.Run Range("TRExcelReCalculateUpdatedFctManualMode"), instead of running for certain range is there a way to write it so it can pause and resume the 1 excel workbook?

2. Also, after we have started refreshing Refinitiv's data into excel, is there a way to know if the event has been completed?
i.e. if Application.Run ("PLPauseResumeEventHandler")=xldone then "do something" ?
so that we can process something once all the data has been updated?

Thank you in advance!

jason.ramchandani01

@Alex.Conti Thanks for your questions.

So for your first point regarding PLPauseResumeEventHandler. From this Tutorial you can see right at the bottom point 10:
-------------------------------------------------------------------------------------------------------------------------

10. Although it is not part of the PLSynchronizationMgr library, there is an Eikon - Microsoft Office macro available to toggle between Pause and Resume Updates - this replicates clicking the Pause Updates/Resume Updates button on the Thomson Reuters Excel ribbon. Using this macro and the update status value, one can control the state of Eikon - Microsoft Office.

Application.Run "PLPauseResumeEventHandler"

------------------------------------------------------------------------------------------------------------------------

My understanding is that this did pause/resume updates for the entire workbook. What someone seems to have done on your side is to modify that macro into just applying those to specific ranges - hence the modified function you are running:

Application.Run Range("PLPauseResumeEventHandler")

If you were to try Application.Run "PLPauseResumeEventHandler" this should evert to affecting the whole workbook.

Regarding your second point, you need to be careful. I believe the event handler is just a toggle on or off - however you can use this with the current status of updating (paused, streaming or none) to determine state (from the above tutorial link):

---------------------------------------------------------------------------------------------------------------------

7. ConnectionMode, ConnectionState and UpdateStatus provide information about the type of connection (Internet, Managed or None), the current state of the connection (whether offline, online etc) and the the current status of updating (paused, streaming or none), respectively. Note in the code sample below where a check is done for the .ConnectionState - if it is 2 ("PL_Connection_State_Waiting _For_Login") then the .Login method is applied.

Dim WithEvents myPLSM As PLSynchronizationMgrLib.SynchronizationMgr

Private Sub checkSynchronization()
Dim strConnMode As String, strConnState As String, strUpdateStatus As String

If Not myPLSM Is Nothing Then Set myPLSM = Nothing
Set myPLSM = CreateSynchronizationMgr()

With myPLSM
Select Case .ConnectionMode
'PLConnectionModeType'
'PL_CONNECTION_MODE_NONE 0'
'PL_CONNECTION_MODE_MPLS 1'
'PL_CONNECTION_MODE_INTERNET 2'
Case 0
strConnMode = "0 - PL_CONNECTION_MODE_NONE"
Case 1
strConnMode = "1 - PL_CONNECTION_MODE_MPLS"
Case 2
strConnMode = "2 - PL_CONNECTION_MODE_INTERNET"
End Select

Select Case .ConnectionState
'PLConnectionStateType'
'PL_CONNECTION_STATE_NONE 0'
'PL_CONNECTION_STATE_MINIMAL 1'
'PL_CONNECTION_STATE_WAITING_FOR_LOGIN 2'
'PL_CONNECTION_STATE_ONLINE 3'
'PL_CONNECTION_STATE_OFFLINE 4'
'PL_CONNECTION_STATE_LOCAL_MODE 5'
Case 0
strConnState = "0 - PL_CONNECTION_STATE_NONE"
Case 1
strConnState = "1 - PL_CONNECTION_STATE_MINIMAL"
Case 2
strConnState = "2 - PL_CONNECTION_STATE_WAITING_FOR_LOGIN"
Case 3
strConnState = "3 - PL_CONNECTION_STATE_ONLINE"
Case 4
strConnState = "4 - PL_CONNECTION_STATE_OFFLINE"
Case 5
strConnState = "5 - PL_CONNECTION_STATE_LOCAL_MODE"
End Select

Select Case .UpdatesStatus
'PLUpdateStatusType'
'PL_UPDATES_STATUS_NONE 0'
'PL_UPDATES_STATUS_STREAMING 1'
'PL_UPDATES_STATUS_PAUSED 2'
Case 0
strUpdateStatus = "0 - PL_UPDATES_STATUS_NONE"
Case 1
strUpdateStatus = "1 - PL_UPDATES_STATUS_STREAMING"
Case 2
strUpdateStatus = "2 - PL_UPDATES_STATUS_PAUSED"
End Select

MsgBox "Connection Mode: " & strConnMode & Chr(13) & _
"Connection State: " & strConnState & Chr(13) & _
"Updates Status: " & strUpdateStatus

If .ConnectionState = 2 Then
' If the user log in details are already set for automatic log in, this will log in Eikon - Microsoft Office,'
' Or provide the log in dialogue box otherwise.'
.Login
End If
End With
End Sub

-----------------------------------------------------------------------------------------------------------------------------

So please use the UpdatesStatus to determine status - if streaming is true then do something. However, you need to be careful as you seem to be using Manual Calculation mode as well. There are some workbook refresh options referred to in the source code contained in the tutorial link above including one as follows:

Private Sub myPLSM_OnRefresh(ByVal a_refreshType As PLSynchronizationMgrLib.PLRefreshType)
    Dim arrRefreshTypes() As Variant
    
    arrRefreshTypes = Array("0 - PL_REFRESH_SELECTION", "1 - PL_REFRESH_ACTIVEWORKSHEET", _
        "2 - PL_REFRESH_ACTIVEWORKBOOK", "3 - PL_REFRESH_ALLWORKBOOKS")
        
    MsgBox "Refreshed: " & arrRefreshTypes(a_refreshType)
    Debug.Print "Refreshed: " & arrRefreshTypes(a_refreshType)
End Sub

I hope this can help.