Nesting limits in VBA
 

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.

Nesting limits in VBA
 

true of FORMULAS but there are lots of workarounds


"PK" wrote in message
...
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.

Nesting limits in VBA
 

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

--
Regards,
Tom Ogilvy

"PK" wrote in message
...
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.

Nesting limits in VBA
 

PK,

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

Dan E

"PK" wrote in message ...
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.

Nesting limits in VBA
 

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"PK" wrote in message
...
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.

Nesting limits in VBA
 

Hi Tom,

In article ,
says...
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.


Alternatively, just reverse the tests...

If i15 then
ElseIf i 14 then
ElseIf i 13 then
....
elseif i1 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

Nesting limits in VBA
 

Maybe I should have used the generic

If criteria1 and criteria2 and criteria3 and criteria4 then


End if

--
Regards,
Tom Ogilvy

Tushar Mehta wrote in message
om...
Hi Tom,

In article ,
says...
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.


Alternatively, just reverse the tests...

If i15 then
ElseIf i 14 then
ElseIf i 13 then
...
elseif i1 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

Nesting limits in VBA
 

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

In article ,
says...
Maybe I should have used the generic

If criteria1 and criteria2 and criteria3 and criteria4 then


End if

--
Regards,
Tom Ogilvy

Tushar Mehta wrote in message
om...
Hi Tom,

In article ,
says...
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.


Alternatively, just reverse the tests...

If i15 then
ElseIf i 14 then
ElseIf i 13 then
...
elseif i1 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

Nesting limits in VBA
 

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"

--
Regards,
Tom Ogilvy



Tushar Mehta wrote in message
news:MPG.19f3aa7ddd3524869896b5@news-server...
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

In article ,
says...
Maybe I should have used the generic

If criteria1 and criteria2 and criteria3 and criteria4 then


End if

--
Regards,
Tom Ogilvy

Tushar Mehta wrote in message
om...
Hi Tom,

In article ,
says...
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.


Alternatively, just reverse the tests...

If i15 then
ElseIf i 14 then
ElseIf i 13 then
...
elseif i1 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