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 from VBA into "Settings" sheet, a way that I have been using for 5 years now to let me develop customize-able and smart applications


Public

Tested

My Own 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



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

Views 2928 Downloads 1256

VBA-Excel Components
ANmarAmdeen
763
Attachments
Revisions

v2.0