| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested If's vs. GoTo statements
I have a Worksheet_Change sub that originally had several levels of If
statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
How many Ifs are we talking about here. Generally, if it is more than
3, I tend to use Select Case. Horatio J. Bilge, Jr. wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
Instead of the GoTo, you could call subroutines. It would basically do the
same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
Would that look this this?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of code here... End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of different code here... End Sub etc. "mark" wrote: Instead of the GoTo, you could call subroutines. It would basically do the same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
No. What Mark is talking about would look like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If condition1=True Then macro1 ElseIf condition2=True Then macro2 Else macro3 End If End Sub Sub macro1() 'code here End Sub Sub macro2() 'code here End Sub Sub macro3() 'code here End Sub Horatio J. Bilge, Jr. wrote: Would that look this this? Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of code here... End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of different code here... End Sub etc. "mark" wrote: Instead of the GoTo, you could call subroutines. It would basically do the same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
You get one Worksheet_change event per worksheet.
So you could use something like: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'on cell at a time if target.cells.count 1 then exit sub if not (intersect(target, me.range("a1:x99")) is nothing then 'do the code for a1:x99 elseif not(intersect(target,me.range("q107:z1233")) is nothing then 'do the code for q107:z1233 end if End Sub If your ranges overlap or you want to check a multicelled target, you wouldn't use this. Horatio J. Bilge, Jr. wrote: Would that look this this? Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of code here... End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of different code here... End Sub etc. "mark" wrote: Instead of the GoTo, you could call subroutines. It would basically do the same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
I like the looks of this, but it is causing errors. I copied the working code
that I had into each sub macro, but there must be something wrong. On the error, when I click "Debug" this is the line that is highlighted (each sub macro starts with that line): If Target.Cells.Count 1 Then Exit Sub End If "JW" wrote: No. What Mark is talking about would look like this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If condition1=True Then macro1 ElseIf condition2=True Then macro2 Else macro3 End If End Sub Sub macro1() 'code here End Sub Sub macro2() 'code here End Sub Sub macro3() 'code here End Sub Horatio J. Bilge, Jr. wrote: Would that look this this? Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of code here... End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of different code here... End Sub etc. "mark" wrote: Instead of the GoTo, you could call subroutines. It would basically do the same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested If's vs. GoTo statements
I didn't see your response earlier. I was trying to figure out calling sub
macros. I really liked how it made the code easy to read, but I kept getting errors about arguments not being optional. Your suggestion was much simpler for my novice programming skills to figure out, and it worked without having to troubleshoot anything. Thanks, ~ Horatio "Dave Peterson" wrote: You get one Worksheet_change event per worksheet. So you could use something like: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'on cell at a time if target.cells.count 1 then exit sub if not (intersect(target, me.range("a1:x99")) is nothing then 'do the code for a1:x99 elseif not(intersect(target,me.range("q107:z1233")) is nothing then 'do the code for q107:z1233 end if End Sub If your ranges overlap or you want to check a multicelled target, you wouldn't use this. Horatio J. Bilge, Jr. wrote: Would that look this this? Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of code here... End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'a bunch of different code here... End Sub etc. "mark" wrote: Instead of the GoTo, you could call subroutines. It would basically do the same thing, in the end.... but it makes the code more modular, and therefore easier to read. If you ever find out you don't need a certain piece, just get ride of that subroutine, or if you need a new piece, just add a new subroutine. "Horatio J. Bilge, Jr." wrote: I have a Worksheet_Change sub that originally had several levels of If statements to do different things with different ranges ("If Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...). It was making my head hurt to keep all of them straight, so I switched to using GoTo statements, so I could separate the code for different ranges. Then I read that using multiple GoTo statements is frowned upon as bad practice, because it makes the code difficult to read. I wanted to get the opinions of some experience programmers. Any thoughts? ~ Horatio -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested If's | Excel Worksheet Functions | |||
| Nested If's | Excel Worksheet Functions | |||
| How many nested IF's??? | Excel Discussion (Misc queries) | |||
| Sorry about the last ones...And statements with IF's | Excel Programming | |||
| NESTED IF's too many | Excel Programming | |||
Linear Mode
