Save persistent settings in a workbook

 2009-07-21    Workbooks    3    151

With the macros below you can save settings/information in a workbook that you can retrieve later. The saved settings will be hidden by default, but it is also possible to store the settings as visible to the user.

Sub SaveSettingWBN(strSettingName As String, strSettingValue As String, _
    Optional blnVisible As Boolean = False)
    With ThisWorkbook
        On Error Resume Next ' assumes that the workbook is unprotected
        .Names(strSettingName).Delete ' delete any existing name/setting
        .Names.Add strSettingName, "'" & strSettingValue, blnVisible
        On Error GoTo 0
    End With
End Sub

Function GetSettingWBN(strSettingName As String, _
    Optional strDefault As String = vbNullString) As String
Dim s As String
    s = strDefault
    With ThisWorkbook
        On Error Resume Next
        s = .Names(strSettingName).RefersTo 
        ' returns something like this: ="'stored_text"
        If Left$(s, 1) = "=" Then
            s = Mid$(s, 2)
        End If
        s = Replace(s, Chr(34), vbNullString)
        If Left$(s, 1) = "'" Then
            s = Mid$(s, 2)
        End If
        On Error GoTo 0
    End With
    GetSettingWBN = s
End Function

Sub TestSettings()
    SaveSettingWBN "TEST1", Format(Now, "yyyy-mm-dd") ' stored as a hidden name
    SaveSettingWBN "TEST2", "100", True ' stored as a visible name
    MsgBox "TEST1 = " & GetSettingWBN("TEST1"), , "Setting Value"
    MsgBox "TEST2 = " & GetSettingWBN("TEST2"), , "Setting Value"
End Sub

Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

Scott | 2017-02-03 14:49:59 (GMT)

Ah! I was looking for just this thing. Brilliant stuff and thank you :)

OPE | 2009-12-03 11:02:31 (GMT)

Since the names are hidden by default, accidental deletion by the users is not very likely to happen. You can also add any prefix you want to the names when you use the procedure to save the setting.
This also works fine with Addins.

Ross | 2009-12-03 09:59:36 (GMT)


This is a good tip, maybe prefix the name with some sort of "guid" constants so that user added names are not deleted in error?

How does this work with Addins?