VBA Issues with WorksheetFunction.RTD("tos.rtd", "", Param, Sym)

[mdm2020]

In my Function/Sub I created the following two bits of simple code

Sub MyTstToS3()
    Dim Bid As Long
    Dim Symbol, Descr As String
    
    Symbol = ".NVDA211217C320"
    Descr = MyToS3("DESCRIPTION", Symbol)
    Bid = MyToS3("BID", Symbol)

    Range("A1") = Descr
    Range("A2") = Bid
End Sub

Function MyToS3(Param As String, Sym As String) As Variant
        MyToS3 = Excel.WorksheetFunction.RTD("tos.rtd", "", Param, Sym)
End Function

If I Just run this I get an immediate Run-time Error: Type Mismatch and highlights Descr = MyToS3("DESCRIPTION", Symbol)
Now if I enter the following in any cell within the spreadsheet
=RTD("tos.rtd", "", "DESCRIPTION", ".NVDA211217C320")
Viola, the next Mismatch error happens at the next statement. Only when I add somewhere in the spreedsheet the below
=RTD("tos.rtd", "", "BID", ".NVDA211217C320")
does the code run through without a problem. The Symbol has to be the same because as soon as I change it it comes up with
the mismatch error again.

I am at loss why the Excel.WorksheetFunction.RTD will not works without the exact same RTD call with the exact same parameters needing
to be called somewhere in the spreadsheet. It is as if the fact that it is called in the spreadsheet and thereby running in the background that
the connection is open and only then the function in the VBA code works. Very frustrating.

I even tried to pre-populate the contents in a spreadsheet via a macro before calling the function but this cannot be done in one call as the
cells with the RTD statements will not update until it exits the macro.

Any ideas, help would be greatly appreciated.

 

[mdm2020]

I am desperate for help, any ideas?