VBA Beginner

[RossG]

I am working through EXCEL VBA Programming for Dummies from 2004. I am getting the "Compile error: Variable not defined" pop-up. Below is what my code looks like and it is verbatim as the example in the book. I know the problem has something to do with the "Option Explicit" at the top and that I need to define my variables, but I don't know how to write the code.

Option Explicit

Sub GuessName()
Msg = "Is your name" & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "Oh, never mind."
If Ans = vbYes Then MsgBox "I must be clairvoyant!"

End Sub

 

[JoeMo]

When you use Option Explicit you must define all variables. try

Option Explicit

Sub GuessName()
Dim Msg as String, Ans as Long
    Msg = "Is your name" & Application.UserName & "?"
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then MsgBox "Oh, never mind."
    If Ans = vbYes Then MsgBox "I must be clairvoyant!"
    
End Sub

 

[mumps]

Try:

Sub GuessName()
    Dim Msg As String, Ans As String
    Msg = "Is your name" & Application.UserName & "?"
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then MsgBox "Oh, never mind."
    If Ans = vbYes Then MsgBox "I must be clairvoyant!"
End Sub

 

[Joe4]

Welcome to the Board!

Here are a few articles on the topic that you may want to read:
https://www.elearnexcel.com/2013/10/01/what-is-option-explicit-for/
http://www.cpearson.com/excel/DeclaringVariables.aspx

 

[mumps]

It can actually be shortened to this:

Sub GuessName()
    If MsgBox("Is your name " & Application.UserName & "?", vbYesNo) = vbNo Then
        MsgBox "Oh, never mind."
    Else
        MsgBox "I must be clairvoyant!"
    End If
End Sub