The purpose of this workbook is to
demonstrate the use ofCustom 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", 1234And 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. |
|
