Excel VBA Equivalent of Word Variables Collection-CodePudding

Does Excel VBA (Office 16) have an equivalent or similar property to the Word VBA ActiveDocument.Variables collection? I'd like to save a project value in the document without having to use a hidden sheet.

CodePudding user response：

You can use the Name object to permanently retain desired values. The Name object usually refers to a cell or group of cells, or even the entire worksheet. But it can also be used to store formulas and in this way also save data.

If you use this feature, it is recommended that you name your variable names so that they are clearly identifiable, in addition to the rest of the other Name objects. Also, you can hide them to avoid misuse. See the example code below:

Sub fnCreateNamesAsVariablesAndHideThem()
'the False parameter hides the name to the front-end

    With ThisWorkbook.Names
        .Add "var_intNumber", 7, False
        .Add "var_strTitle", "Gone with the Wind", False
        .Add "var_LngPtr", 1127654, False
    End With

    Debug.Print Names("var_strTitle")
    Debug.Print Names("var_LngPtr")
    'you can use them in a cell, like: =var_LngPtr, even hidden.
End Sub

It is important to note that, when creating a Name, the possible existence of another one is not evaluated: it will always be overwritten.

On the web there are mentions of other ways to achieve this, such as writing to the Windows Registry, creating a "Custom Property", and so on. They are more laborious to make and to read.