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 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).