Monday, August 16, 2010

OpenOffice VBA Macros

I want to use OpenOffice to file the income tax return online. This created the compulsion of learning to debug VBA.

On the beta version of OO3.3, the Worksheet_Change method is called but fails. The reason for the failure appear to be that some VBA objects still need to be defined, e.g. Application.EnableEvents.

In addition, the target parameter, Target, does not seem to have all the expected attributes of a range object, e.g. Target.Validation.Type and Target.Name.Name. I am not even sure what 'Name.Name' implies!

As most of the validations were now being done with cell functions, I decided to ignore the Worksheet_Change method. Actually, I had no choice if I wanted to progress further.

Worksheet_Change is actually called by Worksheet_Change_OnChange_Proxy, which maps OpenOffice functionality to that expected by Excel VBA. I commented the call to Worksheet_Change in this method and the validation code is ignored.

Now, the generation of the xml file went through but no file was created. Furthermore, when I forced a file name, the xml file was created but all the data fields had no value.

The issue is that the cells had names sheet1.FirstName_2 while the code was looking for sheet1.FirstName. The subscript '_2' seems to be added by OpenOffice. This may be because the sheet1 is actually the 2nd sheet! Since OpenOffice cannot use the same name on multiple sheets, this is probably a safety measure to prevent name conflicts. However, the VBA code does not know about the name change in the sheets.

I have filed these issues with the VBA project of OpenOffice.

A workaround would be to rename the cells back to what the code expects.

I plan to use a Python macro. The macro will be external to the sheet. It can be applied the first time a new tax return spreadsheet is used.

The Python module may continue to be useful because while I expect Novel and Oracle's VBA project to resolve the issues with Worksheet_Change method, I am not so sure if the issue with range names can be easily resolved.

No comments:

Post a Comment