VBA add array to a list

Question

Sub RangeBulkAmend()

   Set list = list.CreateInstance
     Dim c As Range
      Dim i As Long
      Dim myarr() As Variant

      For Each c In Selection

           list.Add c.value     
       Next c

            ReDim myarr(list.Count - 1)

         For i = 1 To list.Count - 1
             myarr(i) = list.Items(i)
             msg = msg & vbCrLf & myarr(i)

          Next i



          {{ListWindow.ListBox1.list = myarr}}

             Load ListWindow

             ListWindow.Show
end sub

i have an error on the compile as I try to pass my array to a list the code with double braces that where the compiler points too but if I highlight i get the message Object variable or With block variable not set any help will be gladly appreciated thank you in advance please note the list refereed to in the code above is my own custom list the issue is sending the array to the list box at the double braces checked the code it produces something now to extract that to a list box

You could skip one loop by loading the array first myarr=selection.Value then looping through it to load your list, using for i = LBound(myarr) to Ubound(myarr). this is not the answer but a suggestion. — Scott Craner
– Scott Craner, Commented Apr 11, 2016 at 14:53
stab in the dark here...set the list after you call Load ListWindow. More than likely the control does not yet exist. — Jeremy
– Jeremy, Commented Apr 11, 2016 at 14:56
It seems that you are using 1 as the UBound of your array (which is not VBA default). So you might want to add Option Base 1 to your VBA module (above / before all subs). — Ralph
– Ralph, Commented Apr 11, 2016 at 15:01
also...what data type is the variable list? where is it declared? — Jeremy
– Jeremy, Commented Apr 11, 2016 at 15:29
thanks for your suggestions none of them work I get error 380 when i try these suggestions if any one knows how I can set my array then maybe that will help — Tim
– Tim, Commented Apr 11, 2016 at 15:37

Scott Craner · Accepted Answer · 2016-04-11 17:22:50Z

If it is only your intention to load a list box with the selected cells values then:

Sub RangeBulkAmend()

    Dim myarr() As Variant

    myarr = Selection.Value

    Load ListWindow
    ListWindow.ListBox1.List = myarr
    ListWindow.Show
End Sub

Will do it

Or for that matter simply skipping the whole and just assigning the selection.Value to the listbox also works:

Sub RangeBulkAmend()

    Load ListWindow
    ListWindow.ListBox1.List = Selection.Value
    ListWindow.Show
End Sub

To mass add to an existing list in a list box try this:

Sub RangeBulkAmend()
    Load ListWindow
    Dim myarr() As Variant
    Dim oldarr() As Variant
    Dim t&, i&

    myarr = Selection.Value

    t = ListWindow.ListBox1.ListCount

    ReDim oldarr(0 To (ListWindow.ListBox1.ListCount + UBound(myarr, 1) - 1)) As Variant
    For i = 0 To UBound(oldarr)
        If i < ListWindow.ListBox1.ListCount Then
            oldarr(i) = ListWindow.ListBox1.List(i)
        Else
            oldarr(i) = myarr(i - t + 1, 1)
        End If
    Next i

    ListWindow.ListBox1.List = oldarr
    ListWindow.Show modal
End Sub

Collectives™ on Stack Overflow

VBA add array to a list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related