A Plea for Custom Document Property Manipulation in Excel

In my experience, the capabilities of Microsoft Excel have far outweighed that of Microsoft Word.  It may possibly be my line of work, but I’ve thus far always found that I can accomplish more with Excel’s formulas, references, and especially macros than I could ever hope to get out of a Word document.  Word was great for typing fancy-looking text, but when I required any amount of functionality Excel was always there to save.  Until today…


The Request
Microsoft: please enhance Excel such that I can

1. Retrieve and manipulate Custom Document Properties using only Formulas (no macros required) in the same way that can be found using Word’s Insert > QuickParts > Field functionality.
2. Insert these same custom document properties into the header/footer section of a worksheet.

Microsoft Word
For example, in Word, I can choose Insert > Quick Parts > Field > DocProperty.  Then choose any document property maintained and insert a reference to it.



Limitation of Microsoft Excel
Unfortunately, in Excel a macro must be written in order to retrieve values from the CustomDocumentProperties collection.  A formula can then reference this macro.  In a corporate network with any sort of security policy, this means that users must “enable the macros” for this to function properly.

Function GetProperty(PropertyName As String, PropertySet As PropertyLocation, _
    Optional WhatWorkbook As Workbook) As Variant

(Reference Chip Pearson’s website for GetProperty code.)

Further, I’m unable to insert any auto-updated values into the footer of an Excel worksheet other than the handful of values pre-defined by Microsoft: page number, date/time, sheet name, etc.  In order for this to occur, I need yet another macro to execute Before_Print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Worksheets("Sheet1").PageSetup
        .CenterFooter = GetProperty(“Issue.Header.Id”, 2)
    End With
End Sub

Code found by doing a Google search for excel formula in footer.

I’d like to see efforts made to improve the functionality of these items in the next release of Microsoft Excel.

Enjoy!