VBA function to identify correct assembly in Truth Table

Question

I am trying to write a VBA function that solves for the correct assembly by identifying if the model digits in a row are true when compared to a model number. There are multiple sheets. Each sheet is an "assembly family".

The model number is 18 digits long, and each sheet solves based on different model digits.. If part of a column is blank, that means it is only evaluated if there is data in the cell, otherwise it is "Tru".

Here are a few valid model numbers:

CMP24FDMCABHNN05NN

SMG24BDFLCSXNA8216

CMD24BDMEASHSN82N2

The raw data from two sheets is below.

Column Desc	Product Type	Unit Config	Nominal Capacity	Generation	Ventilation Configuration	Case Construction	# TRU
Digit #	1,2	3	4,5	7	9	11	1
Part #	C_005_PT	C_005_UC	C_005_MBH	C_005_G	C_005_VC	C_005_CC	Evaluate
3H2000640116	CM	D,P	24,36	D	A,B,C,E,Q	B	F
3H2000640120	CM	D,P	24,36	D	A,B,C,E,Q	A	F
3H2000640120	CM	D,P	24,36	D	A,B,C,E,Q	S	TRU
3H2000640216	CM,SM	G,W,S	24,36	D	F,G,H,K,L,M,P,R,S	B	F
3H2000640220	CM,SM	G,W,S	24,36	D	F,G,H,K,L,M,P,R,S	A	F
3H2000640220	CM,SM	G,W,S	24,36	D	F,G,H,K,L,M,P,R,S	S	F
3H2000640316	CM,SM	G,W,S	24,36	D	A,B,Q	B	F
3H2000640320	CM,SM	G,W,S	24,36	D	A,B,Q	A	F
3H2000640320	CM,SM	G,W,S	24,36	D	A,B,Q	S	F
3H2000650116	CM	D,P	48	D	A,B,C,E,Q	B	F
3H2000650120	CM	D,P	48	D	A,B,C,E,Q	A	F
3H2000650216	CM,SM	G,W,S	48,60	D	F,G,H,K,L,M,P,R,S	B	F
3H2000650220	CM,SM	G,W,S	48,60	D	F,G,H,K,L,M,P,R,S	A	F
3H2000650316	CM,SM	G,W,S	48,60	D	A,B,Q	B	F
3H2000650320	CM,SM	G,W,S	48,60	D	A,B,Q	A	F
3H2000660120	CM	D,P	48	D	A,B,C,E,Q	S	F
3H2000660220	CM,SM	G,W,S	48,60	D	F,G,H,K,L,M,P,R,S	S	F
3H2000660320	CM,SM	G,W,S	48,60	D	A,B,Q	S	F
3H2000670116	CM	D,P	60	D	A,B,C,E,Q	B	F
3H2000670120	CM	D,P	60	D	A,B,C,E,Q	A	F
3H2000680120	CM	D,P	60	D	A,B,C,E,Q	S	F

Column Desc	Product Type	Unit Config	Nominal Capacity	Generation	HGRH	Coaxial Coil Option	# TRU
Digit #	1,2	3	4,5	7	14	17	1
Part #	C_005_PT	C_005_UC	C_005_MBH	C_005_G	C_005_HG	C_005_CO	Evaluate
3H2000762400	CM	D	24	D	N,C		TRU
3H2000763600	CM	D	36	D	N,C		F
3H2000764800	CM	D	48	D	N,C		F
3H2000762410	CM	D	24	D	A,B		F
3H2000763610	CM	D	36	D	A,B		F
3H2000764810	CM	D	48	D	A,B		F
3H2000772400	CM	P	24	D	N,C		F
3H2000773600	CM	P	36	D	N,C		F
3H2000774800	CM	P	48	D	N,C		F
3H2000772410	CM	P	24	D	A,B		F
3H2000773610	CM	P	36	D	A,B		F
3H2000774810	CM	P	48	D	A,B		F
3H2000752401	CM	S	24	D	N,C	T	F
3H2000753601	CM	S	36	D	N,C	T	F
3H2000754801	CM	S	48	D	N,C	T	F
3H2000756001	CM	S	60	D	N,C	T	F
3H2000766000	CM	D	60	D	N,C		F
3H2000766010	CM	D	60	D	A,B		F
3H2000776000	CM	P	60	D	N,C		F

Currently, I solve for this manually with this tricky equation that is horrible to maintain.

=IF(AND(MID(Test!$S$2,1,2)=B61,
        OR(MID(Test!$S$2,3,1)=MID(C61,1,1),
           MID(Test!$S$2,3,1)=MID(C61,3,1),C61=""),
           MID(Test!$S$2,4,2)=D61,
           OR(MID(Test!$S$2,7,1)=MID(E61,1,1),
              MID(Test!$S$2,7,1)=MID(E61,3,1),E61=""),
        OR(MID(Test!$S$2,14,1)=MID(F61,1,1),
           MID(Test!$S$2,14,1)=MID(F61,3,1),
           MID(Test!$S$2,14,1)=MID(F61,5,1),
           MID(Test!$S$2,14,1)=MID(F61,7,1)),
        OR(MID(Test!$S$2,17,1)=G61,G61="")),"TRU","F")

The above function is an IF AND OR evaluator. It reads each cell and compares it to a model number on another sheet. The extra OR statements are used to parse cells that have multiple values that could be true. "CM,SM".

The expected output is simply if the assembly number is true when compared to the model number. There should only one true per sheet.

ideally, I turn this into a recursive function, but I am having trouble writing it without hardcoding the digits into the script

Answer 1

• The 1st argument: model number (18 chars)
• The 2nd argument: the range of model data (6 cells)
• UDF returns "!Err" if the argument is invalid.

Option Explicit

Function ValModel(ByVal sMod As String, Rng As Range) As String
    Dim arr, aTxt(1 To 6) As String, i As Long
    Const COMMA = ","
    Application.Volatile
    If Rng.Count <> 6 Or Rng.Rows.Count > 1 Or Len(sMod) <> 18 Then
        ValModel = "!Err"
    Else
        ValModel = "F"
        arr = Rng.Value
        aTxt(1) = COMMA & Left(sMod, 2) & COMMA
        aTxt(2) = COMMA & Mid(sMod, 3, 1) & COMMA
        aTxt(3) = COMMA & Mid(sMod, 4, 2) & COMMA
        aTxt(4) = COMMA & Mid(sMod, 7, 1) & COMMA
        aTxt(5) = COMMA & Mid(sMod, 9, 1) & COMMA
        aTxt(6) = COMMA & Mid(sMod, 11, 1) & COMMA
        For i = 1 To 6
            arr(1, i) = COMMA & arr(1, i) & COMMA
            If InStr(1, arr(1, i), aTxt(i), vbTextCompare) = 0 Then
                Exit Function
            End If
        Next
        ValModel = "Tru"
    End If
End Function

---

• The Digit # row is different on sheets. The 3rd argument is introduced.

Note: It supports the dynamic configuration of Digit #. But its performance is worse than the first UDF.

Function ValModel2(ByVal sMod As String, ProdRng As Range, ParaRng As Range) As String
    Dim arrProd, arrPara, aTxt() As String, i As Long, iLen As Long, aPara
    Const COMMA = ","
    Application.Volatile
    If ParaRng.Rows.Count > 1 Or ProdRng.Rows.Count > 1 Or _
            Len(sMod) <> 18 Or ParaRng.Count <> ProdRng.Count Then
        ValModel2 = "!Err"
    Else
        ValModel2 = "F"
        arrProd = ProdRng.Value
        arrPara = ParaRng.Value
        ReDim aTxt(1 To ParaRng.Count)
        For i = 1 To UBound(aTxt)
            aPara = Split(arrPara(1, i), ",")
            If UBound(aPara) = 0 Then
                iLen = 1
            Else
                iLen = CInt(aPara(UBound(aPara))) - CInt(aPara(0)) + 1
            End If
            aTxt(i) = COMMA & Mid(sMod, CInt(aPara(0)), iLen) & COMMA
        Next
        For i = 1 To 6
            If Len(arrProd(1, i)) > 0 Then
                arrProd(1, i) = COMMA & arrProd(1, i) & COMMA
                If InStr(1, arrProd(1, i), aTxt(i), vbTextCompare) = 0 Then
                    Exit Function
                End If
            End If
        Next
        ValModel2 = "Tru"
    End If
End Function