How to populate a Control with an Array 1

[EdwardMartinIII]

I'm collecting strings. Each time my User clicks a button, I want to add a new string to an array of strings. However, each time the onclick event is processed, the array variable is flushed, which isn't really a surprise.

What I was hoping I could do is take an invisible listbox and make it a storage bin for the array. Onclick, and the empty array populates itself with the contents of the control. Then adds a new element. Then dumps its guts back into the control, then return to form.

Eventually, User pushes a different button that processes all the "collected" strings, then empties out the control.

Does that seem like a good way of doing it? It seems better than attempting to declare a global variable...

Cheers,

Edward "Do not read this sentence."

 

[RobBroekhuis]

You shouldn't need to go through the trouble of the listbox. You can keep your array current by using one of the following approaches:

- declare your array (and its counter) as public variables in a regular module. They will remain for as long as the workbook is open, and be available to all macros, including button_click event handlers

- declare your array (and its counter) as static variables inside your event macro. This will work if you don't need to refer to the array outside of the scope of the click event.

Rob

 

[vb5prgrmr]

If it is a dynamic array you could do what RobBroekhuis states but you will have to use ...
[tt]
Redim Preserve MyArray(MyCounter + 1)
[/tt]
But there would be no reason to use the array if you are using a list box. Every Time they click on add string you use the additem method. Then when you need to do your process you can just use the control. On the other hand you could use a collection object to do everything also.

Good Luck

 

[xlbo]

Or you could just bung the seperate strings into a worksheet column and assign the range directly to an array for the processing of the strings Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[RobBroekhuis]

The best approach clearly depends on how large this array might be. If it's huge, then use one of the approaches that limits the storage to just the minimum required (listbox, redim, worksheet column). If it's fairly limited, then it's easier to just declare an array large enough to hold the maximum number of strings and be done with it.
Rob

 

[EdwardMartinIII]

Ah, I'm happy to provide more data.

The strings are file path/name combinations (from all over the User's computer, not just one directory). The User chooses various files to associate with the record they're making.

Then, when they click "Submit the information", the code goes through each element of the array and builds a filecopy command, and performs an "eval" on each of the commands. I'm limiting them to 99 files, but if any of them put more than three or four, I'll simply march over to their cubical and smack 'em around.

So, a fairly small array of well behaved strings. And the array has to persist after the "Add a file..." click event because they keep adding items.

I don't need to worry about maintaining a counter because I just search the array for the next open slot each time.

So, declaring the array as a public variable might work. Can I declare it in any module, or perhaps should I declare it in the

onload

event of the form?

Thanks,

Edward "Do not read this sentence."

 

[EdwardMartinIII]

Um, er, it seems arrays are not allowed to be public. "Do not read this sentence."

 

[EdwardMartinIII]

Hm, listbox doesn't seem to have an AddItem method.

I tried such things as

  SourceFileArrayStorage.ItemData(0) = "Fish"
  SourceFileArrayStorage.ItemData(1) = "Dog"

but that declares it "requires an object" "Do not read this sentence."

 

[RobBroekhuis]

Arrays are perfectly allowed to be public. Make sure your declaration sits above all sub code in a regular module, e.g.

Public MyFiles(99) As String, MyFileCtr As Integer

Sub onclick()
MyFileCtr = MyFileCtr + 1
MyFiles(MyFileCtr) = "hello"
End Sub
Rob

 

[EdwardMartinIII]

Sorry about the confusion, Rob. I didn't mean for it to sound as if you gave me bogus advice.

I eventually settled on using a listbox called SourceFileArrayStorage (so I could babysit it):

SourceFileArrayStorage.RowSource = SourceFileArrayStorage.RowSource + SourceFileName + ";"

Cheers,

Edward "Do not read this sentence."

 

[RobBroekhuis]

Edward,
Your code doesn't make sense to me. Why are you adding your filename to the rowsource property of your listbox? I guess it might work (because it is a string, after all), but then why not just use a publically declared string variable? The array approach still seems to me to be by far the most straightforward one, but of course your mileage may vary
Rob

 

[EdwardMartinIII]

It probably is, Rob.

I used a control 'cause I'm new to this and the control allowed me to watch all the strings change as the program progresses. I also knew how to populate a control.

I think wrapping my brain around a control will be helpful later, because another group of Users is going to have to look over this same list of files and choose from them.

And, I've been having a lot of difficulty connecting to this site, and that was the last one I could see for a while... 8)

Cheers,

Edward "Do not read this sentence."