Create a Spreadsheet for Metadata in VBA?

**publius190** · 12-11-2013, 03:04 PM

Hello forum goers,

I am trying to create an array of arrays (list of lists...etc.) that can represent the cells on a worksheet. Basically I want to be able to store some metadata for each cell i.e. flags telling you if the cell is editable, if it is empty, etc...

What i'm really trying to do is create a more advanced spreadsheet where each "Cell" is essentially a list.

Is there some way to do something like this?:

Please Login or Register  to view this content.

I also want the columns to contain a list as well so I can have multiple flags in one "cell"

For example the list in one cell could be:
(1,0) is editable? yes. is empty? No.

Let me know if you need more clarification.

Thanks,

Publius

Edit: Ok I thought of a good way to handle the multiple flags in one cell problem.
I can just fill each slot with seperate class objects. So i'll make a Cell Class module with all of the booleans i need.

My question still stands for the list of list needed to store these cells.

**shg** · 12-11-2013, 03:36 PM

There's a macro (a bas file) at https://app.box.com/s/z6qv4zbz8xy17d5ziesn that does something vaguely like you describe.

**Kyle123** · 12-12-2013, 05:26 AM

What's the goal of this?

I ask as on the face of it, it doesn't sound like a particularly good idea, it seems to me like a sure fire way to grind your spreadsheet to a halt. I imagine that it would be incredibly painful to ensure that your model remains in sync with the cells - capturing whenever a user changes something and sync this back to the model will be arduous and long winded. I'd be tempted to write a set of functions that you can pass a range to and it will return whether the cell is editable etc, much less work and much more reliable.

**publius190** · 12-12-2013, 10:38 AM

Yeah the function idea worked great. I just created some new ranges which are essentially these flags, and add/remove cells from them as the user inputs data. Thanks for the idea!