none
VSTO vs VBA RRS feed

  • Question

  • I have an Excel stock trading program written in VBA.  I am making an Asynchronous call to a server.  But I have noticed that the event handler gets 'bogged' down when handling a lot of activity.  It appears the event handler is not fast enough to handle the data coming in.  Any way VSTO can improve performance?
    Monday, March 4, 2013 1:47 AM

Answers

  • event handlers by itself should be more then fast enough - it is rather your code inside that takes too much time. But of course as Tom said - it could also related to your hardware.
    Monday, March 4, 2013 6:26 AM
  • Hi Hbomb624,

    I think your issue is very complex for trouble-shooting. As far as I know, that VBA and VSTO will access the COM interface of the Office. And neither VSTO nor VBA will improve performance if the key problem at that place. On other handle, the CUP, memory and network bandwidth always affect your issue too.

    Have a good day,

    Tom

    Monday, March 4, 2013 2:43 AM
    Moderator
  • For such a complex application I think you would be foolish in deploying with VBA. However, VBA may be a good prototyping environment. Once you have the functionality built, then rewrite in VSTO using C#-DotNet.

    That's my recommendation.

    Monday, March 4, 2013 2:01 PM

All replies

  • What does the future hold for Office (especially Excel) hold? Should I move to VSTO?  How long will VBA stick around?  I develop stock trading applications using Excel and am thinking about which way I should go, VBA or VSTO?
    Monday, March 4, 2013 1:53 AM
  • Hi Hbomb624,

    I think your issue is very complex for trouble-shooting. As far as I know, that VBA and VSTO will access the COM interface of the Office. And neither VSTO nor VBA will improve performance if the key problem at that place. On other handle, the CUP, memory and network bandwidth always affect your issue too.

    Have a good day,

    Tom

    Monday, March 4, 2013 2:43 AM
    Moderator
  • event handlers by itself should be more then fast enough - it is rather your code inside that takes too much time. But of course as Tom said - it could also related to your hardware.
    Monday, March 4, 2013 6:26 AM
  • For such a complex application I think you would be foolish in deploying with VBA. However, VBA may be a good prototyping environment. Once you have the functionality built, then rewrite in VSTO using C#-DotNet.

    That's my recommendation.

    Monday, March 4, 2013 2:01 PM
  • VBA will be around for the foreseeable future (but none of us can see very far ...).

    VSTO support for Excel is somewhat patchy and performance is poor, so if you want to use .NET I would currently recommend Addin Express or XLDNA rather than VSTO.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Monday, March 4, 2013 6:20 PM
  • I would currently recommend Addin Express or XLDNA rather than VSTO.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Charles - what are the advantages of each alternative above ?
    Monday, March 4, 2013 6:25 PM
  • Both XLDNA and Addin Express allow you to use both the fast but more limited XLL interface and the slower but more complete COM interface, whereas VSTO does not support the XLL interface at all.

    VSTO provides no support for UDFs, whereas both XLDNA and Addin Express fully support UDFs.

    With VSTO its more difficult to support multiple versions of Excel and .NET than with XLDNA and Addin Express.

    Addin Express has several wizards etc to simplify development and is well integrated into Visual Studio, whereas with XLDNA you can start without Visual Studio or any development environment/compiler just using a text editor. Forum support for both seems good.

    Addin Express is a chargeable product, but XLDNA is free.

    I am sure there are some plus points for VSTO as well: I just never got past the lack of support for UDFs and the slow performance to find out what they are.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Tuesday, March 12, 2013 12:14 PM
  • Charles nicely describes the advantages of using the .xll-based libraries. It might be worth adding that Excel-DNA now also allows you to make asynchronous functions (based on Excel's RTD support, so it works on all Excel versions since 2002). The async calls to a server mentioned in the original post could be implemented using this feature.

    I also want to add some links, should you want to have a closer look at the Excel-DNA project:

    -Govert

    Excel-DNA - Free and easy .NET for Excel

    Tuesday, March 12, 2013 1:11 PM