The purpose of this workbook is to demonstrate the use of

Custom Document Properties

through VBA's CustomDocumentProperties (CDP) property

Custom Document Properties are stored in:

Menu: File > Properties : Custom

This workbook has a family of VBA routines to help you create, amend, and delete Custom Document Properties

Download: CustomDocumentProperties.zip

 
These routines are all in the VBA module called CDP.
   
SetCDP Amend or set the value for a CDP
CheckCDP Checks if a Custom Document Property exists
CheckCDPType Check the type of a Custom Document Property
GetCDP Get the current value of a CDP
DeleteCDP Delete a CDP
ListAllCDPs List all CDPs to the immediate window
DeleteAllCDPs Delete all CDPs
CountCDP Return the number of CDPs
   
TestCDPRoutines Test CDP routines
   
CheckType Returns a datatype value usable by CustomDocumentProperties.
   

All routines are commented

You can use these in your own project by copying the VBA module CDP to your workbook.

CustomDocumentProperties (CDP) is a property of the Workbook object. It is one of Excel's least-well documented features.

This main purpose of this workbook is to supply a set of useful routines that allow you to easily use this property within your work.

Why "easily"?

Take, as an example, the VBA routine below.
 

Application.ActiveWorkbook.CustomDocumentProperties.Add _
    Name:="TestCDP", _
    LinkToContent:=False, _
    Type:=msoPropertyTypeString, _
    Value:="Hello World", _
    LinkSource:=False

You can run this once to add the CDP "TestCDP" to your properties.
If you run it again it will fail because the CDP "TestCDP" now exists.

Our routine SetCDP will not only replace an existing CDP with a new value, it will also recognise and, if necessary, change the data type.

Here, we are setting a property with the name CdpName to the number value of 1234:
SetCDP "CdpName", 1234

And now we are resetting it to a string value:
SetCDP "CdpName", "Three"

 
You can link a Custom Document Property to an address within the workbook.

If LinkToContent is set to True, you have to supply an address or range name for LinkSource from the workbook. If the address or range name covers more than one cell, the CDP takes the value from the top left cell of the range.

You can see CustomDocumentProperties requires five parameters. You must supply all five when creating a new value. If you don't link to a source then LinkToContent and LinkSource, and are set to False.

ooo

Published, 27 February 2003
Last updated 01 March 2011 20:51