none
Should I use VBA or VSTO on a new Project? RRS feed

  • Question

  • Hi,

    I've used both VBA and VSTO (C#) for Excel. I've seen advantages and disadvantages in both. Now I'm working on a new project and not sure of the best way to go.

    The project is an Excel sheet that is acting like a form. The entire sheet is protected and some of the cells are available to enter the appropriate data. There are multiple formulas that calculate each time a number is entered, but the sheet is not too slow. Also if a new section is needed, the user clicks a button and the 10 rows are copied, pasted and the new one are cleared out. Right now all of this works with VBA.

    One of the issues is that changes are made by the business to the actual sheet, handed to me and I either make the changes requested or fix the formulas they broke. It is not broken on purpose, they just don't know Excel VBA and the craziness of the code on the sheet (that I inherited).

    When it comes to distributing the sheet, the business leaders either place it on a server or email a new macro-enabled workbook.

    The issues I am facing is that any change needs to be made and then redistributed to all the users. If they have an older version that has an error in it, I have no way of knowing. Also, formatting changes, such as adding columns, moving text around can cause the formulas not to work properly.

    I am thinking that using Click-once deployment would be a big help to distribution and making a code change to a formula. But how would my business user make formatting changes (adding/removing/changing headers, text, etc.) and me still be able to distribute it via Click-once? Plus the base code is shared between multiple versions of the workbook, so if I make a change in one, I can easily update other workbooks that use the same formulas.

    Another issue with Click-once, is that I don't know how to have a single user or just a few users test the workbook before it is actually distributed.

    I really think the best solution would be not using Excel, but designing a web interface or even using InfoPath (not ideal) for this project. However that is a bit of overkill and maybe over-engineering for what this is.  Right now it works pretty well, but I am trying to make is easier for everyone.

    Any suggestions on the right way to proceed would be greatly appreciated.

    Thank you!

     


    Wednesday, November 16, 2016 9:34 PM

Answers

  • You could improve the VBA design by:

    Moving the code to an XLA/XLAM addin that is parked on a server somewhere so that you have a single point to update.

    Using Named Ranges on the worksheet(s) and referring to them in your VBA code and formulas - so that when rows/columns get inserted your VBA and formulas code still work.

    Consider tightening the protection levels on the worksheet to prevent actions that cause problems with the VBA code.


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

    Saturday, November 19, 2016 12:13 PM

All replies

  • Hi,

    In my opinion, I would chose to update the workbook using Open XML library: Create a small app like windows form, publish by using ClickOnce.

    Create a template and a winform used for update, distribute them to users.

    If we need to update the document, using Open XML library to edit specific parts, then users would receive the update for the winform since it is deployed by ClickOnce. Users could choose the document and update it.

    Regards,

    Celeste

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 18, 2016 9:05 AM
    Moderator
  • Hi Celeste,

    I really appreciate the advice. 

    So if I understand you correctly, you are saying distribute a WinForm and an Excel template (that was generated using Open XML). When I need to make a change to the Excel template, the code from the WinForm would update the Excel template. Do I understand that correctly?

    If I understand that correctly, the users would have to run the WinForm each time they wanted the Excel sheet because how would they know if an update was made? 

    I hope got that right.

    Thank you again for the help. 

    Friday, November 18, 2016 9:09 PM
  • You could improve the VBA design by:

    Moving the code to an XLA/XLAM addin that is parked on a server somewhere so that you have a single point to update.

    Using Named Ranges on the worksheet(s) and referring to them in your VBA code and formulas - so that when rows/columns get inserted your VBA and formulas code still work.

    Consider tightening the protection levels on the worksheet to prevent actions that cause problems with the VBA code.


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

    Saturday, November 19, 2016 12:13 PM
  • Hi Charles,

    Thank you for the great advice. I hadn't thought of using an add-in. It seems like a better way to go then I am doing now.  Plus I have multiple workbooks that share the same code.  

    I currently do keep the Workbook protected and I am using lots of Named Ranges. Where the trouble sometimes comes in is when the business owners make changes to the design/layout of the sheet (they have the password to un-protect the sheet). Then I need to go it an make sure the cells are still lined up and within the Named Ranges.

    Thank you.

    ~J

     p.s. One thing I forgot to ask... If I deploy an Add-in and then it is automatically updated the next time the user opens Excel, how can I have it tested first by the business owners before it is actually deployed? This would be using Click-once?


    • Edited by Jenna_Fire Monday, November 21, 2016 4:24 PM added a question
    Monday, November 21, 2016 4:18 PM
  • Hi,

    It seems that you have found a better solution for your project. I suggest you mark helpful post as answer. 

    If you have new issues, please post new threads, so that we could focus on the specific issue and more comminty members could see and offer different suggestion.

     

    >>how can I have it tested first by the business owners before it is actually deployed?

    Sorry I don’t understand your meaning.

    Do you mean that, after your deploy the new version, you want the users to check if the new version suitable for the old worksheets and then decide whether to update?

    If you develop an XLA/XLAM addin, I think we need to copy it to the users to get new version or use Windows Installer to pack new add-in. And in that way, the add-in could not be updated automatically.

    If you develop an VSTO add-in, we could deploy with ClickOnce and could set it auto-update and users could also convert to old version if the new version is not suitable.

     

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 24, 2016 12:25 PM
    Moderator
  • One solution to a test version for the business owners is:

    • Test version of the addin has a different name to the production version
    • Test version has a workbook open routine that closes the production version xla/xlam and re-initialises
    • You email the test version to the business owners for testing, when they open it as a workbook it replaces the production version, if they don't open it they get the production version automatically loaded as an addin
    • When everyone is happy you remove the workbook open routine, rename to production version and replace the production version on the server.

    Charles


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

    Thursday, November 24, 2016 12:39 PM