Solved

Excel VBA - How can I create/store/view custom cell properties?

Posted on 2010-09-22
6
850 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:ajacoa
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33737134
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
0
 

Author Comment

by:ajacoa
ID: 33737328
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.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33737950
ajacoa,To get these metadata to persist, I think you would need to do something like this:1) Have a hidden worksheet to pair up to your "real" worksheet2) Each cell in the hidden sheet would hold XML data corresponding to your custom properties3) At various events such as Sheet_Change and/or Sheet_Calculate, or some other specified juncture, update/retrieve those custom propertiesI'm not really an XML guy, so I am not sure how much further I can push this for you...Patrick
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Expert Comment

by:Norie
ID: 33744142
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.
0
 

Author Comment

by:ajacoa
ID: 33753744
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".
0
 
LVL 33

Expert Comment

by:Norie
ID: 33757735
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now