VBA – Why Range.Find() sometimes mistakes January with November and Febrary with December?

After writing that the built-in Find() function in VBA does not return the first value by default, a new interesting case with the Find function appeared. In order to simulate it, the following task could be imagined:

  • Write the first date of the months from January 2016 to June 2018 on a row
  • Find 01-January-2016 and color it in red
  • Use Range.Find()

This is the code I have created to solve the task:

Running it gets the following picture:



In the picture above Nov 16 is marked in red, as far as it is found from the .Find() method. The .Find() uses xlPart search by default parameter, when it is not specified. A test with LookAt:xlWhole returns the correct result. However, it still seems a bit strange, because there should be a way that the searched value, “Jan 16” is somehow present in the found string “Nov 16”.

This is where my research question pops up automatically – how is 1-January-2016 partially equal to 1-November-2016?

After some trial and errors,  it was found that whenever .Find(LookAt:=xlPart) is used upon a range of dates, it takes the dates not with their .Value2, but it silently converts them to String following the American date format – MM/DD/YY and looks into this string. The display format of the date in Excel is completely irrelevant, as long as the cell is formatted as a date.

Thus, every day of January can be found in November and every day of February can be found in December as a substring, making possible 58 (or 59 in a leap year) different mistakes within a calendar year:

So, what are the ways to avoid such situation, if we are using Range.Find()? The answer is pretty clear – set explicitly the optional LookAt parameter to search for the whole value. Like this – LookAt:= xlWhole.

Tagged with: , , , ,