Here's the code and results:
Sub test300()
Dim arr1()
ReDim arr1(1 To 1, 1 To 1)
Set rng1 = ThisWorkbook.Worksheets(1).Range("e3")
For i = 1 To 1: For j = 1 To 1
arr1(i, j) = rng1(i, j)
Debug.Print rng1(i, j) & " " & arr1(i, j) '-----------------------------------------------prints 1/10/2018 1/10/2018
Debug.Print TypeName(rng1(i, j)) & " " & TypeName(arr1(i, j)) '---------------------------prints Range Date
Debug.Print IsNumeric(rng1(i, j)) & " " & IsNumeric(arr1(i, j)) '-------------------------prints False False
Debug.Print Application.IsNumber(rng1(i, j)) & " " & Application.IsNumber(arr1(i, j)) '---prints True False
Debug.Print Application.Count(rng1) & " " & Application.Count(arr1) '---------------------prints 1 0
Next: Next
'The Excel Count function and IsNumber function are consistent with each other, but both treat the value differently
'depending on whether it is in a range or an array. Certainly presents hurdles to the programmer.
End Sub
Bookmarks