Programmatically Save & Restore Custom Values with VBA

When using the Save defined cell values only mode, XLS Padlock allows you to save and restore custom values in addition to pre-defined cell values. This is useful for saving variables, settings, or data not directly stored in a cell.

Overview #

This functionality relies on two VBA events and two VBA API functions. You place code in the event subs to read or write your custom data when the user loads or saves their work.

Core Events and Functions #

VBA Events #

The following two event subroutines must be placed in a module of your Excel workbook. XLS Padlock will call them automatically during the save/load process.

' Called when a user loads a save file.
Sub XLSPadlock_RestoreCustomValues()
    ' Your code to read values goes here.
    MsgBox ("Restoring custom values...")
End Sub

' Called when a user saves their work.
Sub XLSPadlock_SaveCustomValues()
    ' Your code to write values goes here.
    MsgBox ("Saving custom values...")
End Sub

VBA API Functions #

• WriteCustomCellValue(UniqueID, Value): Writes a single string value associated with a unique ID.
• ReadCustomCellValue(UniqueID, DefaultValue): Reads a single string value for a given ID. If the ID is not found, it returns the DefaultValue.

Reading and Writing Single Values #

Here is how you would write a single value:

Sub XLSPadlock_SaveCustomValues()
    Dim XLSPadlock1 As Object
    On Error Resume Next
    Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
    XLSPadlock1.WriteCustomCellValue "MySetting", "MyValue"
End Sub

And here is how you would read it back:

Sub XLSPadlock_RestoreCustomValues()
    Dim XLSPadlock1 As Object
    On Error Resume Next
    Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
    
    Dim MySettingValue As String
    MySettingValue = XLSPadlock1.ReadCustomCellValue("MySetting", "Default")
End Sub

Reading All Values as a Dictionary #

You can also read all saved custom values at once by passing an empty string as the ID to ReadCustomCellValue. This returns a Scripting.Dictionary object.

Sub XLSPadlock_RestoreCustomValues()
    Dim XLSPadlock1 As Object
    On Error Resume Next
    Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object

    Dim Dict As Object ' Scripting.Dictionary
    Set Dict = XLSPadlock1.ReadCustomCellValue("", "")

    If Not Dict Is Nothing Then
        For Each Key In Dict.Keys
            MsgBox "Key: " & Key & ", Value: " & Dict(Key)
        Next Key
    End If
End Sub

Full Example: Saving/Restoring a Column #

This helper function generates a comma-separated string from the values of a given cell range.

Function CsvRange(myRange As Range) As String
    Dim csvRangeOutput As String
    Dim entry As Variant
    For Each entry In myRange
        If Not IsEmpty(entry.Value) Then
            csvRangeOutput = csvRangeOutput & entry.Value & ","
        End If
    Next
    If Len(csvRangeOutput) > 0 Then
        CsvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
    End If
End Function

This event is called when the user saves their work. It uses the helper function to convert the entire used range of Column A into a single string and saves it.

Sub XLSPadlock_SaveCustomValues()
    Dim XLSPadlock1 As Object
    On Error Resume Next
    Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object

    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(2).Range("A1").CurrentRegion

    Dim myString As String
    myString = CsvRange(rng)

    XLSPadlock1.WriteCustomCellValue "MyEntireColumnA", myString
End Sub

This event is called when the user loads a save file. It reads the string and restores the values back into Column A.

Sub XLSPadlock_RestoreCustomValues()
    Dim XLSPadlock1 As Object
    On Error Resume Next
    Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object

    Dim Val As String
    Val = XLSPadlock1.ReadCustomCellValue("MyEntireColumnA", "")

    If Val <> "" Then
        Dim r As Range, i As Long, ar
        Set r = ThisWorkbook.Worksheets(2).Range("A:A")
        r.ClearContents
        
        ar = Split(Val, ",")
        For i = 0 To UBound(ar)
         r.Cells(i + 1, 1).Value = ar(i)
        Next
    End If
End Sub

## See Also

- [How to migrate user data from a previous version](excel-vba-migrate-user-data-updates)