Array Dimensions Problem

[Neutralizer]

I have the following formula:

{=NumberOfArrayDimensions(IF(RR_Date_Check=Input_Prod_DATE,Input_Prod_RR_LABEL,""))}

RR_Date_Check is a named range for a single cell (i.e. $D$1)
Input_Prod_DATE is a named range (i.e. $A$1:$A$100)
Input_Prod_LABEL is a corresponding named range (i.e. $B$1:$B$100)

Can anyone explain to me why this is producing an array with more than 1 dimension? (i'm using a reliable VBA function to determine the number of dimensions in the array, it's coming back as 2...). To my mind this should produce a 1x100 array full of text strings (i.e. RR_Prod_Labels) or blanks cells (i.e. "")...where the hell is the second dimension coming from?

VBA function:

Function NumberOfArrayDimensions(Arr As Variant) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NumberOfArrayDimensions
' This returns the number of dimensions of the array
' Arr. If Arr is not an array, the result is 0.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim LB As Long
Dim N As Long
On Error Resume Next
N = 1
Do Until Err.Number <> 0
LB = LBound(Arr, N)
N = N + 1
Loop
NumberOfArrayDimensions = N - 2
End Function

 

[mikerickson]

OK.
1D array to range works row wise

Range("A1:C1").Value = Array(1, 2, 3)

but not column wise

Range("A1:A3").Value = Array(1, 2, 3):Rem no good

Using INDEX to extract a row from a 2D array of values results in 1D array

MsgBox UBound(Application.Index(Range("A1:C3").Value, 3, 0), 2):Rem errors

Using INDEX to extract a column from a 2D array of values results in a 2D array

MsgBox UBound(Application.Index(Range("A1:C3").Value, 0, 3), 2):Rem 3

Using INDEX to extract a row (or column) of cells from a rectangular array of cells and then taking the values of that row (or column) results in a 2D array.

MsgBox UBound(Application.Index(Range("A1:C3").Cells, 2, 0).Value), 2):Rem 1
MsgBox UBound(Application.Index(Range("A1:C3").Cells, 0, 2).Value), 2):Rem 3

Which brings us full circle to the OP question.