CODEWITHSUNDEEP
arraysms-accessvbalistboxitems
Paul Machin
Paul Machin

Reputation: 39

Access VBA loop through listbox select items and add to array

I'm trying to loop through a listbox and add the contents to an array....

My code is this: 

Private Sub exportfolders_Click()

Dim list As String
Dim folderlist As String
Dim folderarray() As String
'Dim i As Interger

For i = 0 To Me.selectedfolders.ListCount - 1
    'folderlist = (Me.selectedfolders.Column(0, i))
    'folderarray() = Join(Me.selectedfolders.Column(0, i), ",")
    list = (Me.selectedfolders.Column(0, i))
    folderarray() = Join(list, ",")
    ReDim Preserve folderarray(i)
Next i
   folderlist = folderarray
    'folderarray() = Join(folderlist, ",")
    MsgBox (folderlist)

End Sub

You can see the bits I have commented out, trying all sorts to get it to work. But I keep getting the message "Can't assign to array" at folderarray(i) = Join(list, ","). Any pointers as to where I am failing?

Upvotes: 2

Views: 3929

Answers (3)

HansUp
HansUp

Reputation: 97101

You can concatenate the list box items into a string, and then use Split() to load your array. That way, the array is sized automagically without you needing to ReDim.

I tested this code in Access 2010:

Dim folderarray() As String
Dim i As Long
Dim strList As String

For i = 0 To Me!selectedfolders.ListCount - 1
    strList = strList & "," & Me!selectedfolders.Column(0, i)
Next
' use Mid() to exclude the first comma ...
folderarray = Split(Mid(strList, 2), ",")

Note I don't know what you want to do with the array after loading it. MsgBox folderarray would throw Type mismatch error. MsgBox Mid(strList, 2) would be valid, but if that's what you want, you wouldn't need the array.

Upvotes: 2

Anthony Griggs
Anthony Griggs

Reputation: 1641

You could try something like this:

Private Sub ListToArray()
    Dim folderArray() As Variant
    Dim currentValue As String
    Dim currentIndex As Integer
    Dim topIndex As Integer

    topIndex = Me.selectedfolders.ListCount - 1
    ReDim folderArray(0 To topIndex, 0 To 1)

    For i = 0 To topIndex
        currentValue = Me.selectedfolders.Column(0, i)
        folderArray(i, 0) = i
        folderArray(i, 1) = currentValue
    Next i
End Sub

Note my example is a multi-dimensional array which will give you the ability to add more than one item should you chose to do so. In this example I added the value of "i" as a placeholder/ index.

Upvotes: 0

Cisco
Cisco

Reputation: 251

1) declare the array. Take a look at https://msdn.microsoft.com/en-us/library/wak0wfyt.aspx

2) No need of support variable

3) Assign the values to your array with the correct syntax

Private Sub exportfolders_Click()

Dim folderarray() As String
Dim i As Interger

Redim folderarray (Me.selectedfolders.ListCount-1)

For i = 0 To Me.selectedfolders.ListCount - 1
    folderarray(i) = Me.selectedfolders.Column(0, i)
Next i

' Write here what you want to do with your array

End Sub

Upvotes: 0

Related Questions