0

I am currently working on a project in which I need to save a few variables, that i can use on every opening of my workbook, without the need to set it up manually on startup.

To create a custom document property, I am using the example-code provided by Microsoft on their official website.

Question:
After setting up the properties, I am accessing them by opening an item(x). The number x seems to depend on the alphabetical order of my property's name.

Is there a way to read and to work with a property, opening it by its name?

Sub InitializeCustomProperty()     
    Dim wksSheet1 As Worksheet     
    Set wksSheet1 = Application.ActiveSheet

    ' Add metadata to worksheet.
    wksSheet1.CustomProperties.Add _
      Name:="Computer 1", Value:="computername"
End Sub


Sub Add_My_Computer1()
    ' sets the value of my custom property (i"ve found out its Item(1) by outputting my properties 
    Dim sHostName2 As String
    sHostName2 = Environ$("computername")
    Worksheets(1).CustomProperties.Item(1).Value = sHostName2
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Florian
  • 3
  • 2
  • I recommend to save your properties in a worksheet instead, and make this worksheet hidden. – Pᴇʜ Jan 14 '19 at 10:53
  • Want to draw your attention to the fact that the worksheet related `CustomProperties` collection doesn't mean the same as the workbook related `CustomDocumentProperties` you are asking for in the question's title. - See [MS help: Excel.Workbook.CustomDocumentProperties](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.customdocumentproperties) – T.M. Jan 14 '19 at 17:39
  • Further links: [How to add a DocumentProperty to CustomDocumentProperties in Excel](https://stackoverflow.com/questions/14863250/how-to-add-a-documentproperty-to-customdocumentproperties-in-excel), [Chip Pearsons site](http://www.cpearson.com/excel/docprop.aspx) and a related theme at SO [Check if BuiltInDocumentProperty is set without error trapping](https://stackoverflow.com/questions/41766268/check-if-builtindocumentproperty-is-set-without-error-trapping) – T.M. Jan 14 '19 at 17:45

2 Answers2

1

I've no idea if there's a simpler way, but the following Function returns the CustomProperty by it's name:

Function PropertyByName(PropertyName) As CustomProperty
    Dim x As CustomProperty
    For Each x In Worksheets(1).CustomProperties
        If x.Name = PropertyName Then
            Set PropertyByName = x
            Exit For
        End If
    Next
End Function

You can read it with..

?PropertyByName("Computer 1").Value

Or you can write to it with..

PropertyByName("Computer 1").Value = Environ$("computername")

It could do with some error checking if the name isn't found, and perhaps the Worksheet could be passed as a parameter but hopefully you get the idea..

CLR
  • 11,284
  • 1
  • 11
  • 29
0

You might consider a defined name of either workbook or worksheet scope that is hidden from normal view.

Sub InitializeCustomNames()
    Dim wksSheet1 As Worksheet

    ' Add defined name to workbook
    ThisWorkbook.Names.Add Name:="wb1USR", _
                           RefersTo:=Chr(61) & Chr(34) & Environ("USERNAME") & Chr(34), _
                           Visible:=False

    ' Add defined name(s) to specific worksheet
    Set wksSheet1 = Worksheets("sheet5")
    wksSheet1.Names.Add Name:="ws1CN", _
                        RefersTo:=Chr(61) & Chr(34) & Environ("COMPUTERNAME") & Chr(34), _
                        Visible:=False
End Sub

Sub retrieveCustonNames()

    Debug.Print Split(ThisWorkbook.Names("wb1USR"), Chr(34))(1)

    Debug.Print Split(ThisWorkbook.Worksheets("sheet5").Names("ws1CN"), Chr(34))(1)

End Sub

On Sheet5 in A1:A2,

=ws1CN
=wb1USR

These will not be visible within the Formulas, Defined Names, Name Manager but they could be if you used Visible:=True.