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
' 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
' 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 3,603
Downloads 1,513
CodeID
DB ID
ANmarAmdeen
602
Revisions
v2.0
Monday
July
13
2020