(Working) Can someone explain why?!

[L'Amore]

I'm 4 days old to VBA so still working things out!
I have managed to create this subroutine for a form im working on.
First off, i think it works but any obvious problems (including good practice things) please to point them out. This is an ammalgamation of various bits ive picked up from google and from common sense!


Could someone explain the highlighted bits of code (ones ive put a ' before, but all lines are used in my script, its just to point them out)
The first line can anyone completely explain what each part of the object_Exit() bit does, ive searched and search and cant work it out. Why cant i just put

'Private Sub TextBox3_Exit()

like i have done on TextBox3_Change() (which i use to update other textboxes, havnt looked at whether this can be included in the code below yet.

second line

there are a few things with this area of code:
if an If statement is concluded on one line does it negate the need for an End If?
would it make sense if i put an Else statement to deal with vbYes

I think i understand the rest. Although if anyone has any better / more concise / more efficient ways of doing the same thing I am all ears :D

'Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    With TextBox3
        If IsNull(.text) = True Or IsNumeric(.text) = False Then
            If MsgBox("Invalid entry. Please enter a numerical value for '" + .Tag + "' to continue", vbCritical, "ERROR") = vbOK Then .Value = Val("4.18")
        Else
        If Val(.Value) <> Val("4.18") Then
'            If MsgBox("Are you sure? This is a Universal Constant! **** like this doesn't change overnight!", vbYesNo + vbExclamation, "Really?") = vbNo Then .Value = Val("4.18")
                Exit Sub
        End If
        End If
    End With
    
    TextBox9.Value = (Val(TextBox8.text) / ((Val(TextBox4.text) - Val(TextBox5.text)) * Val(TextBox3.text)))
        
End Sub

Thanks for your help! :D

 

[sijpie]

'Private Sub TextBox3_Exit()

Windows is an event based program (yes windows is a program). What Event based means is that Events are used to communicate between parts of the program. Events are created by the user or by programs running under windows and windows itself.
An example is a key click. The user presses a key. This creates two events in windows: a Key-Down and a Key-up event. Windows sends these two events into the event chain, first to be picked up by the active program. If it doesn't have a trap for this type of event, the events go further down the chain. But a program like Word will capture the event and use it to send Windows an event telling Windows to display the key tapped. Word also kills the event so that it disappears from the chain.

The Textbox_Exit routine is an event trap for the event that is created when the user leaves the text box, either by using a Tab keystroke, or by using the mouse to click on another field, or in another window. Windows detects this and sends the event into the chain. Your form now captures the event, does what you want, but the event is not killed but passed on so that whatever the user clicked on will next use the event. But you may not want the event to be passed on, as the user has put in invalid formation in the textbox. So if that is the case you add the following to your code:

    Cancel=True

Windows now removes the event from the chain before the next program could use it. So if the user clicked on another box on the form, that box does not become active. But Give the user a reason why his click did not register! so a messagebox is to be used...

If MsgBox("Are you sure? This is a Universal Constant! **** like this doesn't change overnight!", vbYesNo + vbExclamation, "Really?") = vbNo Then .Value = Val("4.18")

This line is a bunch of commands together, so, let's split them up:

   strMessage="Are you sure? This is a Universal Constant! **** like this doesn't change overnight!"
   bButton =  vbYesNo + vbExclamation  ' These are two numbers really, disguised
   strTitle = "Really?"

  msbReturn = MsgBox (strMessage, Button:=bButton, Title:= strTitle)
  ' Msgbox displays a dialog with a titel, a text and button(s) + icons as defined by the number in Button _
    MsgBox returns the value of the button pressed by the user, which here is captured in msbReturn _
    For the OK/No button pair 6 is returned if Yes was pressed: (vbYes=6) , 7 if No was pressed (vbNo=7) and _
     0 if the user pressed the X close button. You need to account for this action as well!!

  if msbReturn <> vbYes  then  ' I changed this line to account for both vbNo and Close
      .Value = Val(4.18)
  end if

 

[L'Amore]

Windows is an event based program (yes windows is a program).

New to vba not computers
Thanks though, although i still dont know what the _Exit( bit does, "ByVal Cancel As MSForms.ReturnBoolean" what does this tell me?, the other areas i _Change() there is nothing between the brackets.
Thanks for the tip with the message box, hadnt considered that, but the close option is unavailable when the msgbox is open (i assume as i havnt declared it?) and the yes/no options do there job fine(with less code) so i will stick to it for the time being, but that may effect me elsewhere

Thanks for the help!

 

[shg]

Why cant i just put

Private Sub TextBox3_Exit()

like i have done on TextBox3_Change()

Because event handlers have a specific signature that cannot be changed.

If you select TextBox3 from the left dropdown in the VBE (in the code module for the form), the right-dropdown shows a list of the events, and selecting one puts the shell of the procedure (the signature and End Sub) into the module.

When the event is generated, the VBE calls the event handler, passing the arguments listed in the signature (Exit has a Cancel argument; Change has no arguments).

 

[sijpie]

lthough i still dont know what the _Exit( bit does, "ByVal Cancel As MSForms.ReturnBoolean" what does this tell me?

I thought I spelt it out here:

The Textbox_Exit routine is an event trap for the event that is created when the user leaves the text box, either by using a Tab keystroke, or by using the mouse to click on another field, or in another window. Windows detects this and sends the event into the chain. Your form now captures the event, does what you want, but the event is not killed but passed on so that whatever the user clicked on will next use the event. But you may not want the event to be passed on, as the user has put in invalid formation in the textbox. So if that is the case you add the following to your code:
Code:

Cancel=True</pre>
Windows now removes the event from the chain before the next program could use it. So if the user clicked on another box on the form, that box does not become active. But Give the user a reason why his click did not register! so a messagebox is to be used...

So the Cancel parameter that this event handler gets from Windows is the Cancel that you can set to true in order to stop further processing. The Change event handler does not get a Cancel parameter from windows, because there is nothing to cancel. You may want to undo something but that is different.

The format in which it is written

ByVal Cancel As MSForms.ReturnBoolean

means that the Cancel parameter is a Boolean (true or false) as defined in the MSForms object. ByVal means that you are getting a copy of the Cancel object, and not the pointer to the real thing. That does not make much sense here, but consider the following:
You have a sub (or function) that needs as a parameter a string. So the sub calling your function passes it the string "Hello". Your function is defined as

Function MyThing(sN as String) as String

If your function changes the sN (which contains "Hello") to say "Hello Amore" then after the function ends (and passes another string to the calling sub) when you check the string you passed to the function you see it has been changed.

However if you had defined your function as

Function MyThing(ByVal sN as String) as String

, then if you change the "Hell"in your function, and go back to the calling sub, then the original string has not been changed, only the copy of this string which was sent to the function was changed.

You will probably need to read this a few times to make sense of it.