What Variable-type should I use

[Michelle]

A simple question - I think. I often use a variable to contain the response
from a MessageBox, I always declare it as a variant - is that necessary? In
help, the responses are always integers between 0 and 64, can I use an
Integer (or even a Byte) variable type, or are there some surprise responses
in certain circumstances. I want to be sure that this will not generate an
error.

This is how I'd normally do it...

Dim Again As Variant

' Check that the user wants to start again
Again = MsgBox(" Do you want to start again?", vbQuestion + vbYesNo)


Is there a better way

M

 

[Harald Staff]

Most of "those things" are Long. A byte would sometimes be too small.
Integers are internally translated to Long, so don't use them.

Best wishes Harald

 

[Alan Moseley]

A MsgBox will always return a Long result. What's more you can use some
constants to check the user's response. For example

If MsgBox("Please choose Yes Or No",vbQuestion+vbYesNo)=vbYes Then
'User chose Yes
Else
'User chose No
End If

 

[Chip Pearson]

The best way is to declare the result variable as a VbMsgBox type.
That's the way VBA sees it internally and you get intellisense support
to boot.

Dim Res As VbMsgBoxResult
Res = MsgBox("Question", vbYesNo)
If Res = vbYes Then
' do something
Else
' do something else
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)