0

Don't have much experience with forms/listboxes. The following function aims to remove an array element by passing it to ListBox and using ListBox.RemoveItem method. What i am struggling with is creating/deleting ListBox object programmatically. I do know there are ActiveX and Form ListBox types but recording a code by adding both manually looks identical:

ActiveSheet.ListBoxes.Add(273, 289.5, 72, 71.25).Select

Here is the attempted function:

Function ArraylessElement(arrIn As Variant, ElemNo As Integer) As Variant
    ''''''''''''''''''''''''''''''''
    'removes i element from 1D array by assigning to ListBox
    'returns 1D array w/ Transpose
    ''''''''''''''''''
    Dim lBox As ListBox
    With ActiveSheet
        Set lBox = .ListBoxes.Add(261, 279.75, 72, 71.25)
        lBox.List = arrIn
        lBox.RemoveItem (ElemNo - LBound(arrIn) - 1) '(LBound) of a ComboBox/ ListBox is 0.
        ArraylessElement = Application.Transpose(lBox.List)
        lBox.Delete
    End With
End Function

I'm getting

Unable to set the list property of the ListBox class

error on lBox.List = arrIn line.

PS. I did see a DeleteArrayElement function at http://www.cpearson.com/excel/vbaarrays.htm which i will use if i cannot get mine working.

Community
  • 1
  • 1
gregV
  • 987
  • 9
  • 28
  • https://stackoverflow.com/questions/40586797/delete-an-item-in-an-array – braX Jan 12 '18 at 18:23
  • You would be much better off using a collection if you want to dynamically remove/add elements. Even better, the `Items` property of a collection is an `Array`. So you can get an array back from the collection once you are done with it. – Brandon Barney Jan 12 '18 at 18:30
  • @BrandonBarney. Interesting, thanks. I have not thought about it. I guess the advantage compared to creating a new array is no ReDim for Collection. As I see the advantage of ListBox, however, would be a direct assignment while the collection i'd need to loop through to pass an array. Would not that be slower for a large enough array? – gregV Jan 12 '18 at 19:18
  • @Vrun I work with collections that are hundreds of thousands of items in size and I havent noticed a difference considerable enough where I have had issues. It definitely depends on what you are working with, and how big you array is though. You would be hard pressed to find an instance where the performance cost of a `Collection` outweighed using an `Array` though, even if just because a `Collection` is intuitive and requires far less operations (on the surface) than an `Array`. – Brandon Barney Jan 12 '18 at 19:22
  • @BrandonBarney except `VBA.Collection` has no `Items` property... – Mathieu Guindon Jan 12 '18 at 19:38
  • @Mats Mug For some reason I thought it did. A dictionary would work then (that definitely has items and keys, both arrays), but far less convenient. Or you could easily write a function to take turn it back into an array. Definitely takes some steam out of that approach though. – Brandon Barney Jan 12 '18 at 23:36
  • dictionary keys were unique i thought. and going back to a new array is pretty much what Chip Pearson had on the link i initially enclosed. so going back to my original post, fixing a listbox error, anyone? :) – gregV Jan 12 '18 at 23:56

0 Answers0