Populate one dimensional array without looping

Question

The following line of code will allocate an array as one dimensional sortedArr = WorksheetFunction.Transpose(lstIssues1.List). This line of code allocates an array as two dimensional arrIssues = Table.ListColumns(Table.ListColumns(strNumber).Range.column).DataBodyRange

For each of the above lines I call a bubble sort function. But it errors on one or the other depending on whether I put

If Arr(i) > Arr(j) Then ...

Or

If Arr(i, 1) > Arr(j, 1) Then ...

I can loop to fill arrIssues. But I am wondering if it's possible to fill it as a one dimensional array without looping.

UPDATE

Here is the code I am having trouble with

Private Sub cmdRemove_Click()
Dim SortedArr() As Variant
    With lstPrevious
        If .ListIndex = -1 Then Exit Sub
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) = True Then
                lstAdditional.AddItem .List(i)
                .RemoveItem (i)
            End If
        Next i
    End With
ReDim SortedArr(lstAdditional.ListCount - 1)
    SortedArr = Application.Transpose(lstAdditional.List) 'ERROR Type Mismatch
    Call BubbleSort(SortedArr)
    Me.lstAdditional.List = SortedArr
    txtFocus.SetFocus
End Sub

Public Sub BubbleSort(Arr)
Dim strTemp As String
Dim lngMin As Long
Dim lngMax As Long
    lngMin = LBound(Arr)
    lngMax = UBound(Arr)
    For i = lngMin To lngMax
        For j = i + 1 To lngMax
            If Arr(i) > Arr(j) Then
                strTemp = Arr(i)
                Arr(i) = Arr(j)
                Arr(j) = strTemp
            End If
        Next j
    Next i
End Sub

Strangely, I use the same logic in another userform and it works. Sorry for the lack of clarity.

Maybe this is what you are looking for? [VBA (Excel) Initialize Entire Array without Looping](http://stackoverflow.com/questions/19336987/vba-excel-initialize-entire-array-without-looping) — Dr. Cogent, Sep 14 '16 at 16:54
Thanks, but I need to enter different values from a range. Just wanting to do it without loops. But I may end up looping. — Brian, Sep 14 '16 at 17:14
Two possible solutions, if I understand you correctly. `1`: Transpose the 2D array (if the array is not greater than the upper limit for Transpose, which varys depending on your version of Excel). Or `2`: Check to see how many dimensions are in your array, and then apply the appropriate sort formula. — Ron Rosenfeld, Sep 14 '16 at 18:14
What do people have against looping all of a sudden? Does typing `For foo = 1 to bar` really take longer than writing a question on SO? — Comintern, Sep 14 '16 at 18:24
Try this: remove the parentheses from `Dim SortedArr As Variant`, and remove the `Redim` statement. — trincot, Sep 14 '16 at 19:10
@trincot Thanks for the suggestion but I still got the `Type Mismatch` error. — Brian, Sep 15 '16 at 10:40

score 0 · Answer 1 · answered Sep 14 '16 at 18:10

For each of the above lines I call a bubble sort function. But it errors on one or the other depending on whether I put If Arr(i) > Arr(j) Then ... Or If Arr(i, 1) > Arr(j, 1) Then ...

Your values of i, j are incorrect and hence it errors out. I am guessing that you are getting Subscript out of range error.

Here is a simple way to reproduce the error that you are getting.

Sub Sample()
    Dim i As Long, j As Long, k As Integer
    Dim MyAr As Variant

    MyAr = Range("A1:A5").Value

    For i = LBound(MyAr) To UBound(MyAr)
        For j = LBound(MyAr) To UBound(MyAr)
            If MyAr(j, 1) > MyAr(j + 1, 1) Then
                k = MyAr(j, 1)
                MyAr(j, 1) = MyAr(j + 1, 1)
                MyAr(j + 1, 1) = k
            End If
        Next
    Next
End Sub

The correct way is to loop till UBound(MyAr) - 1

Sub Sample()
    Dim i As Long, j As Long, k As Integer
    Dim MyAr As Variant

    MyAr = Range("A1:A5").Value

    For i = LBound(MyAr) To (UBound(MyAr) - 1)
        For j = LBound(MyAr) To (UBound(MyAr) - 1)
            If MyAr(j, 1) > MyAr(j + 1, 1) Then
                k = MyAr(j, 1)
                MyAr(j, 1) = MyAr(j + 1, 1)
                MyAr(j + 1, 1) = k
            End If
        Next
    Next
End Sub

Your edit doesn't make sense.. where are you using `If Arr(i) > Arr(j) Then ...` or `If Arr(i, 1) > Arr(j, 1) Then ...` My answer is based on that — Siddharth Rout, Sep 14 '16 at 19:31
I am using it in a separate module in a subroutine called `BubbleSort`. I am trying to put the contents of a listbox into an array and then pass the array to the `BubbleSort` subroutine. I added the sort routine above. — Brian, Sep 15 '16 at 10:33
Change `lngMin = LBound(Arr)` to `lngMin = LBound(Arr) - 1` and similarly `lngMax = UBound(Arr)-1` — Siddharth Rout, Sep 15 '16 at 17:10

Populate one dimensional array without looping

1 Answers1