excel中自定义公式_Excel中的自定义文档属性

excel中自定义公式

When developing Excel applications, it's a common need to be able to store settings or non-sheet data so that they are available the next time you open Excel. Some examples of data you might want to store are a Yes/No value pertaining to the user seeing a certain message, a user-selected highlight color, or a count of how many times the user has opened the workbook.

开发Excel应用程序时，通常需要能够存储设置或非工作表数据，以便下次打开Excel时可以使用它们。 您可能要存储的数据的一些示例是与用户看到特定消息有关的是/否值，用户选择的突出显示颜色或用户打开工作簿的次数的计数。

There are several common ways to do that including ini files (text files with a recognized structure), regular text files, databases and the Registry, however some of those methods have potential problems with permissions or the processing time involved with opening and closing external files.

有几种常用的方法可以执行此操作，包括ini文件（具有公认结构的文本文件），常规文本文件，数据库和注册表，但是其中一些方法在打开或关闭外部文件时存在权限或处理时间方面的潜在问题。 。

One less-known method that doesn't have those problems is Custom Document Properties. These are Just like the built-in properties stored in Office documents (like those shown in the above picture) except that these are ones that you can create, modify or delete.

一种没有这些问题的鲜为人知的方法是“自定义文档属性”。 这些就像存储在Office文档中的内置属性（如上图所示）一样，除了可以创建，修改或删除这些属性。

A Practical Example

一个实际的例子

I recently created an Excel application where I wanted to display an informational message when a certain action was performed but I realized that since the action would be performed many times that the message would soon become annoying. Because of that I needed to give the user a way to turn the message off or on as needed and to remember that choice the next time the workbook was opened, and I decided on a Custom Document Property. 

我最近创建了一个Excel应用程序，当执行某项操作时，我想在其中显示一条信息性消息，但我意识到，由于该操作将执行多次，因此该消息很快会变得令人讨厌。 因此，我需要为用户提供一种根据需要关闭或打开消息的方式，并在下次打开工作簿时记住该选择，因此我决定了“自定义文档属性”。

The first step was to create the property and that was accomplished with this code.

第一步是创建属性，并通过此代码完成。

Sub CreateCDP()
   On Error GoTo CreateCDP_Error


    With ActiveWorkbook.CustomDocumentProperties
        .Add Name:="Show Message", _
            LinkToContent:=False, _
            Type:=msoPropertyTypeString, _
            Value:="Yes"
    End With

    On Error GoTo 0
       Exit Sub


CreateCDP_Error:
    If Err.Number = -2147467259 Then
        MsgBox "Custom DocumentProperty already exists"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateCDP of Module Module1"
    End If
End Sub

 

That created a property called "Show Message" with an initial value of "Yes". In other situations that value could could have been a number or other variable types. See this article for a complete description. The reason for the error checking is that an error will result if the property already exists.

这创建了一个名为“ Show Message”的属性，其初始值为“ Yes”。 在其他情况下，该值可能是数字或其他变量类型。 请参阅本文以获取完整说明。 错误检查的原因是，如果该属性已经存在，将导致错误。

To use the property I created a macro similar to this one.

为了使用该属性，我创建了一个与此宏类似的宏。

Sub ShowAgain()
    If ActiveWorkbook.CustomDocumentProperties("Show Message") = "Yes" Then
        If vbCancel = MsgBox("Some long, possibly annoying, message." _
                & vbCrLf & vbCrLf & "(Click 'Cancel' if you no longer wish to see this message)",         vbOKCancel) Then
            HideMessage
        End If
    End If
End Sub 

That refers to the HideMessage macro which changes the value of the property to "No". It looks like the following.

这指的是HideMessage宏，该宏将属性的值更改为“否”。 看起来如下。

Sub HideMessage()

    With ActiveWorkbook.CustomDocumentProperties("Show Message")
        .Value = "No"
    End With
    
End Sub 

I also created these two companion macros.

我还创建了这两个伴随宏。

Sub ShowMessage()

With ActiveWorkbook.CustomDocumentProperties("Show Message")
        .Value = "Yes"
    End With

End Sub 

Sub RemoveCDP()

   On Error GoTo RemoveCDP_Error


    With ActiveWorkbook.CustomDocumentProperties("Show Message")
        .Delete
    End With


   On Error GoTo 0
   Exit Sub


RemoveCDP_Error:


    If Err.Number = 5 Then
        MsgBox "Custom DocumentProperty does not exist"
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RemoveCDP of Module Module1"
    End If
   
End Sub

 

ShowMessage will turn the message on again, and RemoveCDP deletes the custom property. It has error checking because an error will occur if the property already exists.

ShowMessage将再次打开消息，并且RemoveCDP删除自定义属性。 它具有错误检查功能，因为如果该属性已经存在，则会发生错误。

I've attached this workbook which demonstrates the use of the custom property. All the code is in Module1.

我已附加了此工作簿，该工作簿演示了custom属性的用法。 所有代码都在Module1中。

CDP.xlsm

CDP文件

If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!

如果您发现本文对您有所帮助，请单击下面的“竖起大拇指”按钮。 这样做可以让我知道对EE成员有价值的内容，并为以后的文章提供指导。 它还以几点的形式为我提供了积极的反馈。 谢谢！

翻译自: https://www.experts-exchange.com/articles/29697/Custom-Document-Properties-in-Excel.html

excel中自定义公式