检查是否设置了BuiltInDocumentProperty而不发生错误陷阱 [英] Check if BuiltInDocumentProperty is set without error trapping

问题描述

任务:我的目标是检查是否在Excel工作簿的BuiltInDocumentProperties集合中设置了一个值.

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

致辞:我知道有些文档属性项目从不在Excel中显示值，因为它们属于ms word或ppt应用程序(例如，第15项单词数"，第25项幻灯片" ...).另一方面，某些属性在首次使用的情况下只有偶尔的值:

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:

• 项目10:最后打印时间"
• 项目12:最后节省时间"

当然可以通过错误捕获来做到这一点:

Of course one can do that by 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

我的问题:出于主要原因，我想知道是否有一种简单的方法来获取builtinDocumentProperties值，从而避免错误捕获

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

其他提示只需通过显示在CUSTOM doc道具内没有错误陷阱的方法来完成主题，您就可以使用以下代码轻松检查此类项目的存在:

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

推荐答案

我认为没有简单的方法可以做到-这是 Collection ，没有简单的方法测试项目的存在性(相对于 Dictionary.Exists 方法，或对数组使用 Match 函数，等等).除了错误捕获(这似乎是非常简单的IMO)之外，您基本上还需要对集合的项目使用蛮力迭代，检查 .Name 属性是否等效.

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.

这是一种与 CustomDocumentProperties 相似的方法，可以在需要时避免错误处理(尽管我认为该方法没有明显的错误).修改了您的 showDocPropValue 函数，并添加了一个附加的 GetDocProp 函数以串联使用.这应该适用于您的测试用例:

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

我个人将使用该版本( GetDocProp 函数中的错误处理):

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

这篇关于检查是否设置了BuiltInDocumentProperty而不发生错误陷阱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！