RTD in excel

Add-in Express™ Support Service
That's what is more important than anything else

RTD in excel
 
Subscribe
Paul Willard




Posts: 6
Joined: 2017-11-09
Hi,
I would like to know if its possible to get live stream with 2,000 different stock symbols.
I have a vb.net app that receives raw data from over 2,000 different symbols but I would like to use RTD to send some data to excel and be able to crunch some numbers with real-time data.
Shall I use AdxrtdTopic? Where should I start so that I can accomplish something like on the attached image!

User added an image
Posted 09 Nov, 2017 22:26:36 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Paul,

I would suggest that you look into using a COM add-in to fill cells with data. I'm afraid an RTD server will be slow. As to the COM Add-in, see how to write cells fast at https://www.add-in-express.com/creating-addins-blog/2011/09/29/excel-read-update-cells/.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Nov, 2017 08:26:21 Top
Paul Willard




Posts: 6
Joined: 2017-11-09
Hi Andrei, What would be the right way to call those functions from Excel?
Posted 11 Nov, 2017 00:10:46 Top
Paul Willard




Posts: 6
Joined: 2017-11-09
I believe the code in those samples are a bit too outdated for VS2017
Posted 11 Nov, 2017 03:36:41 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Paul,

Paul Willard writes:
Hi Andrei, What would be the right way to call those functions from Excel?


Sorry? To call from VBA? Maybe you need to have VBA versions of these functions?

The code is okay: I compiled it a week ago or so.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Nov, 2017 03:40:34 Top
Paul Willard




Posts: 6
Joined: 2017-11-09
I am a little bit confused.. I have this code running on vb.net and it compiles and I can run test functions from excel and it works by reading the function off vb.net like MyFunc()
but how do I call Private Sub Fast(arr As String(,)) directly from excel?


    Public Function Fast() As Object
        Try
            Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
            Dim sheetCells As Excel.Range = sheet.Cells
            Dim theRange As Excel.Range = sheet.Range("A1", "C1")

            Dim arr As String(,) = {{"A1_val"}, {"B1_val"}, {"C1_val"}}
            theRange.Value = arr
        Catch ex As Exception
            Debug.Print("err: " & ex.Message)
        End Try
        Return ("done")
    End Function


Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll
err: Exception from HRESULT: 0x800A03EC
Posted 13 Nov, 2017 22:21:25 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Paul,

To be able to call a function from an Excel formula, you must declare the function in an Excel Automation add-in or XLL add-in. Also you should understand that an Excel function is an entity that produces a resulting value. If there's no result, the function becomes a command and a command isn't something that can be called from an Excel formula. Also, if you use an XLL add-in, Excel doesn't expect that your function calls into the Excel object model.

This said, the functions on that blog are intended to be called from a COM add-in, not from an Excel add-in.

Paul Willard writes:
Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll err: Exception from HRESULT: 0x800A03EC


Start with setting a single value of a simple type: e.g. "aString". Does it work?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 14 Nov, 2017 01:25:30 Top
Paul Willard




Posts: 6
Joined: 2017-11-09
I get the same error with using just a string

    Public Function Fast() As Object
        Try
            Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
            Dim sheetCells As Excel.Range = sheet.Cells
            Dim theRange As Excel.Range = sheet.Range("A1")

            theRange.Value = "test1"
            Return ("done")

        Catch ex As Exception
            Debug.Print("err: " & ex.Message)
        End Try
    End Function
Posted 15 Nov, 2017 00:53:58 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Could you send me your test project to the support email address? You can find it in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this topic.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 15 Nov, 2017 04:08:42 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Paul,

I've debugged your code: "Exception from HRESULT: 0x800A03EC" occurs when Range.Value is set. It looks like Excel won't let you change a n arbitrary cell while a UDF is called. I suggest that you consider creating a COM add-in that sets some cells with values you retrieve from your data source. What do you think?

Or, you can use a combination of a COM add-in plus a UDF in this fashion: when a function is invoked, your code uses the machinery we describe in section Wait a little to create a small delay; when the delay is over, you receive get the InSendMessage event and use it to update cells as required. In this case, the cells will be updated in the context of the COM add-in, not in the context of a UDF call.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 Nov, 2017 08:04:57 Top