Solved: Variables declared correctly? Space Continuation?

[YellowLabPro]

1) Are my variables declared correctly?
2) Space continuation? I wanted to break my code in the VBE window. I used a space and then an underscore "_" but received a compile error. The following line is what errored.

[VBA]If Ans = vbNo Then MsgBox "You are not authorized to use this computer, this program will not allow non-authorized users to proceed. Please exit immediately, or the administrator will be notified immediately of a non-authorized access attempt."[/VBA]

Thanks,

YLP

[Andy Pope]

It's hard to tell whether they are declared properly as you do not show any declaration code.

You would use continuation something like this.
[vba] If Ans = vbNo Then MsgBox "You are not authorized to use this computer, " & _
"this program will not allow non-authorized users to proceed." & Chr(10) & _
"Please exit immediately, or the administrator will be notified immediately " & _
"of a non-authorized access attempt."
[/vba]

[YellowLabPro]

Doh!
Thanks Andy....

[VBA]
Sub GuessName()
Dim Msg As String
Dim Ans As Integer
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "You are not authorized to use this computer, this program will not allow non-authorized users to proceed. Please exit immediately, or the administrator will be notified immediately of a non-authorized access attempt."
If Ans = vbYes Then MsgBox "You May Proceed"
End Sub
[/VBA]

[Andy Pope]

Yes they were

[vba]Sub GuessName()
Dim Msg As String
Dim Ans As VbMsgBoxResult

Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "You are not authorized to use this computer, " & _
"this program will not allow non-authorized users to proceed." & Chr(10) & _
"Please exit immediately, or the administrator will be notified immediately " & _
"of a non-authorized access attempt."
ElseIf Ans = vbYes Then
MsgBox "You May Proceed"
End If
End Sub
[/vba]

You might want to use VbMsgBoxResult as this will allow intellisense to provide useful information.

[YellowLabPro]

Andy,
A small detail point follow up question regarding the space continuation please.
In my book, it states to use a space followed w/ the underscore. Is it because this is text?

[Andy Pope]

Yes when continuing text across lines you need to split the text within quotes and concatention as I have done.

When using variables or constant values you can just use space undescore.

[vba]Result = _
1 _
+ _
2[/vba]

[mdmackillop]

When you break a line of text within your code, you need to use the & operator to "join it together", remembering to preserve the text space between words when you split text.
If it's a long line of code, the Space and Underscore is correct.

[YellowLabPro]

You might want to use VbMsgBoxResult as this will allow intellisense to provide useful information.

Andy,
I found what VbMsgBoxResult does, how do I incorporate it. I found a great deal of info regarding it, but nothing showing how.

thanks for the assistance,

YLP

[YellowLabPro]

Thanks Malcolm.

[Andy Pope]

As I did in the code example I posted.

[vba] Dim Ans As VbMsgBoxResult [/vba]

[YellowLabPro]

Andy,
You realize one Doh! for the day is acceptable, but two-- man embarrassing.........

Thank you,

YLP

[YellowLabPro]

Thanks Andy,
I found a related article on MS' website. But it is erroring: receiving a Compile err:
User-defined type not defined.
Occurring on: Dim style As MsgBoxStyle
I have read through the code, this was taken directly from their site, the address is commented after the Sub line. I can't see why it is erroring. Can someone shed some light?

[VBA]
Sub Msgbox()
'http://msdn2.microsoft.com/en-us/library/139z2azd.aspx
Dim msg As String
Dim title As String
Dim style As MsgBoxStyle
Dim response As MsgBoxResult
msg = "Do you want to continue?" ' Define message.
style = MsgBoxStyle.DefaultButton2 Or _
MsgBoxStyle.Critical Or MsgBoxStyle.YesNo
title = "MsgBox Demonstration" ' Define title.
' Display message.
response = Msgbox(msg, style, title)
If response = MsgBoxResult.Yes Then ' User chose Yes.
' Perform some action.
Else
' Perform some other action.
End If
End Sub

[/VBA]

Thanks,

YLP

[mdmackillop]

Hi Yelp,
This is for creating a message box in Visual Basic, similar to VBA, but which is compiled into an exe and operates as a stand alone programme. Excel has the MessageBox function built in, so you don't need to do this.

[YellowLabPro]

Thanks Malcolm, again,

[Bob Phillips]

One point about the continuation which I don't think has been clear.

A continuation character is preceded by a space as it is an instruction in its own right, so it has to be separated from other instructions. It is nothing to do with text per se. You can run continutaion without text , as in

[vba]
If Range("A1").Value = 1 Or _
Range("A1").Value = 2 Or _
Range("A1").Value = 3 Then
...
[/vba]

If you want to continue a text string though, you have to close that string on each line that is continued, and issue the string concatenation character, &

[vba]
If Ans = vbNo Then MsgBox "You are not authorized to use this computer, " & _
"this program will not allow non-authorized users to proceed." & Chr(10) & _
"Please exit immediately, or the administrator will be notified immediately " & _
"of a non-authorized access attempt."
[/vba]
In effect you are continuing using a series of strings, all joined together.

[YellowLabPro]

Hi Bob,
I studied this yesterday from what Andy and Malcolm posted and got the idea. This helps solidify the "why" for me. Thanks taking the extra step to help me get it.

Best Regards,

YLP