Access VBA loop through listbox select items and add to array

Question

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?

Try Dim-ing the array as Variant. Arrays defined as String need to have a specified size (i.e. Dim folderarray(5) As String), whereas Variant allows for a non-specified size. — Johnny Bones
– Johnny Bones, Commented Feb 17, 2016 at 16:20
place "ReDim" statement before assigning values to "folderarray()", otherwise it is unable to be assigned any value. then, when assigning "folderarray()" one single value you must use an index (folderarray(i)=...). if you want to assign an entire list to an array, then you must declare the array as of "Variant" type — user3598756
– user3598756, Commented Feb 17, 2016 at 16:35
I get Run-time error '13: Type mismatch even with the variable set to either, Variant or String — Paul Machin
– Paul Machin, Commented Feb 17, 2016 at 16:44
The line that triggers the error is this: folderarray(i) = Join(list, ",") — Paul Machin
– Paul Machin, Commented Feb 17, 2016 at 16:54

HansUp · Accepted Answer · 2016-02-17 17:08:09Z

2

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.

edited Feb 17, 2016 at 17:08

answered Feb 17, 2016 at 17:02

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anthony Griggs Over a year ago

+1 for simplicity and thinking outside the box. If I was the questioner I would definitely award this one!

Paul Machin Over a year ago

Thanks for that! it worked :) , and my plan was to use it in a SQL statement to export more detailed results based on the data that is added to the listbox. As there can be many variations that can be added to the listbox.

Cisco · Accepted Answer · 2016-02-17 16:40:04Z

0

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

answered Feb 17, 2016 at 16:40

Cisco

2511 silver badge7 bronze badges

Comments

Anthony Griggs · Accepted Answer · 2016-02-17 16:45:38Z

0

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.

answered Feb 17, 2016 at 16:45

Anthony Griggs

1,6572 gold badges19 silver badges42 bronze badges

Collectives™ on Stack Overflow

Access VBA loop through listbox select items and add to array

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related