Solved

Exit Main Sub

Posted on 2011-09-07
31
404 Views
Last Modified: 2012-05-12
i've a worksheet change event in which there'r many sub routines, one of the sub routine is a loop, if a condition is not met in the sub routine code, i want to exit that particular loop. i use exit sub , but it only exits that sub routine and not the loop.
0
Comment
Question by:NYQuicksale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 4
  • +2
31 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36497121
What kind of loop is it?

Exit For?

Exit Do?
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497203
the main loop A contain another loopB
Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub

Open in new window


in this LoopB, there's subroutine of worksheet change event.in that worksheet change , if a condition is not met,then  i want to exit loopB.
i use exit sub, then it exits that worksheet change, while i want to exit loop B
0
 
LVL 12

Expert Comment

by:rlandquist
ID: 36497327
You can use the Exit Do statement.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497397
the loop is not in the code, the loop is separate and the other code is in a worksheet change, how can i use exit do, when there's no loop here
0
 
LVL 12

Expert Comment

by:rlandquist
ID: 36497428
If I understand you correctly, you would have to set a value in the sub that the loop checks for and if it is there, exit the loop.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497457
Dim dcell As Range, cellbelowd As Range
            Set dcell = Worksheets("sheet1").Cells.Find("Document", , xlValues, xlWhole, , , False)
            If Not dcell Is Nothing Then
                If dcell.Value = "" Then
            With Sheets("info output")
            With .Cells(.Rows.Count, 8).End(xlUp).Offset(12)
                .Offset(, 1).Value = Sheets("info").Range("A1").Value
            End With
            End With
            Call finfo
            Exit Sub

Open in new window



instead of exit sub, i want to exit loopB, and loopB is in a module, while this code is in worksheet change
0
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 36497677
Define a global Boolean variable. Set it where the error occurs and check for it in the loop.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497743
there's no error, and will u plz be so kind to explain where to put the global bolean and what actually that is
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36497777
I do VB6 so perhaps this doesn't apply, but in VB6 I would put

Public gbConditionMet As Boolean

in the module and then where the "condition is not met" do

gbConditionMet = False

You can then say

If not gbConditionMet Then...
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497849
yes and perhaps i cant get any help today
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36497882
What do you mean when you say "yes and perhaps i cant get any help today". I'm particularly asking about the "yes" part.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497901
I do VB6 so perhaps this doesn't apply
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498037
Interesting attitude for one needing help.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36498079
i didn't mean to show an attitude, if it seemed so, i'm sorry, i never intended
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498140
Well you have your answer, so I'm not really sure what your last couple of posts meant.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36498656
@rorya: Should NYQuicksale be able to do what I suggested (global variable) in post ID 36497777?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498786
Yes - that should work fine.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36498833
@NYQuicksale: So given rorya's answer to my question, what  didn't work for you?
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36502554
Mr.Martin in your Post ID: 36497777, i understood nothing, where to put it and where not,

Public gbConditionMet As Boolean

in the module and then where the "condition is not met" do

gbConditionMet = False

You can then say

If not gbConditionMet Then...

as in my above post ID: 36497743 i have already requested u to explain what this means and where to use it, as i no nothing about global boolean.
i've also given u the part of the code, if u can explain, i would appreciate that

how to use it in my  above sub, will u be so considerate to explain
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36502688
1. Click the 'Insert' menu item and then select Module.

2. At the top of the module add Public gbConditionMet As Boolean. (A boolean variable has two values which are True and False)

3. You mention a condition that is not being met. In the code where you discover that the condition is not being met, add the line gbConditionMet = False

4. Then I assume you want to exit LoopB so change that code to

Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
If gbConditionNotMet Then Exit Sub 'Add this line
mySheet.Range("A1") = cel.Value
Next cel
End Sub
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36502824
that's what i'm saying, the condition not met line of code is not in module, its in the sheet, i've a worksheet change event, in that worksheet change event code, there's a line of code, where condition is not met, its not in a module, when a condition is not met here, i want to exit loopb, which is in module, can i use global boolean in a sheet rather than module?
0
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36502869
Okay, one last try…

A variable that is in a code module (see my steps 1 and 2) can be referred to from anyplace in your project, so you can set it to False whenever and wherever you find the condition not being met (see my step 3) and check for the value of the boolean variable being False wherever you need to.
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503225
In an Inserted Module of your excel document -
Right Click in Project Window  - Select Insert - Select Module...

Global bExitNow As Boolean

Modify your existing Code:

Sub loopB()
    Dim cel As Range
    Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet1")
    bExitNow = false	
    For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
        mySheet.Range("A1") = cel.Value
        if bExitNow then Exit For
    Next cel

End Sub



' your worksheet change code

Private Sub Worksheet_Change(ByVal Target As Range)
            ' your code here
            
            ' perhaps some sort of Loop
            
            If Condition Then
               bExitNow = True
               Exit Sub
            End If
            
            ' other code to run if you do not exit
            
End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503231
If that works for you then please assign pts to MartinLiss :)
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36503497
if i do not exit, meaning if the condition is false, dont u think i should use else, i mean something to differentiate after i use exit sub line

 i should use my rest of the code without using else?? if i do so, its exiting the loop in other condition as well
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36503567
Can you show me the code where you think there should be an 'Else'?
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503589
I do not know what code you have in WorkSheet_Change so I do not know where you want to check and exit the sub.

an Else may not be necessary here unless you want to do several things even if the condition is true.
If you want to leave as soon as the condition is True then you do not need an else.

the (' perhaps some sort of loop) is only indicating that as you mentioned earlier you had two loops...
if that is the case..


Private Sub Worksheet_Change(ByVal Target As Range)
            ' your code here
            
            For Each of These in Those
               If Condition Then
                  bExitNow = True
                  Exit Sub
               End If
            
            Next

            ' other code to run if you do not exit
            
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36503631
u've been so considerating Brook, thanks for all the help
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503684
I appreciate your comments but would rather that you award Martin as all I did was re-state his response which was the correct answer.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 36503760
Thanks Brook.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question