A Sub/Function to cause another Sub/Function to Exit

G

Guest

I have a Sub that dumps information into a worksheet. When a certain amount a
data is dumped, the routine makes a call to another sub to find a clear cell
and then it continues. My range is only from $L$19 to $L$45 and I would like
it if I could use the "new cell" routine to force the termination of the data
dump routine when the range has been exceeded. Possible?

Here's the "new cell" code:

Sub newCell()
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name <> "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
If (whiteSpace.test(ActiveCell.value)) Then
Exit For
ElseIf curCell > 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
 
G

Guest

Perhaps something like this:

Sub DumpData()

' existing code
if not newCell then exit sub
' existing code

End sub


Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name <> "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
End If
Next
NewCell = True
End Function
 
Z

Zone

IT, make newCell a Function so it can return a value to the calling routine.
James

Function newCell() as Boolean
newCell=True

<snip>

ElseIf curCell > 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function
 
G

Guest

Tom Ogilvy said:
Perhaps something like this:

Sub DumpData()

' existing code
if not newCell then exit sub
' existing code

End sub


Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name <> "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
End If
Next
NewCell = True
End Function
Click to expand...

Thank you for the suggestion. I will try it. One question: doesn't the
"NewCell = True" statement at the end override the "NewCell = False"
statement in the "ElseIf curCell = 45" - or is that just to reset "NewCell"
when the For loop is over?
 
G

Guest

Guess I didn't finish putting in the exit function statements:

Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name <> "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
Exit Function
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
Exit Function
End If
Next
NewCell = True
End Function

With the Exit functions added, it should never reach that statement.
 
G

Guest

Zone said:
IT, make newCell a Function so it can return a value to the calling routine.
James

Function newCell() as Boolean
newCell=True

<snip>

ElseIf curCell > 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function
Click to expand...

Thanks Guys for the suggestions.

I noticed Tom put "newCell = True" after the For loop and Zone/James put it
ahead of the For loop. Do I have to declare newcell = True/False outside of
the For loop at all?

Tom: I added in an "Exit For" (in code below) to get it to stop cycling
through the cells to cell L45. There's a locked cell @L46 and it generated an
error about accessing locked cells. Otherwise your code worked perfect.
Thanks!

Zone/James: I still don't quite get the difference between a function and a
sub, but i guess that's part of learning! Maybe you could shed some light?

(code)
If (whiteSpace.test(ActiveCell.value)) Then
newCell = True
Exit For '<======[ THIS ]
 
G

Guest

Tom Ogilvy said:
Guess I didn't finish putting in the exit function statements:

Function newCell() as Boolean
Dim whiteSpace As Object
Dim curCell As Integer

Set whiteSpace = CreateObject("VBScript.RegExp")
whiteSpace.IgnoreCase = True
whiteSpace.Pattern = "^\s*$"

If ActiveSheet.name <> "myForm" Then Sheet2.Activate
If Not ActiveCell.Address = "$L$19" Then Range("L19").Select

For curCell = 19 To 45
cells(curCell,"L").Select
If (whiteSpace.test(ActiveCell.value)) Then
NewCell = True
Exit Function
ElseIf curCell = 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell = False
Exit Function
End If
Next
NewCell = True
End Function

With the Exit functions added, it should never reach that statement.
Click to expand...
Ah, ok. When I got the error (see reply to "Zone") I noticed there wasn't an
exit statement so I put it in.

Thanks again, Tom! You rock. :)
 
Z

Zone

IT,
I like to set the function's return value at the top if possible so that I'm
sure what state the function starts out with. But you could do it either my
way or Tom's way. Either way, you do need to set the function's value to
something in any case. In my test, if you never set the function's return
value, it just returns False. The difference between a sub and a function
is really very simple. A function can return a value. A sub cannot. James
IT_roofer said:
Zone said:
IT, make newCell a Function so it can return a value to the calling
routine.
James

Function newCell() as Boolean
newCell=True

<snip>

ElseIf curCell > 45 Then
MsgBox "No more line entries available." & _
Chr(13) & "Please combine your entries and resubmit."
NewCell=False
Exit For
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Function
Click to expand...

Thanks Guys for the suggestions.

I noticed Tom put "newCell = True" after the For loop and Zone/James put
it
ahead of the For loop. Do I have to declare newcell = True/False outside
of
the For loop at all?

Tom: I added in an "Exit For" (in code below) to get it to stop cycling
through the cells to cell L45. There's a locked cell @L46 and it generated
an
error about accessing locked cells. Otherwise your code worked perfect.
Thanks!

Zone/James: I still don't quite get the difference between a function and
a
sub, but i guess that's part of learning! Maybe you could shed some light?

(code)
If (whiteSpace.test(ActiveCell.value)) Then
newCell = True
Exit For '<======[ THIS ]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If ActiveCell has a comment then exit sub 22
Putting a value in cell based on another cell 4
Convert Function to Sub 6
How To Determine Length Of Variable-Font Text? 9
Modify sub to exit if password is incorrect 2
Array coding type mismatch 6
exit for loop 2
Returning vars to calling sub 4

Top