|
|
|
|
|
|
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()
Const szVersion As String = "WorkbookVersion"
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
Dim szDocVal As String
szDocVal = ThisWorkbook.CustomDocumentProperties(szVersion).Value
ThisWorkbook.CustomDocumentProperties(szVersion).Value = CLng(szDocVal) + 1
End If
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 220 times.
|
|