# 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:

test:

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

``````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

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.