Excel '03: Date(Year(), Month(), Day()) not working

[mavalpha]

Nice, simple test in VBA.
startyear=now()
nextyear=date(year(startyear)+1,month(startyear),day(startyear))
Compile error: Missing )

Help! Have Analysis ToolPak installed.

 

[mikeblas]

No space between "e" and "a" in "startyear" as the parameter to day().

 

[mavalpha]

Cosmetic, if you quote my post you'll see that it's a rendering artifact of the Forums.

At this point, I'm leaning towards VBA and straight Excel not understanding all of the same commands, like date() and edate() (to add months to a starting point). How likely is this?

 

[mikeblas]

Oh, I see. Around here, we use code tags to prevent vBulletin from reformatting code in confusing ways.

Excel uses VBA; if you use the object browser, I think you'll find the function you want is DateSerial(). Date is a data type, not a function.

Sub test()
startyear = Now()
nextYear = DateSerial(Year(startyear) + 1, Month(startyear), Day(startyear))
MsgBox (nextYear)
End Sub

 

[mavalpha]

Date is a function as well, useful for consolidating a month/day/year format into a single, easily manipulated variable.

I ended up cheating, by setting cell.value=date(whatever) and then retrieving the value again. Confirmed that VBA does not have all of Excel's functions built in.

Thanks for the second set of eyes!

 

[mikeblas]

I misspoke; Date is not a type keyword; it is instead a variant type. Date is a function in Excel worksheets. Date is not a function in VBA; the Excel macro functions are a different namespace (and language) than VBA. In VBA, Date is a property of DateTime, which is a global. You can demonstrate that to yourself with this script:

Sub qewrqwer()
    Dim foo As Date
    foo = Now()
    MsgBox (foo)
End Sub

 

[mavalpha]

Yeah, it gave me a lot of headaches- enough that I started reworking a lot of the code, and will be working on it over the weekend. I think I can actually have it done in time for a Monday launch at work.

Thanks again!