Counting Nested If Statements

[ExcelMonkey]

Can anyone tell me how to count nested if statements in a formula via VBA.
That is I want to be able to go into a cell and assess that the formula below
has three IF stmts

=IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))

This may be a Regular Expression question.

Thanks

[NickHK]

ExcelMonkey,
How about:

Public Function CountFunction(TargetCell As Range, FunctionName As String)
As Long
Dim Temp As Variant
Temp = Split(UCase(TargetCell.Formula), UCase(FunctionName) & "(")
CountFunction = UBound(Temp)
If CountFunction < 1 Then CountFunction = 0
End Function

Unless you have other instance(s) of "if(" in the formula.

NickHK

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> Can anyone tell me how to count nested if statements in a formula via VBA.
> That is I want to be able to go into a cell and assess that the formula
below
> has three IF stmts
>
> =IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))
>
> This may be a Regular Expression question.
>
> Thanks

[Paul Martin]

Just as a point of interest, your example formula has two nested IF
statements (two are nested within another IF statement). You can have
up to seven nested within another IF statement (total of 8 IFs).

Regards

Paul Martin
Melbourne, Australia

[Dana DeLouis]

Hi. This small demo returns 3. Not sure if this covers all situations
though.

Sub Demo()
Dim s, t
Dim n As Long

'// Put formula in B1
[B1].Formula = "=IF(A1=1,10,IF(A1=2,20, IF(A1=3,30)))"

'// Then...
s = [B1].Formula
t = Replace(s, "IF(", vbNullString)
n = (Len(s) - Len(t)) / 3
Debug.Print n
End Sub

HTH
--
Dana DeLouis
Win XP & Office 2003


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> Can anyone tell me how to count nested if statements in a formula via VBA.
> That is I want to be able to go into a cell and assess that the formula
> below
> has three IF stmts
>
> =IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))
>
> This may be a Regular Expression question.
>
> Thanks