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

 

[Norie]

Where did you declare the array that get's 'blitzed'?

What exactly are you doing when that happens?

 

[roscoe]

I'm declaring the array variable at the top of the userform module, outside of any of the subroutines. Other variables are working fine, but the array variable is not.

During initialization of the userform the array is filled from a range on my spreadsheet. I then hope to have it sit there populated until I select a button which will add more data, sort it, and then rewrite it back to the spreadsheet. At the end of the initialization routine the array is properly filled, but as soon as I select the button, the array is emptied of all data. I have other variables that seem to work OK, so I don't get what's not working.

 

[Norie]

Why do you need the array when you are already storing the data in the combobox and worksheet?

Is there something I'm missing?

Also, what code are you using for the button that's meant to put the data from the array ok the worksheet?

 

[djreiswig]

Maybe Redim Preserve?

 

[ChrisM]

redim preserve only works on the last dimension of an array, you're not trying to resize the first dimension are you?

 

[Norie]

I don't think ReDim is the problem, the OP seems to be saying the array is fine until they try and transfer it to the worksheet.

Mind you we haven't seen any code yet, so perhaps ReDim is being used.

 

[roscoe]

Why do you need the array when you are already storing the data in the combobox and worksheet?

Is there something I'm missing?

Also, what code are you using for the button that's meant to put the data from the array ok the worksheet?

The data on the spreadsheet is in Excel serial date format (actually pairs)...I convert two of the dates into a single string ("start --> stop") and then populate the combo box with these strings. The date pairs are created in random order but for a future application I realized that I need to sort them chronologically. So I thought that as I read them into the combo box, I could also read them into an array. Once the combo box is filled in the user form I have the option of editing the values or adding another pair. Currently adding a pair of start/stop times just goes to the bottom of the existing list which is what is causing the random nature of this data. I was hoping that I could add the next pair instead to the array, sort the array, and then write that sorted array back to the worksheet.

Currently, after the initialize routine loads the array and finishes, that array is still populated in the watch window. I then input the new values that I wish to add, and then hit the "add" command button...as soon as I do that, the array I just filled goes empty before any line in that subroutine runs.

redim preserve only works on the last dimension of an array, you're not trying to resize the first dimension are you?

I learned that lesson a long time ago. Never lets me get that far...

I don't think ReDim is the problem, the OP seems to be saying the array is fine until they try and transfer it to the worksheet.

Mind you we haven't seen any code yet, so perhaps ReDim is being used.

Unfortunately, sharing code is a problem as the computer I am working on is not on the grid. I may be able to print the code and scan it in and post the image, but that's more hassle that I want to deal with right now.

I tried Redim, ReDim preserve...I tried declaring it with dimensions, without, as dates, as variants...nothing has worked. In the initialization routine I fill two variables from the worksheet that I declared outside the routine and they work fine.

Color me confused...I suppose I could read the data in again but that seems very inefficient.

 

[Norie]

Roscoe

If you add to the list It will be added ay the bottom if you don't specify where it's supposed to be added.

Also, if the dates on the worksheet are formatted as dates you can get them formatted that way on the code.

All you would need to do for that would be to use the Text property instead of Value.

By the way is this definitely a combobox?

What you are doing sounds more suited to a listbox.

Well, I think it might be but hard to tell without knowing more details.

PS Is there no way you can transfer data, even a text file, from the computer the file is on?

 

[roscoe]

If you add to the list It will be added ay the bottom if you don't specify where it's supposed to be added.

Also, if the dates on the worksheet are formatted as dates you can get them formatted that way on the code.

All you would need to do for that would be to use the Text property instead of Value.

By the way is this definitely a combobox?

What you are doing sounds more suited to a listbox.

All that is well and good and your time to respond is appreciated, but none addresses my root issue...is there a way to create an array and have it hold it's values across different subroutines in a user form (each sub is executed by a button push)

PS Is there no way you can transfer data, even a text file, from the computer the file is on?

Nope...scan and post as an image would be the only way, but I haven't been to work this week so I haven't had the opportunity