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!

Geen opmerkingen: