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

 

[GlennUK]

Gee, I wish you'd had said where you got the NumberOfArrayDimensions function from ... it was Chip Pearson wasn't it. Do you think that people are psychic or something???

That said, your answer is that you are generating an array that is equivalent to a range object, which has a dimension for columns, and another for rows, even if the object is only equivalent to a single column.

 

[Neutralizer]

Yep it is Chip's indeed. I'm actually using his whole DistinctValues function, but it is (I think) having an error with the number of array dimensions. I thought the cause lay in the array that was being generated and passed to it using the if function and the array formula all in one...

Given your answer though, I would have thought the DistinctValues function would work as normal (and not thrown a value error) as it uses a variant to handle spreadsheet ranges that are passed to it...

If you are familiar with the Chip's UDF, can you explain why passing it this produces a value error:
{=DistinctValues(IF(RR_Date_Check=Input_Prod_DATE,Input_Prod_RR_LABEL,""))}

However, this combination works fine:
D1 to however many cells are required has the array formula:
{=DistinctValues(C1:C100)}
Where the range C1:C100 is filled with values from the array formula:
{=IF(RR_Date_Check=Input_Prod_DATE,Input_Prod_RR_LABEL,""))}

Why can't I combine them into one formula?

I suspect I'm missing something fundamental about the nature of either variant or range objects...

 

[Neutralizer]

To confirm what GlennUK has said, you cna read here:
http://msdn.microsoft.com/en-us/library/aa139976(v=office.10).aspx

But in essence:

When the values in a range are assigned to a <CODE class=ce>Variant</CODE>, the indexes of the array that is created are always one-based, not zero-based, regardless of the <CODE class=ce>Option</CODE> <CODE class=ce>Base</CODE> setting in the declarations section of the module. Also, the array always has two dimensions, even if the range has only one row or one column. This preserves the inherent column and row structure of the worksheet in the array and is an advantage when you write the array back to the worksheet.

 

[GlennUK]

If you put a trace through Chip's code you can see that it's not taking the array as a range ... and then it tests only for 0 or 1 dimensions ( because it's not a range ), otherwise returning an error. Maybe you could tweak his VBA to suit your formula.

 

[mikerickson]

Range("A1:A100") is 100 rows X 1 column

Setting myArray = Range("A1:A100").Value includes ReDim myArray(1 to 100, 1 to 1)

Two dimensions, Ubound(myArray,1) = 100, LBound(myArray, 2) = UBound(myArray,2) = 1

Similarly Range("A1:G1") returns a two dimension array (1 to 1, 1 to 7)

To get a one D array

OneDArray = Application.Transpose(Range("A1:A100").Value)

OtherOneDArray = Application.Transpose(Application.Transpose(Range("A1:G1").Value))

 

[Peter_SSs]

To get a one D array

OtherOneDArray = Application.Transpose(Application.Transpose(Range("A1:G1").Value))

.. or for that case possibly

Another1DArray = Application.Index(Range("A1:G1").Value, 1, 0)

 

[pgc01]

... or even

v = Application.Index(Range("A1:G1").Value, 0)

 

[mikerickson]

Hmm. .I don't understand why Index in not symmetric in that usage.

Sub test()
   Dim cRRay As Variant, rRRay As Variant
   
    cRRay = Application.Index(Range("A1:C3").Value, 0, 2)
    MsgBox UBound(cRRay, 1) & " X " & UBound(cRRay, 2): Rem 3 X 1
    
    rRRay = Application.Index(Range("A1:C3").Value, 2, 0)
    MsgBox UBound(rRRay, 2): Rem subscript out of range error
End Sub

 

[pgc01]

Hi Mike

I guess its because of the .Value bit

rRRay = Application.Index(Range("A1:C3")[COLOR=red][B].Value[/B][/COLOR], 2, 0)

The .Value causes excel to get an array of values out of the range before indexing, and so the result is an horizontal, one dimensional array.

If you index the range object instead, you'll get, of course, the same values, but this time in a 2 dimensional array, because this time you first index the range, getting another range, and only then you write the values into the array.

If you index the ranges directly in your code:

Sub test()
   Dim cRRay As Variant, rRRay As Variant
 
    cRRay = Application.Index(Range("A1:C3"), 0, 2)
    MsgBox UBound(cRRay, 1) & " X " & UBound(cRRay, 2): Rem 3 X 1
 
    rRRay = Application.Index(Range("A1:C3"), 2, 0)
    MsgBox UBound(rRRay, 1) & " X " & UBound(rRRay, 2): Rem 1 X 3
End Sub

In this case it's symmetrical, in both cases you get a 2 dimensional array.

This works both ways, you can also write a 1 dimensional array into a horizontal range, like

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