Nesting limits in VBA

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

Hello, I hope someone can help me...

I have a matching subroutine in which there are nine
criteria that need to be checked in order to complete a
match. Therefore, the code is nested nine layers deep,
using mostly "IFs" but also two loops.

I am pretty experienced at successfully setting these
types of things up, but never this deep. Although I see no
coding problems, it doesn't match everything in my arrays.

I remember years ago reading that the maximum nesting was
6 layers. Is this still true? Could this be my issue?

Any suggestions on making my code flatter would be
appreciated.

Thanks in advance for your assistance.
 
I don't know what the limit is, but it is more than 9

Sub Tester1()
i = 20
If 1 < i Then
If 2 < i Then
If 3 < i Then
If 4 < i Then
If 5 < i Then
If 6 < i Then
If 7 < i Then
If 8 < i Then
If 9 < i Then
If 10 < i Then
If 11 < i Then
If 12 < i Then
If 13 < i Then
If 14 < i Then
If 15 < i Then
MsgBox " 15 is less than i"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 
PK,

From VBA help:
If...Then...Else statements can be nested to as many
levels as you need

Dan E
 
how to make it flatter would depend on the criteria - if they don't have to
checked sequentially, then use the And statement

if 1< i and 2 < i and 3 < i and 4 < i and 5 < i and 6 < i then

end if

as an example.
 
Hi Tom,

how to make it flatter would depend on the criteria - if they don't have to
checked sequentially, then use the And statement

if 1< i and 2 < i and 3 < i and 4 < i and 5 < i and 6 < i then

end if

as an example.
Click to expand...
Alternatively, just reverse the tests...

If i>15 then
ElseIf i > 14 then
ElseIf i > 13 then
....
elseif i>1 then
else
end if
--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Maybe I should have used the generic

If criteria1 and criteria2 and criteria3 and criteria4 then


End if
 
Hi Tom,

My comment was not aimed at your suggestion. It was targeted at how
one might eliminate a (one-sided) bushy tree.

If i <= 3 then
If i <= 2 then
If i <= 1 then
msgbox "i <= 1"
else
msgbox "i=2"
end if
else
msgbox "i=3"
end if
else
msgbox "i > 3"
end if

could be replaced with

if i > 3 then
msgbox "i > 3"
elseif i =3 then
msgbox "i =3"
elseif i=2 then
msgbox "i=2"
else
msgbox "i <=1"
end if

or, of course, with a case statement.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
My interpretation of the original post was that the criteria are unrelated -
all criteria must be met.

You present a good solution for the special case where the criteria can be
sequentially evaluated or the criteria creates "bins"
 
Back
Top