maandag 14 april 2008

Some wishes for Office and Visual Studio

If, but only if you have a licensed copy of Office (Excel, Word, Outlook) AND a licensed copy of Visual Studio Professional and up, you can create your own add-in libraries or you can extend your documents and templates using managed code at the expense of VBA or you can create a mixture from both worlds if you want to save that nicely-working VBA module!

I'd like to see such programmability for Access projects (.accdb databases or .adp projects) and I'd like to see the following:

1. There's a FREE Access 2007 runtime version just in order to run Access databases like applications. I'd like to see a FREE Excel 2007 runtime that can only run the underlying VBA code and only edit cells that are normal editable cells. Like the Access 2007 runtime the menus should be very very crippled just in order to execute that .xls spreadsheet as an application.
2. I'd like to be able to use an Access database or an Excel spreadsheet as a program library INSIDE my VB.NET program WITHOUT the need to 'load and execute' the whole whopping Access or Excel executable and its depending libraries. The .mdb (.accdb) or the .xls should be able to get treated like a normal .dll library.
3. I'd like to be able to re-use Office components in my own software. This should only be possible if the following is met: licensed version of Office AND a licensed version of Visual Studio.NET Professional just in order to bypass the necessity of 'loading and executing' the whole whopping Office application.

Think about an extended DAO/ADO.NET engine for Access databases that adds VBA possibilities to your database in order to create user-defined functions or a VBA version of stored-procedures to make the database more intelligent.
Think about an Excel Spreadsheet engine the cousin of Jet's database engine which is able to create full fledged .xls spreadsheets programmatically.
Think about a grid component for the sake of opening an Excel spreadsheet for data-entry or querying data resulted from calculation by formulas.
Deploying the application to customers should then be royalty-free and there should be some other licensing thoughts.

maandag 7 april 2008

Why VBA can be a P.I.T.A.

I'm maintaining an Excel spreadsheet also for Office 97 because of my work doesn't have the newest Excel. I'm also running that old Office 97 on a VMWare image running Windows'98 as the program. Reason to do is to ensure compatibility of older systems. I was busy with creating macros responding to clicks on userform buttons on the worksheet itself. I saved the bits 'n bytes and I ran the code, but the older code got used instead of the newly typed corrections. Using the debugger had lead Excel to a grinding halt: an invalid page fault occurred in the VBA32.DLL. I felt bad, since I've thought that I've ruined my project. I didn't panic or got ballistic, but I tried the "ruined" project on my other VMWare images running XP Pro sp2. Yes, I've two images: one running Office 2000 and the other Office 2007. Both Offices managed to convert a "ruined" project into a healthy one. I can debug and now the macros are using the corrected code. Then I've tried the "repaired" project back on the Windows 98/Office 97 image and now the problems were gone! It looks like that opening a potentially ruined Excel macro project can be repaired by using an Excel with higher version than the version in which bad things happen like errors in VBA32.DLL.

The best thing is acquiring both Office 2007 and Visual Studio 2008 and converting the VBA project into a managed one. But how you you do this:

1. Open Excel 2007 by clicking the project
2. Open the VBA window
3. Check the Object Inspector and export EVERYTHING to .bas or .cls files which are plain text files.
4. Close Excel
5. Open Visual Studio Professional
6. Create an empty Excel project and select your project
7. Create class-modules from your .cls files. They got suffixed .vb
8. Create module-modules from your .bas files. They got suffixed .vb
9. Correct all errors and please replace the clumsy ON ERROR instructions with TRY instructions and
your code will be very pleasant to read and to maintain!
10. If everything compiles well, you can try to run it.

Now you've got a managed .DLL that will never fail you!