Sunday, August 29, 2010

Fascinating lectures on justice

The courses I enjoyed even more than physics and mathematics were in philosophy and psychology. Hence, I found the lectures on http://www.justiceharvard.org/ very stimulating. The first lecture should probably be seen by all, or at least by those who are certain about their belief in what is right or just.

The ninth lecture is worth seeing to, perhaps, reflect on how India should address the issue of 'fair' admissions in colleges. All I can say is that uniformity of rules is not the answer.

Monday, August 23, 2010

Python Macro

Writing a macro in Python for renaming cells turned out to be simple. Use of the dir command and just trying various options helped.


def chgSheet1RangeNames( ):
"""This macro changes names to the Excel values"""
#get the doc from the scripting context which is made available to all scripts
model = XSCRIPTCONTEXT.getDocument()
names = model.NamedRanges
name_list = [names.getByIndex(i).getName() for i in range(names.getCount())
if names.getByIndex(i).getName().startswith('sheet1')
and names.getByIndex(i).getName().endswith('_2')]
for n in name_list:
rng = names.getByName(n)
rng.setName(n[:-2])
return None


I selected the names which needed to be renamed and renamed them one at a time.

Unfortunately, some hidden functions gave errors. So, the best option was to create additional names for the same ranges. This was also easier than expected. Instead of

rng.setName(n[:-2])

I used

names.addNewByName(n[:-2], rng.getContent(), rng.getReferencePosition(), 0)

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.

Saturday, August 7, 2010

Income Tax Returns using OpenOffice

The income tax department makes some spreadsheets available(ITRx) for online filing of the return. The spreadsheets are for Excel and contain VBA macros.

I could fill the form but could not get it to generate the XML files. I filed the return on paper this year. However, I decided to get started for next year and see if I could make these forms work on OpenOffice. Since I do not have access to Excel and my knowledge of VBA macros was zero, it was hard even to know what was expected.

Finally, I feel a little less ignorant.

On Fedora and Arch Linux, the macros were commented. On Ubuntu, they were visible. It turned out that vba module is enabled in all; however, on Fedora and Arch Linux, I had to enable 'Executable Code' in Options => Load/Save => VBA Properties. I was confused and thought that VBA module was only available in go-oo.

I am still puzzled as 'Load Basic code' was enabled and I am still ignorant about the difference between Basic code and Executable code!

The ITRx uses the event Worksheet_Change to validate the data as it is being entered. However, the validation was happening even if the macros were commented. It appears that the protected cells within the ITRx sheets are doing the validation as well. Hence, the Worksheet_Change function is not really critical.

OpenOffice 3.2 seems to have had a regression error in handling VBA code. Hence, I had problems on the current versions of Fedora and Ubuntu. Since few complaints were visible, it would appear that not too many Open Office users use VBA macros.

In any case, OpenOffice 3.2.1 worked better. However, the Worksheet_Change method was not called on it and neither were the macros triggered by the buttons in the spreadsheets.

Finally, I downloaded the development version. Now the Worksheet_change macro is being called as well as the calling of the macros on clicking a button. So, I can concentrate on checking out the core of what does or does not work in the ITRx macros. And, hopefully, find an easy way to get over the limitations.

Incidentally, it seems much easier to experiment with development version of OpenOffice while keeping the original installation intact. Arch Linux makes it even easier by offering beta and development versions of Open Office in its repository. All three can be installed concurrently.

The Yes Men and Bhopal

Approximately half-hour of the yes men save the world movie is the most effective documentary on the Bhopal tragedy I have seen.

It was particularly disturbing and depressing to hear the journalist whose claim to fame is that he failed. All his efforts to bring the possibility of disaster to the notice of various authorities failed.

Dow Chemicals paid $2billion to 14 victims of asbestos poisoning by Union Carbide in the US. We are aware of the amount they paid to clean up the Bhopal mess.

The area is still not cleaned up of the pollution.

The link: http://vodo.net/theyesmen

The timing of watching this movie was particularly moving. The press, the media, the politicians have all been clamouring for greater punishment for those who headed Union Carbide's Indian subsidiary and, of course, punishing the ceo of the parent company. And if these people were actually surprised by the outcome, they are even more incompetent than anyone could have expected.

If anyone talked about the survivors, I did not hear it. It was lost in the noise.