Where's Art?

I don't like it if I don't have a post every day. So I must have a reason, right? Well yeah: There's the lawn to mow...

Apart from that, I've been working on a little project that I thought would go quick, so that if I missed a day or two the tradeoff would be that I got the project done and then everything would be back to normal. But it's taking longer than I thought. So here's a progress report.

This morning there was an error in my Excel VBA code:

I clicked the DEBUG button to bring up the code with the error highlighted:

The roDate function takes the value ro (which is a row-number in my spreadsheet) and gives me back the date it finds in the date column on that row of the sheet.

But I don't think the error is in the roDate function. Because it was working until now. I think the error must be in the calling function, the part of the code that uses roDate to get the date from the spreadsheet.

See that yellow arrow in the margin, by the highlighted line? I dragged that arrow down a row to highlight the End Function line and clicked F8 to execute that one line of code. That brought me back to the calling code:

The highlighted line here is the next line after the line that calls the roDate function. VBA is still trying to work its way through the code, and mindlessly moves on to the next line. So I have to read the line that comes before the highlighted line:

currentDate = roDate(D, ro)

because that it is the one that uses my roDate function, the one we were looking at in the images above.

One line up from the currentDate line there is the For ro = dateRo line, and above that is the Dim firstData line. These are the lines I have to check.

Just like with the economy. If the thing crashed in 2008, you don't look for the cause in 2008. You look in the years before 2008.

So I hovered my mouse over the names of variables in those few lines of code. When you hover the mouse like that, a little tag pops up that tells you the value of the variable. When I hovered over the variable named firstData a tag popped up that said "firstData = 0.5".

But the value is supposed to be the date of the first unlagged value (as you can tell by reading the whole Dim firstData line).

The value is supposed to be a date. 0.5 is not a date. The DateOfFirstUnlaggedValue function doesn't give back a date. There's the problem.

So I went and looked at the DateOfFirstUnlaggedValue function.

As the highlighted line shows, the DateOfFirstUnlaggedValue function gets the value 0.5 from dt, the variable named dt. Aha! And one line up from there, the variable dt gets the value from the spreadsheet, the active sheet, from the cell at row number ro and column number D.UnlaggedCol.

That's gotta be where the problem is. I'm looking in the wrong column. I'm looking in the column of unlagged values instead of in the column of dates.

For reference, here's the part of the spreadsheet with the dates and values:

First column is dates. The other three columns are used to make three lines on a graph. The "lagged" and "unlagged" columns have the same values now. After I make the lagging work right, values on the "lagged" column will appear in later years or, in some cases, they will be different values altogether.

My code is looking in the UnlaggedCol (column 5) when it should be looking in the DateCol (column 1). As you can see, the row of data for 2001 has the value 0.50 in the UnlaggedCol. That's where that number came from.

So all I had to do to fix the problem -- after tracking it down, which is where the work is -- was to change D.UnlaggedCol to D.DateCol and that was it. Here it is, after the fix:

After making that change, the DateOfFirstUnlaggedValue function actually returns a date:

I put the mouse on the firstData variable, and the tag that popped up says 2001. What more could I ask?

Thinking about it, you know, I named the variable firstData. But I didn't want the data. I wanted the year. Maybe I had the error because I confused myself by calling it the first data. So I went in and changed the name of that variable.

So, where's Art? Now you know.