Listbox items from an array

[ukdane]

Is it possible to take the items found in an array, and list them directly in a listbox, without placing them into an excel worksheet (The reason for not putting them in a worksheet, is that whilst I'm using VBA, it's not in Excel itself- but a similar program)?

Any Excel VBA clues very welcome.

Thanks for your help.

[JONvdHeyden]

Absolutely, use the List property, e.g:

[VBA]
Sub AddItems()
Dim arr()
arr = Array("Jon", "Mike", "Bob")
ListBox1.List = arr
End Sub
[/VBA]

[ukdane]

Hi JON,
Thanks for your reply, it ALMOST works.
The problem is that it only shows the latest added item, and the previous items are "invisible" or "removed" (it should be noted that the item does appear on the correct row; so that array item 3 is shown on line 3. But items 1 and 2 are not shown).

Any ideas what I have done wrong?

The array is dynamic.

[JONvdHeyden]

What app are you doing this in? I can only comment on how Excel will treat this and my testing shows that the listbox will display all the items in the array. Items 0 thru 2 (arrays start at 0, not 1) are all displayed...

Could you share your code?

[ukdane]

It's a reflection program, but the code should work in the same way.
It shows and lists the last added item only, not ALL items.

Here's the code:
[VBA]
Dim ICOUNT As Integer
Dim MYARRAY() As String

Private Sub addbutton_Click()
Dim ADDNEW As String
ADDNEW = Item.Value
ICOUNT = ICOUNT + 1
ReDim MYARRAY(1 To ICOUNT)
MYARRAY(ICOUNT) = ADDNEW
updateform
Item.Value = ""
End Sub

Private Sub UserForm_Activate()
ICOUNT = 0
End Sub

Sub updateform()
mylist.List() = MYARRAY()
End Sub[/VBA]

[GTO]

ACK! Sorry, a bit premature.

If you are attempting to add items upon a click, why worry about adding as an array?

Mark

[ukdane]

Because I will need to action all of the items in the list as an array in some later code.

If there is a more efficient solution to this (possibly defning the array once the list has been created) then please let me know.

Thank you

[ukdane]

I've done some checking, (by replacing the mylist.list() = myarray() with msgbox myarray() ) and it is showing me that it can't recall the previous array details.

So for example, if I start by enetering 1.
it shows 1
if I then input 2
array (1) is now empty
array (2) = 2

or have I missed a trick here with the arrays starting a 0?

[ukdane]

It appears to be the REDIM that is causing the problem.

[mdmackillop]

[VBA]ReDim Preserve MYARRAY(1 To ICOUNT)[/VBA]

[JONvdHeyden]

From VBA help file (I think it explains it nicely):

In a procedure within the array's scope, use the ReDim statement to change the number of dimensions, to define the number of elements, and to define the upper and lower bounds for each dimension. You can use the ReDim statement to change the dynamic array as often as necessary. However, each time you do this, the existing values in the array are lost. Use ReDim Preserve to expand an array while preserving existing values in the array. For example, the following statement enlarges the array varArray by 10 elements without losing the current values of the original elements.

ReDim Preserve varArray(UBound(varArray) + 10)

Note When you use the Preserve keyword with a dynamic array, you can change only the upper bound of the last dimension, but you can't change the number of dimensions.