VBA Express : Excel - Store Values in Custom Document Properties

Excel

Store Values in Custom Document Properties

Ease of Use

Easy

Version tested with

2002, 2002

Submitted by:

Justinlabenne

Description:

Store Values in a Defined Name

Discussion:

Alot of times it is useful to store a value for later use in your Excel workbook or application. Instead of storing these values in cells on a sheet, can creat your own properties and then store values in them The example code createsa new Custom Document Property with a starting value of 1 if it does not exist. If it does, the value is incremented by 1 every time the code is called. This example is based on Worbook version numbering, but Document properties can be created and used to store many types of values. The example workbook also contains code that places Custom Document Properties into a newly created workbook.

Code:

instructions for use

			Option Explicit 
 
Sub SaveValueInCustomDocProperty() 
     '   ============================================
     '   Save a value in CustomDocumentProperties
     '   ============================================
     '   Constant string for the property we are adding
    Const szVersion As String = "WorkbookVersion" 
     
     
     '   ========================================================================
     '   If the name doesn't exist, we create it and set the initial value to 1
    On Error Resume Next 
    Dim cstmDocProp As DocumentProperty 
    Set cstmDocProp = ThisWorkbook.CustomDocumentProperties(szVersion) 
     
    If Err.Number > 0 Then 
         
        ThisWorkbook.CustomDocumentProperties.Add _ 
        Name:=szVersion, _ 
        LinkToContent:=False, _ 
        Type:=msoPropertyTypeNumber, _ 
        Value:=1 
         '   ========================================================================
         
    Else 
         
         '   ========================================================================
         '       if our name exists, we need to increment the value in it by 1
         '       to do this, we parse the name's RefersTo value:
        Dim szDocVal As String 
        szDocVal = ThisWorkbook.CustomDocumentProperties(szVersion).Value 
         
         
         '       Reset the name to refer to our new value
        ThisWorkbook.CustomDocumentProperties(szVersion).Value = CLng(szDocVal) + 1 
         '   ========================================================================
         
    End If 
     
     '   Explicitly clear memory
    Set cstmDocProp = Nothing 
     
End Sub

How to use:

Open an Excel Workbook
Copy the code
Press Alt + F11 to open the Visual Basic Editor (VBE)
Select INSERT > MODULE from the menubar
Paste code into the right pane
Press Alt+Q to return to Excel
Save workbook before any other changes

Test the code:

Go to TOOLS > MACRO > MACROS
When the dialog appears, select {SaveValueInCustomDocProperty}
Press Run

Sample File:

SaveValuesInCstmDocProps.zip 9.06KB

Approved by mdmackillop

This entry has been viewed 168 times.