Linking VBA with the Data Tools functions

leojohann

Is there a way to link the excel functions with VBA? I want to start my VBA code as soon as i resume updates.

Jirapongse

@leojohann

Thank you for reaching out to us.

The Workspace doesn't provide the COM library that can be used in Excel VBA anymore.

The workaround could be like this:

Sub Test()

    Dim res

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = "=@RDP.Data(""IBM.N"",""TR.PriceClose"",""CH=Fd RH=IN"",B2)"


    Dim Style, Title, Help, Ctxt, Response, MyString


    Style = vbOK

    Title = "MsgBox Demonstration"    ' Define title.

    Help = "DEMO.HLP"    ' Define Help file.

    Ctxt = 1000    ' Define topic context.


    DoEvents

    Application.Run "WorkspaceRefreshWorksheet", True, 120000, "Sheet1"

    DoEvents


    res = ThisWorkbook.Worksheets("Sheet1").Cells(3, 3).Value

    Response = MsgBox(res, Style, Title, Help, Ctxt)

End Sub

You can use VBA to assign the Excel function to a cell in the worksheet. Then, use VBA to get a value.