, , 45 , -, . , .
:
; . , .
, .
| A | B | C |
--+--------------------+--------------------+--------------------+
1|Permitted |Conditions --------------> |
2|C=V1|V2|V3|V4 | | |
3|D=V5 |C=V1|V2 | |
4|D=V6 |C=V3|V4 | |
5|E=V7|V8 |D=V5 |C=V1 |
6|E=V9|V10 |D=V5 |C=V2 |
7|E=V11|V12 |D=V6 |C=V3 |
8|E=V13|V14 |D=V6 |C=V4 |
2 , C V1 V2 V3 V4.
3 , D V5, C V1 V2.
4 D .
5 , E V7 V8, D V5, C V1.
, , . , , , .
. , , , :
Rules per Column table
C RR RR = Column First rule Last rule
3 1 1
4 2 3
5 4 7
, , 3 (C), 4 (D) 5 (E). , 3 (C) 1 - 1, 5 (E) 4 7.
Rule table
I VV VV CC CC = Index First value Last value First condition Last condition
1 1 4 1 0
2 5 5 1 1
3 8 8 2 2
4 11 12 3 4
5 15 16 5 6
6 19 20 7 8
7 23 24 9 10
1 1 - 0, . 1 4 (V1, V2, V3 V4) . 2 .
4 11 12 (V7 V8) , 3-4. 3 , 4 (D) 13 (V5) . 4 , 3 (C) 14 (V1) . 5 .
Condition table
I C VV VV = Index Column First value Last value
1 3 6 7
2 3 9 10
3 4 13 13
4 3 14 14
5 4 17 17
6 3 18 18
7 4 21 21
8 3 22 22
9 4 25 25
10 3 26 26
Value table Entries 1 to 26
E 1=V1 E 2=V2 E 3=V3 E 4=V4 E 5=V5 E 6=V1 E 7=V2 E 8=V6 E 9=V3 E10=V4
E11=V7 E12=V8 E13=V5 E14=V1 E15=V9 E16=V10 E17=V5 E18=V2 E19=V11 E20=V12
E21=V6 E22=V3 E23=V13 E24=V14 E25=V6 E26=V4
, , . . , . , , .
. .
, , , . , .
, .
Option Explicit
Type typColRule ' Definition of entry in Rules per Column table
InxRule1 As Long ' Index of first rule for this column. ) InxRule1 > InxRuleL
InxRuleL As Long ' Index of last rule for this column. ) if no rules for column
End Type
Type typRule ' Definition of Rule table
InxValue1 As Long ' Index of first permitted value for this rule
InxValueL As Long ' Index of last permitted value for this rule
InxCond1 As Long ' Index of first condition for this column. ) InxCond1 > InxCondL
InxCondL As Long ' Index of last rule for this column. ) if no rules for column
End Type
Type typCond ' Definition of Condition table
Col As Long ' Column to which this condition applies
InxValue1 As Long ' Index of first permitted value for this condition
InxValueL As Long ' Index of last permitted value for this condition
End Type
' ColRule is sized to (Min to Max) where Min is the lowest column validated
' and Max is the highest column validated. ColRule(N).InxRule1 identifies
' the first rule in Rule for column N. ColRule(N).InsRuleL identifies the
' last rule in Rule for column N.
Dim ColRule() As typColRule
' There is one entry in Rule per validation row in worksheet "Validate".
Dim Rule() As typRule
' There is one entry in ValueCell per value referenced in a permitted or
' a condition.
Dim ValueCell() As String
' There is one entry in Cond per condition in worksheet "Validate"
Dim Cond() As typCond
Sub CompileValidation()
Dim ColCodeCrnt As String
Dim ColNumCrnt As String
Dim ColValCrnt As Long
Dim ColValidateCrnt As Long
Dim ColValMin As Long
Dim ColValMax As Long
Dim ConditionCrnt As String
Dim InxCondCrnt As Long
Dim InxRuleCrnt As Long
Dim InxValueCellCrnt As Long
Dim InxValueListCrnt As Long
Dim NumCond As Long
Dim NumValue As Long
Dim PermittedCrnt As String
Dim PosEqual As Long
Dim RowValidateCrnt As Long
Dim ValueList() As String
With Worksheets("Validate")
' Determine the size of the arrays to which information will be
' compiled. Find
' * The minimum and maximum columns subject to validated
' * Number of conditions
' * Number of values references
' This routine does not allow for blank rows or columns in the
' middle of worksheet "Validate".
ColValMin = -1
ColValMax = -1
NumCond = 0
NumValue = 0
RowValidateCrnt = 2
Do While True
PermittedCrnt = .Cells(RowValidateCrnt, 1).Value
If PermittedCrnt = "" Then
Exit Do
End If
PosEqual = InStr(1, PermittedCrnt, "=")
Debug.Assert PosEqual > 1
' Determine range of columns validated
ColCodeCrnt = Mid(PermittedCrnt, 1, PosEqual - 1)
ColNumCrnt = Range(ColCodeCrnt & "1").Column
If ColValMin = -1 Then
ColValMin = ColNumCrnt
ElseIf ColValMin > ColNumCrnt Then
ColValMin = ColNumCrnt
End If
If ColValMax = -1 Then
ColValMax = ColNumCrnt
ElseIf ColValMax < ColNumCrnt Then
ColValMax = ColNumCrnt
End If
' Determine number of conditions and number of values
ValueList = Split(Mid(PermittedCrnt, PosEqual + 1), "|")
NumValue = NumValue + UBound(ValueList) - LBound(ValueList) + 1
ColValidateCrnt = 2
Do While True
ConditionCrnt = .Cells(RowValidateCrnt, ColValidateCrnt).Value
If ConditionCrnt = "" Then
Exit Do
End If
PosEqual = InStr(1, ConditionCrnt, "=")
Debug.Assert PosEqual > 1
ValueList = Split(Mid(ConditionCrnt, PosEqual + 1), "|")
NumValue = NumValue + UBound(ValueList) - LBound(ValueList) + 1
ColValidateCrnt = ColValidateCrnt + 1
Loop
NumCond = NumCond + ColValidateCrnt - 2
RowValidateCrnt = RowValidateCrnt + 1
Loop
' Size arrays
ReDim ColRule(ColValMin To ColValMax)
ReDim Rule(1 To RowValidateCrnt - 2)
ReDim ValueCell(1 To NumValue)
ReDim Cond(1 To NumCond)
InxRuleCrnt = 0
InxValueCellCrnt = 0
InxCondCrnt = 0
' Extract rules in column number order
For ColValCrnt = ColValMin To ColValMax
' The first rule for this column, if any, will be the
' next entry in the Rule table
ColRule(ColValCrnt).InxRule1 = InxRuleCrnt + 1
' If there are no rules for this column, the last rule index
' will be less than the first rule undex
ColRule(ColValCrnt).InxRuleL = InxRuleCrnt
RowValidateCrnt = 2
Do While True
PermittedCrnt = .Cells(RowValidateCrnt, 1).Value
If PermittedCrnt = "" Then
Exit Do
End If
PosEqual = InStr(1, PermittedCrnt, "=")
ColCodeCrnt = Mid(PermittedCrnt, 1, PosEqual - 1)
ColNumCrnt = Range(ColCodeCrnt & "1").Column
If ColNumCrnt = ColValCrnt Then
' This rule is for the current column
InxRuleCrnt = InxRuleCrnt + 1
' This could be the last rule for this column so
' store its index against the column
ColRule(ColValCrnt).InxRuleL = InxRuleCrnt
' The first value for this rule will be the next
' entry in the Value table
Rule(InxRuleCrnt).InxValue1 = InxValueCellCrnt + 1
ValueList = Split(Mid(PermittedCrnt, PosEqual + 1), "|")
' Save each permitted value in the Value table
For InxValueListCrnt = LBound(ValueList) To UBound(ValueList)
InxValueCellCrnt = InxValueCellCrnt + 1
ValueCell(InxValueCellCrnt) = ValueList(InxValueListCrnt)
Next
' Record the index of the last permitted value for this rule
Rule(InxRuleCrnt).InxValueL = InxValueCellCrnt
' The first condition for this rule, if any, will be the next
' entry in the Condition table
Rule(InxRuleCrnt).InxCond1 = InxCondCrnt + 1
' If there are no conditions for this rule, the last condition
' index will be less than the first condition undex
Rule(InxRuleCrnt).InxCondL = InxCondCrnt
ColValidateCrnt = 2
Do While True
ConditionCrnt = .Cells(RowValidateCrnt, ColValidateCrnt).Value
If ConditionCrnt = "" Then
Exit Do
End If
InxCondCrnt = InxCondCrnt + 1
PosEqual = InStr(1, ConditionCrnt, "=")
ColCodeCrnt = Mid(ConditionCrnt, 1, PosEqual - 1)
ColNumCrnt = Range(ColCodeCrnt & "1").Column
' Store the column for this condition
Cond(InxCondCrnt).Col = ColNumCrnt
' The first value for this condition will be the next
' entry in the Value table
Cond(InxCondCrnt).InxValue1 = InxValueCellCrnt + 1
ValueList = Split(Mid(ConditionCrnt, PosEqual + 1), "|")
For InxValueListCrnt = LBound(ValueList) To UBound(ValueList)
InxValueCellCrnt = InxValueCellCrnt + 1
ValueCell(InxValueCellCrnt) = ValueList(InxValueListCrnt)
Next
' Record last value for this condition
Cond(InxCondCrnt).InxValueL = InxValueCellCrnt
ColValidateCrnt = ColValidateCrnt + 1
Loop
' Record last condition for this rule
Rule(InxRuleCrnt).InxCondL = InxCondCrnt
End If
RowValidateCrnt = RowValidateCrnt + 1
Loop
Next
End With
Debug.Print " Rules per Column table"
Debug.Print " C RR RR"
For ColValCrnt = ColValMin To ColValMax
Debug.Print " " & ColValCrnt & " " & _
Right(" " & ColRule(ColValCrnt).InxRule1, 2) & " " & _
Right(" " & ColRule(ColValCrnt).InxRuleL, 2)
Next
Debug.Print
Debug.Print " Rule table"
Debug.Print " I VV VV CC CC"
For InxRuleCrnt = 1 To UBound(Rule)
Debug.Print " " & InxRuleCrnt & " " & _
Right(" " & Rule(InxRuleCrnt).InxValue1, 2) & " " & _
Right(" " & Rule(InxRuleCrnt).InxValueL, 2) & " " & _
Right(" " & Rule(InxRuleCrnt).InxCond1, 2) & " " & _
Right(" " & Rule(InxRuleCrnt).InxCondL, 2) & " "
Next
Debug.Print
Debug.Print " Condition table"
Debug.Print " I C VV VV"
For InxCondCrnt = 1 To UBound(Cond)
Debug.Print " " & Right(" " & InxCondCrnt, 2) & " " & _
Cond(InxCondCrnt).Col & " " & _
Right(" " & Cond(InxCondCrnt).InxValue1, 2) & " " & _
Right(" " & Cond(InxCondCrnt).InxValueL, 2)
Next
Debug.Print
Debug.Print " Value table"
Debug.Print " ";
For InxValueCellCrnt = 1 To UBound(ValueCell)
Debug.Print "E" & Right(" " & InxValueCellCrnt, 2) & "=" & _
Left(ValueCell(InxValueCellCrnt) & " ", 5);
If (InxValueCellCrnt Mod 10) = 0 Then
Debug.Print
Debug.Print " ";
End If
Next
Sub