Finding date one month from now in VBA function 1

[cdipaolo]

I am passing a date into a function and want to return a date one month from that date. For example, if I pass in 1/1/2003 I want to return 2/1/2003. I know how to do this in Excel (=date(year(a1),month(a1)+1,day(a1)) (the date will always be the first of the month for simplicity's sake). How can I do this within a VBA function? I don't think I can call WorksheetFunction.Date like I can WorksheetFunction.Vlookup...

 

[xlbo]

You can use Day Month and Year in VBA without any probs
For UK date formats, this function will work:
Function AddMonth(mDate As Date)
AddMonth = Day(mDate) & "/" & Month(mDate) + 1 & "/" & Year(mDate)
End Function Rgds
~Geoff~

 

[cdipaolo]

Thanks - I'm still getting the hang of translating standard worksheet functions into VBA - this will be particularly helpful!

 

[haig]

There's also the 'DateAdd' function it takes three parameters, == interval, number, date

Function AddAYear(dType, dCount, DateToIncrement) As Variant
AddToDate = DateAdd(dType, dCount, DateToIncrement)
End Function

Interval is a string for the type of increment "m" for month, "yyyy" for year, etc.

I really don't understand why this function (and similarly useful ones) isn't available as an Excel function. Haig
The Aeon Group