SettingRead+SettingSave

This is the final version of my set of Setting functions
SettingRead, SettingSave, SettingCount, SettingRow, SettingRename, SettingRemove, SettingRead_Desctription, SettingSave_Description, SettingRead_Value2, SettingSave_Value2
Along with the _Custom version of each

These functions are used to manipulate and read/write settings to/from VBA into "Settings" sheet, a way that I have been using for decades now to let me develop customizable and smart applications.

Edit 2024-07-18: Adding SettingInsert and SettingInsert_Custom to insert row of setting after certain row

CodeFunctionName

What is this?

Public

Tested

Original Work

' New version of SettingRead
' Using the customizable SettingRead_Custom
Function SettingRead(SettingID)
    ' Columns expected ...
    '    Sheet name = 'D'
    ' A    |    B        |    C    |    D
    ' ID    |    Name    | Value | Description
    ' Default call, Sheet = 'D'
    SettingRead = SettingRead_Custom(SettingID)
    ' Custom call
    'SettingRead = SettingRead_Custom(SettingID, ThisWorkbook.Name, "Data", "A1")
End Function
Sub SettingSave(SettingID, SettingValue, Optional SettingDescription = "")
    'SettingSave_Custom SettingID, SettingValue, SettingDescription, ThisWorkbook.Name, "Data", "A1"
    SettingSave_Custom SettingID, SettingValue, SettingDescription
End Sub
Function SettingCount(SettingMask)
    SettingCount_Custom SettingMask
End Function
Function SettingRow(SettingID)
    SettingRow = SettingRow_Custom(SettingID)
End Function
Sub SettingRename(SettingID, NewName)
    SettingRename_Custom SettingID, NewName
End Sub
Sub SettingRemove(SettingID)
    SettingRemove_Custom SettingID
End Sub
Function SettingRead_Description(SettingID)
    SettingRead_Description = SettingRead_Description_Custom(SettingID)
End Function
Sub SettingSave_Description(SettingID, NewDescription)
    SettingSave_Description_Custom SettingID, NewDescription
End Sub
Function SettingRead_Value2(SettingID)
    SettingRead_Value2 = SettingRead_Value2_Custom(SettingID)
End Function
Sub SettingSave_Value2(SettingID, NewValue2)
    SettingSave_Value2_Custom SettingID, NewValue2
End Sub
' Added 2024-07-18
Sub SettingInsert(SettingID, Settingvalue, AfterRow, Optional SettingDescription = "")
    'SettingSave_Custom SettingID, SettingValue, SettingDescription, ThisWorkbook.Name, "Data", "A1"
    SettingInsert_Custom SettingID, Settingvalue, AfterRow, SettingDescription, , "Meta", "EA1"
End Sub

Sub SettingInsert_Custom(SettingID, Settingvalue, AfterRow, Optional SettingDescription = "", Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    CoCo1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If CoCo1 > 0 Then
        NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0) - 1
    ElseIf AfterRow = -1 Then
        NV = WorksheetFunction.CountA(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(0, 1).EntireColumn)
    Else
        Workbooks(WbData).Worksheets(ShData).Range(Range(A1Data).Offset(AfterRow, 0).Address, Range(A1Data).Offset(AfterRow, 8).Address).Insert xlDown
        NV = AfterRow
    End If
    If Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 0).Value = "" Then _
        Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 0).Value = _
        WorksheetFunction.Max(Workbooks(WbData).Worksheets(ShData).Range(A1Data).EntireColumn) + 1
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 1).Value = SettingID
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 2).Value = Settingvalue
    If SettingDescription > "" Then Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 3).Value = SettingDescription
End Sub

Function SettingRead_Custom(SettingID, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    SettingRead_Custom = "N/A"
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Function
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    SettingRead_Custom = Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 2).Value
End Function
Sub SettingSave_Custom(SettingID, SettingValue, Optional SettingDescription = "", _
    Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    NV = WorksheetFunction.CountA(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(0, 1).EntireColumn)
    If Coco1 > 0 Then NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0) - 1
    If Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 0).Value = "" Then _
        Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 0).Value = _
        WorksheetFunction.Max(Workbooks(WbData).Worksheets(ShData).Range(A1Data).EntireColumn) + 1
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 1).Value = SettingID
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 2).Value = SettingValue
    If SettingDescription > "" Then Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV, 3).Value = SettingDescription
End Sub
Function SettingCount_Custom(SettingMask, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    SettingCount_Custom = 0
    If WbData = "This" Then WbData = ThisWorkbook.Name
    SettingCount_Custom = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingMask)
End Function
Function SettingRow_Custom(SettingID, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    SettingRow_Custom = 0
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Function
    SettingRow_Custom = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
End Function
Sub SettingRename_Custom(SettingID, NewName, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Sub
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 1).Value = NewName
End Sub
Sub SettingRemove_Custom(SettingID, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Sub
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    Workbooks(WbData).Worksheets(ShData).Range(Range(A1Data).Offset(NV - 1, 0).Address, Range(A1Data).Offset(NV - 1, 3).Address).Delete xlShiftUp
End Sub
Function SettingRead_Description_Custom(SettingID, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    SettingRead_Description_Custom = "N/A"
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Function
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    SettingRead_Description_Custom = Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 3).Value
End Function
Sub SettingSave_Description_Custom(SettingID, NewDescription, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    Coco1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If Coco1 = 0 Then Exit Sub
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 3).Value = NewDescription
End Sub

Function SettingRead_Value2_Custom(SettingID, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    SettingRead_Value2_Custom = "N/A"
    If WbData = "This" Then WbData = ThisWorkbook.Name
    CoCo1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If CoCo1 = 0 Then Exit Function
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    SettingRead_Value2_Custom = Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 4).Value
End Function
Sub SettingSave_Value2_Custom(SettingID, NewValue2, Optional WbData = "This", Optional ShData = "D", Optional A1Data = "A1")
    If WbData = "This" Then WbData = ThisWorkbook.Name
    CoCo1 = WorksheetFunction.CountIf(Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, SettingID)
    If CoCo1 = 0 Then Exit Sub
    NV = WorksheetFunction.Match(SettingID, Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(, 1).EntireColumn, 0)
    Workbooks(WbData).Worksheets(ShData).Range(A1Data).Offset(NV - 1, 4).Value = NewValue2
End Sub

SettingID, SettingValue, SettingDescription, SettingMask, NewName, NewDescription, NewValue2, WbData, ShData, A1Data, AfterRow