+ Reply to Thread
Results 1 to 17 of 17

Problem with EOMONTH function in VB

  1. 10-02-2010, 01:01 PM #1
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Problem with EOMONTH function in VB

    Hi,

    I want to create a list of dates starting in B6 and going down to B33 or B36 depending of the number of days in a month. The "EOMONTH" function works ok in Excel but when I try to use "EOMONTH" in a macro I'm getting run-time error 1004.

    Have set a reference to "atpvbaen" in VB editor. I've also tried "WorksheetFunction.EOMONTH" and "Application.WorksheetFunction.EOMONTH" but with no luck.

    Please Login or Register  to view this content.
    Problem is with this line:

    Please Login or Register  to view this content.
    Grateful for any help on this problem.
    Last edited by Alf; 10-02-2010 at 03:13 PM.
    Reply With QuoteRegister To Reply
  2. 10-02-2010, 01:13 PM #2
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    Did you also activate the ATP VBA Add-In in native XL ?

    For more info. re: using ATP in VBA (pre XL2007) see: http://www.cpearson.com/excel/ATP.htm
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  3. 10-02-2010, 01:48 PM #3
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with EOMONTH function in VB

    Hi,
    Tanks for link but I have added the "Analysis ToolPak" so in a workbook I can use the function but trying to use in a macro does not work.
    Reply With QuoteRegister To Reply
  4. 10-02-2010, 01:54 PM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    [edit - removed - misread]
    Reply With QuoteRegister To Reply
  5. 10-02-2010, 01:56 PM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    Is the sheet protected by any chance ?
    Reply With QuoteRegister To Reply
  6. 10-02-2010, 02:21 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Problem with EOMONTH function in VB

    DO is saying that you need to set a reference in the VBE to atpvbaen.xls, then use the function like so

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 10-02-2010, 02:23 PM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    @shg (hi) - I was actually referring to the native ATP VBA add-in but I think in this case the issue is sheet protection - nothing more - could be wrong though

    (given the formula is not being evaluated from within VBE I don't believe any VBA related references are required)
    Reply With QuoteRegister To Reply
  8. 10-02-2010, 02:26 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Problem with EOMONTH function in VB

    given the formula is not being evaluated from within VBE
    Sorry, DO, I completely missed that. Pardon the interruption, we now resume your normal program in progress ...
    Reply With QuoteRegister To Reply
  9. 10-02-2010, 02:29 PM #9
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with EOMONTH function in VB

    No the sheet is not protected. At the moment I'm trying to paste the formula as a string and then strip away "" at front and back of the string to get a proper formula in the cell.

    Please Login or Register  to view this content.
    Looks like it could work but I would like solve the VB problem.
    Reply With QuoteRegister To Reply
  10. 10-02-2010, 02:29 PM #10
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    Quote Originally Posted by shg
    Sorry, DO, I completely missed that
    You're not alone - so did I - see posts 2 & 4
    Reply With QuoteRegister To Reply
  11. 10-02-2010, 02:32 PM #11
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    Quote Originally Posted by Alf View Post
    No the sheet is not protected.
    There is no reason why your original code would generate 1004 irrespective of ATP add-ins.

    Unless you explicitly use .FormulaLocal rather than .Formula in your VBA the formula should be US-centric.
    This means the version in post 1 should work irrespective of your locale settings (the formula will auto translate to locale specifications when it is written to the worksheet)

    That is to say, this:

    Please Login or Register  to view this content.
    should work without issue if the native ATP add-in is active - if it isn't you will just get an error value in B7 (not a debug)

    What happens when you run:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 10-02-2010 at 02:43 PM. Reason: rephrased to avoid confusion (hopefully)
    Reply With QuoteRegister To Reply
  12. 10-02-2010, 02:51 PM #12
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Problem with EOMONTH function in VB

    The easy way to debug these problems is to print the formula before you use it, and verify it's correct:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 10-02-2010, 02:53 PM #13
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    hee hee... can I put that oversight down to donkey-flu ?
    Reply With QuoteRegister To Reply
  14. 10-02-2010, 02:57 PM #14
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with EOMONTH function in VB

    Hi DonkeyOte,

    First, thanks for all time you have spent on my problem.

    Second, I get a date,1st of January 1900. As I started saying

    I want to create a list of dates starting in B6 and going down to B33 or B36 depending of the number of days in a month.
    so column B is formated as date to give values like "01-Jan"

    Have you tested my macro, does it work for you?

    Alf
    Reply With QuoteRegister To Reply
  15. 10-02-2010, 03:09 PM #15
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with EOMONTH function in VB

    Gentelmen, my bad

    I did set a reference to "atpvbnen.xls" but did not save the file so when I looked again the reference was gone. Set a new reference, saved the file and now it works beutifully.

    I also corrected a mistake in my formula. There should be 4 """")) instead of 2 "")).

    Thanks for all the help, sorry I made a mess of it.

    Alf
    Reply With QuoteRegister To Reply
  16. 10-02-2010, 03:22 PM #16
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem with EOMONTH function in VB

    Alf, glad you resolved... I think the only issue was the missing "" in the existing formula as spotted by shg.

    Based on your code you should not need a reference to atpvbnen.xls given you are not using the ATP functions from with the VBE environment - the only reference you need is to the standard ATP add-in in native XL
    Reply With QuoteRegister To Reply
  17. 10-02-2010, 04:28 PM #17
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with EOMONTH function in VB

    Yes, you are absolutely right. I did uncheck the reference to atpvbnen.xls and the macro works as expected. I then removed the "" from the formula and got the 1004 error back again.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1