VBA function to identify correct assembly in Truth Table

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.

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

• 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