4

As a follow-up question to an answer I posted here I would like to know why the following doesn't error, is this a possible bug in VBA?

Take the following data:

example data set
If we use the following VBA code, we will receive an error because we need to use the numeric value of the date to match:

'//Produces error  
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14"), 0)

and so either of these statements will work: 

'// Cast to Long
Debug.Print WorksheetFunction.Match(CLng(Range("C3").Value), Range("A1:A14"), 0)
'// Access .Value2 property directly
Debug.Print WorksheetFunction.Match(Range("C3").Value2, Range("A1:A14"), 0)

However as pointed out by Jean-François Corbett, if we don't specify a property it also works:

Debug.Print WorksheetFunction.Match(Range("C3"), Range("A1:A14"), 0)

So if .Value doesn't work, and this is the default property of the Range object - why does it work in the above example?

Could this be a bug? Or is there some level of evaluation taking place that counteracts this?

Community
  • 1
  • 1
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • https://msdn.microsoft.com/en-gb/library/office/ff835873.aspx - Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. So in this case `WorksheetFunction.Match(Range("C3"), Range("A1:A14"), 0)` will not use the default method of the `Range` object but the `Range` object itself. – Axel Richter Apr 09 '15 at 10:35
  • Even still, in this scenario `Range("C3").Value` and `Range("C3")` would be evaluated and return the same "dd/mm/yyyy" value to be used in the function instead of the numeric value - so it should still error? – SierraOscar Apr 09 '15 at 10:41
  • 1
    Sure ;-)? You know exactly how `WorksheetFunction.Match` internally works? I don't know because it is not open source. – Axel Richter Apr 09 '15 at 10:44
  • This is exactly what I'm trying to find out! If you use `Debug.Print` for `Range("C3").Value` and `Range("C3")` you get the same output because they are evaluated in the same way - so this makes me think that the underlying code for the `Match()` method has some other evaluation going on... – SierraOscar Apr 09 '15 at 10:46
  • Hm, `Debug.Print` must use the default method `.Value` because it needs a value which can be cast to string. My suspicion is `WorksheetFunction.Match` will use the `Range` object. I don't know what it is doing then with this internally. – Axel Richter Apr 09 '15 at 10:52
  • Which leads me to think 2 things: a) We may never know the answer to this question. b) VBA can itself imply a required method or value instead of using the default when a specific data type is needed. – SierraOscar Apr 09 '15 at 10:56

1 Answers1

1

I don't think it is a bug. If you run below macro:

Debug.Print Range("C3"), Range("C3").Value, Range("C3").Value2

1st two will return returns identical results.

3/2/2015 3/2/2015 42065

Which confirms the default property as Value for Range Object.
It is worth noting though that if you explicitly use Value all throughout, it will work as well.

Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14").Value, 0)

So I think that it is another manifestation of the issue once described here.

If you don't explicitly define Value property for both, Excel is smart enough to assume that you're matching Values. However, if you explicitly imply one property but leave Excel guessing for the other, it will not work.

L42
  • 19,427
  • 11
  • 44
  • 68
  • I tried `.Value` with both arguments - it still didn't work. In fact, if I use `.Value` at all in the 2nd argument the code fails - is it expecting a range object instead of an array? – SierraOscar Apr 09 '15 at 09:45
  • MSDN implies that an array can be used as the 2nd argument - I can only think that it may not work here because `.Value` will produce a 2 dimensional array, although again I cannot see why that wouldn't work. This is causing more questions *d'oh* – SierraOscar Apr 09 '15 at 10:33
  • Win 7 32bit and XL2010 - you? – SierraOscar Apr 09 '15 at 13:09
  • @SO I'm on XL2013. Anyways I can't find a ways to check how `Match` processes it's operation. I just based my answer on the link I've posted. – L42 Apr 09 '15 at 13:16
  • Yeah I don't think a definitive answer will arrive soon unfortunately, appreciate the effort though cheers :) – SierraOscar Apr 09 '15 at 13:20