Application.EOMONTH giving wrong answer

[baitmaster]

Edit: Application or Worksheetfunction, both giving same issue

Getting strange results in VBA when trying to work with EOMONTH. Debugging in Immediate I've got:

?format(WorksheetFunction.EoMonth(clng(Date),0),"dd mmm yyyy")

I'm expecting it to return 28 Feb 2018 but it's coming up with the 27th instead. Any ideas why?
I'm using CLNG because I'm in the UK and without it I'm getting an answer in 2010, trying to rule out US/UK date issues

Is there a known instability with VBA EOMONTH? Or is the answer staring me in the face but I just can't see it?!

Using Office Pro Plus 2013

Thanks

 

[shg]

I see 28 Feb.

 

[Jonmo1]

Wild guess here, but based on the line you posted.
Everything appears capitalized as it should, except clng. It should appear as CLng.
Just on the assumption that the rest was auto capitalized but clng was not.

That may indicate you have some other sub / function or Variable named clng that is messing it up ?

What happens when you do it without CLng, that really shouldn't be necessary.

 

[Joe4]

I see 28 Feb.

Me too, but then again the three of us are all in the States.

 

[Fluff]

I get the correct value with & without CLng

?format(WorksheetFunction.EoMonth(Date,0),"dd mmm yyyy")
28 Feb 2018
?format(WorksheetFunction.EoMonth(clng(Date),0),"dd mmm yyyy")
28 Feb 2018

I'm also in the UK using Pro Plus 2013

 

[baitmaster]

Thanks all. No other function, it didn't capitalise because I wrote it directly in Immediate. I don't know why it bugged but I worked around by creating a different function that used

DateSerial(Year(lDate), Month(lDate) + 1, 1) - 1

I don't know what caused this but since it's unstable I don't want it. I get enough of that at home...

 

[Jonmo1]

Just curiosity now..

Does it work in an actual spreadsheet formula?
=EOMONTH(TODAY(),0)

And I notice that in your original post you used Date, but in your solution/workaround you used lDate
Don't know if that's the issue, just an observation.

 

[baitmaster]

lDate was a long variable I was using in my code (I always use long since a painful learning experience many years ago when thousands of dates completely corrupted with no way of unscrambling them). The code gave the wrong result so I debugged it using Date in the immediate window and got the wrong answers. I tried in Excel and got the right answer, which was a relief for my sanity...

Testing once again in the immediate window:

?format(WorksheetFunction.EoMonth(Date,0),"dd mmm yyyy")
27 Feb 2010
?format(WorksheetFunction.EoMonth(clng(Date),0),"dd mmm yyyy")
27 Feb 2018

Weird

 

[Jonmo1]

What about the format function, do you have another macro or variable by that name?

What does this do
MsgBox WorksheetFunction.EoMonth(Date,0)

 

[baitmaster]

Nope, no other reasons I can come up with thus far to explain the behaviour.

?WorksheetFunction.EoMonth(Date,0)
 40236

Same result when using msgbox. Pasting into Excel and formatting there tells me this is 28 Feb 2014. System clock definitely saying 05/02/2018 (UK = 05 Feb)