6
\$\begingroup\$

Some folks told that a two-dimensional array can solve almost all the problems, but let me disagree with that. That's why I made this Deca dimensional array sort function. The array must be symmetrical, meaning that it must contain elements corresponding to all the existing elements in the other dimensions.

I didn't put any error handler, because depending on usage purposes, it can take different shapes.

I tried for several days to find a way to avoid such multiple statements by using an on-the-fly variable generator to replace the index of array in the loop. I didn't succeeded, so the question is: did someone know a way to replace the seqArr(i) = arr(s(0), i, s(2) with: seqArr(i) = something based on the s variable (which already is generating the same result)?

The improvement I need is regarding the length of the code. Actually the code is limited to 10 dimensions because of its length, with a solution regarding repetitive statement in the loop it can be extended to an unlimited number of dimensions.

I also don't want to use Microsoft scripting runtime library on this.

Function SortArray(ByRef arr As Variant, ByVal selPoint As Variant, ByRef selDim As Integer, Optional ByRef ascend As Boolean = True) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Deca Dimensional Ascending and Descending Array Sort Function
'Inputs:
'1) arr         = one to 10 dimensional symmetrical array
'2) selPoint    = selected point index as string e.g. "arr(1,15,4)" or just "(1,15,4)"
'3) selDim      = selected dimension, integer from 1 to 10
'4) ascend      = Optional ascending or descending direction (default = ascending)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim seq As Variant, seqArr As Variant, s As Variant
Dim i As Integer, j As Integer, arrDim As Integer

On Error Resume Next
Do
    arrDim = arrDim + 1
    seq = UBound(arr, arrDim)
Loop Until Err.Number <> 0
arrDim = arrDim - 1
On Error GoTo 0

ReDim seq(UBound(arr, selDim))
For i = LBound(seq) To UBound(seq)
seq(i) = i
Next i

'following array is already generating necessary array index but
'I didn't find a way to use it to avoid such multiple statements
s = Split(Split(selPoint, "(")(1), ")")(0)
s = Split(s, ",")

selPoint = ""
For i = 1 To arrDim
If i = selDim Then selPoint = selPoint & "i," Else selPoint = selPoint & "s(" & i - 1 & "),"
Next i

ReDim seqArr(0)
For i = LBound(seq) To UBound(seq)
ReDim Preserve seqArr(i)
If arrDim = 1 Then
seqArr(i) = arr(s(0))
ElseIf arrDim = 2 And selDim = 1 Then
seqArr(i) = arr(i, s(1))
ElseIf arrDim = 2 And selDim = 2 Then
seqArr(i) = arr(s(0), i)
ElseIf arrDim = 3 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2))
ElseIf arrDim = 3 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2))
ElseIf arrDim = 3 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i)
ElseIf arrDim = 4 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3))
ElseIf arrDim = 4 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3))
ElseIf arrDim = 4 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3))
ElseIf arrDim = 4 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i)
ElseIf arrDim = 5 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4))
ElseIf arrDim = 5 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4))
ElseIf arrDim = 5 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4))
ElseIf arrDim = 5 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4))
ElseIf arrDim = 5 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i)
ElseIf arrDim = 6 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4), s(5))
ElseIf arrDim = 6 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4), s(5))
ElseIf arrDim = 6 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4), s(5))
ElseIf arrDim = 6 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4), s(5))
ElseIf arrDim = 6 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i, s(5))
ElseIf arrDim = 6 And selDim = 6 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), i)
ElseIf arrDim = 7 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4), s(5), s(6))
ElseIf arrDim = 7 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4), s(5), s(6))
ElseIf arrDim = 7 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4), s(5), s(6))
ElseIf arrDim = 7 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4), s(5), s(6))
ElseIf arrDim = 7 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i, s(5), s(6))
ElseIf arrDim = 7 And selDim = 6 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), i, s(6))
ElseIf arrDim = 7 And selDim = 7 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), i)
ElseIf arrDim = 8 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4), s(5), s(6), s(7))
ElseIf arrDim = 8 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4), s(5), s(6), s(7))
ElseIf arrDim = 8 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4), s(5), s(6), s(7))
ElseIf arrDim = 8 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4), s(5), s(6), s(7))
ElseIf arrDim = 8 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i, s(5), s(6), s(7))
ElseIf arrDim = 8 And selDim = 6 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), i, s(6), s(7))
ElseIf arrDim = 8 And selDim = 7 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), i, s(7))
ElseIf arrDim = 8 And selDim = 8 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), i)
ElseIf arrDim = 9 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4), s(5), s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4), s(5), s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4), s(5), s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4), s(5), s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i, s(5), s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 6 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), i, s(6), s(7), s(8))
ElseIf arrDim = 9 And selDim = 7 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), i, s(7), s(8))
ElseIf arrDim = 9 And selDim = 8 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), i, s(8))
ElseIf arrDim = 9 And selDim = 9 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7), i)
ElseIf arrDim = 10 And selDim = 1 Then
seqArr(i) = arr(i, s(1), s(2), s(3), s(4), s(5), s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 2 Then
seqArr(i) = arr(s(0), i, s(2), s(3), s(4), s(5), s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 3 Then
seqArr(i) = arr(s(0), s(1), i, s(3), s(4), s(5), s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 4 Then
seqArr(i) = arr(s(0), s(1), s(2), i, s(4), s(5), s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 5 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), i, s(5), s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 6 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), i, s(6), s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 7 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), i, s(7), s(8), s(9))
ElseIf arrDim = 10 And selDim = 8 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), i, s(8), s(9))
ElseIf arrDim = 10 And selDim = 9 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7), i, s(9))
ElseIf arrDim = 10 And selDim = 10 Then
seqArr(i) = arr(s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7), s(8), i)
Else
End If
Next i

seq = SortSeq(seqArr, seq, LBound(seq), UBound(seq), ascend)

SortArray = ParseArray(arr, seq, selDim)

End Function

Private Function SortSeq(ByRef iArr As Variant, ByRef sArr As Variant, ByRef iDnRow As Long, ByRef iUpRow As Long, Optional ByRef ascend As Boolean = True) As Variant

Dim oArr As Variant, vArr As Variant
Dim vDnRow As Long, vUpRow As Long

vDnRow = iDnRow
vUpRow = iUpRow
oArr = iArr((iDnRow + iUpRow) \ 2)

While (vDnRow <= vUpRow)

If ascend = True Then
    While (iArr(vDnRow) < oArr And vDnRow < iUpRow)
       vDnRow = vDnRow + 1
    Wend
    While (oArr < iArr(vUpRow) And vUpRow > iDnRow)
       vUpRow = vUpRow - 1
    Wend
Else
    While (iArr(vDnRow) > oArr And vDnRow < iUpRow)
       vDnRow = vDnRow + 1
    Wend
    While (oArr > iArr(vUpRow) And vUpRow > iDnRow)
       vUpRow = vUpRow - 1
    Wend
End If

If (vDnRow <= vUpRow) Then
   vArr = iArr(vDnRow)
   iArr(vDnRow) = iArr(vUpRow)
   iArr(vUpRow) = vArr
   vArr = sArr(vDnRow)
   sArr(vDnRow) = sArr(vUpRow)
   sArr(vUpRow) = vArr
   vDnRow = vDnRow + 1
   vUpRow = vUpRow - 1
End If

Wend

If (iDnRow < vUpRow) Then SortSeq iArr, sArr, iDnRow, vUpRow, ascend
If (vDnRow < iUpRow) Then SortSeq iArr, sArr, vDnRow, iUpRow, ascend

SortSeq = sArr

End Function

Private Function ParseArray(ByRef baseArray As Variant, ByRef seq As Variant, ByRef d As Integer) As Variant
Dim selDim As Integer, baseDim As Integer
Dim d1 As Integer, d2 As Integer, d3 As Integer, d4 As Integer, d5 As Integer
Dim d6 As Integer, d7 As Integer, d8 As Integer, d9 As Integer, d10 As Integer
Dim tmpArray As Variant

On Error Resume Next
Do
    baseDim = baseDim + 1
    tmpArray = UBound(baseArray, baseDim)
Loop Until Err.Number <> 0
baseDim = baseDim - 1
On Error GoTo 0
tmpArray = baseArray

selDim = 1
For d1 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
    If baseDim > selDim Then
    selDim = selDim + 1
    For d2 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
        If baseDim > selDim Then
        selDim = selDim + 1
        For d3 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
            If baseDim > selDim Then
            selDim = selDim + 1
            For d4 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                If baseDim > selDim Then
                selDim = selDim + 1
                For d5 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                    If baseDim > selDim Then
                    selDim = selDim + 1
                    For d6 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                        If baseDim > selDim Then
                        selDim = selDim + 1
                        For d7 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                            If baseDim > selDim Then
                            selDim = selDim + 1
                            For d8 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                If baseDim > selDim Then
                                selDim = selDim + 1
                                For d9 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                    If baseDim > selDim Then
                                    selDim = selDim + 1
                                    For d10 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                            If d = 1 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 2 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 3 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 4 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 5 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8, d9, d10)
                                            ElseIf d = 6 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8, d9, d10)
                                            ElseIf d = 7 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8, d9, d10)
                                            ElseIf d = 8 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8), d9, d10)
                                            ElseIf d = 9 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, seq(d9), d10)
                                            ElseIf d = 10 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, seq(d10))
                                            Else
                                            End If
                                    Next d10
                                    selDim = selDim - 1
                                    Else
                                        If d = 1 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 2 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 3 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 4 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8, d9)
                                        ElseIf d = 5 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8, d9)
                                        ElseIf d = 6 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8, d9)
                                        ElseIf d = 7 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8, d9)
                                        ElseIf d = 8 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8), d9)
                                        ElseIf d = 9 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, seq(d9))
                                        Else
                                        End If
                                    End If
                                Next d9
                                selDim = selDim - 1
                                Else
                                    If d = 1 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8)
                                    ElseIf d = 2 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8)
                                    ElseIf d = 3 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8)
                                    ElseIf d = 4 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8)
                                    ElseIf d = 5 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8)
                                    ElseIf d = 6 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8)
                                    ElseIf d = 7 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8)
                                    ElseIf d = 8 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8))
                                    Else
                                    End If
                                End If
                            Next d8
                            selDim = selDim - 1
                            Else
                                If d = 1 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7)
                                ElseIf d = 2 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7)
                                ElseIf d = 3 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7)
                                ElseIf d = 4 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7)
                                ElseIf d = 5 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7)
                                ElseIf d = 6 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7)
                                ElseIf d = 7 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7))
                                Else
                                End If
                            End If
                        Next d7
                        selDim = selDim - 1
                        Else
                            If d = 1 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(seq(d1), d2, d3, d4, d5, d6)
                            ElseIf d = 2 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, seq(d2), d3, d4, d5, d6)
                            ElseIf d = 3 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, seq(d3), d4, d5, d6)
                            ElseIf d = 4 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, seq(d4), d5, d6)
                            ElseIf d = 5 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, d4, seq(d5), d6)
                            ElseIf d = 6 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, d4, d5, seq(d6))
                            Else
                            End If
                        End If
                    Next d6
                    selDim = selDim - 1
                    Else
                        If d = 1 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(seq(d1), d2, d3, d4, d5)
                        ElseIf d = 2 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, seq(d2), d3, d4, d5)
                        ElseIf d = 3 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, seq(d3), d4, d5)
                        ElseIf d = 4 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, d3, seq(d4), d5)
                        ElseIf d = 5 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, d3, d4, seq(d5))
                        Else
                        End If
                    End If
                Next d5
                selDim = selDim - 1
                Else
                    If d = 1 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(seq(d1), d2, d3, d4)
                    ElseIf d = 2 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, seq(d2), d3, d4)
                    ElseIf d = 3 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, d2, seq(d3), d4)
                    ElseIf d = 4 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, d2, d3, seq(d4))
                    Else
                    End If
                End If
            Next d4
            selDim = selDim - 1
            Else
                If d = 1 Then
                baseArray(d1, d2, d3) = tmpArray(seq(d1), d2, d3)
                ElseIf d = 2 Then
                baseArray(d1, d2, d3) = tmpArray(d1, seq(d2), d3)
                ElseIf d = 3 Then
                baseArray(d1, d2, d3) = tmpArray(d1, d2, seq(d3))
                Else
                End If
            End If
        Next d3
        selDim = selDim - 1
        Else
            If d = 1 Then
            baseArray(d1, d2) = tmpArray(seq(d1), d2)
            ElseIf d = 2 Then
            baseArray(d1, d2) = tmpArray(d1, seq(d2))
            Else
            End If
        End If
    Next d2
    selDim = selDim - 1
    Else
    baseArray(d1) = tmpArray(seq(d1))
    End If
Next d1

ParseArray = baseArray

End Function

A simple test sub:

Sub testSortArray()

Dim arr As Variant

ReDim arr(1, 1, 5)

arr(0, 0, 0) = 0
arr(0, 0, 1) = 1
arr(0, 0, 2) = 2
arr(0, 0, 3) = 3
arr(0, 0, 4) = 4
arr(0, 0, 5) = 5
arr(0, 1, 0) = 10
arr(0, 1, 1) = 11
arr(0, 1, 2) = 12
arr(0, 1, 3) = 13
arr(0, 1, 4) = 14
arr(0, 1, 5) = 15
arr(1, 0, 0) = 100
arr(1, 0, 1) = 101
arr(1, 0, 2) = 102
arr(1, 0, 3) = 103
arr(1, 0, 4) = 104
arr(1, 0, 5) = 105
arr(1, 1, 0) = 110
arr(1, 1, 1) = 111
arr(1, 1, 2) = 112
arr(1, 1, 3) = 113
arr(1, 1, 4) = 114
arr(1, 1, 5) = 115

'arr = SortArray(arr, "arr(0,1,0)", 1, False)
'arr = SortArray(arr, "arr(0,1,0)", 2, False)
arr = SortArray(arr, "arr(0,1,0)", 3, False)

End Sub
\$\endgroup\$
3
  • 2
    \$\begingroup\$ Can you tell us why you don't want to use the scripting runtime? \$\endgroup\$
    – RubberDuck
    Sep 14, 2015 at 11:47
  • \$\begingroup\$ Because this function is just a part from a bigger work. The final result should not contain it. For security reasons. \$\endgroup\$
    – Sorin GFS
    Sep 14, 2015 at 13:43
  • 1
    \$\begingroup\$ Security reasons? What security concerns do you have? (I asked the first time to help improve the question, now I'm genuinely curious.) \$\endgroup\$
    – RubberDuck
    Sep 14, 2015 at 13:50

2 Answers 2

6
\$\begingroup\$

Let's start with the obvious. If you're trying to do anything with a 10-d array other than put it in a proper database, you're doing it wrong.

That aside, in no particular order, let's begin:


You have too much energy. Seriously. After the second elseif or so your mind should be going "hmm, this seems inefficient, there must be a simpler way to do this".

There is. It's called a Select...Case Statement: MSDN.

This code:

If arrDim = 1 Then
seqArr(i) = arr(s(0))
ElseIf arrDim = 2 And selDim = 1 Then
seqArr(i) = arr(i, s(1))
ElseIf arrDim = 2 And selDim = 2 Then
seqArr(i) = arr(s(0), i)
....

Becomes:

Select Case arrDim

    Case Is = 1
    seqArr(i) = arr(s(0))

    Case Is = 2
        Select Case selDim

            Case Is = 1
            seqArr(i) = arr(i, s(1))

            Case Is = 2
            seqArr(i) = arr(s(0), i)

        End Select

    Case Is = 3
    ......

Select Case is cleaner than elseif, easier to read, easier to follow, and much easier to Alter.


Much better idea: Scrap it entirely, re-write it as a for...loop. And then refactor that into a function that returns the desired array:

For i = LBound(seq) To UBound(seq)

    seqArr(i) = GenerateSequenceArray(s, i, arrDim, selDim)

Next i

Public Function GenerateSequenceArray(ByRef s as Variant, ByVal i as integer, ByVal arrDim as integer, ByVal selDim as integer) As Variant

    Dim j As Long

    Dim arr As Variant
        arr = Array()
        ReDim arr(1 To arrDim)
            For j = 1 To arrDim
                If j = i Then
                    arr(j) = i
                    Else
                    arr(j) = s(j - 1)
                End If
            Next j

            GenerateSequenceArray = arr

End Function

From 115 lines of code to 12. And now, it's absolutely clear what's going on, and it's incredibly easy to change.


Variable Naming: What is s? What does s tell me about the variable? Is it a variable? Or is it just some random mis-typed letter that got into the compiler because Option Explicit wasn't turned on? Meaningful Names. Always.

Do you know what I thought it was? a counter variable. Why? Because it's almost a universal programming law that single letter variables are counters E.G. dim i as long, j as long, k as long. Sure, it's not as neat and tidy, but neat and tidy is useless if I have no idea what's going on.

The same continues elsewhere. I've got seq, seqArr, s. Now, the first 2 are probably sequences of some kind. And the second is supposed to be an array, but beyond that, I've no idea what they are or what they contain.


What is seq?

ReDim seq(UBound(arr, selDim))
For i = LBound(seq) To UBound(seq)
seq(i) = i
Next i

So seq() just contains ascending numbers for each index in the selected dimension? How is that useful, or even necessary? If/when I need it, I'll just write:

For i = 1 to UBound(arr, selDim)
    <code>
Next i

Which is also much clearer about what's going on.


I'm not even going to try and work out how ParseArray works. It's 200 lines of nested for...loops and elseif statements that's been made to work through sheer stubbornness. I guarantee that it can be reduced to a small function like the one above for seqArr. I leave that as an exercise for you.

As a general rule, if the sub/function you're writing is over 50 lines. You should stop, take a long hard look and just check that you can't simplify / refactor it further.

\$\endgroup\$
8
  • \$\begingroup\$ First of all, thank you for fast replay and for your effort. \$\endgroup\$
    – Sorin GFS
    Sep 14, 2015 at 13:44
  • \$\begingroup\$ It co-incided nicely with my lunch break :) \$\endgroup\$
    – Kaz
    Sep 14, 2015 at 13:45
  • \$\begingroup\$ Your first solution actually is adding more rows. For the second solution I will start with the end: if we could find a solution for lowering the number of rows for the SortArray function, the same solution will work for ParseArray function. I tried your nice code, but it gives a TypeMismach error. S variable is just a substitute array for arr array's indexes. Seq variable is a single dimensional sort sequence generated using quick sort algorithm. Useful for rearrange entire array. You could use locals for clearer picture. \$\endgroup\$
    – Sorin GFS
    Sep 14, 2015 at 13:57
  • \$\begingroup\$ w/r/t more rows: yes, it is. But better does not always mean fewer rows. That's code golf. IMO readability, clarity and editability are far more important than having fewer lines. I literally can't think of any situation where I'd use 2 elseifs over a select case. \$\endgroup\$
    – Kaz
    Sep 14, 2015 at 14:00
  • \$\begingroup\$ w/r/t to my function not working perfectly. Apologies for that. but, the principles are the important part. I wrote the best I could given what I could understand of your code and what time I had. Given that you wrote it and understand it much better, you should be able to sort out any syntax / reference mistakes. And/or write your own version. \$\endgroup\$
    – Kaz
    Sep 14, 2015 at 14:02
1
\$\begingroup\$

Here you can find a modified version based on Zak's ideas: using select case and splitting the code in a way that makes possible of using code parts as support for various functions like 'CopyArrayRow', 'RemoveArrayRow', etc...

I didn't use the same method to the ParseArray function because I still hope for a solution to fill array's index dynamically.

'Option Explicit off (or make ix1, ix2,... public variables)

Function SortArray(ByRef arr As Variant, ByVal selPoint As Variant, ByRef selDim As Integer, Optional ByRef ascend As Boolean = True) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Deca Dimensional Ascending and Descending Array Sort Function
'Inputs:
'1) arr         = one to 10 dimensional symmetrical array
'2) selPoint    = selected point index as string e.g. "arr(1,15,4)" or just "(1,15,4)"
'3) selDim      = selected dimension, integer from 1 to 10
'4) ascend      = Optional ascending or descending direction (default = ascending
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim seq As Variant, seqArr As Variant, s As Variant
Dim i As Integer, j As Integer, arrDim As Integer

    On Error Resume Next
    Do
        arrDim = arrDim + 1
        seq = UBound(arr, arrDim)
    Loop Until Err.Number <> 0
    arrDim = arrDim - 1
    On Error GoTo 0

    ReDim seq(UBound(arr, selDim))
    For i = LBound(seq) To UBound(seq)
    seq(i) = i
    Next i

    s = Split(Split(selPoint, "(")(1), ")")(0)
    s = Split(s, ",")

    ReDim seqArr(0)
    For i = LBound(seq) To UBound(seq)
    ReDim Preserve seqArr(i)

        Select Case arrDim
            Case Is = 1
                seqArr = arr
            Case Is = 2
                seqArr = ListFromArray2d(arr, selDim, s(0), s(1))
            Case Is = 3
                seqArr = ListFromArray3d(arr, selDim, s(0), s(1), s(2))
            Case Is = 4
                seqArr = ListFromArray4d(arr, selDim, s(0), s(1), s(2), s(3))
            Case Is = 5
                seqArr = ListFromArray5d(arr, selDim, s(0), s(1), s(2), s(3), s(4))
            Case Is = 6
                seqArr = ListFromArray6d(arr, selDim, s(0), s(1), s(2), s(3), s(4), s(5))
            Case Is = 7
                seqArr = ListFromArray7d(arr, selDim, s(0), s(1), s(2), s(3), s(4), s(5), s(6))
            Case Is = 8
                seqArr = ListFromArray8d(arr, selDim, s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7))
            Case Is = 9
                seqArr = ListFromArray9d(arr, selDim, s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7), s(8))
            Case Is = 10
                seqArr = ListFromArray10d(arr, selDim, s(0), s(1), s(2), s(3), s(4), s(5), s(6), s(7), s(8), s(9))
        End Select

    Next i

    seq = SortSeq(seqArr, seq, LBound(seq), UBound(seq), ascend)

    SortArray = ParseArray(arr, seq, selDim)

End Function

Public Function ListFromArray2d(arr, selDim, ix1, ix2)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2)
            Case Is = 2
                    tempArr(i) = arr(ix1, i)
        End Select

    Next i

    ListFromArray2d = tempArr

End Function

Public Function ListFromArray3d(arr, selDim, ix1, ix2, ix3)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i)
        End Select

    Next i

    ListFromArray3d = tempArr

End Function

Public Function ListFromArray4d(arr, selDim, ix1, ix2, ix3, ix4)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i)
        End Select

    Next i

    ListFromArray4d = tempArr

End Function

Public Function ListFromArray5d(arr, selDim, ix1, ix2, ix3, ix4, ix5)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i)
        End Select

    Next i

    ListFromArray5d = tempArr

End Function

Public Function ListFromArray6d(arr, selDim, ix1, ix2, ix3, ix4, ix5, ix6)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5, ix6)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5, ix6)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5, ix6)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5, ix6)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i, ix6)
            Case Is = 6
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, i)
        End Select

    Next i

    ListFromArray6d = tempArr

End Function

Public Function ListFromArray7d(arr, selDim, ix1, ix2, ix3, ix4, ix5, ix6, ix7)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5, ix6, ix7)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5, ix6, ix7)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5, ix6, ix7)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5, ix6, ix7)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i, ix6, ix7)
            Case Is = 6
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, i, ix7)
            Case Is = 7
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, i)
        End Select

    Next i

    ListFromArray7d = tempArr

End Function

Public Function ListFromArray8d(arr, selDim, ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5, ix6, ix7, ix8)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5, ix6, ix7, ix8)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5, ix6, ix7, ix8)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5, ix6, ix7, ix8)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i, ix6, ix7, ix8)
            Case Is = 6
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, i, ix7, ix8)
            Case Is = 7
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, i, ix8)
            Case Is = 8
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, i)
        End Select

    Next i

    ListFromArray8d = tempArr

End Function

Public Function ListFromArray9d(arr, selDim, ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8, ix9)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5, ix6, ix7, ix8, ix9)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5, ix6, ix7, ix8, ix9)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5, ix6, ix7, ix8, ix9)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5, ix6, ix7, ix8, ix9)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i, ix6, ix7, ix8, ix9)
            Case Is = 6
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, i, ix7, ix8, ix9)
            Case Is = 7
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, i, ix8, ix9)
            Case Is = 8
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, i, ix9)
            Case Is = 9
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8, i)
        End Select

    Next i

    ListFromArray9d = tempArr

End Function

Public Function ListFromArray10d(arr, selDim, ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8, ix9, ix10)

Dim i As Long, LB As Long, UB As Long
Dim tempArr As Variant

    tempArr = Array()

    LB = LBound(arr, selDim)
    UB = UBound(arr, selDim)

    ReDim tempArr(UB)

    For i = LB To UB

        Select Case selDim
            Case Is = 1
                    tempArr(i) = arr(i, ix2, ix3, ix4, ix5, ix6, ix7, ix8, ix9, ix10)
            Case Is = 2
                    tempArr(i) = arr(ix1, i, ix3, ix4, ix5, ix6, ix7, ix8, ix9, ix10)
            Case Is = 3
                    tempArr(i) = arr(ix1, ix2, i, ix4, ix5, ix6, ix7, ix8, ix9, ix10)
            Case Is = 4
                    tempArr(i) = arr(ix1, ix2, ix3, i, ix5, ix6, ix7, ix8, ix9, ix10)
            Case Is = 5
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, i, ix6, ix7, ix8, ix9, ix10)
            Case Is = 6
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, i, ix7, ix8, ix9, ix10)
            Case Is = 7
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, i, ix8, ix9, ix10)
            Case Is = 8
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, i, ix9, ix10)
            Case Is = 9
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8, i, ix10)
            Case Is = 10
                    tempArr(i) = arr(ix1, ix2, ix3, ix4, ix5, ix6, ix7, ix8, ix9, i)
        End Select

    Next i

    ListFromArray10d = tempArr

End Function

Private Function SortSeq(ByRef iArr As Variant, ByRef sArr As Variant, ByRef iDnRow As Long, ByRef iUpRow As Long, Optional ByRef ascend As Boolean = True) As Variant

Dim oArr As Variant, vArr As Variant
Dim vDnRow As Long, vUpRow As Long

vDnRow = iDnRow
vUpRow = iUpRow
oArr = iArr((iDnRow + iUpRow) \ 2)

While (vDnRow <= vUpRow)

If ascend = True Then
    While (iArr(vDnRow) < oArr And vDnRow < iUpRow)
       vDnRow = vDnRow + 1
    Wend
    While (oArr < iArr(vUpRow) And vUpRow > iDnRow)
       vUpRow = vUpRow - 1
    Wend
Else
    While (iArr(vDnRow) > oArr And vDnRow < iUpRow)
       vDnRow = vDnRow + 1
    Wend
    While (oArr > iArr(vUpRow) And vUpRow > iDnRow)
       vUpRow = vUpRow - 1
    Wend
End If

If (vDnRow <= vUpRow) Then
   vArr = iArr(vDnRow)
   iArr(vDnRow) = iArr(vUpRow)
   iArr(vUpRow) = vArr
   vArr = sArr(vDnRow)
   sArr(vDnRow) = sArr(vUpRow)
   sArr(vUpRow) = vArr
   vDnRow = vDnRow + 1
   vUpRow = vUpRow - 1
End If

Wend

If (iDnRow < vUpRow) Then SortSeq iArr, sArr, iDnRow, vUpRow, ascend
If (vDnRow < iUpRow) Then SortSeq iArr, sArr, vDnRow, iUpRow, ascend

SortSeq = sArr

End Function

Private Function ParseArray(ByRef baseArray As Variant, ByRef seq As Variant, ByRef d As Integer) As Variant
Dim selDim As Integer, baseDim As Integer
Dim d1 As Integer, d2 As Integer, d3 As Integer, d4 As Integer, d5 As Integer
Dim d6 As Integer, d7 As Integer, d8 As Integer, d9 As Integer, d10 As Integer
Dim tmpArray As Variant

On Error Resume Next
Do
    baseDim = baseDim + 1
    tmpArray = UBound(baseArray, baseDim)
Loop Until Err.Number <> 0
baseDim = baseDim - 1
On Error GoTo 0
tmpArray = baseArray

selDim = 1
For d1 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
    If baseDim > selDim Then
    selDim = selDim + 1
    For d2 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
        If baseDim > selDim Then
        selDim = selDim + 1
        For d3 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
            If baseDim > selDim Then
            selDim = selDim + 1
            For d4 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                If baseDim > selDim Then
                selDim = selDim + 1
                For d5 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                    If baseDim > selDim Then
                    selDim = selDim + 1
                    For d6 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                        If baseDim > selDim Then
                        selDim = selDim + 1
                        For d7 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                            If baseDim > selDim Then
                            selDim = selDim + 1
                            For d8 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                If baseDim > selDim Then
                                selDim = selDim + 1
                                For d9 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                    If baseDim > selDim Then
                                    selDim = selDim + 1
                                    For d10 = LBound(baseArray, selDim) To UBound(baseArray, selDim)
                                            If d = 1 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 2 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 3 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 4 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8, d9, d10)
                                            ElseIf d = 5 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8, d9, d10)
                                            ElseIf d = 6 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8, d9, d10)
                                            ElseIf d = 7 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8, d9, d10)
                                            ElseIf d = 8 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8), d9, d10)
                                            ElseIf d = 9 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, seq(d9), d10)
                                            ElseIf d = 10 Then
                                            baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, d10) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, d9, seq(d10))
                                            Else
                                            End If
                                    Next d10
                                    selDim = selDim - 1
                                    Else
                                        If d = 1 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 2 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 3 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8, d9)
                                        ElseIf d = 4 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8, d9)
                                        ElseIf d = 5 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8, d9)
                                        ElseIf d = 6 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8, d9)
                                        ElseIf d = 7 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8, d9)
                                        ElseIf d = 8 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8), d9)
                                        ElseIf d = 9 Then
                                        baseArray(d1, d2, d3, d4, d5, d6, d7, d8, d9) = tmpArray(d1, d2, d3, d4, d5, d6, d7, d8, seq(d9))
                                        Else
                                        End If
                                    End If
                                Next d9
                                selDim = selDim - 1
                                Else
                                    If d = 1 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7, d8)
                                    ElseIf d = 2 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7, d8)
                                    ElseIf d = 3 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7, d8)
                                    ElseIf d = 4 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7, d8)
                                    ElseIf d = 5 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7, d8)
                                    ElseIf d = 6 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7, d8)
                                    ElseIf d = 7 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7), d8)
                                    ElseIf d = 8 Then
                                    baseArray(d1, d2, d3, d4, d5, d6, d7, d8) = tmpArray(d1, d2, d3, d4, d5, d6, d7, seq(d8))
                                    Else
                                    End If
                                End If
                            Next d8
                            selDim = selDim - 1
                            Else
                                If d = 1 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(seq(d1), d2, d3, d4, d5, d6, d7)
                                ElseIf d = 2 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, seq(d2), d3, d4, d5, d6, d7)
                                ElseIf d = 3 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, seq(d3), d4, d5, d6, d7)
                                ElseIf d = 4 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, seq(d4), d5, d6, d7)
                                ElseIf d = 5 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, seq(d5), d6, d7)
                                ElseIf d = 6 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, d5, seq(d6), d7)
                                ElseIf d = 7 Then
                                baseArray(d1, d2, d3, d4, d5, d6, d7) = tmpArray(d1, d2, d3, d4, d5, d6, seq(d7))
                                Else
                                End If
                            End If
                        Next d7
                        selDim = selDim - 1
                        Else
                            If d = 1 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(seq(d1), d2, d3, d4, d5, d6)
                            ElseIf d = 2 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, seq(d2), d3, d4, d5, d6)
                            ElseIf d = 3 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, seq(d3), d4, d5, d6)
                            ElseIf d = 4 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, seq(d4), d5, d6)
                            ElseIf d = 5 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, d4, seq(d5), d6)
                            ElseIf d = 6 Then
                            baseArray(d1, d2, d3, d4, d5, d6) = tmpArray(d1, d2, d3, d4, d5, seq(d6))
                            Else
                            End If
                        End If
                    Next d6
                    selDim = selDim - 1
                    Else
                        If d = 1 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(seq(d1), d2, d3, d4, d5)
                        ElseIf d = 2 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, seq(d2), d3, d4, d5)
                        ElseIf d = 3 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, seq(d3), d4, d5)
                        ElseIf d = 4 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, d3, seq(d4), d5)
                        ElseIf d = 5 Then
                        baseArray(d1, d2, d3, d4, d5) = tmpArray(d1, d2, d3, d4, seq(d5))
                        Else
                        End If
                    End If
                Next d5
                selDim = selDim - 1
                Else
                    If d = 1 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(seq(d1), d2, d3, d4)
                    ElseIf d = 2 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, seq(d2), d3, d4)
                    ElseIf d = 3 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, d2, seq(d3), d4)
                    ElseIf d = 4 Then
                    baseArray(d1, d2, d3, d4) = tmpArray(d1, d2, d3, seq(d4))
                    Else
                    End If
                End If
            Next d4
            selDim = selDim - 1
            Else
                If d = 1 Then
                baseArray(d1, d2, d3) = tmpArray(seq(d1), d2, d3)
                ElseIf d = 2 Then
                baseArray(d1, d2, d3) = tmpArray(d1, seq(d2), d3)
                ElseIf d = 3 Then
                baseArray(d1, d2, d3) = tmpArray(d1, d2, seq(d3))
                Else
                End If
            End If
        Next d3
        selDim = selDim - 1
        Else
            If d = 1 Then
            baseArray(d1, d2) = tmpArray(seq(d1), d2)
            ElseIf d = 2 Then
            baseArray(d1, d2) = tmpArray(d1, seq(d2))
            Else
            End If
        End If
    Next d2
    selDim = selDim - 1
    Else
    baseArray(d1) = tmpArray(seq(d1))
    End If
Next d1

ParseArray = baseArray

End Function
\$\endgroup\$
4
  • \$\begingroup\$ Can you explain what you mean by "'Option Explicit off (or make ix1, ix2,... public variables)"? You should always be using Option Explicit. \$\endgroup\$ Sep 19, 2015 at 20:46
  • \$\begingroup\$ I always use 'Option Explicit', but not everyone does. This code is a combination between my initial code and Zak's workaround. Since you can't find definition for ix1, ix2... variables, this means that you can't use 'Option Explicit', or if you want to use it you must define those variables as 'Public' ix1, ix2... in order to be used shared between functions. \$\endgroup\$
    – Sorin GFS
    Sep 20, 2015 at 14:09
  • \$\begingroup\$ But aren't ix1, etc parameters passed to the function? (On my phone so hard to check) \$\endgroup\$ Sep 20, 2015 at 20:41
  • \$\begingroup\$ Variables included as arguments are defined, just as much as if you'd written "dim ix". You may find this useful: MSDN \$\endgroup\$
    – Kaz
    Sep 21, 2015 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.