Is there a way to use an array in VBA to permanently store data, so that the
data is intact the next time a workbook is opened? Specifically, here is
what I am trying to do:
I need to load production data in half-hour intervals over the course of a
week. So there are 336 bins per week, and a multitude of data to store into
the third dimension of the array for each bin. Then I need a complete record
of each week's production. Rather than have a massive workbook with 52
worksheets, 1 for each week, I would like to store all the data in a
permanent VBA array. When I want to show a particular week's data, I'll set
up some kind of pick list to extract the appropriate row/column data from the
array and load it into a designated range on a display worksheet. That way,
I only need a single worksheet instead of 52.
Basically, the array becomes a hidden database. Is there a way to do
such a thing?

There is no advantage to using an array over worksheet cells.
--
Gary''s Student - gsnu200836


"Mark Schreiber" wrote:

Is there a way to use an array in VBA to permanently store data, so that the
data is intact the next time a workbook is opened? Specifically, here is
what I am trying to do:
I need to load production data in half-hour intervals over the course of a
week. So there are 336 bins per week, and a multitude of data to store into
the third dimension of the array for each bin. Then I need a complete record
of each week's production. Rather than have a massive workbook with 52
worksheets, 1 for each week, I would like to store all the data in a
permanent VBA array. When I want to show a particular week's data, I'll set
up some kind of pick list to extract the appropriate row/column data from the
array and load it into a designated range on a display worksheet. That way,
I only need a single worksheet instead of 52.
Basically, the array becomes a hidden database. Is there a way to do
such a thing?

As far as I know, it's not possible. According to me, the best way would be
to use a real database that you could interact with Excel.

PA

"Mark Schreiber" wrote:

Is there a way to use an array in VBA to permanently store data, so that the
data is intact the next time a workbook is opened? Specifically, here is
what I am trying to do:
I need to load production data in half-hour intervals over the course of a
week. So there are 336 bins per week, and a multitude of data to store into
the third dimension of the array for each bin. Then I need a complete record
of each week's production. Rather than have a massive workbook with 52
worksheets, 1 for each week, I would like to store all the data in a
permanent VBA array. When I want to show a particular week's data, I'll set
up some kind of pick list to extract the appropriate row/column data from the
array and load it into a designated range on a display worksheet. That way,
I only need a single worksheet instead of 52.
Basically, the array becomes a hidden database. Is there a way to do
such a thing?

Arrays are loaded from data stored in worksheet cells or by the AddItem method.

If you use a range on a sheet then the array is redundant to the range.

If you use the AddItem method, then the array is emptied each time the
workbook is closed and has to be reloaded when the workbook is reopened.

So from the point of view of using the array as a means of storage, I don't
believe it would serve a useful purpose. Arrays are useful for organizing
disorganized data so it can be accessed and utilized efficiently.

"Mark Schreiber" wrote:

Is there a way to use an array in VBA to permanently store data, so that the
data is intact the next time a workbook is opened? Specifically, here is
what I am trying to do:
I need to load production data in half-hour intervals over the course of a
week. So there are 336 bins per week, and a multitude of data to store into
the third dimension of the array for each bin. Then I need a complete record
of each week's production. Rather than have a massive workbook with 52
worksheets, 1 for each week, I would like to store all the data in a
permanent VBA array. When I want to show a particular week's data, I'll set
up some kind of pick list to extract the appropriate row/column data from the
array and load it into a designated range on a display worksheet. That way,
I only need a single worksheet instead of 52.
Basically, the array becomes a hidden database. Is there a way to do
such a thing?

Arrays are basically variables, so they will be gone once the workbook is
closed.
I would use a database and I very much recommend to go with SQLite.
Very fast, simple and reliable.
Second choice (not far behind) would be to store in text files. Possibly
slower, but simpler.

RBS


"Mark Schreiber" wrote in message
...
Is there a way to use an array in VBA to permanently store data, so that
the
data is intact the next time a workbook is opened? Specifically, here is
what I am trying to do:
I need to load production data in half-hour intervals over the course of a
week. So there are 336 bins per week, and a multitude of data to store
into
the third dimension of the array for each bin. Then I need a complete
record
of each week's production. Rather than have a massive workbook with 52
worksheets, 1 for each week, I would like to store all the data in a
permanent VBA array. When I want to show a particular week's data, I'll
set
up some kind of pick list to extract the appropriate row/column data from
the
array and load it into a designated range on a display worksheet. That
way,
I only need a single worksheet instead of 52.
Basically, the array becomes a hidden database. Is there a way to do
such a thing?