Storing a Dynamic Array for Later Use

[DixiePiper]

I need to know if/how I can take a generated array and store it for later use. The application I'm building is specific to my industry so please be patient as I try to explain what I'm doing and what I would like to do.

I am building a template that will be used as a stand-alone for individual projects. From a userform checklist, the user selects up to 17 categories. The available categories never change but the number of categories applicable to any given project vary. However, the applicable categories do not change within the scope of the project. On another tab is a list of 50-60 items that are assigned to these 17 categories. Once the project categories are chosen, the script sorts/filters that list and exports the results to another tab. What I have works like a charm. The challenge is this: while the categories will never change, it is possible that the categorization of the 50-60 items will. If we revise the categorization, I need a new sorted/filtered list to overwrite the old one. I've got that part figured out. What I want to know, can the array generated when the original categories are selected be stored and accessed if/when a revision is required?

Here is the script that generates my array:

For Each ctrl In Me.Controls[INDENT]Debug.Print TypeName(ctrl)[/INDENT]
    
        If TypeOf ctrl Is msforms.CheckBox Then
            If ctrl.Value = True Then
                i = i + 1
                ReDim Preserve myList(1 To i)
                myList(i) = ctrl.Caption
            End If
        End If
    Next ctrl

The array myList() is then fed to a filter command. I would like to store myList() as an array that I can access without having to go through the "select categories" process. I have no problem with (and actually it would be preferable) the stored array changing if I restarted the original process.

Thanks in advance!

 

[Fazza]

hi,

what about storing the data on a worksheet?
make it very hidden and your code can use it as a storage place

regards

 

[DixiePiper]

fazza,
I think that's going to be the best, most stable option. I've already determined the need for such a "data sheet". Thank you for chiming in, I appreciate your time.

 

[Fazza]

you're welcome. cheers

 

[DixiePiper]

I apologize for the long delay in response. Due to some miscommunication and other errors, my project was deleted and I've been rebuilding it from the ground up. I've played with storing the array on a worksheet but I cannot quite figure out how to make it work. Here's what I need, I'm hopeful someone can help me.

1. My code will generate two dynamic arrays "onList()" and "offList()". The arrays will vary in size from 1 to 17 items.
2. I need to store the arrays on an sheet within my project workbook.
3. I need to be able to recall the array, preferable with a named range or some other method so I don't have to rewrite the formula/edit the code each time.

I've searched for this but nothing seems to hit all the points and I'm just novice enough that I cannot seem to adapt the suggestions for my purpose.

Thanks

 

[DixiePiper]

After trial and error and querying the Great Google, I have a solution that works just fine for what I need. Again, thanks all for your help.