Save persistent settings in a workbook
2009-07-21 Workbooks 3 234
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