+ Reply to Thread
Results 1 to 17 of 17

Problem with EOMONTH function in VB

  1. #1
    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,697

    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.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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

  3. #3
    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,697

    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.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Problem with EOMONTH function in VB

    [edit - removed - misread]

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Problem with EOMONTH function in VB

    Is the sheet protected by any chance ?

  6. #6
    Forum Guru 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

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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)

  8. #8
    Forum Guru 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 ...

  9. #9
    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,697

    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.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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)

  12. #12
    Forum Guru 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.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Problem with EOMONTH function in VB

    hee hee... can I put that oversight down to donkey-flu ?

  14. #14
    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,697

    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

  15. #15
    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,697

    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

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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

  17. #17
    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,697

    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 to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1