Cell Properties or XML data

  • Thread starter Thread starter AnandaSim
  • Start date Start date
A

AnandaSim

I'm thinking of iterating through cell in a range and dumping/
documenting the value, formula and other attributes into a database so
that I can sort, filter, analyse cells. Tried range("a1").properties
(i) but properties does not seem to be supported in the object model?

Next, I would like to document properties which are different from
default - is there some approach to define what is default and then
use that as comparison?

I thought of looking at the .xlsx XML file but there's Excel specific
XML to decipher and to use an XML engine rather than the VBA / Excel
Object Model.

Any tips? Or any established tools that already do this?

Thanks.

Ananda
 
A cell is part of a Range and the Range Oject can have many Properites:

Sub CellProperties()
Dim r As Range
Set r = Range("A1")
With r
MsgBox (.Formula)
MsgBox (.Value)
MsgBox (.Value)
MsgBox (.Interior.ColorIndex)
End With
End Sub
 
I've noticed that you have a habit of surrounding your MsgBox with
parentheses when you use them as a statement (that is, as a subroutine type
call, not as a function call). This is not a good idea and it will generate
an error if you try to include additional arguments; for example, if you try
to include a Button argument along with your Prompt argument.

This works...

MsgBox "Name?", vbQuestion

but this doesn't work...

MsgBox ("Name?", vbQuestion)

So, instead of doing this...

MsgBox (.Formula)

you should really be doing this...

MsgBox .Formula

or even this...

Call MsgBox(.Formula)

instead. Note that using the Call statement with the MsgBox, as shown in my
last example, requires the parentheses and it allows multiple arguments to
be specified. The above discussion, by the way, applies to any subroutine or
to any function that is call as if it were a subroutine.

The reason behind this is that VB considers anything surrounded by
parentheses that are *not* required by syntax to be an expression to be
evaluated. A single value inside of parentheses just evaluates to itself.
Two values inside parentheses without an operator of some sort between them
(the comma is not an operator) is not an expression and, hence, it can't be
evaluated. So, while you can get away with using parentheses around single
values subroutine arguments, it is not generally a good habit to get in.
 
Thanks Rick! You make some very good points. I'll try your technique in the
future. Some people consider me extremely lazy and you approach will save me
two keystrokes per MsgBox call.
 
Back
Top