Check if the array is 1d array or 2d array

[YasserKhalil]

Hello everyone

I need a function that detects an array to determine if it is 1d array (one dimension) or 2d array (two dimensions).

[HansV]

You could use this function:

Code: Select all

Function WhatIsIt(v As Variant) As String
    Dim n As Long
    If IsArray(v) Then
        On Error Resume Next
        n = LBound(v, 2)
        If Err Then
            WhatIsIt = "1d array"
        Else
            WhatIsIt = "2d (or higher) array"
        End If
    Else
        WhatIsIt = "Not an array"
    End If
End Function

Let's say you have a variable MyVar. You can use

Code: Select all

    Debug.Print WhatIsIt(MyVar)

or

Code: Select all

    Select Case WhatIsIt(MyVar)
        Case "1d array"
            ' do something with 1d array
        Case "2d (or higher) array"
            ' do something with 2d array
        Case Else
            ' do something else
    End Select

[YasserKhalil]

Amazing. Thank you very much.
Another question if possible:
Let's say I have 1d array with five elements [a(1 To 5)]
How can I convert it to 2d array with one row and 5 columns [a(1 to 1, 1 to 5)]?
I have used this UDF

Code: Select all

Function Convert1DTo2D(ByVal arr)
    Dim outputArr, numRows As Long, i As Long
    numRows = UBound(arr) - LBound(arr) + 1
    ReDim outputArr(1 To 1, 1 To numRows)
    For i = LBound(arr) To UBound(arr)
        outputArr(1, i) = arr(i)
    Next i
    Convert1DTo2D = outputArr
End Function

But welcome any other new ideas.

[HansV]

That would fail if LBound(arr) does not equal 1.
Alternative 1:

Code: Select all

Function Convert1DTo2D(ByVal arr)
    Dim outputArr, i As Long
    ReDim outputArr(1 To 1, LBound(arr) To UBound(arr))
    For i = LBound(arr) To UBound(arr)
        outputArr(1, i) = arr(i)
    Next i
    Convert1DTo2D = outputArr
End Function

Alternative 2:

Code: Select all

Function Convert1DTo2D(ByVal arr)
    Dim outputArr, numRows As Long, i As Long
    numRows = UBound(arr) - LBound(arr) + 1
    ReDim outputArr(1 To 1, 1 To numRows)
    For i = LBound(arr) To UBound(arr)
        outputArr(1, i - LBound(arr) + 1) = arr(i)
    Next i
    Convert1DTo2D = outputArr
End Function

[YasserKhalil]

Thank you very much. I appreciate you a lot your support.

[DocAElstein]

Hello
On the issue of a 1D array to a 1 row 2D array conversion, it’s perhaps useful to remember/note here that you can do that through the spreadsheet. It’s a simple way but may not be as efficient as the VBA array way. Possibly as the size goes up the two ways may get closer in performance. I am not sure.

We simply need to paste the array in a spare range, then take it back in

Code: Select all

 Sub WundyToody() ' http://www.eileenslounge.com/viewtopic.php?p=305432#p305432
Dim arrWundyToo() As Variant  ' for any array
 Let arrWundyToo() = Array("a", "b") ' an example 1D array    Watch Window : - Variant/Variant(0 to 1)        {"a", "b"}
 Let ActiveSheet.Range("A100").Resize(1, UBound(arrWundyToo) - LBound(arrWundyToo()) + 1).Value = arrWundyToo()   '  put it anywhere in a spare range in a worksheet.
 Let arrWundyToo() = ActiveSheet.Range("A100").Resize(1, UBound(arrWundyToo) - LBound(arrWundyToo()) + 1).Value  '        Watch Window   Variant/Variant(1 to 1, 1 to 2)
End Sub

I am not sure how best to explain what is going on there. Something like this perhaps….
Excel is just wired like that for our convenience as Humans because of how we perceive things as Humans. Arrays are not really rows , columns , horizontals or verticals, but we as Human perceive them as such, or we find it easier to understand if we choose to perceive them as such.
If we see a 1D array written we usually think it looks like a 1 unit “wide” horizontal thing. We think a row in a spreadsheet looks similarly like a 1 unit “wide” horizontal thing. So for convenience Excel puts a 1D array in a row if we try to put it in anywhere.

What we see as a Spreadsheets is organised to look to us like a 2D grid type structure, so you usually want two pseudo like coordinates to get at stuff there. So a range needs a two co ordinate type reference way, and a 2D array fits that idea nicely. If we take anything out of a range it will reflect that pseudo 2 dimensional structure. Hence we always get a 2D array out, even if we take stuff from a single row.

There could also be some good technical reason and explanation I don’t know about.
_._____________________________________

That last short macro will error if you start with a 1D array with just one value.
You can get over that in two ways
_(i) If you change arrWundyToo() to arrWundyToo , then it won’t error for the case of starting with a 1D array with just one value. But in that case the result will not be an array. Instead it will become a single value since .Value applied to a range returns either an array when applied to a multi cell range , or a single value for when applied to a single cell
_ Alternatively if you want to have always an array out you would need to do something like this:

Code: Select all

 Sub WundyToodyTwo() ' http://www.eileenslounge.com/viewtopic.php?p=305432#p305432
Dim arrWundyToo() As Variant  ' for any array
 Let arrWundyToo() = Array("a") ' an example 1D array    Watch Window : - Variant/Variant(0 to 0)        {"a"}
 Let ActiveSheet.Range("A100").Resize(1, UBound(arrWundyToo) - LBound(arrWundyToo()) + 1).Value = arrWundyToo()
    If UBound(arrWundyToo) - LBound(arrWundyToo()) + 1 = 1 Then     ' an array of just one  element
    ReDim arrWundyToo(1 To 1, 1 To 1)
     Let arrWundyToo(1, 1) = ActiveSheet.Range("A100").Resize(1, UBound(arrWundyToo) - LBound(arrWundyToo()) + 1).Value '        Watch Window   Variant/Variant(1 to 1, 1 to 1)
    Else
     Let arrWundyToo() = ActiveSheet.Range("A100").Resize(1, UBound(arrWundyToo) - LBound(arrWundyToo()) + 1).Value  '
    End If
End Sub

Alan

[YasserKhalil]

Thanks a lot for sharing Mr. Alan