Understanding which response codes are returned from MsgBox

Question

I am very new to programming and am just starting to learn VBA with excel. I came across this site and made examples here, but I have a question about this code:

I know that variables are declared using the "Dim" operator. "Message" here is a variable with an integer data type. That I do not quite understand what is the meaning of "6" here and "7". I believe that they came from somewhere. But since I just started to study this program, I have no idea. Could you tell me how this will end before "6" and "7". I believe that there are some basics.

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 are very helpful. Yes, this topic has already been posted on the superuser site. I was informed that this question should be here, so I read it here after reading that they will do this automatically from superuser to stackoverflow.

thanks again

Answer 1

MsgBox returns an Enum(eration) called MsgBoxResult , which basically is nothing more than numeric values ​​with a label. 6 and 7 in this case are members of this enumeration, which are compared with the answers Yes and No

The use of so-called “magic numbers” instead of constants or Enums should be avoided whenever possible.

Basically, you can rewrite the code for 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 

It is possible that Enum is called vbMsgBoxResult or something else ... I do not have Office to test this, just Visual Studio.

While we're on it ... it 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 2

The very poorly written code "6" and "7" are the values ​​of the constants "vbYes" and "vbNo", which are returned when the user clicks "Yes" or "No" in the dialog box.

Link: http://www.techonthenet.com/access/constants/msgbox_ret.php

The code must be specified

 If message = Constants.vbYes 

instead

 If message = 6 

So it’s clear what is happening.

Answer 3

When I first started with MsgBox answers, I almost always declared the answer as Integer . However, I found out that it is best to declare your message variable as VbMsgBoxResult , which is an enumeration that will always show the available responses. In the figure below, the IDE (for example, the Visual Basic for Application Editor) will show you the options available in VbMsgBoxResult .

You can save your answer variable as Integer , since all variables in Enumeration (e.g. vbAbort , vbYes , vbOK , etc.) do allow integers. However, you need to find out which values ​​for these variables are integer values ​​every time you want to refer to them. In my opinion, it is better to save your answer as VbMsgBoxResult so that you can see the available answers.

Answer 4

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

Return codes tell you which button was pressed:

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

Answer 5

6 and 7 are hard-coded values ​​that have special meaning. Calling "MsgBox (" Click Yes ... ") returns a number that will allow your code to determine what the user has done with the message field, and then you can use conditional expressions (your IF statements) to decide what to do next.

A complete list of these special values ​​can be found in the MSDN documentation here:

http://msdn.microsoft.com/en-us/library/139z2azd(VS.80).aspx

Answer 6

This is the return value from MsgBox (). Instead, the author had to use his symbolic meaning to make the program more readable:

 vbYes 6 vbNo 7 

See the MSDN article for more information.

Answer 7

6 and 7 are return codes from the MsgBox method. Basically, when MsgBox is called, it shows the user a window that clicks Yes, No, or Cancel. User selection is returned from the MsgBox method as a number, where 6 is Yes and 7 is No.

It is believed that it is better to use these numbers in your code directly, and instead use the constants provided by Microsoft that represent them. Your code can be rewritten 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 8

Just rewrite 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