PDA

View Full Version : Solved: Code to stop all subsequent code and return to userform



Shortz
04-04-2010, 08:13 AM
Hi guys,

This maybe a very simple question but i currently have a form which when the "process" button is clicked it triggers this code:

Private Sub ProcessBut_Click()

Application.ScreenUpdating = False

Select Case SQForm.WG.Value

Case ""
Exit Sub

Case Else
Process.PPLevelCheck
Process.PPCheck
Process.OutSheetName
Process.OutSheetCopy
Process.Checker
Process.Queryruns

End Select

Application.ScreenUpdating = True

End Sub
most of these macros are error checking such as OutSheetName which checks if the inputted name for a new worksheet already exists.

Currently these nested macros are coded to 'Exit Sub' when certain conditions are met eg. worksheet already exists. When it does this it merely exits the particular nested macro and proceeds to the next one on the list, rather than taking me back to the form for the user to amend the error.

What is the best way to deal with this? should i just use the 'End' command?

IDEALLY what i want is that when one of the nested macros exit then it will display the message i've written in them and go back to the userform.

thanks in advance!

mdmackillop
04-04-2010, 08:32 AM
Rather than a Sub, you could make the test code a Function whick will return True/False,
Then something like this for each test

If Not Process.PPLevelCheck Then Exit Sub

SamT
04-04-2010, 08:48 AM
You could Dim a Public Flag Variable As Boolean, then set it in each sub before exiting. Be sure to reset it after reading it.

Shortz
04-04-2010, 09:07 AM
So you mean that at the end of each nested macro have

Flag = true

And in the larger macro you would have an If flag = true then proceed statement? Would you make that into a function then?

eg.

function Flagger()
dim flag as boolean
If flag = false then
exit sub
end if

but would this then just recreate the problem? or should i not make it a function and hardcode it in?

eg. Flag statement
Nested macro1
flag statement
nested macro2
etc

mdmackillop
04-04-2010, 09:23 AM
Here's a Function example

Sub test()
If Not ShExist("Test1") Then
MsgBox "does not exist"
Exit Sub
Else
MsgBox "Exists"
End If
End Sub

Function ShExist(sh) As Boolean
On Error Resume Next
ShExist = True
If Sheets(sh) Is Nothing Then ShExist = False
End Function

Shortz
04-04-2010, 06:10 PM
I have this code already embedded into one of the nested macros
eg. PPlevelcheck
and it exits that macro fine, but the rest still run.

I think i'll make a global flag variable that gets checked after each macros run. (true/false). If i went with this idea, could i make it into a function or does it need to be hard coded in this macro? (will the function just make the same problem as before??)

Could i also ask about some optimisations?
If i place the applicationscreenupdate code at the beginning of this macro, will it apply to all the nested ones?

SamT
04-04-2010, 08:59 PM
Don't need a function. The code to use with a public Boolean Variable is;

In Called Procedure:


'yada yada

'Something bad here
Flag = False
Exit Sub

'All was good, continue
'yada yada

Flag = True
End Sub


In your Master Procedure


'Yada Yada

Flag = False
Call SumSub()

Do Events
If Not Flag Then
Call Sub OOPS()
Stop Procedure.Me
End If

'yada yada

And


Sub OOPS(Parameters)
Messages = "Warning! Warning! Danger Will Robinson!"
'Yada Yada


In my opinion:
Boolean Flags are about the only variable Types that should be used As Public. If you need Public Properties, you should use Property Procedures.

Flags themselves don't have to be just Booleans.

Public Success As String
The Setting Procedure

Success = Me.Name & "True" 'Since the name is expected to be known
'OR
Success = Me.Name & ErrorCode

You can create a User Defined Type (UDT)

Type tySuccess
suBool As Boolean
suCaller As String(32)
suError_Code As Long
suMsg As String(64)
End Type

Public Success As tySuccess

To set it

With Success
.suBool = True
.suCaller = Me.Name
.suMsg = "Yay!"
End With

Read it again, I did say "About." Those more compex structure should only be used when it is critical that more than a boolean value be used. It's just a "Bad Thing."

A Public StringVar can be real handy because it will carry boolean values, and once in a while you may want it to carry some string value. Until that happens you can just
StringVar = True
'OR
StringVar = BooleanVar
'OR
If StringVar Then