vba – How can I extract the logical_test from an if statement in excel?

vba – How can I extract the logical_test from an if statement in excel?

If have clear understanding of what you asking for, then you can use something like this (shall be used only with IF() statement :

Function extrIf(ByVal ifstatement As Range) As String

Dim S$, sRev$, x%, k
S = Replace(Replace(ifstatement.Formula, IF(, ), ),, )|)

sRev = StrReverse(S)

If InStr(1, sRev, |) > InStr(1, sRev, ) Or InStr(1, sRev, |) = 0 Then
    x = InStr(1, StrReverse(Left(sRev, InStr(1, sRev, ))), ,) - 1
    S = Mid(S, 1, Len(S) - InStr(1, sRev, ) + x) & |
End If

sRev = 

For Each k In Split(S, |)
    If k <>  Then
        If k Like ** Then
            sRev = sRev & ,  & Mid(k, InStr(1, k, ) + 1, 999)
        End If
    End If
Next

extrIf = Mid(sRev, 3, 999)

End Function

example:

enter

test:

enter

Maybe this is not complete solution for you, but I think it might give you right direction.

If the cell formula starts with an If statement then you can return the logic test (starting after the first open parenthesis) by determining the position of the first comma where the sum of the previous open parenthesis – the sum previous closed = 0.

Formulas

enter

Function ExtractIfTest(Target As Range) As String
    Dim ch As String, s As String
    Dim openP  As Long
    Dim x As Long
    s = Target.formula

    For x = 5 To Len(s)
        ch = Mid(s, x, 1)
        If Mid(s, x, 1) = ( Then
            openP = openP + 1
        ElseIf Mid(s, x, 1) = ) Then
            openP = openP - 1
        ElseIf Mid(s, x, 1) = , And openP = 0 Then
            ExtractIfTest = Mid(s, 5, x - 12)
        End If
    Next
End Function

Results

enter

There might be instances where the is a comma without parenthesis A1,B1. If this happens simple escape them with parenthesis (A1,B1)

vba – How can I extract the logical_test from an if statement in excel?

Ive written an UDF that extract any of the parameters of the target formula. Its close to the one in Thomas answer, but more global and takes into account strings that can enclose commas or parenthesis.

Function ExtractFormulaParameter(Target As Range, Optional Position As Long = 1) As Variant
    Dim inString As Boolean
    Dim formula As String
    Dim st As Long, sp As Long, i As Long, c As String
    Dim parenthesis As Long, comma As Long
    formula = Target.formula
    st = 0: sp = 0
    If Position <= 0 Then ExtractFormulaParameter = CVErr(xlErrValue): Exit Function

    For i = 1 To Len(formula)
        c = Mid$(formula, i, 1)
        If inString Then
            If c =  Then
                inString = False
            End If
        Else
            Select Case c
            Case 
                inString = True
            Case (
                parenthesis = parenthesis + 1
                If parenthesis = 1 And Position = 1 Then
                    st = i + 1
                End If
            Case )
                parenthesis = parenthesis - 1
                If parenthesis = 0 And sp = 0 Then sp = i: Exit For
            Case ,
                If parenthesis = 1 Then
                    comma = comma + 1
                    If Position = 1 And comma = 1 Then sp = i: Exit For
                    If Position > 1 And comma = Position - 1 Then st = i + 1
                    If Position > 1 And comma = Position Then sp = i: Exit For
                End If
            Case Else
            End Select
        End If
    Next i

    If st = 0 Or sp = 0 Then
        ExtractFormulaParameter = CVErr(xlErrNA)
    Else
        ExtractFormulaParameter = Mid$(formula, st, sp - st)
    End If
End Function

By default it returns the first parameter, but you can also return the second or the third, and it should work with any formula.

Leave a Reply

Your email address will not be published. Required fields are marked *