cell properties sniffer?

[jayd4wg]

has anyone ever written a cell properties sniffer?

I'm trying to do a simple text and background color inverter, and when i go to white background with black text, i lose the fine gray outline. But later on, i'll be doing much more with it and it would be very helpful to know what the VBA properties are for certain cells.

 

[chead5]

Mabye if I clarify a little better..

What I'm wondering is if there is a way to loop through all of the possible properties of the Range object (or any other object for that matter).

I was hoping that there was a container object that held all of the available properties that we could just loop through, kinda' like an array or a range of multiple cells

Ex.
For Each cell in activesheet.range("a1:d12")
cell.value=1
next cell

Does anyone know if such an object exisits? Failing that, I guess we would have to manually code a line for each and every property.

I'm going to send a link to this tread over to **** at Daily Dose of Excel (http://www.dicks-blog.com/)and see if he knows of anything...

 

[erik.van.geit]

Yes, chead5,
it was clear to me

Sub list_cell_properties()
For Each P in ActiveCell.Properties
msg = msg & P & vTab & P.Value & Chr(10)
Next P
Range("A1") = msg
'MsgBox msg
End Sub

this it not working, but shows the intention

kind regards,
Erik

 

[chead5]

**** found a stop-gap solution!!

check this out. It kina' does the job but it is not ideal..

http://www.dicks-blog.com/archives/2005/06/08/property-sniffer/

I refuse to believe that with all the collection objects and in the excel model that there is no way to list all the properties of a range/shape/ ect...

But then again, there is lots of other stuff it's missing too...

 

[chead5]

Unfortunatly I have just been assigned a dealine project that will have to take priority for now. I will get back to this in a couple of days....

 

[Ahnold]

Probably not really useful, but a neat trick. Go to Insert | Names | Define. Make the name Info (can be any valid name). In the refers to box enter.

=GET.CELL(ROW(),Sheet1!$A$1)

In B1 enter.

=Info

And drag down. This is what you get. I added the text from the help file.

Book1
	A	B	C	D
1		$A$1	Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.
2		1	Row number of the top cell in reference.
3		1	Column number of the leftmost cell in reference.
4		1	Same as TYPE(reference).
5		0	Contents of reference.
6			Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.
7		General	Number format of the cell, as text (for example, "m/d/yy" or "General").
8		1	Number indicating the cell's horizontal alignment:	1 = General
9		0	Number indicating the left-border style assigned to the cell:	0 = No border
10		0	Number indicating the right-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
11		0	Number indicating the top-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
12		2	Number indicating the bottom-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
13		1	Number from 0 to 18, indicating the pattern of the selected cell as displayed in the Patterns tab of the Format Cells dialog box, which appears when you choose the Cells command from the Format menu. If no pattern is selected, returns 0.
14		TRUE	If the cell is locked, returns TRUE; otherwise, returns FALSE.
15		FALSE	If the cell's formula is hidden, returns TRUE; otherwise, returns FALSE.
16		8.43	A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell's width is set to change as the standard width changes (TRUE) or is a custom width (FALSE).
17		13.5	Row height of cell, in points.
18		Arial	Name of font, as text.
19		10	Size of font, in points.
20		FALSE	If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.
21		FALSE	If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.
22		FALSE	If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.
23		FALSE	If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.
24		0	Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.
25		FALSE	If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE. Outline font format is not supported by Microsoft Excel for Windows.
26		FALSE	If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE. Shadow font format is not supported by Microsoft Excel for Windows.
Sheet1

 

[chead5]

Ok, I was able ot duplicate what you did.

How the heck does it work???

Is the info from the help file fror the Info worksheet function?

 

[erik.van.geit]

Hi, Ahnold,

beautiful, but I can't get this to work, unable to translate to Dutch
GET.CELL ...

can you translate it?
or can you email your sheet ?

kind regards,
Erik

 

[Ahnold]

chead5,

You can have names refer to formulas as well as ranges. If you put in =5*5 and name it TwentyFive. You can put in any cell =TwentyFive and 25 will be displayed. GET.CELL is available I believe for backward compatibilty to Macro 4.0. I had to download the help file from http://www.microsoft.com/downloads/results.aspx?freetext=xlmacr8.hlp&productID=&DisplayLang=en

Erik,

I emailed you the file. Hope it works for you.

EDIT: Math error

 

[erik.van.geit]

Ahnold,

Thank you for sending the workbook with a kind note attached
This is working within the "defined-name-formula", but I can't put this GET.CELL in a cell to get results. (popup: "this function is unvalid")Can you ?
Where does the text of column C come from ?
not here ? http://www.microsoft.com/downloads/results.aspx?freetext=xlmacr8.hlp&productID=&DisplayLang=en = the link you posted

kind regards,
Erik

PS
to avoid any confusion for future "readers"
you wrote
=5*10 .... =TwentyFive and 25 will be displayed

make it
"=5*5"

 

[Ahnold]

This is working within the "defined-name-formula", but I can't put this GET.CELL in a cell to get results. (popup: "this function is unvalid")Can you ?

No, it won't work that way. I believe it will only work that way in Excel 4.0.

Where does the text of column C come from ?
not here ? http://www.microsoft.com/downloads/results.aspx?freetext=xlmacr8.hlp&productID=&DisplayLang=en = the link you posted

Might be a bad link. Try going to http://www.microsoft.com/downloads and search for xlmacr8.hlp.

PS
to avoid any confusion for future "readers"
you wrote
=5*10 .... =TwentyFive and 25 will be displayed

make it
"=5*5"

Fixed