CODEWITHSUNDEEP
vbams-access
linuxman
linuxman

Reputation: 157

Get the last day of month

I want to get the last day of the month.

This is my code. If I want to debug it and compile it to the database it says it has an error in the syntax.

Public Function GetNowLast() As Date
    Dim asdfh As Date
    asdfh = DateValue("1." _
      & IIf(Month(Date) + 1) > 12, Month(Date) + 1 - 12, Month(Date) + 1) _
      &"."&IIf(Month(Date)+1)>12 , Year(Date)+1,Year(Date))
    asdf = DateAdd("d", -1, asdf)
    GetNowLast = asdf
End Function

Upvotes: 7

Views: 82390

Answers (7)

hiuller
hiuller

Reputation: 461

I would create a Date with the current year, month and day as 1, store it to date_ and then

Dim date_, LastDayofMonth as Date
LastDayOfMonth = DateAdd("m", 1, date_) - 1

Upvotes: 0

pumpkin man
pumpkin man

Reputation: 1

Another method I used was:

nMonth = 2
nYear = 2021

lastDayOfMonth = DateSerial(nYear, nMonth + 1, 0)

Upvotes: 0

CraigD
CraigD

Reputation: 111

In short, a great and straightforward approach is to find the first day of the following month and then move backward one day.

Make yourself a little function that does something like this:

  1. Obtain the month and year in question (the one where you want the last day)
  2. Use DateSerial to combine the month and the year, along with the day "1" to get the first day of the month in question.
  3. Use DateAdd to add one month. This will get you the first day of the next month (which is one day after the date you really want).
  4. Use DateAdd again to subtract (move back) one day. This will give you the last day of the month where you started.
    Function eom(ByVal input_date As Date) As Date
    
      ' take the first day of the month from the input date, add one month, 
      ' then back up one day
      eom = DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year(input_date), Month(input_date), 1)))
    
    End Function

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

In Access VBA, you can call Excel's EOMonth worksheet function (or almost any of Excel's worksheet methods) is by binding to an Excel application object and a WorksheetFunction object, which can be accomplished in a few ways.

Calling Excel functions with an Late Bound object

The shortest method from Access VBA is with a single line of code using a late-bound object. This example returns the date of the last day of the current month:

MsgBox CreateObject("Excel.Application").WorksheetFunction.EOMonth(Now(), 0)

A more verbose method, as a function:

Function eoMonth_LateBound(dt As Date) As Date
  Dim xl As Object
  Set xl = CreateObject("Excel.Application")
  eoMonth_LateBound = xl.WorksheetFunction.eomonth(dt, 0)
  Set xl = Nothing
End Function

An issue with late-bound references is that VBA takes a second to bind the object each time the function is called. This can be avoided by using early binding.

Calling Excel functions with an Early Bound object

If the function is to be used repeatedly, it's more efficient to go with Early Binding and retain the object between calls, for example:

  • Go Tools > References and add a reference to "Microsoft Excel x.xx Object Library" (use whatever the newest version number is that you have installed).

  • Add this code in a new module:

    Option Compare Database
Option Explicit

Dim xl As Excel.Application

Function eoMonth_EarlyBound(dt As Date) As Date
  If xl Is Nothing Then Set xl = New Excel.Application
  eoMonth_EarlyBound = xl.WorksheetFunction.eomonth(dt, 0)
End Function

Sub demo()
  MsgBox eoMonth_EarlyBound(Now())
  MsgBox eoMonth_EarlyBound("4/20/2001")
End Sub

Creating a WorksheetFunction object

If Excel's worksheet functions are to be used lots throughout your code, you could even create a WorksheetFunction object to simplify the calls. For example, this could be a simple way to join multiple strings with TEXTJOIN, or get a response from an API with WEBSERVICE:

Sub Examples()
'requires reference: "Microsoft Excel x.xx Object Library"
  Dim xl As Excel.Application, wsf As Excel.WorksheetFunction
  Set xl = New Excel.Application
  Set wsf = xl.WorksheetFunction

  'use EOMONTH to return last date of current month
  Debug.Print CDate(wsf.eomonth(Now(), 0))

  'use WEBSERVICE return your current IP address from a free JSON API
  Debug.Print wsf.WebService("https://api.ipify.org")

  'use TEXTJOIN to implode a bunch of values
  Debug.Print wsf.TextJoin(" & ", True, "join", "this", , "and", , "that", "too")

  'always tidy up your mess when finished playing with objects!
  Set wsf = Nothing
  Set xl = Nothing
End Sub

Note that these functions may require Excel 2016+ or Excel 365 (aka: Object Library 16.0+.)

Upvotes: 0

OTA-SAN
OTA-SAN

Reputation: 39

I realize this is a bit late into the conversation, but there is an already available worksheet function that gives the end of month date, EoMonth().

Pasting into the Immediate Window:

    ?Format(CDate(WorksheetFunction.EoMonth(Date, 0)), "dd")

Will return the last day of the month based on current date.

As a UDF, it makes sense to give it a default Argument:

    Function LastDay(Optional DateUsed As Date) As String
        If DateUsed = Null Then DateUsed = Date
        LastDay = Format(CDate(WorksheetFunction.EoMonth(DateUsed, 0)), "dd")
        Debug.Print LastDay
    End Function

If you feed it Arguments, be sure that they are Date Literals (i.e. Enclosed with #s)

LastDay(#3/10#)
Result: 31

LastDay #2/11/2012#
Result: 29 '(A leap Year)

Note the output Data Type is String (not Date) and that the format of the date can be adjusted as needed (Ex: "mm/dd/yyyy" instead of "dd").

If the Date Data Type is needed, use:

Function LastDay(Optional DateUsed As Date) As Date
    If DateUsed = 0 Then DateUsed = Date
    LastDay = WorksheetFunction.EoMonth(DateUsed, 0)
    Debug.Print CDate(LastDay)
End Function

I hope that helps someone.

Upvotes: 3

Bill
Bill

Reputation: 41

Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim d1 As String

   Set Rng = Range("A2")
   d1 = Range("a2").Value2 'put a date in A2 Formatted as date(cell format)

   Dim years
   Dim months
   Dim end_month
   years = year(d1)
   months = month(d1)

   end_month = Day(DateSerial(years, months + 1, 1 - 1)) 'add one month and subtract one day from the first day of that month

   MsgBox CStr(end_month), vbOKOnly, "Last day of the month"


End Sub

Upvotes: 4

mtholen
mtholen

Reputation: 1693

GD Linuxman,

Let's focus on obtaining the result...:-)

See also: here

The comment by @Scott Craner is spot on ! Though strictly speaking there is no need to use the formatting. (Assuming you want to work with the 'Date' object)

To achieve what you want, setup the function as per below:

Function GetNowLast() as Date

    dYear = Year(Now)
    dMonth = Month(Now)

    getDate = DateSerial(dYear, dMonth + 1, 0)

    GetNowLast = getDate

End Function

You can call the function in your code as:

Sub findLastDayOfMonth()

    lastDay = GetNowLast()

End Sub

Alternatively, and neater is likely:

Function GetNowLast(inputDate as Date) as Date

    dYear = Year(inputDate)
    dMonth = Month(inputDate)

    getDate = DateSerial(dYear, dMonth + 1, 0)

    GetNowLast = getDate

End Function

You can call that function and pass it an input parameter.

Sub findLastDayOfMonth()

lastDay = GetNowLast(Now()) 'Or any other date you would like to know the last day of the month of.

End Sub

See also this neat solution by @KekuSemau

Upvotes: 21

Related Questions