2014-04-12

Workbook Close Event Capture

For anyone that has had to capture the WorkbookBeforeClose event and struggled with the imperfectness of the process, this is C# code that fires only if the workbook is completely closed.

Kudos to the developer; Wordpress entry is here.

2014-03-17

Development Speed in VBA

Lately, I have been asked why something cannot be done faster: As far how to make the process faster:

2013-10-24

Advice to Young Programmer

In response to a Google+ group question, "Hey guys! Does anyone have an tips on how to pre-plan a library or a program in general?", I wrote the following:

Just some general ideas:
For myself, I like to head out to a coffee shop and draw my app, the UI, the classes and relationships, and messaging between components. It looks a bit like a UML diagram.

I recently built a first release Excel add-in for a portfolio manager to retrieve somewhat large data sets (50K rows by 85 columns). The PM's requirements were speed, query flexibility, and small UI footprint. This size is not normally a problem, but there are limitations in .NET when dealing with Excel. I have separate libraries for threaded SQL execution, desktop logging - there are better off-the-shelf products like Log4Net - , and query table management. The architecture is Model-View-Presenter since that both works well for this kind of app, but also provides an upgrade path to a Web UI. The Excel UI is a mixture of Ribbon groups and a separate task panel.

I searched to find best methods for dynamic SQL that avoided SQL injection, as well as experimented a bit to find a fast method for retrieving large data sets to Excel via .NET. I used a query table, native to Excel, which provided several requirements natively.
read all...