excel - Check if BuiltInDocumentProperty is set without error trapping

Question

Task: My goal is to check if a value has been set in the BuiltInDocumentProperties collection of an Excel workbook.

Amplifying remark: I know that some doc properties items never show a value in Excel as they belong to ms word or ppt applications (e.g. item 15 'Number of words', item 25 'Slides' ...). On the other hand some properties have only occasional values in case of first use:

• item 10: 'Last print time'
• item 12: 'Last save time'

Of course one can do that by error trapping:

Example Code with Error trapping:

Sub test_showDocPropValue()
' Name of built in doc prog
  Dim propName As String
' a) Choose builtin doc prop disposing about a set value, such as 'Author', 'Category', ...
'    propName = "Category"
' b) Choose builtin doc prop of another ms application
'    propName = "Number of pages"

' c) Choose doc prop with occasionally set values
  propName = "Last print time"

' Show result
  MsgBox propName & " = " & showDocPropValue(propName), vbInformation, "BuiltInDocumentProperties"
End Sub

Function showDocPropValue(ByVal propName As String) As Variant
  Dim prop As Object
  Dim ret
' Built in Doc Props collection
  Set prop = ThisWorkbook.BuiltinDocumentProperties
' Error trapping
  On Error Resume Next
  ret = prop(propName).Value
  If Err.Number <> 0 Then
     ret = "(No value set)"
     Debug.Print Err.Number & ": " & Err.Description
  End If
' Return
  showDocPropValue = ret
End Function

My Question: For principal reasons I'd like to know if there is a straightforward method to get builtinDocumentProperties values avoiding error trapping

Additional hint Just to complete the theme by showing methods without error trapping within CUSTOM doc props, you can easily check for the existence of such items with the following code:

Private Function bCDPExists(sCDPName As String) As Boolean
' Purp.: return True|False if custom document property name exists
' Meth.: loop thru CustomDocumentProperties and check for existing sCDPName parameter 
' Site:  <http://stackoverflow.com/questions/23917977/alternatives-to-public-variables-in-vba/23918236#23918236>
' cf:    <https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_other/using-customdocumentproperties-with-vba/91ef15eb-b089-4c9b-a8a7-1685d073fb9f>
Dim cdp As Variant      ' element of CustomDocumentProperties Collection
Dim boo As Boolean      ' boolean value showing element exists
For Each cdp In ThisWorkbook.CustomDocumentProperties
    If LCase(cdp.Name) = LCase(sCDPName) Then
       boo = True      ' heureka
       Exit For        ' exit loop
    End If
Next
bCDPExists= boo          ' return value to function
End Function

Answer 1

I think there is not a straightforward way of doing it -- this is a Collection which doesn't have an easy way to test for existence of an item (versus a Dictionary.Exists method, or using a Match function against an array, etc.). Apart from error-trapping (which seems pretty straightforward IMO) you are left basically to use brute-force iteration over the collection's items, checking the .Name property for equivalence.

This is a approach similar to what you have with the CustomDocumentProperties to avoid the Error-handling if desired (although I see nothing explicitly wrong about that approach). Modified your showDocPropValue function and added an additional GetDocProp function to be used in tandem. This should work with your test case:

Function showDocPropValue(ByVal propName As String) As Variant
Dim prop As Object
Dim ret
' Get the BuiltInDocumentProperty(propName) if it exists
Set prop = GetDocProp(propName)
If prop Is Nothing Then
    ret = "(No value set)"
Else
    ret = prop(propName).Value
End If
' Return
showDocPropValue = ret
End Function

Function GetDocProp(ByVal propName$)
' returns the BuiltInDocumentProperties(propName) object if exists, else Nothing
Dim p As Object
Dim prop As Object
Set prop = ThisWorkbook.BuiltinDocumentProperties
For Each p In prop
    If p.Name = propName Then
        Set GetDocProp = p
        GoTo EarlyExit
    End If
Next
Set GetDocProp = Nothing
EarlyExit:
End Function

Personally, I would use this version instead (error handling in the GetDocProp function):

Function GetDocProp(ByVal propName$)
' returns the BuiltInDocumentProperties(propName) object if exists, else Nothing
Dim ret As Object

On Error Resume Next
Set ret = ThisWorkbook.BuiltinDocumentProperties(propName)
If Err.Number <> 0 Then Set ret = Nothing 'just to be safe...

Set GetDocProp = ret

End Function