Worksheet.CustomProperties
 

Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note that
the CustomProperty.Count is incrementing as expected; it blows up on the 2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if a
property was actually added

Excel.CustomProperty prop = activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

Worksheet.CustomProperties
 

Derrick,

Yes, custom properties can be accessed by name. I don't know C#,
but the following works in VBA:

Debug.Print ThisWorkbook.CustomDocumentProperties("Test").Valu e


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Derrick" wrote in message
...
Hello all;

I'm messing around with Visual Studio Tools for Office 2003,
particularly Excel.

Was wondering if the Worksheet.CustomProperties collection has
to be indexed by an integer, or can you use the name of the
property you are trying to access? Since I don't think I can
guarantee the order in which the properties are added, I don't
want to use integer indexing (though I could loop through and
check the property Name).

Specifically, this code snippet (which I fire from a CommandBar
button) always throws a Type Mismatch exception (hope you don't
mind C#). Note that the CustomProperty.Count is incrementing
as expected; it blows up on the 2nd line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just
checking if a property was actually added

Excel.CustomProperty prop =
activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e)
//property doesn't exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

Worksheet.CustomProperties
 

Just to add, worksheets don't have custom properties. Workbooks do. And as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

--
Regards,
Tom Ogilvy

"Derrick" wrote in message
...
Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be
indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note
that
the CustomProperty.Count is incrementing as expected; it blows up on the
2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if a
property was actually added

Excel.CustomProperty prop =
activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property
doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

Worksheet.CustomProperties
 

Actually, in Visual Studio Tools for Office 2003, worksheets do indeed have
CustomProperties. Previous versions did not.

Cheers,

Derrick


"Tom Ogilvy" wrote in message
...
Just to add, worksheets don't have custom properties. Workbooks do. And
as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

--
Regards,
Tom Ogilvy

"Derrick" wrote in message
...
Hello all;

I'm messing around with Visual Studio Tools for Office 2003, particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be
indexed
by an integer, or can you use the name of the property you are trying to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I
could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note
that
the CustomProperty.Count is incrementing as expected; it blows up on the
2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking if
a
property was actually added

Excel.CustomProperty prop =
activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property
doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}

Worksheet.CustomProperties
 

Yes, apparently we are talking about two different things with similar
names.

anyway, this reference indicates you can refer to them using an index:

http://blogs.msdn.com/eric_carter/ar...18/265787.aspx

--
Regards,
Tom Ogilvy


"Derrick" wrote in message
.. .
Actually, in Visual Studio Tools for Office 2003, worksheets do indeed
have
CustomProperties. Previous versions did not.

Cheers,

Derrick


"Tom Ogilvy" wrote in message
...
Just to add, worksheets don't have custom properties. Workbooks do.
And
as
Chip pointed out, they are called customdocumentproperties in the Excel
object model. Can't say for C#.

--
Regards,
Tom Ogilvy

"Derrick" wrote in message
...
Hello all;

I'm messing around with Visual Studio Tools for Office 2003,
particularly
Excel.

Was wondering if the Worksheet.CustomProperties collection has to be
indexed
by an integer, or can you use the name of the property you are trying
to
access? Since I don't think I can guarantee the order in which the
properties are added, I don't want to use integer indexing (though I
could
loop through and check the property Name).

Specifically, this code snippet (which I fire from a CommandBar button)
always throws a Type Mismatch exception (hope you don't mind C#). Note
that
the CustomProperty.Count is incrementing as expected; it blows up on
the
2nd
line within the Try:

private void TestProperties(Excel.Worksheet activeSheet)
{
try
{
int i = activeSheet.CustomProperties.Count; //just checking
if
a
property was actually added

Excel.CustomProperty prop =
activeSheet.CustomProperties["Test"];

if((bool)prop.Value == true)
{
MessageBox.Show("Test property was true");
}
else
{
MessageBox.Show("Test property was false");
}
}
catch(System.Runtime.InteropServices.COMException e) //property
doesn't
exist, add it
{
activeSheet.CustomProperties.Add("Test", true);
//MessageBox.Show(e.Message);
}
}