Carrying an array in a user form??

[roscoe]

I have a user form that reads in data from a worksheet and populates a combo box (after converting time values into strings). I have a few global variables (within the context of the user form) that I "dim" at the top of the user form module that allows them to stay active through the various routines in the user form. So far so good

Problem is now I need to do more than load the data into the combobox, so I thought I'd load them into an array at the same time. Works fine when I load it. But when I select a button on the userform (in this case to add more data to the array I just created), the array has been blitzed and I get an error.

What I tried to do that has always worked in the past was DIM "array" as VARIANT, then redim ARRAY(as applicable) when I first fill it. I then load the user form. Next step is to add data to the array by clicking on a button. The plan was to "redim preserve" to make it bigger and then fill the new line, but I never got there...it was empty as soon as I clicked on the button and executed that sub.

How do I create an array in the user form and keep it alive until I unload the user form? Is there a trick to the DIM statement or how to set the dimensions or ... to make this work?

Thanks in advance

 

[djreiswig]

Not to sidetrack the discussion, but what are you doing with the data that you need to load it into a combo box and an array? You said you want to add another item and the sort the array. Can't you make your add sub loop through the combo box and move each item down one position and then add the new item in the correct space? The combo box could in essence act as your array, saving you the trouble of loading the data twice. This doesn't answer your question of the array being lost, but it may solve your problem.

 

[ChrisM]

Yes, a form level or module level array will hold it's value between subs

 

[roscoe]

Can't really do that with the combo box. The entries are string representations of a pair of dates that start with the day of the week. Each entry is built from a pair of dates on the spreadsheet and looks like "Wed, 22 Jun 2011 2100 --> Thu, 23 Jun 2011 0500". If I add a new entry it currently goes to the end. For a new requirement I decided I needed them sorted before I write the new line back to the spreadsheet.

I thought the easiest way would be to keep a separate array with real date codes rather than these convoluted strings...and it wouldve been if I could just figure out how to maintain array contents between subroutines.

 

[djreiswig]

I'd think if you can convolute them into a string then you should be able to change them back into the date code before you write them back.

 

[Norie]

Why not specify where the item you are adding should go in the list as I suggested?

The 2nd argument if the AddItem method allows yiu to do that.

eg Combobox1.AddItem "New item", 0 adds the item at the top if the list

Also, you've now mentioned that there is more than one sub called/run when you click the button.

Does that mean the problem is with the array being passed/used in these subs?

PS How can you post a scanned image of the code but not the actual code itself?

 

[roscoe]

Why not specify where the item you are adding should go in the list as I suggested? The 2nd argument if the AddItem method allows yiu to do that. eg Combobox1.AddItem "New item", 0 adds the item at the top if the list

Because with the way the strings were constructed, I could think of no easy way to figure out where the new line should go.

Also, you've now mentioned that there is more than one sub called/run when you click the button. Does that mean the problem is with the array being passed/used in these subs?

If you read that into something I typed that that was a mistake. I have a user form with with 6 buttons. I needed the array to stay populated so long as the userform is loaded and independent of which button I push (One button deletes a line from the combo box, another adds a line, etc...)

PS How can you post a scanned image of the code but not the actual code itself?

Because I can print the code to paper and carry it across the room to a scanner connected to the web. I can't digitally transfer it; not allowed to "cross the streams" as it were.

Bottom line: I went another way entirely that works. Not as efficient as I would have liked, but it works and I'm done. I appreciate everyone's help.