The Dex2 COM API can be used to replicate the TR() function in VBA.

Add the PlVbaAPIs. Bas file to the Excel project from the Thomson Reuters Eikon installation directory.

In the PlVbaAPIs modules, comment out all the code except the DEX2 section (otherwise the code will error on compiling).

From Tools, References, in the Excel Visual Basic Editor, add the Dex2.dll (if the Dex2 Type Library is not listed, the dll will be found in the Thomson Reuters Eikon installation directory in the X/Bin, Y/Bin or Z/Bin sub directory).

In an object module (Workbook, Worksheet or Class)

Private MyDex2Mgr As Dex2Lib.Dex2Mgr
Private MyDex2MgrADC As Dex2Lib.IDex2Mgr2
Private MyDex2Cookie As Long
' Private variable that holds the Dex2 RData, able to obtain TR fields.
Private WithEvents MyDex2RData As Dex2Lib.RData
And in the main routine
    Set MyDex2Mgr = CreateDex2Mgr()
    Set MyDex2MgrADC = MyDex2Mgr
    
    ' Initialise using the DE_MC_ADC_POWERLINK enumeration
    MyDex2Cookie = MyDex2MgrADC.Initialize(DE_MC_ADC_POWERLINK)
    ' And create an RData object.
    Set MyDex2RData = MyDex2Mgr.CreateRData(MyDex2Cookie)
Before using the input parameters as one would for the TR() function.
    With MyDex2RData
        .InstrumentIDList = "TRI.N"
        .FieldList = "TR.CLOSE"
        .RequestParam = "edate:-20d sdate:-9d"
        .DisplayParam = "RH:In CH:Fd"
        .Subscribe
    End With

in the result, i get "The access to field(s) denied" when try to use thr TR.CLOSE. what do i missed? tks