Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Properties or XML data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Properties or XML data
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 -- Gary''s Student - gsnu200903 "AnandaSim" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Properties or XML data
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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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 -- Gary''s Student - gsnu200903 "AnandaSim" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Properties or XML data
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. -- Gary''s Student - gsnu200903 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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 -- Gary''s Student - gsnu200903 "AnandaSim" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data: OLE DB and data link properties dialog | Excel Programming | |||
Importing Data: OLE DB and data link properties dialog | Excel Discussion (Misc queries) | |||
External Data Queries - Data Range Properties v Spreadsheet Format | Excel Discussion (Misc queries) | |||
Properties transferring from excel cells to word file properties | Excel Programming | |||
file properties with cell data? | Excel Programming |