"End" Statement Question

[pilky100]

Hi all,

I have used the site for some time to help with VBA issues in excel so first of all thanks to everyone!

Secondly I have an issue when using the "End" statment in my code

I have a Userform which is used to run multiple difference macros from. This Userform is designed to stay shown all the time.

There are a few macros which I have which call a "Checking" sub routine which checks the specified data. If any of the data appears to be unexpected then the macro warns the user then uses the "End" statement to stop the code.

All this works as expected and all the code works without erroring. My only problem is that when the "End" statment is used the Userform is also unloaded.

Is there another statement which can be used which will only stop the code and allow the userform to remain in view?

I would use "Exit Sub" but as you can see from the below hierachy the are instances where I would need to use it 3 times

Userform
'->Button Click
'->Call Macro x
'->Call Macro y
'->Call Checking

Any help on this would be greatly appreciated.

Thanks
James

 

[Ruddles]

I would not recommend allowing a subordinate procedure to do anything as drastic as ending VBA or even interact with the user. Generally your checking macro should only check and then return the results of its checking to the parent macro so that the parent macro can decide what to do.

A common question is: I have this code:-

[FONT=Fixedsys]Public Sub MainRoutine()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Call DataValidation
  Call ReformatData
  Call ExportData
  
End Sub
[/FONT]

How do I make sure ReformatData and ExportData are only executed if the data passes validation?

The answer is to make your DataValidation macro a function which returns a value to the parent routine indicating success or failure.

Take a look at this:-

[FONT=Fixedsys]Public Sub MainRoutine()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim iReturnCode As Integer
  
  iReturnCode = DataValidation
  
  If iReturnCode = 0 Then
    Call ReformatData
    Call ExportData
  Else
[COLOR=blue]    MsgBox "Error! Data failed validation! Code " & iReturnCode & "!" & Space(10), vbOKOnly + vbInformation[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=blue]    Exit Sub
[/COLOR]  End If
 
End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Private Function DataValidation() As Integer[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  DataValidation = 0 [COLOR=green]' assume okay for the moment
[/COLOR]  
  If Not IsNumber(Range("A1")) Then DataValidation = 1: Exit Function
  
  If Range("A1").Value < 0 Then DataValidation = 2: Exit Function
  
  If Range("A1").Value = 0 Then DataValidation = 3: Exit Function
  
  If Range("A1").Value >= 100 Then DataValidation = 4: Exit Function
  
End Function
[/FONT]

DataValidation is now a function which does any checking you need to do and sets a return code indicating which test has resulted in the validation failure. The return code is passed back to the parent routine in the name of the function, the parent routine stores it locally and can then use it to take further action: calling ReformatData and ExportData if DataValidation returned a zero or issuing an error message and exiting the subroutine if the return code was non-zero. (Obviously you'd insert the code for whatever you wanted to do in place of the blue bit.)

Does this make sense?

 

[pilky100]

Hi Ruddles,

Thanks for the quick response.

Yes your suggestions make sense, In fact I can incorporate the same style into other areas of code which will probably help improve the coding of the macros

Thanks very much for your help

James

 

[Ruddles]

And thanks to you for the feedback!

 

[Ruddles]

Thanks for the quick response.

Actually, fifty minutes is unusual - responses are normally much quicker than that!