End of Month Function in Visual Basic

[Fredriksson via OfficeKB.com]

I would like to initialize a text box in a user form with the last day of the
month calculated from the current date (Today)

I tried the following:

Private Sub UserForm_Initialize()
Me.AcctPeriodInputBox.Value = EOMONTH(Date)
End Sub

How do I use Functions that are in Excel to calculate values in a macro?

 

[merjet]

Precede them by Application. or Application.WorksheetFunction.

Hth,
Merjet

 

[NickHK]

If you are using VBA anyway, there's no need for external functions:

Public Function LastDayOfMonth(WhichMonth As Date) As Date
LastDayOfMonth = DateSerial(Year(WhichMonth), Month(WhichMonth) + 1, 0)
End Function

Adjust the argument/return value for your needs.

NickHK

 

[Fredriksson via OfficeKB.com]

Thank you for your suggestion

Precede them by Application. or Application.WorksheetFunction.

Hth,
Merjet

 

[Fredriksson via OfficeKB.com]

Thank you for your suggestion

If you are using VBA anyway, there's no need for external functions:

Public Function LastDayOfMonth(WhichMonth As Date) As Date
LastDayOfMonth = DateSerial(Year(WhichMonth), Month(WhichMonth) + 1, 0)
End Function

Adjust the argument/return value for your needs.

NickHK

I would like to initialize a text box in a user form with the last day of the
month calculated from the current date (Today)

[quoted text clipped - 6 lines]

How do I use Functions that are in Excel to calculate values in a macro?