Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum, ave with limits, help pls. | Excel Discussion (Misc queries) | |||
Sum with limits | Excel Discussion (Misc queries) | |||
Row Limits | Excel Worksheet Functions | |||
Nesting level limits | Excel Worksheet Functions | |||
Limits | Excel Discussion (Misc queries) |