Save persistent settings in a workbook

 2009-07-21    Workbooks    3    208

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