CODEWITHSUNDEEP
exceldatevba
Marten Onnink
Marten Onnink

Reputation: 131

How to return a date value using a Public Function

I want to use a function that returns the most recent date from a series of dates. 

Public Function RecentDate() As Date

Dim MaxDate As Date
Sheets("Data").Activate
MaxDate = Application.WorksheetFunction.Max(Columns("A"))

End Function

However, when I use this function within a sub I get: 00:00:00. What am I doing wrong?

Sub ShowDate()

MsgBox (RecentDate())

End Sub

Upvotes: 1

Views: 368

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

You are calling Function RecentDate, but you never set the value of RecentDate to the value of MaxDate.

Anyway, you don't realy need an extra vairable MaxDate, and there's not need to Activate the "Data" worksheet.

Change your Function code to:

Public Function RecentDate() As Date

RecentDate = Application.WorksheetFunction.Max(Sheets("Data").Columns("A"))

End Function

Upvotes: 1

Related Questions