How to add a DocumentProperty to CustomDocumentProperties in Excel?

Question

I'm trying to add a DocumentProperty to the CustomDocumentProperties collection. Code as follows:

Sub testcustdocprop()
Dim docprops As DocumentProperties
Dim docprop As DocumentProperty

Set docprops = ThisWorkbook.CustomDocumentProperties
Set docprop = docprops.Add(Name:="test", LinkToContent:=False, Value:="xyz")

End Sub

Running this gives me the following error:

Run-time error '5':
Invalid procedure call or argument

I tried running it with .Add as a void function, like so:

docprops.Add Name:="test", LinkToContent:=False, Value:="xyz"

This gave me the same error. How do I add a custom document property?

Answer 1

Try this routine:

Public Sub updateCustomDocumentProperty(strPropertyName As String, _
    varValue As Variant, docType As Office.MsoDocProperties)

    On Error Resume Next
    ActiveWorkbook.CustomDocumentProperties(strPropertyName).Value = varValue
    If Err.Number > 0 Then
        ActiveWorkbook.CustomDocumentProperties.Add _
            Name:=strPropertyName, _
            LinkToContent:=False, _
            Type:=docType, _
            Value:=varValue
    End If
End Sub

---

Edit: Usage Examples

^{Five years later and the 'official' documentation is still a mess on this... I figured I'd add some examples of usage:}

Set Custom Properties

Sub test_setProperties()
    updateCustomDocumentProperty "my_API_Token", "AbCd1234", msoPropertyTypeString
    updateCustomDocumentProperty "my_API_Token_Expiry", #1/31/2019#, msoPropertyTypeDate
End Sub

Get Custom Properties

Sub test_getProperties()
    MsgBox ActiveWorkbook.CustomDocumentProperties("my_API_Token") & vbLf _
        & ActiveWorkbook.CustomDocumentProperties("my_API_Token_Expiry")
End Sub

List All Custom Properties

Sub listCustomProps()
    Dim prop As DocumentProperty
    For Each prop In ActiveWorkbook.CustomDocumentProperties
        Debug.Print prop.Name & " = " & prop.Value & " (" & Choose(prop.Type, _
            "msoPropertyTypeNumber", "msoPropertyTypeBoolean", "msoPropertyTypeDate", _
            "msoPropertyTypeString", "msoPropertyTypeFloat") & ")"
    Next prop
End Sub

Delete Custom Properties

Sub deleteCustomProps()
    ActiveWorkbook.CustomDocumentProperties("my_API_Token").Delete
    ActiveWorkbook.CustomDocumentProperties("my_API_Token_Expiry").Delete
End Sub