msgbox question

[lezawang]

Hi
I can write msgbox with ( ) or without like the code below? Why I can do that? This function is not returning any value then I thought it should not take ( ),

Also I noticed that, I defined X as Int. Then whenever I type x as small letter, excel change it to big X. Does that mean variable are not case sensitive? Thank you so much.

Sub mysecondvba()
'++++++++
'this to demo using variables
'++++++++
    Dim X As Integer
    X = 10
    MsgBox (X)
    MsgBox X
    
End Sub

 

[AlexanderBB]

I find casing is set by how you type it (Dim x or Dim X). msgbox(x) will return a value if you assign it, e.g. something = Msgbox(x). Usually vbYes, No or Cancel.

 

[Scott Huish]

It will change the variable to the case you used when you dimmed the variable.

There are cases when the parentheses are required such as when you are assigning the results of a msgbox to variable (which is usually used to determine which button you clicked on), but in this case it is not necessary. If you are just using it to display a value the parentheses are optional.

Sub test()
Dim X As Integer
Dim c As Long
X = 7
c = MsgBox("Did you mean 7?", vbYesNo)
If c = vbYes Then
    MsgBox X
Else
    MsgBox "You clicked No"
End Sub

 

[Jon Peltier]

The short answer is that wrapping an argument in parentheses where the parentheses are not required, causes VBA to evaluate that argument before passing it.

MsgBox X ' the variable X is passed into MsgBox, and MsgBox decides what to do with it.
MsgBox (X) ' the variable X is evaluated, and this value is passed into MsgBox.

So what's the big deal? Well, for MsgBox, it hardly makes any difference. But take this procedure:

Sub FOO(ByRef BAR As Boolean)
  BAR = False
End Sub

BAR is passed ByRef into FOO, FOO changes the value of BAR, and having been passed ByRef, BAR retains the new value after FOO is finished with it.

If BAR had been passed ByVal, FOO would change the value of BAR, but only within the scope of FOO, and when FOO is finished, BAR reverts to its previous value.

Let's test the procedure with two variables, Bar1 and Bar2. We'll pass Bar1 into FOO as is, and we'll wrap Bar2 in parentheses.

Sub TEST()
  Dim Bar1 As Boolean
  Bar1 = True
  FOO Bar1
  
  Dim Bar2 As Boolean
  Bar2 = True
  FOO (Bar2)
  
  Debug.Print "FOO Bar1 ----> Bar1 = " & Bar1 & vbNewLine & "FOO (Bar2) --> Bar2 = " & Bar2
End Sub

We would expect FOO to change Bar1 to False, but since we are passing the value of Bar2 and not the value itself, we don't expect Foo to change Bar2's value. And the output in the Immediate Window bears this out:

FOO Bar1 ----> Bar1 = False
FOO (Bar2) --> Bar2 = True

 

[Rick Rothstein]

The short answer is that wrapping an argument in parentheses where the parentheses are not required, causes VBA to evaluate that argument before passing it.

Just to add to Jon's comment above, there could be a consequence to using parentheses around a function's argument list that needs to be watched out for. For a single argument... no problem, it simply evaluates to itself. However, if pass multiple argument surrounded by parentheses into a function, you'll generate an error. Why? Because VB does not know how to evaluate a comma delimited list. So, where as this works...

MsgBox ("Some text")

this will not work...

MsgBox ("Some text", vbCritical)

but this will...

MsgBox "Some text", vbCritical

 

[Scott Huish]

Unless you pass the value to a variable, in which case you need the parentheses:

c= MsgBox ("Some text", vbYesNo)

 

[Jon Peltier]

Just to add to Jon's comment above, there could be a consequence to using parentheses around a function's argument list that needs to be watched out for. For a single argument... no problem, it simply evaluates to itself. However, if pass multiple argument surrounded by parentheses into a function, you'll generate an error. Why? Because VB does not know how to evaluate a comma delimited list. So, where as this works...

MsgBox ("Some text")

this will not work...

MsgBox ("Some text", vbCritical)

but this will...

MsgBox "Some text", vbCritical

Of course, putting each argument within parens has the effect of passing them all by value:

MsgBox ("Some text"), (vbCritical)