PDA

View Full Version : Solved: Help to solve a little mistery



didig
05-24-2013, 09:45 AM
I'm trying to ask for a date and provide as default today's date. I tried with the following

Date= Application.InputBox("date (month/day/year)", "date", now(), , , , , 1)

worked fine. Then since it shows also the time, which is not needed I changed now() with today() as shown below and it tells me

compile error:
Sub or Function not defined

Date= Application.InputBox("date (month/day/year)", "date", TODAY(), , , , , 1)

What am I doing wrong?

Thanks a lot :thumb

JKwan
05-24-2013, 10:57 AM
try this
Date = Application.InputBox("date (month/day/year)", "date", Format(Now(), "mm/dd/yyyy"), , , , , 1)

didig
05-24-2013, 11:11 AM
Tnx JKwan, it worked!!

so basically today() cannot be used in VBA...?

:hi: :hi:

Kenneth Hobs
05-24-2013, 12:03 PM
Today() is not a VBA function.

I would use Date rather than Now() in VBA since time was not needed and avoid using named functions for variable names.

If you love Today() then use Evaluate().


Sub test()
Dim d As Date
d = Application.InputBox("date (month/day/year)", "date", Format(Date, "mm/dd/yyyy"), , , , , 1)
MsgBox Format(d, "mm/dd/yyyy"), , "test"
End Sub

Sub test2()
Dim d As String
d = Application.InputBox("date (month/day/year)", "date", Format(Evaluate("Today()"), "mm/dd/yyyy"), , , , , 1)
MsgBox Format(d, "mm/dd/yyyy"), , "test2"
End Sub

didig
05-24-2013, 01:10 PM
Thanks Ken :-).

So basically one can use Evaluate ("no VBA Funct.") anytime one has a function that cannot be used directly in VBA, correct?

Thanks again :-)!

Kenneth Hobs
05-24-2013, 02:40 PM
Normally, for a worksheet function, I use WorkSheetFunction.FunctionName(input parameters) or Application.WorksheetFunction.FunctionName(input parameters). For some functions not available via WorkSheetFunction method as in this case, use Evaluate().

Brackets, [], can be used to directly Evaluate some worksheetfunctions and useful for quick array notation.

didig
05-25-2013, 10:35 AM
Thanks a lot!! Very helpful :-)