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 2690 Downloads 1175

VBA-Excel Components
ANmarAmdeen
728
Attachments
Revisions

v2.0