When Code Breaks

Revising a code is way different from creating a code from scratch. My company switched over to a new payroll vendor and the new vendor’s data file is almost – but not quite – the same as the old data layout, but it is that tiny little variance that broke the code. Most of the column layout is the same, although there are a few new additions, and the field order is the same. So I wasn’t expecting a lot of problems with column layouts and I don’t think that is my major problem.

And yet I probably spent 3 weeks trying to fix the VBA program. At first I tried to make the code updates in a simple manner without going through the code using the Step Into feature of VBA. For a week, I avoided using the Step Into method of running the code line by line but in the end, I found no easy way to identify places in the code to update the columns. One of the things that I had thought about after I finished the main program a couple of months ago was to figure an easy way of identifying the number of columns in a data file and make the program adjust easily to the variations in the number of columns. I hadn’t quite come up with a repeatable mechanism and I figured that the columns would be my headache when it came time to adjust the program to suit a new data file.

And, yep, the columns were a problem but it turned out they weren’t the major problem. There were two other problems that were more problematic for me: 1) using the shortcut keys for maneuvering your cursor around the screen no longer worked as expected so range selections, sorting, and subtotaling gave unexpected results; and 2) unit description did not fall under its own column but fell in the same columns as the totals columns which again led to unintended results.

Those two wrinkles were what took me the longest to resolve. Simple little differences led to large coding efforts.

But, on the flip side, it looked like I no longer needed code to fix the time charges for certain people - that part of the code could be shut down. So that part appears to go away – for the most part.

Here’s a brief outline of the general process I went through to fix the program, actually 2 programs:

1. First tried running the program just to see what happens, even though I recognized that some columns were added. Just hoping that the problems weren’t severe.
2. But the end results told me that there were other issues besides new columns.
3. Since I had set up my program in short modules, I proceeded to test each module to see where the problems first start to appear.
4. I focused on each module individually and Stepped Into each module to see which lines were the culprit. Most lines were easily fixed by adjusting the columns.
5. When I found the issue of the new wrinkles, especially those shortcut keys that were not working, it was here that the finding the solution took a long time coming. Sometimes I had to take breaks and do something else in the effort to clear out my mind.
6. I’m going to repeat that last statement because it may not be typical: when I find that I’m not figuring out an answer to the problem despite many attacks, I will take a break and do something totally different in order to get my mind off of the thinking rut. It’s an effort to jiggle my mind out of the thinking groove it’s been in and into another one, with a fresh idea to test.
7. Work through module by module, turning each one on when it’s time to work on them.
8. Finally, test the entire program in one piece.

The thing that I should have done when creating the program from scratch was develop a mechanism whereby the program recognizes how many columns of data there are in the data file. But…that really wasn’t the big issue in the reprogramming effort but it would have made the updates a lot simpler.

The good thing that I did when creating the program from scratch was embedding the capability to turn off parts of the program and chunking up the program into modules. The capability to turn off parts of the program was done so that the user could turn them off in case something different and unexpected happened with some data and the program no longer worked properly. The concern was mainly in the area of programming to fix people’s time charges. I originally set the program to make the adjustments but if it was necessary, the user could make the fixes manually herself if the program didn’t work.

I also created a mechanism for adding new reports so long as the format of the report were the same as the one already created in the program, but I doubt this mechanism wouldn’t have made the programming changes any easier.

So fixing up a program to encompass a change in a data file can surprisingly take a while to resolve.