Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Thanked 1 Time in 1 Post

    Is this VBA book wrong, or am I? (VBA Excel 2002)

    I just bit the bullet, deciding to finally learn VBA properly. I am finally working through the early chapters of John Walkenbach's "Excel VBA Programming for Dummies" (2004). I must be a sub-dummy as I can't get the first example of code to run properly. It's bottlenecking my study plan big time. Here's what I do and what happens.

    I create a new module, which automatically begins with "Option Explicit" . Below that I type his code:

    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 "Thought so"
    End Sub

    Even I can work out what this is supposed to do. But when I write the code and then press F5, I get an error message stating "Compile error: Variable not defined". At the same time, the word "Msg" in the second line is highlighted in blue. When I click "OK" , the words "Sub GuessName()" in the first line are highlighted in yellow.

    What's wrong? Any guidance would be appreciated.

    For this to happen on the first exercise in a For Dummies book is not an ideal start.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 187 Times in 173 Posts

    Re: Is this VBA book wrong, or am I? (VBA Excel 2002)

    Because you have Option Explicit, you must declare all variables. If you add:
    <code> Dim Msg, Ans</code> after the <code>Sub GuessName()</code> line, you should be OK - I am assuming the third line has a typo and should read:
    <code>Ans = MsgBox(Msg, vbYesNo)</code>

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Is this VBA book wrong, or am I? (VBA Excel 2002)

    As a side remark: it is a good idea to have Option Explicit at the top of all your modules. See <post:=314,748>post 314,748</post:> for an explanation.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Melbourne, Victoria, Australia
    Thanked 0 Times in 0 Posts

    Re: Is this VBA book wrong, or am I? (VBA Excel 2002)

    Further to the previous answers, you might want to take it a step further by using:
    Dim Msg As String
    Dim Ans As Long
    This practice improves readability and maintainability of code. There are other recommended "codes of practice" or conventions for variable naming and coding, such as those described in RVBA Conventions - Xoc Software (RVBA Conventions, Maya Calendar, For instance, you (can) use strMsg and lngAns for the variable names above, so that their type is immediately obvious wherever they appear in your code.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts