Coffeehouse Thread

10 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

when will the excel VBA programming model be replaced?

Back to Forum: Coffeehouse
  • User profile image
    SteveRichter

    I am hoping to write some VBA functions that connect an excel spreadsheet user to an ODBC data source.  Excel 2010 is crashing left and right on me and hours into the process I can't find the cause. Take away code bit by bit until it stops crashing. Then you put the code back and it still does not crash. Then, put the app back that I had renamed and run it. And it crashes.

    Anyway, I am very happy that Access has been replaced by lightswitch. Is there a .NET version of Excel anywhere on the horizon?

     

  • User profile image
    PerfectPhase
  • User profile image
    figuerres

    ,SteveRichter wrote

    I am hoping to write some VBA functions that connect an excel spreadsheet user to an ODBC data source.  Excel 2010 is crashing left and right on me and hours into the process I can't find the cause. Take away code bit by bit until it stops crashing. Then you put the code back and it still does not crash. Then, put the app back that I had renamed and run it. And it crashes.

    Anyway, I am very happy that Access has been replaced by lightswitch. Is there a .NET version of Excel anywhere on the horizon?

     

    also there is the option to use the new excel file format , xlsx files are a zip file that have xml inside.

    there is a .net sdk to read and write xlsx files w/o even installing excel or office on a pc.

  • User profile image
    SteveRichter

    I will check on VSTO again. I am looking to put a combobox in a cell that is populated with season codes from a database. Select a season code and the combobox and the cell to the right is populated with styles for the season. Which cascades to a combobox with the colors of the style.  

    Was going to use the .Validation property of a cell to do this, but the validation list is limited to some limitation that likely dates back to 1992.

    Does VSTO replace VBA?   Can I double click a cell in the spreadsheet and run .NET code that pops up a WPF form and then populates a row in the spreadsheet from the .NET code?  I can attach the VSTO project to an excel spreadsheet just like VBA code?  The VSTO code will run on Excel 2007?

    I have isolated what is crashing excel 2010 on me.  When the button click handler assigns a value to the active cell, excel crashes. Run the code with that single assignment statement commented out, and it runs ok.

    -- worksheet double click handler news up a VBA form:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim fm2 As frmDummy
    Set fm2 = New frmDummy
    fm2.Show
    Set fm2 = Nothing

    End Sub

    -- ok button click handler assigns a value to the active cell. excel 2010 crashes.

    Private Sub butOk_Click()
    Worksheets("Sheet1").Activate
    Set r1 = ActiveCell.Offset(0, 0)
    r1.Value = "abc"    ' <== comment this out. excel does not crash.
    Set r1 = Nothing
    Unload Me
    End Sub

     

  • User profile image
    SteveRichter

    just to complete the circle. I reboot the PC and excel no longer crashes.  My PC hardware is not that bad. IE9 crashes maybe once a month.

  • User profile image
    contextfree`

    The next version of Office is apparently adding a JavaScript programming model, if that helps..

  • User profile image
    wsdotnet

    i always wondered why office does just replace vba (vb script) with c# and vb.net. 

    O and javacript, really office team, lets just use batch files.

  • User profile image
    ScanIAm

    @SteveRichter:

    VSTO development that does stuff in office uses the same api that you use in VBA to make things happen.  In fact, the cool part is when you can't figure out how to do it in VSTO, you can just record a macro and see what happens to figure out the api calls that are needed.

    The VSTO advantage is that you can ALSO do anything that a .net application can do so you could, for example, look up the data for the various cells using any database connection you might need.  I'm more familiar with Word VSTO, but there's a pretty heavy crossover between the two.

    Another VSTO advantage is creating your own ribbon that allows you to add functionality to the office product.  It's really quite awesome, but it doen't magically fix the fact that all office products contain 2+ decades of backward compatibility goofiness.

  • User profile image
    W3bbo

    Microsoft will only add alternatives to VBA, they won't be removing it any time soon.

    VBA is great in that non-technical users can write short macros that boost their productivity (it's hard to maintain a straight face when writing this, but ostensibly it's true). The other approaches, such as VSTO and COM Automation are really meant for 'proper' software developers who might exist within an IT department for a large multinational.

    Now the rumours of a new ECMAScript-based approach are interesting (I'm curious as to how it will be implemented: JScript.NET using VSTO? Active Scripting using the same COM-based backend as VBA? Or an IE9-derived JIT ECMAScript engine?) but I don't think it will replace VBA too soon: non-technical users scream and run-away at the sight of semi-colons, braces, and obscure syntactical constructs. VBA has the whole "it looks like English" thing going for it.

  • User profile image
    contextfree`

    I would give 99:1 odds it will be based on the IE9/10 script engine and WinRT.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.