VBA - 访问 - 获取一个月的最后一天

VBA - Access - Get the last day of month

提问by linuxman

I need your help. I want to get the last day of the month. Well this is my code, but if i want to debug it and compile it to the database it says it has an error in the syntax. Please help =)

我需要你的帮助。我想得到这个月的最后一天。好吧，这是我的代码，但是如果我想调试它并将其编译到数据库中，它会说它在语法中存在错误。请帮忙=)

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

回答by mtholen

GD Linuxman,

GD Linux人，

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)

@Scott Craner 的评论是正确的！虽然严格来说没有必要使用格式。（假设您想使用“日期”对象）

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 thisneat solution by @KekuSemau

另请参阅@KekuSemau 的这个简洁的解决方案

回答by Bill

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

回答by OTA-SAN

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().

我意识到这有点晚了，但是已经有一个工作表函数可以提供月末日期 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:

作为 UDF，给它一个默认参数是有意义的：

    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)

如果您提供参数，请确保它们是日期文字（即用#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").

请注意，输出数据类型是String（不是Date）并且可以根据需要调整日期格式（例如：“mm/dd/yyyy”而不是“dd”）。

If the DateData 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.

我希望能帮助某人。

回答by ashleedawg

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

在 Access VBA 中，您可以通过绑定到 Excel 应用程序对象和对象来调用 Excel 的EOMonth工作表函数（或几乎任何Excel 的工作表方法），这可以通过几种方式完成。WorksheetFunction

Calling Excel functions with an Late Bound object

使用后期绑定对象调用 Excel 函数

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:

Access VBA 中最短的方法是使用一行代码使用后期绑定对象。此示例返回当月最后一天的日期：

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.

后期绑定引用的一个问题是，每次调用该函数时，VBA 都需要一秒钟来绑定对象。这可以通过使用早期绑定来避免。

---

---

Calling Excel functions with an Early Bound object

使用 Early Bound 对象调用 Excel 函数

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:

如果要重复使用该函数，则使用 E​​arly Binding 并在调用之间保留对象会更有效，例如：

• Go Tools> Referencesand add a referenceto "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
  

• 转到“工具”>“引用”并添加对“ Microsoft Excel x.xx Object Library”的引用（使用您安装的任何最新版本号）。

• 将此代码添加到新模块中：

  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 WorksheetFunctionobject

创建WorksheetFunction对象

If Excel's worksheet functions are to be used lots throughout your code, you could even create a WorksheetFunctionobject 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:

如果要在整个代码中大量使用 Excel 的工作表函数，您甚至可以创建一个WorksheetFunction对象来简化调用。例如，这可能是一种将多个字符串与 连接起来的简单方法TEXTJOIN，或者使用以下命令从 API 获取响应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+.)}

_{请注意，这些函数可能需要Excel 2016+ 或 Excel 365（又名：Object Library 16.0+。）}

回答by CraigD

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.
4. Use DateAdd again to subtract (move back) one day. This will give you the last day of the month where you started.

1. 获取有问题的月份和年份（您想要最后一天的月份和年份）
2. 使用 DateSerial 组合月份和年份，以及日期“1”以获得相关月份的第一天。
3. 使用 DateAdd 添加一个月。这将使您获得下个月的第一天。
4. 再次使用 DateAdd 减去（移回）一天。这将为您提供您开始的月份的最后一天。

    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