Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    XL Macro to place date in sheet header

    I'm trying to create a macro to get a date, place it in a cell, and then use that date to update certain sheets within the workbook. But I'm getting an error message when the macro gets to user entry into the InputBox (UserValue is highlighted and the error message is Variable Not Defined). How do I correct this code?

    Sub AddDate2Header()
    '
    Sheets("PlayerDailyDetail").Activate
    Range("c5").Select
    UserValue = InputBox("Enter FIRST Day of month in MM/01/yyyy format")
    Range("c5").Value = UserValue

    ' 'adds the data from cell c5 into the header of the active worksheet

    Sheets("Alpha Player List").Activate
    ActiveSheet.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Range("I1").Value

    ' 'from here on the code steps through the remaining sheets


    (edited later) PS: I can remark out the two lines for the InputBox and UserValue and the remaining code executes as expected.
    Last edited by Arcturus16a; 2013-09-05 at 13:59.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I suspect that you have the line:
    Option Explicit
    above the macro. This is recommended, and it requires that all the variables to be dimensioned. Add the line into your code:
    Dim UserValue as String

    to define the variable that you do not have defined.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Steve.

    This works with just Dim UserValue as String. What's the Option Explicit for? Seems to work fine without it.
    When I try to it add above the line Sub AddDate2Header(), VBA draws a line between Option Explicit and Sub AddDate2Header(). Is that expected?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Arcturus,

    The Option Explicit means that the VBE compiler will flag any thing that looks like a variable to it but has not been Dimensioned.

    If you Add the Dim statement ABOVE the Sub() line you are declaring (DIMing) a Module level variable (SCOPE) that can be used in any routine in that module (sometimes used to pass variables back and forth). If you put your DIM after the SUB() you are declaring a variable that is only knows between the Sub/End Sub commands.

    See this article on Scope Of Variables if you want a fuller explanation. HTH

    I almost forgot the line is just a visual convention in the Editor and doesn't affect any of the code
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks RG for the explanation. Since most of my code is simple I think I can declare the variables within the routine (between the Subs). I'll check out that article, too.

Posting Permissions

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