vba code in this example

Question

Hi

I'm very new to programming and I'm just starting to learn VBA with excel. I came across on this website and did the examples here but I have question about this code:

I know the variables are declared using "Dim" statement "Message" here is the variable with a data type of integer. What I don't clearly understand is; what is the meaning of "6" here and "7". I believe they come from somewhere. But as I just started learning this program, I don't have any idea. Could you please tell me how it end up to "6" and "7". I believe there is some basis here

Private Sub CommandButton1_Click()
Dim message As Integer
message = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
If message = 6 Then
Range("A1").Value = "You may proceed"
ActiveWorkbook.Activate 
ElseIf message = 7 Then
ActiveWorkbook.Close
End If

End Sub

Thank you for your help:-)

=======

Thanks guys for the answers, they're very helpful. Yes this thread has been already posted in superuser site. I was informed that this question should belong here so I posted it here after reading that they will do it automatically from superuser to stackoverflow.

thanks once again

Answer 1

These are return value from MsgBox(). The author should have used their symbolic value instead to make the program more readable:

vbYes   6
vbNo    7

See this MSDN article for more info

Answer 2

6 and 7 are the return codes from the MsgBox method. Basically, when MsgBox is called, it shows a message-box to the user, who clicks either "Yes", "No", or "Cancel". The user's selection is returned from the MsgBox method as a number, where 6 is Yes, and 7 is No.

It is considered best-practice not to use these numbers in your code directly, but instead to use Microsoft supplied constants which represent them. Your code could be re-written as:

Private Sub CommandButton1_Click()
    Dim message As Integer
    message = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
    If message = vbYes Then
        Range("A1").Value = "You may proceed"
        ActiveWorkbook.Activate 
    ElseIf message = vbNo Then
        ActiveWorkbook.Close
    ElseIf message = vbCancel Then
        'Do nothing.
    End If
End Sub

Answer 3

It's very poorly written code, "6" and "7" are the values of the constants "vbYes" and "vbNo" where are returned when the user clicks Yes or No on the dialog.

Reference: http://www.techonthenet.com/access/constants/msgbox%5Fret.php

The code should say

If message = Constants.vbYes

instead of

If message = 6

So that it is clear what is happening.

Answer 4

This link is for VBScript, but I think the return codes should be the same: MsgBox Function Reference

The Return Codes tell you which button was clicked:

1   OK
2   Cancel
3   Abort
4   Retry
5   Ignore
6   Yes
7   No

Answer 5

MsgBox does return an Enum(eration) called MsgBoxResult, which is basically nothing else then numeric values with a 'label'. 6 and 7 in this case are members of this enum, which are mapped to the answers 'Yes' and 'No'.

Basically, you could rewrite the code to this:

Dim message As Integer
message = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
If message = MsgBoxResult.Yes Then
    Range("A1").Value = "You may proceed"
    ActiveWorkbook.Activate
ElseIf message = MsgBoxResult.No Then
    ActiveWorkbook.Close
End If

Bobby

Edit: Might theat the Enum os called vbMsgBoxResult or something...I don't have an Office to verify this, just Visual Studio.

Edit2: While we are on it...this might be easier to understand:

Select Case MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
    Case MsgBoxResult.Yes
        Range("A1").Value = "You may proceed"
        ActiveWorkbook.Activate
    Case MsgBoxResult.No
        ActiveWorkbook.Close
    Case MsgBoxResult.Cancel
        ' he clicked cancel '
End Select

Answer 6

The 6 and 7 are hard coded values that hold a special meaning. The 'MsgBox("Click Yes...")' call will return a number that will let your code determine what the user did with the message box and you can then use conditionals (your IF statements) to decide what to do next.

A full list of these special values can found in the MSDN documentation here:

http://msdn.microsoft.com/en-us/library/139z2azd%28VS.80%29.aspx

Answer 7

When I first started with MsgBox answers, I almost always declared the answer as an Integer. However, I learned that the best thing to do is to declare your message variable as VbMsgBoxResult, which is an enumeration that will always show the available answers. In the picture below, the IDE (e.g. the Visual Basic for Application editor) will show you the possible options available in VbMsgBoxResult.

You could store your answer variable as an Integer since all of the variables in the Enumeration (e.g. vbAbort, vbYes, vbOK, etc.) do in fact resolve to integers. However, you have to figure out what the integer values for those variables are every time you want to reference them. In my opinion, it's a better practice to store your answer as VbMsgBoxResult so you can actually see the available answers.

Answer 8

Just rewrite to the equivalent:

Private Sub CommandButton1_Click()
  Dim optionSelected As VbMsgBoxResult
  optionSelected = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")
  If optionSelected = vbYes Then
    Range("A1").Value = "You may proceed"
    ActiveWorkbook.Activate 
  ElseIf optionSelected = vbNo Then
    ActiveWorkbook.Close
  End If
End Sub

And move on