2

I just discovered that in MS Word it is possible to store a Variable in a MS Word File that can not be accessed through the regular interface when running Microsoft Word.

Sub SetMyVariable()
Dim VARNAME As String
    VARNAME = "HiddenVar"
    ActiveDocument.Variables.Add VARNAME, "My special info"
End Sub

This gets saved in the XML Schema under word\settings.xml

I have tried using the ThisWorkbook Object in Excel, but it doesn't seem to have a Variable object that can be added like in word.

I want to know if there is something similar in Excel to store information/varialbes that get saved with the file.

PS: the closest thing I can think of (and use in codig) is a hidden named range.

rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • 1
    Try `CustomDocumentProperties`: http://stackoverflow.com/questions/14863250/how-to-add-a-documentproperty-to-customdocumentproperties-in-excel – Robin Mackenzie Mar 09 '17 at 09:06
  • I'm aware of this option, but a regular user can still access this and change it... although I realize that the vast majority will never bother to look. the Variable object in Word is like a data island, in that it can not be changed without specifically knowing it is there or looking at the XML file, which i found interesting. – rohrl77 Mar 09 '17 at 10:09
  • AFAIK, hidden names are used in Excel instead of .Variables http://excelsemipro.com/2010/11/hide-or-show-names-in-excel-with-vba/ – Slai Mar 09 '17 at 22:57

1 Answers1

1

You can try with the CustomXMLParts property of the Workbook which from the link seems a generic feature of Office products and available in Excel. Given you noted that a user would have to manually inspect the XML within the unzipped xlsx files then this seems to map to the Word Variables feature. The code sample just substitutes ThisWorkbook for ActiveDocument: 

Option Explicit

Sub TextXMLPart()

    Dim objXMLPart As CustomXMLPart

    'add
    Set objXMLPart = ThisWorkbook.CustomXMLParts.Add("<foo>bar</foo>")

    'inspect
    For Each objXMLPart In ThisWorkbook.CustomXMLParts
        Debug.Print objXMLPart.XML
    Next objXMLPart

End Sub

The accepted answer to this question (which focuses on Excel and ) states that:

Custom XML parts For an application-level add in, this is my preferred method of storing any application data that needs to be persisted in a saved xls file without ever being visible to the user.

Community
  • 1
  • 1
Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56