Nested If's vs. GoTo statements

Nested If's vs. GoTo statements

Post by SG9yYXRpby » Fri, 12 Oct 2007 22:55:02


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
 
 
 

Nested If's vs. GoTo statements

Post by JW » Fri, 12 Oct 2007 23:33:33

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. < XXXX@XXXXX.COM >

 
 
 

Nested If's vs. GoTo statements

Post by bWFya » Sat, 13 Oct 2007 01:04:02

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.
 
 
 

Nested If's vs. GoTo statements

Post by SG9yYXRpby » Sat, 13 Oct 2007 01:11:00

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.
 
 
 

Nested If's vs. GoTo statements

Post by JW » Sat, 13 Oct 2007 01:46:22

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. < XXXX@XXXXX.COM >
 
 
 

Nested If's vs. GoTo statements

Post by Dave Peter » Sat, 13 Oct 2007 01:56:58

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.







--

Dave Peterson
 
 
 

Nested If's vs. GoTo statements

Post by SG9yYXRpby » Sat, 13 Oct 2007 23:14:00

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
 
 
 

Nested If's vs. GoTo statements

Post by SG9yYXRpby » Sun, 14 Oct 2007 01:37:01

fter moving things around in the code, it seems that it hangs on whatever
happens to be the first line of each sub macro. Is it a problem with the
"main" private sub? Here's what I wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not (Application.Intersect(Target, Range("Times")) Is Nothing) Then
macro_Times
ElseIf Not (Application.Intersect(Target, Range("Diving")) Is Nothing) Then
macro_Diving
ElseIf Not (Application.Intersect(Target, Range("Places")) Is Nothing) Then
macro_Places
Else
Exit Sub
End If
End Sub


"Horatio J. Bilge, Jr." wrote:

 
 
 

Nested If's vs. GoTo statements

Post by SG9yYXRpby » Wed, 17 Oct 2007 02:36:02

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