Every now and again in my job I am asked to slip back in time and look at some legacy code that is now not playing ball. Today was one of those days as I was given the task to investigate some ancient VBA that sat behind MS project 97.
The most enjoyable aspect of my current role is the variety. Yesterday I was looking at performance tweaks to a bunch of T-SQL stored procedures that were taking an age to run (more on that another day) and today it was some C#, VBA and excel formulas.
The problem that was occurring with the VBA was in the pay rates object of the project file. Basically, the VBA was interrogating the object model and dumping selected elements out to excel. The first problem I encountered was that the project file was linked to a project server that provided some custom elements to the project and enabled access to a lot of back-end data on costs, resources etc. Once I had the project file linked to the server it was a simple case of run the macro that performed the export and see where it was blowing up.
After a few minutes into the export the debugger stopped in a function that was iterating through a table of costs and was complaining about "invalid value for argument". A quick look in the watch windows was showing that the pay rates table object contained a collection of pay rates. The count of the collection showed 27 objects and under closer investigation I could see that the debugger had stopped whilst processing the 26th pay rate. Looking at the 26th object a bit closer it was obvious that it was corrupt and didn't contain any information , as did the 27th object in the list. I flipped back to the project from the VBA editor to look at the table and saw that the table only contained 25 objects. Strange.
A quick Google on the pay rates object found that the table in project can only hold 25 rows but there was no explanation of how I was getting 27 out of the count. I switched the count and index loop for a for each loop to see if there was a bug in the count but again it attempted to iterate through 27 objects before blowing up on the 26th. The quick fix was to put a limiter on the count if it exceeded 25 and stop it processing the last objects and indeed this sorted the problem but I couldn't really explain why it was happening. Further trawls through the forums and blogs found nothing so I was forced to go back to the customer with the quick fix and three guesses on what might be happening.
My first guess was that there was a bug in the collection that was just not returning the correct count of objects in the collection. My second guess was that there might be some cleanup issues where the users had deleted some rows and then overwritten them and the collection had a bug which was not cleaning up the dead rows correctly. This would explain why they were full of junk. My third guess was that there was some other MS project setting that I was just failing to spot that was somehow hiding items in the collection or extending the collection to store extra detail.
I presented my conclusion and quick fix to the customer along with the recommendation that they first look at upgrading the version of project so that at least there will be more support. I also suggested that a more thorough review of the code at some point would be beneficial to ensure that there were no other strange things happening that were going unnoticed.
Overall it was a nice little side problem to get my teeth into and although it still nags me that I didn't find out why it was happening the customer was satisfied with the result. Hopefully I'll be able to give an update soon on what the root cause was.
No comments:
Post a Comment