ajacoa
asked on
Excel VBA - How can I create/store/view custom cell properties?
I can view/store many cell properties, such as cell.value, cell.formula, cell.comment, etc. But, how can I view/store custom properties/metadata about each cell? For example I want to be able to store cell.defaultformula or cell.value5 or cell.joescoment?
Patrick Matthews
ajacoa,Can you elaborate on what you want to do? I am thinking this can be done with custom classes.Question: will this data need to persist beyond the current Excel session?Patrick
ASKER
Yes, I want the custom metadata to be saved as part of the file (preferable like cell values, comments, etc.).
For example, I would like to save a previously entered (or default) value so it can be restored.
I'd also like to have a hidden custom property that indicates what type of cell it is. For example, if it is a user input cell.
For example, I would like to save a previously entered (or default) value so it can be restored.
I'd also like to have a hidden custom property that indicates what type of cell it is. For example, if it is a user input cell.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ajacoa
Excel doesn't really do this sort of thing, eg meta data etc.
If you did want to simulate something like that you'd need to do something like Patrick suggests.
Or use some sort of 3rd party application.
Excel doesn't really do this sort of thing, eg meta data etc.
If you did want to simulate something like that you'd need to do something like Patrick suggests.
Or use some sort of 3rd party application.
ASKER
I found a range property "ID" that I think I can use to store the data. Its unlikely I'll use ID otherwise.
Definition of ID: "Returns or sets a String value that represents the identifying label for the specified cell when the page is saved as a Web page."
I like the "hidden/shadow worksheet" pair idea, but in my case I think I'd prefer storing in ID so it stays with the cell when I move things around.
I do see info about parsing strings and XML by searching "excel vba parse string xml".
Definition of ID: "Returns or sets a String value that represents the identifying label for the specified cell when the page is saved as a Web page."
I like the "hidden/shadow worksheet" pair idea, but in my case I think I'd prefer storing in ID so it stays with the cell when I move things around.
I do see info about parsing strings and XML by searching "excel vba parse string xml".
Interesting property, doesn't seem to do much even when saving as web page.
I thought it would at least add an id for the element but it just seems to create a hyperlink.
Actually there might be a way to sort of do what you want, or at least give you
There is a PHP module called excel_reader that takes an Excel worksheet and creates summary of the formats, fontd etc
I think it does that at worksheet level rather than cell by cell, but perhaps it could be adapted.
The HTML produced is pretty straightforward, in comparison to what you get when you convert a worksheet from Excel.
So perhaps there's some possibility to take that and convert it to XML or whatever.
I thought it would at least add an id for the element but it just seems to create a hyperlink.
Actually there might be a way to sort of do what you want, or at least give you
There is a PHP module called excel_reader that takes an Excel worksheet and creates summary of the formats, fontd etc
I think it does that at worksheet level rather than cell by cell, but perhaps it could be adapted.
The HTML produced is pretty straightforward, in comparison to what you get when you convert a worksheet from Excel.
So perhaps there's some possibility to take that and convert it to XML or whatever.