20 May 2013

Excel add-in using VSTO and VBA

To find out more about Concentra's bespoke Analytics Services and Solutions see Services.

WHAT IS IT?

VSTO (Visual Studio Tools for Office) is a great means of customising Office applications, especially the UI. VSTO allows us to customise task panes which is impossible with VBA (Visual Basic for Applications). It is much easier to customize ribbons in VSTO using ribbon designer, which makes creating new groups and adding controls to the ribbon as intuitive as adding control to a windows form, compared to VBA which uses RibbonX. Microsoft has stopped making any updates to VBA for the last decade and has been promoting developers to work on VSTO, even then it is still the primary choice for many developers because of the Macro recording feature which helps the developers to record a certain repetitive task and then modify to suit their requirements using Visual Basic Editor.

VSTO is a part of the Microsoft Visual Studio .Net suite used for developing application-level or document-level Add-ins for Microsoft Office Word, Excel, Access and Outlook Applications. Microsoft launched VSTO in 2003 as an alternative to VBA.

Programs written in VSTO are executed by a separate virtual machine called CLR (Common Language Runtime). Unlike VBA code which is stored in the document file itself, programs written in VSTO are stored in separate CLI assemblies which are associated with the documents by means of custom properties.

How to use it?

VSTO can be used to extend office applications for several uses. An example where we have used VSTO is to create custom ribbon, assist in validating, and pre populating an excel sheet. Specifically, VSTO was used for pre-populating data in our sheet, linked in with VBA to validate the data entered by user and to trigger events on change of data/sheet to ensure the correct data was entered by the user.

Creating the Excel add-in

To create a VSTO project:

  • File -> New -> Project in visual studio 2010
  • Under installed templates go to
    • Visual C# -> Office -> Select Excel 2007/2010/2013 Add-In
    • Name the Solution and click ok

  • Right click on the Solution Name and then Go to
    • Add -> New Item
  • Select Ribbon (Visual Designer) and Add

This will give you a ribbon on the screen where you can drag and drop controls from the toolbox on the left hand side.

  • Drag a button from the toolbar on the ribbon.
  • Right click on the button and go to properties
  • Change the name and label of the button
  • You can change the size of the button by changing the ControlSize property.

Note: If the ControlSize is large than showlabel property is True and cannot be changed from the properties window.

  • Double click on button to start writing your code.

Access Excel workbook/worksheet/cells from C#

To access workbook elements use following lines of code:

This will give you indexes (Row/column) of the current selection in numeric. But columns in Excel are in alphabets and to convert column numbers that are returned above in to excel columns use the function below:

This will give us Excel column names.

Call VBA Macros using C#

To call VBA macros using C# use the following method.

And then invoke your VBA macro using the following syntax: In this situation we consider that we are invoking a VBA subroutine called “RemoveSpaces” on Button Click.

Note: ThisWorkBook is the name of the VBA project in which my subroutine is located. This is done to avoid any conflicts.

Create VBA Code to Connect to Databases

VBA Code can be used to connect to databases to retrieve values while accepting values from the excel cells or popup windows.

The VBA code below is accepting the name of the database to be connected and then retrieving values from the table and printing to the excel sheet.

 *Note: While developing/testing excel custom plugins, Excel may crash several times due to which Excel sometimes disables the plugins that are responsible for abruptly closing the workbook or create problem in opening the workbook. As this might happen several times while developing and debugging, make sure to change the ControlID -> ControlIDType property of the ribbon to ‘Custom’. This property is found under properties tab of the ribbon designer.

Publish the code

Once the code is complete, you can publish the code by going to Build -> Publish. This will take you through the series of steps to create an .EXE file. Which can then be easily shared and deployed on other machines.

Final Solution

Here is the glimpse of the final solution that can help developers to insert metadata in excel sheet making their work faster also much less prone to errors, and the fact that all the customisation is within the friendly excel environment makes learning curve almost negligible hence making it easier to adapt.

Summary

VSTO is a great way of customising excel sheets and the fact that the .Net framework is available with all its debugging and testing features makes it a great experience for .Net developers. Whereas the simplicity to record macros and write VBA code to validate excel sheets makes automation process quicker. Here is the list of key features of both the languages, this can help developers to choose depending on their requirements.

Key features of VBA:

  • The key feature of VBA is Macro recording that makes it really easy to learn office API by recording actions.
  • Integrated development environment.
  • Access to the entire object model of excel.

Key features of VSTO:

  • Make use of entire .Net framework.
  • Custom UI using visual ribbon designer.
  • Enhanced security model.
  • Microsoft recommended approach for new office solutions development.

*Header logo is credited to magnusfive.com