VSTO & .NET & Excel

October 10, 2006

The Transition Case – VBA Part I

Filed under: .NET & Excel — Dennis M Wallentin @ 11:37 am

The DataTool Add-in

This is the basic utility that will be used to review the transition from VBA, via classic VB and also via VB.NET, to finally end up on the VSTO platform as an Add-in.

The utility does the following:

  • Create a customized menu option on the Worksheet Commandbar – DataTool.
  • Create a customized toolbar, which status (visible/hidden and position Top/Floating) is written to / read from the Windows registry.
  • Retrieve data from a local SQL Server 2005 database – Northwind – based on a stored procedure and with ADO.
  • Add a worksheet and populate it with data and do some calculation – Data Report.
  • Add a worksheet, populate it with data and create a Chart – Chart Report.
  • Add a worksheet, populate it with data and create a Pivot Table – Pivot Report.
  • Monitor the following application events:
    NewWorkbok, NewWorksheet, WorkbookOpen
  • It includes a helpfile and also an About form.
  • All error handling is left out. Please see Bullen’s et al book, “Professional Excel Development”, and in particular chapter 12 “VBA Error Handling” for an excellent discussion about this subject.

To some degree the above functionality reflects how I view Excel as a tool for personal analyze of data and presentation. With projects like this one the process starts at one point and during the developing it undergo several changes. In the end You realize You could do some parts of it total different. That’s why written requirements are always best to work with. 😉

The following images show the Menu option as well as the Toolbar:  

The Toolbar


The VBA Project  

For clarity (if possible) I use some more components in the VBA Project and also some more procedures that may be considered as necessary for a utility of this kind.

I prefer to use the Auto_Open and Auto_Close events rather then using the equal events for the object ThisWorkbook.

The options in the DataTool menu are directly hooked to the procedures while the buttons on the toolbar share the same click event.

The following images shows the VBA Project (If You don’t recognize some of the words it’s due to the fact that I use a Swedish version of Excel 2003 to create this add-in with):


The following link is to the zipped file, CaseXLA.zip, which enabling You to download and take part of the solution:  DataTool – VBA add-in

Note 1: The add-in has been updated based on Will Riley’s input. 

The zipped file contains two files, the XLA file and the CHM-file, i e the help file.

Please feel free to comment it and also if You would like to add or remove anything (in view that it’s a case tool only).

Kind regards,



  1. Hi Dennis,

    Thanks for making a start on this. I know you specified no Error Checking code being present at the outset, however I had an issue with these lines of code to name the worksheet, probably due to my settings

    ‘Name for the added worksheet.
    m_stName = “Data_Report” & ” ” & Date

    This gave me an error as the date became formatted using illegal chars i.e. Data_Report 10/10/2006

    I changed the three code references to

    ‘Name for the added worksheet.
    m_stName = “Data_Report” & ” ” & Format(Date, “dd_mm_yyyy”)

    In order to produce

    Data_Report 10_10_2006

    And get rid of the immediate errors (if anyone else is following this they might find they have a similar issue with date formatting)


    PS – Dennis, do WordPress allow you to use the Code Generator plugin here ? Given the amount of code I guess you will be posting you may find it useful ( I have some knowledge of it, as Does Dick & Ross) if you want help with configuration 🙂

    Comment by Will Riley — October 10, 2006 @ 1:22 pm

  2. Good catch Will and I have updated the XLA accordingly.

    It means that the available zipped file include the update 🙂

    Actually, this kind of issues are usually a problem for us who use non-English settings in the Windows operating system.

    Unfortunately WordPress does not allow any plugins. Depending on the blog’s popularity I will move it to an own domain.

    Thanks for Your kind input which is highly appreciated.


    Comment by Dennis Wallentin — October 10, 2006 @ 1:43 pm

  3. Hi Dennis,

    Excuse the ‘stupid’ question but does that mean we need SQL Server 2005 installed?

    Comment by Andy Pope — October 10, 2006 @ 8:44 pm

  4. Andy,

    I think you would at least need an instance of SQL 2005 installed under the defalt “localhost” installation – although if you had a named instance you’d need to amend the connection string…

    I’m pretty sure this would work with even the SQL 2005 Express version (which is free)


    Comment by Will Riley — October 10, 2006 @ 10:19 pm

  5. Andy,

    Per se – No but I prefer to work with SQL Server 2005 (and as Will pointed out a free express edition is available) instead of using any Jet Database.

    Just make sure that the retrieved data is placed in the A (Names) & B column (Volume) in the added worksheet where the cell A3 is the starting position.

    In the following article a Jet Database (‘Access’) is used and instead of ‘stored procedure’ we use ‘stored questions’.

    HOWTO: Execute macros, procedures and stored questions

    In summary:
    Create a simple SQL Expression (SELECT Products, Volume FROM Tablename’) in the JET Database and save it.

    Change the connection string (MZTools has a tool to generate it from the VB-editor in Excel as well some other tools.).

    Change the way the stored question is called as the above article indicates via the example ‘Execute Stored questions’.

    Kind regards,

    Comment by Dennis Wallentin — October 10, 2006 @ 11:16 pm

  6. Hi,
    Quick question regarding stored “SQL” procedures. The advantages of this are?


    Comment by Ross — October 13, 2006 @ 6:15 pm

  7. Ross,

    Good question!

    I don’t want to start a thread ‘in the thread’ about stored procedures (sp) vs dynamic SQL queries. Will and I had recently a discussion about it on his blog where he pointed to an ongoing discussion about it – http://wills-blog.com/?p=48

    There are some general advantages of using sp where security, performance and the possibilities to easy maintance are the keys to it.

    BTW, do You believe that the transition case is doable for You?

    Kind regards,

    Comment by Dennis Wallentin — October 13, 2006 @ 7:14 pm

  8. Thanks Dennis over there now.

    Yes, I think this will be doable, I’m intrested to see the .Net versons, both the VS, and the VSTO.

    thanks Dennis.

    Comment by ross — October 13, 2006 @ 9:56 pm

  9. Dennis,

    I am joining the party a bit late but just to let you know the VBA version worked first time for me. Now moving on to VB.Net.



    Comment by Carl Mackinder — October 29, 2006 @ 3:27 pm

  10. Hi Carl,

    Good to see You around and also that the case works as expected 🙂

    Before moving to .NET we will make a stop with the classic COM Add-in.

    Kind regards,

    Comment by Dennis Wallentin — October 29, 2006 @ 4:31 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: