Setting and retrieving Excel CustomProperties

[SBendBuckeye]

Can you not reference Excel CustomProperties using the name as a variant index into the collection like you do for Names, Sheets, etc (eg Sheets("Sheet1"))?

I created a CustomProperty using Add Name:="MyName", Value:="MyValue" and it is there correctly, but I get an Error 13, Subscript message when trying to reference CustomProperties("MyName").

The only way I can find it is to iterate them and look for a match. Am I missing something obvious here? Thanks!

Have a great day!

j2consulting@yahoo.com

 

[TonyJollans]

Hi SBB,

Not sure exactly what you're using, but ..

[blue][tt] ActiveWorkbook.CustomDocumentProperties("TonyTest")[/tt][/blue]

.. works fine for me.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SBendBuckeye]

Hello Tony,

I needed something I could specify at the sheet level so CustomDocumentProperties won't work for me, so I used CustomProperties. They do not appear to support the standard number or named index properties most Excel collections to.

I ended up prefixing the name with a number so I could guarantee the order. You can try something like below to see what I did:

Sheets("Sheet1").CustomProperties.Add Name:="SheetType", Value:="Test"

Sheets("Sheet1").CustomProperties.Add Name:="01SheetType", Value:="Test"

Sheets("Sheet1").CustomProperties.Add Name:="HideSheet", Value:="True"

Sheets("Sheet1").CustomProperties.Add Name:="02HideSheet", Value:="True"



Have a great day!

j2consulting@yahoo.com

 

[TonyJollans]

Hi SBB,

CustomProperties are new in 2K2 and I only have 2K so can’t try anything. It does seem a bit restrictive (and different from other collections) doesn’t it? All the examples on MSDN use numeric indexes into the Collection so it seems you are right.

You could just use your own Collection(s). In each sheet add a general declaration like ..

[blue]

Public MyCustomProperties As New Collection

[/blue]

Then, wherever you want you should be able to add ..

[blue]

Sheet1.MyCustomProperties.Add “Test”, “SheetType”

[/blue]

.. and use ..

[blue]

Sheet1.MyCustomProperties(“SheetType”)

[/blue]

Not quite as good as the built-in variety but it should work.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.